Introducing Query:
Database Interaction Simplified

Presentation at Lasso Developer Conference, May 2012 
by Brad Lindsay, North Carolina Hospital Association

Query is a modern replacement in Lasso 9 for the built-in inline, resultset, records/rows, and field/column methods. It is a wrapper for inline_type that also implements trait_queriable (making it compatible with query expressions and other iteration methods). Each element in a query is an object that must implement the provided trait_query_result. This allows access to the resulting data in an object-oriented manner. Query comes with a type named query_result which implements trait_query_result and is the default type for each object returned.

Why Create Query

Query was created to help me more easily create automated tests based on data pulled from a database. Writing tests for methods of objects is fairly easy to do — a test expects a method to have a specific result given that the object was created with a specific set of data. I needed a way to translate a row of data from a database into an object of my custom type to facilitate writing theses tests and hence query was born.

The other benefit to creating query is that it simplifies how Lasso interacts with data sources. The Lasso-supplied way of interacting with datasources is procedural in nature, requiring programmers to use at least two to four different tags to access the data. Query, on the other hand, takes an object-oriented approach and only requires 1 tag to access the data and optionally integrates with your own custom types. Because of its procedural nature, inline's can't be stored in a variable and inline data, such as found_count, can't be accessed outside of an inline block. Because query is a type, instances of query can be stored in a variable and information about the query can be referenced anywhere you want.

Installing Query

There are multiple different ways you can install query:

1. Put the query.inc file in the LassoStartup folder in the Lasso home directory. However, since query is meant to be a replacement for your datasource interaction, I recommend following one of the other two options.

2. Download the compiled library file for the appropriate system from https://github.com/bfad/Lasso-Query/downloads and stick it in the LassoLibraries folder in your Lasso home directory.

3. Download the source files with the makefile and compile it from source. The following commands will use git to clone the project down to a computer and then compile and install the library file into the LassoLibraries folder in the Lasso home directory. (Note, the LASSO9_HOME environment variable must be correctly set.)
    $> cd where/you/want/the/source/downloaded/
    $> git clone https://github.com/bfad/Lasso-Query.git
    $> cd Lasso-Query
    $> make
    $> make install

Using Query

Since query is a wrapper for inline_type just like the inline method, the same parameters used for inline are used for query. Optionally, the first parameter can be a string with the name of a type that implements trait_query_result. If a type is not specified in this manner, then query will use the query_result type.

Query can be used by itself or stored in a variable for later access. Besides implementing the methods that allow one to iterate through the data, query also implements a number of member methods that provide information about the query itself, such as foundCount, fieldNames, and keyValue.

Since query implements trait_queriable, it works with a variety of sequencing methods. It has its own forEach member method that takes a block of code to execute for each element. It can be iterated over using an iterate block or a query expression. And it can use a loop block and the get member method to loop through each element.

Each row that gets returned from the database becomes an object whose type is either query_result or whatever custom type specified that implements trait_query_result. Implementing trait_query_result means that an object can access the data from the fields of the row by using the field name as a method. Here is a basic example in which the object stored in #person has member methods whose names are the names of the fields in the SQL query:

<?=
local(people) = query(
    -database="rhino",
         -sql="SELECT p.name_preferred, p.name_first, p.name_middle, 
                 p.name_last, p.email, p.type
               FROM people p WHERE p.name_last LIKE 'A%' LIMIT 100"
)
?>
<table>
[with person in #people do {^]    
    <tr>
    <td>[#person->name_first]</td>
        <td>[#person->name_middle]</td>
        <td>[#person->name_last]</td>
        <td>[#person->c('type')]</td>
    </tr>
[^}]
</table>

 

Examine the last column in the HTML table and notice that it accesses the data in the type column in a different way. It calls the member method "c" and passes it a string with the name of the column it is looking for. Any column can be accessed this way as well, but it is especially important to access the column named "type" this way. This is because Lasso has some built-in member methods for every type that don't take any parameters. These methods will always return the built-in values and not the values from the column. The following are a list of all such member methods:

• ascopy
• ascopydeep
• asstring
• invoke
• listmethods
• oncreate
• parent
• trait 
• type

Using Custom Types

Custom types can be created and used for the objects in the query results. To do this, the type must implement trait_query_result by importing that trait and having a member method named "__cols" that returns a map. Below is some sample code that creates a type named "person" that implements trait_query_result and then is used by query.
 

<?=
define person => type {
    trait { import trait_query_result }
    data protected __cols::map

    public onCreate(cols::map) => {
        .'__cols' = #cols
    }
    
    public .name_preferred => {
        return (.'name_preferred' == ''? .name_first | .'name_preferred')
    }
    
    public name_fml => .name_first + ' ' + .name_middle + ' ' + .name_last
    public name_pl  => .name_preferred + ' ' + .name_last
}
    
local(people) = query(
    'person',
    -database="rhino",
         -sql="SELECT p.name_preferred, p.name_first, p.name_last, p.email
               FROM people p WHERE p.name_last LIKE 'A%' LIMIT 100"
)
?>
<table>
[with person in #people do {^]    
    <tr>
        <td>[#person->name_pl]</td>
        <td>[#person->email]</td>
    </tr>
[^}]
</table>

 

Lessons Learned

The biggest lesson I learned from this is that traits rock! I learned how to create my own trait as well as implement a trait. Being able to implement trait_queriable gave me a bunch of functionality for free and made query compatible with query expressions.

I created the trait_query_result trait because it made more sense to have types implement this trait to get the functionality than to force them to inherit from a different type to get the functionality. If you are inheriting from a type just to get some functionality from it, consider making that parent type a trait instead.

Conclusion

With query, database interaction is simplified as is integrating data from databases into custom types. Feedback and suggestions are very welcome, and feel free to fork and submit pull requests via GitHub.

Author: Brad Lindsay
Created: 25 May 2012
Last Modified: 5 Jun 2012