# pSQLite

A wrapper for SQLite, a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine.
SQLite is the most used database engine in the world. Some excerpts from their website:

> Small. Fast. Reliable. Choose any three.
> SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
> The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.
> SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.
> SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process.
> SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
> The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big- and little-endian architectures.
> These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()
> SQLite is very carefully tested prior to every release and has a reputation for being very reliable.
> SQLite responds gracefully to memory allocation failures and disk I/O errors.
> Transactions are ACID (Atomic, Consistent, Isolated, and Durable) even if interrupted by system crashes or power failures.
> The SQLite code base is supported by an international team of developers who work on SQLite full-time.
> The developers continue to expand the capabilities of SQLite and enhance its reliability and performance while maintaining backwards compatibility with the published interface spec, SQL syntax, and database file format. The source code is absolutely free to anybody who wants it, but professional support is also available.
> We the developers hope that you find SQLite useful and we entreat you to use it well: to make good and beautiful products that are fast, reliable, and simple to use.

Specific advantages of pSQLite.e over database.e include:

- Much higher resilience to program crashes and power failures.
- The resulting databases can be read and written by applications whether they are written in Phix or not.
- It can be much faster (see performance note below).

And disadvantages include:

- It uses SQL - which is ok, I guess, but I never did quite get inner/outer joins..

To use pSQLite, you must include pSQLite.e. The file phix\builtins\pSQLite.e is a "stub include" that simply contains include ..\demo\pSQLite\pSQLite.e (same on Windows and Linux) to simplify matters.
I would however recommend placing copies of (the real) pSQLite.e and any dlls you need in with the application directory, to ease distribution and maintenance.
Based heavily on the work of Ray Smith, Chris Burch, and Tone Skoda, the main difference here is that Phix can pass strings directly to C functions, which leads to a saving of at least one pair of calls to allocate_string() and free() in almost every routine. Another difference is where it loads the dll from, and some work was done to make it a little bit more thread safe, and 64-bit compatible. I also implemented the sqlite3_exec() optional callback routine to process rows retrieved by queries, which was missing. It was also helpful to repackage it as a learning exercise and as part of crafting this documentation, or perhaps more accurately, it is usually virtually impossible to write any docs without finding some excuse to tweak the code.
None of these routines are supported by pwa/p2js.

### Example:

Copy

    constant LIM = 100000
    include pSQLite.e

    ?sqlite3_libversion()

    sqlite3 db = sqlite3_open("test.sqlite")

    procedure db_exec(string cmd, bool fatal=true)
        integer res = sqlite3_exec(db,cmd)
        if fatal and res!=SQLITE_OK then ?9/0 end if
    end procedure

    db_exec("DROP TABLE test;",fatal:=false)
    db_exec("CREATE TABLE test (code INTEGER PRIMARY KEY, data);")

    atom t0 = time()
    db_exec("BEGIN TRANSACTION;") -- (or just "BEGIN;")
    for i=1 to LIM do
        db_exec(sprintf("INSERT INTO test VALUES (%d,%d);",{i,i}))
    end for
    db_exec("END TRANSACTION;")  -- (same as "COMMIT;")
    t0 = time()-t0

    sqlite3_close(db)

    printf(1,"%,d records added in %3.2fs, %3.2f/s\n",{LIM,t0,LIM/t0})

    -- sample output:
    -- "3.23.1"
    -- 100,000 records added in 0.98s, 101522.84/s
    --or (an older and slower verson):
    -- "3.2.1"
    -- 100,000 records added in 1.24s, 80971.66/s

### Performance:

SQLite defaults to maximum integrity: letting it re-open/verify/wait for disk updates/close around every change can easily result in a program that achieves less than 10 updates per second. Thankfully, grouping (related) updates by simply surrounding them with "BEGIN TRANSACTION;" and "END TRANSACTION;" can easily result in a 10,000-fold performance increase, ie a truly staggering 100,000+ updates per second, though I suspect that peak rate might not last too long. In comparison, database.e manages about 5,000/s, by itself still fairly decent, even if it does seem rather poor head-to-head like that.
Although at heart it uses much the same technology, there may be some additional overheads with using sqlite3_bind_blob() and sqlite3_column_blob() on long/complex sequences/structures (specifically storage of interim serialization values) that mean database.e is actually faster, or at least a little more on par.
Obviously you can test performance by running the above snippet, but should you decide to comment out those transaction statements, I strongly recommend that you also change LIM to 100 or less!
One approach I have used quite successfully is a timer/countdown arrangement that performs an end transaction after 5 seconds without an update, or when the program shuts down (and occasionally those rare events that genuinely need perfect integrity). Obviously each update starts a new transaction when the countdown is already zero, otherwise just resets it to the full 5 seconds.
None of these routines are supported by pwa/p2js.