#!/bin/sh
dbdir=$(cd `dirname $0`; pwd -P)/../db
echo $dbdir
sqlite3 $dbdir/viewer_prc.sq3 <<-EOF
	-- Initialize tables
	PRAGMA FOREIGN_KEYS=on;
	CREATE TABLE IF NOT EXISTS mapitem(
	 ename PRIMARY KEY, name TEXT NOT NULL, addr TEXT, lat REAL, lon REAL);
	CREATE TABLE IF NOT EXISTS mapname(
	  maptype TEXT PRIMARY KEY, mapname TEXT
	);
	CREATE TABLE IF NOT EXISTS maptype(
	 ename, maptype TEXT,
		FOREIGN KEY(ename) REFERENCES mapitem(ename)
		ON DELETE CASCADE ON UPDATE CASCADE,
		FOREIGN KEY(maptype) REFERENCES mapname(maptype)
		ON DELETE CASCADE ON UPDATE CASCADE,
		UNIQUE(ename, maptype));
	CREATE TABLE IF NOT EXISTS attribute(
	  ename, attr TEXT, value TEXT,
	  FOREIGN KEY(ename) REFERENCES mapitem(ename)
	  ON DELETE CASCADE ON UPDATE CASCADE,
	  UNIQUE(ename,attr,value));
	CREATE TABLE IF NOT EXISTS object(
	  ename, objtype TEXT, jsonval TEXT,
	  FOREIGN KEY(ename) REFERENCES mapitem(ename)
	  ON DELETE CASCADE ON UPDATE CASCADE,
	  CHECK (objtype IN (
	  'Point', 'MultiPoint', 'LineString',
	  'MultiLineString', 'Polygon', 'MultiPolygon')),
	  UNIQUE(ename, objtype, jsonval));
	-- Insert initial records from CSV files
	DELETE FROM mapitem;
	DELETE FROM maptype;
	DELETE FROM mapname;
	DELETE FROM attribute;
	DELETE FROM object;
	.mode csv
	.import prc_csv/mapitem.csv mapitem
	.import prc_csv/mapname.csv mapname
	.import prc_csv/maptype.csv maptype
	.import prc_csv/attr.csv attribute
	.import prc_csv/obj.csv object
EOF
