private void ToVersion2(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE AnalysisTemplates (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL,
Template TEXT NOT NULL
)");
}
private void ToVersion10(AssetsContext ctx)
{
UpdateTable(ctx.Database, "Companies", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL,
Homepage TEXT NULL,
Sector TEXT NULL,
Country TEXT NULL",
"Id, Name, Homepage, Sector, Country");
}
private void ToVersion6(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"DROP TABLE Companies");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Companies (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL,
Homepage TEXT NULL,
Sector TEXT NULL,
Country TEXT NULL,
XdbPath TEXT NULL
)");
}
private void ToVersion4(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"DROP TABLE Translations");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Translations (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Rate DOUBLE NOT NULL,
Timestamp DATETIME NOT NULL,
SourceId INTEGER NOT NULL,
TargetId INTEGER NOT NULL,
FOREIGN KEY(SourceId) REFERENCES Currencies(Id),
FOREIGN KEY(TargetId) REFERENCES Currencies(Id)
)");
}
private void ToVersion8(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Assets (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
}
private void ToVersion14(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Tags (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE CompanyTags (
Tag_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Tag_id) REFERENCES Tags(Id) ON DELETE CASCADE
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE
)");
}
private void ToVersion15(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Currencies ADD COLUMN Symbol TEXT;");
foreach (var currency in ctx.Currencies)
{
currency.Symbol = "ToBeDefined";
}
ctx.SaveChanges();
UpdateTable(ctx.Database, "Currencies", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL,
Symbol TEXT NOT NULL",
"Id, Name, Symbol");
}
private void ToVersion3(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Currencies (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Translations (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Rate DOUBLE NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_id INTEGER NOT NULL,
FOREIGN KEY(Currency_id) REFERENCES Currencies(Id)
)");
}
private void ToVersion5(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"DROP TABLE Companies");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Companies (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL,
Homepage TEXT NULL,
Sector TEXT NULL,
Country TEXT NULL
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE 'References' (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Url TEXT NOT NULL,
Company_id INTEGER NOT NULL,
FOREIGN KEY(Company_id) REFERENCES Companies(Id)
)");
}
private void ToVersion1(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE SchemaInfoes (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Version INTEGER NOT NULL
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Companies (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name TEXT NOT NULL
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Stocks (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Isin TEXT NOT NULL,
Company_id INTEGER NOT NULL,
FOREIGN KEY(Company_id) REFERENCES Companies(Id)
)");
}
public void Initialize()
{
SchemaInfo schemaInfo = null;
if (!File.Exists(Location))
{
SQLiteConnection.CreateFile(Location);
schemaInfo = new SchemaInfo {
Id = -1, Version = 0
};
}
using (var context = new AssetsContext(Location))
{
if (schemaInfo == null)
{
schemaInfo = context.SchemaInfos.Single();
}
var migrationScript = new DatabaseMigrations();
while (schemaInfo.Version < DatabaseMigrations.RequiredDatabaseVersion)
{
schemaInfo.Version++;
migrationScript.Migrations[schemaInfo.Version](context);
context.SaveChanges();
}
if (schemaInfo.Id == -1)
{
context.SchemaInfos.Add(schemaInfo);
context.SaveChanges();
}
}
myIsInitialized = true;
}
private void ToVersion12(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Companies ADD COLUMN Guid TEXT;");
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Stocks ADD COLUMN Guid TEXT;");
foreach (var company in ctx.Companies)
{
company.Guid = Guid.NewGuid().ToString();
}
foreach (var stock in ctx.Stocks)
{
stock.Guid = Guid.NewGuid().ToString();
}
ctx.SaveChanges();
UpdateTable(ctx.Database, "Companies", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Guid TEXT NOT NULL,
Name TEXT NOT NULL,
Homepage TEXT NULL,
Sector TEXT NULL,
Country TEXT NULL",
"Id, Guid, Name, Homepage, Sector, Country");
UpdateTable(ctx.Database, "Stocks", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Guid TEXT NOT NULL,
Isin TEXT NOT NULL,
Wpkn TEXT,
Symbol TEXT,
Company_id INTEGER NOT NULL,
FOREIGN KEY(Company_id) REFERENCES Companies(Id)",
"Id, Guid, Isin, Wpkn, Symbol, Company_Id");
}
private void ToVersion9(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Debts (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Dividends (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE EBITs (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Equities (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE InterestExpenses (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Liabilities (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE NetIncomes (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE Revenues (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
ctx.Database.ExecuteSqlCommand(@"
CREATE TABLE SharesOutstandings (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Company_Id) REFERENCES Companies(Id)
)");
}
private void ToVersion11(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Stocks ADD COLUMN Wpkn TEXT;");
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Stocks ADD COLUMN Symbol TEXT;");
}
private void ToVersion17(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Debts RENAME TO TotalLiabilities;");
}
private void ToVersion16(AssetsContext ctx)
{
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Assets RENAME TO CurrentAssets;");
ctx.Database.ExecuteSqlCommand(@"ALTER TABLE Liabilities RENAME TO CurrentLiabilities;");
}
private void ToVersion13(AssetsContext ctx)
{
UpdateTable(ctx.Database, "'References'", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Url TEXT NOT NULL,
Company_id INTEGER NOT NULL,
FOREIGN KEY(Company_id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Url, Company_Id");
UpdateTable(ctx.Database, "Stocks", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Guid TEXT NOT NULL,
Isin TEXT NOT NULL,
Wpkn TEXT,
Symbol TEXT,
Company_id INTEGER NOT NULL,
FOREIGN KEY(Company_id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Guid, Isin, Wpkn, Symbol, Company_Id");
UpdateTable(ctx.Database, "Prices", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Stock_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Stock_Id) REFERENCES Stocks(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Stock_Id");
UpdateTable(ctx.Database, "Assets", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "Debts", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "Dividends", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "EBITs", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "Equities", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "InterestExpenses", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "Liabilities", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "NetIncomes", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "Revenues", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Currency_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Currency_Id) REFERENCES Currencies(Id)
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Currency_Id, Company_Id");
UpdateTable(ctx.Database, "SharesOutstandings", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value DOUBLE NOT NULL,
Source TEXT NOT NULL,
RawPeriod TEXT NOT NULL,
Timestamp DATETIME NOT NULL,
Company_Id INTEGER NOT NULL,
FOREIGN KEY(Company_Id) REFERENCES Companies(Id) ON DELETE CASCADE",
"Id, Value, Source, RawPeriod, Timestamp, Company_Id");
UpdateTable(ctx.Database, "Translations", @"
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Rate DOUBLE NOT NULL,
Timestamp DATETIME NOT NULL,
SourceId INTEGER NOT NULL,
TargetId INTEGER NOT NULL,
FOREIGN KEY(SourceId) REFERENCES Currencies(Id) ON DELETE CASCADE,
FOREIGN KEY(TargetId) REFERENCES Currencies(Id) ON DELETE CASCADE",
"Id, Rate, Timestamp, SourceId, TargetId");
}