| 1 | # Database creation. | ||
| 2 | |||
| 3 | # Copyright 2007, 2008, 2009, 2011, 2014 Kevin Ryde | ||
| 4 | |||
| 5 | # This file is part of Chart. | ||
| 6 | # | ||
| 7 | # Chart is free software; you can redistribute it and/or modify it under the | ||
| 8 | # terms of the GNU General Public License as published by the Free Software | ||
| 9 | # Foundation; either version 3, or (at your option) any later version. | ||
| 10 | # | ||
| 11 | # Chart is distributed in the hope that it will be useful, but WITHOUT ANY | ||
| 12 | # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS | ||
| 13 | # FOR A PARTICULAR PURPOSE. See the GNU General Public License for more | ||
| 14 | # details. | ||
| 15 | # | ||
| 16 | # You should have received a copy of the GNU General Public License along | ||
| 17 | # with Chart. If not, see <http://www.gnu.org/licenses/>. | ||
| 18 | |||
| 19 | package App::Chart::Database::Create; | ||
| 20 | |||
| 21 | use strict; | ||
| 22 | use warnings; | ||
| 23 | use DBI; | ||
| 24 | use File::Basename; | ||
| 25 | use File::Path; | ||
| 26 | use Locale::TextDomain ('App-Chart'); | ||
| 27 | |||
| 28 | use App::Chart::Database; | ||
| 29 | |||
| 30 | |||
| 31 | # Cribs: | ||
| 32 | # | ||
| 33 | # "TEXT" for unlimited length isn't SQL standard, but it's in sqlite, | ||
| 34 | # postgres, and apparently others | ||
| 35 | |||
| 36 | |||
| 37 | my $create_latest = <<'HERE'; | ||
| 38 | CREATE TABLE latest ( | ||
| 39 | symbol TEXT NOT NULL PRIMARY KEY, | ||
| 40 | name TEXT DEFAULT NULL, | ||
| 41 | month DATE DEFAULT NULL, | ||
| 42 | exchange TEXT DEFAULT NULL, | ||
| 43 | currency TEXT DEFAULT NULL, | ||
| 44 | quote_date DATE DEFAULT NULL, | ||
| 45 | quote_time TIME DEFAULT NULL, | ||
| 46 | bid TEXT DEFAULT NULL, | ||
| 47 | offer TEXT DEFAULT NULL, | ||
| 48 | last_date DATE DEFAULT NULL, | ||
| 49 | last_time TIME DEFAULT NULL, | ||
| 50 | open TEXT DEFAULT NULL, | ||
| 51 | high TEXT DEFAULT NULL, | ||
| 52 | low TEXT DEFAULT NULL, | ||
| 53 | last TEXT DEFAULT NULL, | ||
| 54 | change TEXT DEFAULT NULL, | ||
| 55 | volume TEXT DEFAULT NULL, | ||
| 56 | source TEXT NOT NULL, | ||
| 57 | halt BOOLEAN DEFAULT 0 NOT NULL, | ||
| 58 | limit_up BOOLEAN DEFAULT 0 NOT NULL, | ||
| 59 | limit_down BOOLEAN DEFAULT 0 NOT NULL, | ||
| 60 | dividend TEXT DEFAULT NULL, | ||
| 61 | note TEXT DEFAULT NULL, | ||
| 62 | error TEXT DEFAULT NULL, | ||
| 63 | fetch_timestamp TEXT NOT NULL, | ||
| 64 | copyright TEXT DEFAULT NULL, | ||
| 65 | url TEXT DEFAULT NULL, | ||
| 66 | etag TEXT DEFAULT NULL, | ||
| 67 | last_modified TEXT DEFAULT NULL) | ||
| 68 | HERE | ||
| 69 | |||
| 70 | my $create_intraday_image = <<'HERE'; | ||
| 71 | CREATE TABLE intraday_image ( | ||
| 72 | symbol TEXT NOT NULL, | ||
| 73 | mode TEXT NOT NULL, | ||
| 74 | image BLOB DEFAULT NULL, | ||
| 75 | error TEXT DEFAULT NULL, | ||
| 76 | fetch_timestamp TEXT NOT NULL, | ||
| 77 | url TEXT DEFAULT NULL, | ||
| 78 | etag TEXT DEFAULT NULL, | ||
| 79 | last_modified TEXT DEFAULT NULL, | ||
| 80 | PRIMARY KEY (symbol, mode)) | ||
| 81 | HERE | ||
| 82 | |||
| 83 | my $create_preference = <<'HERE'; | ||
| 84 | CREATE TABLE preference ( | ||
| 85 | key TEXT NOT NULL, | ||
| 86 | value TEXT NOT NULL, | ||
| 87 | PRIMARY KEY (key)) | ||
| 88 | HERE | ||
| 89 | |||
| 90 | # DATABASE_SCHEMA_VERSION revisions | ||
| 91 | # | ||
| 92 | # Schema 1, version 99.015 | ||
| 93 | # - "latest" - fetch_timestamp rather than fetch_unixtime | ||
| 94 | # - "intraday_image" - fetch_timestamp likewise | ||
| 95 | # - "extra" - timestamp strings rather than unixtime numbers | ||
| 96 | # - "info" - new exchange column | ||
| 97 | # | ||
| 98 | # Schema 2, version 99.023 | ||
| 99 | # - "preference" - missed PRIMARY KEY | ||
| 100 | # | ||
| 101 | sub upgrade_database { | ||
| 102 | my ($dbh, $dbversion) = @_; | ||
| 103 | print "Chart: Upgrading database from $dbversion to ", | ||
| 104 | App::Chart::DBI::DATABASE_SCHEMA_VERSION(),"\n"; | ||
| 105 | require App::Chart::Download; | ||
| 106 | |||
| 107 | App::Chart::Database::call_with_transaction | ||
| 108 | ($dbh, sub { | ||
| 109 | # version 1 adopting timestamps instead of unixtime | ||
| 110 | if ($dbversion <= 0) { _upgrade_0_to_1 ($dbh); } | ||
| 111 | if ($dbversion <= 1) { _upgrade_1_to_2 ($dbh); } | ||
| 112 | |||
| 113 | $dbh->do ("INSERT OR REPLACE INTO extra (symbol,key,value) | ||
| 114 | VALUES ('','database-schema-version',?)", | ||
| 115 | undef, | ||
| 116 | App::Chart::DBI::DATABASE_SCHEMA_VERSION()); | ||
| 117 | }); | ||
| 118 | } | ||
| 119 | sub _upgrade_0_to_1 { | ||
| 120 | my ($dbh) = @_; | ||
| 121 | $dbh->do ('ALTER TABLE info ADD COLUMN | ||
| 122 | exchange TEXT DEFAULT NULL'); | ||
| 123 | |||
| 124 | $dbh->do ('DROP TABLE latest'); | ||
| 125 | $dbh->do ($create_latest); | ||
| 126 | |||
| 127 | $dbh->do ('DROP TABLE intraday_image'); | ||
| 128 | $dbh->do ($create_intraday_image); | ||
| 129 | |||
| 130 | $dbh->do ("DELETE FROM extra WHERE key like '%unixtime'"); | ||
| 131 | App::Chart::Download::consider_latest_from_daily | ||
| 132 | ([ App::Chart::Database->symbols_list() ]); | ||
| 133 | } | ||
| 134 | sub _upgrade_1_to_2 { | ||
| 135 | my $nbh = nbh(); | ||
| 136 | $nbh->do ('DROP TABLE preference'); | ||
| 137 | $nbh->do ($create_preference); | ||
| 138 | } | ||
| 139 | |||
| 140 | |||
| 141 | # $database_filename is in filesystem charset bytes | ||
| 142 | sub initial_database { | ||
| 143 | my ($database_filename) = @_; | ||
| 144 | print __x("Creating {filename}\n", | ||
| 145 | filename => Glib::filename_display_name($database_filename)); | ||
| 146 | |||
| 147 | File::Path::mkpath (File::Basename::dirname ($database_filename)); | ||
| 148 | my $dbh = DBI->connect ("dbi:SQLite:dbname=$database_filename", | ||
| 149 | '', '', {RaiseError=>1}); | ||
| 150 | $dbh->{sqlite_unicode} = 1; | ||
| 151 | |||
| 152 | $dbh->do ('PRAGMA encoding = "UTF-8"'); | ||
| 153 | |||
| 154 | $dbh->do (<<'HERE'); | ||
| 155 | CREATE TABLE daily ( | ||
| 156 | symbol TEXT NOT NULL, | ||
| 157 | date DATE NOT NULL, | ||
| 158 | open TEXT DEFAULT NULL, | ||
| 159 | high TEXT DEFAULT NULL, | ||
| 160 | low TEXT DEFAULT NULL, | ||
| 161 | close TEXT DEFAULT NULL, | ||
| 162 | volume TEXT DEFAULT NULL, | ||
| 163 | openint TEXT DEFAULT NULL, | ||
| 164 | PRIMARY KEY (symbol, date)) | ||
| 165 | HERE | ||
| 166 | |||
| 167 | # 'exchange' is for yahoo index and US symbols to differentiate NYSE, AMEX, | ||
| 168 | # NASDAQ, Philadelphia, etc, for other symbols the suffix gives the exchange | ||
| 169 | # | ||
| 170 | $dbh->do (<<'HERE'); | ||
| 171 | CREATE TABLE info ( | ||
| 172 | symbol TEXT NOT NULL PRIMARY KEY, | ||
| 173 | name TEXT DEFAULT NULL, | ||
| 174 | exchange TEXT DEFAULT NULL, | ||
| 175 | isin TEXT DEFAULT NULL, | ||
| 176 | decimals INT DEFAULT 0 NOT NULL, | ||
| 177 | currency TEXT DEFAULT NULL, | ||
| 178 | historical BOOLEAN DEFAULT 0 NOT NULL, | ||
| 179 | CHECK (decimals >= 0)) | ||
| 180 | HERE | ||
| 181 | |||
| 182 | # 'type' is meant to allow multiple flavours on the same day, like a capital | ||
| 183 | # return and a dividend. Empty string '' for ordinary dividends. (It's not | ||
| 184 | # NULL because a null in a primary key defeats the uniqueness (you can | ||
| 185 | # insert multiple symbol+ex_date with type=null). | ||
| 186 | # | ||
| 187 | # 'qualifier' values: | ||
| 188 | # TBA with amount NULL | ||
| 189 | # estimated when amount present, but only estimated | ||
| 190 | # unknown when outright unknown, with amount NULL | ||
| 191 | # | ||
| 192 | $dbh->do (<<'HERE'); | ||
| 193 | CREATE TABLE dividend ( | ||
| 194 | symbol TEXT NOT NULL, | ||
| 195 | ex_date DATE NOT NULL, | ||
| 196 | record_date DATE DEFAULT NULL, | ||
| 197 | pay_date DATE DEFAULT NULL, | ||
| 198 | type TEXT NOT NULL DEFAULT '', | ||
| 199 | amount TEXT, | ||
| 200 | imputation TEXT DEFAULT NULL, | ||
| 201 | qualifier TEST DEFAULT NULL, | ||
| 202 | note TEXT DEFAULT NULL, | ||
| 203 | PRIMARY KEY (symbol, ex_date, type)) | ||
| 204 | HERE | ||
| 205 | |||
| 206 | $dbh->do (<<'HERE'); | ||
| 207 | CREATE TABLE split ( | ||
| 208 | symbol TEXT NOT NULL, | ||
| 209 | date DATE NOT NULL, | ||
| 210 | new TEXT NOT NULL, -- number | ||
| 211 | old TEXT NOT NULL, -- number | ||
| 212 | note TEXT DEFAULT NULL, | ||
| 213 | PRIMARY KEY (symbol, date)) | ||
| 214 | HERE | ||
| 215 | |||
| 216 | $dbh->do ($create_latest); | ||
| 217 | |||
| 218 | $dbh->do ($create_intraday_image); | ||
| 219 | |||
| 220 | $dbh->do (<<'HERE'); | ||
| 221 | CREATE TABLE extra ( | ||
| 222 | symbol TEXT NOT NULL, | ||
| 223 | key TEXT NOT NULL, | ||
| 224 | value TEXT, | ||
| 225 | PRIMARY KEY (symbol, key)) | ||
| 226 | HERE | ||
| 227 | |||
| 228 | $dbh->do ("INSERT INTO extra (symbol,key,value) | ||
| 229 | VALUES ('','database-schema-version',?)", | ||
| 230 | undef, | ||
| 231 | App::Chart::DBI::DATABASE_SCHEMA_VERSION()); | ||
| 232 | } | ||
| 233 | |||
| 234 | sub nbh { | ||
| 235 | require App::Chart::DBI; | ||
| 236 | my $notes_filename = App::Chart::DBI::notes_filename(); | ||
| 237 | File::Path::mkpath (File::Basename::dirname ($notes_filename)); | ||
| 238 | return DBI->connect ("dbi:SQLite:dbname=$notes_filename", | ||
| 239 | '', '', {RaiseError=>1}); | ||
| 240 | } | ||
| 241 | |||
| 242 | # $notes_filename is in filesystem charset bytes | ||
| 243 | sub initial_notes { | ||
| 244 | my ($notes_filename) = @_; | ||
| 245 | print __x("Creating {filename}\n", | ||
| 246 | filename => Glib::filename_display_name($notes_filename)); | ||
| 247 | |||
| 248 | my $nbh = nbh(); | ||
| 249 | $nbh->{sqlite_unicode} = 1; | ||
| 250 | $nbh->do ('PRAGMA encoding = "UTF-8"'); | ||
| 251 | |||
| 252 | $nbh->do ($create_preference); | ||
| 253 | |||
| 254 | $nbh->do (<<'HERE'); | ||
| 255 | CREATE TABLE annotation ( | ||
| 256 | symbol TEXT NOT NULL, | ||
| 257 | id INT NOT NULL, | ||
| 258 | date DATE NOT NULL, | ||
| 259 | note TEXT NOT NULL, | ||
| 260 | PRIMARY KEY (symbol, id)) | ||
| 261 | HERE | ||
| 262 | |||
| 263 | $nbh->do (<<'HERE'); | ||
| 264 | CREATE TABLE line ( | ||
| 265 | symbol TEXT NOT NULL, | ||
| 266 | id INT NOT NULL, | ||
| 267 | date1 DATE NOT NULL, | ||
| 268 | price1 TEXT NOT NULL, | ||
| 269 | date2 DATE NOT NULL, | ||
| 270 | price2 TEXT NOT NULL, | ||
| 271 | horizontal BOOLEAN NOT NULL DEFAULT 0, | ||
| 272 | PRIMARY KEY (symbol, id)) | ||
| 273 | HERE | ||
| 274 | |||
| 275 | $nbh->do (<<'HERE'); | ||
| 276 | CREATE TABLE alert ( | ||
| 277 | symbol TEXT NOT NULL, | ||
| 278 | id INT NOT NULL, | ||
| 279 | price TEXT NOT NULL, | ||
| 280 | above BOOLEAN NOT NULL, | ||
| 281 | PRIMARY KEY (symbol, id)) | ||
| 282 | HERE | ||
| 283 | |||
| 284 | # Ought to have "symlist.key" unique and NOT NULL, and the intention is | ||
| 285 | # for "symlist_content.symbol" to be NOT NULL too, but it's easier for | ||
| 286 | # treeview drag and drop to loosen both those, so it can insert an empty, | ||
| 287 | # set it to a copy of the source, then delete the source. | ||
| 288 | # | ||
| 289 | # The symlist key lookups are mostly done in-memory, so don't really need | ||
| 290 | # an index. Or maybe should have it for the REFERENCES constraint, except | ||
| 291 | # that's not enforced by sqlite anyway. | ||
| 292 | # | ||
| 293 | # CREATE INDEX symlist_key_index ON symlist (key) | ||
| 294 | # | ||
| 295 | $nbh->do (<<'HERE'); | ||
| 296 | CREATE TABLE symlist ( | ||
| 297 | seq INT NOT NULL, | ||
| 298 | key TEXT DEFAULT NULL, | ||
| 299 | name TEXT DEFAULT NULL, | ||
| 300 | condition TEXT DEFAULT NULL, | ||
| 301 | PRIMARY KEY (seq)) | ||
| 302 | HERE | ||
| 303 | |||
| 304 | $nbh->do (<<'HERE'); | ||
| 305 | CREATE TABLE symlist_content ( | ||
| 306 | key TEXT NOT NULL REFERENCES symlist, | ||
| 307 | seq INT NOT NULL, | ||
| 308 | symbol TEXT DEFAULT NULL, | ||
| 309 | note TEXT DEFAULT NULL, | ||
| 310 | PRIMARY KEY (key, seq)) | ||
| 311 | HERE | ||
| 312 | |||
| 313 | my $sth = $nbh->prepare ('INSERT INTO symlist (seq, key, name, condition) | ||
| 314 | VALUES (?,?,?,?)'); | ||
| 315 | $sth->execute (0, 'alerts', __('Alerts'), 'alert'); | ||
| 316 | $sth->execute (1, 'favourites', __('Favourites'), undef); | ||
| 317 | $sth->execute (2, 'all', __('All'), 'not historical'); | ||
| 318 | $sth->execute (3, 'historical', __('Historical'), 'historical'); | ||
| 319 | $sth->finish; | ||
| 320 | } | ||
| 321 | |||
| 322 | 1; | ||
| 323 | __END__ |