Andrew Benson

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 

 object to do all the important work under the hood. However, the 

 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.

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 

 object, with a fairly simple language for doing all sorts of operations on the data. Also, unlike 

, 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 

To get a feel for the essential SQLite interface, open up the the 

 patch. In this patch, there is nothing more than a 

 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.

 objects and assign them to the variables "sqlite" and "result". The 

 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 

 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 

. This is the all-purpose query method, and takes as arguments a SQL query string and the name of a 

 object to store the results of your query. Once the query is sent, you can then iterate through the 

 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 

 function being called. This nice little bit of code simply loops through all the fields in the 

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

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

outlet(0, "set", j, i+1, result.value(j, i));

 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 

 To practice your SQL skills, you can try typing strings into message boxes and send them to the 

 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.

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.

 function so that we can see some visual feedback and have the patch update when we load a previously saved database. The 

 function is just a wrapper for the query 

 with the elements loaded in the database. The 

 and other functions pass information out of the second outlet, since we are already using the first outlet for our 

 interface. If we are creating a new database file from scratch, we'll call the 

 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.

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 

. 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 

 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.

exec("INSERT INTO dumpster ('name','filetype') VALUES ('"+arguments[0]+"','"+arguments[1]+"')");

Finally, once all the files have been added as new entries in the database, we once again call 

 to update our patcher interface, and then post a message to the Max window to let us know it is all ready to go.

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 

 object to provide a consistent range of options for each tag. Since our 

 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 

 to pass these messages to the right place. Using the 

 that is loaded with our movie names, we can call the 

 objects to the proper saved value and allow for changes (using 

) 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 

Now that you have seen some basic techniques for using 

 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.

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.

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.

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.

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.

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.

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.

data-collection-building-databases-using-sqlite

i have a problem to make connection with existent database (DataDemo.s3db) i change the script with opendb DB/DataDemo.s3db but the program don't accept the path,

hi,
thx for the tutorial,
i m a new user of Max/Msp
i have a problem to make connection with existent database  (DataDemo.s3db) i change the script with opendb DB/DataDemo.s3db    but   the program don't accept the  path,

Hi, is there any full documentation about SQLite commands supported by this embedded object into MAX?

Hi, is there any full documentation about SQLite commands supported by this embedded object into MAX?
thanks


Wow, thanks for this! I was thinking about this very issue, did a web search, and landed here. This should get me started!

Another good place to learn SQL is http://www.1keydata.com/sql/sql.html


Hey, very nice tutorial, however, the tool is not very useful as it does not update dynamically. After a file change in the directory, the database does not respond properly. How can this be changed?

This is so much more useful than all the new dict stuff, I can't believe that it is only supported as a "js hack"..

I got my databases in general working BUT!

Its a pain in the ass for debugging your SQL syntax. I never see an error message if a command is not correctly formatted.

This is my most demanded feature for any future version of Max: Add native objects to deal with sqlite and at least introduce a way to get error messages out of it somehow. js is still better than nothing...

This is so much more useful than all the new dict stuff, I can't believe that it is only supported as a "js hack"..
I got my databases in general working BUT!
Its a pain in the ass for debugging your SQL syntax. I never see an error message if a command is not correctly formatted.
This is my most demanded feature for any future version of Max: Add native objects to deal with sqlite and at least introduce a way to get error messages out of it somehow. js is still better than nothing...


I'm having trouble opening up a file-based database. Can anyone see if there is anything wrong with my syntax for this function?

sqlite.open("C:\Project\db-max5-patches\frames.db",1);

sqlite.open("C:Projectdb-max5-patchesframes.db",1);

Hello! You are referring to patches and files by name in the tutorial but I don't see these files, basic-db for example - where is this? Thank you for this wonderful tutorial.

Data Collection: Building Databases Using SQLite