db_sqlite_examples.C

Examples of use of KVSQLite interface to sqlite databases

To execute this function, do

$ kaliveda
kaliveda[0] .L db_sqlite_examples.C+
kaliveda[1] sqlite_example()

#include "KVConfig.h"
#include "TSystem.h"
#ifdef WITH_RSQLITE
#include "SQLiteDB.h"
 
void create_database()
{
   gSystem->Unlink("example.sqlite");
 
   // creating a database
   KVSQLite::database db("example.sqlite");
 
   // Creating a simple table with a liste of people:
   //~~~
   // CREATE TABLE assignees (
   //    id INTEGER
   //    name TEXT NOT NULL
   //    PRIMARY KEY(id)
   // );
   KVSQLite::table _assignees("assignees");
   _assignees.add_column("id", KVSQLite::column_type::INTEGER);
   _assignees.primary_key("id");
   _assignees.add_column("name", KVSQLite::column_type::TEXT).NOT_NULL();
 
   auto& assignees = db.add_table(_assignees);
 
   // add some data
   // Note that as 'id' is declared `PRIMARY KEY` and is of `INTEGER` type,
   // it is automatically filled with incremented values for each row.
   assignees.prepare_insert_single_row();
   assignees["name"] = "Sammy";
   assignees.insert_single_row();
   assignees["name"] = "Jo";
   assignees.insert_single_row();
   assignees["name"] = "Charlie";
   assignees.insert_single_row();
   assignees["name"] = "Ashley";
   assignees.insert_single_row();
 
   // print data in table
   db.print_selected_data("assignees");
 
   // Creating another simple table:
   //~~~
   //   CREATE TABLE lists (
   //       id INTEGER,
   //       created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   //       title TEXT NOT NULL,
   //       PRIMARY KEY(id)
   //   );
   //~~~
 
   KVSQLite::table _lists("lists");
   _lists.add_column("id", KVSQLite::column_type::INTEGER);
   _lists.add_column("created", KVSQLite::column_type::TIMESTAMP)
   .NOT_NULL()
   .DEFAULT(KVSQLite::column::default_time::CURRENT_TIMESTAMP);
   _lists.add_column("title", KVSQLite::KVSQLite_column_type::TEXT).NOT_NULL();
   _lists.primary_key("id");
 
   auto& lists = db.add_table(_lists);
 
   // insert some data
   //
   // Here we use a more efficient bulk insertion operation
   // The "selected columns" are the ones which effectively contain data for each row,
   // i.e. the `title` column in this example
   lists.prepare_insert_single_row();
   lists["title"] = "Work";
   db.prepare_data_insertion_selected_columns("lists");
   db.insert_data_row_selected_columns();
   lists["title"] = "Home";
   db.insert_data_row_selected_columns();
   lists["title"] = "Study";
   db.insert_data_row_selected_columns();
   db.end_data_insertion();
 
   // print data in table
   db.print_selected_data("lists");
 
   // Create a table which implements a one-to-many relationship with a `FOREIGN KEY` constraint
   //
   // The `list_id` must exist for each item, and correspond to a value in the `id` column of the `lists`
   // table. Then each item is associated to 1 and only 1 list, but each list can be associated to many items
   //~~~
   //   CREATE TABLE items (
   //       id INTEGER PRIMARY KEY,
   //       list_id INTEGER NOT NULL,
   //       created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   //       content TEXT NOT NULL,
   //       done INTEGER NOT NULL DEFAULT 0,
   //       FOREIGN KEY (list_id) REFERENCES lists (id)
   //   );
   //~~~
 
   KVSQLite::table _items("items");
   _items.add_column("id", KVSQLite::column_type::INTEGER);
   _items.primary_key("id");
   _items.add_column("list_id", KVSQLite::column_type::INTEGER).NOT_NULL();
   _items.foreign_key("list_id", "lists", "id");
   _items.add_column("created", KVSQLite::column_type::TIMESTAMP)
   .NOT_NULL()
   .DEFAULT(KVSQLite::column::default_time::CURRENT_TIMESTAMP);
   _items.add_column("content", KVSQLite::column_type::TEXT).NOT_NULL();
   _items.add_column("done", KVSQLite::column_type::INTEGER).NOT_NULL().DEFAULT(0);
 
   auto& items = db.add_table(_items);
 
   // insert some data
   items.prepare_insert_single_row();
   items["list_id"] = 1;
   items["content"] = "Morning meeting";
   items.insert_single_row();
   items.prepare_insert_single_row();
   items["list_id"] = 2;
   items["content"] = "Buy fruit";
   items.insert_single_row();
   items.prepare_insert_single_row();
   items["list_id"] = 2;
   items["content"] = "Cook dinner";
   items.insert_single_row();
   items.prepare_insert_single_row();
   items["list_id"] = 3;
   items["content"] = "Learn C++";
   items.insert_single_row();
   items.prepare_insert_single_row();
   items["list_id"] = 3;
   items["content"] = "Learn SQLite";
   items.insert_single_row();
 
   // print data in table
   db.print_selected_data("items");
 
   // demonstrate automatic 'INNER JOIN' between tables with a foreign key
   db.print_selected_data("lists,items", "title,content");
   // this is equivalent to:
   //
   //~~~
   //   SELECT title,
   //          content
   //   FROM lists
   //   INNER JOIN items ON items.list_id = lists.id
   //~~~
 
   // INNER JOIN with 3 tables
   KVSQLite::table _artists("artists");
   _artists.add_column("id", "INTEGER");
   _artists.add_column("ArtistName", "TEXT").NOT_NULL().UNIQUE();
   _artists.primary_key("id");
   auto& artists = db.add_table(_artists);
 
   artists.prepare_insert_single_row();
   artists["ArtistName"] = "David Bowie"; // => id=1
   artists.insert_single_row();
   artists["ArtistName"] = "Kraftwerk"; // => id=2
   artists.insert_single_row();
   artists["ArtistName"] = "Rod Stewart"; // => id=3
   artists.insert_single_row();
 
   KVSQLite::table _albums("albums");
   _albums.add_column("id", "INTEGER");
   _albums.add_column("AlbumTitle", "TEXT").NOT_NULL().UNIQUE();
   _albums.add_column("Year", "INTEGER").NOT_NULL();
   _albums.add_column("artist_id", "INTEGER");
   _albums.primary_key("id");
   _albums.foreign_key("artist_id", "artists", "id");
   auto& albums = db.add_table(_albums);
 
   albums.prepare_insert_single_row();
   albums["AlbumTitle"] = "Hunky Dory"; // => id=1
   albums["Year"] = 1971;
   albums["artist_id"] = 1;
   albums.insert_single_row();
   albums["AlbumTitle"] = "Every Picture Tells A Story"; // => id=2
   albums["Year"] = 1971;
   albums["artist_id"] = 3;
   albums.insert_single_row();
   albums["AlbumTitle"] = "Autobahn"; // => id=3
   albums["Year"] = 1974;
   albums["artist_id"] = 2;
   albums.insert_single_row();
 
   KVSQLite::table _tracks("tracks");
   _tracks.add_column("id", "INTEGER");
   _tracks.add_column("SongTitle", "TEXT").NOT_NULL();
   _tracks.add_column("album_id", "INTEGER");
   _tracks.primary_key("id");
   _tracks.foreign_key("album_id", "albums", "id");
   auto& tracks = db.add_table(_tracks);
 
   tracks.prepare_insert_single_row();
   tracks["SongTitle"] = "The Bewlay Brothers";
   tracks["album_id"] = 1;
   tracks.insert_single_row();
   tracks["SongTitle"] = "Maggie May";
   tracks["album_id"] = 2;
   tracks.insert_single_row();
   tracks["SongTitle"] = "Mitternacht";
   tracks["album_id"] = 3;
   tracks.insert_single_row();
   tracks["SongTitle"] = "Life On Mars";
   tracks["album_id"] = 1;
   tracks.insert_single_row();
   tracks["SongTitle"] = "Mandolin Wind";
   tracks["album_id"] = 2;
   tracks.insert_single_row();
   tracks["SongTitle"] = "Changes";
   tracks["album_id"] = 1;
   tracks.insert_single_row();
 
   db.print_selected_data("artists,tracks,albums", "ArtistName,AlbumTitle,SongTitle", "Year > 1972");
}
 
