Getting Started with ScriptDB

You can find all the sample scripts discussed in this manual in the folder "Getting Started Scripts".

Each section indicates the name of the script that is relevant to that section.

At the heart of Script DB lies a data class we call ScriptDB. ScriptDB is like a record that contains everything the scripting addition needs in order to perform database operations.

The ScriptDB class has three properties: DBName, DBFields and DBData.

DBName

The DBName property is a string that contains the name of the database.

DBFields

The DBFields property contains a list of database fields. Each field is specified according to the ScriptDB Field class. It has a field name and a field type. The name is a string and the type is simply one of AppleScript's data classes. Currently ScriptDB Field support the following field types: String, Unicode, Real, Integer, Date and Boolean.

A typical field may be specified as follows: {"field name", string}. Once compiled, however, the ScriptDB class turns into a record: {DBFieldName: "field name", DBFieldClass: string}.

DBData

The DBData property contains the actual data. The DBData property is simply a list of lists. Each list inside DBData is a record in the database.

In order to make ScriptDB commands both easy to use and not conflict with other commands, we decided to start all of them with "DB". It may sound a bit funny at first, but it will grow on you. We also tried to make the commands sound as descriptive as possible. For instance, to sort a database, you use:

DB sort db the_script_db by the_sort_criteria

Since ScriptDB doesn't use a file to store the database, the ScriptDB class is all you have. For that reason, most commands will accept a ScriptDB as one of the parameters and return a new ScriptDB result. This means that if you want to sort a database stored in the variable my_db, for instance, you will use the following command:

set my_db to DB sort db my_db by {"first name"}

Or, to add records to a database, you will use:

set my_db to DB add records the_records_list to db my_db

This is a bit like doing this: set x to x + 1. You assign the result of an 0operation to a variable that is also used as one of the operands.

There are two ways to create a database: from file or from scratch. If you create a database from file, a single DB load command will supply both the fields and the data information. If you create a database to which you want to add records later on from the script itself, you can start by specifying the name and fields of the database, or you can also use the default DB name and start an empty database with no fields..

You can always add or remove fields after the database is created.

To create an empty database with the default name and a single field, use the db create command:

set my_db to DB create

Result:

my_db =

{class:ScriptDB, DBName:"SCRIPT_DB", DBFields:{{DBFieldClass:string, DBFieldName:"Field1"}}, DBData:{}}

Notice that the name used for the database is "SCRIPT_DB", and that there's a single field named "Field1" with a string class.

You can also start a database by specifying a name and fields. In the command below we create a database with the name "contacts" and with no fields:

set my_db to DB create with properties {DBName:"contacts", DBFields:{}}

Result:

my_db =

{class:ScriptDB, DBName:"contacts", DBFields:{}, DBData:{}}

Noticed that both the DBFields and DBData properties are empty lists.

We could start with a few fields, though. We will repeat the command but this time with an empty list for each field:

set my_db to DB create with properties {DBName:"contacts", DBFields:{{}, {}, {}}}

Result:

my_db =

{class:ScriptDB, DBName:"contacts", DBFields:{{DBFieldName:"Field1", DBFieldClass:string}, {DBFieldName:"Field2", DBFieldClass:string}, {DBFieldName:"Field3", DBFieldClass:string}}, DBData:{}}

Now, ScriptDB used its defaults: The fields are all of type string and are named "Fields1", "Field2", etc.

The following DB create command starts a database with two fields: name and age. This time, however, we will setup the fields earlier in a variable:

set my_fields to {{"name", string}, {"age", integer}}

set my_db to DB create with properties {DBName:"contacts", DBFields:my_fields}

Result:

my_db =

{class:ScriptDB, DBName:"contacts", DBFields:{{DBFieldName:"name", DBFieldClass:string}, {DBFieldName:"age", DBFieldClass:integer}}, DBData:{}}

Field names can't start with a dash or with a number, only an alpha character.

Next, we will add a few records to your database.

We will start by adding a single record. To do that, we will build on the contacts database we created above.

Add this line to the end of the script:

set my_db to DB add record {"George", 45} to db my_db

Result:

my_db =

