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.
var sqlite = new SQLite;
var result = new SQLResult;
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.
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.
//insert filename and filetype
exec("INSERT INTO dumpster ('name','filetype') VALUES ('"+arguments+"','"+arguments+"')");
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.