Programming SQLite in C Tutorial Two

Programming SQLite in C Tutorial Two This instructional exercise is the second in an arrangement on programming SQLite in C. SQLite stores an assortment of tables in a solitary document database, normally finishing off with .db. Each table resembles a spreadsheet, it comprises of various segments and each column has values. In the event that it helps, think about each line just like a struct, with the segments in the table comparing to theâ fields in the struct. A table can have the same number of lines as will fit on a circle. There is a maximum breaking point yet its immense 18,446,744,073,709,551,616 to be exact. A table can have up to 2,000 segments or on the off chance that you recompile the source, you can max it to a wonderful 32,767 segments. The SQLite API To utilize SQLite, we have to make calls to the API. You can discover a prologue to this API on the official Introduction to SQLite C/C Interface site page. Its an assortment of capacities and simple to utilize. To start with, we need a handle to the database. This is of type sqlite3 and is returned by a call to sqlite3_open( filename, **ppDB). From that point onward, we execute the SQL. Lets have a slight deviation first however and make a usable database and a few tables utilizing SQLiteSpy. (See the past instructional exercise for connections to that and the SQLite Database Browser). Occasions and Venues The database about.DB will hold three tables to oversee occasions at a few scenes. These occasions will be gatherings, discos, and shows and will occur at five scenes (alpha, beta, charlie, delta, and reverberation). At the point when you are displaying something like this, it regularly assists with beginning with a spreadsheet. For simplicities purpose, Ill simply store a date not a period. The spreadsheet has three segments: Dates, Venue, Event Type and around ten occasions this way. Dates run from 21st to 30th of June 2013. Presently SQLite has no unequivocal date type, so its simpler and quicker to store it as an int and a similar way that Excel utilizes dates (days since Jan 1, 1900) have int values 41446 to 41455. On the off chance that you put the dates in a spreadsheet, at that point position the date section as a number with 0 decimal spots, it looks something like this: Presently we could store this information in one table and for such a straightforward model, it would presumably be worthy. Anyway great database configuration practice requires some standardization. One of a kind information things like setting type ought to be in its own table and the occasion types (party and so forth) ought to likewise be in one. At last, as we can have different occasion types at numerous scenes, ( a numerous to numerous relationship) we need a third table to hold these. The three tables are: scenes - holds every one of the five venueseventtypes - holds each of the three occasion typesevents - holds the date in addition to setting id in addition to occasion type id. I likewise included a depiction field for this occasion eg Jims Birthday. The initial two tables hold the information types so settings have names alpha to reverberate. Ive included a whole number id too and made a list for that. With the little quantities of scenes (5) and occasion types (3), it should be possible without a record, however with bigger tables, it will get moderate. So any segment that is probably going to be looked on, include a list, ideally number The SQL to make this is: The file on the occasions table has date, id-occasion, the occasion type, and setting. That implies we can question the occasion table for all occasions out on the town, all occasions at a venue,all parties and so on and blends of those, for example, all gatherings at a setting and so on. In the wake of running the SQL make table inquiries, the three tables are made. Note Ive put all that sql in the content document create.sql and it incorporates information for populating a portion of the three tables. In the event that you put ; on the finish of the lines as Ive done in create.sql then you can bunch and execute all the orders in one go. Without the ; you need to show every one to itself. In SQLiteSpy, simply click F9 to run everything. Ive additionally included sql to drop every one of the three tables inside multi-line remarks utilizing/* .. */same as in C. Simply select the three lines and do ctrl F9 to execute the chose text. These orders insertâ the five scenes: Again Ive included remarked out content to purge tables, with the erase from lines. Theres no fix so be cautious with these! Incredibly, with all the information stacked (truly very little) the whole database document on circle is just 7KB. Occasion Data Instead of develop a lot of ten addition proclamations, I utilized Excel to make a .csv document for the occasion information and afterward utilized the SQLite3 order line utility (that accompanies SQLite) and the accompanying orders to import it. Note: Any line with a period (.) prefix is an order. Use .help to see all orders. To run SQL simply type it in with no period prefix. You need to utilize twofold blackslashes \ in the import way for every organizer. Just do the last line after the .import has succeeded. When SQLite3 runs the default separator is a : so it must be changed to a comma before the import. Back to the Code Presently we have a completely populated database, lets compose the C code to run this SQL inquiry which restores a rundown of gatherings, with portrayal, dates and scenes. New to SQL? Peruse What is SQL? This does a join utilizing the idvenue section between the occasions and scenes table so we get the name of the setting not its int idvenue esteem. SQLite C API Functions There are numerous capacities however we just need a bunch. The request for handling is: Open database with sqlite3_open(), exit if have blunder opening it.Prepare the SQL with sqlite3_prepare()Loop utilizing slqite3_step() until no more records(In the circle) process every section with sqlite3_column...Finally call sqlite3_close(db) Theres a discretionary advance in the wake of calling sqlite3_prepare where any went in boundaries are bound however well spare that for a future instructional exercise. So in the program recorded beneath the pseudo code for the significant advances are: The sql returns three qualities so in the event that sqlite3.step() SQLITE_ROW then the qualities are duplicated from the fitting segment types. Ive utilized int and text. I show the date as a number yet don't hesitate to change over it to a date.​ Posting of Example Code