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, typically finishing off with .db. Each table resembles a spreadsheet, it comprises of various sections and each line has values. In the event that it helps, think about each line similar to a struct, with the segments in the table comparing to theâ fields in the struct. A table can have the same number of columns as will fit on a circle. There is a maximum breaking point yet its tremendous 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 sections. 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 begin 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 forward, we execute the SQL. Lets have a slight diversion 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 happen at five settings (alpha, beta, charlie, delta, and reverberation). At the point when you are demonstrating 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. In the event that you put the dates in a spreadsheet, at that point design 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 likely be worthy. Anyway great database configuration practice requires some standardization. Exceptional information things like scene 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 various scenes, ( a numerous to numerous relationship) we need a third table to hold these. The three tables are: scenes - holds each of the five venueseventtypes - holds every one of the three occasion typesevents - holds the date in addition to setting id in addition to occasion type id. I additionally included a portrayal 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 file for that. With the little quantities of scenes (5) and occasion types (3), it should be possible without a list, yet with bigger tables, it will get exceptionally moderate. So any section that is probably going to be looked on, include a record, ideally whole number The SQL to make this is: The list on the occasions table has date, id-occasion, the occasion type, and scene. That implies we can inquiry 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 questions, the three tables are made. Note Ive put all that sql in the content record create.sql and it incorporates information for populating a portion of the three tables. On the off chance 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 likewise included sql to drop each 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 settings: Again Ive included remarked out content to discharge tables, with the erase from lines. Theres no fix so be cautious with these! Incredibly, with all the information stacked (as a matter of fact very little) the whole database record on circle is just 7KB. Occasion Data As opposed to develop a lot of ten supplement explanations, I utilized Excel to make a .csv record 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 envelope. 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 question which restores a rundown of gatherings, with portrayal, dates and settings. New to SQL? Peruse What is SQL? This does a join utilizing the idvenue segment 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 yet we just need a bunch. The request for preparing 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 segment with sqlite3_column...Finally call sqlite3_close(db) Theres a discretionary advance subsequent to calling sqlite3_prepare where any went in boundaries are bound yet 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 on the off chance 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 however don't hesitate to change over it to a date.​ Posting of Example Code