{class:ScriptDB, DBName:"contacts", DBFields:{{DBFieldName:"name", DBFieldClass:string}, {DBFieldName:"age", DBFieldClass:integer}}, DBData:{{"George", 45}}}

To add a few (or many) records at the same time, use the db add records command with a list of lists. We will do that by first creating the list and assign it to a variable, and then add the list in the variable, like that:

set my_records to {{"George", 45}, {"Beth", 24}, {"Jack", 30}}

set my_db to DB add records my_records to db my_db

In reality, you will probably have your script create the records list.

You can easily get the number of fields or records in a certain database with these two commands:

set record_count to DB count records of db my_db

result: 3

set field_count to DB count fields of db my_db

result: 2

Now that we have a database with a few records we can sort them.

To sort the database we use the DB sort command followed by the field or fields you want to sort by..

You can specify the field you want to sort by using either the field name or number. You can also specify more than one field and specify whether you want the records sorted in ascending or descending order.

To sort the database stored in the my_db variable by the field "age", add the following line to your script:

set my_sorted_db to DB sort db my_db by "age"

If you want to sort the database by one record first and then by another one, simply provide a list of the records:

set my_sorted_db to DB sort db my_db by {"age", "name"}

You can use the field numbers instead of their names:

set my_sorted_db to DB sort db my_db by {2, 1}

Sorting in descending order

To sort in descending order, add a minus sign before the field name or number:

set my_sorted_db to DB sort db my_db by {-2, 1}

set my_sorted_db to DB sort db my_db by {"-age", "name"}

Notice that the minus is inside the quotes.

There are a couple of ways to specify which records you want to remove from the database. The easiest way is by specifying the record number.

We will start from the eight-record database created in the sample script Deleting Records.

Specifying records in a list

To delete records by specifying numbers, use the 'number' parameter with the DB delete records command. In the following script we will delete record number 2:

set my_smaller_db to DB delete records from db my_db number 2

And here, we delete records 2 and 5 by using a list:

set my_smaller_db to DB delete records from db my_db number {2, 5}

Deleting record ranges

But, what if we want to delete records 2 through 5. Well, it would be nice to use the familiar AppleScript'thru' word, but unfortunately it can't be used in scripting additions. Instead, however, ScriptDB uses a string that allows you to mix non-consecutive ranges in a single delete command.

To delete records 2 through 5, use the following command:

set my_smaller_db to DB delete records from db my_db number "2-5"

If you really want to get fancy, you can mix commas and dashes to delete a few ranges at a time:

set my_smaller_db to DB delete records from db my_db number "1-3,5,7-8"

The script above deletes records 1, 2, 3, 5, 7 and 8

The nice thing here is that you can use AppleScript to create the 'number' parameter for you.

Specifying delete criteria

What if you don't know the numbers of the records you want to delete? For that, you can define the criteria you want ScriptDB to use for determining which records will be deleted.

ScriptDB uses a sophisticated parameter to allow you to pinpoint the records you want to delete. This 'where' parameter is the same one used to find records with the DB get data command, and is explained there in more detail.

Here are a couple of examples you can try.

The script below will delete all the records where the field "name" is "Olivia":

set my_smaller_db to DB delete records from db my_db where {"Field(name)", "=", "Olivia"}

The script below will delete all records where the field "age" is greater than 13

set my_smaller_db to DB delete records from db my_db where {"Field(age)", ">", "13"}

You can update a record in a database using the record number and the new data for the record.

The following line will replace the second record in the my_db database with the record indicated after the 'with data' parameter:

DB update record 2 in db my_db with data {"Beth", 25}

To find records in a database you use the DB get data command.

This command can return one of three types of results: A database, a list of lists or the record numbers of the found fields.

There are a few ways for you to specify which data you want to get. You can restrict the search to a number of records, specify the fields you want to get data from, use a complex 'where' parameter, or a mix of any of the above.

We will start with a simple example.

The script below assumes that the variable my_db has the eight-record database we looked at earlier. This database has two fields: name and age.

You can find these examples in the 'Find and Get Data' example script.

Getting a range of records and fields

The scripts below allow you to get data from specific fields and specific record ranges.

set my_data to DB get data from db my_db in records "3-6"

