August 20 2009 
Go with the Flow with SQL Yoga
Writing your SQL queries can now be easy and fun with this brand new library from Blue Mango
I love to write with a fountain pen. There is something about the feel of the pen in your hand and the way it glides over the paper while you write that other pens just can't match. It seems as if the fountain pen is a natural extension of my body as I transfer the ideas in my head to the sheet in front of me.
When I'm writing applications, Revolution is a lot like my fountain pen. It is a very fluid development environment, one in which I can quickly sketch out ideas in revTalk and then see what that idea looks like without waiting to compile. Revolution doesn't inhibit the creation process but rather helps it along.
Sometimes, however, I have to work with resources that fall outside of Revolution's current areas of expertise. When this happens there is a break in the flow of application writing as I wrestle with creating a library to interact with the external resource.
One common resource that Revolution developers interact with is a database. Databases come in many shapes and sizes - some are text files, some stacks and others SQL databases. Various projects have different requirements but in most cases we all want the same thing - ease of use and the flexibility to experiment while we develop.
SQL Yoga is a new library that brings ease of use and flexibility to database development. Incorporating SQL databases into Revolution has traditionally involved the painful process of creating a handler for each bit of data you want to extract from it. Not only does this require an enormous amount of time but it also requires constant additions as your database evolves. Not with SQL Yoga though. With SQL Yoga you focus on using your data instead of worrying about accessing your data.
SQL Yoga extends the simplicity of Revolution to SQL databases by allowing you to treat your database like an object. With SQL Yoga you:
- Set properties rather than writing SQL
- Define searches using english words rather than arcane wildcard symbols
- Manipulate arrays, not cursors
- Easily generate searches from complex search UIs
- Tap into database table relationships to simplify code
Why Is SQL Yoga Different?
The reason that SQL Yoga makes life easy for you is simple: for the various interactions you have with a database it provides objects to facilitate those interactions. These small, narrowly focused objects help you write the code for that interaction very quickly.
Let's take creating a new record in the database as an example. Assume I have a database table named "lessons" that has three fields: id, name and description. In order to create a new record in a SQL database the following SQL needs to be sent from Revolution to the database:
INSERT INTO lessons (id, name, description) VALUES (...);
When creating the SQL statement I also have to take care to escape special characters that are passed in as values. For example, any instances of the single quote character would need to be escaped if I were using SQLite:
INSERT INTO lessons (id, name, description) VALUES (1, 'my \ title, 'It''s a great lesson.');
The RevTalk to create this insert statement might look something like this:
## Get user input put the text of field "ID" into theID put the text of field "Name" into theTitle put the text of field "Description" into theDescription ## Sanitize input replace "'" with "''" in theTitle replace "'" with "''" in theDescription ## Generate statement put "INSERT INTO lessons (id, name, description) VALUES (" & \ theID & ", " & theTitle & ", " & theDescription & ")" \ into theSQL ## Send SQL to database revExecuteSQL sDatabaseID, theSQL
With SQL Yoga you can skip the SQL and you can skip the escaping. Here is an example of creating a record using a SQL Record object:
## Create SQL Record for Lesson put sqlrecord_createFromTable("lessons") into theLessonA ## Get user input put the text of field "ID" into theLessonA["id"] put the text of field "Name" into theLessonA["title"] put the text of field "Description" into \ theLessonA["description"] ## Send SQL to database sqlrecord_create theLessonA
How does the above example work? SQL Yoga knows all about your database and its tables and it allows you to treat them as objects. In this example a SQL Record object was created for a lesson (arrays are used to represent objects since Revolution has no current means of creating custom objects). The properties of the lesson were inserted into the keys of the theLessonA array and then handed off to sqlrecord_create, the command that converted the object to a SQL statement and sent the statement off to the database.
The immediate benefits of the object approach is that the code is easier to write and easier to read. Additional benefits include the ability to trigger callbacks and send messages to other objects (stacks, cards, etc.) whenever a new record is added to the "lessons" table.
The SQL Query Object
Now that I've introduced you to the concept of objects in SQL Yoga I want to show you the SQL Query object. The SQL query object makes extracting data from a database fun. How could that possibly be fun you ask? Because it is painless.
Here is a simple example that returns lessons from the database as an array. The lessons will be sorted by title and they will be filtered based on titles that begin with a search term entered by the user.
put sqlquery_create("lessons") into theQueryA ## Sort records by title sqlquery_set theQueryA, "sort by", "title" ## Only return records whose title starts with term entered by # user ## Note: the :1 is a binding variable. ":1" will be replaced # with the ## value of the variable theSearch. put the text of field "SearchTerm" into theSearch sqlquery_set theQueryA, "conditions", "title begins with ':1'", \ theSearch ## Execute query and return a numerically indexed array ## in the theFoundLessonsA variable sqlquery_retrieveAsArray theQueryA, theFoundLessonsA
With a SQL Query object creating queries is so easy that you no longer have to write those long libraries for getting data out of each table. SQL Yoga provides an object with the properties you need and takes care of everything for you.
What Else Can SQL Yoga Do?
Hopefully you are starting to see why SQL Yoga makes integrating SQL databases into Revolution easy. Here are some of the other objects that SQL Yoga has as well as some of the benefits they provide.
Connection Objects- Easily connect to a database by setting properties.
- Define connections for development, testing and deployment and instantly switch between them while working on your application.
Table Objects
- Add Relationships between tables (one-to-many, one-to-one, many-to-many).
- Add Scopes to make dynamic searches easy.
- Add your own properties to tables (e.g. a "short description" that returns a truncated version of the lesson description).
- Add listeners that receive messages when records are created, retrieved, updated or deleted.
Relationship Objects
- Define how two or three tables are related to each other so that SQL Yoga can convert query results to hierarchal arrays that take parent-child relationships into account.
Scope Objects
- Define targeted search conditions for a table that can be added to a SQL Query object.
- Add multiple scopes to a SQL Query based on user input. SQL Yoga generates the search string for you.
SQL Record Objects
- Create, retrieve, update and delete one or multiple records in a database table with ease.
- Link and unlink records from two related tables.
- Quickly retrieve child records from a table related to a Record object.
SQL Query Template Objects
- Create templates that can be used when creating a new SQL Query.
How Do I Get SQL Yoga?
A public beta version of SQL Yoga is currently available for you to download and use. The library is fully functional but will expire on September 15, 2009. Now is the opportune time to try it out and provide feedback.
SQL Yoga will retail for $199 but you can pre-order SQL Yoga for only $99 up through the end of the RunRevLive 2009 conference.
SQL Yoga Resources
Download the SQL Yoga public beta here
Download Sample Application here.
You will find API Documentation here.
And the Manual is here.
 |
|
