Creating A SQL Yoga Database Object

The first time you incorporate SQL Yoga into one of your applications you will need to perform a couple of tasks before you get to work. This lesson will walk you through those tasks.

Note that you should not have any other SQL Yoga projects open when performing the following steps (unless you already know what you are doing). You should start with a freshly launched IDE that only has the tutorial application open just to make sure you don't run into any problems.

To get started, open the Message Box by choosing the Tools > Message Box menu.

Create a Database Object

SQL Yoga stores information about your database in a Database object. A Database object stores all information about your database schema and other objects that you will use to interact with the database (Tables, Relationships, Scopes, etc.).

Creating a Database object only needs to be done once and can be done by calling dbobject_createObject in the Message Box. 'the result' will be empty if everything goes well, otherwise you will see an error appear in the Message Box.

==========
Copy & Paste
==========

dbobject_createObject
put the result

Create A Storage Object

Now there is a Database object named 'default' that exists in memory. The next step is to create a place to store that Database object between sessions. You can store a Database object in a Revolution control (as a custom property) or a file (as an encoded array). We will look at how to store the object in a button.

Open the application stack (1) and create a button on the card by dragging it from the Tools Palette. Name it SQL Yoga Database Object Storage (2). Since this application uses the GLX Application Framework I'm going to create the button on the application stack as it is a good place to put controls that are application specific rather than tied to a particular window in the program.

Note: A quick way to open the application stack is to right-click on it in the Application Browser and select Go from the contextual menu.

Tell Database Object Where It Should Be Stored

Now that you have a place to the Database object you need to tell the Database object about it. You can perform this operation in the multi-line message box. Here is what you need to do:

1) Specify the control the Database object will be saved to when calling dbobject_save by setting the storage object property and passing in the long id of the button you just created.

2) Save the Database object. dbobject_save will save the Database Object to the button specified in step 1.

==========
Copy & Paste
==========
dbobject_set "storage object", \
    the long id of btn "SQL Yoga Database Object Storage" of stack "application"
dbobject_save

After executing the above code in the message box you can look at the custom properties of the button. There is now a uSQLDatabaseObject custom property that contains all the information about your Database object.

Create a Connection Object

Now that you have created a Database object it is time to add other objects to it. Think of a Database object as a Group in Revolution. A Group contains other controls like buttons and fields. A Database object contains Connections, Tables, Relationships, Scopes and more.

The first object you will create in the 'default' Database object you just created is a Connection object. Connection objects store connection settings for the databases you want to communicate with. Creating a Connection object is easy. Just execute the dbconn_createObject command in the message box and pass in a name (1) and adaptor (2) for the connection.

Whenever you add a new object to a Database object you should save your work. Calling dbobject_save (3) will save the Database object to the SQL Yoga Database Object Storage button. Note that dbobject_save merely saves the object to the button. You still need to save the stack containing the button to permanently store the changes to disk.

==========
Copy & Paste
==========

dbconn_createObject "my connection", "sqlite"
dbobject_save

Read In Schema By Testing Connection

The first time that SQL Yoga connects to a database it asks the database for information about the tables and fields in it. SQL Yoga stores this information in a Schema object that is stored inside the Database object. All of the SQL Yoga objects provide their automated features by using this cached information.

Let's read in the schema of the To-Do list database. Follow these steps in the multi-line Message Box:

1) Display a file selection dialog. Select the to-do.sqlite database file that is located in the ./Blank Application/components/ folder of the tutorials distribution folder.

2) Set the file property of the Connection object you created. Note that when you created the first Connection object SQL Yoga assigned it as the default connection for the Database object. The 'default connection' is assumed in all handlers with the dbconn_ prefix so there is no need to pass in the connection name to dbconn_set.

3) Connect to the database. SQL Yoga will import the database schema when you connect.

4) See what the schema looks like. You can use dbobject_getArray() and the helper function printKeys (part of SQL Yoga library) to see a printout of the Database object.

Now would be a good time to execute dbobject_save in the message box so that the new information that has been saved in the Schema object is saved.

==========
Copy & Paste
==========

answer file "Select to-do.sqlite database file"
dbconn_set "file", it
dbconn_connect
put dbobject_getArray() into theDatabaseA
put printkeys(theDatabaseA)

Let SQL Yoga Know If You Update Your Database Schema

If you ever add, remove or alter tables in your database then you need to tell SQL Yoga about it. To do this just call dbobject_reloadSchema which clears out the existing schema stored in your Database object and imports it again using the default Connection object.

==========
Copy & Paste
==========

dbobject_reloadSchema
dbobject_save

That's It

You have now performed all of the preliminary tasks for setting up SQL Yoga for a project.

6 Comments

Jérôme Rosat

Does the "storage object" have to be created in a “separate” stack which can be saved ? For example, if we create a new table in the database by script, the schema must be reloaded et saved. Should we save the stack ?

Trevor DeVore

The storage object can be on any stack you would like but you do need to save the stack if you want the changes you make to the Database object to be permanent.

Shane Carroll

Did you use SQL Yoga to create the sqlite schema or did you use some other tool?

Trevor DeVore

Another tool.

David Glass

Similar to Jerome's question, if I have a standalone that receives an update to its backend db schema how does that get handled?

I'm guessing I can run the 'dbobject_' commands as part of an update handler, but the changes wouldn't be saved until the stack is saved, and the LC Dictionary says 'cannot save to a standalone application's file', which I interpret to mean its stacks.

Is this correct, and if so how can something like this be managed/implemented?

Trevor DeVore

If your application allows the user to update the db schema object after you release the application then you should store the database object in a stack file that resides in the user application data folder, not within your application. You can save stack files whenever you would like, assuming you have write permission.

Add your comment

E-Mail me when someone replies to this comment