SBME1 Database Basics

There are two (three) database types natively in SIMPOL: the Superbase PPCS format, which we’ve talked about here and this the SBME format.

1. What is SBME
2. PPCS vs SBME
3. Example
   a. Part 1
   b. Part 2
   c. Part 3
   d. All together
4. A third database type
5. Download
6. Source Code

SBME?

SBME stands for Superbase Micro Engine. It’s a very fast, compact and highly compatible database, supporting all of the SIMPOL data types. SBME is however limited to single-users, and for multiple users, PPCS has to be used. The SBME database provides a fairly low-level API for accessing database tables and records. It is not a typical SQL-style API but rather a table and record-oriented one. The current engine provides only a storage-only database (no calculations, constants, validations, triggers, etc. that are found in Superbase Classic). The format is as follows:

• SBME database files have an sbm extension
• The file can contain one or more database tables
• All of the parts of the database table are contained within the database file
• Each table consists of one or more fields and 0 or more indexes, each index being associated with a specific field
• A field has a data type that must be one of the value types or else a date, time, or datetime

One of the more significant points to be aware of (especially when switching from the older SBF) is that there is no column width or display format associated with a field, if these are necessary they have to be dealt with within the program that saves the records to the database.

Programming with PPCS vs SBME

There is very little difference between working with an SBME and a PPCS database. The only significant programming difference occurs when opening an SBME database.

To open an SBME database the new() method is called. When calling new the name of the database and the action to be taken must be specified, the following characters are acceptable actions:

• O – open
• C – create
• R – replace
• OC – open if exists otherwise create
• RC – replace if exists otherwise create

Order of events

Programming with SBME Databases broadly requires the following things to occur:

1. Get at least a shared lock on the sbme1 object using the lock() method
2. Create a new table using the newtable() method, this will return a sbme1newtable object
3. For each field desired a call to thenewfield() method of the sbme1newtable object must be made
4. If a field should also be an index you have to pass that field into newindex()
5. Finally, to create the table, you call thecreate() method of the sbme1newtable object
6. To write changes to file the commit() method of the sbme1 must be called
7. The last step once you’ve written all changes to file is to unlock the database again using the unlock() function

An Example

This program will demonstrate how to create and open a database and table, add, make changes to, and commit records. It consists of three parts, we’ll create all three parts as separate functions that will be called by one main function.

The first thing to do is to declare the constants for the file and table name at the top of the program

constant TESTFILENAME     "jdk.sbm"
constant TABLENAME        "JDK"

Here we can also create our main function, although currently, calling this will cause an error as these functions do not yet exist

function main()
  string s

  s = part1()
  s = s + part2()
  s = s + part3()
end function s

Part One – creating the database

In this tutorial I will not be declaring any variables, only highlighting the key pieces of code required for basic functionality, you can find the full source code with error checking and variable declarations at the bottom of the page, alongside a download to the full project.

Bearing that in mind to create a database with a table, fields, and records we must first create an sbme1 object

sbme =@ sbme1.new(TESTFILENAME, "RC", error=e)

As you can see we are using the constant we have declared to replace (or create) our new database. Once we’ve done this the next thing we need to do is lock the database:

sbme.lock("shared", error=e)

Here we are using the “shared” lock but we just as well could put an “exclusive” lock on the database, in this case, it makes very little difference although the lock type should be considered for more advanced use cases. Once we’ve successfully locked the database we now want to create a table, fields and indexes:

nt =@ sbme.newtable(TABLENAME)
fld =@ nt.newfield("FieldA", string)
nt.newindex(fld, 100)
// The following field is created as an integer
fld =@ nt.newfield("FieldB", integer)
nt.newindex(fld)
fld =@ nt.newfield("FieldC", date)
nt.newindex(fld)
nt.create(error=e)

Let’s walk through this code line by line, we start by creating a table using our newtable() method, once we’ve done this we want to add some fields, the fisrt of these is a string field called “FieldA“. If we want to turn this field into an index, we are going to use the newindex() function.

It may be a good idea to further look at the newindex method as it’s a very powerful function, below is the list of parameters it accepts

sbme1newtablevar.newindex(sbme1newfield field, integer precision, boolean unique, string algorithm ) 

field is a fairly obvious parameter it should be the field you wish to index, precision represents the number of bytes of a string or the number of digits from the left of a value to use when sorting through an index. unique determines whether the index should consist of only unique values, and finally, algorithm is the name of the algorithm that which is used for sorting the index, the only valid values of which are currently .nul and SB Compatible

Once we’ve added all the fields we can now finalise or “create” the database using the create() function

Part One – Adding records

Now we have a table with fields we will want to actually add records to it. To do that we must first unlock the database, open the table, and get the three fields we have created

sbme.unlock(error=e)
jdk =@ sbme.opentable(TABLENAME, error=e)

