/* Name:	DBcreate.scania.02.sql */
/* Description:	Build new DB tables and structure */
/*		after MS-sql has been abandoned on NT */
/*		And SV5010 has become the primary database */
/*		Also extension of the original snl_object_01 table */
/*		with user and date info and a history file. */
/* */
/* Programmer:	R. Sondaar */
/* Copyright:	(c) WM-data Zwolle B.V. */
/* Version:	2000-10-03/01, initial */

/* Make sure to use the correct database */
\connect scaniadb

/* Make a backup of the original data */
/* Remove these files by hand */
CREATE TABLE temp_object_bak0 AS SELECT * FROM snl_object_01;
CREATE TABLE temp_object_bak1 AS SELECT * FROM snl_object_01;

/* Remove before creating (cleaning up) */

DROP TRIGGER trigger_snl_object_02_insert ON snl_object_02;
DROP TRIGGER trigger_snl_object_02_update ON snl_object_02;
DROP TRIGGER trigger_snl_object_02_delete ON snl_object_02;
DROP SEQUENCE snl_object_02_id_seq;
DROP FUNCTION isip(text);
DROP FUNCTION ismac(text);
DROP FUNCTION function_snl_object_02_insert();
DROP FUNCTION function_snl_object_02_update();
DROP FUNCTION function_snl_object_02_delete();
DROP INDEX snl_object_02_naam;
DROP INDEX snl_object_02_patch;
DROP INDEX snl_object_02_aansluit;
DROP TABLE snl_object_02;
DROP TABLE snl_object_02_history;

/* Create main table */
CREATE SEQUENCE snl_object_02_id_seq;
CREATE TABLE snl_object_02 (
	naam		VARCHAR(255)	NOT NULL,
	macadres	CHAR(12)	NOT NULL,
	ipadres		CHAR(15)	NOT NULL,
	alias		VARCHAR(255),
	patchkast	CHAR(4)		NOT NULL,
	patchnummer	VARCHAR(30)	NOT NULL,
	aansluitnummer	VARCHAR(30)	NOT NULL,
	gebouw		VARCHAR(50)	NOT NULL,
	kamer		VARCHAR(30)	NOT NULL,
	gebruiker	VARCHAR(50)	NOT NULL,
	gebruiker_id	VARCHAR(10)	NOT NULL,
	telefoonnr	VARCHAR(20)	NOT NULL,
	connectie	VARCHAR(30),
	connectie_omschrijving	VARCHAR(30),
	isic_mutatie_nr	VARCHAR(100)	NOT NULL,
	responsetijd	CHAR(5),
	kommentaar1	VARCHAR(255),
	kommentaar2	VARCHAR(255),
	hardwaretype	VARCHAR(50)	NOT NULL,
	serienr		INT4,
	bootfile	VARCHAR(100),
	subnetmask	CHAR(15)	NOT NULL,
	update_user	VARCHAR(255)	NOT NULL	DEFAULT CURRENT_USER,
	update_time	TIMESTAMP	NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	update_type	VARCHAR(6)	NOT NULL	DEFAULT 'insert',
	id		INT4		DEFAULT nextval('snl_object_02_id_seq')	PRIMARY KEY
	);

/* Create history table */

CREATE TABLE snl_object_02_history (
	naam		VARCHAR(255)	NOT NULL,
	macadres	CHAR(12)	NOT NULL,
	ipadres		CHAR(15)	NOT NULL,
	alias		VARCHAR(255),
	patchkast	CHAR(4)		NOT NULL,
	patchnummer	VARCHAR(30)	NOT NULL,
	aansluitnummer	VARCHAR(30)	NOT NULL,
	gebouw		VARCHAR(50)	NOT NULL,
	kamer		VARCHAR(30)	NOT NULL,
	gebruiker	VARCHAR(50)	NOT NULL,
	gebruiker_id	VARCHAR(10)	NOT NULL,
	telefoonnr	VARCHAR(20)	NOT NULL,
	connectie	VARCHAR(30),
	connectie_omschrijving	VARCHAR(30),
	isic_mutatie_nr	VARCHAR(100)	NOT NULL,
	responsetijd	CHAR(5),
	kommentaar1	VARCHAR(255),
	kommentaar2	VARCHAR(255),
	hardwaretype	VARCHAR(50)	NOT NULL,
	serienr		INT4,
	bootfile	VARCHAR(100),
	subnetmask	CHAR(15)	NOT NULL,
	update_user	VARCHAR(255)	NOT NULL	DEFAULT CURRENT_USER,
	update_time	TIMESTAMP	NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	update_type	VARCHAR(6)	NOT NULL,
	id		INT4		NOT NULL
	);

