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