Mustard Xtra Documentation

Developed by

SmartPants Media Inc.

© 2001 SmartPants Media, Inc.

Purpose:

The purpose of this Xtra is to allow developers to run predefined SQL commands from a remote director application using the Multiuser Server. In designing this xtra, we have done everything possible to ensure a flexible, yet secure solution.

Download Folder:

The default download of the MUSTARD xtra contains the xtra itself, this documentation, the MUSTARD license agreement, a sample multiuser.cfg file, and a director file containing the source code for the MUSTARD Tool. You will use the MUSTARD Tool for adding MUSTARD handlers to your database, so that you can easily perform limitless SQL commands. You may find it useful to make the MUSTARD Tool into a projector.

Methods:

Using the MUSTARD xtra is designed to be intuitive, flexible, and easy. There are only two methods: System.MustardODBC.Get and System.MustardODBC.Set. Which method you use depends on whether you are reading or writing data (deleting data, constitutes a writing method).

You don't pass SQL queries directly to the multiuser server. Instead, all queries are predefined. Essentially, you create unique MUSTARD handler names, which are all stored in a special table within your database (MUSTARD will automatically create these tables and add them to your database on the first run). Each MUSTARD handler is associated with a query, as well as a list of parameters. To perform a SQL query, you would simply call this handler and pass in the parameters as a property list. The reason this strategy is employed is that if we left the xtra open to accept any SQL query, an unethical hacker could wreak havoc on your database. By predefining handlers (as well as restrictions to these handlers), we have put tangible limits on what functions can be performed on your database.

For example, let's say you have a database of employees, and you wish to add a function to your director application in which you select an employee record that matches the field employeeID. To do this, you would need the SQL query, Select * from employee where employeeID = (parameter to be passed in). To add this, you would create a MUSTARD handler called "getemployee" and associate it with the aforementioned SQL query. You would also need to define the variable that will be passed in, so that MUSTARD can figure out where to place this parameter in the query.

Fortunately, the MUSTARD authoring tool takes care of these details for you. To begin, launch the MUSTARD Tool, either by opening the Director file, or launching the MUSTARD Tool application. Before you begin using the tool, you must first fill-in the necessary forms located at the bottom of the screen in the Main Menu. The most important field is the "Multiuser Server IP Address" field. Enter the IP address at which your multiuser server is located. To connect to the multiuser server, click the Reconnect Button. A series of handler names should appear in the Handlers window. To add a new handler, simply click on the Add Handler button. A form appears in which you can add the name of the handler, the SQL query, and any restriction you wish to specify. The MUSTARD authoring tool parses the information you enter here into the format that MUSTARD requires.

NOTE: The pre-defined MUSTARD handlers that the MUSTARD Tool uses to create and get handlers, use the MUST_SYS_AUTH level of security. In order for the MUSTARD Tool to work at all, you will need to verify that the MUST_SYS_AUTH name and password in the MUST_NAME and MUST_PASSWORD fields on the Main Screen of the MUSTARD Tool match the DefaultUsername and DefaultPassword values defined in the multiuser.cfg file (see Installation below).

So, to create our handler, enter "getemployee" in the field marked handler name. Now enter the following for the SQL Query:

Select * from employee where employeeID = $employeeID

Note the "$" symbol before the last word "employeeID" This tells the parser that this is the name of the variable to be passed into the handler. The first instance of employeeID is simply the field name. Be careful not to confuse these two items.

Ignore the last two fields for now, and click the submit button. You will be brought back to the main screen containing the list of all handlers. You should see the new handler, "getemployee" at the bottom of the screen.

Now, to execute the query, you need to connect to the multiuser server and send a message to the server with the appropriate handler name and parameters:

