module Audrey::Engine::SQLite3::Init

Audrey::Engine::SQLite3::Init

Public Class Methods

build(p_dbh) click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 10
def self.build(p_dbh)
        # create database file
        # @dbh = SQLite3::Database.new(engine.path)
        @dbh = p_dbh
        
        # go into transaction
        @dbh.transaction
        
        # temp tables
        # Audrey::Engine::SQLite3.session_tables @dbh
        
        # create and populate tables
        journal()
        settings()
        partitions()
        objects()
        relationships()
        views()
        sessions()
        
        # commit
        @dbh.commit
end
journal() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 41
        def self.journal
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                -- create table
                create table journal (
                        pk integer primary key autoincrement,
                        ts datetime default current_timestamp,
                        tbl varchar(15)  not null,
                        fpk varchar(36)  not null,
                        action varchar(1) not null,
                        dsc json
                );
                
                -- cannot update journal
                create trigger journal_bu
                        before update on journal
                begin
                        select raise(abort, 'cannot-update-journal-record');
                end;
                
                -- cannot delete journal
                create trigger journal_bd
                        before delete on journal
                begin
                        select raise(abort, 'cannot-delete-journal-record');
                end;
                SQL
        end
objects() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 167
        def self.objects
                # build
                @dbh.execute_batch <<-SQL
                -- aclasses table
                create table aclasses(
                        pk integer primary key autoincrement,
                        name varchar(255) unique not null,
                        check (length(name)>0)
                );
                
                -- cannot update aclasses
                create trigger aclasses_bu
                before update on aclasses
                begin
                        select raise(abort, 'cannot-update-aclasses');
                end;
                
                -- base_objects table
                create table base_objects (
                        pk varchar(36) primary key,
                        partition varchar(1) not null references partitions(pk),
                        root boolean,
                        hsa varchar(1) not null check(hsa in ('h', 's', 'a')),
                        aclass_pk integer not null references aclasses(pk) on delete restrict,
                        scalar text,
                        xt uuid references base_objects(pk) unique,
                        changed datetime default current_timestamp,
                        check (root=1)
                );
                
                -- indexes
                create unique index base_objects_partition_root on base_objects(partition, root);
                create index base_objects_partition  on base_objects(partition);
                create index base_objects_hsa        on base_objects(hsa);
                create index base_objects_aclass_pk  on base_objects(aclass_pk);
                create index base_objects_scalar     on base_objects(scalar);
                create index base_objects_changed    on base_objects(changed);
                
                -- cannot update most fields
                create trigger base_objects_bu_pk        before update of pk        on base_objects begin select raise(abort, 'cannot-update-object-pk');        end;
                create trigger base_objects_bu_partition before update of partition on base_objects begin select raise(abort, 'cannot-update-object-partition'); end;
                create trigger base_objects_bu_root      before update of root      on base_objects begin select raise(abort, 'cannot-update-object-root');      end;
                create trigger base_objects_bu_hsa       before update of hsa       on base_objects begin select raise(abort, 'cannot-update-object-hsa');       end;
                create trigger base_objects_bu_aclass_pk before update of aclass_pk on base_objects begin select raise(abort, 'cannot-update-object-aclass_pk'); end;
                create trigger base_objects_bu_scalar    before update of scalar    on base_objects begin select raise(abort, 'cannot-update-object-scalar');    end;
                
                -- cannot update xt once it has been defined
                create trigger base_objects_bu_xt
                        before update of xt on base_objects when (old.xt is not null)
                begin
                        select raise(abort, 'cannot-update-object-xt-once-set');
                end;
                
                -- cannot delete root object
                create trigger base_objects_bd_root
                        before delete on base_objects when (old.root)
                begin
                        select raise(abort, 'cannot-delete-root-object');
                end;
                
                -- insert: journal
                create trigger base_objects_ai_journal
                        after insert on base_objects when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action, dsc) values(
                                'o',
                                new.pk,
                                'i',
                                insert_object_journal(
                                        new.partition,
                                        new.root,
                                        (select name from aclasses where pk=new.aclass_pk),
                                        new.scalar,
                                        new.xt
                                )
                        );
                end;
                
                -- update: journal
                create trigger base_objects_au_journal
                        after update on base_objects when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action, dsc) values('o', new.pk, 'u',
                                '{' ||
                                '"x":"' || replace(new.xt, '"', '\"') || '"' ||
                                '}');
                end;
                
                -- after delete on object with xt
                create trigger base_objects_ad
                        after delete on base_objects
                begin
                        delete from base_objects where pk=old.xt;
                end;
                
                -- delete: journal
                create trigger base_objects_ad_journal
                        after delete on base_objects when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action) values('o', old.pk, 'd');
                end;
                
                -- initial aclass
                insert into aclasses(name) values('Audrey::Object::Hash');
                insert into aclasses(name) values('Audrey::Object::Root');
                
                -- add root objects
                insert into base_objects(pk, partition, hsa, aclass_pk, root, changed) values('1', 'm', 'h', 2, 1, null);
                insert into base_objects(pk, partition, hsa, aclass_pk, root, changed) values('2', 'r', 'h', 2, 1, null);
                insert into base_objects(pk, partition, hsa, aclass_pk, root, changed) values('3', 'd', 'h', 2, 1, null);
                
                -- objects view
                create view objects as
                        select
                                o.pk, o.partition, o.root, o.scalar, o.xt, o.changed, o.hsa,
                                (select name from aclasses where pk=o.aclass_pk) as aclass
                        from base_objects o
                        where o.partition = current_partition();
                        
                -- insert into objects
                create trigger objects_insert
                        instead of insert on objects
                begin
                        -- notify database of change
                        -- select change_to_true(new.aclass);
                        
                        -- ensure aclass record
                        insert or ignore into
                        aclasses(name)
                        values(new.aclass);
                        
                        -- insert
                        insert into
                                base_objects(
                                        pk,
                                        partition,
                                        root,
                                        hsa,
                                        aclass_pk,
                                        scalar,
                                        xt
                                )
                                
                                values(
                                        new.pk,
                                        current_partition(),
                                        new.root,
                                        new.hsa,
                                        (select pk from aclasses where name=new.aclass),
                                        new.scalar,
                                        new.xt
                                );
                end;
                
                /*
                -- update objects
                create trigger objects_update
                        instead of update of changed on objects
                begin
                        update base_objects
                        set changed = new.changed
                        where pk = new.pk;
                end;
                */
                
                -- delete from objects
                create trigger objects_delete
                        instead of delete on objects
                begin
                        delete from base_objects where pk=old.pk and partition = current_partition();
                end;
                
                -- end of sql
                SQL
        end
