ARCA DATABASE XTRA HELP: DATABASE STRUCTURE

Arca Databases are stored in a single file. The two main structures of a database are TABLES and INDEXES.

Tables are the structures that store your data in the database. Each table is composed of a number of FIELDS, also known as COLUMNS in some database engines. Suppose you want to store information about the users of your application: you could create a table named USERS, with the following fields: NAME, AGE and ADDRESS. Information about each user would be stored in what we call ROWS or RECORDS in the database. Your database table could be represented by the image below, containing records for 3 users:

<!--[if gte mso 9]> Normal 0 false false false EN-US ZH-CN X-NONE MicrosoftInternetExplorer4

It is important to learn that each field in the database has a TYPE associated with it. Common types are TEXT, NUMERIC and BLOB. Some database engines are very strict about the type of data that can be stored in a field, even limiting the number of characters by using types as CHAR[20]. Arca however uses a loosely typeless database engine, so the type information is only used as a hint about the contents of each field, to aid the conversion to Lingo types when you retrieve your data. It is entirely possible to store any kind of data supported by Arca in any field of the database, regardless of the declared TYPE for each field. You can even create your own types, or leave the type property blank when creating your tables. This is a feature inherited from the SQLite engine used in Arca. We however recommend that you use the TEXT type to store Lingo strings, the NUMERIC type to store floats and integers, and the BLOB type to store media and pictures (binary data.) This will make it easier for other developers working with your database to identify immediately which type of data was intended to be stored on each field, and it will give the Xtra a hint on how to convert the data back to Lingo. This can be important if you want to interpret numeric data as a string. Take for example the value "20.67". If you store this value on a field of type NUMERIC the Xtra will assume that you want to interpret it as a numeric Lingo value, and will return the float 20.67 in the results. If however you store the same information in a field of type TEXT the Xtra will return this result as a Lingo string, "20.67".

Field names and types are defined when you create a table. But in order to create a table you must first create the database file, of course. Database files can be created with the createdb function in the Arca Xtra, or the Arca Database Browser application. There is no need to specify a specific 3 letter extension for the database filename, but you may specify one if you want to. The following script will create an unencrypted database file named "mydata" in the same directory of the current Director movie:

Lingo:

gDB = new (xtra "arca")

gDB.createDB(the moviepath & "mydata")

JavaScript syntax :

gDB = new xtra ("arca")

gDB.createDB(_movie.path + "mydata")

Normal 0 false false false EN-US ZH-CN X-NONE MicrosoftInternetExplorer4

Arca 2 introduces support for encrypted database files. Files can be encrypted or decrypted at any time using the changekey function, but you can also specify an optional encryption key when creating the database:

Lingo:

gDB = new (xtra "arca")

gDB.createDB(the moviepath & "mydata", "mysecretkey")

JavaScript syntax :

gDB = new xtra ("arca")

gDB.createDB(_movie.path + "mydata", "mysecretkey")

Databases are empty when they are created: they have no tables with data stored, or indexes. It is possible to create tables and indexes using Lingo as well, but this requires knowledge of the SQL language. It is generally much easier to use the Arca Database Browser application to create your database and design the tables and indexes, however. For reference, you can create the users table described above using Lingo or JavaScript syntax with the following command:

gDB.executeSQL("CREATE TABLE users(name text, age numeric, address text)")

We however recommend using the Arca Database Browser application, as it provides wizards that help you define the table fields and types before creation. If you have not downloaded it yet, please get the Arca Database Browser from the DOWNLOAD section at http://xtras.tabuleiro.com. All screenshots of data contained in this guide were taken with it. The database browser tool is available in versions for Windows, MacOSX and Linux.

Creating a database with the Arca Database Browser is simple: just select the CREATE DATABASE menu entry. Choose a name and location for your database file. The browser will open the CREATE TABLE wizard, where you can define the name of your table, and click the ADD button to define fields and types.

The Arca Database Browser also lets you create indexes, with the CREATE INDEX button. Indexes do not store data, and you do not use them directly. They are used internally by the database engine to speed up certain search operations. In order to create an index you have to define the table and the field to be indexed, and the indexing order (Ascending or Descending). Indexes can also be UNIQUE, and in this case the indexed field does not allow duplicate data to be inserted in different records or rows (for example you could not have two employees with the same userid value if the userid field is being indexed as UNIQUE.) The figure below shows the Create Index window in the Arca Database tool:

Of course, you can create indexes using scripting as well. Remember that almost all operations in Arca can be done using the executeSQL command. The syntax to create the index above would look like this:

gDB.executeSQL("CREATE INDEX idx1 ON users(age ASC)")

For most Director applications however indexes are really not necessary: Arca is very fast and optimized, and it already maintains an internal index using a special property called ROWID (more on this later.) You may need to create an index however if your table contains lots of rows (10,000 for example), and you always search for data using an expression that will benefit from an index. In our example it would make sense to index the table using the AGE field if your application usually tries to find users that are older than 18, or present a list of users sorted by age.

A tip: the Arca Database Browser application also lets you see the database structure, including all tables and indexes, and also the SQL commands that were used internally to create them, as you can see in the figure below. You can study the SQL commands used (also called the schema of the database) if you want to recreate tables or indexes later using Lingo, or to learn a bit more of SQL.

An Arca database may have several tables and indexes, depending on your needs. All data is stored in the same database file, no matter how many tables you use. Let's now check how to insert, delete and update data in your databases.