mORMot Open Source

enrcas

Member

Registered: 2013-06-16

Posts: 20

Simple SynDBSQLite3 or SynSQLite3 example

hi friends,

i am lately playing a bit with SQLite3 as base for a chess project, i am trying to clean and de-dupe all my pgn library. PGN files are text files with description of chess games. So i think creating a SQLite3 DB will be a good solution in the first step. I got some GBs of PGN with nearly ~10-15? million games, and i suspect that after deduping i'll get much less, maybe 8-9? Let's see...

well, i tried first with DiSqlite but i got some weird errors (i am using Delphi 7 in my Windows8 machine) and start looking information, so, through some stackoverflow threads i am finally here, discorvering this project...

As the text files are huge i need to be *fast* so, i don't want overheads, just pure speed, and that's is a thing that mORMot excels!

I've read i can left out all the ORM overhead and just use the SQlite3 fast access, that's what i am looking for. i've read the pdf file (the 1.8) version, and read the examples, but nearly all the information is abou using full mORMot, servers, json, complex classes, etc...

So i am a bit lost, because what i need is much much simpler,  a chess games just are some text strings (players, elo, dates, etc...) and the game in-self (also a string), i only need

- test if local file db exists, if not create it, using parameters in the SQLite3 to be as fast as possible (no locking, etc, i've already read all of this)

- process a pgn file (this can be a 1MB file or a 4GB file), i've already implemented this

- BATCH/bulk insert chess games in the file, because for being VERY FAST some kind of batch/bulk inserts is needed, some useful information i've read in these urls
http://blog.quibb.org/2010/08/fast-bulk … to-sqlite/ and http://stackoverflow.com/questions/1209 … rts-sqlite

- of course, if a chess game is already in the file is not inserted, i'll use uniques in the game data (this must be tested because the length of the string...)

- also i have to study if it's interesting to store the chess game in compressed data, i've already tested this with DiUCL library, but found you ALSO provide a SynLZ unit (FANTASTIC WORK!!!)

- reaching the end of the pgn file, close all files, and output data

- repeat with all the information, and finally i'll get a base sqlite3 file with millions of chess games,

- maybe build a interface over this file to make fast searchs, per position, per player, etc...

so...

- open/create file db
- bulk inserts
- close file

in the future

- open db
- provide some db maintaning through sql or direct api (delete records, search, etc)

thanks for reading friends!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Simple SynDBSQLite3 or SynSQLite3 example

Some points:

- BATCH is included in SynDB, but for SQLite3, just re-use an existing statement, WITHIN a transaction.
- For uniqueness, maybe stores the CRC32 hash of the game (stored as integer), then if it matches, search for the actual content.

For the sample code, see:
- "12 SynDBExplorer";
- "13 Standalone JSON SQL Server";
- "16 Execute SQL via services".

And the latest version of the SAD pdf (uploaded today).

enrcas

Member

Registered: 2013-06-16

Posts: 20

Re: Simple SynDBSQLite3 or SynSQLite3 example

thanks for the tips admin!
i'll keep on posting in this thread for other people interested in starting simple sqlite3 use in delphi