[gevice/gevice-2-0] add file db_gevice.sql, remove file db_gevice

[Alejandro Valdes Jimenez <avaldes src gnome org>]

commit 86aa7231661764139df791809041a255ed73448a
Author: Alejandro Valdes Jimenez <avaldes gnome org>
Date:   Thu Nov 3 11:30:52 2011 -0300

    add file db_gevice.sql, remove file db_gevice

 db_gevice     |  Bin 25600 -> 0 bytes
 db_gevice.sql |   57 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 57 insertions(+), 0 deletions(-)
---
diff --git a/db_gevice.sql b/db_gevice.sql
new file mode 100644
index 0000000..8aa1d41
--- /dev/null
+++ b/db_gevice.sql
@@ -0,0 +1,57 @@
+PRAGMA foreign_keys=OFF;
+BEGIN TRANSACTION;
+CREATE TABLE campus(id_campus NUMERIC PRIMARY KEY NOT NULL, nom_campus TEXT NOT NULL);
+INSERT INTO "campus" VALUES(0,'None');
+CREATE TABLE marca (id_marca NUMERIC PRIMARY KEY NOT NULL, nom_marca TEXT NOT NULL);
+INSERT INTO "marca" VALUES(0,'None');
+INSERT INTO "marca" VALUES(1,'Cisco');
+INSERT INTO "marca" VALUES(2,'Sun');
+CREATE TABLE tipo_disp (id_tipo_disp NUMERIC PRIMARY KEY NOT NULL, nom_tipo_disp TEXT NOT NULL);
+INSERT INTO "tipo_disp" VALUES(0,'None');
+INSERT INTO "tipo_disp" VALUES(1,'Switch');
+INSERT INTO "tipo_disp" VALUES(2,'Router');
+INSERT INTO "tipo_disp" VALUES(3,'Router/Switch');
+INSERT INTO "tipo_disp" VALUES(4,'Server');
+CREATE TABLE tipo_enlace (id_tipo_enlace NUMERIC PRIMARY KEY NOT NULL, nom_tipo_enlace TEXT NOT NULL);
+INSERT INTO "tipo_enlace" VALUES(0,'None');
+INSERT INTO "tipo_enlace" VALUES(1,'1000mbps UTP');
+CREATE TABLE nivel (id_nivel NUMERIC PRIMARY KEY NOT NULL, nom_nivel TEXT NOT NULL);
+INSERT INTO "nivel" VALUES(0,'None');
+CREATE TABLE modelo (id_modelo NUMERIC PRIMARY KEY NOT NULL, nom_modelo TEXT NOT NULL, id_tipo_disp NUMERIC, id_marca NUMERIC, FOREIGN KEY (id_tipo_disp) REFERENCES tipo_disp (id_tipo_disp), FOREIGN KEY (id_marca) REFERENCES marca (id_marca));
+INSERT INTO "modelo" VALUES(0,'None',0,0);
+INSERT INTO "modelo" VALUES(1,'WS-C2950T24',1,1);
+INSERT INTO "modelo" VALUES(2,'WS-C3550-24',3,1);
+INSERT INTO "modelo" VALUES(3,'X4140',4,2);
+CREATE TABLE edificio (id_edif NUMERIC PRIMARY KEY NOT NULL, nom_edif TEXT NOT NULL, id_campus NUMERIC, FOREIGN KEY (id_campus) REFERENCES campus (id_campus));
+INSERT INTO "edificio" VALUES(0,'None',0);
+CREATE TABLE rack (id_rack NUMERIC PRIMARY KEY NOT NULL, detalle TEXT, id_nivel NUMERIC, id_edif NUMERIC, FOREIGN KEY (id_nivel) REFERENCES nivel (id_nivel), FOREIGN KEY (id_edif) REFERENCES edificio (id_edif));
+INSERT INTO "rack" VALUES(0,'None',0,0);
+CREATE TABLE dispositivo (serial TEXT PRIMARY KEY NOT NULL, nom_disp TEXT NOT NULL,obs_disp TEXT, ip_disp TEXT, ptas_disp TEXT, id_modelo NUMERIC, id_rack NUMERIC, FOREIGN KEY (id_modelo) REFERENCES modelo (id_modelo), FOREIGN KEY (id_rack) REFERENCES rack (id_rack));
+INSERT INTO "dispositivo" VALUES('22222222','rsw-main','','127.0.0.1','',2,0);
+INSERT INTO "dispositivo" VALUES('11111111','sw-main','','127.0.0.1','',1,0);
+INSERT INTO "dispositivo" VALUES('33333333','srv-web1','','127.0.0.1','',3,0);
+INSERT INTO "dispositivo" VALUES('44444444','srv-dns','','127.0.0.1','',3,0);
+INSERT INTO "dispositivo" VALUES('55555555','srv-dhcp','','127.0.0.1','',3,0);
+INSERT INTO "dispositivo" VALUES('66666666','rsw-campus1','','127.0.0.1','',2,0);
+INSERT INTO "dispositivo" VALUES('77777777','sw-campus1-1','','127.0.0.1','',1,0);
+INSERT INTO "dispositivo" VALUES('88888888','srv-dhcp','','127.0.0.1','',3,0);
+CREATE TABLE conecta (serialp TEXT NOT NULL, serialh TEXT NOT NULL, id_tipo_enlace NUMERIC NOT NULL, FOREIGN KEY (serialp) REFERENCES dispositivo (serial), FOREIGN KEY (serialh) REFERENCES dispositivo (serial), FOREIGN KEY (id_tipo_enlace) REFERENCES tipo_enlace (id_tipo_enlace), PRIMARY KEY (serialp,serialh,id_tipo_enlace));
+INSERT INTO "conecta" VALUES('22222222','11111111',1);
+INSERT INTO "conecta" VALUES('22222222','66666666',1);
+INSERT INTO "conecta" VALUES('11111111','33333333',1);
+INSERT INTO "conecta" VALUES('11111111','44444444',1);
+INSERT INTO "conecta" VALUES('11111111','55555555',1);
+INSERT INTO "conecta" VALUES('66666666','77777777',1);
+INSERT INTO "conecta" VALUES('77777777','88888888',1);
+CREATE VIEW todoslosdispositivos as
+SELECT tipo_disp.id_tipo_disp, tipo_disp.nom_tipo_disp, marca.id_marca, marca.nom_marca, modelo.id_modelo, modelo.nom_modelo, dispositivo.serial, dispositivo.nom_disp, dispositivo.ip_disp, dispositivo.obs_disp, '[' || campus.nom_campus || '][' || edificio.nom_edif || '][' || nivel.nom_nivel || '][' || rack.detalle || ']' AS ubicacion, rack.id_rack, edificio.id_edif, nivel.id_nivel, campus.id_campus, dispositivo.ptas_disp
+   FROM dispositivo
+   JOIN modelo ON dispositivo.id_modelo = modelo.id_modelo
+   JOIN marca ON modelo.id_marca = marca.id_marca
+   JOIN tipo_disp ON modelo.id_tipo_disp = tipo_disp.id_tipo_disp
+   JOIN rack ON dispositivo.id_rack = rack.id_rack
+   JOIN nivel ON rack.id_nivel = nivel.id_nivel
+   JOIN edificio ON rack.id_edif = edificio.id_edif
+   JOIN campus ON edificio.id_campus = campus.id_campus
+  ORDER BY dispositivo.serial;
+COMMIT;