Important information for the new SQL desktop database beta

What is a desktop database good for?

Features introduced with the new SQL desktop database

Installation

Volume conversion to the new SQL desktop format

Volume restore to the old desktop format

Performance

Online backup copies of a live volume (sample script)

Re-indexing the desktop database

New preferences

Changes for beta 1

Changes for beta 3

SQL desktop database engine

Known issues

Feedback

Prerequisites

Although we are pretty sure that everything works as expected, it is recommended to backup the current desktop database before testing the new SQL desktop database beta.

The prerequisites for this beta are:

• A HELIOS UB based installation
• Reserved for service customers who own a HELIOS Software Upgrade Service Agreement (SE002 or SE003)
• All volumes must be in UTF-8 format (general HELIOS UB requirement)
• The available free-space must be checked before converting volumes. At least twice the free space of the ".Desktop" file size must be available in the corresponding volume.

Time-out on February 1, 2009 (will be extended upon demand)

What is a desktop database good for?

Unique file/folder IDs

• Unique file and folder IDs are allocated when the file or folder is first created, and never change even if the file is renamed or moved to another location in the same volume
• Renaming a file/folder will not destroy references (aliases, file IDs, OPI links, etc.) because identification is handled via the unique file/folder ID
• Mac aliases can always be resolved to the linked file/folder

Mac and Windows compatibility

• The desktop database is compatible with AFP/SMB protocols and HFS/NTFS file systems

Fast "find file" searching

• A file or folder search takes only seconds vs. hours (no scanning of files/folders needed)

Future meta data support

Features introduced with the new SQL desktop database

Installation

Download SQL desktop database beta 3.

Stop the HELIOS services:
# cd /usr/local/helios
# bin/stop-helios now

Save the original desktop server, e.g.:
# mv sbin/desksrv sbin/desksrv.save

Extract the downloaded "<arch>.tgz" archive.
The following example is for Linux:
# gunzip < linux.tgz | tar xvf -

In case you encounter problems extracting the downloaded "<arch>.tgz" file,
run the UNIX command-line tool "cksum" and compare the generated output
with the values given in the file "sums.txt".

Then start the HELIOS services again:
# bin/start-helios -i

Note: The SQL desktop database beta 3 cannot be installed, updated, or uninstalled via the HELIOS Update Installer!

Volume conversion to the new SQL desktop format

By default the old desktop database format is used. The new format can be enabled per volume using the "UseSQL" preference, e.g. for the volume "/data1/demovol":

# prefvalue -k "Volumes/\/data1\/demovol/UseSQL" -t bool TRUE

A rebuild will create the new desktop database file via:

# rebuild -f /data1/demovol

Download the "vollist.pl" script which outputs the proper volume conversion command for each volume.

Please note: If a volume is mounted instead of rebuilding the NEW desktop file format, an automatic rebuild will take place. The volume should only be used after the rebuild is complete.

Volume restore to the old desktop format

# prefvalue -d -k "Volumes/\/data1\/demovol/UseSQL"
# rebuild -f /data1/demovol

Please note: If a volume is mounted instead of rebuilding the OLD desktop file format, an automatic rebuild will take place. The volume should only be used after the rebuild is complete.

Performance

Identical with existing release, performance mainly depends on disk and file system performance.

Online backup copies of a live volume (sample script)

One workaround is to make the volume inactive during the time span of the backup.

Another, quite better method is to lock the desktop database for the time span that it is saved and copy the intact ".Desktop" file into a temporary directory, and copy the saved desktop file into the restored volume. An online ".Desktop" backup copy can be done with the following script:

(The script can be called on the command line via perl backup-desktop.pl)

#!/usr/bin/perl -w

# This script opens the ".Desktop" database, issues an exclusive lock
# and then copies the database. NOTE: This is only an example, the
# path to the HELIOS volume and the destination directory must be
# adjusted to your needs.

# Path to the HELIOS volume:
my $volpath = "/Volumes/Data/demovol";
# Where to store the backup copy:
my $dest = "/tmp/";

my $errlog = "/tmp/db_cp_log$$";
my $lockcmd = "begin exclusive;\n";
my $heliosdir;
my $errcode = 0;

if (not -f "$volpath/.Desktop") {
        die "No .Desktop in '$volpath'\n";
}

# Find the HELIOS installation directory.
open (FD, "/etc/HELIOSInstallPath") ||
        die "Cannot find HELIOS product, check installation\n";
chop($heliosdir = <FD>);
close(FD);

# Let the desksrv flush the database.
system("$heliosdir/bin/srvutil", "reconf", "desksrv");
sleep 1;

