Aaron Puchert aaronpuchert

aaronpuchert / tree.sql

Created May 11, 2013 17:47

Recursive tree structure in SQLite allowing to get all descendants of an element via storing "intervals" for each node.

	PRAGMA recursive_triggers=on;

	CREATE TABLE nodes (
	id INTEGER NOT NULL,
	parent_id INTEGER,
	interval_start REAL,
	interval_end REAL,
	name TEXT NOT NULL,
	PRIMARY KEY (id ASC),
	FOREIGN KEY (parent_id)

aaronpuchert / alter.sql

Created May 11, 2013 17:27

Alter table in SQLite. This is not possible directly, we need a workaround. This keeps foreign key relations from other tables alive.

	CREATE TABLE tab2 (
	columns TEXT);
	INSERT INTO tab2 (columns) SELECT columns from tab;
	DROP TABLE tab;
	ALTER TABLE tab2 RENAME TO tab;