partitions() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 114
        def self.partitions
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                        -- create table
                        create table partitions (
                                pk varchar(1) primary key,
                                name varchar(30) unique not null
                        );
                        
                        -- cannot update pk
                        create trigger partitions_bu_pk
                                before update of pk on partitions
                        begin
                                select raise(abort, 'cannot-update-partition-pk');
                        end;
                        
                        -- create partitions
                        insert into partitions(pk, name) values('m', 'main');
                        insert into partitions(pk, name) values('r', 'roles');
                        insert into partitions(pk, name) values('d', 'design');
                        
                        -- cannot insert partitions
                        create trigger partitions_bi
                                before insert on partitions
                        begin
                                select raise(abort, 'cannot-insert-new-partition');
                        end;
                        
                        -- cannot update
                        create trigger partitions_bu
                                before update on partitions
                        begin
                                select raise(abort, 'cannot-update-partition');
                        end;
                        
                        -- cannot delete partitions
                        create trigger partitions_bd
                                before delete on partitions
                        begin
                                select raise(abort, 'cannot-delete-partition');
                        end;
                SQL
        end
relationships() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 351
        def self.relationships()
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                -- create table
                create table relationships (
                        pk varchar(36) primary key,
                        parent varchar(36) not null references base_objects(pk) on delete cascade,
                        child varchar(36) not null references base_objects(pk) on delete cascade,
                        ord integer not null,
                        hkey varchar(255),
                        check(typeof(ord)='integer')
                );
                
                -- indexes
                create unique index relationships_parent_hkey on relationships(parent, hkey);
                create index relationships_parent on relationships(parent);
                
                -- must be in same partition
                create trigger relationships_bi_partitions
                        before insert on relationships
                begin
                        select case
                                when (select partition from base_objects where pk=new.parent) <> (select partition from base_objects where pk=new.child) then
                                        raise(abort, 'relationship-objects-not-in-same-partition')
                                end;
                end;
                
                -- parent must be array or hash
                create trigger relationships_bi_hsa
                        before insert on relationships
                begin
                        select case
                                when (select hsa from objects where pk=new.parent) not in ('h', 'a') then
                                        raise(abort, 'relationship-parent-base-class-does-not-support-relatonships')
                        end;
                end;
                
                -- cannot update
                create trigger relationships_bu
                        before update on relationships
                begin
                        select raise(abort, 'cannot-update-relationships');
                end;
                
                -- insert: journal
                create trigger relationships_ai_journal
                        after insert on relationships when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action, dsc) values('r', new.pk, 'i',
                                '{' ||
                                '"p":"' || replace(new.parent, '"', '\"') || '",' ||
                                '"c":"' || replace(new.child, '"', '\"') || '",' ||
                                '"ord":' || new.ord || ',' ||
                                '"hk":"' || replace(new.hkey, '"', '\"') || '"' ||
                                '}');
                end;
                
                -- insert: changed
                create trigger relationships_ai_changed
                        after insert on relationships
                begin
                        update base_objects set changed=current_timestamp where pk=new.parent;
                end;
                
                -- journal: update
                create trigger relationships_au_journal
                        after update on relationships when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action, dsc) values('r', new.pk, 'u',
                                '{' ||
                                '"c":"' || replace(new.child, '"', '\"') || '",' ||
                                '"ord":' || new.ord || ',' ||
                                '"hk":"' || replace(new.hkey, '"', '\"') || '"' ||
                                '}');
                end;
                
                -- delete: journal
                create trigger relationships_ad_journal
                        after delete on relationships when (select value from settings where pk='journal')
                begin
                        insert into journal(tbl, fpk, action) values('r', old.pk, 'd');
                end;
                
                -- delete: changed
                create trigger relationships_ad_changed
                        after delete on relationships
                begin
                        update base_objects set changed=current_timestamp where pk=old.parent;
                end;
                
                -- delete parent if child is graph
                -- KLUDGE: Cannot figure out why, but if this routine attempts to delete
                -- from objects view, the delete never happens. So deleting directly
                -- from base_objects.
                create trigger relationships_ad_graph
                        after delete on relationships when (select graph_field((select aclass from objects where pk=old.parent), old.hkey))
                begin
                        delete from base_objects where pk=old.parent;
                end;
                
                -- end of sql
                SQL
        end