open(FSQL, "|$heliosdir/bin/sqlite $volpath/.Desktop >$errlog 2>&1") ||
        die "Cannot open desktop: $!";
# Use syswrite to avoid buffering.
# Sleep one second to allow the execution of the cmd.
syswrite(FSQL, $lockcmd, length($lockcmd));
sleep 1;

# Check if the logfile contains error messages.
if (-s $errlog) {
        open(FD, $errlog);
        while(<FD>) {
                print STDERR $_;
        }
        $errcode = 1;
} else {
        # Database is locked and can be copied.
        system("/bin/cp", "-p", "$volpath/.Desktop", $dest);
}

system("/bin/rm", "$errlog");
close FSQL;
exit $errcode;

Re-indexing the desktop database

The new transaction database does not need any rebuild to go online after an abort such as power failure. However file changes that have been done without using the HELIOS "dt" tools (e.g.: a host script not using the "dt" tools, restoring partial volume data from a system backup, non-HELIOS compatible file changes, e.g. ftp uploads, non "dt" folder renames) require a desktop rebuild in order to obtain a proper index of the volume. Please note that an up-to-date desktop database is required for EtherShare and PCShare clients because they access files via unique IDs. Wrong or missing IDs will result in major problems.

Warning: Using non-HELIOS desktop compatible file services, e.g. Samba on a live HELIOS volume will cause problems such as jumping Finder listings, strange "File not found..." messages, etc.

Important: It is recommended to check that sufficient free space is available on the disk before rebuilding the desktop.

New preferences

Volume preference:

<All volume preference keys require the volume to be mounted anew to take effect!>
Key: Volumes/<UNIX directory>/<preference>

UseSQL <bool; default=FALSE> Activates the SQL database for the volume

Important: If a key comprises a path, the "/" ("slash") characters within the path must be marked by preceding each with a "\" ("backslash") character.

Desktop server preference:

<All desktop server preference keys require a service restart to take effect!>
Key: Programs/desksrv/<preference>

SQLFlushDelay <int; default=60> Time in seconds before the live desktop database is flushed

SQL desktop database engine

The SQL desktop uses the underlying "Sqlite Version 3.2.1" database file format. We include the command line "sqlite" tool within the HELIOS "bin" directory.

Most common built-in "sqlite" commands:

Warning: The Mac OS X 10.4 or any other "sqlite" distribution is incompatible with the HELIOS SQL desktop version, therefore non-HELIOS delivered "sqlite" tools or libraries will not work or will result into locking problems, which will damage the database. It is safe to use the HELIOS included "sqlite" command.

The SQLite SQL documentation can be found here: http://www.sqlite.org/docs.html

Tip: Modifying the the SQL desktop database should only be done by experienced developers who know about SQL and the file/directory ID requirements within the HELIOS products. Locking the database for a long period (longer than a few seconds) may result in network time-outs of the remotely connected Mac or Windows clients. If clients attempt to change volume information and the desktop database file of that volume is locked, you will see a warning message like: "Warning, desktop DB busy by another process".

The HELIOS "desksrv" service will prepare changes into a transaction and flushes this every 60 seconds (default value; see sqlflushdelay above), to ensure that the SQL desktop file contains the latest information. The "srvutil reconf desksrv" command will issue a flush command for volumes.

Changes for beta 1

Changes for beta 3

Time-out of the SQL desktop database beta 3 has been extended to February 1, 2009.

Known issues

Accidentally removed volume definition in HELIOS Admin:

In case an SQL desktop volume has been removed via HELIOS Admin, re-entering the volume via HELIOS Admin will automatically rebuild the database using the old desktop file format.

This is due to the fact, that after creating a new volume, HELIOS Admin will open the ".Desktop" file and find an unexpected SQL ".Desktop" file format.

To avoid an automatic rebuild with the non-SQL format, the volume can be entered via preferences.

There are several solutions to overcome this problem:

Download the "vollist.pl" script which outputs the proper volume conversion command for each volume.

A final version will use the SQL desktop format by default for all read-write volumes. Therefore this issue will disappear.

Rebuild messages:

The command rebuild -s scan may issue error messages like "orphan .rsrc file" or "zero ID" for the ".Desktop-journal" file. These can be ignored. No desktop rebuild is required.

Database lock messages:

"SQL error: database is locked" message from "backup-desktop.pl"

In case the above message is issued though no client is actively working in the volume, it is possible that data is being written in that particular moment, so just try the backup again.

Feedback

Please report errors or make suggestions to: beta (at) helios.de.