
-- this is the IP Address table
drop table michael.ip;

-- IP4 addresses can be up to 15 characters long
-- [255.255.255.255]
-- IP6 could be as long as 42 characters long?
-- [FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF]
-- We will be using IPv4 addresses for now

-- Store address and name
create table michael.ip(
	ip VARCHAR(44) UNIQUE NOT NULL,
	name VARCHAR(44) UNIQUE NOT NULL,
	date DATETIME NOT NULL);

-- might want a longer name
ALTER TABLE ip MODIFY COLUMN name VARCHAR(42) NOT NULL;

-- we remove entries like this
DELETE FROM michael.ip
WHERE ip='127.0.0.1' OR name='Passive';

-- then we create entries like this
-- except using REPLACE instead of INSERT, to kill duplicated entries.
REPLACE INTO michael.ip VALUES
	('127.0.0.1', 'Jennifer', NOW()),
	('127.0.0.2', 'Jayne', NOW());


-- if we do this then we should have one record left
-- I.e. Jennifer disguises herself as Jayne

REPLACE INTO michael.ip VALUES
        ('127.0.0.1', 'Jayne', NOW());

-- old method to update record.
-- UPDATE michael.ip
-- SET
--	name='Jayne',
--	ip='127.0.0.1',
--	date=NOW()
-- WHERE ipaddress='127.0.0.1' OR hostname='Jayne';

-- we generate the IP list based on this...

SELECT ip, name FROM michael.ip;

-- historic ENUM('yes','no') NOT NULL DEFAULT 'no',


