35 lines
990 B
SQL
35 lines
990 B
SQL
-- schema.sql
|
|
|
|
-- Table of user
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
username VARCHAR(255) PRIMARY KEY UNIQUE NOT NULL
|
|
);
|
|
|
|
-- Table of VMs
|
|
CREATE TABLE IF NOT EXISTS vms (
|
|
hostname VARCHAR(255) PRIMARY KEY UNIQUE NOT NULL,
|
|
vmid INT UNIQUE NOT NULL,
|
|
pveuser VARCHAR(255) NOT NULL,
|
|
pvepass VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
-- Table of student access to vms
|
|
CREATE TABLE IF NOT EXISTS user_access (
|
|
hostname VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) NOT NULL,
|
|
FOREIGN KEY (hostname) REFERENCES vms (hostname),
|
|
FOREIGN KEY (username) REFERENCES users (username),
|
|
PRIMARY KEY (hostname, username)
|
|
);
|
|
|
|
-- Table of VM information to present for users
|
|
CREATE TABLE IF NOT EXISTS vm_info (
|
|
hostname VARCHAR(255) PRIMARY KEY NOT NULL,
|
|
dns VARCHAR(255) NOT NULL,
|
|
ip VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
services VARCHAR(255) NOT NULL,
|
|
FOREIGN KEY (hostname) REFERENCES vms (hostname)
|
|
);
|