schema.sql 2.5 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970
  1. BEGIN TRANSACTION;
  2. CREATE TABLE IF NOT EXISTS "params" (
  3. "name" TEXT NOT NULL UNIQUE,
  4. "value" TEXT NOT NULL,
  5. PRIMARY KEY("name")
  6. );
  7. INSERT INTO "params"("name", "value") VALUES('instance_bucket_tokens', '0');
  8. INSERT INTO "params"("name", "value") VALUES('instance_bucket_last_update', '0');
  9. INSERT INTO "params"("name", "value") VALUES('secret_key', '0');
  10. INSERT INTO "params"("name", "value") VALUES('secret_key_last_change', '0');
  11. INSERT INTO "params"("name", "value") VALUES('username_salt', '00000000000000000000000000000000'); -- Should be unique and secret ; generate one using `openssl rand -hex 16` ; can't be changed without breaking current accounts login
  12. CREATE TABLE IF NOT EXISTS "users" (
  13. "id" TEXT NOT NULL UNIQUE CHECK (LENGTH(id) = 64),
  14. "username" TEXT NOT NULL UNIQUE,
  15. "password" TEXT NOT NULL,
  16. "registration_date" TEXT NOT NULL,
  17. "bucket_tokens" INTEGER NOT NULL,
  18. "bucket_last_update" INTEGER NOT NULL,
  19. "type" TEXT NOT NULL,
  20. "services" TEXT NOT NULL,
  21. PRIMARY KEY("id")
  22. );
  23. CREATE TABLE IF NOT EXISTS "approval-keys" (
  24. "key" TEXT NOT NULL UNIQUE,
  25. PRIMARY KEY("key")
  26. );
  27. CREATE TABLE IF NOT EXISTS "registry" (
  28. "domain" TEXT NOT NULL UNIQUE,
  29. "username" TEXT NOT NULL,
  30. "creation" TEXT NOT NULL,
  31. PRIMARY KEY("domain"),
  32. FOREIGN KEY("username") REFERENCES "users"("id")
  33. );
  34. CREATE TABLE IF NOT EXISTS "registry-history" (
  35. "domain" TEXT NOT NULL,
  36. "creation" TEXT NOT NULL,
  37. "expiration" TEXT NOT NULL
  38. );
  39. CREATE TABLE IF NOT EXISTS "zones" (
  40. "zone" TEXT NOT NULL UNIQUE,
  41. "username" TEXT NOT NULL,
  42. PRIMARY KEY("zone"),
  43. FOREIGN KEY("username") REFERENCES "users"("id")
  44. );
  45. CREATE TABLE IF NOT EXISTS "ns-syncs" (
  46. "username" TEXT NOT NULL,
  47. "source" TEXT NOT NULL,
  48. "destination" TEXT NOT NULL UNIQUE,
  49. FOREIGN KEY("username") REFERENCES "users"("id"),
  50. FOREIGN KEY("destination") REFERENCES "zones"("zone")
  51. );
  52. CREATE TABLE IF NOT EXISTS "sites" (
  53. "username" TEXT NOT NULL,
  54. "site_dir" TEXT NOT NULL,
  55. "address" TEXT NOT NULL,
  56. "type" TEXT NOT NULL,
  57. "creation_date" TEXT NOT NULL,
  58. UNIQUE("address", "type"),
  59. UNIQUE("username", "site_dir", "type"),
  60. PRIMARY KEY("address", "type"),
  61. FOREIGN KEY("username") REFERENCES "users"("id")
  62. );
  63. CREATE TABLE IF NOT EXISTS "ssh-keys" (
  64. "key" TEXT NOT NULL,
  65. "username" TEXT NOT NULL,
  66. "directory" TEXT NOT NULL,
  67. UNIQUE("key", "username", "directory"),
  68. FOREIGN KEY("username") REFERENCES "users"("id")
  69. );
  70. COMMIT;