Data Collection: Building Databases Using SQLite

Those of you who are paying close attention already know that Max 5 includes a database that manages all the files in the search path and makes handy things like the File Browser possible. To enable this functionality, we wrote an SQLite object to do all the important work under the hood. However, the SQLite object in Max isn't really something that you can type into an object box, and it doesn't come with any help files or documentation. In this article, we'll look at ways to interface with this mysterious "no box" object using JavaScript, so that you can build, query, and edit your own databases in Max.

Getting Started

The first thing you might be wondering is, why do I need a database? Well, you can think of a SQL database as a really souped up coll object, with a fairly simple language for doing all sorts of operations on the data. Also, unlike coll, you never have to explicitly save your changes, and it is a pretty robust and safe solution for managing data. Even if you crash Max, your database will remain unharmed.

The example patches and JavaScript code can be downloaded here

To get a feel for the essential SQLite interface, open up the the basic-db patch. In this patch, there is nothing more than a jit.cellblock, a js object, and a bunch of messages. The JavaScript code for this patch provides the most minimal interface possible for sending queries to the SQLite object and formatting the results for display. Let's open up the JavaScript code (easyDatabase.js) to see what's going on.

var sqlite = new SQLite;
var result = new SQLResult;

function opendb()
{
sqlite.open(x,1); //open a file-based DB
}

To begin, we create our new SQLite and SQLResult objects and assign them to the variables "sqlite" and "result". The SQLite object has a couple of methods that we'll be using to get a database going. The first one that you see here is the SQLite.open() method. This opens an existing database, or creates a new one if it doesn't already exist. It expects two arguments - one for the filepath, another to specify if it is file-based or RAM-based. For our purposes, we'll be using a file-based database since it will probably be the most useful for you.

The other method we'll be using extensively is SQLite.exec(). This is the all-purpose query method, and takes as arguments a SQL query string and the name of a SQLResult object to store the results of your query. Once the query is sent, you can then iterate through the SQLResult to get the resulting values. The SQLite.exec() method is capable of creating,altering, and deleting tables, inserting data, doing all sorts of formatting operations, and performing complex searches depending on the string you supply it. Some examples of common SQL queries are provided in the patch. In our exec() function, you'll also notice the formatResultForCellblock() function being called. This nice little bit of code simply loops through all the fields in the SQLResult table and outputs them in a way that jit.cellblock will understand.

function formatResultForCellblock()
{
var numfields = result.numfields();
var numrecords = result.numrecords();

outlet(0, "clear", "all");
outlet(0, "cols", numfields);
outlet(0, "rows", numrecords + 1); // rows +1 so we can create a header row

for(var i=0; i
outlet(0, "set", i, 0, result.fieldname(i));

for(var i=0; i
for(var j=0; j
outlet(0, "set", j, i+1, result.value(j, i));
}
}

Notice that the result.value() method takes 2 arguments, since you are working with a two-dimensional collection of information. To see our little database in action, try clicking on the different messages in the patch. Each of these messages is an example of common queries that you'll be using. For more extensive documentation on SQL syntax, there is a nice reference and tutorials here. To practice your SQL skills, you can try typing strings into message boxes and send them to the js object to see the result. With a little planning and patching, you can build a pretty useful database using just variations of the provided queries. In next section of this article, we'll be looking at ways to apply this information to a working database-driven patch.

Video Database

To give an example of a possible application for SQLite within a Max patch, we're going to build an interface that loads a folder of video files into a database and provides controls to add different tags to each clip. In addition, we're also going to create a function to randomly choose a video for playback that has a specified tag. In order to accomplish these things, we'll need to create function-wrappers for the necessary SQL queries, create a couple of patcher interface elements, and make use of the JS Folder object.

Building the Database

We'll go ahead and use the familiar opendb() function from the easyDatabase.js, but we're going to also add a get_all() and menuLoad() function so that we can see some visual feedback and have the patch update when we load a previously saved database. The get_all() function is just a wrapper for the query exec("SELECT * FROM dumpster");, while the menuLoad() loads a umenu with the elements loaded in the database. The menuLoad() and other functions pass information out of the second outlet, since we are already using the first outlet for our jit.cellblock interface. If we are creating a new database file from scratch, we'll call the build_db() function that creates a TABLE named "dumpster" where we'll store all of our information. More complex databases often use several different cross-referenced tables to manage large amounts of data and relationships between entries, but for our uses all we need is one. Once we create our "dumpster" table, we also create columns to store the filetype of each entry, as well as our two content tags 'color' and 'style'. Now that we have the basic structure in place, we can begin to start populating it with some data.

Come to the Table

In order to be of any use to us, we need to grab some meaningful data to load into our database. In this case, we're going to use the names of movie files from a specified folder. In order to accomodate this, we create a function called folderLoad() that makes use of the JS Folder object. First, we create a new Folder object with the line f = new Folder(xpath);. We can also filter results by specifying a list of filetypes that are supported by Quicktime. We then iterate through our Folder, passing the filename and filetype to the insert_entry() function. This function makes use of JavaScript's user-friendly text-concatenation features to generate a valid SQL string that inserts our information into the table.

//insert filename and filetype
function insert_entry(){
if(arguments.length==2){
exec("INSERT INTO dumpster ('name','filetype') VALUES ('"+arguments[0]+"','"+arguments[1]+"')");
}
else{
post("Wrong number of arguments");
}
}

Finally, once all the files have been added as new entries in the database, we once again call get_all() and menuLoad() to update our patcher interface, and then post a message to the Max window to let us know it is all ready to go.

Deeper Meanings

Now that we have a table full of movie names, we can set to work on providing an interface that allows a user to add tags to each movie to describe specific aspects of the clip. In this case, I've elected to use the tab object to provide a consistent range of options for each tag. Since our JS object has to interface with a variety of objects in this patch, we simply prepend our output messages with a symbol that indicates where the information should go. This way we can use route to pass these messages to the right place. Using the umenu that is loaded with our movie names, we can call the get_entry function, which will set the tab objects to the proper saved value and allow for changes (using change_entry) to the metadata. Both of these functions simply provide an interface that generates an SQL string to accomplish its task. Using this interface, which could be expanded to provide many other tags with little effort, we can go through our menu of movie files and assign color and style tags to each one.

Now comes the fun part. One of the great features of SQL, and the reason it is so widely used in web applications, is the ability to search the table using different sorts of criteria, and even perform different operations on the results. Our movie selection interface uses a query that searches the table for entries that match the specified metadata tag we click on. We then pass the results of this query to a random number generator to select which entry to display. This selection is then passed on to the umenu object, which tells the jit.qt.movie to read the chosen video.

Now that you have seen some basic techniques for using SQLite in a Max patch, it is up to your imagination how to expand on this and create new and useful solutions. One could easily alter the JavaScript code and patcher interface to load folders full of audio files or images, or even just to store named and tagged presets for a complex patch. Enjoy.