/* Create indexes on main table (snl_object_02) */
/* for maintaining unique connections */

CREATE UNIQUE INDEX snl_object_02_naam ON snl_object_02(naam);
CREATE UNIQUE INDEX snl_object_02_patch ON snl_object_02(patchkast, patchnummer);
CREATE UNIQUE INDEX snl_object_02_aansluit ON snl_object_02(patchkast, aansluitnummer);

/* *** fuctions *** */
/* Validate ip-address */
CREATE FUNCTION isip(text)
RETURNS boolean
AS 'DECLARE
        len	integer;
        i	integer;
        dot	integer;
        dot2	integer;
    BEGIN
	/* must contain 7 to 15 characters */
	len := length(trim($1));
        IF len < 7
        THEN RETURN ''f'';
        END IF;
        IF len > 15
        THEN RETURN ''f'';
        END IF;

	/* must contain digits 0 - 9 and . */
	i := 1;
	WHILE i <= len LOOP
	    IF position(substr($1, i, 1) in ''1234567890.'') < 1
	    THEN RETURN ''f'';
	    END IF;
	    i := i + 1;
	END LOOP;
	
	/* must contain 3 dots not in sequence */
	/* must not end or begin with a dot */
	i := 2;
	dot := 0;
	WHILE i < len LOOP
	    IF substr($1, i, 1) = ''.''
	    THEN
	        dot := dot + 1;
	        i := i + 2;
	    ELSE
	        i := i + 1;
	    END IF;
	END LOOP;
	IF dot <> 3
        THEN RETURN ''f'';
        END IF;

	/* values around the dots must be from 0 to 255 */
	i := 1;
	dot := 1;
	dot2 := 0;
	WHILE i <= len LOOP
	    IF substr($1, i, 1) = ''.''
	    THEN
	        dot2 := i;
	        IF int4(substr($1, dot, dot2 - dot)) > 255
	        THEN RETURN ''f'';
	        END IF;
	        dot := i + 1;
	    END IF;
	    i := i + 1;
	END LOOP;

	/* all ok */
	RETURN ''t'';
    END;'
LANGUAGE 'plpgsql';

/* Validate mac-address */
CREATE FUNCTION ismac(text)
RETURNS boolean
AS 'DECLARE
        len	integer;
        i	integer;
    BEGIN
	/* must contain 12 characters */
	len := length(trim($1));
        IF len <> 12
        THEN RETURN ''f'';
        END IF;

	/* must contain digits 0 - 9 and abcdef */
	i := 1 ;
	WHILE i <= len LOOP
	    IF position(substr(upper($1), i, 1) in ''0123456789ABCDEF'') < 1
	    THEN RETURN ''f'';
	    END IF;
	    i := i + 1;
	END LOOP;

	/* all ok */
	RETURN ''t'';
    END;'
LANGUAGE 'plpgsql';

/* Check insert */
CREATE FUNCTION function_snl_object_02_insert()
RETURNS opaque
AS 'BEGIN
	/* Syntax checking */
	/* Mac address */
	IF ismac(new.macadres) = ''f''
	THEN	RAISE EXCEPTION ''Mac address is not valid.'';
	END IF;
	/* ip-address */
	IF isip(new.ipadres) = ''f''
	THEN	 RAISE EXCEPTION ''IP-address is not valid.'';
	END IF;
	/* subnet-mask */
	IF isip(new.subnetmask) = ''f''
	THEN	 RAISE EXCEPTION ''Subnet-mask is not valid.'';
	END IF;

	/* Correct data */
	new.naam = upper(new.naam);
	new.macadres = upper(new.macadres);
	new.gebruiker_id= upper(new.gebruiker_id);

	/* Copy old data to history */
	INSERT INTO snl_object_02_history
	VALUES(
		new.naam,
		new.macadres,
		new.ipadres,
		new.alias,
		new.patchkast,
		new.patchnummer,
		new.aansluitnummer,
		new.gebouw,
		new.kamer,
		new.gebruiker,
		new.gebruiker_id,
		new.telefoonnr,
		new.connectie,
		new.connectie_omschrijving,
		new.isic_mutatie_nr,
		new.responsetijd,
		new.kommentaar1,
		new.kommentaar2,
		new.hardwaretype,
		new.serienr,
		new.bootfile,
		new.subnetmask,
		new.update_user,
		new.update_time,
		new.update_type,
		new.id
	);
	RETURN new;
    END;'
