Hi, all.
Thanks for everyone’s hardwork in realising the promise and potential of Grist.
So far, I understand that .grist files are based on SQLite databases, but I would like to get into more of the details…
Where might I find a specification for the .grist file format?
Thanks
1 Like
Hi @kks-rnl ,
Grist files are SQLite databases, with user data stored as regular SQLite tables. Grist maintains a set of metadata tables, all starting with _grist_*, as defined here:
"""
schema.py defines the schema of the tables describing Grist's own data structures. While users can
create tables, add and remove columns, etc, Grist stores various document metadata (about the
users' tables, views, etc.) also in tables.
Before changing this file, please review:
https://phab.getgrist.com/w/migrations/
"""
import itertools
from collections import OrderedDict, namedtuple
import six
import actions
SCHEMA_VERSION = 36
def make_column(col_id, col_type, formula='', isFormula=False):
show original
There is another batch of tables all starting with _gristsys_* storing data not needed by the “data engine” that handles formula evaluation:
/**
* Schema for all system tables, i.e. those that are NOT known by the data engine. Regular
* metadata tables (such as _grist_DocInfo) are created via DocActions received from
* InitNewDoc useraction.
*
* The current "Storage Version" used by Grist is the length of the migrations list in its
* Schema. We use it to track changes to how data is stored on disk, and changes to the schema
* of non-data-engine tables (such as _gristsys_* tables). By contrast, "Schema Version" keeps
* track of the version of data-engine metadata. In SQLite, we use "PRAGMA user_version" to
* store the storage version number.
*/
public static docStorageSchema: SchemaInfo = {
async create(db: SQLiteDB): Promise<void> {
await db.exec(`CREATE TABLE _gristsys_Files (
id INTEGER PRIMARY KEY,
ident TEXT UNIQUE,
data BLOB
)`);
await db.exec(`CREATE TABLE _gristsys_Action (
id INTEGER PRIMARY KEY,
show original
Another way to approach this is to look at what is needed to make an SQLite database created outside of Grist readable by Grist. There is a utility for that (yarn run cli sqlite), implemented here:
import { ColInfoWithId } from 'app/common/DocActions';
import { ActiveDoc } from 'app/server/lib/ActiveDoc';
import { DocManager } from 'app/server/lib/DocManager';
import { makeExceptionalDocSession, OptDocSession } from 'app/server/lib/DocSession';
import { createDummyGristServer } from 'app/server/lib/GristServer';
import { TrivialDocStorageManager } from 'app/server/lib/IDocStorageManager';
import { DBMetadata, quoteIdent, SQLiteDB } from 'app/server/lib/SQLiteDB';
/**
* A utility class for modifying a SQLite file to be viewed/edited with Grist.
*/
export class Gristifier {
public constructor(private _filename: string) {
}
/**
* Add Grist metadata tables to a SQLite file. After this action,
* the file can be opened as a Grist document, with partial functionality.
* Level of functionality will depend on the nature of the tables in the
* SQLite file.
show original
The script has limits but it may give ideas.
2 Likes
Bilbo
June 13, 2023, 10:20am
4
Hello. I have an SQLite database that I want to import into Grist. I am not a technical user, can someone please explain how to use the gristify.ts script to make my SQLite grist-readable?
(grist-core/blob/main/app/server/utils/gristify.ts)
@paul-grist you said run (yarn run cli sqlite ) , could you explain the prerequisites for that command to work?