Sqlite
Introduction
The page on the SQLite in the Seed Reference Manual is pretty much all you need to do for this module:
http://library.gnome.org/devel/seed/stable/sqlite-module.html
I thought I'd expand it a bit with a few more examples.
Create a database
As always, the sqlite module needs to be imported. This is done by
sqlite = imports.sqlite;
The next thing is to open the database. The following command opens the sqlite database music_collection.db. If it doesn't exist, then it is created. Once this is done, a sqlite.Database object called db.
var db = new sqlite.Database("music_collection.db");
Status
To check the status after an operation has occurred, Sqlite enums can be used. For example to check the status of opening a database which was done in the last section, enter the following commands:
if (db.status == sqlite.OK)
print ("Opened Ok");
else if (db.status == sqlite.ERROR)
print ("Error opening");
else
print ("Something else happended");The status of this action is checked by using the db.status function. In the below example, the function is used to see if it equals either the sqlite.OK or sqlite.ERROR enums and displays the appropriate object.
With the examples I have used, the sqlite.OK has already been returned except for a Sqlite exception thrown when for example an incorrect sql command was entered in db.exec or to create a table that already exists. I imagine this is behaving correctly or else it would be better if the exception was caught and a sqlite.ERROR was produced (nothing is returned when an exception is thrown). As it works, can it be defined as a bug or a nice to have feature?
Tables – a bit of background
With sqlite and any other database software, once a database is created, it is populated with tables. Tables are ways of storing data in which they are easily organised to find. They are also used so that they data is stored more efficiently. The reason for this is dependent on the relationship of some data to another. One item of data can have a one to one relationship with another item of data (i.e. data item 'a' can only have only a data item 'b'). This is different from an item of data having a one to many relationship with many items of data (i.e. data item 'a' can have data item 'c', data item 'd' and data item 'e'). All this information could be put into one table but it would be very large and slow to extract.
To give an example of how tables work, imagine an employee database containing details of a companies employees. The database has the following tables:
Employee – contains the details of all employees in the company Factory – contains the details of the factory sites the company has Grade-scales – contains the grade scales that an employee can have Skills – contains skill sets an employee has.
To get information, queries are run that extracts information depending on certain criteria. To get information from different tables they need to be joined via key fields which are unique identifiers.
The joining of tables is where the one-to-one and one-to-many relationships come in. Using the above example, the Employee table would have a one-to-one relationship with the Factory table as an employee can only work in one factory. The Employee table has a one-to-many relationship with the Factory, Grade-scales and Skills tables as an employee will work in a factory, have a Grade skill and have one or more skills. It has to noted that some tables can't be joined to another as they don't have a relationship ie a Factory would not have a Grade Scale.
Each table in then organised into a number of columns which contains items of data specific to it. Using the above example, the Employee table would have the following columns which is specific details of all employees:
Employee ID (key field as each employee would have a unique one) name address age.
Creating a table and Populating it
The following Seed JavaScript program populates the database.
1 #!/usr/bin/env seed
2 var sqlite = imports.sqlite;
3 var db = new sqlite.Database("music_collection.db");
4
5 var music_data = [{artist : "Saxon", album: "Wheels of Steel", popularity: 20},
6 {artist : "Iron Maiden", album: "Iron Maiden", popularity: 17},
7 {artist : "Judas Priest", album: "Live In Concert", popularity: 20},
8 {artist : "Saxon", album: "Into the Labyrinth", popularity: 23},
9 {artist : "Runrig", album: "Once In A Lifetime", popularity: 14},
10 {artist : "Simple Minds", album: "Got Live If You Want It", popularity: 4}
11 ];
12
13 try{
14 db.exec("create table music (key INTEGER PRIMARY KEY, artist TEXT, album TEXT, popularity INTEGER);");
15 }
16 catch(e){
17 print("Error message:" + e.name + " type:" + e.message);
18 }
19
20 for (var x = 0; x < music_data.length; x ++) {
21 db_str = "insert into music(artist, album, popularity) values('" + music_data[x].artist + "', '" + music_data[x].album + "', " + music_data[x].popularity + ");";
22 db.exec(db_str);
23 }
24 db.close();
The following lines creates the database as mentioned in previous section:
var sqlite = imports.sqlite;
var db = new sqlite.Database("music_collection.db");The following lines creates an Array of objects which has fields artist, album and popularity. These correspond to the columns of the database. I intend to do a tutorial of objects later so a description of them is out of scope for this one.
var music_data = [{artist : "Saxon", album: "Wheels of Steel", popularity: 20},
{artist : "Iron Maiden", album: "Iron Maiden", popularity: 17},
{artist : "Judas Priest", album: "Live In Concert", popularity: 20},
{artist : "Saxon", album: "Into the Labyrinth", popularity: 23},
{artist : "Runrig", album: "Once In A Lifetime", popularity: 14},
{artist : "Simple Minds", album: "Got Live If You Want It", popularity: 4}
];To execute a command on Sqlite, the sqlite.exec (in the below comamnd db.exec is the database object name which was created when opening database) command is used. This executes the SQlite query that is contained in the string. As mentioned before, an exception is thrown if an incorrect Sql command is executed or in this case a table is created when it already exists.
The exception is caught by wrapping the db.exec with the try and catch commands. Exception handling is described in the Seed Standalone Tutorial at the following link.
http://people.gnome.org/~racarr/seed/tutorial-standalone/tutorial.html
The string inside the db.exec contains the SQL command create table. It creates a table called music with the following columns:
Key – The primary key field used as an unique identifier for each record artist – Artist name album – Name of Album popularity – Number of times album has been played.
try{
db.exec("create table music (key INTEGER PRIMARY KEY, artist TEXT, album TEXT, popularity INTEGER);");
}
catch(e){
print("Error message:" + e.name + " type:" + e.message);
}Now that the table has been created, it is populated with the array of objects created before.
The insert into command is used to insert the data into the music table. You have to specify the columns in database to where the data will be inserted. If you try to insert the wrong type of data to database column i.e. a string value to a integer database column, an exception is thrown. This is the reason why I named the fields of the object to the columns of the database
for (var x = 0; x < music_data.length; x ++) {
db_str = "insert into music(artist, album, popularity) values('" + music_data[x].artist + "', '" + music_data[x].album + "', " + music_data[x].popularity + ");";
db.exec(db_str);
}To close the database:
db.close();
Viewing the database via Sqlite command line interface:
To view the contents of the database without writing JavaScript code can be done via the Sqlite command line interface program. This can also be used to write SQL commands for use in the JS program. I personally find it easier to do this especially with more complex ones. Please note that Seed uses SQlite version 3.
Run the Sqlite command line interface with the following command (instructions on installation are outside this tutorial):
sqlite3
e.g. using the database created with the above program:
sqlite3 music music_collection.db
The command prompt will appear. To view the tables that exist in the database, enter the following command:
select * from sqlite_master;
As music is the only table is this database, it will only be displayed. To display the contents of the database, enter the following command:
select * from music;
The is called a query and can be quite complex depending on number of tables in the database and the criteria required. As this is only a brief tutorial on Sqlite in the context of Seed, other tutorials are better suited to explain how to do this. I will briefly describe it in next section.
To exit out of the command line interface, hold down ctrl key and press d.
Running queries on table
The following program runs queries on music database:
1 var sqlite = imports.sqlite;
2 var db = new sqlite.Database("music_collection.db");
3
4 function cb_results(results) {print ("\nResults of average popularity");
5 db.exec("select avg(popularity) as result from music", cb_numeric_results);
6
7 print ("\nResults of maximum popularity");
8 db.exec("select max(popularity) as result from music", cb_numeric_results);
9
10 print ("\nResults of minimum popularity");
11 db.exec("select min(popularity) as result from music", cb_numeric_results);
12 print(results.artist + ":" + results.album + ":" + results.popularity);
13 }
14
15 function cb_numeric_results(results) {
16 print ("Results=" + results.result);
17 }
18
19 print ("\nResults of records with artist Saxon");
20 db.exec("select * from music where artist='Saxon';", cb_results);
21
22 print ("\nResults of records where album has Live in title");
23 db.exec("select * from music where album like '%live%';", cb_results);
24
25 print ("\nResults of records where album has In in title");
26 db.exec("select * from music where album like '%in%';", cb_results);
27
28 print ("\nResults of records where album has In at begining of title");
29 db.exec("select * from music where album like 'in%';", cb_results);
30
31 print ("\nResults of average popularity");
32 db.exec("select avg(popularity) as result from music", cb_numeric_results);
33
34 print ("\nResults of maximum popularity");
35 db.exec("select max(popularity) as result from music", cb_numeric_results);
36
37 print ("\nResults of minimum popularity");
38 db.exec("select min(popularity) as result from music", cb_numeric_results);
39
40 db.close();
The first two lines are self-evident:
var sqlite = imports.sqlite;
var db = new sqlite.Database("music_collection.db");The following functions are required as callback for when the sql query command is run later via the sqlite.exec command (db.exec being the database object created when opening the database). Callback functions where touched on in an earlier GTK tutorials and serves the same purpose.
In my program, I have created two callback functions because each one is displaying different information. The first one is displaying all the columns in the database and the other name is displaying only one column.
the .exec function passes an object containing the results from the sql query to the callback function. I have called the object results. Inside the objects, contains fields which contains the information from the column results. In the first one, cb_results, contains the artist, album and popularity. The second one, cb_numeric_results, contains the field result. You will notice that this isn't a column in the database table. I will explain later.
function cb_results(results) {
print(results.artist + ":" + results.album + ":" + results.popularity);
}
function cb_numeric_results(results) {
print ("Results=" + results.result);
}The following command runs a query on all artists that equals Saxon. This is done from the select SQL command. A query command is split up into the following parts
Select from where
The select part specifies the table columns to be displayed. The examples I have used is * which is all but individuals columns can be specified. The from part specifies that table to be used, in this example music where specifies the criteria to be used. This criteria is used to specify certain criteria i.e. if a column of a record equals a certain value. In the below example, the artist column must equal the text Saxon. As mentioned in the section about the theory of database tables, if there is more than one table in a database, they can be joined. This is where they would be joined i.e. the keyfield of one table equals the keyfield of another table.
db.exec("select * from music where artist='Saxon';", cb_results);In this query, all records that has the text 'live' in the album column will be displayed. Please note that % is the wildcard character used in SQL.
db.exec("select * from music where album like '%live%';", cb_results);In this query, all records with text 'in' in the album field is displayed:
db.exec("select * from music where album like '%in%';", cb_results);In this query, all records with text 'in' at beginning of album column is displayed. You will note that the difference in results from previous query is down to the wildcard or lack of it.
db.exec("select * from music where album like 'in%';", cb_results);Hopefully this should give a wee taster of how to use sql queries. To get further examples, it is best to google for SQL tutorials and use them in conjunction with the sqlite command line interface.
The last 3 commands runs queries to perform mathematical functions on the results of queries from the database. The below are avg being average value, max being maximum value and min being minimum value. For obvious reasons, these can only be used on table columns that have numeric fields.
I mentioned earlier when describing the callback functions that the second one, cb_numeric_results, had a column name called result which wasn't defined when the table was created. It wasn't created then by 'created' now.
When running the below .exec commands, the object passes an object which contains the results from the sql query. In these objects, it was called results.avg(popularity) for the first one, max(popularity) for the second one and min(popularity) for the last one. This could have been written in the callback function to allow for this but it would be easier if they were all called one name. This is done by the as function for example in the first query
select avg(popularity) as result
Sets the column header as result. The name does not matter but obviously this needs to match that in the callback function.
db.exec("select avg(popularity) as result from music", cb_numeric_results);
db.exec("select max(popularity) as result from music", cb_numeric_results);
db.exec("select min(popularity) as result from music", cb_numeric_results);
Updating/deleting records in table:
The following program shows how records in tables can be either updated or deleted.
1 sqlite = imports.sqlite;
2 var db = new sqlite.Database("music_collection.db");
3
4 function cb_results(results) {
5 print(results.artist + ":" + results.album + ":" + results.popularity);
6 }
7
8 print ("\nResults of music collection");
9 db.exec("select * from music;", cb_results);
10
11 // Update database command
12 db.exec("update music SET artist = 'SAXON' where artist = 'Saxon'");
13
14 print ("\nResults After record updated (artist Saxon turned to Upper case)");
15 db.exec("select * from music ;", cb_results);
16
17 // Delete record
18 db.exec("delete from music where album like '%Wheel%'");
19
20 print("\nResults of records where record deleted (Album with word Wheel in it)");
21 db.exec("select * from music;", cb_results);
22 db.close()
The command to update a record in a table is update set sql command. The format of the command is as follows:
Update Set where
The line Update specifies which table to update, in this instance music The line Set is the columns in the table to be updated with new values, in this instance the artist column with SAXON. Multiple columns can be updated. The line where is the same as the query command which specifies the criteria of the record(s) to be updated, in this case, every record that has artist column equal to Saxon.
db.exec("update music SET artist = 'SAXON' where artist = 'Saxon'");The command to delete a record in a table is the delete sql command. The format of the command is as follows:
delete from where
The line delete from specifies which table to delete record(s) on, in this case, the music table. The line where is the same as the query command which specifies the criteria of the record(s) to be deleted, in the case any record with the text 'Wheel' in the album column.
db.exec("delete from music where album like '%Wheel%'");