SQLite UUID extension in MBS SQL Plugin

Extras
1

For the MBS Xojo SQL Plugin 25.5, we add a few extensions and include UUID, Base64 and CSV. The uuid extension allows us to make an uuid in a SQL query as the extension adds these functions:

uuid() generate a version 4 UUID as a string
uuid_str(X) convert a UUID X into a well-formed UUID string
uuid_blob(X) convert a UUID X into a 16-byte blob

Let’s use this in an example project:

We activate our internal SQLite library via InternalSQLiteLibraryMBS module and turn on the extensions we need like, in our case the UUID one:

// use internal sqlite library with UUID extension
InternalSQLiteLibraryMBS.UUIDExtensionEnabled = True 
Call InternalSQLiteLibraryMBS.Use

Next we connect to an in-memory database in this example. But if you use a real file path, it will open a file on disk. If connect doesn’t report an error, we have an open database:

Var db As New SQLDatabaseMBS
Var path As String = ":memory:" // in-memory database without a file 

db.DatabaseName = "sqlite:"+path
db.Connect

The decision which extensions register into a SQLite connection is made at the Connect call. You can enable extensions just before and disable them after to avoid having them available in other connections. Or you just enable all extensions you may need in app.Opening event.

Next we test the uuid() function and show the uuid in a dialog:

// make an uuid 
Var r As RowSet = db.SelectSQL("SELECT uuid()") 
MessageBox "uuid: " + r.ColumnAt(0).StringValue

Time to create our sample table. We use the uuid as primary key and let SQLite automatically calculate a new row id each time a record is created. Please note that we need to provide the default in brackets, so SQLite knows where to start and end when parsing the statement.

// create table with uuid id field 
db.SQLExecute "CREATE TABLE Test(id TEXT PRIMARY KEY DEFAULT (uuid()), FirstName TEXT, LastName TEXT)"

When we insert rows, we just pass data. The id column is calculated automatically.

// insert value 
db.SQLExecute "INSERT INTO Test(FirstName, LastName) VALUES ('Bob', 'Jones')" 
db.SQLExecute "INSERT INTO Test(FirstName, LastName) VALUES ('John', 'Smith')" 
db.SQLExecute "INSERT INTO Test(FirstName, LastName) VALUES ('Anna', 'Miller')" 
db.SQLExecute "INSERT INTO Test(FirstName, LastName) VALUES ('Diana', 'Doe')"

Now we query the table and add rows to a ListBox:

Var rec As RowSet = db.SelectSQL("SELECT * FROM Test")

Var list As Listbox = SQLWindow.List 
While Not rec.AfterLastRow 
	list.AddRow rec.Column("id"), rec.Column("FirstName"), 	rec.Column("LastName") 
	rec.MoveToNextRow 
Wend

Finally we can use our new DumpToStrings function to dump the content of the database as SQL commands. This allows you to recreate the database again later or to take the SQL statements and e.g. move to MySQL to recreate it there.

// dump the database 
Var lines() As String = InternalSQLiteLibraryMBS.DumpToStrings(db.SQLiteConnectionHandle, "main", "") 
SQLWindow.TextArea1.Text = string.FromArray(lines, EndOfLine)

Please try the new uuid function and let us know if you need other extensions.

2 Likes