#1 2013-06-16 14:18:34

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...


- 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!


#2 2013-06-16 19:16:02

From: France
Registered: 2010-06-21
Posts: 13,181

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).


#3 2013-06-17 10:53:37

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


Board footer

Powered by FluxBB