fielda =@ jdk.firstfield
fieldb =@ fielda.next
fieldc =@ fieldb.next

This is not the method I would recommend for getting fields, I would use the member operator ! as generally this is better practice than going through every field, especially when dealing with much larger tables with possibly hundreds of entries, and we will be using that method in the next two parts of this tutorial but it is important to highlight this approach as it can come in handy from time to time

Once this has been done we’ll want to initialise our date object and lock the table

today =@ date.new()
today.setnow()

jdk.lock("shared", error=e)

The only time it is necessary to lock the table is when adding records, for reading or modifying records this does not need to be done. When reading or modifying it is best practice to just lock the record you’re accessing.

Now we get to the exciting bit, creating new records:

r =@ jdk.newrecord()
r.put(fielda, "First Record")
r.put(fieldb, 1)
r.put(fieldc, today - 1)
r.save("", error=e)

This method will become familiar, it is not already, as it is identical to the method used for creating PPCS records. The final step in this part is to commit these records using the commit() method

sbme.commit(error=e)

Part Two – Opening a record

This part will concern itself with selecting and modifying records and starts where the last part left off. Having now created a file and database with records in it we will want to open it, there are several ways of doing this. If we already know the name and location of the file we can hard code that in for opening, alternatively we could look into our box of tools and find wxfiledialog(), a very useful function that lets the end-user select a file

wxfiledialog(.nul, "Select the database file to open", ".", TESTFILENAME, \
             "SBME Files (*.sbm)|*.sbm", "open, mustexist", filename, response)

I would suggest looking further into this function in the Language Reference Guide for a full breakdown of the options. But for now, just accept that this function will open a dialog window where a user can select any .sbm file that already exists

Once we have the filename and location we can now call the new method to open the database:

sbme =@ sbme1.new(filename, "O", error=e)

Note the use of the “O” instead of “RC” in this call. Now we’ve opened our database we will want to open the table we created in part one so we can play around with the records inside it, this is done like so:

jdk =@ sbme.opentable(TABLENAME, error=e)

We want to now get the record for today, days are stored in FieldC so we will use a member operator and the selectkey method like so:

r =@ jdk!FieldC.index.selectkey(today, error=e, found=found)

If found returns true we will now create a simple readout of the record

s = "Record contains:{d}{a}"
s = s + r!FieldA + "{d}{a}"
s = s + .tostr(r!FieldB, 10) + "{d}{a}"
s = s + .tostr(r!FieldC, 10) + "{d}{a}"

This last line will not return an easily readable format, displaying the date as an integer but since it is not pertinent to this exercise we will leave it like this (see our date tutorial for more information).

Part Two – Editing a record

Now we’ve opened and read from a record the next thing we want to be able to do is edit a record, to do that we must select the record with a lock on it

r1 =@ r.selectcurrent(r.index, "exclusive", e)

We are using a different variable r1 to reselect the record as if the lock fails we don’t want to lose the reference to our original record, as this will often still be required.

Once we’ve successfully selected the record we can now edit it, in this case we are going to change the value of FieldA to “Fourth Record”:

r1!FieldA = "Fourth Record"

Normally I wouldn’t highlight this but in this case we are going to do something more advanced following this. We will now attempt to save it, notice we are setting the error parameter to e

r1.save(error=e)

The next piece of logic relies on the rollback and commit methods: rollback is a method that reverts a record back to its last committed state (this is either the last time the commit method was used or the state of the record on initial opening).

Note: This method only works if the committype is set to "". and not to "auto" (the default for an sbme1 object is "")

With that in mind the following code should now make sense:

if e != 0
     sbme.rollback(error=e)
else
     sbme.commit(error=e)

Following the attempt to save the record we are going to do one of two things: If an error is thrown we will attempt to rollback the changes, else we will commit the record to file. After this, we are going to again create a readout of the record as before. This will be returned at the end of the function

Part Three – Reverting the changes

This part is very similar to part two, in this part we are going to revert the changes from the second part but this time without any of the fancy logic. The key differences here are in field selection, same technique, but different field

r =@ jdk!FieldA.index.selectkey("Fourth Record", error=e, found=found)

We are then going to readout the record, reselecting it, and then saving it as before, but following that we will not be error catching. The code for that is as follows.

s = "Record contains:{d}{a}"
s = s + r!FieldA + "{d}{a}"
s = s + .tostr(r!FieldB, 10) + "{d}{a}"
s = s + .tostr(r!FieldC, 10) + "{d}{a}"

r1 =@ r.selectcurrent(r.index, "exclusive", error=e)

r1!FieldA = "Second Record"
r1.save(error=e)

A third option

At the beginning of this tutorial, I briefly mentioned a third database type: volatable, unlike the other two volatable is an in-memory, virtual database, and thus only exists when the program is running. I’ve written up a more in depth guide here

Download

This tutorial project is available in your SIMPOL installation under projects\sbme\jdktutorial and for download here: