[Netpass-devel] NetPass/install.d create-indexes.sql,NONE,1.1 drop-indexes.sql,NONE,1.1 drop-tables.
Brought to you by:
jeffmurphy
Menu
▾
▴
[Netpass-devel] NetPass/install.d create-indexes.sql,NONE,1.1 drop-indexes.sql,NONE,1.1 drop-tables.sql,NONE,1.1 convertdb,1.2,1.3 tables.sql,1.15,1.16
|
From: jeff m. <jef...@us...> - 2005-05-02 17:06:38
|
Update of /cvsroot/netpass/NetPass/install.d In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv14910/install.d Modified Files: convertdb tables.sql Added Files: create-indexes.sql drop-indexes.sql drop-tables.sql Log Message: db changes --- NEW FILE: drop-indexes.sql --- USE netpass; DROP INDEX results_idx1 ON results; DROP INDEX results_idx2 ON results; DROP INDEX results_idx3 ON results; DROP INDEX config_idx1 ON config; DROP INDEX pages_idx1 ON pages; DROP INDEX clientHistory_idx1 ON clientHistory; DROP INDEX clientHistory_idx2 ON clientHistory; DROP INDEX nessusScans_idx1 ON nessusScans; DROP INDEX snortRules_idx1 ON snortRules; DROP INDEX appStarter_idx1 ON appStarter; DROP INDEX stats_procs_idx1 ON stats_procs; DROP INDEX stats_procs_idx2 ON stats_procs; DROP INDEX urlFilters_idx1 ON urlFilters; DROP INDEX testConfig_idx1 ON testConfig; Index: tables.sql =================================================================== RCS file: /cvsroot/netpass/NetPass/install.d/tables.sql,v retrieving revision 1.15 retrieving revision 1.16 diff -u -d -r1.15 -r1.16 --- tables.sql 1 May 2005 05:44:10 -0000 1.15 +++ tables.sql 2 May 2005 17:06:25 -0000 1.16 @@ -13,7 +13,6 @@ registeredOn DATETIME, status ENUM('QUAR', 'PQUAR', 'UNQUAR', 'PUNQUAR') NOT NULL, uqlinkup ENUM('yes', 'no') NOT NULL DEFAULT 'no', - message TEXT, username VARCHAR(16) NOT NULL, OS VARCHAR(255), switchIP VARCHAR(128), @@ -114,12 +113,12 @@ ) ENGINE=MyISAM; CREATE TABLE clientHistory ( - chid INTEGER UNSIGNED AUTO_INCREMENT NOT NULL, + rowid INTEGER UNSIGNED AUTO_INCREMENT NOT NULL, macAddress VARCHAR(32) NOT NULL, username VARCHAR(32) NOT NULL, dt DATETIME NOT NULL, notes TEXT NOT NULL, - PRIMARY KEY (chid) + PRIMARY KEY (rowid) ) ENGINE=NDBCLUSTER; CREATE INDEX clientHistory_idx1 ON clientHistory (macAddress); @@ -170,6 +169,17 @@ CREATE INDEX snortRules_idx1 ON snortRules (status); +CREATE TABLE testConfig ( + rowid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + testType VARCHAR(32) NOT NULL, + ID VARCHAR(128) NOT NULL, + status enum('enabled', 'disabled') NOT NULL default 'disabled', + network VARCHAR(128) NOT NULL default 'default', + PRIMARY KEY (rowid) +) ENGINE=NDBCLUSTER; + +CREATE INDEX testConfig_idx1 ON testConfig (testType, ID, status, network); + CREATE TABLE appStarter ( rowid INTEGER UNSIGNED AUTO_INCREMENT, requested DATETIME, @@ -192,7 +202,7 @@ ) ENGINE=NDBCLUSTER; CREATE INDEX stats_procs_idx1 ON stats_procs (dt); -CREATE INDEX stats_procs_idx1 ON stats_procs (proc); +CREATE INDEX stats_procs_idx2 ON stats_procs (proc); CREATE TABLE urlFilters ( rowid INTEGER UNSIGNED AUTO_INCREMENT, @@ -203,45 +213,45 @@ PRIMARY KEY (rowid) ); -CREATE UNIQUE INDEX on urlFilters (url, network); +CREATE UNIQUE INDEX urlFilters_idx1 ON urlFilters (url, network); -insert into urlFilters values ('itpolicies\.buffalo\.edu', NULL, 'default', 'permit'); -insert into urlFilters values ('netpass\.buffalo\.edu', NULL, 'default', 'permit'); -insert into urlFilters values ('cert\.org', NULL, 'default', 'permit'); -insert into urlFilters values ('download\.microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('lavasoftusa\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('protect\.microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('redhat\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('securityresponse\.symantec\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('service1\.symantec\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('support\.microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('swquery\.apple\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('swscan\.apple\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('symantecliveupdate\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('us\.mcafee\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('vil\.nai\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('windows\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('windowsupdate\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('windowsupdate\.microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('wings\.buffalo\.edu', NULL, 'default', 'permit'); -insert into urlFilters values ('www\.microsoft\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('www\.sans\.org', NULL, 'default', 'permit'); -insert into urlFilters values ('www\.sophos\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'itpolicies\.buffalo\.edu', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'netpass\.buffalo\.edu', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'cert\.org', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'download\.microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'lavasoftusa\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'protect\.microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'redhat\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'securityresponse\.symantec\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'service1\.symantec\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'support\.microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'swquery\.apple\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'swscan\.apple\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'symantecliveupdate\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'us\.mcafee\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'vil\.nai\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'windows\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'windowsupdate\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'windowsupdate\.microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'wings\.buffalo\.edu', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'www\.microsoft\.com', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'www\.sans\.org', NULL, 'default', 'permit'); +insert into urlFilters values (NULL,'www\.sophos\.com', NULL, 'default', 'permit'); -insert into urlFilters values ('command\.weatherbug\.com', NULL, 'default', 'block'); -insert into urlFilters values ('isapi60\.weatherbug\.com', NULL, 'default', 'block'); -insert into urlFilters values ('wisapidata\.weatherbug\.com', NULL, 'default', 'block'); -insert into urlFilters values ('config\.180solutions\.com', NULL, 'default', 'block'); -insert into urlFilters values ('ping\.180solutions\.com', NULL, 'default', 'block'); -insert into urlFilters values ('desktop3\.weather\.com', NULL, 'default', 'block'); -insert into urlFilters values ('image\.weather\.com', NULL, 'default', 'block'); -insert into urlFilters values ('www\.statblaster\.com/updatestats', NULL, 'default', 'block'); -insert into urlFilters values ('www\.mydailyhoroscope\.net/mdh/AdResponse\.aspx', NULL, 'default','block'); -insert into urlFilters values ('204\.177\.92\.204/w/getclientid', NULL, 'default', 'block'); -insert into urlFilters values ('client\.warez\.com/data/gcache\.php', NULL, 'default', 'block'); -insert into urlFilters values ('http://sports\.espn\.go\.com/espn/espnmotion/ESPNMotionXMLv4', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'command\.weatherbug\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'isapi60\.weatherbug\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'wisapidata\.weatherbug\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'config\.180solutions\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'ping\.180solutions\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'desktop3\.weather\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'image\.weather\.com', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'www\.statblaster\.com/updatestats', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'www\.mydailyhoroscope\.net/mdh/AdResponse\.aspx', NULL, 'default','block'); +insert into urlFilters values (NULL,'204\.177\.92\.204/w/getclientid', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'client\.warez\.com/data/gcache\.php', NULL, 'default', 'block'); +insert into urlFilters values (NULL,'http://sports\.espn\.go\.com/espn/espnmotion/ESPNMotionXMLv4', NULL, 'default', 'block'); -insert into urlFilters values ('DEFAULT', 'http://npvip-d.cit.buffalo.edu/?url=%u', 'default', 'hard-redirect'); +insert into urlFilters values (NULL,'DEFAULT', 'http://npvip-d.cit.buffalo.edu/?url=%u', 'default', 'hard-redirect'); --- NEW FILE: create-indexes.sql --- USE netpass; CREATE INDEX results_idx1 ON results (macAddress); CREATE INDEX results_idx2 ON results (macAddress, testType); CREATE INDEX results_idx3 ON results (macAddress, status); CREATE INDEX config_idx1 ON config (dt); CREATE UNIQUE INDEX pages_idx1 ON pages (name, network); CREATE INDEX clientHistory_idx1 ON clientHistory (macAddress); CREATE INDEX clientHistory_idx2 ON clientHistory (dt); CREATE INDEX nessusScans_idx1 ON nessusScans (status); CREATE INDEX snortRules_idx1 ON snortRules (status); CREATE INDEX appStarter_idx1 ON appStarter (status); CREATE INDEX stats_procs_idx1 ON stats_procs (dt); CREATE INDEX stats_procs_idx2 ON stats_procs (proc); CREATE UNIQUE INDEX urlFilters_idx1 ON urlFilters (url, network); Index: convertdb =================================================================== RCS file: /cvsroot/netpass/NetPass/install.d/convertdb,v retrieving revision 1.2 retrieving revision 1.3 diff -u -d -r1.2 -r1.3 --- convertdb 15 Oct 2004 19:02:57 -0000 1.2 +++ convertdb 2 May 2005 17:06:25 -0000 1.3 @@ -4,145 +4,76 @@ echo `date` $* } +B="install.d" -# 0.9x to 0.96 - -for i in portMoves register audit ; do - myecho "Backing up $i table to /tmp/$i.sql .." - mysqldump -ce -u root netpass $i > /tmp/${i}.sql -done - -########################################################## - -myecho "Upgrading portMoves table.." - - -mysql -u root netpass <<EOF - -ALTER TABLE portMoves ADD requestedBy VARCHAR(128) DEFAULT ''; -CREATE INDEX portMoves_idx1 ON portMoves (requested); -CREATE INDEX portMoves_idx2 ON portMoves (switchIP, switchPort); +[ ! -d $B ] && { + echo "run this script from the root of the netpass source tree" + echo "for example: cd /tmp/NetPass && install.d/convertdb" + exit 255 +} -EOF +MD=`which mysqldump 2>/dev/null ` +M=`which mysql 2>/dev/null` +[ x"$MD" = "x" ] && { + echo "can't find mysqldump command. update your PATH." + exit 255; +} -########################################################## +[ x"$M" = "x" ] && { + echo "can't find mysql command. update your PATH." + exit 255; +} -myecho "Upgrading register table.." +echo "using mysqldump = $MD" +echo " mysql = $M" -mysql -s -f -u root netpass > /dev/null 2>&1 <<EOF -DROP TABLE register2; +# 2.00beta to 2.00beta (mysql cluster patch) -EOF +# we do the dump twice. cluster will sometimes report +#Error 1296: Got error 241 'Invalid schema object version' from ndbcluster when dumping table `users` at row: 0 +# on the first attempt (when you run this on another server) +for i in users config ; do + myecho "Backing up $i table to /tmp/$i.sql .." + $MD -ce -u root netpass $i 2> /dev/null > /tmp/${i}.sql + $MD -ce -u root netpass $i 2> /dev/null > /tmp/${i}.sql +done ########################################################## +myecho "Dropping indexes ..." - -mysql -u root netpass <<EOF - -CREATE TABLE register2 ( - macAddress TINYTEXT NOT NULL, - ipAddress VARCHAR(64) NOT NULL, - firstSeen DATETIME NOT NULL, - registeredOn DATETIME, - status ENUM('QUAR', 'PQUAR', 'UNQUAR', 'PUNQUAR') NOT NULL, - uqlinkup ENUM('yes', 'no') NOT NULL DEFAULT 'no', - message TEXT, - username VARCHAR(16) NOT NULL, - OS VARCHAR(255), - switchIP VARCHAR(128), - switchPort SMALLINT, - - PRIMARY KEY(macAddress(12)) -) TYPE=MyISAM; - - -INSERT INTO register2 (macAddress, - ipAddress, firstSeen, registeredOn, status, - uqlinkup, message, username, OS, switchIP, switchPort) - SELECT RIGHT(CONCAT('000000000000', LOWER(HEX(register.macAddress))), 12), - register.ipAddress, register.firstSeen, register.registeredOn, - register.status, - register.uqlinkup, register.message, register.username, - register.OS, register.switchIP, register.switchPort - FROM register; - -DROP TABLE register; - -ALTER TABLE register2 RENAME register; - -EOF +$M -fu root netpass > /dev/null 2>&1 < $B/drop-indexes.sql ########################################################## +myecho "Dropping tables ..." -myecho "Upgrading audit table.." - -mysql -s -f -u root netpass > /dev/null 2>&1 <<EOF - -DROP TABLE audit2; - -EOF - - -# because the audit table is fairly large, we apply the indices -# after the copy. this should speed the copy up quite a bit. - - -mysql -u root netpass <<EOF - -CREATE TABLE audit2 ( - ts DATETIME NOT NULL, - server VARCHAR(128), - username VARCHAR(32), - ipAddress VARCHAR(64), - macAddress TINYTEXT, - severity ENUM('DEBUG', 'ALERT', 'CRITICAL', 'ERROR', - 'WARNING', 'NOTICE', 'INFO') - NOT NULL, - location VARCHAR(255), - message text NOT NULL - -) TYPE=MyISAM; - - -INSERT INTO audit2 (macAddress, - ts, server, username, ipAddress, severity, location, - message) - SELECT RIGHT(CONCAT('000000000000', LOWER(HEX(audit.macAddress))), 12), - audit.ts, audit.server, audit.username, - audit.ipAddress, audit.severity, audit.location, - audit.message - FROM audit; - -DROP TABLE audit; +$M -s -f -u root netpass > /dev/null 2>&1 < $B/drop-tables.sql -ALTER TABLE audit2 RENAME audit; -EOF +########################################################## -myecho "Indexing audit table.." +myecho "Creating tables ..." +$M -fu root netpass < $B/tables.sql -mysql -u root netpass <<EOF -CREATE INDEX audit_idx1 ON audit (username(8)); -CREATE INDEX audit_idx2 ON audit (ipAddress); -CREATE INDEX audit_idx3 ON audit (macAddress(12)); -CREATE INDEX audit_idx4 ON audit (ts); -CREATE FULLTEXT INDEX audit_idx5 ON audit (message); +########################################################## -EOF +myecho "Re-importing config data ..." +for i in users config ; do + myecho " from /tmp/$i.sql to table $i ..." + $M -fu root netpass < /tmp/${i}.sql +done -myecho "Done indexing audit table.." -myecho "Upgrade complete." +myecho "Upgrade complete. Run this script on all netpass servers." exit 0 --- NEW FILE: drop-tables.sql --- use netpass; DROP TABLE register; DROP TABLE results; DROP TABLE policy; DROP TABLE users; DROP TABLE config; DROP TABLE passwd; DROP TABLE pages; DROP TABLE portMoves; DROP TABLE audit; DROP TABLE clientHistory; DROP TABLE nessusScans; DROP TABLE snortRules; DROP TABLE appStarter; DROP TABLE stats_procs; DROP TABLE urlFilters; DROP TABLE testConfig; |