result: {{"Jack", 30}, {"Olivia", 10}, {"Jordan", 15}, {"Sophia", 27}}

Now, we will just get the data from a certain field:

set my_data to DB get data from db my_db fields "age"

result: {45, 24, 30, 10, 15, 27, 3, 13}

Now, we will mix the two. We will get the field 'age' from records 3 through 6:

set my_data to DB get data from db my_db in records "3-6" fields "age"

result: {30, 10, 15, 27}

You can also use a number or list of numbers to specify the fields you want to use:

set my_data to DB get data from db my_db in records "3-6" fields {1, 3}

Transposing data

If we look at the result we got from the first DB get data command, it looked like this:

{{"Jack", 30}, {"Olivia", 10}, {"Jordan", 15}, {"Sophia", 27}}

Four lists, each is a record that has two items.

But what if instead, we want two lists, each with four items. We want a field's content in every list, not a record's content. All we have to do is include the 'transposed' parameter. By default, the transposed parameter is false.

Here's the command with the transposed parameter and the result you get:

set my_data to DB get data from db my_db in records "3-6" with transposed

result: {{"Jack", "Olivia", "Jordan", "Sophia"}, {30, 10, 15, 27}}

Using find criteria

Until now we just got data based on records and fields that we knew of in advance. Using the 'where' parameter you can specify real database searches.

The values you use in the 'where' parameter may appear to be a bit confusing at first. The reason for their structure has to do with both the scripting addition code restrictions and our strive to create the most open format that will allow you to create search parameters on the fly using AppleScript.

OK now, enough explaining, lets get going.

The following DB get data command will look for the record where the field "name" has the value "Olivia":

set my_data to DB get data from db my_db where {"Field(name)", "=", "Olivia"}

result: {"Olivia", 10}

But what if all we want is Olivia's age? Here's how we do that:

set my_data to DB get data from db my_db fields "age" where {"Field(name)", "=", "Olivia"}

result: {10}

Next, we will get the names of all people in our database who are between the ages of 10 and 18:

set my_data to DB get data from db my_db fields "name" where {"Field(age)", "³", 10, "AND", "Field(age)", "²", 18}

result: {"Olivia", "Jordan", "Harry"}

Getting the results in other forms

By default, the DB get data command will return a list of lists. You can use the 'as' parameter to request the result as a ScriptDB database or as a list or record numbers. Example:

set my_data to DB get data from db my_db where {"Field(name)", "=", "Olivia"} as row numbers

result: {4}

set my_data to DB get data from db my_db where {"Field(name)", "=", "Olivia"} as database

result:

{class:ScriptDB, DBName:"contacts", DBFields:{{DBFieldName:"name", DBFieldClass:string}, {DBFieldName:"age", DBFieldClass:integer}}, DBData:{{"Olivia", 10}}}

To add fields to a database you need to supply the name and data type of the field you want to add. You may also supply the data for the new field in a form of a list and the default value to be used for unsupplied data.

The sample below can be found in the Adding and Removing Fields sample

Adding a field

In the first example we will add a 'member' field of type Boolean.

set new_field_specs to {"member", boolean}

set my_db to DB add field new_field_specs to db my_db

result:

{class:ScriptDB, DBName:"contacts", DBFields:{{DBFieldName:"name", DBFieldClass:string}, {DBFieldName:"age", DBFieldClass:integer}, {DBFieldName:"member", DBFieldClass:boolean}}, DBData:{{"Beth", 24, false}, {"Jack", 30, false}, {"Olivia", 10, false}, {"Pikachu", 3, false}, {"Harry", 13, false}}}

Notice that the default data used is false. To change the default, use the 'using default' parameter:

set my_db to DB add field new_field_specs to db my_db using default {true}

Note that the value 'true' is in a list just to prevent if from being seen as a Boolean value but rather as any value.

Using defaults is OK if we don't have the data, but if we do, we can use it right away.

Lets assume that we have a list of Booleans that we want to use as the data for the field:

set member_data_list to {true, true, false, true}

set my_db to DB add field new_field_specs to db my_db with data member_data_list

Removing a field

To remove a field we have to specify the field's name:

