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.
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.
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!
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
<A4> tmp <- t select: #(myColumn thirdColumn) -> SQLTable
To limit this selection to rows matching certain conditions (instead of SQL's
<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
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
The SQL implementation could be extended in the future to allow SQL syntax for queries, and to support
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.