The ROWID Field

A very important characteristic of Arca databases is that every table has an invisible field that is always present, the ROWID. This field is created, maintained and indexed automatically by the Arca database engine every time you create a table or add a record to it. The value of the ROWID is an unique number that identifies each row in the table. Remember that you can not set the value of the ROWID: it is set automatically by the database engine when a record is created. This value is not displayed in the Arca Database Browser, but you can always retrieve the value in your SQL commands, and use it as a temporary unique identifier for each row in a database. The Arca Database Browser for example always presents the table data sorted by ROWID, so older records are displayed first and new records are always created as the last record in the spreadsheet.

The tutorial files available in the Arca Database Xtra page use the ROWID extensively to keep track of current records inside a selection. Just keep in mind that the ROWID will change when a database file is compacted, so while useful for temporary identification of records it is not a substitute for a real INTEGER PRIMARY KEY field in more complex applications with multiple tables.

The INTEGER PRIMARY KEY type

Sometimes it is necessary to have an unique key that identifies each record on a table. The ROWID internal field is an option for temporary data, but it does not survive a compact operation and is also not exported to external .sql dumps, for example. For these cases you can declare a field of type INTEGER PRIMARY KEY in your table. The Arca Database Browser allows creation of INTEGER PRIMARY KEY field types easily, or you can use this type in the CREATE TABLE SQL statement directly.

Arca will automatically insert an unique integer incremental value every time you insert a new record using NULL as the INTEGER PRIMARY KEY field value. In this special case the value of the field can be subsequently retrieved by the getLastInsertRowID() function, for convenience. Here is an example session that you can try using the Arca Database Browser SQL query window, but of course you can issue the same sequence of SQL commands using the Arca Xtra and the executeSQL function:

CREATE TABLE mytable (id INTEGER PRIMARY KEY, name text, age numeric);

INSERT INTO mytable VALUES(NULL, 'John', 33);

INSERT INTO mytable VALUES(NULL, 'Mary', 24);

If you check the values in the browser window you will see that the ID field has the values 1 and 2 for John and Mary, respectively. You can also use the NEW RECORD button in the browser to add additional records, and they will automatically be created with an incremental value in the id field. The id value in this case is part of the table data, so it will survive a compact operation and even a dump/restore of the table data to an external file.

COLLATE LOCALE

This is a SQL clause used to retrieve data sorted in the order determined by the system locale. This is used in conjuction with the ORDER BY condition in the SQL query:

SELECT firstname, lastname FROM users ORDER BY name COLLATE LOCALE;

This is useful to display accented characters in the correct order when your program runs on a system set to use the German or French locale, for example. The system runtime strcoll function is used for the comparison.

Compatibility note: the COLLATE LOCALE operator is not functional on MacOS Classic systems and also when using the OSX version of the Arca Database Browser. In these systems no error will be returned, but data will continue to be sorted using the standard C locale. The function works correctly in the Arca Database Browser on Linux and Windows, and is also available in the Arca Database Xtra running under OSX and Windows.

Database Encryption

Arca 2 supports encryption of database files on disk using a strong algorithm. Data is encrypted and decrypted on the fly and automatically. Encrypted database files can not be read by users without the encryption key, and are not compatible with standard SQLite 3.x database files.

You can create an encrypted database using the Arca Database Browser or by passing an optional encryptionkey string to the createDB Lingo function, documented in the functions list. Once encrypted there is nothing on the database file that identifies it as an Arca database. To open an encrypted database you need to pass the encryption key string as the second parameter of the openDB function. You can also remove, add or change the encryption key for a database file at any time using the changeKey function in the Xtra, or the Arca Database Browser. Again, please consult the functions reference page for more information. Non-encrypted files can be exchanged with other users or other tools and engines that understand the SQLite 3.x database format.

Database Encoding

Arca databases by default store string data as Unicode characters, using UTF8 encoding. This makes it possible to interchange data with other database engines that also expect UTF8, including SQLite 3 standard files. Versions of Director older than Director 11 however do not understand Unicode, so the Arca Xtra translates strings automatically for you, depending on the platform it is running. By default Arca uses the #MACROMAN client encoding when translating data to Lingo on the Macintosh platform, and the #LATIN1 client encoding when translating string data on Windows. The default database encoding is UTF8. When running under Director 11 (both MacOSX and Windows) Arca will exchange data directly in UTF8 format, with no translation necessary.
Translation only applies to string variables in Lingo: all other media types are not translated. Also only single byte characters (including accented characters for western languages) are translated reliably on versions of Director before Director 11, as the encoding used by Director for double byte data can not be guessed for Unicode transformation.
For almost all applications there is no need to change the default encoding. However, if you are trying to use Arca to store double byte text in Director MX 2004 and earlier it might be necessary to disable the translation, so string data will stored exactly as passed by Lingo to the Xtra engine. The setEncoding function described in the functions reference page can be used to change the default translation used by the Xtra in both the Lingo (#CLIENT) or database (#DB) side. Please notice that changing the encoding does not change the actual data on the database file, only its translation to Lingo, and the translation used in future operations.

Note: the Arca Database Browser application also operates with UTF8 encoding as default. This setting can be changed in the PREFERENCES menu if necessary.

Compatibility note: older versions of Arca (pre-2.x) used #LATIN1 as the #DB side encoding, #LATIN1 as the client encoding on Windows and #MACROMAN as the client encoding on the Macintosh. If you are converting older databases that contain accented characters you may want to continue using these settings to avoid having to re-import your data.