set my_db to DB remove field "member" from db my_db

ScriptDB allows you to load databases from files and to save databases to files. ScriptDB uses two ASCII-based file formats for saving and loading databases: tab delimited text and XML.

For saving and later loading any database you create using AppleScript, you will want to use XML. When saved as XML, a database retains all of its attributes, including field names, field types and database name.

The tab-delimited format is mainly good for importing exiting text files and for greater compatibility with other applications.

The XML format is based on FileMaker Pro XML format. This allows ScriptDB to load FileMaker databases that were exported as XML, and for FileMaker Pro to import or open databases created with ScriptDB.

The script below will create a database, save it to a file, and later load it to another database:

-- create the database

set my_fields to {{"name", string}, {"age", integer}}

set my_db to DB create with properties {DBName:"contacts", DBFields:my_fields}

-- add records

set my_records to {{"Beth", 24}, {"Jack", 30}, {"Olivia", 10}, {"Pikachu", 3}, {"Harry", 13}}

set my_db to DB add records my_records to db my_db

-- save to file

set db_file_path to (path to desktop) & "database.txt" as string

DB save my_db to file db_file_path

The database is saved as XML by default. The contents of the XML file looks like this:

<?xml version="1.0" encoding="UTF-8"?><FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"><ERRORCODE>0</ERRORCODE><PRODUCT BUILD="02/24/2004" NAME="Script DB" VERSION="1.01"/><DATABASE DATEFORMAT="yyyy/MM/dd" LAYOUT="" NAME="contacts" RECORDS="5" TIMEFORMAT="hh:mm:ss"/><METADATA><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="name" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="age" TYPE="NUMBER"/></METADATA><RESULTSET FOUND="5"><ROW MODID="1" RECORDID="1"><COL><DATA>Beth</DATA></COL><COL><DATA>24</DATA></COL></ROW><ROW MODID="1" RECORDID="2"><COL><DATA>Jack</DATA></COL><COL><DATA>30</DATA></COL></ROW><ROW MODID="1" RECORDID="3"><COL><DATA>Olivia</DATA></COL><COL><DATA>10</DATA></COL></ROW><ROW MODID="1" RECORDID="4"><COL><DATA>Pikachu</DATA></COL><COL><DATA>3</DATA></COL></ROW><ROW MODID="1" RECORDID="5"><COL><DATA>Harry</DATA></COL><COL><DATA>13</DATA></COL></ROW></RESULTSET></FMPXMLRESULT>

ScriptDB's XML format is compatible with FileMaker pro and therefore FileMaker Pro can open or import databases exported from ScriptDB as XML.

Exporting to XML requires the pro version of ScriptDB.

We could also choose to save the database as a tab delimited file, by using the as parameter, like this:

-- create the database

set my_fields to {{"name", string}, {"age", integer}}

set my_db to DB create with properties {DBName:"contacts", DBFields:my_fields}

-- add records

set my_records to {{"Beth", 24}, {"Jack", 30}, {"Olivia", 10}, {"Pikachu", 3}, {"Harry", 13}}

set my_db to DB add records my_records to db my_db

-- save to file

set db_tab_file_path to (path to desktop) & "database.txt" as string

DB save my_db to file db_tab_file_path format txt

-- load tab delimited to another database

set my_fields to {{"name", string}, {"age", integer}}

set new_db_2 to DB load file db_tab_file_path with properties {DBFields:my_fields} format txt

Notice that when we loaded the tab delimited text file back into a database, we redefined the database properties. If we wouldn't have done that, ScriptDB would have just used the default fields names (Field1, Field2, etc.) and assigned them all a text type.

When you save and load XML, all the settings are there for you.

Sample scripts

The ScriptDB class

DBName

DBFields

DBData

Creating a database

Adding records to the database

Counting records and fields

Sorting records

Sorting by one field

Sorting by multiple fields

Sorting using field numbers

Sorting in descending order

Deleting records

Specifying records in a list

Deleting record ranges

Specifying delete criteria

Updating a record

Finding records

Getting a range of records and fields

Transposing data

Using find criteria

Getting the results in other forms

Adding and removing fields

Adding a field

Removing a field

Loading and saving database files