sessions() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 513
        def self.sessions
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                -- sessions
                create table sessions (
                        pk varchar(36) primary key,
                        pw varchar(36)
                );
                
                -- cannot update sessions
                create trigger sessions_bu
                        before update on sessions
                begin
                        select raise(abort, 'cannot-update-sessions');
                end;
                
                -- results
                create table results (
                        pk varchar(36) primary key,
                        session varchar(36) not null references sessions(pk) on delete cascade
                );
                
                -- cannot update results
                create trigger results_bu
                        before update on results
                begin
                        select raise(abort, 'cannot-update-results');
                end;
                
                -- result_rows
                create table result_rows (
                        pk varchar(36) primary key,
                        result varchar(36) not null references results(pk) on delete cascade,
                        object varchar(36) not null references base_objects(pk) on delete cascade
                );
                
                -- cannot update result_rows
                create trigger result_rows_bu
                        before update on result_rows
                begin
                        select raise(abort, 'cannot-update-result_rows');
                end;
                
                -- end of SQL
                SQL
        end
settings() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 79
        def self.settings
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                -- create table
                create table settings (
                        pk varchar(5) primary key,
                        value varchar(64) not null
                );
                
                -- cannot update pk
                create trigger settings_bu_pk
                        before update of pk on settings
                begin
                        select raise(abort, 'cannot-update-setting-pk');
                end;
                
                -- add setting for journaling
                insert into settings(pk, value) values('journal', 1);
                
                -- create version setting
                insert into settings(pk, value) values('version', '0.1');
                
                -- end sql
                SQL
        end
views() click to toggle source
# File lib/audrey/engine/sqlite3/init.rb, line 464
        def self.views
                # $tm.hrm
                
                # build
                @dbh.execute_batch <<-SQL
                -- hash_pairs
                create view hash_pairs as
                select c.partition, r.parent, r.child, r.hkey, c.scalar, r.ord
                from objects c, relationships r
                where (c.pk = r.child) and (r.hkey is not null)
                order by ord;
                
                -- fcos
                create view fcos as
                        select pk
                        from objects
                        where aclass_fco(aclass);
                
                -- fco_descendants
                create view fco_descendants as
                        with recursive ancestor(child) as (
                                select child from relationships where parent in (select pk from fcos)
                                union
                                select r.child from ancestor a inner join relationships r on r.parent = a.child
                        )
                        select child from ancestor;
                        
                -- fco_traced
                create view fco_traced as
                        select distinct pk
                        from objects
                        where aclass_fco(aclass) or (pk in (select child from fco_descendants));
                
                -- fco_not_traced
                create view fco_not_traced as
                        select pk from objects
                        where pk not in (select pk from fco_traced);
                        
                -- end of SQL
                SQL
        end