bump DB schema to v2

Add token to "users" (for mail verification etc)
Add new table "pending_mails" (to avoid spamming individual addresses)

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();