Small Basic - Database Extension

Over the years a few people have written simple databases in Small Basic saving data in a text file, then performing sorts or other operations on Small Basic arrays of data.

There were a few recent questions on databases and sorting arrays as well as a challenge question on the Small Basic forum.

Here is an example, import ZTC919-1, by Jibba Jabba contributed for this month's challenges (some auto commented File commands need to be un-commented after importing).

And another demonstrating sorts and searches, import CDC823.

This is a great way to learn, but in the real world people usually use commercial databases, which are controlled by a special language, usually SQL (Structured Query Language). 

SQL statements then perform either COMMANDS to modify a database or QUERIES to do searches in the database, returning results.

Learning how to use SQL is a great skill to learn if you are interested in using databases in your programs.  SQL is used by creating a text variable that is passed to the database to perform a COMMAND or QUERY, so most of the work is learning how to create these text commands to do what you want.

In the LitDev extension I created a simple interface to SQLite.  SQLite is a small implementation of an SQL database, where the database is stored in a single file with no special installation or other pre-requisites.  The LitDev extension object for using SQLite is called LDDataBase and has only 4 methods making it quite simple to use.

LDDataBase
SQLite Database with ListView control (see https://zetcode.com/db/sqlite/ for SQLite commands).

AddDataBase(fileName)
Create or open a database.
This must be called before any SQL methods.
When a table is created it must have first column as "Id INTEGER PRIMARY KEY".
fileName The full path to the database file (usually with extension db).
returns A label to identify the database.

AddListView(width,height)
Add a ListView to view database.
width The width of the ListView.
height The height of the ListView.
returns The ListView control.

Command(database,command)
Perform an SQLite command (not a query) on a database.
database The existing database label (see AddDataBase).
command The SQLite Command.
returns The number of rows updated.

Query(database,query,listview,getRecords)
Perform an SQLite Query (not a command) on a database.
database The existing database label (see AddDataBase).
query The SQLite Query.
Example "SELECT * FROM myTable; ".
listview A ListView to populate with the query result or "" for none.
getRecords Optionally return an array of results ("True" or "False").
Remember large multi-dimensional arrays in Small Basic are slow.
returns Optional array of results or "".

A simple example comes with the LitDev extension, located in folder other-samples/LDDataBase.sb in the zip download and is based on the samples on the SQLite website.  This is an excellent website and is a great place to start using SQL databases; there are many other good SQL resources and tutorials, for example techonthenet.

The following is a exert from this example.

'Set a database file - it is created if it doesn't exist yet

datFile `` = ``Program``.``Directory``+``"\database.db"

database `` = ``LDDataBase``.``AddDataBase``(``datFile``)

 

'Create a listview control to view the table data

GraphicsWindow``.``Show``(``)

listView `` = ``LDDataBase``.``AddListView``(``GraphicsWindow``.``Width``,``GraphicsWindow``.``Height``)

 

'Create a Cars table

command `` = ``"BEGIN TRANSACTION;"

command `` = `` command `` + ``"DROP TABLE IF EXISTS Cars;"

command `` = `` command `` + ``"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Cost NUM);"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Audi','52642');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Mercedes','57127');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Skoda','9000');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Volvo','29000');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Bentley','350000');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Citroen','21000');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Hummer','41400');"

command `` = `` command `` + ``"INSERT INTO Cars(Name,Cost)VALUES('Volkswagen','21600');"

command `` = `` command `` + ``"COMMIT;"

LDDataBase``.``Command``(``database``,``command``)

 

'Some SQL commands on this databse

LDDataBase``.``Query``(``database`` , ``"SELECT * FROM Cars;"`` , ``listView`` , ``"False"``)

A ListView is a special control used to display QUERY results like below from the sample.

Using the extension is easy, but mastering SQL is a big challenge.  It is however well worth getting some experience with SQL databases if you are interested in practical computing. 

It is not necessary to learn much SQL to use a database to store and manipulate your data efficiently in Small Basic; the sample contains everything for quite advanced use.  The storage, sorting and querying of the data is then much easier and faster compared to using Small Basic arrays, leaving you free to concentrate on the other features of your program logic.