X-Git-Url: https://defaria.com/gitweb/?a=blobdiff_plain;f=clearadm%2Flib%2Fclearadm.sql;h=7142ad9b80442a6d81d770069c0aae924942d9ff;hb=7ddf095f187ca60d9a70fb83b2bc3c2b6d91f088;hp=e8236c33f7c5d14ff7e6a7e6cafe1a980b335e74;hpb=020a4a5ea2be725b155cae3a2cadc9aba3911b9b;p=clearscm.git diff --git a/clearadm/lib/clearadm.sql b/clearadm/lib/clearadm.sql index e8236c3..7142ad9 100644 --- a/clearadm/lib/clearadm.sql +++ b/clearadm/lib/clearadm.sql @@ -36,11 +36,11 @@ create table system ( 'Unix', 'Windows' ) not null, - region tinytext, + region tinytext, port int default 25327, lastheardfrom datetime, notification varchar (255), - description text, + description text, 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, + loadavgsmall blob, + loadavg blob, primary key (name) -) type=innodb; -- system +) engine=innodb; -- system -- 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) -) type=innodb; -- clearcase +) engine=innodb; -- clearcase -- 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) -) type=innodb; -- package +) engine=innodb; -- package -- 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', + fssmall blob, + fslarge blob, 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 ( @@ -155,7 +159,47 @@ create table fs ( 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 ( @@ -165,50 +209,62 @@ create table loadavg ( 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 -) type=innodb; -- loadavg +) engine=innodb; -- loadavg --- vobs: Describe a system's vobs +-- vob: Describe a system's vobs 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 ( - 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, @@ -221,10 +277,11 @@ create table task ( ) 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, @@ -242,32 +299,12 @@ create table runlog ( 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 -) 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 ( @@ -278,7 +315,7 @@ create table alert ( who tinytext, primary key (name) -) type=innodb; -- alert +) engine=innodb; -- alert 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 - 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, @@ -314,7 +371,7 @@ create table notification ( 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 -) type=innodb; -- alertlog \ No newline at end of file +) engine=innodb; -- alertlog