A4 as a Database

As well as featuring automatic persistence, whereby program state is saved automatically without the need for an additional database backend, A4 also comes with some degree of compatibility with SQL.

Current Status

A4's SQL implementation is intended to serve as a helpful utility, but is not a drop-in replacement for SQLite or a client/server database.

A4 includes the basic SQL data model (tables, rows, columns) and the basic query commands (select, where, order-by), but it doesn't (yet) support the full SQL query syntax or offer a client/server mode or bulk import/export features.

Creating a Database

Ok, let's create a database:

<A4> db <- SQLDatabase new
  -> SQLDatabase

Now let's create a table:

<A4> t <- db addTable: 'myTable'
  -> SQLTable

And add some columns:

<A4> t addColumn: 'myColumn' type: 'ANY'; addColumn: 'otherColumn' type: 'ANY'; addColumn: 'thirdColumn' type: 'ANY'
  -> SQLColumn

Then let's add some rows of data, using Smalltalk's array literal syntax:

<A4> t addRow: #(1 2 3); addRow: #(4 5 6); addRow: #(7 8 9)
  -> SQLRow

And let's check our data is really there:

<A4> (t row: 2) otherColumn
  -> 5

Awesome! Time to run some queries!

Querying the Database

A4 includes the same basic querying commands as SQL, but the syntax is different.

To select some columns from a table (instead of SQL's SELECT syntax):

<A4> tmp <- t select: #(myColumn thirdColumn)
  -> SQLTable

To limit this selection to rows matching certain conditions (instead of SQL's WHERE syntax):

<A4> tmp <- tmp where: [ :row | row myColumn ~= 4]
  -> SQLTable

To order the selection, we provide a block that results in a positive number for "a > b", a negative number for "a < b" or zero for "a = b" (instead of SQL's ORDER BY syntax):

<A4> tmp <- tmp orderBy: [ :a :b | b thirdColumn - a thirdColumn]
  -> SQLTable

Now we can check that our result only contains the columns myColumn and thirdColumn, doesn't have the middle row, and is in reversed order:

<A4> tmp countColumns
  -> 2
<A4> tmp countRows
  -> 2
<A4> (tmp row: 1) myColumn
  -> 7
<A4> (tmp row: 2) thirdColumn
  -> 3

What's Next?

The SQL implementation could be extended in the future to allow SQL syntax for queries, and to support MERGE, JOIN, type-safety and other features.

You might also want to take a look at the internals of the help system if you're building your own dataset. It might be a good starting point, especially if you're dealing with a lot of text data.