view release on metacpan or  search on metacpan

lib/App/Standby/DB.pm  view on Meta::CPAN

    if($dbh->do($sql_group_services)) {
        #$self->logger()->log( message => 'Table groups OK', level => 'debug', );
    } else {
        $self->logger()->log( message => 'Failed to create table group_services: '.$dbh->errstr, level => 'error', );
    }

    my $sql_ctcs = <<EOS;
CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY ASC,
        name TEXT,
        cellphone TEXT,
        group_id INTEGER,
        is_enabled INTEGER,
        ordinal INTEGER,
        CONSTRAINT fk_gid FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE
);
EOS

    if($dbh->do($sql_ctcs)) {
        #$self->logger()->log( message => 'Table contacts OK', level => 'debug', );
    } else {
        $self->logger()->log( message => 'Failed to create table contacts: '.$dbh->errstr, level => 'error', );
    }

    my $sql_log = <<EOS;
CREATE TABLE IF NOT EXISTS log (
        id INTEGER PRIMARY KEY ASC,
        group_id INTEGER,
        remote_addr INTEGER,
        msg TEXT,
);
EOS

    #if($dbh->do($sql_log)) {
    #    #$self->logger()->log( message => 'Table log OK', level => 'debug', );
    #} else {
    #    $self->logger()->log( message => 'Failed to create table log: '.$dbh->errstr, level => 'error', );
    #}

    my $sql_sstats = <<EOS;
CREATE TABLE IF NOT EXISTS shift_stats (
        id INTEGER PRIMARY KEY ASC,
        contact_id INTEGER,
        num_shifts INTEGER,
        CONSTRAINT fk_cid FOREIGN KEY (contact_id) REFERENCES contacts (id) ON DELETE CASCADE
);
EOS

    #if($dbh->do($sql_sstats)) {
    #    #$self->logger()->log( message => 'Table shift_stats OK', level => 'debug', );
    #} else {
    #    $self->logger()->log( message => 'Failed to create table shift_stats: '.$dbh->errstr, level => 'error', );
    #}

    # SQLite honors FK constraints only if the are explicitly turned on ...
    # http://www.sqlite.org/foreignkeys.html#fk_enable
    $dbh->do('PRAGMA foreign_keys = ON;');

    # Speed up a bit, if we encounter a crash we have more to worry about than
    # just a messed up spooler queue ...
    # http://www.sqlite.org/pragma.html#pragma_synchronous
    $dbh->do('PRAGMA synchronous = OFF;');

    $self->_check_db_version($dbh);

    return 1;
}

sub _check_db_version {
    my $self = shift;
    my $dbh = shift;

    $dbh->do('BEGIN TRANSACTION');

    my $db_version = 0; # set default
    my $sql = 'SELECT value FROM meta WHERE `key` = ?';
    my $sth = $dbh->prepare($sql);
    if($sth) {
        if($sth->execute('version')) {
            $db_version = $sth->fetchrow_array();
        } else {
            $self->logger()->log( message => 'Failed to execute statement w/ error: '.$sth->errstr, level => 'warning', );
        }
        $sth->finish();
    } else {
        $self->logger()->log( message => 'Failed to prepare statement from SQL: '.$sql.' w/ error: '.$dbh->errstr, level => 'warning', );
    }

    # place any version independent statements here

    # place any version dependent statements inside
    if(defined($db_version)) {
        if($db_version < 2) {
            # do upgrades to version 2 here
            # remove column class from table groups
            my $sql = <<EOS;
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE groups_backup(id,name);
DROP TABLE groups;
CREATE TABLE IF NOT EXISTS groups (
        id INTEGER PRIMARY KEY ASC,
        name TEXT
);
INSERT INTO groups SELECT id,name FROM groups_backup;
DROP TABLE groups_backup;
COMMIT;
EOS
            # TODO HIGH do multi-statements work es expected?
            $dbh->do($sql);
        }
        if($db_version < 3) {
            my $sql = 'ALTER TABLE groups ADD COLOUMN key TEXT';
            $dbh->do($sql);
        }
        if($db_version < 4) {
            my $sql = 'DROP TABLE notify_order';
            $dbh->do($sql);
        }
        if($db_version < 5) {
            # remove column class from table contacts
            my $sql = <<EOS;
BEGIN TRANSACTION;