servnest/db/migrations/002-add-token-bucket.sql

BEGIN TRANSACTION;

-- Add user-relative bucket
ALTER TABLE "users" ADD COLUMN "bucket_tokens" INTEGER NOT NULL DEFAULT 0;
ALTER TABLE "users" ADD COLUMN "bucket_last_update" INTEGER NOT NULL DEFAULT 0;

-- Remove "DEFAULT 0" from user-relative bucket
CREATE TABLE "users_temp" (
	"id"	INTEGER NOT NULL UNIQUE,
	"username"	TEXT NOT NULL UNIQUE,
	"password"	TEXT NOT NULL,
	"registration_date"	TEXT NOT NULL,
	"bucket_tokens"	INTEGER NOT NULL,
	"bucket_last_update"	INTEGER NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);
INSERT INTO "users_temp" SELECT "id","username","password","registration_date","bucket_tokens","bucket_last_update" FROM "users";
DROP TABLE "users";
ALTER TABLE "users_temp" RENAME TO "users";

-- Add instance-wide bucket
CREATE TABLE IF NOT EXISTS "params" (
	"name"	TEXT NOT NULL UNIQUE,
	"value"	TEXT NOT NULL
);
INSERT INTO "params"("name", "value") VALUES("instance_bucket_tokens", "0");
INSERT INTO "params"("name", "value") VALUES("instance_bucket_last_update", "0");

COMMIT;