sendNetMessage("System.MustardODBC.Get", "getemployee", [#employeeID: 5])

Here we have sent a message to the multiuser server, and invoked the method "Get" since we are performing a database selection. The second parameter is simply a property list containing each variable name and its corresponding value. In this case we only have one variable, which is employeeID, and its value of 5.

Security:

Security was a big concern for us when developing the MUSTARD xtra. We have designed a system which has the capability of being as secure as you want it to be. We have 2 levels of security: System Authentication ("MUST_SYS_AUTH") and "Restriction Queries."

The first level of security, System Authentication, necessitates that all database queries require a username and password to be passed to the multiuser server. If the username and password don't match what is defined in the multiuser config file (in the MUSTARD section of the config file), the query will not execute. To pass in the MUST_SYS_AUTH username and password, you must include the properties: #must_user and #must_password in the property list that you send with the other variables required by your MUSTARD handler. This level of security is simple, and allows developers to protect their data with simple authentication.

The second level of security, called "restriction queries," is a system in which you set up a series of queries which are executed first, and which must return a valid result (i.e. not NULL) in order for the actual query to be run. For example, you might want to run the query "select * from grades where userid = $user." In order for this query to execute, one or more predefined restriction queries would have to be executed first, and most importantly, would have to return valid data. For instance, you might have to pass an access query like:

Select * from users where username = $user and password = $password

The system also allows you to stack access queries so you may have three access queries associated with one query. Basically, this system will allow you to protect your data, so that any user does not have global read or write privileges for data. Imagine an educational program where each student had access to every other student's grades! By using restriction queries, you can easily prevent such a security flaw.

Of course, if you don't need any restriction or security in your application, you can forego any of these measures. In this case, you simply specify a handler, pass in parameters via a property list, and off you go.

Adding Restriction Queries using the MUSTARD Tool:

If you wish to add a Restriction to a particular SQL Query, you must first define the Restriction, and then attach this Restriction to a MUSTARD handler. To do this, click "Add Handler" from the Main Menu of the MUSTARD Tool. When the Add Handler screen appears, click "Add Restrictions."

You will arrive at the Add Restriction Screen, containing two forms: Name and Restriction SQL. Enter the name of the Restriction Command in the Name field, and the SQL Command in the Restriction SQL field. The SQL command must be formatted in the same manner as the MUSTARD handler SQL commands are formatted (as described above). When you are done, click Submit Restriction.

You will be returned to the Add Handler screen, but you will now notice that the Restriction you just added now appears in the Restriction Queries field. You can continue adding restrictions, by clicking the Add Restrictions button. All available Restrictions will always appear in the Restriction Queries field. If you wish to add a particular Restriction to a MUSTARD handler, simply double-click on the text of the Restriction. The Restriction will now appear in the Selected Restriction Queries field. You can add as many Restriction Queries as you need.

If you wish to use the MUST_SYS_AUTH strategy of security instead of a Restriction, simply click on the Mustard Auth button. This will specify that your handler will require a valid MUSTARD username and password (as specified in the multiuser.cfg file in the MUSTARD section) in order for the handler to be executed. You can not use MUST_SYS_AUTH and restriction queries at the same time.

Performing Inserts and Updates:

Performing Inserts and Updates is done in a very similar fashion to Select statements. The biggest difference is that you will need to use the command:

System.MustardODBC.Set instead of the Get command.

There are also slight differences in the SQL syntax. For instance, when performing an Insert, you will need to enclose the variable names and field names that will receive an insert in parenthesis. For example, if you wish to insert employeename and job into the table employee, you would use:

INSERT INTO employee (employeename, job) VALUES ($employeename, $job)

Again, note the use of the dollar sign ($) which denotes that the subsequent characters are a variable name. You should not use the dollar sign when passing variables into your MUSTARD handlers. The dollar sign character is only used by the MUSTARD Tool as a means of separating variable names from field names. In the above example, the first items in parenthesis denote the field names "employeename" and "job."

Performing updates are more similar to Select syntax. If you wanted to update the field job in the table employee for a given employeeid, you would create a MUSTARD handler with the following SQL syntax:

UPDATE employee SET job = $job where employeeid = $employeeid

Deleting rows from a table also requires that you use the System.MustardODBC.Set command. To delete an employee record from the employee table, you would use the following SQL syntax in your MUSTARD handler:

DELETE from employee where employeeid = $employeeid

Executing MUSTARD Handlers from the MUSTARD Tool:

As mentioned earlier, in order to execute a MUSTARD handler, you will need to first connect to the multiuser server, and then issue the proper command. If you wanted to execute a MUSTARD handler named "getemployee" you would use the following syntax in your director application:

sendNetMessage("System.MustardODBC.Get", "getemployee", [#employeeID: 5])

If you wanted to execute a MUSTARD handler called "addemployee" you would use the following syntax in your director application:

sendNetMessage("System.MustardODBC.SET", "addemployee", [#employeename: "Fred", #employeetype: "developer", #employeesalary: 60000])

Obviously, the parameters that are passed into the MUSTARD handler depend on your query and what variables it requires. You may also have additional variables that you will need to pass in that are required by a Restriction Query.

The MUSTARD Tool, can also be used to perform simple queries. To access the Execute Query Screen, double-click on a handler form the Main Menu. The Execute Handler Screen will appear, containing the Handler name, as well as several fields in which you can enter "test values" for the variable names that you wish to pass into your handler. The MUSTARD Tool will automatically pass in the values of MUST Name and MUST Password entered on the Main Menu screen to authenticate any handler that uses the MUST_SYS_AUTH security scheme. However, you can not use the Execute Handler feature to execute handlers that utilize access queries or that contain more than four variables. These features will be added in successive versions of the tool.

To execute a MUSTARD handler, simply fill in sample values in the appropriate fields (if applicable) and click either the Execute Select or Execute Insert button (depending on whether your query is a read or write operation). The response to your query should appear in the message window.

Data that is selected will be contained in the Content property of the returned message. So, if you executed a handler named "getemployee," you might get a response similar to the following:

[#errorCode: 0, #recipients: ["paul"], #senderID: "System.MustardODBC.Get", #Subject: "getemployee", #Content: [[#employeename: "Fred", #employeesalary: 60000]], #timeStamp: 591680812]

It is important to note that the Content Property actually contains a list of property lists. So, if your MUSTARD handler returns only one record, you will have to get the first item in the list of the property Content. Handlers that return multiple records, will produce a content property that contains multiple property lists.

To access the first record in your data (assuming it is stored in the variable message), you can use the following lingo syntax:

FirstRecord = message[#Content][1]

This will assign the variable FirstRecord a value of: [#employeename: "Fred", #employeesalary: 60000]

Installation:

To get this Xtra running, you must place the included "Mustard.x32" file in the "Xtra" subdirectory of the Multiuser Server.

Pre-conditions: you must have a database created that you will be using to store all the information the application is going to use. The xtra will also use this database to store its own configuration information.

You must set up an ODBC entry for the database. In order to use this with MySQL you will have to get the MySQL ODBC driver from www.mysql.com and install this on your computer.

To begin, you must edit your "multiuser.cfg" configuration file to use

the new Xtra. You may find it helpful to copy parts of the sample multiuser.cfg file contained in the MUSTARD download.

Find the line that starts with "ServerExtensionXtras =", and add "MustardODBC"

This Xtra also requires the following configuration options to be set up:

Database <name>

RetryInterval <time>

The following must also be added to the configuration:

XtraCommand = "localhost" #where localhost is the IP address of the multiuser server

XtraCommand = "Username mus_db" # defines the username required to connect to DB

XtraCommand = "Password mus_db" #defines the password required to connect to DB

XtraCommand = "DefaultUsername mus_db" #username for MUST_SYS_AUTH

XtraCommand = "DefaultPassword mus_db" #password for MUST_SYS_AUTH

XtraCommand = "Database mus_db" ` #Database name from ODBC entry

XtraCommand = "System.MustardODBC.Get 0" # 0 specifies minimum user level

XtraCommand = "System.MustardODBC.Set 0"

From within your application you must call the Get function for all "SELECT" queries. Everything else will use the Set Function. When issuing the command you should supply the name of the query you want to run in the subject field. You should then supply the variables if any in the msg content area. The message content should be sent through as a property list. You must supply all the variable the query need as well as any the restriction queries might need. If one of the restriction queries and the main query require the same variable you need only specify it once.

MUSTARD Tables:

This chapter describes the inner-workings of the MUSTARD xtra. It might prove useful to browse through this section in order to better understand how MUSTARD stores information about the handlers, SQL commands, variables, and restriction queries in the database. However, because the MUSTARD Tool automates the insertion of these elements for you, it is not necessary to learn these finer details. Nevertheless, we have included the following section in case you wish to add handlers directly to the database, or if you wish to make your own enhancements to the MUSTARD Tool.

MUSTARD requires two tables that are automatically added to your database on the first run: must_query and must_restrictionquery. For every handler you create, a new entry will be added to the must_query table. Restrictions to SQL commands are optional; these are added to the mus_ restrictionquery table.

The must_query table contains the following fields:

Id . . . . . . . . . . . . . . . . int

name. . . . . . . . . . . . . .varchar(255)

query. . . . . . . . . . . . . varchar(255)

varlist. . . . . . . . . . . . .varchar(255)

accessqarray. . . . . . . varchar(255)

The following is a description of each field

Id: unique id number of the MUSTARD handler.

Name: name of the MUSTARD handler.

Query: SQL command to be executed by handler.

Varlist: list of variables (in their proper order) to be used in SQL command.

Query: The SQL command to be executed is placed in the query field. You must use question marks in place of dynamic variables. For example, to select all records from the employee table that match a particular employeeid, you would use:

Select * from employee where employeeid = ?

Varlist: The Varlist field lists all of the variables of an SQL command, in the order in which the appear in the command. For instance, in the above example, we would use a varlist of:

employeeid;

It is important to remember that you must terminate each variable name with a semicolon. Also remember that MUSTARD matches which variable refers to which question mark by the order of the variable in the varlist field. In other words, say you use a query that has two variables such as:

SELECT * FROM user WHERE userid = ? and password = ?

It is critical that your varlist is ordered as such:

Userid;password;

If you specified the varlist in the opposite order, MUSTARD would assume that the query was backwards, like:

SELECT * FROM user where userid = $password and password = $userid

Accessqarray: This field should contain a semicolon delimited list of accessids. AccessIds are simply an ID number from the must_restrictionquery table. To add three access (or Restriction) queries for your handler, you can simply specify a semi-colon delimited list of the accessIDs you wish to use (i.e. 3;6;9;).

Upon making a call to this query, all of the access queries identified in this list will be run. In this list you can also add the reserved word "MUST_SYS_AUTH" which will then authenticate against the user/password definition in your multiuser.cfg file (see above). To specify the MUST_SYS_AUTH name and password in your queries, you must pass along the properties (and their corresponding values) #must_user and #must_password with the rest of your variables for a particular MUSTARD handler.

The must_restrictionquery (Restriction Table) has five fields which are:

accessid. . . . . . . . . . . . . . int

name. . . . . . . . . . . . . . . . varchar(255)

query. . . . . . . . . . . . . . . .varchar(255)

status. . . . . . . . . . . . . . . .int

varlist . . . . . . . . . . . . . . varchar(255)

The name field defines some short name to help you remember what the query is about.

Query: The SQL command to be executed is placed in the query field. You must use question marks in place of dynamic variables. For example, to select all records from the employee table that match a particular employeeid, you would use:

Select * from employee where employeeid = ?

Status: not currently used, but is reserved for future versions.

Varlist: all of the variables for the query in the order the statement needs them. The varlist must be a semicolon-delimited list of variables (refer to example below)

A query might be defined as:

SELECT * FROM user WHERE userid = ? and password = ?

Or

INSERT INTO user (userid, password) VALUES (?,?)

For both of the above queries the varlist would be:

Userid;password;

NOTE: It doesn’t matter what the names are, so long as in your application you use the same name you use here.

Additional Features:

There is a reserved property called #must_id to assist in a series of asynchronous commands. If you include #must_id (and a corresponding value) along with your variable property list when sending a MUSTARD handler to the multiuser server, you will receive the #must_id property and the value attached to it when your query response is returned. The benefit of this feature is that it will allow you to track multiple queries that use the same handler name. All you will need to do is include a unique identifier for the #must_id property:

SendNetMessage("System.Mustard.ODBC", "getemployee" [#employeeid: 5, #must_id: 3213]

When the response is returned, you will find the #must_id property along with the rest of the returned properties and values:

[#errorCode: 0, #recipients: ["paul"], #senderID: "System.MustardODBC.Get", #Subject: "getemployee", #Content: [[#employeename: "Fred", #employeesalary: 60000, #must_id: 3213]], #timeStamp: 591680812]

Known Limitations:

This xtra is designed to work with any ODBC-compliant database. Currently, we have done extensive testing with MySQL, Access, and SQLServer. Please forward any issues you find with other ODBC-compliant databases.

The final version of this xtra will support all Director types (i.e. pict, flash, etc.) Currently, the only officially supported Director types are: string, integer, list, property list, and rect. If you are using a Director type (such as a list), the field in your database that will store the type must be a binary field.

We appreciate your feedback and your support. If you have any ideas on how we can improve the xtra, the MUSTARD Tool, or this documentation, please email xtras@smartpants.com.