App/Chart/Database/Create.pm untested

filter: Statementstmt 0.0% 0 / 86 Branchbran 0.0% 0 / 4 Conditioncond n/a MC/DCmcdc n/a Subroutinesub 0.0% 0 / 15 Podpod 0.0% 0 / 6 totaltotal 0.0% 0 / 111 CC 3 SCAR 13.3
CC
3
Cov
0%
upgrade_database
12.0
_upgrade_0_to_1
2.0
_upgrade_1_to_2
2.0
CRAP 3.8
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__