summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2019-03-06 18:11:26 +0100
committerDaniel Friesel <derf@finalrewind.org>2019-03-06 18:11:26 +0100
commitf2efaef2b9e20ee1e89df49e31dd3d8dfaa1384d (patch)
tree8f8fcd200b150e6f83271dae3c233fa2601ac6d6
parent058d93a6fd9f186c46ff8f53a444a914fb226d8a (diff)
bump DB schema to v2
Add token to "users" (for mail verification etc) Add new table "pending_mails" (to avoid spamming individual addresses)
-rwxr-xr-xindex.pl5
-rwxr-xr-xmigrate.pl76
2 files changed, 78 insertions, 3 deletions
diff --git a/index.pl b/index.pl
index 2157c03..1969b8c 100755
--- a/index.pl
+++ b/index.pl
@@ -187,7 +187,7 @@ app->attr(
return $self->app->dbh->prepare(
qq{
select
- id, name, status, is_public, email,
+ id, name, status, public_level, email,
registered_at, last_login, deletion_requested
from users where id = ?
}
@@ -536,8 +536,9 @@ helper 'get_user_id' => sub {
id integer primary key,
name char(64) not null unique,
status int not null,
- is_public bool not null,
+ public_level bool not null,
email char(256),
+ token char(80),
password text,
registered_at datetime not null,
last_login datetime not null,
diff --git a/migrate.pl b/migrate.pl
index 722eea4..3b4e8dc 100755
--- a/migrate.pl
+++ b/migrate.pl
@@ -78,7 +78,7 @@ my @migrations = (
insert into new_users
(id, name, status, is_public, registered_at, last_login)
values
- (?, ?, ?, ?, ?, ?)
+ (?, ?, ?, ?, ?, ?);
}
);
$get_users_query->execute;
@@ -100,6 +100,80 @@ my @migrations = (
);
$dbh->commit;
},
+
+ # v1 -> v2
+ sub {
+ $dbh->begin_work;
+ $dbh->do(
+ qq{
+ update schema_version set version = 2;
+ }
+ );
+ $dbh->do(
+ qq{
+ create table new_users (
+ id integer primary key,
+ name char(64) not null unique,
+ status int not null,
+ public_level int not null,
+ email char(256),
+ token char(80),
+ password text,
+ registered_at datetime not null,
+ last_login datetime not null,
+ deletion_requested datetime
+ );
+ }
+ );
+ my $get_users_query = $dbh->prepare(
+ qq{
+ select * from users;
+ }
+ );
+
+ # At this point, some "users" fields were never used -> skip those
+ # during migration.
+ my $add_user_query = $dbh->prepare(
+ qq{
+ insert into new_users
+ (id, name, status, public_level, registered_at, last_login)
+ values
+ (?, ?, ?, ?, ?, ?);
+ }
+ );
+
+ $get_users_query->execute;
+
+ while ( my @row = $get_users_query->fetchrow_array ) {
+ my (
+ $id, $name, $status,
+ $is_public, $email, $password,
+ $reg_at, $last_login, $del_requested
+ ) = @row;
+ $add_user_query->execute( $id, $name, $status, $is_public, $reg_at,
+ $last_login );
+ }
+ $dbh->do(
+ qq{
+ drop table users;
+ }
+ );
+ $dbh->do(
+ qq{
+ alter table new_users rename to users;
+ }
+ );
+ $dbh->do(
+ qq{
+ create table pending_mails (
+ email char(256) not null primary key,
+ num_tries int not null,
+ last_try datetime not null
+ );
+ }
+ );
+ $dbh->commit;
+ },
);
my $schema_version = get_schema_version();