view release on metacpan or  search on metacpan

lib/App/Chart/Database/Create.pm  view on Meta::CPAN

# DATABASE_SCHEMA_VERSION revisions
#
# Schema 1, version 99.015
#   - "latest" - fetch_timestamp rather than fetch_unixtime
#   - "intraday_image" - fetch_timestamp likewise
#   - "extra" - timestamp strings rather than unixtime numbers
#   - "info" - new exchange column
#   
# Schema 2, version 99.023
#   - "preference" - missed PRIMARY KEY
#
sub upgrade_database {
  my ($dbh, $dbversion) = @_;
  print "Chart: Upgrading database from $dbversion to ",
    App::Chart::DBI::DATABASE_SCHEMA_VERSION(),"\n";
  require App::Chart::Download;

  App::Chart::Database::call_with_transaction
      ($dbh, sub {
         # version 1 adopting timestamps instead of unixtime
         if ($dbversion <= 0) { _upgrade_0_to_1 ($dbh); }
         if ($dbversion <= 1) { _upgrade_1_to_2 ($dbh); }

         $dbh->do ("INSERT OR REPLACE INTO extra (symbol,key,value)
                    VALUES ('','database-schema-version',?)",
                   undef,
                   App::Chart::DBI::DATABASE_SCHEMA_VERSION());
       });
}
sub _upgrade_0_to_1 {
  my ($dbh) = @_;
  $dbh->do ('ALTER TABLE info ADD COLUMN
                      exchange TEXT DEFAULT NULL');

  $dbh->do ('DROP TABLE latest');
  $dbh->do ($create_latest);

  $dbh->do ('DROP TABLE intraday_image');
  $dbh->do ($create_intraday_image);

  $dbh->do ("DELETE FROM extra WHERE key like '%unixtime'");
  App::Chart::Download::consider_latest_from_daily
      ([ App::Chart::Database->symbols_list() ]);
}
sub _upgrade_1_to_2 {
  my $nbh = nbh();
  $nbh->do ('DROP TABLE preference');
  $nbh->do ($create_preference);
}


# $database_filename is in filesystem charset bytes
sub initial_database {
  my ($database_filename) = @_;
  print __x("Creating {filename}\n",
            filename => Glib::filename_display_name($database_filename));

  File::Path::mkpath (File::Basename::dirname ($database_filename));
  my $dbh = DBI->connect ("dbi:SQLite:dbname=$database_filename",
                       '', '', {RaiseError=>1});
  $dbh->{sqlite_unicode} = 1;

  $dbh->do ('PRAGMA encoding = "UTF-8"');

  $dbh->do (<<'HERE');
CREATE TABLE daily (
    symbol  TEXT     NOT NULL,
    date    DATE     NOT NULL,
    open    TEXT     DEFAULT NULL,
    high    TEXT     DEFAULT NULL,
    low     TEXT     DEFAULT NULL,
    close   TEXT     DEFAULT NULL,
    volume  TEXT     DEFAULT NULL,
    openint TEXT     DEFAULT NULL,
    PRIMARY KEY (symbol, date))
HERE

# 'exchange' is for yahoo index and US symbols to differentiate NYSE, AMEX,
# NASDAQ, Philadelphia, etc, for other symbols the suffix gives the exchange
#
  $dbh->do (<<'HERE');
CREATE TABLE info (
    symbol     TEXT     NOT NULL       PRIMARY KEY,
    name       TEXT     DEFAULT NULL,
    exchange   TEXT     DEFAULT NULL,
    isin       TEXT     DEFAULT NULL,
    decimals   INT      DEFAULT 0      NOT NULL,
    currency   TEXT     DEFAULT NULL,
    historical BOOLEAN  DEFAULT 0      NOT NULL,
    CHECK (decimals >= 0))
HERE

# 'type' is meant to allow multiple flavours on the same day, like a capital
# return and a dividend.  Empty string '' for ordinary dividends.  (It's not
# NULL because a null in a primary key defeats the uniqueness (you can
# insert multiple symbol+ex_date with type=null).
#
# 'qualifier' values:
#    TBA          with amount NULL
#    estimated    when amount present, but only estimated
#    unknown      when outright unknown, with amount NULL
#
  $dbh->do (<<'HERE');
CREATE TABLE dividend (
    symbol          TEXT     NOT NULL,
    ex_date         DATE     NOT NULL,
    record_date     DATE     DEFAULT NULL,
    pay_date        DATE     DEFAULT NULL,
    type            TEXT     NOT NULL DEFAULT '',
    amount          TEXT,
    imputation      TEXT     DEFAULT NULL,
    qualifier       TEST     DEFAULT NULL,
    note            TEXT     DEFAULT NULL,
    PRIMARY KEY (symbol, ex_date, type))
HERE

  $dbh->do (<<'HERE');
CREATE TABLE split (
    symbol TEXT     NOT NULL,
    date   DATE     NOT NULL,
    new    TEXT     NOT NULL,  -- number
    old    TEXT     NOT NULL,  -- number
    note   TEXT     DEFAULT NULL,
    PRIMARY KEY (symbol, date))
HERE

  $dbh->do ($create_latest);

  $dbh->do ($create_intraday_image);

  $dbh->do (<<'HERE');
CREATE TABLE extra (
    symbol        TEXT  NOT NULL,
    key           TEXT  NOT NULL,
    value         TEXT,
    PRIMARY KEY (symbol, key))
HERE

  $dbh->do ("INSERT INTO extra (symbol,key,value)
             VALUES ('','database-schema-version',?)",
            undef,
            App::Chart::DBI::DATABASE_SCHEMA_VERSION());
}

sub nbh {
  require App::Chart::DBI;
  my $notes_filename = App::Chart::DBI::notes_filename();
  File::Path::mkpath (File::Basename::dirname ($notes_filename));
  return DBI->connect ("dbi:SQLite:dbname=$notes_filename",
                       '', '', {RaiseError=>1});
}

# $notes_filename is in filesystem charset bytes
sub initial_notes {
  my ($notes_filename) = @_;
  print __x("Creating {filename}\n",
            filename => Glib::filename_display_name($notes_filename));

  my $nbh = nbh();
  $nbh->{sqlite_unicode} = 1;
  $nbh->do ('PRAGMA encoding = "UTF-8"');

  $nbh->do ($create_preference);

  $nbh->do (<<'HERE');
CREATE TABLE annotation (
    symbol     TEXT     NOT NULL,
    id         INT      NOT NULL,
    date       DATE     NOT NULL,
    note       TEXT     NOT NULL,
    PRIMARY KEY (symbol, id))
HERE

  $nbh->do (<<'HERE');
CREATE TABLE line (
    symbol     TEXT     NOT NULL,
    id         INT      NOT NULL,
    date1      DATE     NOT NULL,
    price1     TEXT     NOT NULL,
    date2      DATE     NOT NULL,
    price2     TEXT     NOT NULL,
    horizontal BOOLEAN  NOT NULL  DEFAULT 0,
    PRIMARY KEY (symbol, id))
HERE

  $nbh->do (<<'HERE');
CREATE TABLE alert (
    symbol     TEXT     NOT NULL,
    id         INT      NOT NULL,
    price      TEXT     NOT NULL,
    above      BOOLEAN  NOT NULL,
    PRIMARY KEY (symbol, id))
HERE

  # Ought to have "symlist.key" unique and NOT NULL, and the intention is
  # for "symlist_content.symbol" to be NOT NULL too, but it's easier for
  # treeview drag and drop to loosen both those, so it can insert an empty,
  # set it to a copy of the source, then delete the source.
  #
  # The symlist key lookups are mostly done in-memory, so don't really need
  # an index.  Or maybe should have it for the REFERENCES constraint, except
  # that's not enforced by sqlite anyway.
  #
  # CREATE INDEX symlist_key_index ON symlist (key)
  #
  $nbh->do (<<'HERE');
CREATE TABLE symlist (
    seq        INT      NOT NULL,
    key        TEXT     DEFAULT NULL,
    name       TEXT     DEFAULT NULL,
    condition  TEXT     DEFAULT NULL,
    PRIMARY KEY (seq))
HERE

  $nbh->do (<<'HERE');
CREATE TABLE symlist_content (
    key        TEXT     NOT NULL  REFERENCES symlist,
    seq        INT      NOT NULL,
    symbol     TEXT     DEFAULT NULL,
    note       TEXT     DEFAULT NULL,