perfsonar development work

Author: roman
Date: 2006-08-11 06:19:44 -0400 (Fri, 11 Aug 2006)
New Revision: 1540

Modified:
   trunk/perfsonar/conf/mysql-sqlma-dbremove.sql
   trunk/perfsonar/conf/mysql-sqlma-dbsetup.sql
   trunk/perfsonar/conf/psql-sqlma-dbremove.sql
   trunk/perfsonar/conf/psql-sqlma-dbsetup.sql
Log:
changes of db schema for L2 path status

Modified: trunk/perfsonar/conf/mysql-sqlma-dbremove.sql
===================================================================
--- trunk/perfsonar/conf/mysql-sqlma-dbremove.sql       2006-08-11 10:14:52 
UTC (rev 1539)
+++ trunk/perfsonar/conf/mysql-sqlma-dbremove.sql       2006-08-11 10:19:44 
UTC (rev 1540)
@@ -22,9 +22,13 @@
 
 
 DROP TABLE
-    perfsonar_pathstatus;
+    domain_link;
 
 
+DROP TABLE
+       interdomain_link;
+
+
 DROP DATABASE
     perfsonar_ma;
 

Modified: trunk/perfsonar/conf/mysql-sqlma-dbsetup.sql
===================================================================
--- trunk/perfsonar/conf/mysql-sqlma-dbsetup.sql        2006-08-11 10:14:52 
UTC (rev 1539)
+++ trunk/perfsonar/conf/mysql-sqlma-dbsetup.sql        2006-08-11 10:19:44 
UTC (rev 1540)
@@ -41,15 +41,25 @@
 
 
 CREATE TABLE
-    perfsonar_pathstatus
-        (pathId VARCHAR(255) NOT NULL,
-         time INT NOT NULL,
-         status VARCHAR(255) NOT NULL,
-         alarm_id_set VARCHAR(255) NULL,
-         PRIMARY KEY (pathId, time));
+    domain_link
+        (id VARCHAR(255) NOT NULL,
+         time TIMESTAMP DEFAULT NOW(),
+         status_oper VARCHAR(255) DEFAULT 'UNKNOWN',
+         status_admin VARCHAR(255) DEFAULT 'UNKNOWN',
+         alarm_id_set VARCHAR(255),
+         comments VARCHAR(255));
 
 
+CREATE TABLE
+    interdomain_link
+        (id VARCHAR(255) NOT NULL,
+         time TIMESTAMP DEFAULT NOW(),
+         status_oper VARCHAR(255) DEFAULT 'UNKNOWN',
+         status_admin VARCHAR(255) DEFAULT 'UNKNOWN',
+         alarm_id_set VARCHAR(255),
+         comments VARCHAR(255));
 
+
 # insert statements for testing perfsonar_utilization table
 
 INSERT
@@ -76,15 +86,15 @@
 # insert statements for testing perfsonar_pathstatus table
 
 INSERT
-    INTO perfsonar_pathstatus
-    VALUES ('DFN-link-1234', 1144318523, 'down', 'link is broken');
+    INTO domain_link (id, status_oper, time)
+    VALUES ('DFN-link-1234', 'down', '2006-04-06 12:15:23');
 
 INSERT
-    INTO perfsonar_pathstatus
-    VALUES ('DFN-Surfnet-Link-5678', 1144318524, 'up', 'link is broken');
+    INTO domain_link (id, status_oper, time)
+    VALUES ('DFN-Surfnet-Link-5678', 'up', '2006-04-06 12:15:24');
 
 INSERT
-    INTO perfsonar_pathstatus
-    VALUES ('PSNC-DFN-MUE-Link-1001', 1144318525, 'up', 'link is broken');
+    INTO domain_link (id, status_oper, time)
+    VALUES ('PSNC-DFN-MUE-Link-1001', 'down', '2006-04-06 12:15:25');
 
 

Modified: trunk/perfsonar/conf/psql-sqlma-dbremove.sql
===================================================================
--- trunk/perfsonar/conf/psql-sqlma-dbremove.sql        2006-08-11 10:14:52 
UTC (rev 1539)
+++ trunk/perfsonar/conf/psql-sqlma-dbremove.sql        2006-08-11 10:19:44 
UTC (rev 1540)
@@ -19,9 +19,11 @@
 
 drop table perfsonar_utilization;
 
-drop table perfsonar_pathstatus;
+drop table domain_link;
 
+drop table interdomain_link;
 
+
 -- root (postgres) user
 
 \c perfsonar_ma postgres

Modified: trunk/perfsonar/conf/psql-sqlma-dbsetup.sql
===================================================================
--- trunk/perfsonar/conf/psql-sqlma-dbsetup.sql 2006-08-11 10:14:52 UTC (rev 
1539)
+++ trunk/perfsonar/conf/psql-sqlma-dbsetup.sql 2006-08-11 10:19:44 UTC (rev 
1540)
@@ -55,14 +55,24 @@
         );
 
 create table
-    perfsonar_pathstatus
-        (pathId varchar(255) not null,
-         time integer not null,
-         status varchar(255) not null,
-         alarm_id_set varchar(255) null,
-         primary key (pathId, time)
+    domain_link
+        (id varchar(255) not null,
+         time timestamp default now(),
+         status_oper varchar(255) default 'unknown',
+         status_admin varchar(255) default 'unknown',
+         alarm_id_set varchar(255),
+         comments varchar(255)
         );
 
+create table
+    interdomain_link
+        (id varchar(255) not null,
+         time timestamp default now(),
+         status_oper varchar(255) default 'unknown',
+         status_admin varchar(255) default 'unknown',
+         alarm_id_set varchar(255),
+         comments varchar(255)
+        );
 
 -- insert statements for testing perfsonar_utilization table
 
@@ -83,19 +93,19 @@
     values ('meta4-test', 5, 'bps', 1143034709);
 
 
--- insert statements for testing perfsonar_pathstatus table
+-- insert statements for testing domain_link table
 
 insert
-    into perfsonar_pathstatus
-    values ('DFN-link-1234', 1144318523, 'down', 'link is broken');
+    into domain_link (id, status_oper, time)
+    values ('DFN-link-1234', 'down', '2006-04-06 12:15:23');
 
 insert
-    into perfsonar_pathstatus
-    values ('DFN-Surfnet-Link-5678', 1144318524, 'up', 'link is broken');
+    into domain_link (id, status_oper, time)
+    values ('DFN-Surfnet-Link-5678', 'up', '2006-04-06 12:15:24');
 
 insert
-    into perfsonar_pathstatus
-    values ('PSNC-DFN-MUE-Link-1001', 1144318525, 'up', 'link is broken');
+    into domain_link (id, status_oper, time)
+    values ('PSNC-DFN-MUE-Link-1001', 'down', '2006-04-06 12:15:25');
 
 
 -- trigger