diff options
Diffstat (limited to 'builddb_instructions.txt')
| -rw-r--r-- | builddb_instructions.txt | 377 |
1 files changed, 377 insertions, 0 deletions
diff --git a/builddb_instructions.txt b/builddb_instructions.txt new file mode 100644 index 0000000..7ad1c85 --- /dev/null +++ b/builddb_instructions.txt @@ -0,0 +1,377 @@ +User Info +------------------------------------------------------------------------------- +USE mysql; + +CREATE USER 'root'@'192.168.1.10' identified BY 'password'; + +CREATE USER 'root'@'nucleus.berzerkula.org' identified BY 'password'; + +SELECT host, + USER, + password +FROM mysql.USER; + +Grant Info +------------------------------------------------------------------------------- +SHOW grants FOR 'root'@'192.168.1.10'; +GRANT SELECT, INSERT, UPDATE, DELETE ON a8_srv_build.* TO 'root'@'192.168.1.10' +IDENTIFIED BY 'password'; + +GRANT SELECT, INSERT, UPDATE, DELETE ON a8_srv_build.* TO 'root'@ +'nucleus.berzerkula.org' IDENTIFIED BY 'password'; + +SHOW grants FOR 'root'@'192.168.1.10'; + +================== +New Relational DB: +================== + +Create database/tables (Include table/column comments here as required) +------------------------------------------------------------------------------- +CREATE DATABASE A8_srv_build; +USE A8_srv_build; + +CREATE TABLE pkg + ( + id INT PRIMARY KEY NOT NULL auto_increment, + seq INT, + name VARCHAR(40), + version VARCHAR(40) + ); + +CREATE TABLE pkg_cfg + ( + pkg_id INT REFERENCES pkg (id), + configure VARCHAR(2048) + ); + +CREATE TABLE pkg_bld + ( + pkg_id INT REFERENCES pkg (id), + build VARCHAR(2048) + ); + +CREATE TABLE pkg_nst + ( + pkg_id INT REFERENCES pkg (id), + install VARCHAR(2048) + ); + +CREATE TABLE pkg_set + ( + pkg_id INT REFERENCES pkg (id), + setup TEXT + ); + +CREATE TABLE pkg_not + ( + pkg_id INT REFERENCES pkg (id), + notes VARCHAR(2048) + ); + +CREATE TABLE pkg_url + ( + pkg_id INT REFERENCES pkg (id), + url VARCHAR(2048) + ); + + +====================== +Comments for DB/Tables +====================== + +View comments: +------------------------------------------------------------------------------- +SELECT table_name, + table_comment +FROM information_schema.tables +WHERE table_schema = 'A8_srv_build'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_CFG'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_BLD'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_NST'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_SET'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_NOT'; + +SELECT column_comment +FROM information_schema.columns +WHERE table_name = 'PKG_URL'; + +Alter comments (TABLES): +------------------------------------------------------------------------------- +ALTER TABLE pkg comment = +'Package ID (from jHALFS build order) with name and version'; + +ALTER TABLE pkg_bld comment = 'Build instructions'; +ALTER TABLE pkg_cfg comment = 'Configure instructions'; +ALTER TABLE pkg_not comment = 'Notes for package'; +ALTER TABLE pkg_nst comment = 'Install instructions'; +ALTER TABLE pkg_set comment = 'Setup instructions'; +ALTER TABLE pkg_url comment = 'URLs for package'; + +Alter comments (COLUMNS): +------------------------------------------------------------------------------- +ALTER TABLE `pkg` + CHANGE `id` `id` INT(11) NOT NULL auto_increment comment 'unique id'; +ALTER TABLE `pkg` + CHANGE `seq` `seq` INT(11) DEFAULT NULL comment 'sequence in build order'; + +ALTER TABLE `pkg` + CHANGE `name` `name` VARCHAR(40) DEFAULT NULL comment 'name of package'; + +ALTER TABLE `pkg` + CHANGE `version` `version` VARCHAR(40) DEFAULT NULL comment 'version number'; + +ALTER TABLE `pkg_cfg` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_cfg` + CHANGE `configure` `configure` VARCHAR(2048) DEFAULT NULL comment + 'configure commands'; + +ALTER TABLE `pkg_bld` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_bld` + CHANGE `build` `build` VARCHAR(2048) DEFAULT NULL comment 'build commands'; + +ALTER TABLE `pkg_nst` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_nst` + CHANGE `install` `install` VARCHAR(2048) DEFAULT NULL comment + 'install commands'; + +ALTER TABLE `pkg_set` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_set` + CHANGE `setup` `setup` TEXT DEFAULT NULL comment 'setup commands'; + +ALTER TABLE `pkg_not` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_not` + CHANGE `notes` `notes` VARCHAR(2048) DEFAULT NULL comment 'important notes'; + +ALTER TABLE `pkg_url` + CHANGE `pkg_id` `pkg_id` INT(11) DEFAULT NULL comment + 'unique id referenced in PKG'; + +ALTER TABLE `pkg_url` + CHANGE `url` `url` VARCHAR(2048) DEFAULT NULL comment 'urls for download'; + +==================================================== +Load PKG table with data from csv (from spreadsheet) +==================================================== +Load data in file: +------------------------------------------------------------------------------- +LOAD DATA INFILE '/home/kb0iic/new_server_build/PKG.csv' INTO TABLE PKG FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (seq,name,version); + +Populate id's into other tables' pkg_id's: +------------------------------------------------------------------------------- +INSERT INTO pkg_cfg + (pkg_id, + configure) +SELECT id, + "" +FROM pkg; + +INSERT INTO pkg_bld + (pkg_id, + build) +SELECT id, + "" +FROM pkg; + +INSERT INTO pkg_nst + (pkg_id, + install) +SELECT id, + "" +FROM pkg; + +INSERT INTO pkg_set + (pkg_id, + setup) +SELECT id, + "" +FROM pkg; + +INSERT INTO pkg_not + (pkg_id, + notes) +SELECT id, + "" +FROM pkg; + +INSERT INTO pkg_url + (pkg_id, + url) +SELECT id, + "" +FROM pkg; + +============== +Insert records +============== +Use transaction (with last insert ID): +------------------------------------------------------------------------------- +SET autocommit = 0; +START TRANSACTION; +INSERT INTO pkg + (id, + seq, + name, + version) +VALUES (NULL, + '999', + 'TEST', + '1.2.3'); + +INSERT INTO pkg_cfg + (pkg_id, + configure) +VALUES (Last_insert_id(), + './configure --prefix=/usr'); + +INSERT INTO pkg_bld + (pkg_id, + build) +VALUES (Last_insert_id(), + 'make'); + +INSERT INTO pkg_nst + (pkg_id, + install) +VALUES (Last_insert_id(), + 'make -j1 install'); + +INSERT INTO pkg_set + (pkg_id, + setup) +VALUES (Last_insert_id(), + 'cat blah > /tmp/test'); + +INSERT INTO pkg_not + (pkg_id, + notes) +VALUES (Last_insert_id(), + 'May want to strip files'); + +INSERT INTO pkg_url + (pkg_id, + url) +VALUES (Last_insert_id(), + 'http://url.to.pkg/pkg.tar.gz'); + +COMMIT; (or ROLLBACK; if mistake) + +=================== +Update package info +=================== +Update all tables: +------------------------------------------------------------------------------- +UPDATE pkg, + pkg_cfg, + pkg_bld, + pkg_nst, + pkg_set, + pkg_not, + pkg_url +SET pkg.seq = '999', + pkg.name = 'TEST', + pkg.version = '1.2.3', + pkg_cfg.configure = './configure --prefix=/usr', + pkg_bld.build = 'make', + pkg_nst.install = 'make -j1 install', + pkg_set.setup = 'cat blah > /tmp/test', + pkg_not.notes = 'May want to strip files', + pkg_url.url = 'http://url.to.pkg/pkg.tar.gz' +WHERE pkg_cfg.pkg_id = pkg.id + AND pkg_bld.pkg_id = pkg.id + AND pkg_nst.pkg_id = pkg.id + AND pkg_set.pkg_id = pkg.id + AND pkg_not.pkg_id = pkg.id + AND pkg_url.pkg_id = pkg.id + AND pkg.name = 'TEST'; + +============= +Delete Record +============= +Delete: +------------------------------------------------------------------------------- +DELETE pkg, + pkg_cfg, + pkg_bld, + pkg_nst, + pkg_set, + pkg_not, + pkg_url +FROM pkg, + pkg_cfg, + pkg_bld, + pkg_nst, + pkg_set, + pkg_not, + pkg_url +WHERE pkg_cfg.pkg_id = pkg.id + AND pkg_bld.pkg_id = pkg.id + AND pkg_nst.pkg_id = pkg.id + AND pkg_set.pkg_id = pkg.id + AND pkg_not.pkg_id = pkg.id + AND pkg_url.pkg_id = pkg.id + AND pkg.name = 'TEST'; + +============= +Select Record +============= +Select: +------------------------------------------------------------------------------- +SELECT id, + seq, + name, + version, + configure, + build, + install, + setup, + notes, + url +FROM pkg, + pkg_cfg, + pkg_bld, + pkg_nst, + pkg_set, + pkg_not, + pkg_url +WHERE pkg_cfg.pkg_id = pkg.id + AND pkg_bld.pkg_id = pkg.id + AND pkg_nst.pkg_id = pkg.id + AND pkg_set.pkg_id = pkg.id + AND pkg_not.pkg_id = pkg.id + AND pkg_url.pkg_id = pkg.id +ORDER BY seq ASC |