The executable has two distinct interpreters. The outer interpreter is the one to which all command lines are initially given.
If the first keyword of a command line looks like a database command then the entire line is given to the internal SQLite database machine. These database keywords include:
- ALTER
- ANALYZE
- ATTACH
- BEGIN
- COMMIT
- CREATE
- DELETE
- DETACH
- DROP
- EXPLAIN
- INSERT
- PRAGMA
- REINDEX
- RELEASE
- ROLLBACK
- SAVEPOINT
- SELECT
- UPDATE
- VACUUM
You can mix database command lines with non-database command lines.
A number of custom commands have been created to simplify some database activities. So for example, there is a function dbopen that can be used to create a new database, either in memory or disk backed. Typically such functions are provided when the underlying SQLite API does not have a simple text based interface.
The remainder of this page will cover the creation of a simple database and insert a table with a few rows in to that database.
The menu option shown below will load a mini script.
The script will be loaded in to a read-only text panel, the Help Panel.
See below:
At this point it is difficult to see the text in the Help Panel. But you can change the size of the executable’s main window, and of the relative sizes of the 3 main sub panels.
The following image shows where the window can be resized using the mouse.
(There are some minimum size values! ).
The size of the text font used in the 3 main sub-windows can be changed by the font command, or using a menu option under Options:
Create Simple Table
The script for the Create Simple Table demo is shown below, note that it is a mixture of database and non-database commands:
' just to be safe, and to prevent writing to an existing database,
' check if a database is open
IF dbopen THEN
Message =" A Database is already open, save it and or close it. Then rerun this demo"
stop
end if
' we could use a menu command File/ New Memory DB to create a new database in memory,
' we will use a command
dbopen ("memory")
' the following are pure SQLite commands
' create a table called Family with 4 columns
' refer to the demo via menu Help / SQL / Demos / BLOBs on
' using the column named Pic
create table family ( Role text , Age int, Id TEXT , Pic BLOB )
insert into family values ( 'Father', 42, 0123 , NULL)
insert into family values ( 'Mother', 41, 4.5 , NULL)
insert into family values ( 'Daughter', 4, 000 , NULL)
insert into family values ( 'Son', 14, -1 , NULL)
insert into family (role, age) values ( 'Grandfather', 70)
' finished
Script explanation:
A given line is either a database line or a non-database line. A given line can not really be a mixture.
As mentioned above, the first token of each line is used to decide which interpreter gets the command line.
The first two lines start with the keyword ' which is not a database command, and which is treated by the non-database interpreter as the start of a comment.
' just to be safe, and to prevent writing to an existing database,
' check if a database is open
The next snippet ensures that we do not overwrite an existing database.
IF dbopen THEN
Message =" A Database is already open, save it and or close it. Then rerun this demo"
stop
end if
The command dbopen, if called without parameters, returns TRUE if there is already an open database, else it return FALSE.
The IF expression THEN .. END IF is a standard flow control construct.
The message command is straight forward.
The stop command simply stops execution of a running script and returns control to the user.
The following snippet will create an empty database that resides only in memory, i.e. it is not disk-backed. It is possible to save such a database to disk using the menu option File / Save as or directly using the command dbSaveAs which is not described further here. To get help on such a command enter help(“dbSaveAs”) on the Utility or SQL command => utility field!
' we could use a menu command File/ New Memory DB to create a new database in memory,
' we will use a command
dbopen ("memory")
The command dbOpen, if given parameters, will try to open a new database: either new and empty (memory only or disk-backed), or a memory copy of a disk-backed database, or an open copy of a disk-backed database in which database changes are immediately implemented on to the disk. Use the command help(“dbopen”) to get more information.
I prefer to work on memory copies of a disk based database and then save the memory only database to disk when I am ready. Working directly on a disk based database is usually slower than doing the changes to a memory only copy and then saving to disk: I consider memory based databases to be safer because if you do something wrong to a memory only copy then you have not corrupted the disk database.
Next comes a few comment lines.
' the following are pure SQLite commands
' create a table called Family with 4 columns
' refer to the demo via memu Help / SQL / Demos / BLOBs on
' using the column named Pic
Now we have the SQL database commands.
First create a table inside the currently open database. The table is to be called family and consists of 4 columns. Each column is given a name and a default content type.
create table family ( Role text , Age int, Id TEXT , Pic BLOB )
Short diversion on the use of single and double quotes in SQLite:
In SQLite there are 4 types of token (that I shall discuss) that make up a command line.
1 – Built-in keywords such as: create, table, text, int, BLOB, etc are case-insensitive, and always made from simple alpha characters (A..Z and a..z).
2 – Then there are (what I call) temporary keywords such as the name of a table, and the name of a column. These are also case-insensitive, but can contain weird and wonderful characters such as +#”§ÄÜ and many many many more, they can even contain spaces. So you can create a table with a column named Column 123 . However the interpreter needs to know that the characters belong together. So, any temporary keyword that contains more than just the alpha characters (A..Z and a..z) should be enclosed in a pair of “ e.g. “Column 123”.
3 – The third type of token is a simple text string to be passed as a value to some command. These strings are case-sensitive and should always be enclosed in a pair of ' characters.
4- The forth token is a number. It can be signed, it can be an integer or factional. Numerical values are never enclosed in single or double quotes. If numbers are inside single or double quotes then they do not represent a numerical value, rather they form part of a text string!
The following 5 lines populate the table family with information.
insert into family values ( 'Father', 42, 0123 , NULL)
insert into family values ( 'Mother', 41, 4.5 , NULL)
insert into family values ( 'Daughter', 4, 000 , NULL)
insert into family values ( 'Son', 14, -1 , NULL)
insert into family (role, age) values ( 'Grandfather', 70)
If you have loaded the Create Simple Table demo, either from the menu option described earlier Help / SQL sub-option Demos/ Create Simple Table or entered the text by hand ( via Edit Panel, or your own script file) then execute the script (use the Execute button immediately above the text in whichever Panel you have placed the script).
In this example, I loaded the script using the menu Help / SQL sub-option Demos/ Create Simple Table. And then pressed the Execute button immediately above the script. (Note: it is a green button.).
The script ran, took about 1 second. And the result looks like:
There is a green field in the upper left corner saying Memory this means that database changes are in memory only. If the changes were to go immediately to disk then this field would be light red!
To the right of the green field is the name of the disk file on which the database is based, in this case it is given as :memory: , there is no disk base.
The first database opened in a SQLite session is always called main. The first database can be memory only or disk-backed. Additional databases can be attached but they are always given a unique name and must be disk-backed!
Inside the main database that we have created in the demo, there should be a table called family. There are 2 ways to see the table.
The command way.
Enter a database query command, for example:
select * from family
You can either treat this line as part of a script, and enter it via the Edit Panel, or as a script file. But to minimise complexity, in this case, we can simply type it directly in to the the Utility or SQL command => utility field! Then press the Enter or the carriage return key, or click on the grey Utility or SQL command => button.
The executable will direct the results of a database query to the rows and columns in the lower half of the window. If the query results are too many or too wide for the panel then there will be horizontal / vertical scroll bars to allow you to view other parts of the query response.
The GUI way (mouse only, no typing).
Left mouse click anywhere on the row indicated by the red arrow in the following image.
This will cause the executable to find the names of the user defined tables in the main database and display information about them. The information will be written in to the panel with the red arrow!
The panel with the red arrow shows the names of all the tables in the main database. In this case there is only the table family.
Left mouse click anywhere on the row indicated by the red arrow, the family row, and the executable will generate a database select query for that table. The result is shown below:
By all means, play around with the demos. If you want to, then copy them to the Edit Panel either by copy and paste, or use the Copy>Edit button (above the demo Help Panel).
If you want to clean out the database and start again, then either enter the command dbclose or use the menu option File / Close DB
For safety, both options will cause a pop-up window to appear for you to confirm the close.