Module: Audrey::Engine::SQLite3::Init

Defined in:

lib/audrey/engine/sqlite3/init.rb

Overview

Audrey::Engine::SQLite3::Init

Class Method Summary

• .build(p_dbh) ⇒ Object

  ————————————————————————— build.

• .journal ⇒ Object

  ————————————————————————— journal.

• .objects ⇒ Object

  ————————————————————————— objects.

• .partitions ⇒ Object

  ————————————————————————— partitions.

• .relationships ⇒ Object

  ————————————————————————— relationships TODO: Need to rework relationships table so that ord can be changed.

• .sessions ⇒ Object

  ————————————————————————— sessions.

• .settings ⇒ Object

  ————————————————————————— settings.

• .views ⇒ Object

  ————————————————————————— views.

Class Method Details

.build(p_dbh) ⇒ Object

---

build

# 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 ⇒ Object

---

journal

# 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 ⇒ Object

---

objects

# 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 ⇒ Object

---

partitions

# 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 ⇒ Object

---

relationships TODO: Need to rework relationships table so that ord can be changed.

# 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 ⇒ Object

---

sessions

# 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 ⇒ Object

---

settings

# 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 ⇒ Object

---

views

# 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