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
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
You have now performed all of the preliminary tasks for setting up SQL Yoga for a project.