void use_database()
{
   if (gSystem->AccessPathName("example.sqlite")) {
      std::cerr << "Create the database first by calling create_database()!\n";
      return;
   }
 
   // open existing database
   KVSQLite::database db("example.sqlite");
 
   // show tables in database
   db.show_tables();
 
   // print data in table 'assignees'
   db.print_selected_data("assignees");
 
   // print data in table 'lists'
   db.print_selected_data("lists");
 
   // print data in table 'items'
   db.print_selected_data("items");
 
   // demonstrate automatic 'INNER JOIN' between tables with a foreign key
   db.print_selected_data("lists,items", "title,content");
 
   // INNER JOIN with 3 tables
   db.print_selected_data("artists,tracks,albums", "ArtistName,Year,SongTitle", "AlbumTitle = 'Hunky Dory'");
 
   db["artists"].prepare_insert_single_row();
   db["artists"]["ArtistName"] = "Brian Eno";
   db["artists"].insert_single_row();
   db["albums"].prepare_insert_single_row();
   db["albums"]["AlbumTitle"] = "Here Come The Warm Jets";
   db["albums"]["Year"] = 1974;
   db["albums"]["artist_id"] = 4;
   db["albums"].insert_single_row();
   db["tracks"].prepare_insert_single_row();
   db["tracks"]["SongTitle"] = "Baby's On Fire";
   db["tracks"]["album_id"] = 4;
   db["tracks"].insert_single_row();
 
   db.print_selected_data("artists,tracks,albums", "SongTitle,AlbumTitle,ArtistName", "Year > 1972");
 
   // make sure 'count' gives right answer for joined tables
   std::cout << "Number of results in previous query = "
             << db.count("artists,tracks,albums", "*", "Year > 1972")
             << "?\n";
 
   // display number of tracks per album
   db.select_data("tracks,albums,artists", "AlbumTitle,ArtistName,COUNT(*)", "", false, "GROUP BY album_id");
   while (db.get_next_result()) {
      std::cout << "The album " << db["albums"]["AlbumTitle"].get_data<TString>() << " by " << db["artists"]["ArtistName"].get_data<TString>()
                << " contains " << db.get_count_column_value() << " tracks\n";
   }
}
#endif