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