mORMot Open Source

enrcas

Member

Registered: 2013-06-16

Posts: 20

Very simple Sqlite3 example, no ORM, just basic usage stuff.

I want to test basic Sqlite3 usage here, i want to show the lib to some coworkers.

I'll do some inserts, show table name, then a general select, and then show first 10 rows of select. Pretty basic stuff to start with this lib.

This is inside a basic console app,

const
  CREATE_SQL ='create table testtable (id integer primary key, randomst text, randomint integer);';
  CREATE_SQL_IDX = 'create unique index idx_st on testtable (randomst asc);';
  INSERT_SQL ='insert into testtable values (null, ?, ?);';
  SELECT_SQL ='select * from testtable;';
  DB_FILE_PATH = 'd:\00\';
  DB_FILE_NAME = 'sqltest.db';
  DB_FILE = DB_FILE_PATH+DB_FILE_NAME;

var
  s1,s2:rawutf8;
  c1,c2:longint;

  sqldb:tsqldatabase;
  req:tsqlrequest;
  tableNames: TRawUTF8DynArray;
  nrows:ptrint;

...

//create table / file
  sqldb:=TSQLDatabase.Create(DB_FILE);
  sqldb.Synchronous:=smOff;
  sqldb.LockingMode:=lmExclusive;
  sqldb.MemoryMappedMB:=128;
  sqldb.usecache:=true;

  Writeln('Running on SQLite3 version ',sqlite3.ClassName,' ',sqlite3.Version);

  sqldb.Execute(CREATE_SQL);
  sqldb.Execute(CREATE_SQL_IDX);

  sqldb.GetTableNames(tableNames);
  writeln('Tables found in db: ',length(tableNames));
  for c1 := 0 to length(tableNames)-1 do
    writeln(tablenames[c1]);


  req.Prepare(sqldb.DB,INSERT_SQL); //prepare insert
  //insert 1000 random rows
  sqldb.TransactionBegin;
  for c1 := 1 to 1000 do
  begin
    req.bind(1,genrandomstring(50));
    req.bind(2,random(MaxInt));
    req.Step;
    req.Reset;
  end;
  sqldb.Commit;
  req.close;
  //select all rows
  s1:=sqldb.ExecuteJSON(SELECT_SQL,false,@nrows);
  writeln(nrows,' rows returned');

  FreeAndNil(sqldb);

  writeln('End testing. Press enter to exit');

 
  What i am thinking is if this is the right way to make inserts inside a transaction, with a prepared insert.

  req.Prepare(sqldb.DB,INSERT_SQL); //prepare insert
  sqldb.TransactionBegin;
  for c1 := 1 to 1000 do
  begin
    req.bind(1,genrandomstring(50));
    req.bind(2,random(MaxInt));
    req.Step;
    req.Reset;
  end;
  sqldb.Commit;
  req.close;

  The req.Reset seems necessary, if not i got a SQLITE_MISUSE(21) error.

  First i tried this:

  req.Prepare(sqldb.DB,INSERT_SQL); //prepare insert
  sqldb.TransactionBegin;
  for c1 := 1 to 1000 do
  begin
    req.bind(1,genrandomstring(50));
    req.bind(2,random(MaxInt));
    req.Execute;
  end;
  sqldb.Commit;
  req.close;

But got a SQL error also. So i was thinking which is the right way to do basic inserts with the sqlite3 wrapper.

  Prepare->bind->step->reset->...repeat the bind->...etc ??

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,232

Website

Re: Very simple Sqlite3 example, no ORM, just basic usage stuff.

I would rather use the SynDB layer, so you my be able to switch to another DB afterwards, if needed.

Note that your code will actually be slower than by using the ORM and a Batch.
The ORM Batch will generate optimized INSERTs for better performance than naive iteration, and will reuse prepared statements.

About how to use of direct SynSqlite3, you may use the TSQLStatementCached to reuse prepared statements.
Otherwise, the method order is Prepare followed by Bind + Execute + Reset.
Step is only to browse all results rows after a SELECT.
Please check the documentation, which states all this.

enrcas

Member

Registered: 2013-06-16

Posts: 20

Re: Very simple Sqlite3 example, no ORM, just basic usage stuff.

Thanks! I've been reading the examples, and see exactly what you say, ORM+Batch. It's more complex at first sight to me, thanks! Sometimes it's hard to learn new paradigms...
I am very happy with this library it seems very well rounded in all corner, thanks for all the good work ab!

Edit:
well it seems bind, execute and reset don't work for me, ("tsqlrequest.Reset called with no previous Request", error) ... but bind, step and reset works perfectly.

Anyway, i'll forge accesing directly the library and will try ORM+Batch
Thanks!

Edit 2: I know all you already have seen many benchmarks of Mormot. So this is just mine testing pretty easy inserts.

Compiled in Berlin 10.1, executed in a i5, 8GB, SSD Samsung EVO

Transactioned, basic sqlite3 1.000.000 rows inserts using Tsqlrequest, with prepared insert
- 12,3segs - 80.932 rows inserted per second

TSQLRestServerDB+TSQLRestBatch same 1.000.000 rows inserted
- 8,50seg prepared + 2,14seg inserts - total 10,54seg - 94.789 rows inserted per second

Black magic!

Last edited by enrcas (2017-06-12 16:46:25)