mirror of https://github.com/Kodomo/Dazzler
1 line
6.1 KiB
SQL
1 line
6.1 KiB
SQL
---------DB DAZZLER------------
|
|
|
|
CREATE TABLE users ( -- acct_mgr_login
|
|
id BIGINT PRIMARY KEY,
|
|
|
|
weakhash varchar, -- random password for weak auth AcctManager (not Dazzler)
|
|
group_default BIGINT,
|
|
tipo integer,
|
|
FOREIGN KEY (id) REFERENCES usuarios(id) ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE projects (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uid BIGINT,
|
|
name VARCHAR not null,
|
|
url VARCHAR not null,
|
|
url_signature VARCHAR not null,
|
|
estado INTEGER,
|
|
|
|
UNIQUE (url),
|
|
FOREIGN KEY (uid) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE authenticators (
|
|
uid BIGINT not null,
|
|
pid BIGINT not null,
|
|
|
|
weak varchar,
|
|
authenticator varchar,
|
|
opaque varchar,
|
|
|
|
FOREIGN KEY (pid) REFERENCES projects(id) ON DELETE RESTRICT ON UPDATE CASCADE ,
|
|
FOREIGN KEY (uid) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE ,
|
|
PRIMARY KEY (uid,pid)
|
|
);
|
|
|
|
CREATE VIEW myprojects (authenticator, weak, uid, id, name, url, url_signature, estado) AS
|
|
|
|
SELECT authenticator, weak, authenticators.uid, id, name, url, url_signature, estado FROM (authenticators LEFT JOIN projects ON (authenticators.pid = projects.id));
|
|
|
|
|
|
CREATE TABLE groups (
|
|
id BIGSERIAL PRIMARY KEY ,
|
|
uid BIGINT,
|
|
name VARCHAR not null ,
|
|
info VARCHAR not null ,
|
|
|
|
-- Confuiguracion de preferencias del grupo
|
|
mod_time INT DEFAULT '0' , -- 0 allways run
|
|
start_hour float not null DEFAULT '0.00' ,
|
|
end_hour float not null DEFAULT '0.00' ,
|
|
cpu_usage_limit INT not null DEFAULT '100' ,
|
|
run_if_user_active smallint not null default 1,
|
|
idle_time_to_run INT not null DEFAULT 1 ,--trabaja despues de un minuto de inactiv
|
|
|
|
|
|
FOREIGN KEY (uid) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE hosts (
|
|
id BIGSERIAL PRIMARY KEY ,
|
|
gid BIGINT not null ,
|
|
name varchar,
|
|
|
|
-- informacion de identificacion adicional.
|
|
cpid char(32) not null ,
|
|
xcpid char(32) not null ,
|
|
|
|
-- informacion con fines estadisticos
|
|
cpus integer,
|
|
mips integer,
|
|
flops integer,
|
|
ipaddr integer,
|
|
|
|
-- informacion de contactos
|
|
hits BIGINT DEFAULT 1,
|
|
first_contact int not null ,
|
|
last_contact int not null ,
|
|
last_request varchar ,
|
|
|
|
UNIQUE (cpid),
|
|
FOREIGN KEY (gid) REFERENCES groups(id) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
|
|
CREATE TABLE configprojects (
|
|
pid BIGINT not null ,
|
|
gid BIGINT not null ,
|
|
|
|
resource_share INTEGER DEFAULT 100 ,
|
|
suspend INTEGER DEFAULT 0 ,
|
|
detach_when_done INTEGER not null DEFAULT 0,
|
|
|
|
mod_time INTEGER DEFAULT 0 ,
|
|
|
|
FOREIGN KEY (pid) REFERENCES projects(id) ON DELETE RESTRICT ON UPDATE CASCADE ,
|
|
FOREIGN KEY (gid) REFERENCES groups(id) ON DELETE CASCADE ON UPDATE CASCADE ,
|
|
PRIMARY KEY (pid,gid)
|
|
);
|
|
|
|
|
|
CREATE TABLE statistics (
|
|
id BIGSERIAL PRIMARY KEY ,
|
|
pid BIGINT not null ,
|
|
gid BIGINT not null ,
|
|
dia INTEGER not null,
|
|
--datos estaditicos
|
|
hosts INTEGER,
|
|
cpus INTEGER,
|
|
mips INTEGER,
|
|
flops INTEGER,
|
|
|
|
--datos estaditicos
|
|
total_credit BIGINT,
|
|
expavg_credit BIGINT,
|
|
expavg_time BIGINT,
|
|
|
|
-- indices
|
|
UNIQUE (dia,pid,gid)
|
|
);
|
|
|
|
-- sequencias
|
|
ALTER SEQUENCE contenido_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 RESTART 1101 CACHE 1 NO CYCLE;
|
|
ALTER SEQUENCE usuarios_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 RESTART 1002 CACHE 1 NO CYCLE;
|
|
ALTER SEQUENCE grupos_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 RESTART 1004 CACHE 1 NO CYCLE;
|
|
ALTER SEQUENCE groups_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 RESTART 3 CACHE 1 NO CYCLE;
|
|
|
|
-- Grupos / Categorias
|
|
insert into grupos(id,nombre) values(1001,'SCV Manager');
|
|
insert into grupos(id,nombre) values(1002,'SCV Provider');
|
|
insert into grupos(id,nombre) values(1003,'SCV Researcher');
|
|
|
|
-- Sitio
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1000,1,2,2,'SCV','application_home','Dazzler.apphome.js');
|
|
insert into gcontenido(gid,cid) values(1001,1000);
|
|
insert into gcontenido(gid,cid) values(1002,1000);
|
|
insert into gcontenido(gid,cid) values(1003,1000);
|
|
|
|
insert into contenido(id,pid,pos,tipo,nombre,hijos,icon,contenido) values(1001,1,3,0,'Manager',1,'folder','');
|
|
insert into gcontenido(gid,cid) values(1001,1001);
|
|
insert into contenido(id,pid,pos,tipo,nombre,hijos,icon,contenido) values(1002,1,4,0,'Proveedor',1,'folder','');
|
|
insert into gcontenido(gid,cid) values(1002,1002);
|
|
insert into contenido(id,pid,pos,tipo,nombre,hijos,icon,contenido) values(1003,1,5,0,'Investigador',1,'folder','');
|
|
insert into gcontenido(gid,cid) values(1003,1003);
|
|
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1010,1001,0,2,'Usuarios','user_earth','Dazzler.manager.users.js');
|
|
insert into gcontenido(gid,cid) values(1001,1010);
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1011,1001,1,2,'Proyectos','world','Dazzler.manager.projects.js');
|
|
insert into gcontenido(gid,cid) values(1001,1011);
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1012,1001,2,2,'Hosts','computer_wrench','Dazzler.manager.hosts.js');
|
|
insert into gcontenido(gid,cid) values(1001,1012);
|
|
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1020,1002,0,2,'Resumen','table','Dazzler.proveedor.resumen.js');
|
|
insert into gcontenido(gid,cid) values(1002,1020);
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1021,1002,1,2,'Grupos','server','Dazzler.proveedor.group.js');
|
|
insert into gcontenido(gid,cid) values(1002,1021);
|
|
insert into contenido(id,pid,pos,tipo,nombre,icon,contenido) values(1023,1002,2,2,'Instalacion','bricks','Dazzler.proveedor.install.js');
|
|
insert into gcontenido(gid,cid) values(1002,1023);
|
|
|
|
-- Usuario Administrador
|
|
insert into usuarios( id,login,clave,activo,nombre,correo) values (1001,'bamadmin','dazzler',2,'Admin Account','root@localhost');
|
|
insert into users ( id,weakhash,group_default,tipo ) values(1001,'11625ce7ca73b92f95c0d1adb75b8382',2,7);
|
|
insert into groups ( id,uid,name,info ) values (1,1001,'lost+found','Grupo de maquinas perdidas');
|
|
insert into groups ( id,uid,name,info ) values (2,1001,'Default','Grupo por defecto del Usuario');
|
|
|
|
insert into gusuarios(gid,uid) values(1001,1001);
|
|
insert into gusuarios(gid,uid) values(1002,1001);
|
|
insert into gusuarios(gid,uid) values(1003,1001);
|
|
|
|
|
|
|
|
|
|
|