dbScript.txt

CREATE TABLE alarm(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    password TEXT,
    value INT(11) DEFAULT '0' NOT NULL
);


CREATE TABLE events(
    email VARCHAR(101) NOT NULL,
    title VARCHAR(100) NOT NULL,
    description VARCHAR(300) NOT NULL,
    start VARCHAR(40) NOT NULL,
    end VARCHAR(40) NOT NULL,
    id INT(255) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    participants TEXT
);


CREATE INDEX email ON events (email);


CREATE TABLE groups(
    groupname VARCHAR(100) PRIMARY KEY NOT NULL,
    groupstate INT(11) DEFAULT '0' NOT NULL
);


CREATE UNIQUE INDEX groups_groupname_uindex ON groups (groupname);

CREATE TABLE sensors(
    id INT(255) PRIMARY KEY NOT NULL,
    value INT(2) NOT NULL
);


CREATE TABLE shoppingList(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    product TEXT NOT NULL
);


CREATE TABLE units(
    id INT(255) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    description VARCHAR(100) NOT NULL,
    state INT(11) DEFAULT '0' NOT NULL,
    controller INT(20) DEFAULT '23328130' NOT NULL,
    groupid VARCHAR(100),
    luxstate INT(11) DEFAULT '0' NOT NULL,
    luxvalue INT(11),
    CONSTRAINT units_fk FOREIGN KEY (groupid) REFERENCES groups (groupname)
);


CREATE TABLE users(
    email VARCHAR(101) PRIMARY KEY NOT NULL,
    password TEXT NOT NULL,
    firstname TEXT NOT NULL,
    surname TEXT NOT NULL,
    address VARCHAR(101) NOT NULL,
    postalCode VARCHAR(9999) NOT NULL,
    city VARCHAR(101) NOT NULL,
    country VARCHAR(101) NOT NULL
);


CREATE INDEX email ON users (email);


#Password is "password" with hash value

INSERT INTO alarm (password, value) VALUES ("$2a$10$DFsRPxRDjXiHMTscPYSy7u3GrE21HK5UkxSZv9.Dc271PIHL6gH.a", 1)