PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
	DROP TABLE IF EXISTS fileLog;
	DROP TABLE if EXISTS commandBody;
	DROP TABLE if EXISTS commandLog;
	DROP TABLE IF EXISTS goodList;
	DROP VIEW IF EXISTS vHistory;
COMMIT;
BEGIN TRANSACTION;
	CREATE TABLE "fileLog" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "filePath" VARCHAR(255), "LMD" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP, "NRows" INTEGER NOT NULL  DEFAULT 0, "Comment" TEXT);
	CREATE TABLE "commandBody" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "CommandID" INTEGER NOT NULL , "SequenceID" INTEGER NOT NULL , "CommandText" TEXT);
	CREATE TABLE "commandLog" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "LMD" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP, "Statement" VARCHAR, "RetainCode" INTEGER NOT NULL  DEFAULT 1, "Checksum" VARCHAR NOT NULL  DEFAULT '-1');
	CREATE TABLE goodList
	(
	    id integer primary key autoincrement,
	    uprn varchar(20),
	    add_1 varchar(50),
	    lo_code integer,
	     post_code varchar(8),
	    door integer,
	    door_suffix varchar(4),
	    Opco VARCHAR(3),
	    street_name varchar(50)
	);
	CREATE VIEW vHistory AS
	SELECT ID, RetainCode, LMD, Statement
	FROM commandLog;
COMMIT;
.quit
