sql

SQLite is a public domain C library that implements an SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk.

All information is written to disk as strings. This means that in a CREATE TABLE statement you do not have to give the kind of data you will be putting in the fields. Another thing is that SQLite uses records of variable length. So no need to give fieldlengths. All tables (datafiles) and indexes in a database are all in 1 physical file on disk.

This is a first version (v1.4) of a Liberty Basic user interface to test the SQLite DLL and to learn SQL. Colin McMurchie wrote the API functions to access the DLL and provided us with a program to test them. I used some of his functions as a base for LibSQL.

For the SQL syntax understood by SQLite see the SQLite website:

[http://www.hwaci.com/sw/sqlite/lang.html]

For the time being LibSQL only supports part of SQLite (see below). On the other hand I added some (I hope) useful functions. This is not a SQL course. There are many sources of information available on the subject in written form and on the internet. If there is enough interest in the subject I can eventually create a tutorial for a future newsletter.

How to get started

1. Create a folder to contain the program, the DLL and your (future) database(s).

2. Download the SQLite documentation from the above mentioned website.

2. CopySQLite.dll v2.7.5 to the created folder.

3. Copy LibSQL to the created folder.

4. Run LibSQL.

5. Create a database (database -> new) or open an existing one.

Do not give an extension - default is .dbs

If you try to create a database that already exists, the existing one is opened.

The program opens 2 texteditorwindows. The upper one is used to enter commands and queries. You can enter commands on one line or on different lines. When you click 'execute' everything is concatenated in one line.

Ex: 'Select * from test'
       is the same as
      
        'Select
        *
        from test'

Every string has to be enclosed in single quotes. Numbers may be but it is optional.

Ex: Select * from test where name='lennon'

You can save LibSQL commands with the file -> save option and recall them with file -> open. To save time commands can be copied and pasted.

The lower texteditor will display the results and the program messages.

6. Create a table.

7. Do some SELECT queries.

8. Try things out

9. Give feedback

LibSQL v1.1 - Supported functions

All syntax starting with . is an added feature. This means that the program does at least some processing before (eventually) calling the DLL. All syntax without a leading . is a straightforward SQLite call. In the following all LibSQL syntax is shown in UPPER Case, although lowercase is permitted in all commands.

CREATE TABLE tablename (field1,field2,...,fieldx)
    Ex: CREATE TABLE test (Name,firstname,age)

CREATE INDEX indexname ON tablename (field)
    Ex:CREATE INDEX textindex ON test (name)

DROP TABLE tablename
    Ex: DROP TABLE test

DROP INDEX indexname
    Ex: DROP INDEX testindex

DELETE FROM tablename
    deletes complete table

DELETE FROM tablename WHERE condition
    Ex: DELETE FROM test WHERE name='peeters'

INSERT and .INSERT
INSERT follows the SQL syntax -  .INSERT allow to drop the ,'s and the ()'s.

    Ex: INSERT INTO test VALUES
        ('peeters',
        'richard',
        30)

    Ex: .INSERT INTO test
        peeters
        richard
        30
 
UPDATE tablename SET
    fieldname=value
    WHERE condition

    Ex: update test
        SET name='tanson'
        WHERE age=30


SELECT * FROM tablename
    Ex: SELECT * FROM test
    Ex: SELECT * FROM sqlite_master
.MODE
sets the way the result from a Select query is displayed.

.MODE LINE: every field of the returned records is displayed one after the other seperated by |.
.MODE LIST: every field is on a line of its own
.MODE COL: all fields are lined up every 15 positions - fields longer then 15 are truncated.
.mod PRINT: same as col but result to the printer.
.MODE FILE: result is output to a comma separated textfile.

Future plans