gitprep
/
setup_database
/
1 contributor
#!/usr/bin/env perl
use FindBin;
use lib "$FindBin::Bin/lib";
use lib "$FindBin::Bin/extlib/lib/perl5";
use DBIx::Custom;
my $database_file = shift // "$FindBin::Bin/data/gitprep.db";
# DBI
my %dbi_args = (
dsn => "dbi:SQLite:database=$database_file",
connector => 1,
option => {sqlite_unicode => 1, sqlite_use_immediate_transaction => 1}
);
my $dbi = DBIx::Custom->connect(%dbi_args);
# Database state
my $database_state;
if (!-f $database_file) {
$database_state = 'empty';
}
else {
# If project.user_id exists, that database is version 1
eval { $dbi->select('user_id', table => 'project', append => 'limit 0, 1') };
if ($@) {
$database_state = 'current';
}
else {
$database_state = 'v1';
}
}
# Need upgrade
if ($database_state eq 'v1') {
die "Can't setup database. you maybe need upgrade database";
}
# Create database
else {
# Create user table
eval {
my $sql = <<"EOS";
create table user (
row_id integer primary key autoincrement,
id not null unique default '',
email not null unique default ''
);
EOS
$dbi->execute($sql);
};
# Create user columns
my $user_columns = [
"admin integer not null default 0",
"password not null default ''",
"salt not null default ''",
"name not null default ''"
];
for my $column (@$user_columns) {
eval { $dbi->execute("alter table user add column $column") };
}
# Check user table
eval { $dbi->select([qw/row_id id admin password salt email name/], table => 'user') };
if ($@) {
my $error = "Can't create user table properly: $@";
die $error;
}
# Create project table
eval {
my $sql = <<"EOS";
create table project (
row_id integer primary key autoincrement,
user integer not null default 0,
id not null,
unique(user, id)
);
EOS
$dbi->execute($sql);
};
# Create Project columns
my $project_columns = [
"default_branch not null default 'master'",
"original_project integer not null default 0",
"private integer not null default 0",
"ignore_space_change integer not null default 0",
"guess_encoding integer not null default ''",
"website_url not null default ''"
];
for my $column (@$project_columns) {
eval { $dbi->execute("alter table project add column $column") };
}
# Check project table
eval {
$dbi->select(
[qw/row_id user id default_branch original_project private ignore_space_change guess_encoding website_url/],
table => 'project'
);
};
if ($@) {
my $error = "Can't create project table properly: $@";
die $error;
}
# Create ssh_public_key table
eval {
my $sql = <<"EOS";
create table ssh_public_key (
row_id integer primary key autoincrement,
key not null unique default ''
);
EOS
$dbi->execute($sql);
};
# Create ssh_public_key columns
my $ssh_public_key_columns = [
"user integer not null default 0",
"title not null default ''"
];
for my $column (@$ssh_public_key_columns) {
eval { $dbi->execute("alter table ssh_public_key add column $column") };
}
# Check ssh_public_key table
eval { $dbi->select([qw/row_id user key title/], table => 'ssh_public_key') };
if ($@) {
my $error = "Can't create ssh_public_key table properly: $@";
die $error;
}
# Create collaboration table
eval {
my $sql = <<"EOS";
create table collaboration (
row_id integer primary key autoincrement,
project integer not null default 0,
user integer not null default 0,
unique(project, user)
);
EOS
$dbi->execute($sql);
};
# Check collaboration table
eval { $dbi->select([qw/row_id project user/], table => 'collaboration') };
if ($@) {
my $error = "Can't create collaboration table properly: $@";
die $error;
}
# Create issue table
eval {
my $sql = <<"EOS";
create table issue (
row_id integer primary key autoincrement,
project integer not null default 0,
number integer not null default 0,
unique(project, number)
);
EOS
$dbi->execute($sql);
};
# Create issue columns
my @issue_columns = (
"title not null default ''",
"open integer not null default 0",
"open_time integer not null default 0",
"open_user integer not null default 0",
"pull_request integer not null default 0",
);
for my $column (@issue_columns) {
eval { $dbi->execute("alter table issue add column $column") };
}
# Check issue table
eval { $dbi->select([qw/row_id title open open_time open_user pull_request project/], table => 'issue') };
if ($@) {
my $error = "Can't create issue table properly: $@";
die $error;
}
# Create issue_message table
eval {
my $sql = <<"EOS";
create table issue_message (
row_id integer primary key autoincrement,
issue integer not null default 0,
number integer not null default 0,
unique(issue, number)
);
EOS
$dbi->execute($sql);
};
# Create issue_message columns
my @issue_message_columns = (
"message not null default ''",
"create_time integer not null default 0",
"update_time integer not null default 0",
"user integer not null default 0"
);
for my $column (@issue_message_columns) {
eval { $dbi->execute("alter table issue_message add column $column") };
}
# Check issue_message table
eval { $dbi->select([qw/row_id issue number message create_time update_time user/], table => 'issue_message') };
if ($@) {
my $error = "Can't create issue_message table properly: $@";
die $error;
}
# Create pull_request table
eval {
my $sql = <<"EOS";
create table pull_request (
row_id integer primary key autoincrement,
base_project integer not null default 0,
base_branch not null default '',
target_project integer not null default 0,
target_branch not null default '',
unique(base_project, base_branch, target_project, target_branch)
);
EOS
$dbi->execute($sql);
};
# Create pull_request columns
my @pull_request_columns = ();
for my $column (@pull_request_columns) {
eval { $dbi->execute("alter table pull_request add column $column") };
}
# Check pull_request table
eval { $dbi->select([qw/row_id base_project base_branch target_project target_branch/], table => 'pull_request') };
if ($@) {
my $error = "Can't create pull_request table properly: $@";
die $error;
}
# Create label table
eval {
my $sql = <<"EOS";
create table label (
row_id integer primary key autoincrement,
project integer not null default 0,
id varchar(100) not null default '',
unique(project, id)
);
EOS
$dbi->execute($sql);
};
# Create label columns
my @label_columns = (
"color not null default ''"
);
for my $column (@label_columns) {
eval { $dbi->execute("alter table label add column $column") };
}
# Check label table
eval { $dbi->select([qw/row_id project id color/], table => 'label') };
if ($@) {
my $error = "Can't create label table properly: $@";
die $error;
}
# Create issue_label table
eval {
my $sql = <<"EOS";
create table issue_label (
row_id integer primary key autoincrement,
issue integer not null default 0,
label integer not null default 0,
unique(issue, label)
);
EOS
$dbi->execute($sql);
};
# Create issue_label columns
my @issue_label_columns = (
);
for my $column (@issue_label_columns) {
eval { $dbi->execute("alter table issue_label add column $column") };
}
# Check issue_label table
eval { $dbi->select([qw/row_id issue label/], table => 'issue_label') };
if ($@) {
my $error = "Can't create issue_label table properly: $@";
die $error;
}
}