Database Programming

The mscript runtime has dedicated function calls for doing database programming. These functions constitute a simple subset of SQL and NoSQL database programming.
define
This is the function to start with. It establishes a table of data, what the key value for the new row in that table in the database, and what column data there is for the new row.
For the purposes of this documentation, we will work with colors. Not too many colors, just red, green, and blue. Here are the calls to define to get the colors into the systems:
* msdb.define("colors", "red", index("description", "like a rose", "RGB", "255,0,0"))
* msdb.define("colors", "green", index("description", "like a leaf", "RGB", "0,255,0"))
* msdb.define("colors", "blue", index("description", "like the sky", "RGB", "0,0,255"))
The first call to define creates the table "colors". That same first call creates a row in that table with the value for the row, the key, of the color name, "red". The index defines the column data for the colors, in this case descriptions and color values. The second and third statements define the other two colors as two more rows in the colors table.  The define function does UPSERT's, call it again with the same color and a different description, you do not get a separate row, you would get a modified description.
selectValue, selectList, selectIndex, and selectRecords
These function are used for access the data in the database. They use a simple subset of the SQL database language, namely the SELECT statement. 

There are significant restrictions place to make parsing the query easier.  For me!  You can select whatever columns you want, but you can only select FROM one table, no joining multiple tables.  Any criteria you put in the WHERE part, the values you are checking against must be parameters, no literal values.  You can ORDER BY all the columns you want, but the columns you order by must exist in the list of columns in the SELECT part, and you have to say ASC for ascending or DESC for descending.

To print all the color names out and print them, you would get selectList like so:
@ curColor : msdb.selectLine("SELECT value FROM colors")
    > curColor
}
To get the description of "blue" you would call selectValue like so:
> msdb.selectValue("SELECT description FROM colors WHERE value = @blue", index("@blue", "blue"))
This would output "like the sky"
To get an index of the color names and descriptions, you would call selectIndex like so:
$ colorDescriptionIndex = msdb.selectIndex("SELECT value, description FROM colors ORDER BY value")
@ curColor : colorDescriptionIndex.keys()
    $ curDescription = colorDescriptionIndex.get(curColor)
    > curColor + ": " + curDescription
}
This would output:
blue: like the sky
green: like a leaf
red: like a rose
To extract all data from the table, you would call selectRecords like so:
@ curRecord : msdb.selectRecords("SELECT value, description, RGB from colors ORDER BY value ASC")
    $ statement = curRecord.get("value")
    & statement = statement + ": " + curRecord.get("description")
    & statement = statement + " - " + curRecord.get("RGB")
    > statement
}
This would output:
blue: like the sky - 0,255,0
green: like a leaf - 255,0,0
red: like a rose - 0,0,255
And that's it for accessing data in the database.
delete
To remove rows from a table, you call the delete function passing in the keys of the rows to delete:
* msdb.delete("colors", list("green", "blue"))
drop
Finally, if you need to start from scratch with a table, you use the drop function:
* drop "colors"
There's no undo, so take care.  
That's it for the mscript programming system.  A strange programming language with dedicated syntax and runtime for learning database-driven website development.  Have fun!

%d bloggers like this: