MarketDatabase.sqlite.sql

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "__EFMigrationsHistory";
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
	"MigrationId"	TEXT NOT NULL,
	"ProductVersion"	TEXT NOT NULL,
	CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY("MigrationId")
);
DROP TABLE IF EXISTS "AspNetRoles";
CREATE TABLE IF NOT EXISTS "AspNetRoles" (
	"Id"	TEXT NOT NULL,
	"Name"	TEXT,
	"NormalizedName"	TEXT,
	"ConcurrencyStamp"	TEXT,
	CONSTRAINT "PK_AspNetRoles" PRIMARY KEY("Id")
);
DROP TABLE IF EXISTS "AspNetUsers";
CREATE TABLE IF NOT EXISTS "AspNetUsers" (
	"Id"	TEXT NOT NULL,
	"UserName"	TEXT,
	"NormalizedUserName"	TEXT,
	"Email"	TEXT,
	"NormalizedEmail"	TEXT,
	"EmailConfirmed"	INTEGER NOT NULL,
	"PasswordHash"	TEXT,
	"SecurityStamp"	TEXT,
	"ConcurrencyStamp"	TEXT,
	"PhoneNumber"	TEXT,
	"PhoneNumberConfirmed"	INTEGER NOT NULL,
	"TwoFactorEnabled"	INTEGER NOT NULL,
	"LockoutEnd"	TEXT,
	"LockoutEnabled"	INTEGER NOT NULL,
	"AccessFailedCount"	INTEGER NOT NULL,
	CONSTRAINT "PK_AspNetUsers" PRIMARY KEY("Id")
);
DROP TABLE IF EXISTS "UpdateTracker";
CREATE TABLE IF NOT EXISTS "UpdateTracker" (
	"UpdateTracker_ID"	INTEGER NOT NULL,
	"REF_DATE"	TEXT NOT NULL,
	"TYPE"	TEXT,
	"DATA"	TEXT,
	"StockMasterID"	INTEGER NOT NULL,
	CONSTRAINT "PK_UpdateTracker" PRIMARY KEY("UpdateTracker_ID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "AspNetRoleClaims";
CREATE TABLE IF NOT EXISTS "AspNetRoleClaims" (
	"Id"	INTEGER NOT NULL,
	"RoleId"	TEXT NOT NULL,
	"ClaimType"	TEXT,
	"ClaimValue"	TEXT,
	CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY("RoleId") REFERENCES "AspNetRoles"("Id") ON DELETE CASCADE,
	CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY("Id" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "AspNetUserClaims";
CREATE TABLE IF NOT EXISTS "AspNetUserClaims" (
	"Id"	INTEGER NOT NULL,
	"UserId"	TEXT NOT NULL,
	"ClaimType"	TEXT,
	"ClaimValue"	TEXT,
	CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE,
	CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY("Id" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "AspNetUserLogins";
CREATE TABLE IF NOT EXISTS "AspNetUserLogins" (
	"LoginProvider"	TEXT NOT NULL,
	"ProviderKey"	TEXT NOT NULL,
	"ProviderDisplayName"	TEXT,
	"UserId"	TEXT NOT NULL,
	CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE,
	CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY("LoginProvider","ProviderKey")
);
DROP TABLE IF EXISTS "AspNetUserRoles";
CREATE TABLE IF NOT EXISTS "AspNetUserRoles" (
	"UserId"	TEXT NOT NULL,
	"RoleId"	TEXT NOT NULL,
	CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY("RoleId") REFERENCES "AspNetRoles"("Id") ON DELETE CASCADE,
	CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE,
	CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY("UserId","RoleId")
);
DROP TABLE IF EXISTS "AspNetUserTokens";
CREATE TABLE IF NOT EXISTS "AspNetUserTokens" (
	"UserId"	TEXT NOT NULL,
	"LoginProvider"	TEXT NOT NULL,
	"Name"	TEXT NOT NULL,
	"Value"	TEXT,
	CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE,
	CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY("UserId","LoginProvider","Name")
);
DROP TABLE IF EXISTS "BEARISH_ENGULFING";
CREATE TABLE IF NOT EXISTS "BEARISH_ENGULFING" (
	"BEARISH_ENGULFING_ID"	INTEGER NOT NULL,
	"BUY_CANDLE_DATE"	TEXT NOT NULL,
	"SELL_CANDLE_DATE"	TEXT NOT NULL,
	"BUY_PRICE"	REAL NOT NULL,
	"SELL_PRICE"	REAL NOT NULL,
	"StockMasterID"	INTEGER NOT NULL,
	CONSTRAINT "FK_BEARISH_ENGULFING_StockMaster_StockMasterID" FOREIGN KEY("StockMasterID") REFERENCES "StockMaster"("StockMasterID") ON DELETE CASCADE,
	CONSTRAINT "PK_BEARISH_ENGULFING" PRIMARY KEY("BEARISH_ENGULFING_ID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "BULLISH_ENGULFING_STRATEGY";
CREATE TABLE IF NOT EXISTS "BULLISH_ENGULFING_STRATEGY" (
	"BULLISH_ENGULFING_STRATEGY_ID"	INTEGER NOT NULL,
	"BUY_CANDLE_DATE"	TEXT NOT NULL,
	"SELL_CANDLE_DATE"	TEXT NOT NULL,
	"BUY_PRICE"	REAL NOT NULL,
	"SELL_PRICE"	REAL NOT NULL,
	"AVG_BUY_PRICE"	REAL NOT NULL,
	"StockMasterID"	INTEGER NOT NULL,
	CONSTRAINT "FK_BULLISH_ENGULFING_STRATEGY_StockMaster_StockMasterID" FOREIGN KEY("StockMasterID") REFERENCES "StockMaster"("StockMasterID") ON DELETE CASCADE,
	CONSTRAINT "PK_BULLISH_ENGULFING_STRATEGY" PRIMARY KEY("BULLISH_ENGULFING_STRATEGY_ID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "StockPriceHistory";
CREATE TABLE IF NOT EXISTS "StockPriceHistory" (
	"StockPriceHistoryID"	INTEGER NOT NULL,
	"PriceDate"	TEXT NOT NULL,
	"Open"	REAL NOT NULL,
	"High"	REAL NOT NULL,
	"Low"	REAL NOT NULL,
	"Close"	REAL NOT NULL,
	"Volume"	REAL NOT NULL,
	"Change"	REAL NOT NULL,
	"ChangePercent"	REAL NOT NULL,
	"PrevClose"	REAL NOT NULL,
	"RSI_OPEN"	REAL,
	"RSI_CLOSE"	REAL,
	"RSI_HIGH"	REAL,
	"RSI_LOW"	REAL,
	"SMA_SMALL"	REAL,
	"SMA_MID"	REAL,
	"SMA_LONG"	REAL,
	"CROSSOVER_FLAG"	TEXT,
	"LOWER_THAN_SMA_SMALL"	INTEGER NOT NULL,
	"BULLISH_ENGULFING"	INTEGER NOT NULL,
	"BUY_SMA_STRATEGY"	REAL,
	"SELL_SMA_STRATEGY"	REAL,
	"V20_CANDLE"	INTEGER NOT NULL,
	"V20_CANDLE_BUY_PRICE"	REAL,
	"V20_CANDLE_SELL_PRICE"	REAL,
	"SlowD"	REAL,
	"FastK"	REAL,
	"StockMasterID"	INTEGER NOT NULL,
	CONSTRAINT "FK_StockPriceHistory_StockMaster_StockMasterID" FOREIGN KEY("StockMasterID") REFERENCES "StockMaster"("StockMasterID") ON DELETE CASCADE,
	CONSTRAINT "PK_StockPriceHistory" PRIMARY KEY("StockPriceHistoryID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "V20_CANDLE_STRATEGY";
CREATE TABLE IF NOT EXISTS "V20_CANDLE_STRATEGY" (
	"V20_CANDLE_STRATEGY_ID"	INTEGER NOT NULL,
	"FROM_DATE"	TEXT NOT NULL,
	"TO_DATE"	TEXT NOT NULL,
	"DIFF_PCT"	REAL NOT NULL,
	"BUY_PRICE"	REAL NOT NULL,
	"SELL_PRICE"	REAL NOT NULL,
	"StockMasterID"	INTEGER NOT NULL,
	CONSTRAINT "FK_V20_CANDLE_STRATEGY_StockMaster_StockMasterID" FOREIGN KEY("StockMasterID") REFERENCES "StockMaster"("StockMasterID") ON DELETE CASCADE,
	CONSTRAINT "PK_V20_CANDLE_STRATEGY" PRIMARY KEY("V20_CANDLE_STRATEGY_ID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "SMA_BUYSELL";
CREATE TABLE IF NOT EXISTS "SMA_BUYSELL" (
	"SMA_BUYSELL_ID"	,
	"SMA_FAST"	,
	"SMA_MID"	,
	"SMA_SLOW"	,
	"CLOSE"	,
	"BUY_INDICATOR"	,
	"SELL_INDICATOR"	,
	"StockMasterID"	
);
DROP TABLE IF EXISTS "USER_MASTER";
CREATE TABLE IF NOT EXISTS "USER_MASTER" (
	"USER_MASTER_ID"	INTEGER NOT NULL,
	"USER_ID"	TEXT NOT NULL,
	"USER_PWD"	TEXT NOT NULL,
	"USER_TYPE"	INTEGER NOT NULL,
	CONSTRAINT "PK_USER_MASTER" PRIMARY KEY("USER_MASTER_ID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "StockMaster";
CREATE TABLE IF NOT EXISTS "StockMaster" (
	"StockMasterID"	INTEGER NOT NULL,
	"Exchange"	TEXT NOT NULL,
	"Symbol"	TEXT NOT NULL,
	"CompName"	NUMERIC NOT NULL,
	"INVESTMENT_TYPE"	TEXT,
	"QuoteDateTime"	TEXT,
	"Open"	REAL NOT NULL,
	"High"	REAL NOT NULL,
	"Low"	REAL NOT NULL,
	"Close"	REAL NOT NULL,
	"Volume"	REAL NOT NULL,
	"Change"	REAL NOT NULL,
	"ChangePercent"	REAL NOT NULL,
	"PrevClose"	REAL NOT NULL,
	"V40"	INTEGER NOT NULL,
	"V40N"	INTEGER NOT NULL,
	"V200"	INTEGER NOT NULL,
	"SMA_BUY_SIGNAL"	INTEGER NOT NULL,
	"SMA_SELL_SIGNAL"	INTEGER NOT NULL,
	"STOCH_BUY_SIGNAL"	INTEGER NOT NULL DEFAULT 0,
	"STOCH_SELL_SIGNAL"	INTEGER NOT NULL DEFAULT 0,
	"RSI_OVERBOUGHT"	INTEGER NOT NULL DEFAULT 0,
	"RSI_OVERSOLD"	INTEGER NOT NULL DEFAULT 0,
	"SMA_FAST"	REAL NOT NULL,
	"SMA_MID"	REAL NOT NULL,
	"SMA_SLOW"	REAL NOT NULL,
	"RSI_CLOSE"	REAL NOT NULL DEFAULT 0,
	"SlowD"	REAL DEFAULT 0,
	"FastK"	REAL DEFAULT 0,
	"STOCH_BUY_PRICE"	REAL NOT NULL DEFAULT 0,
	"STOCH_SELL_PRICE"	REAL NOT NULL DEFAULT 0,
	"YEAR_HI"	REAL NOT NULL,
	"YEAR_LO"	REAL NOT NULL,
	"LIFETIME_HIGH"	REAL NOT NULL,
	"LIFETIME_LOW"	REAL NOT NULL,
	"LESSTHAN_67PCT_ON"	TEXT NOT NULL,
	"DIFF_FROM_YEAR_HI"	REAL NOT NULL,
	"DIFF_FROM_LIFETIME_HIGH"	REAL NOT NULL,
	"HI_LOW_67_50_LastUpDt"	TEXT NOT NULL,
	"SMA_LastUpDt"	TEXT NOT NULL,
	"RSI_LastUpDt"	TEXT NOT NULL,
	"STOCH_LastUpDt"	TEXT NOT NULL,
	"BULL_ENGULF_LastUpDt"	TEXT NOT NULL,
	"BEAR_ENGULF_LastUpDt"	TEXT NOT NULL,
	"V20_LastUpDt"	TEXT NOT NULL,
	"SMA_BUYSELL_LastUpDt"	TEXT NOT NULL,
	"STOCH_BUYSELL_LastUpDt"	TEXT NOT NULL DEFAULT '0001-01-01',
	"RSI_TREND_LastUpDt"	TEXT NOT NULL DEFAULT '0001-01-01',
	CONSTRAINT "PK_StockMaster" PRIMARY KEY("StockMasterID" AUTOINCREMENT)
);
DROP TABLE IF EXISTS "PORTFOLIO_MASTER";
CREATE TABLE IF NOT EXISTS "PORTFOLIO_MASTER" (
	"PORTFOLIO_MASTER_ID"	INTEGER NOT NULL,
	"PORTFOLIO_NAME"	TEXT NOT NULL,
	"Id"	TEXT,
	CONSTRAINT "PK_PORTFOLIO_MASTER" PRIMARY KEY("PORTFOLIO_MASTER_ID" AUTOINCREMENT),
	CONSTRAINT "FK_PORTFOLIO_MASTER_AspNetUsers_Id" FOREIGN KEY("Id") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE,
	FOREIGN KEY("Id") REFERENCES "AspNetUsers"("Id")
);
DROP TABLE IF EXISTS "PORTFOLIOTXN";
CREATE TABLE IF NOT EXISTS "PORTFOLIOTXN" (
	"PORTFOLIOTXN_ID"	INTEGER NOT NULL,
	"PORTFOLIO_MASTER_ID"	INTEGER NOT NULL,
	"StockMasterID"	INTEGER NOT NULL,
	"TXN_BUY_DATE"	TEXT NOT NULL,
	"TXN_TYPE"	TEXT NOT NULL DEFAULT 'B',
	"PURCHASE_QUANTITY"	REAL NOT NULL,
	"COST_PER_UNIT"	REAL NOT NULL,
	"TOTAL_COST"	REAL NOT NULL,
	"TXN_SELL_DATE"	TEXT NOT NULL DEFAULT '0001-01-01',
	"SELL_QUANTITY"	REAL NOT NULL DEFAULT 0,
	"SELL_AMT_PER_UNIT"	REAL DEFAULT 0,
	"TOTAL_SELL_AMT"	REAL DEFAULT 0,
	"DAYS_SINCE"	INTEGER NOT NULL,
	"SOLD_AFTER"	INTEGER NOT NULL DEFAULT 0,
	"CMP"	REAL,
	"VALUE"	REAL,
	"CAGR"	REAL DEFAULT 0,
	"GAIN_PCT"	REAL,
	"SELL_GAIN_PCT"	REAL NOT NULL DEFAULT 0,
	"GAIN_AMT"	REAL,
	"SELL_GAIN_AMT"	REAL NOT NULL DEFAULT 0,
	"BUY_VS_52HI"	REAL NOT NULL,
	"LastUpDt"	TEXT NOT NULL,
	CONSTRAINT "PK_PORTFOLIOTXN" PRIMARY KEY("PORTFOLIOTXN_ID" AUTOINCREMENT),
	CONSTRAINT "FK_PORTFOLIOTXN_StockMaster_StockMasterID" FOREIGN KEY("StockMasterID") REFERENCES "StockMaster"("StockMasterID") ON DELETE CASCADE,
	CONSTRAINT "FK_PORTFOLIOTXN_PORTFOLIO_MASTER_PORTFOLIO_MASTER_ID" FOREIGN KEY("PORTFOLIO_MASTER_ID") REFERENCES "PORTFOLIO_MASTER"("PORTFOLIO_MASTER_ID") ON DELETE CASCADE
);
DROP INDEX IF EXISTS "IX_AspNetRoleClaims_RoleId";
CREATE INDEX IF NOT EXISTS "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" (
	"RoleId"
);
DROP INDEX IF EXISTS "RoleNameIndex";
CREATE UNIQUE INDEX IF NOT EXISTS "RoleNameIndex" ON "AspNetRoles" (
	"NormalizedName"
);
DROP INDEX IF EXISTS "IX_AspNetUserClaims_UserId";
CREATE INDEX IF NOT EXISTS "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" (
	"UserId"
);
DROP INDEX IF EXISTS "IX_AspNetUserLogins_UserId";
CREATE INDEX IF NOT EXISTS "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" (
	"UserId"
);
DROP INDEX IF EXISTS "IX_AspNetUserRoles_RoleId";
CREATE INDEX IF NOT EXISTS "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" (
	"RoleId"
);
DROP INDEX IF EXISTS "EmailIndex";
CREATE INDEX IF NOT EXISTS "EmailIndex" ON "AspNetUsers" (
	"NormalizedEmail"
);
DROP INDEX IF EXISTS "UserNameIndex";
CREATE UNIQUE INDEX IF NOT EXISTS "UserNameIndex" ON "AspNetUsers" (
	"NormalizedUserName"
);
DROP INDEX IF EXISTS "IX_BEARISH_ENGULFING_StockMasterID";
CREATE INDEX IF NOT EXISTS "IX_BEARISH_ENGULFING_StockMasterID" ON "BEARISH_ENGULFING" (
	"StockMasterID"
);
DROP INDEX IF EXISTS "IX_BULLISH_ENGULFING_STRATEGY_StockMasterID";
CREATE INDEX IF NOT EXISTS "IX_BULLISH_ENGULFING_STRATEGY_StockMasterID" ON "BULLISH_ENGULFING_STRATEGY" (
	"StockMasterID"
);
DROP INDEX IF EXISTS "IX_StockPriceHistory_StockMasterID";
CREATE INDEX IF NOT EXISTS "IX_StockPriceHistory_StockMasterID" ON "StockPriceHistory" (
	"StockMasterID"
);
DROP INDEX IF EXISTS "IX_V20_CANDLE_STRATEGY_StockMasterID";
CREATE INDEX IF NOT EXISTS "IX_V20_CANDLE_STRATEGY_StockMasterID" ON "V20_CANDLE_STRATEGY" (
	"StockMasterID"
);
DROP INDEX IF EXISTS "IX_PORTFOLIOTXN_PORTFOLIO_MASTER_ID";
CREATE INDEX IF NOT EXISTS "IX_PORTFOLIOTXN_PORTFOLIO_MASTER_ID" ON "PORTFOLIOTXN" (
	"PORTFOLIO_MASTER_ID"
);
DROP INDEX IF EXISTS "IX_PORTFOLIOTXN_StockMasterID";
CREATE INDEX IF NOT EXISTS "IX_PORTFOLIOTXN_StockMasterID" ON "PORTFOLIOTXN" (
	"StockMasterID"
);
COMMIT;