Create_portfolio.sql

DROP TABLE "PORTFOLIO_MASTER"

CREATE TABLE "PORTFOLIO_MASTER" (
	"USERID" TEXT NOT NULL,
	"PORTFOLIO_NAME" TEXT NOT NULL,
	UNIQUE(USERID, PORTFOLIO_NAME)
)

DROP TABLE "PORTFOLIO"

CREATE TABLE "PORTFOLIO" (
	"MASTER_ID"	INTEGER NOT NULL,
	"FUNDHOUSECODE"	INTEGER NOT NULL,
	"SCHEMECODE"	INTEGER NOT NULL,
	"SCHEMETYPEID"	INTEGER NOT NULL,
	"PURCHASE_DATE"	TEXT,
	"PURCHASE_NAV"	REAL,
	"PURCHASE_UNITS"	REAL,
	"VALUE_AT_COST"	REAL,
	FOREIGN KEY("MASTER_ID") REFERENCES "PORTFOLIO_MASTER"("ROWID") ON DELETE RESTRICT ON UPDATE RESTRICT,
	FOREIGN KEY("SCHEMECODE") REFERENCES "SCHEMES"("SCHEMECODE") ON DELETE RESTRICT ON UPDATE RESTRICT,
	FOREIGN KEY("FUNDHOUSECODE") REFERENCES "FUNDHOUSE"("FUNDHOUSECODE") ON DELETE RESTRICT ON UPDATE RESTRICT,
	FOREIGN KEY("SCHEMETYPEID") REFERENCES "SCHEME_TYPE"("ID") ON DELETE RESTRICT ON UPDATE RESTRICT
)

SELECT seq from sqlite_sequence WHERE name="PORTFOLIO"