Page Contents

• Important notes
• Schema rev3
• Schema rev2
• Schema rev1

Important notes

We need to think what sort of permisions hordes need to have appliable to them. Current lists can be open or closed, locked or unlocked, read only or read-write, anonymous or public. How could we expand on this and allow, say, moderated hordes?

Schema rev3

Primary changes:

• Removed the persistent column, because non-persistant hordes will never reach the database

CREATE TABLE User(
  id       INTEGER NOT NULL PRIMARY KEY,
  name     TEXT    NOT NULL UNIQUE,
  password TEXT                             -- first character signifies version, for backwards
);                                          -- compatability. '0' = md5sum of password, '1' =
                                            -- md5sum of username concatenated with password,
                                            -- '2' == sha1sum of username concatenated with
                                            -- password.


CREATE TABLE UserField(
  id    INTEGER NOT NULL PRIMARY KEY,
  user  INTEGER NOT NULL,
  key   TEXT    NOT NULL,
  value TEXT    NOT NULL                    -- May as well be NN, delete the row if unwanted
);

CREATE TABLE Horde(
  id         INTEGER NOT NULL PRIMARY KEY,
  name       TEXT    NOT NULL
);

CREATE TABLE HordeField(
  id    INTEGER NOT NULL PRIMARY KEY,
  horde INTEGER NOT NULL,
  key   TEXT    NOT NULL,
  value TEXT    NOT NULL                    -- Absence of row == NULL, keep the constraints tight.
);

CREATE TABLE HordeMembership(
  id    INTEGER NOT NULL PRIMARY KEY,
  user  INTEGER NOT NULL,
  horde INTEGER NOT NULL
);

Schema rev2

Primary changes:

• Table names made singular
• underscores removed in favour of Camel''Case
• Layout tidied for clarity
• Horde gained persistent column because it's a critical bit of data which shouldn't be stored in a random text field

CREATE TABLE User(
  id       INTEGER NOT NULL PRIMARY KEY,
  name     TEXT    NOT NULL UNIQUE,
  password TEXT                             -- sha1sum of name concatenated with password
);

CREATE TABLE UserField(
  id    INTEGER NOT NULL PRIMARY KEY,
  user  INTEGER NOT NULL,
  key   TEXT    NOT NULL,
  value TEXT    NOT NULL,                   -- May as well be NN, delete the row if unwanted
);

CREATE TABLE Horde(
  id         INTEGER NOT NULL PRIMARY KEY,
  name       TEXT    NOT NULL,
  persistent BOOL    NOT NULL,
);

CREATE TABLE HordeField(
  id    INTEGER NOT NULL PRIMARY KEY,
  horde INTEGER NOT NULL,
  key   TEXT    NOT NULL,
  value TEXT    NOT NULL,                   -- Absence of row == NULL, keep the constraints tight.
);

CREATE TABLE HordeMembership(
  id    INTEGER NOT NULL PRIMARY KEY,
  user  INTEGER NOT NULL,
  horde INTEGER NOT NULL
);

Schema rev1

CREATE TABLE users(
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  password TEXT                             -- sha1sum of name concatenated with password
);

CREATE TABLE user_fields(
  id INTEGER NOT NULL PRIMARY KEY,
  user INTEGER NOT NULL,
  key TEXT NOT NULL,
  value TEXT
);

CREATE TABLE hordes(
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE horde_fields(
  id INTEGER NOT NULL PRIMARY KEY,
  horde INTEGER NOT NULL,
  key TEXT NOT NULL,
  value TEXT
);

CREATE TABLE horde_members(
  id INTEGER NOT NULL PRIMARY KEY,
  user INTEGER NOT NULL,
  horde INTEGER NOT NULL
);