backends

var PREPARE_MYSQL = []string{
	fmt.Sprintf(`CREATE TABLE IF NOT EXISTS Users (
		uid INTEGER PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(255) NOT NULL,
		password VARCHAR(255) NOT NULL,
		state INTEGER DEFAULT %d,
		CONSTRAINT SingleKeys UNIQUE (name)
	) ENGINE=InnoDB;`, STATUS_ACTIVE),
	`CREATE TABLE IF NOT EXISTS Groups (
		gid INTEGER PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(255) NOT NULL,
		CONSTRAINT SingleKeys UNIQUE (name)
	) ENGINE=InnoDB;`,
	`CREATE TABLE IF NOT EXISTS UserGroups (
		uid INTEGER NOT NULL,
		gid INTEGER NOT NULL,
		CONSTRAINT SingleKeys UNIQUE (uid, gid),
		CONSTRAINT FOREIGN KEY (uid) REFERENCES Users(uid) ON DELETE CASCADE,
		CONSTRAINT FOREIGN KEY (gid) REFERENCES Groups(gid) ON DELETE CASCADE
	) ENGINE=InnoDB;`,
	fmt.Sprintf(`CREATE TABLE IF NOT EXISTS UserValues (
		uid INTEGER,
		%s VARCHAR(255) NOT NULL,
		value LONGTEXT NOT NULL,
		CONSTRAINT SingleKeys UNIQUE (uid, %s),
		CONSTRAINT FOREIGN KEY (uid) REFERENCES Users(uid) ON DELETE CASCADE
	) ENGINE=InnoDB;`, "`key`", "`key`"),
}

var PREPARE_POSTGRES = []string{
	fmt.Sprintf(`CREATE TABLE IF NOT EXISTS Users (
		uid INTEGER PRIMARY KEY DEFAULT nextval('UsersSeq'),
		name TEXT NOT NULL,
		password TEXT NOT NULL,
		state INTEGER DEFAULT %d,
		CONSTRAINT UniqueUserNames UNIQUE (name)
	);`, STATUS_ACTIVE),
	`CREATE TABLE IF NOT EXISTS Groups (
		gid INTEGER PRIMARY KEY DEFAULT nextval('GroupsSeq'),
		name TEXT NOT NULL,
		CONSTRAINT UniqueGroupNames UNIQUE (name)
	);`,
	`CREATE TABLE IF NOT EXISTS UserGroups (
		uid INTEGER NOT NULL REFERENCES Users(uid) ON DELETE CASCADE,
		gid INTEGER NOT NULL REFERENCES Groups(gid) ON DELETE CASCADE,
		CONSTRAINT UniqueUidGidPairs UNIQUE (uid, gid)
	);`,
	`CREATE TABLE IF NOT EXISTS UserValues (
		uid INTEGER REFERENCES Users(uid) ON DELETE CASCADE,
		key TEXT NOT NULL,
		value TEXT NOT NULL,
		CONSTRAINT UniqueUidKeyPairs UNIQUE (uid, key)
	);`,
}

var PREPARE_SQLITE = []string{
	`PRAGMA encoding = "UTF-8";`,
	"PRAGMA foreign_keys = ON;",
	"PRAGMA journal_mode;",
	"PRAGMA integrity_check;",
	"PRAGMA busy_timeout = 60000;",
	"PRAGMA auto_vacuum = INCREMENTAL;",
	fmt.Sprintf(`CREATE TABLE IF NOT EXISTS Users (
		uid INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		password TEXT NOT NULL,
		state INTEGER DEFAULT %d,
		CONSTRAINT UniqueUserNames UNIQUE (name) ON CONFLICT ROLLBACK
	);`, STATUS_ACTIVE),
	`CREATE TABLE IF NOT EXISTS Groups (
		gid INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		CONSTRAINT UniqueGroupNames UNIQUE (name) ON CONFLICT ROLLBACK
	);`,
	`CREATE TABLE IF NOT EXISTS UserGroups (
		uid INTEGER NOT NULL REFERENCES Users(uid) ON DELETE CASCADE,
		gid INTEGER NOT NULL REFERENCES Groups(gid) ON DELETE CASCADE,
		CONSTRAINT UniqueUidGidPairs UNIQUE (uid, gid) ON CONFLICT IGNORE
	);`,
	`CREATE TABLE IF NOT EXISTS UserValues (
		uid INTEGER REFERENCES Users(uid) ON DELETE CASCADE,
		key TEXT NOT NULL,
		value BLOB NOT NULL,
		CONSTRAINT UniqueUidKeyPairs UNIQUE (uid, key) ON CONFLICT REPLACE
	);`,
}