[Netatalk-admins] Call for beta testing: SQLite CNID backend

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Major netatalk news: we now support SQLite as the CNID database backend for the AFP file server, in the main development branch.

Why is this big news you ask? Thank you for the great question! For the last two decades (since at least v1.5 in 2002) netatalk has been using Berkeley DB as the backend for managing CNID records. BDB is a great database, but it has been slowly dying ever since Oracle acquired it in 2006. In recent years, it's started getting flagged as obsolete by several OS distributions (Alpine, Debian etc.) so we have a semi-urgent need to find an alternative.

Enter SQLite: A popular, actively developed, and super fast embedded database engine! Just like DBD, it stores it database in a simple file on the file system, no need to juggle a stand-alone database instance with authentication, networking and so on.

Now I'm looking for beta testing volunteers to get some data from the field. If you have an existing netatalk 4.x deployment, this is how you convert your volumes to the new backend:

### Step 1 - Back up your data

I'm not kidding. You need to take a full copy of both the shared volume dir, as well as the CNID database dir for your volume. The conversion process is destructive and may lead to data loss.

### Step 2 - Build and install netatalk with SQLite support

For this you need sqlite3 library and development headers. See the build.yml file for examples of packages to install.

Once you have the required packages on your system, the Meson build system will detect and build the sqlite CNID backend by default.

### Step 3 - Configure netatalk to use the SQLite backend

In the relevant volume sections in your afp.conf, put this option:

`cnid scheme = sqlite`

### Step 4 - Convert from an earlier CNID scheme

When using the SQLite backend with an existing volume, you need to rebuild the CNID database in the new scheme.

After updating afp.conf, start the netatalk daemon, but DON'T try to connect to the shared volume yet.

First you need to run this command with root privileges:

`dbd -f /path/to/shared/volume`

Inspect the output from the dbd tool and make sure there aren't any errors. If you see an error, please report a bug with the netatalk project, attach the log, SQLite database dump, and as much details about your volume as possible!

### Step 5 - Connect to the AFP server

Now you should be able to connect to and use the converted volume as usual!

### What to look out for

-   That actual, long-lived organic volumes can be converted.
-   Concurrency. Can multiple users connect and do intensive file operations concurrently (in particular write operations)
-   Cross-platform. I've actively tested on Debian, Fedora, and macOS.

I'm looking forward to your bug reports. :)

### Troubleshooting

If you want thorough debug logs for the CNID library, this is a good log level setting:

`log level = default:info cnid:maxdebug`

To dump a SQLite database to stdout, you can do something like:

`sqlite3 /var/netatalk/CNID/myvolume/myvolume.sqlite .dump`

Sample output:

PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE volumes (VolUUID CHAR(32) PRIMARY KEY, VolPath TEXT(4096), Stamp BINARY(8), Depleted INT); INSERT INTO volumes VALUES('C5DC147DD05B3440BE95D4E6A3397768','/opt/afplite','?ah',0); CREATE TABLE IF NOT EXISTS "C5DC147DD05B3440BE95D4E6A3397768" (Id INTEGER PRIMARY KEY AUTOINCREMENT,Name VARCHAR(255) NOT NULL,Did INTEGER NOT NULL,DevNo INTEGER NOT NULL,InodeNo INTEGER NOT NULL,UNIQUE (Did, Name),UNIQUE (DevNo, InodeNo)); INSERT INTO C5DC147DD05B3440BE95D4E6A3397768 VALUES(17,'Navigator for Pucko',2,16777239,74852120); INSERT INTO C5DC147DD05B3440BE95D4E6A3397768 VALUES(18,'TheVolumeSettingsFolder',2,16777239,74848959); INSERT INTO C5DC147DD05B3440BE95D4E6A3397768 VALUES(19,'Web Pages',2,16777239,74863775); INSERT INTO C5DC147DD05B3440BE95D4E6A3397768 VALUES(20,'default.html',19,16777239,74863778); DELETE FROM sqlite_sequence; INSERT INTO sqlite_sequence VALUES('C5DC147DD05B3440BE95D4E6A3397768',100); CREATE INDEX idx_volpath ON volumes(VolPath); COMMIT;