-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsqlite_schema.sql
121 lines (121 loc) · 3.97 KB
/
sqlite_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
CREATE TABLE history (
date DATETIME,
total_liquid FLOAT,
total_real_estate FLOAT,
total_retirement FLOAT,
total_investing FLOAT,
etfs FLOAT,
commodities FLOAT,
"ira" FLOAT,
pillar2 FLOAT
, total_no_homes float generated always as (total_liquid+total_retirement+total_investing), total float generated always as (total_no_homes+total_real_estate));
CREATE TABLE forex (
date DATETIME,
"CHFUSD" FLOAT,
"SGDUSD" FLOAT
);
CREATE TABLE wealthfront_cash_yield (
date DATETIME,
percent FLOAT
);
CREATE TABLE swvxx_yield (
date DATETIME,
percent FLOAT
);
CREATE TABLE "schwab_etfs_amounts" (
date DATETIME,
"SCHA" FLOAT,
"SCHF" FLOAT,
"SCHR" FLOAT,
"SCHX" FLOAT
, [SWTSX] FLOAT, [SWISX] FLOAT, [SWAGX] FLOAT, [SCHZ] FLOAT, [IBKR] FLOAT, [SCHB] FLOAT, [GLDM] FLOAT, [SGOL] FLOAT, [SIVR] FLOAT, [SCHE] FLOAT, [SCHO] FLOAT, [COIN] FLOAT, [BITX] FLOAT, [MSTR] FLOAT, [SGOV] FLOAT, [VV] FLOAT);
CREATE TABLE schwab_etfs_prices (
date DATETIME,
"SCHA" FLOAT,
"SCHF" FLOAT,
"SCHR" FLOAT,
"SCHX" FLOAT
, [SWTSX] FLOAT, [SWISX] FLOAT, [SWAGX] FLOAT, [SCHZ] FLOAT, [IBKR] FLOAT, [SCHB] FLOAT, [GLDM] FLOAT, [SGOL] FLOAT, [SIVR] FLOAT, [SCHE] FLOAT, [SCHO] FLOAT, [COIN] FLOAT, [BITX] FLOAT, [MSTR] FLOAT, [SGOV] FLOAT, [VV] FLOAT);
CREATE TABLE schwab_ira_amounts (
date DATETIME,
"SWYGX" FLOAT
);
CREATE TABLE schwab_ira_prices (
date DATETIME,
"SWYGX" FLOAT
);
CREATE TABLE "toshl_income_export_2023-01-01" (
"Date" DATETIME,
"Category" TEXT,
"Tags" TEXT,
"Income amount" FLOAT,
"Currency" TEXT,
"In main currency" FLOAT,
"Main currency" TEXT,
"Description" TEXT
);
CREATE TABLE "toshl_expenses_export_2023-01-01" (
"Date" DATETIME,
"Category" TEXT,
"Tags" TEXT,
"Expense amount" FLOAT,
"Currency" TEXT,
"In main currency" FLOAT,
"Main currency" TEXT,
"Description" TEXT
);
CREATE TABLE real_estate(name TEXT PRIMARY KEY NOT NULL);
CREATE TABLE real_estate_prices(date DATETIME, name TEXT NOT NULL REFERENCES real_estate(name), redfin_value BIGINT, zillow_value BIGINT, value BIGINT generated always as ((redfin_value+zillow_value)/2));
CREATE TABLE real_estate_rents(date DATETIME, name TEXT NOT NULL REFERENCES real_estate(name), value BIGINT);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE swygx_holdings (
date DATETIME,
"SCHX" FLOAT,
"SCHF" FLOAT,
"SCHZ" FLOAT,
"SCHH" FLOAT,
"SCHA" FLOAT,
"SCHE" FLOAT,
"USD" FLOAT,
"SCHO" FLOAT,
"SVUXX" FLOAT
, SGUXX FLOAT);
CREATE TABLE swtsx_market_cap (
date DATETIME,
"US_LARGE_CAP" FLOAT,
"US_SMALL_CAP" FLOAT
);
CREATE TABLE interactive_brokers_margin_rates (
date DATETIME,
"USD" FLOAT,
"CHF" FLOAT
);
CREATE TABLE index_prices (
date DATETIME,
"^SPX" FLOAT, [^SSMI] FLOAT);
CREATE TABLE fedfunds (
date DATETIME,
percent FLOAT
);
CREATE TABLE sofr (
date DATETIME,
percent FLOAT
);
CREATE INDEX ix_history_date ON history (date);
CREATE INDEX ix_forex_date ON forex (date);
CREATE INDEX ix_wealthfront_cash_yield_date ON wealthfront_cash_yield (date);
CREATE INDEX ix_swvxx_yield_date ON swvxx_yield (date);
CREATE INDEX ix_schwab_etfs_prices_date ON schwab_etfs_prices (date);
CREATE INDEX ix_schwab_etfs_amounts_date ON schwab_etfs_amounts (date);
CREATE INDEX ix_schwab_ira_amounts_date ON schwab_ira_amounts (date);
CREATE INDEX ix_schwab_ira_prices_date ON schwab_ira_prices (date);
CREATE INDEX "ix_toshl_income_export_2023-01-01_Date" ON "toshl_income_export_2023-01-01" ("Date");
CREATE INDEX "ix_toshl_expenses_export_2023-01-01_Date" ON "toshl_expenses_export_2023-01-01" ("Date");
CREATE INDEX ix_real_estate_prices_date ON real_estate_prices (date);
CREATE INDEX ix_real_estate_rents_date ON real_estate_rents (date);
CREATE INDEX ix_swygx_holdings_date ON swygx_holdings (date);
CREATE INDEX ix_swtsx_market_cap_date ON swtsx_market_cap (date);
CREATE INDEX ix_interactive_brokers_margin_rates_date ON interactive_brokers_margin_rates (date);
CREATE INDEX ix_index_prices_date ON index_prices (date);
CREATE INDEX ix_fedfunds_date ON fedfunds (date);
CREATE INDEX ix_sofr_date ON sofr (date);