Emmanuel BENOîT
e50775ec76
* The main loader script has been updated to generate the list of files it needs to load automatically. As a consequence, files that contained manually-maintained lists of scripts have been removed, and definition directories have been renamed accordingly. * PostgreSQL extension loading and configuration has been moved to a separate script to be loaded automatically in the main transaction. * Data and function definition scripts that had the -data or -functions suffix have been renamed (the suffix is unnecessary). * Unit tests have been reorganised to follow the definition's structure. * Documentation has been improved
111 lines
No EOL
2.7 KiB
SQL
111 lines
No EOL
2.7 KiB
SQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- User management tables
|
|
--
|
|
-- Copyright(C) 2004-2010, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Identifiers sequence for users
|
|
--
|
|
CREATE SEQUENCE users.identifiers_seq;
|
|
|
|
|
|
--
|
|
-- E-mail addresses
|
|
--
|
|
CREATE TABLE users.addresses(
|
|
id INT DEFAULT nextval('users.identifiers_seq') NOT NULL PRIMARY KEY ,
|
|
address VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_addresses_address
|
|
ON users.addresses (lower(address));
|
|
|
|
|
|
--
|
|
-- User credentials
|
|
--
|
|
CREATE TABLE users.credentials(
|
|
address_id INT NOT NULL PRIMARY KEY,
|
|
pass_md5 CHAR(32) NOT NULL ,
|
|
pass_sha1 CHAR(40) NOT NULL ,
|
|
credits INT NOT NULL CHECK (credits >= 0) ,
|
|
language_id INT NOT NULL
|
|
);
|
|
|
|
ALTER TABLE users.credentials
|
|
ADD CONSTRAINT fk_credentials_id
|
|
FOREIGN KEY (address_id) REFERENCES users.addresses
|
|
ON DELETE CASCADE ,
|
|
ADD CONSTRAINT fk_credentials_language
|
|
FOREIGN KEY (language_id) REFERENCES defs.languages;
|
|
|
|
|
|
--
|
|
-- Validation keys
|
|
--
|
|
CREATE TABLE users.validation_keys(
|
|
credentials_id INT NOT NULL PRIMARY KEY ,
|
|
token CHAR( 64 ) NOT NULL ,
|
|
created TIMESTAMP WITHOUT TIME ZONE
|
|
NOT NULL
|
|
DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_validationkeys_created
|
|
ON users.validation_keys (created);
|
|
|
|
ALTER TABLE users.validation_keys
|
|
ADD CONSTRAINT fk_validationkeys_id
|
|
FOREIGN KEY (credentials_id) REFERENCES users.credentials
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
|
|
--
|
|
-- Password recovery
|
|
--
|
|
CREATE TABLE users.pwd_recovery_requests(
|
|
credentials_id INT NOT NULL PRIMARY KEY ,
|
|
token CHAR( 64 ) NOT NULL ,
|
|
used BOOLEAN NOT NULL
|
|
DEFAULT FALSE ,
|
|
created TIMESTAMP WITHOUT TIME ZONE
|
|
NOT NULL
|
|
DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_pwdrecovery_created
|
|
ON users.pwd_recovery_requests (created);
|
|
|
|
ALTER TABLE users.pwd_recovery_requests
|
|
ADD CONSTRAINT fk_pwdrecovery_id
|
|
FOREIGN KEY (credentials_id) REFERENCES users.credentials
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
|
|
--
|
|
-- Mail address change requests
|
|
--
|
|
CREATE TABLE users.address_change_requests(
|
|
credentials_id INT NOT NULL PRIMARY KEY ,
|
|
address_id INT NOT NULL ,
|
|
token CHAR( 64 ) NOT NULL ,
|
|
used BOOLEAN NOT NULL
|
|
DEFAULT FALSE ,
|
|
created TIMESTAMP WITHOUT TIME ZONE
|
|
NOT NULL
|
|
DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_addrchangereq_address
|
|
ON users.address_change_requests (address_id);
|
|
CREATE INDEX idx_addrchangereq_created
|
|
ON users.address_change_requests (created);
|
|
|
|
ALTER TABLE users.address_change_requests
|
|
ADD CONSTRAINT fk_addrchangereq_id
|
|
FOREIGN KEY (credentials_id) REFERENCES users.credentials ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT fk_addrchangereq_address
|
|
FOREIGN KEY (address_id) REFERENCES users.addresses ON DELETE CASCADE; |