BRZ: Bruce Archive Format

archive.brz
└── model.db

PRAGMA application_id = 1112687682;
PRAGMA user_version = 7;

-- Attribute definitions.
-- One row per distinct attribute that Entities in this archive can carry.
CREATE TABLE Attribute (
    id          INTEGER PRIMARY KEY,
    -- Dot-separated grouping path, e.g. "dimensions.physical".
    category    TEXT,
    name        TEXT,
    -- 0=STRING  1=NUMERIC  2=BOOLEAN  3=TIMESTAMP  4=DATE
    dataType    INT,
    -- Optional regex validation pattern. NULL or empty if no validation is needed.
    pattern     TEXT,
    displayName TEXT,
    description TEXT
);

-- One row per Entity.
CREATE TABLE Entity (
    -- Local auto-increment. Used for all FK references within this file only.
    internalID  INTEGER PRIMARY KEY,
    name        TEXT,
    -- 0 = regular Entity instance, 1 = prototype definition (deprecated, don't create new prototypes).
    prototype   BOOLEAN,
    -- UUID from the source system. Used for identity matching after an export from Nextspace.
    -- Leave as NULL for generated files that aren't coming from Nextspace.
    ID          TEXT,
    -- References "EntityType.internalid".
    -- Leave as NULL if no defined Entity Types are being brought in.
    typeid      INTEGER,
    -- Optional geohash string for spatial indexing.
    -- Leave as NULL if not applicable.
    geohash     TEXT,
    -- Optional WGS84 geographic position.
    -- Leave as NULL if not applicable (ie: Entities are relatively positioned within the Assembly).
    longitude   DOUBLE PRECISION,
    latitude    DOUBLE PRECISION,
    altitude    DOUBLE PRECISION
);

-- Attribute values for Entities. One row per attribute value per Entity.
-- Populate only the value column that matches the attribute dataType; leave the others NULL.
-- STRING/BOOLEAN/TIMESTAMP/DATE values under ~240 chars -> StringValue.
-- STRING values over ~240 chars -> LongTextValue.
-- NUMERIC values (and booleans as 0.0/1.0) -> NumericValue.
CREATE TABLE EntityAttribute (
    -- References "Entity.internalID".
    entityID      BIGINT,
    -- References "Attribute.id".
    AttributeID   BIGINT,
    StringValue   TEXT,
    LongTextValue TEXT,
    NumericValue  DOUBLE PRECISION
);

-- One row per GLB geometry asset.
-- The binary data must be DEFLATE-compressed; the importer always decompresses before use.
CREATE TABLE Geometry (
    id           INTEGER PRIMARY KEY,
    -- e.g. "model/gltf-binary"
    type         TEXT,
    -- UUID from the source system.
    -- Leave as NULL for generated files that aren't coming from Nextspace.
    persistentID TEXT,
    -- Deprecated field. Leave as NULL for generated files.
    filepath     TEXT,
    -- Vertex / face / line counts. Set to 0 if unknown.
    nverts       INT,
    nfaces       INT,
    nlines       INT,
    -- Axis-aligned bounding box of the geometry in its local space.
    minx         DOUBLE PRECISION,
    miny         DOUBLE PRECISION,
    minz         DOUBLE PRECISION,
    maxx         DOUBLE PRECISION,
    maxy         DOUBLE PRECISION,
    maxz         DOUBLE PRECISION,
    -- Uncompressed byte size of the GLB.
    size         DOUBLE PRECISION,
    -- CRC32 of the uncompressed GLB bytes. Used by the server for deduplication.
    hash         INTEGER,
    -- Hash algorithm version. Always 1 (CRC32).
    hashVersion  SMALLINT,
    -- DEFLATE-compressed GLB bytes. Must not be stored uncompressed.
    data         VARBINARY
);

-- Defines the parent-child hierarchy and the local transform of each child.
-- Every non-root entity must have exactly one row here.
CREATE TABLE ParentRelationship (
    -- Both reference "Entity.internalID".
    parentID    BIGINT,
    childID     BIGINT,
    -- Zero-based ordering index among siblings.
    childNumber INT,
    -- Local translation.
    px          DOUBLE PRECISION,
    py          DOUBLE PRECISION,
    pz          DOUBLE PRECISION,
    -- Local rotation as a unit quaternion. Identity = (0, 0, 0, 1).
    qx          DOUBLE PRECISION,
    qy          DOUBLE PRECISION,
    qz          DOUBLE PRECISION,
    qw          DOUBLE PRECISION,
    -- Local scale per axis. Identity = (1, 1, 1).
    sx          DOUBLE PRECISION,
    sy          DOUBLE PRECISION,
    sz          DOUBLE PRECISION
);

-- Links instance Entities to their prototype.
-- Only needed when multiple Entities share the same geometric definition.
-- Deprecated and not needed for newly generated files.
CREATE TABLE PrototypeRelationship (
    -- Both reference "Entity.internalID".
    instanceID  BIGINT,
    prototypeID BIGINT
);

-- Optional material references for a geometry row.
-- Can be left empty for generated files.
CREATE TABLE GeometryMaterial (
    id                INTEGER PRIMARY KEY,
    -- References "Geometry.id".
    geometryID        BIGINT,
    -- Material slot path within the GLB.
    path              TEXT,
    materialReference TEXT
);

-- Alternative to embedding geometry in "Geometry.data": points to a downloadable URL instead.
-- Only populate this if "Geometry.data" is NULL (e.g. for very large assets).
-- Can be left empty for generated files that embed geometry directly.
CREATE TABLE GeometryRemoteURL (
    -- References "Geometry.id".
    geometryID BIGINT PRIMARY KEY,
    url        TEXT
);

-- Not read by the current importer. Can be left empty.
CREATE TABLE EntityRemoteURL (
    -- References "Entity.internalID".
    entityID BIGINT PRIMARY KEY,
    url      TEXT
);

-- Metadata about the account that exported this file.
-- Leave empty if generating files outside of Nextspace.
CREATE TABLE AccountSettings (
    accountID   TEXT,
    accountName TEXT,
    -- e.g. "prod", "uat", "dev".
    environment TEXT
);

-- Generic key/value store.
-- Leave empty if generating files outside of Nextspace.
CREATE TABLE Parameter (
    name  TEXT PRIMARY KEY,
    value TEXT
);

-- Defines the root Entity of the assembly.
-- At least one row is required; the importer uses this to locate the top of the hierarchy.
CREATE TABLE Component (
    id           INTEGER PRIMARY KEY,
    name         TEXT,
    -- References "Entity.internalID" of the root entity.
    rootEntityID BIGINT
);

-- Associates Entities with geometry at specific levels of detail.
-- Add one row per Entity that has geometry.
CREATE TABLE EntityLOD (
    id          INTEGER PRIMARY KEY,
    -- References "Entity.internalID".
    entityID    BIGINT,
    -- References "Geometry.id".
    geometryID  BIGINT,
    -- 0 = highest detail.
    level       INT,
    -- "GLB" for primary geometry. "BOUNDS" for a bounding-box proxy mesh.
    LODCategory TEXT,
    -- Optional grouping label. NULL is fine.
    LODGroup    TEXT
);

-- Entity Type definitions used by Entities in this archive.
CREATE TABLE EntityType (
    -- Local auto-increment. Used for FK references within this file only.
    internalid     INTEGER PRIMARY KEY,
    -- UUID from the source system. Used for identity matching after an export from Nextspace.
    -- Leave as NULL for generated files that aren't coming from Nextspace.
    id             TEXT,
    name           TEXT,
    description    TEXT,
    -- 1 = reject attributes not in this schema on import. 0 = allow extras.
    isStrictSchema INTEGER
);

-- Maps attributes to Entity Types, forming a typed schema.
CREATE TABLE SchemaAttribute (
    id              INTEGER PRIMARY KEY,
    -- References "EntityType.internalid".
    typeID          INTEGER,
    -- References "Attribute.id".
    attributeID     INTEGER,
    -- Display ordering index.
    attributeNumber INTEGER
);

-- Required indexes.
CREATE UNIQUE INDEX IDX_Geometry_FilePath   ON Geometry (filepath);
CREATE UNIQUE INDEX UIDX_Geometry_Material  ON GeometryMaterial (geometryID, path);
CREATE        INDEX IDX_Geometry_Hash       ON Geometry (hash);
CREATE UNIQUE INDEX IDX_Entity_ID           ON Entity (ID);