Merge branch 'master' of https://github.com/adefaria/clearscm

[clearscm.git] / clearadm / lib / clearadm.sql

diff --git a/clearadm/lib/clearadm.sql b/clearadm/lib/clearadm.sql
index e8236c3..7142ad9 100644 (file)
--- a/clearadm/lib/clearadm.sql
+++ b/clearadm/lib/clearadm.sql
@@ -36,11 +36,11 @@ create table system (
                      'Unix',
                      'Windows'
                    ) not null,
                      'Unix',
                      'Windows'
                    ) not null,

-  region          tinytext,
+  region                tinytext,

   port             int default 25327,
   lastheardfrom    datetime,
   notification     varchar (255),
   port             int default 25327,
   lastheardfrom    datetime,
   notification     varchar (255),

-  description     text,
+  description       text,

   loadavgHist      enum (
                      '1 month',
                      '2 months',
   loadavgHist      enum (
                      '1 month',
                      '2 months',


@@ -56,9 +56,11 @@ create table system (
                      '1 year'
                    ) not null default '6 months',
   loadavgThreshold float (4,2) default 5.00,
                      '1 year'
                    ) not null default '6 months',
   loadavgThreshold float (4,2) default 5.00,

+  loadavgsmall     blob,
+  loadavg          blob,

 
   primary key (name)
 
   primary key (name)

-) type=innodb; -- system
+) engine=innodb; -- system

 
 -- clearcase: Information about a Clearcase system
 create table clearcase (
 
 -- clearcase: Information about a Clearcase system
 create table clearcase (


@@ -90,7 +92,7 @@ create table clearcase (
     on delete cascade
     on update cascade,
   primary key (system)
     on delete cascade
     on update cascade,
   primary key (system)

-) type=innodb; -- clearcase
+) engine=innodb; -- clearcase

 
 -- package: A package is any software package that we wish to keep track of
 create table package (
 
 -- package: A package is any software package that we wish to keep track of
 create table package (


@@ -106,7 +108,7 @@ create table package (
     on delete cascade
     on update cascade,
   primary key (system, name)
     on delete cascade
     on update cascade,
   primary key (system, name)

-) type=innodb; -- package
+) engine=innodb; -- package

   
 -- filesystem: A systems file systems that we are monitoring 
 create table filesystem (
   
 -- filesystem: A systems file systems that we are monitoring 
 create table filesystem (


@@ -130,13 +132,15 @@ create table filesystem (
                    '11 months',
                    '1 year'
                  ) not null default '6 months',
                    '11 months',
                    '1 year'
                  ) not null default '6 months',

+  fssmall        blob,
+  fslarge        blob,

   
   key filesystemIndex (filesystem),
   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade,
   primary key (system, filesystem)
   
   key filesystemIndex (filesystem),
   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade,
   primary key (system, filesystem)

-) type=innodb; -- filesystem
+) engine=innodb; -- filesystem

 
 -- fs: Contains a snapshot reading of a filesystem at a given date and time
 create table fs (
 
 -- fs: Contains a snapshot reading of a filesystem at a given date and time
 create table fs (


@@ -155,7 +159,47 @@ create table fs (
     references filesystem (system, filesystem)
       on delete cascade
       on update cascade
     references filesystem (system, filesystem)
       on delete cascade
       on update cascade

-) type=innodb; -- fs
+) engine=innodb; -- fs
+
+-- vobstorage: Contains a snapshot of a vob's storage pools at a given date
+-- and time
+create table vobstorage (
+  tag            varchar(255) not null,
+  region          varchar(255) not null,
+  timestamp      datetime     not null,
+  admin                  decimal(10,1),
+  db             decimal(10,1),
+  cleartext      decimal(10,1),
+  derivedobj     decimal(10,1),
+  source         decimal(10,1),
+  total                  decimal(10,1),
+
+  key vobtagIndex (tag, region),
+  primary key  (tag, region, timestamp)
+  foreign key vobLink (tag, region)
+    references vob (tag, region)
+      on delete cascade
+      on update cascade
+) engine=innodb; -- vobstorage
+
+-- viewstorage: Contains a snapshot of a view's storage pools at a given date
+-- and time
+create table viewstorage (
+  tag          varchar(255) not null,
+  region        varchar(255) not null,
+  timestamp    datetime     not null,
+  private      decimal(10,1),
+  db           decimal(10,1),
+  admin                decimal(10,1),
+  total         decimal(10,1),
+
+  key viewtagIndex (tag, region),
+  primary key (tag, region, timestamp),
+  foreign key viewLink (tag, region)
+    references view (tag, region)
+      on delete cascade
+      on update cascade
+) engine=innodb; -- viewstorage

 
 -- loadavg: Contains a snapshot reading of a system's load average
 create table loadavg (
 
 -- loadavg: Contains a snapshot reading of a system's load average
 create table loadavg (


@@ -165,50 +209,62 @@ create table loadavg (
   users         int,
   loadavg       float (4,2),
 
   users         int,
   loadavg       float (4,2),
 

-  primary key   (system, timestamp).
+  primary key   (system, timestamp),

   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade
   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade

-) type=innodb; -- loadavg
+) engine=innodb; -- loadavg

 
 

--- vobs: Describe a system's vobs
+-- vob: Describe a system's vobs

 create table vob (
 create table vob (

-  system varchar (255) not null,
-  tag    varchar (255) not null,
+  tag            varchar (255) not null,
+  region         varchar (255) not null,
+  adminsmall     blob,
+  dbsmall        blob,
+  cleartextsmall  blob,
+  derivedobjsmall blob,
+  sourcesmall     blob,
+  totalsmall      blob,
+  adminlarge             blob,
+  dblarge        blob,
+  cleartextlarge  blob,
+  derivedobjlarge blob,
+  sourcelarge     blob,
+  totallarge      blob,

   
   

-  key systemIndex (system),
-  foreign key systemLink (system) references system (name)
-    on delete cascade
-    on update cascade,
-  primary key (tag)
-) type=innodb; -- vob 
+  key vobTagIndex (tag),
+  primary key (tag, region)
+) engine=innodb; -- vob 

 
 -- view: Describe views
 create table view (
 
 -- view: Describe views
 create table view (

-  system    varchar (255) not null,
-  region    varchar (255) not null,
-  tag       varchar (255) not null,
-  owner     tinytext,
-  ownerName tinytext,
-  email     tinytext,
-  type      enum (
-              'dynamic',
-              'snapshot',
-              'web'
-            ) not null default 'dynamic',
-  gpath     tinytext,
-  modified  datetime,
-  timestamp datetime,
-  age       tinytext,
-  ageSuffix tinytext,
+  tag          varchar (255) not null,
+  region       varchar (255) not null,
+  owner        tinytext,
+  ownerName    tinytext,
+  email        tinytext,
+  type         enum (
+                 'dynamic',
+                 'snapshot',
+                 'web'
+               ) not null default 'dynamic',
+  gpath        tinytext,
+  modified     datetime,
+  timestamp    datetime,
+  age          tinytext,
+  ageSuffix    tinytext,
+  privatesmall blob,
+  dbsmall      blob,
+  adminsmall   blob,
+  totalsmall   blob,
+  privatelarge blob,
+  dblarge      blob,
+  adminlarge   blob,
+  totallarge   blob,

   
   

-  key systemIndex (system),
-  foreign key systemLink (system) references system (name)
-    on delete cascade
-    on update cascade,
-  key regionIndex (region),
-  primary key (region, tag)
-) type=innodb; -- view
+  key viewTagIndex (tag),
+  primary key (tag, region)
+) engine=innodb; -- view

 
 create table task (
   name          varchar (255) not null,
 
 create table task (
   name          varchar (255) not null,


@@ -221,10 +277,11 @@ create table task (
                 ) not null default 'true',
   
   primary key (name)
                 ) not null default 'true',
   
   primary key (name)

-  foreign key systemLink (system) references system (name)
-    on delete cascade
-    on update cascade,
-) type=innodb; -- task
+--  primary key (name),
+--  foreign key systemLink (system) references system (name)
+--    on delete cascade
+--    on update cascade
+) engine=innodb; -- task

 
 create table runlog (
   id            int not null auto_increment,
 
 create table runlog (
   id            int not null auto_increment,


@@ -242,32 +299,12 @@ create table runlog (
   primary key (id, task, system),
   foreign key taskLink (task) references task (name)
     on delete cascade
   primary key (id, task, system),
   foreign key taskLink (task) references task (name)
     on delete cascade

-    on update cascade
+    on update cascade,

   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade
   foreign key systemLink (system) references system (name)
     on delete cascade
     on update cascade

-) type=innodb; -- runlog
-  
-create table schedule (
-  name          varchar (255) not null,
-  task          varchar (255) not null,
-  notification  varchar (255) not null,
-  frequency     tinytext,
-  active        enum (
-                  'true',
-                  'false'
-                ) not null default 'true',
-  lastrunid     int,
+) engine=innodb; -- runlog

   
   

-  primary key (name),
-  foreign key taskLink (task) references task (name)
-    on delete cascade
-    on update cascade
-  foreign key notificationLink (notification) references notification (name)
-    on delete cascade
-    on update cascade
-) type=innodb; -- schedule
-

 create table alert (
   name varchar (255) not null,
   type enum (
 create table alert (
   name varchar (255) not null,
   type enum (


@@ -278,7 +315,7 @@ create table alert (
   who  tinytext,
   
   primary key (name)
   who  tinytext,
   
   primary key (name)

-) type=innodb; -- alert
+) engine=innodb; -- alert

 
 create table notification (
   name         varchar (255) not null,
 
 create table notification (
   name         varchar (255) not null,


@@ -294,10 +331,30 @@ create table notification (
   primary key (name),
   foreign key alertLink (alert) references alert (name)
     on delete cascade
   primary key (name),
   foreign key alertLink (alert) references alert (name)
     on delete cascade

-    on update cascade,
- ) type=innodb; -- notification
+    on update cascade
+ ) engine=innodb; -- notification

  
  

- create table alertlog (
+create table schedule (
+  name          varchar (255) not null,
+  task          varchar (255) not null,
+  notification  varchar (255) not null,
+  frequency     tinytext,
+  active        enum (
+                  'true',
+                  'false'
+                ) not null default 'true',
+  lastrunid     int,
+  
+  primary key (name),
+  foreign key taskLink (task) references task (name)
+    on delete cascade
+    on update cascade,
+  foreign key notificationLink (notification) references notification (name)
+    on delete cascade
+    on update cascade
+) engine=innodb; -- schedule
+
+create table alertlog (

   id           int not null auto_increment,
   alert        varchar (255) not null,
   system       varchar (255) not null,
   id           int not null auto_increment,
   alert        varchar (255) not null,
   system       varchar (255) not null,


@@ -314,7 +371,7 @@ create table notification (
   foreign key notificationLink (notification) references notification (name)
     on delete cascade
     on update cascade,
   foreign key notificationLink (notification) references notification (name)
     on delete cascade
     on update cascade,

-  foreigh key runlogLink (runlog) references runlog (id)
+  foreign key runlogLink (runlog) references runlog (id)

     on delete cascade
     on update cascade
     on delete cascade
     on update cascade

-) type=innodb; -- alertlog
\ No newline at end of file
+) engine=innodb; -- alertlog