Using SQLite - presentation to ADUG Melbourne by Lance Collins, 15 July 2013 *INTRODUCTION* I have a program I wrote years ago to process sharemarket data, principally for use with Metastock. Now I'm rewriting it for portfolio management and to do charts with TeeChart (I won a copy at a couple of years ago at an ADUG symposium). I'm using SQLite to store my data rather than csv, typed, ini and Metastock files. I program in Delphi 7, sticking with it because I don't need to refer to the help any more! Disclaimer: Code written for my own use. Design decisions may be 'seemed like a good idea at the time' This is not a best practice tutorial on using SQLite, Object oriented coding etc. __________________________________________________________________________________________________ *MY PROJECT* When I started investing in 1987 data was limited and expensive and presentation tools were also limited and expensive. I wrote programs to collect market data from several sources and prepare it for display using MetaStock The final version of the program I use now dates from 2009. Price and company data is stored in typed files and the day to day parameters stored in a complex INI file. The new program will use SQLite tables and will present the data using TeeChart. ______________________________________________________________________________________________________ *WHAT IS SQL (STRUCTURED QUERY LANGUAGE)* SQL is a human readable syntax for management of an SQL database. Data in an SQL database is stored as tables. A table is a set of data elements that is organized using a model of vertical columns (which are identified by their name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns but can have any number of rows. Think of a string grid of columns and rows. Each column has a heading (a field name). Querying a database table returns rows of data. The basic interface is a buffer with a cell for each field in a row. (grossly oversimplifying) The general form of an SQL command is: action fields table condition e.g. SELECT code date openprice FROM PRICES WHERE code = 'BHP' _______________________________________________________________________________________ *WHAT IS SQLITE* (from SQLITE.ORG) << 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 currently found in 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-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. *Features Of SQLite* Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures. Zero-configuration - no setup or administration needed. Implements most of SQL92. A complete database is stored in a single cross-platform disk file. Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.) Small code footprint: less than 500KB fully configured or much less with optional features omitted. Faster than popular client/server database engines for most common operations. Simple, easy to use API. Written in ANSI-C. Bindings for dozens of other languages available. Well-commented source code with 100% branch test coverage. Available as a single ANSI-C source-code file that you can easily drop into another project. Self-contained: no external dependencies. Cross-platform: Unix (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems. Sources are in the public domain. Use for any purpose. Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases. >> Probably more copies of SQLite in existance than all other databases combined. For use on Windows SQLite comes as a DLL about 500Kb in size. _____________________________________________________________________________________________ *RESOURCES* SQLITE.ORG has lists of resources The book: The Definitive Guide To SQLite - Allen & Owens is available in two editions. The first edition is readily available for download. The second edition is not free. It's a classic computer text, baffling on first reading but when you understand the topic a little you can find lots of useful information. There is also lots of stuff about SQL and relational databases which is way way beyond anything I needed for my project. I found that Googling 'Delphi SQLite xxxx' where xxxx is a few keywords of your problem usually returned lots of useful information at StackOverflow. _________________________________________________________________________________________________ *Delphi versus SQL* SQL can do a lot of data transformations besides just storing and retrieving data. You have a choice of where to place some of your logic. Use SQL or do it yourself. I chose to use SQLite as a data repository only. I am also ignorant of TDataSet and data aware components. So in my programs I use a TObjectList to parallel each table in the database. _________________________________________________________________________________________________ *INTERFACE* There is lot of interface code on the net by a Tim Anderson and many additions to his code. I have simplified this code down to two derived Delphi units and one of my own devising. SQLiteAPI maps the DLL C functions SQLiteMgr Simple classes for using SQLite to persist Delphi objects TSQLiteMgr wraps the calls to open and close an SQLite database. It also wraps SQLite_exec for queries that do not return a result set. i.e. management functions not involving a table. TSQLiteTable wraps execution of an SQL query to read or write table rows. It runs a query and reads first row only! You can step to next row (until 'not EOF') with the 'Step' method. By not using any internal buffering, this class is very close to Sqlite API. This is a simplified verion of the Delphi Wrapper SQLiteTable3 unit. SQLiteFields Base definitions to be inherited by fields needed by the code which copies data between Delphi objects and SQLite. ___________________________________________________________________________________________ *PERSISTANCE* For each table there is a unit which handles copying data between the table buffer and an objectlist passed from the main program. At program start the smaller tables are read into object lists and on completion are written back to the database. With large tables the SQL query will retrieve or write a sub-set of the data. e.g just the price data for BHP or just today's data for the whole market. _________________________________________________________________________________________ * DLL Interface* This is a basic sub-set of the API calls, enough to see how the interface works << C calls type TSQLiteDB = Pointer; TSQLiteResult = ^PAnsiChar; TSQLiteStmt = Pointer; function SQLite3_Open(filename: PAnsiChar; var db: TSQLiteDB): integer; cdecl; external SQLiteDLL name 'sqlite3_open'; function SQLite3_Close(db: TSQLiteDB): integer; cdecl; external SQLiteDLL name 'sqlite3_close'; function SQLite3_ColumnCount(hStmt: TSqliteStmt): integer; cdecl; external SQLiteDLL name 'sqlite3_column_count'; function SQLite3_ColumnName(hStmt: TSqliteStmt; ColNum: integer): PAnsiChar; cdecl; external SQLiteDLL name 'sqlite3_column_name'; function SQLite3_Prepare_v2(db: TSQLiteDB; SQLStatement: PAnsiChar; nBytes: integer; var hStmt: TSqliteStmt; var pzTail: PAnsiChar): integer; cdecl; external SQLiteDLL name 'sqlite3_prepare_v2'; function SQLite3_Step(hStmt: TSqliteStmt): integer; cdecl; external SQLiteDLL name 'sqlite3_step'; function SQLite3_Finalize(hStmt: TSqliteStmt): integer; cdecl; external SQLiteDLL name 'sqlite3_finalize'; function SQLite3_Reset(hStmt: TSqliteStmt): integer; cdecl; external SQLiteDLL name 'sqlite3_reset'; _____________________________________________________________________________________ function SQLite3_ColumnText(hStmt: TSqliteStmt; ColNum: integer): PAnsiChar; cdecl; external SQLiteDLL name 'sqlite3_column_text'; function SQLite3_ColumnInt(hStmt: TSqliteStmt; ColNum: integer): integer; cdecl; external SQLiteDLL name 'sqlite3_column_int'; function SQLite3_ColumnInt64(hStmt: TSqliteStmt; ColNum: integer): Int64; cdecl; external SQLiteDLL name 'sqlite3_column_int64'; function SQLite3_ColumnDouble(hStmt: TSqliteStmt; ColNum: integer): double; cdecl; external SQLiteDLL name 'sqlite3_column_double'; function SQLite3_ColumnBlob(hStmt: TSqliteStmt; ColNum: integer): pointer; cdecl; external SQLiteDLL name 'sqlite3_column_blob'; ______________________________________________________________________________________ function sqlite3_bind_text(hStmt: TSqliteStmt; ParamNum: integer; Text: PAnsiChar; numBytes: integer; ptrDestructor: TSQLite3Destructor) : integer; cdecl; external SQLiteDLL name 'sqlite3_bind_text'; function sqlite3_bind_int(hStmt: TSqLiteStmt; ParamNum: integer; Data: integer) : integer; cdecl; external SQLiteDLL name 'sqlite3_bind_int'; function sqlite3_bind_int64(hStmt: TSqliteStmt; ParamNum: integer; Data: int64) : integer; cdecl; external SQLiteDLL name 'sqlite3_bind_int64'; function sqlite3_bind_double(hStmt: TSqliteStmt; ParamNum: integer; Data: Double) : integer; cdecl; external SQLiteDLL name 'sqlite3_bind_double'; function sqlite3_bind_blob(hStmt: TSqliteStmt; ParamNum: integer; ptrData: pointer; numBytes: integer; ptrDestructor: TSQLite3Destructor) : integer; cdecl; external SQLiteDLL name 'sqlite3_bind_blob'; >> _________________________________________________________________________________________________________________