view release on metacpan or  search on metacpan

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

    # 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;
CREATE TEMPORARY TABLE contacts_backup(id,name,cellphone,group_id,is_enabled,ordinal);
DROP TABLE contacts;
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
);
INSERT INTO contacts SELECT id,name,cellphone,group_id,is_enabled,ordinal FROM contacts_backup;
DROP TABLE contacts_backup;
COMMIT;
EOS
            # TODO HIGH do multi-statements work es expected?
            $dbh->do($sql);
        }
    }

    # finally we set the current version which we've reached by upgrading (or not)
    $sql = "INSERT OR REPLACE INTO meta ('key','value') VALUES('version',5);";
    $dbh->do($sql);