contact book

schema.sql

CREATE TABLE people ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT, dob TEXT, name TEXT, photo TEXT, notes TEXT);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "contacts" (
	"id"	integer NOT NULL,
	"type"	text NOT NULL CHECK(type in ('work', 'mobile', 'home', 'email')),
	"value"	text NOT NULL,
	"people_id"	integer NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("people_id") REFERENCES "people"("id"),
	UNIQUE("type","value")
);
CREATE TABLE IF NOT EXISTS "addresses" (
	"id"	integer NOT NULL,
	"country"	text NOT NULL,
	"city"	text,
	"people_id"	INTEGER NOT NULL,
	"address"	text,
	"created_at"	datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"updated_at"	datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY("id" AUTOINCREMENT),
	FOREIGN KEY("people_id") REFERENCES "people"("id")
);
CREATE TRIGGER addresses_updated_at_populate
AFTER UPDATE ON addresses FOR EACH ROW
BEGIN
	UPDATE addresses SET updated_at = CURRENT_TIMESTAMP 
		WHERE id = old.id;
END;
CREATE TRIGGER "people_consistency"
  BEFORE DELETE ON people
 FOR EACH ROW
 BEGIN
	DELETE FROM "contacts" WHERE people_id = OLD.id;
	DELETE FROM "addresses" where people_id = OLD.id;	
 END;
CREATE VIEW "people+contacts" AS SELECT p.first_name, p.last_name, p.dob, p.name, c.contact_details
FROM people as p
JOIN (SELECT group_concat(c.type || ": " || c.value) AS contact_details, c.people_id FROM "contacts_bckp" c GROUP BY c.people_id) as c ON c.people_id = p.id;