Idea: use sqlar file storage layout for .grist files

Grist Feedback
1

There is a (lesser known) “feature” / “convention” of sqlite, called “sqlar”.

This describes a way how to use sqlite as a “tar” or “zipfile”.
You basically need this table in your sqlite file, then its a valid sqlar:

CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- name of the file
  mode INT,               -- access permissions
  mtime INT,              -- last modification time
  sz INT,                 -- original file size
  data BLOB               -- compressed content
);

The current format in a grist file, looks like this:

sqlite> .schema _gristsys_Files 
CREATE TABLE _gristsys_Files (
        id INTEGER PRIMARY KEY,
        ident TEXT UNIQUE,
        data BLOB,
        storageId TEXT
       );

(So you see its not that “far away” from beeing a valid sqlar.)

On some distros there even is the sqlar command which can list, add update the sqlar archive.

If you have sqlite3 installed there is a good chance that you can try out sqlar:

# show the archive help
sqlite3 -A

# create an archive called images.sqlar, with all images
sqlite3 images.sqlar -Ac images/*.avif

# list the content 
sqlite3 images.sqlar -At

$ sqlite3 mtg.grist -A
Wrong number of arguments.  Usage:
.archive ...             Manage SQL archives
   Each command must have exactly one of the following options:
     -c, --create               Create a new archive
     -u, --update               Add or update files with changed mtime
     -i, --insert               Like -u but always add even if unchanged
     -r, --remove               Remove files from archive
     -t, --list                 List contents of archive
     -x, --extract              Extract files from archive
   Optional arguments:
     -v, --verbose              Print each filename as it is processed
     -f FILE, --file FILE       Use archive FILE (default is current db)
     -a FILE, --append FILE     Open FILE using the apndvfs VFS
     -C DIR, --directory DIR    Read/extract files from directory DIR
     -g, --glob                 Use glob matching for names in archive
     -n, --dryrun               Show the SQL that would have occurred
   Examples:
     .ar -cf ARCHIVE foo bar  # Create ARCHIVE from files foo and bar
     .ar -tf ARCHIVE          # List members of ARCHIVE
     .ar -xvf ARCHIVE         # Verbosely extract files from ARCHIVE
   See also:
      http://sqlite.org/cli.html#sqlite_archive_support

So what i propose :
Change grist internal file storage so that every grist file also is a sqlar file.

  1. Add missing colums
    1. Invent some file access modes (or whatever)
    2. for the name/path you could even go fancy with either a flat layout, or a directory tree where the directories are the different tables etcpp
  2. Rename the table to sqlar for compatibility.
  3. There might be an issue with compression i think current grist stores uncompressed.
    The default command seem to expect a compressed file, but this is something to look at.

This would make it super easy to, add files via grist in all tables.
Then just use and extract them in bash scripts on a server, no need for anything but sqlite3.

Edit: i have not tried yet, but maybe a view that joins _grist_Attachments and _gristsys_Files could be enough :thinking: then it would be even easier.
At least a read only access would be possible at least, which i think could be enough for a generic tool like sqlar/sqlite3.

Edit2: This unfortunately does not work:

CREATE VIEW IF NOT EXISTS 
  sqlar 
AS 
    SELECT 
      aa.fileName as name, 
      0 as mode,
      0 as mtime,
      aa.fileSize as sz, 
      ff.data as data 
    FROM _gristsys_Files AS ff 
    INNER JOIN _grist_Attachments AS aa 
    ON ff.ident = aa.fileident  ;

It seems that sqlite3 looks for a real table :smile:

What do you think?

2

I’ve build a prototype of a tar like gristar command
which extracts grist attachments in bulk from a grist file.

If you use it, be aware that this is a prototype.