LANGUAGE 'plpgsql';

/* Check update */
CREATE FUNCTION function_snl_object_02_update()
RETURNS opaque
AS 'BEGIN
	/* Syntax checking */
	/* Mac address */
	IF ismac(new.macadres) = ''f''
	THEN	RAISE EXCEPTION ''Mac address is not valid.'';
	END IF;
	/* ip-address */
	IF isip(new.ipadres) = ''f''
	THEN	 RAISE EXCEPTION ''IP-address is not valid.'';
	END IF;
	/* subnet-mask */
	IF isip(new.subnetmask) = ''f''
	THEN	 RAISE EXCEPTION ''Subnet-mask is not valid.'';
	END IF;

	/* Correct data */
	new.naam = upper(new.naam);
	new.macadres = upper(new.macadres);
	new.gebruiker_id= upper(new.gebruiker_id);
	new.update_user = CURRENT_USER;
	new.update_time = CURRENT_TIMESTAMP;
	new.update_type = ''update'';

	/* Copy the data to history */
	INSERT INTO snl_object_02_history
	VALUES(
		new.naam,
		new.macadres,
		new.ipadres,
		new.alias,
		new.patchkast,
		new.patchnummer,
		new.aansluitnummer,
		new.gebouw,
		new.kamer,
		new.gebruiker,
		new.gebruiker_id,
		new.telefoonnr,
		new.connectie,
		new.connectie_omschrijving,
		new.isic_mutatie_nr,
		new.responsetijd,
		new.kommentaar1,
		new.kommentaar2,
		new.hardwaretype,
		new.serienr,
		new.bootfile,
		new.subnetmask,
		new.update_user,
		new.update_time,
		new.update_type,
		new.id
	);
	RETURN new;
    END;'
LANGUAGE 'plpgsql';

/* Check delete */
CREATE FUNCTION function_snl_object_02_delete()
RETURNS opaque
AS 'BEGIN
	/* Syntax checking */
	/* There is nothing to check */

	/* Correct data */
	/* There is nothing to correct */

	/* Copy old data to history */
	old.update_user = CURRENT_USER;
	old.update_time = CURRENT_TIMESTAMP;
	old.update_type = ''delete'';
	INSERT INTO snl_object_02_history
	VALUES(
		old.naam,
		old.macadres,
		old.ipadres,
		old.alias,
		old.patchkast,
		old.patchnummer,
		old.aansluitnummer,
		old.gebouw,
		old.kamer,
		old.gebruiker,
		old.gebruiker_id,
		old.telefoonnr,
		old.connectie,
		old.connectie_omschrijving,
		old.isic_mutatie_nr,
		old.responsetijd,
		old.kommentaar1,
		old.kommentaar2,
		old.hardwaretype,
		old.serienr,
		old.bootfile,
		old.subnetmask,
		old.update_user,
		old.update_time,
		old.update_type,
		old.id
	);
	RETURN;
    END;'
LANGUAGE 'plpgsql';

/* Create triggers */
CREATE TRIGGER trigger_snl_object_02_insert
BEFORE INSERT
ON snl_object_02
FOR EACH ROW
EXECUTE PROCEDURE function_snl_object_02_insert();

CREATE TRIGGER trigger_snl_object_02_update
BEFORE UPDATE
ON snl_object_02
FOR EACH ROW
EXECUTE PROCEDURE function_snl_object_02_update();

CREATE TRIGGER trigger_snl_object_02_delete
BEFORE DELETE
ON snl_object_02
FOR EACH ROW
EXECUTE PROCEDURE function_snl_object_02_delete();

/* Adjust access */
REVOKE ALL on "snl_object_02" from PUBLIC;
GRANT UPDATE,DELETE,SELECT on "snl_object_02" to "root";





