setup_database

#!/usr/bin/env perl

use FindBin;
use lib "$FindBin::Bin/lib";
use lib "$FindBin::Bin/extlib/lib/perl5";
use DBIx::Custom;
use File::Path qw(make_path);

# make the data folder here
my $dir = 'data/rep';
if (!-e $dir) {
    make_path $dir or die "Error creating directory: $dir";
}

$dir = 'data/work';
if (!-e $dir) {
    make_path $dir or die "Error creating directory: $dir";
}

# setup the database

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";
}


# Table definitions.
my @tables = (
  {
    name => 'user',
    fields => [
      {name => 'id', unique => 1},
      {name => 'email', unique => 1},
      {name => 'admin', type => 'integer'},
      {name => 'password'},
      {name => 'salt'},
      {name => 'name'},
      {name => 'show_email', type => 'integer'},
      {name => 'avatar', type => 'blob'},
      {name => 'bio'},
      {name => 'url'},
      {name => 'location'},
      {name => 'company'},
      {name => 'social'}
    ]
  },
  {
    name => 'project',
    fields => [
      {name => 'user', type => 'integer'},
      {name => 'id'},
      {name => 'original_project', type => 'integer'},
      {name => 'private', type => 'integer'},
      {name => 'ignore_space_change', type => 'integer'},
      {name => 'guess_encoding'},
      {name => 'website_url'}
    ],
    unique => ['user', 'id']
  },
  {
    name => 'ssh_public_key',
    fields => [
      {name => 'key', unique => 1},
      {name => 'user', type => 'integer'},
      {name => 'title'}
    ]
  },
  {
    name => 'collaboration',
    fields => [
      {name => 'project', type => 'integer'},
      {name => 'user', type => 'integer'}
    ],
    unique => ['project', 'user']
  },
  {
    name => 'issue',
    fields => [
      {name => 'project', type => 'integer'},
      {name => 'number', type => 'integer'},
      {name => 'title'},
      {name => 'open', type => 'integer'},
      {name => 'open_time', type => 'integer'},
      {name => 'open_user', type => 'integer'},
      {name => 'pull_request', type => 'integer'}
    ],
    unique => ['project', 'number']
  },
  {
    name => 'issue_message',
    fields => [
      {name => 'issue', type => 'integer'},
      {name => 'number', type => 'integer'},
      {name => 'message'},
      {name => 'create_time', type => 'integer'},
      {name => 'update_time', type => 'integer'},
      {name => 'user', type => 'integer'},
      {name => 'reactions'}
    ],
    unique => ['issue', 'number']
  },
  {
    name => 'pull_request',
    fields => [
      {name => 'base_project', type => 'integer'},
      {name => 'base_branch'},
      {name => 'target_project', type => 'integer'},
      {name => 'target_branch'},
      {name => 'create_time', type => 'integer'},
      {name => 'update_time', type => 'integer'},
      {name => 'user', type => 'integer'}
    ],
    unique => ['base_project', 'base_branch', 'target_project', 'target_branch']
  },
  {
    name => 'label',
    fields => [
      {name => 'project', type => 'integer'},
      {name => 'id', type => 'varchar(100)'},
      {name => 'description'},
      {name => 'color'}
    ],
    unique => ['project', 'id']
  },
  {
    name => 'issue_label',
    fields => [
      {name => 'issue', type => 'integer'},
      {name => 'label', type => 'integer'}
    ],
    unique => ['issue', 'label']
  },
  {
    name => 'wiki',
    fields => [
      {name => 'project', type => 'integer'}
    ],
    unique => ['project']
  },
  {
    name => 'subscription',
    fields => [
      {name => 'issue', type => 'integer'},
      {name => 'user', type => 'integer'},
      {name => 'reason', type => 'text'}
    ],
    unique => ['issue', 'user']
  },
  {
    name => 'watch',
    fields => [
      {name => 'user', type => 'integer'},
      {name => 'project', type => 'integer'}
    ],
    unique => ['user', 'project']
  },
  {
    name => 'ruleset',
    fields => [
      {name => 'project', type => 'integer'},
      {name => 'name'},
      {name => 'target'},
      {name => 'active', type => 'integer'},
      {name => 'owner_bypass', type => 'integer'},
      {name => 'creation', type => 'integer'},
      {name => 'updating', type => 'integer'},
      {name => 'deletion', type => 'integer'},
      {name => 'required_signatures', type => 'integer'},
      {name => 'non_fast_forward', type => 'integer'}
    ],
    unique => ['project', 'name', 'target']
  },
  {
    name => 'ruleset_selector',
    fields => [
      {name => 'ruleset', type => 'integer'},
      {name => 'kind'},
      {name => 'selector'}
    ]
  },
);


sub create_table {
  my ($table) = @_;

  # Create table if needed and make sure all fields are present.
  my $create = "create table $table->{name} (row_id integer primary key autoincrement";
  my @alters;
  my @columns = ('row_id');
  foreach my $field (@{$table->{fields}}) {
    my $define = $field->{name};
    my $type = $field->{type};
    $define .= " $type" if $field->{type};
    $define .= ' not null' unless $field->{nullable};
    $define .= ' unique' if $field->{unique};
    my $default = $field->{default};
    $type //= '';
    unless ($field->{nullable} || $type =~ /null/i || defined $default) {
      # Force a default for not null fields.
      $default = $type =~ /integer|real/i? '0': q('');
    }
    $define .= " default $default" if defined $default;
    $create .= ", $define";
    push @alters, $define;
    push @columns, $field->{name};
  };
  $create .= ', unique(' . join(', ', @{$table->{unique}}) . ')' if $table->{unique};

  # Create table if not yet done.
  eval { $dbi->execute("$create);") };

  # Add fields that may not be present.
  foreach my $alter (@alters) {
    eval { $dbi->execute("alter table $table->{name} add column $alter") };
  }

  # Check that everything is ok.
  eval { $dbi->select(\@columns, table => $table->{name}) };
  if ($@) {
    my $error = "Can't create $table->{name} table properly: $@";
    die $error;
  }
}


# Create database

foreach my $table (@tables) {
  create_table($table);
}