Programming SQLite in C Tutorial Two

Programming SQLite in C Tutorial Two This tutorial is the second in a series on programming SQLite in C. SQLite stores a collection of tables in a single file database, usually ending in .db. Each table is like a spreadsheet, it consists of a number of columns and each row has values. If it helps, think of each row as being a struct, with the columns in the table corresponding to the  fields in the struct. A table can have as many rows as will fit on a disk. There is an upper limit but its huge 18,446,744,073,709,551,616 to be precise. A table can have up to 2,000 columns or if you recompile the source, you can max it to an awesome 32,767 columns. The SQLite API To use SQLite, we need to make calls to the API. You can find an introduction to this API on the official Introduction to SQLite C/C Interface web page. Its a collection of functions and easy to use. First, we need a handle to the database. This is of type sqlite3 and is returned by a call to sqlite3_open( filename, **ppDB). After that, we execute the SQL. Lets have a slight digression first though and create a usable database and some tables using SQLiteSpy. (See the previous tutorial for links to that and the SQLite Database Browser). Events and Venues The database about.DB will hold three tables to manage events at several venues. These events will be parties, discos, and concerts and will take place at five venues (alpha, beta, charlie, delta, and echo). When you are modeling something like this, it often helps to start with a spreadsheet. For simplicities sake, Ill just store a date not a time. The spreadsheet has three columns: Dates, Venue, Event Type and about ten events like this. Dates run from 21st to 30th of June 2013. Now SQLite has no explicit date type, so its easier and faster to store it as an int and the same way that Excel uses dates (days since Jan 1, 1900) have int values 41446 to 41455. If you put the dates in a spreadsheet then format the date column as a number with 0 decimal places, it looks something like this: Now we could store this data in one table and for such a simple example, it would probably be acceptable. However good database design practice requires some normalization. Unique data items like venue type should be in its own table and the event types (party etc) should also be in one. Finally, as we can have multiple event types at multiple venues, ( a many to many relationship) we need a third table to hold these. The three tables are: venues - holds all five venueseventtypes - holds all three event typesevents - holds the date plus venue id plus event type id. I also added a description field for this event eg Jims Birthday. The first two tables hold the data types so venues have names alpha to echo. Ive added an integer id as well and created an index for that. With the small numbers of venues (5) and event types (3), it could be done without an index, but with larger tables, it will get very slow. So any column that is likely to be searched on, add an index, preferably integer The SQL to create this is: The index on the events table has date, id-event, the event type, and venue. That means we can query the event table for all events on a date, all events at a venue,all parties etc and combinations of those such as all parties at a venue etc. After running the SQL create table queries, the three tables are created. Note Ive put all that sql in the text file create.sql and it includes data for populating some of the three tables. If you put ; on the end of the lines as Ive done in create.sql then you can batch and execute all the commands in one go. Without the ; you have to run each one by itself. In SQLiteSpy, just click F9 to run everything. Ive also included sql to drop all three tables inside multi-line comments using /* .. */ same as in C. Just select the three lines and do ctrl F9 to execute the selected text. These commands insert  the five venues: Again Ive included commented out text to empty tables, with the delete from lines. Theres no undo so be careful with these! Amazingly, with all the data loaded (admittedly not much) the entire database file on disk is only 7KB. Event Data Rather than build up a bunch of ten insert statements, I used Excel to create a .csv file for the event data and then used the SQLite3 command line utility (that comes with SQLite) and the following commands to import it. Note: Any line with a period (.) prefix is a command. Use .help to view all commands. To run SQL just type it in with no period prefix. You have to use double blackslashes \\ in the import path for each folder. Only do the last line after the .import has succeeded. When SQLite3 runs the default separator is a : so it has to be changed to a comma before the import. Back to the Code Now we have a fully populated database, lets write the C code to run this SQL query which returns a list of parties, with description, dates and venues. New to SQL? Read What is SQL? This does a join using the idvenue column between the events and venues table so we get the name of the venue not its int idvenue value. SQLite C API Functions There are many functions but we only need a handful. The order of processing is: Open database with sqlite3_open(), exit if have error opening it.Prepare the SQL with sqlite3_prepare()Loop using slqite3_step() until no more records(In the loop) process each column with sqlite3_column...Finally call sqlite3_close(db) Theres an optional step after calling sqlite3_prepare where any passed in parameters are bound but well save that for a future tutorial. So in the program listed below the pseudo code for the major steps are: The sql returns three values so if sqlite3.step() SQLITE_ROW then the values are copied from the appropriate column types. Ive used int and text. I display the date as a number but feel free to convert it to a date.​ Listing of Example Code