/* ****************************************************************************** */
/* DO NOT execute this on instalation */
/* *** Begin Test instructions *** */
/* Test insert */
INSERT INTO snl_object_02
	(naam, macadres, ipadres, patchkast, patchnummer, aansluitnummer, gebouw,
	 kamer, gebruiker, gebruiker_id, telefoonnr, isic_mutatie_nr,
	 hardwaretype, subnetmask)
	 VALUES( 'NAAM00', '0123456789AB', '1.1.1.1', '9999', 'A1', '1.1.1', '-',
	 '000', 'gebr', 'uid', '123', 'cl0', 'hwtype', '255.255.255.0');
SELECT * FROM snl_object_02;
/* malformed macadres */
INSERT INTO snl_object_02
	(naam, macadres, ipadres, patchkast, patchnummer, aansluitnummer, gebouw,
	 kamer, gebruiker, gebruiker_id, telefoonnr, isic_mutatie_nr,
	 hardwaretype, subnetmask)
	 VALUES( 'NAAM01', '0156789AB', '1.1.1.1', '9999', 'A2', '1.1.2', '-',
	 '000', 'gebr', 'uid', '123', 'cl0', 'hwtype', '255.255.255.0');

/* Test update */
UPDATE snl_object_02 SET kommentaar1 = '??~~@@##$$%%' WHERE naam = 'NAAM00';
SELECT * FROM snl_object_02;
SELECT * FROM snl_object_02_history;

/* Test delete */
DELETE FROM snl_object_02 WHERE naam = 'NAAM00';
SELECT * FROM snl_object_02;
SELECT * FROM snl_object_02_history;
/* *** End Test instructions *** */

/* Clean up test carbage */
DELETE FROM snl_object_02;
DELETE FROM snl_object_02_history;

/* Copy existing data to new files */
INSERT INTO snl_object_02 SELECT * FROM temp_object_01;

DROP TRIGGER trigger_snl_object_02_insert ON snl_object_02;
DROP FUNCTION function_snl_object_02_insert();


CREATE TRIGGER trigger_snl_object_02_insert
BEFORE INSERT
ON snl_object_02
FOR EACH ROW
EXECUTE PROCEDURE function_snl_object_02_insert();

INSERT INTO snl_object_02
	(naam, macadres, ipadres, patchkast, patchnummer, aansluitnummer, gebouw,
	 kamer, gebruiker, gebruiker_id, telefoonnr, isic_mutatie_nr,
	 hardwaretype, subnetmask)
	 VALUES( 'naam00', '0123456789b', '1.171.1.1', '9999', 'A1', '1.1.1', '-',
	 '000', 'gebr', 'uid', '123', 'cl0', 'hwtype', '255.255.255.0');
SELECT * FROM snl_object_02;
SELECT * FROM snl_object_02_history;


DROP TRIGGER trigger_snl_object_02_delete ON snl_object_02;
DROP FUNCTION function_snl_object_02_delete();

CREATE FUNCTION function_snl_object_02_delete()
RETURNS opaque
AS 'BEGIN
	/* Syntax checking */
	/* There is nothing to check */

	/* Correct data */
	/* There is nothing to correct */

	/* Copy old data to history */
	old.update_user = CURRENT_USER;
	old.update_time = CURRENT_TIMESTAMP;
	old.update_type = ''delete'';
	INSERT INTO snl_object_02_history
	VALUES(
		old.naam,
		old.macadres,
		old.ipadres,
		old.alias,
		old.patchkast,
		old.patchnummer,
		old.aansluitnummer,
		old.gebouw,
		old.kamer,
		old.gebruiker,
		old.gebruiker_id,
		old.telefoonnr,
		old.connectie,
		old.connectie_omschrijving,
		old.isic_mutatie_nr,
		old.responsetijd,
		old.kommentaar1,
		old.kommentaar2,
		old.hardwaretype,
		old.serienr,
		old.bootfile,
		old.subnetmask,
		old.update_user,
		old.update_time,
		old.update_type
	);
	RETURN old;
    END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_snl_object_02_delete
BEFORE DELETE
ON snl_object_02
FOR EACH ROW
EXECUTE PROCEDURE function_snl_object_02_delete();

DELETE FROM snl_object_02 WHERE naam = 'NAAM00';
SELECT * FROM snl_object_02_history WHERE update_type = 'delete';

UPDATE snl_object_01
SET kommentaar2 = 'dddddd'
WHERE naam = 'WS7098';
