DROP TABLE extrafield;

ALTER TABLE fastdial_contact DROP COLUMN field1;
ALTER TABLE fastdial_contact DROP COLUMN field2;
ALTER TABLE fastdial_contact DROP COLUMN field3;
ALTER TABLE fastdial_contact DROP COLUMN field4;
ALTER TABLE fastdial_contact DROP COLUMN field5;

ALTER TABLE fastdial_company DROP COLUMN field1;
ALTER TABLE fastdial_company DROP COLUMN field2;
ALTER TABLE fastdial_company DROP COLUMN field3;
ALTER TABLE fastdial_company DROP COLUMN field4;
ALTER TABLE fastdial_company DROP COLUMN field5;

DROP VIEW v_my_account;
DROP VIEW people;
DROP VIEW loggedin_people;

alter table contact drop column identifier1;
alter table contact drop column identifier2;
alter table contact drop column identifier3;

BEGIN;
-- Copyright (c) 1997-now, NeoNova BV, The Netherlands. All rights Reserved.
--
-- This software is the copyrighted work of NeoNova BV.
-- Use of the software is subject to the general business terms and conditions of NeoNova BV
-- and its related transaction documents like license sheets or other written notices.#
-- Any reproduction or redistribution of the software not in accordance with the license terms
-- is expressly prohibited by law, and may result in severe civil and criminally penalties.
-- Violators will be prosecuted to the maximum extent possible.

CREATE TABLE extrafield (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL,
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this record belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar deze gegevens bij horen.

 tablename varchar(16) NOT NULL default '',
--HELP_EN_INFO:tablename
--HELP_EN_LONG:Name of the table where this field resides
--HELP_NL_INFO:naam tabel
--HELP_NL_LONG:Naam voor de tabel.

 number integer NOT NULL default '1',
--HELP_EN_INFO:number
--HELP_EN_LONG:Field number
--HELP_NL_INFO:nummer
--HELP_NL_LONG:Veld nummer

 type integer NOT NULL default '1',
--HELP_EN_INFO:name
--HELP_EN_LONG:Name for this field
--HELP_NL_INFO:naam
--HELP_NL_LONG:Naam voor dit veld

 visible integer NOT NULL default '8',
--HELP_EN_INFO:visible length
--HELP_EN_LONG:The visible size of this field on screen. In case of a text area, 
--HELP_EN_LONG:the number of lines that are visible in edit mode
--HELP_NL_INFO:lengte op scherm
--HELP_NL_LONG:Het zichtbare gedeelte van dit veld. In het geval van een tekst veld
--HELP_NL_LONG:het aantal regels wat boven elkaar wordt getoond tijdens het wijzigen

 len integer NOT NULL default '8',
--HELP_EN_INFO:length
--HELP_EN_LONG:The maxmimum size of this field. In case of a text area,
--HELP_EN_LONG:the width of the area in characters in edit mode.
--HELP_NL_INFO:lengte
--HELP_NL_LONG:Maximale lengte van dit veld. In het geval van een tekst veld,
--HELP_NL_LONG:de breedte van het tekst veld in karakters.

 name varchar(24) NOT NULL default '',
--HELP_EN_INFO:name
--HELP_EN_LONG:Name for this field
--HELP_NL_INFO:naam
--HELP_NL_LONG:Naam voor dit veld

 description text NOT NULL default '',
--HELP_EN_INFO:description
--HELP_EN_LONG:Description for this field. This will show up in the helptext
--HELP_NL_INFO:omschrijving
--HELP_NL_LONG:Een omschrijving voor dit veld. Dit wordt getoond in de help tekst

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON extrafield TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON extrafield_id_seq TO astium;

alter table contact add column field1 text;
update contact set field1 = '' where field1 IS NULL;
alter table contact alter column field1 set default '';
alter table contact alter column field1 set not null;

alter table contact add column field2 text;
update contact set field2 = '' where field2 IS NULL;
alter table contact alter column field2 set default '';
alter table contact alter column field2 set not null;

alter table contact add column field3 text;
update contact set field3 = '' where field3 IS NULL;
alter table contact alter column field3 set default '';
alter table contact alter column field3 set not null;

alter table contact add column field4 text;
update contact set field4 = '' where field4 IS NULL;
alter table contact alter column field4 set default '';
alter table contact alter column field4 set not null;

alter table contact add column field5 text;
update contact set field5 = '' where field5 IS NULL;
alter table contact alter column field5 set default '';
alter table contact alter column field5 set not null;

CREATE VIEW v_my_account AS
SELECT *
   FROM contact
  WHERE contact.username::name = "current_user"();
GRANT SELECT, UPDATE ON v_my_account TO GROUP astium_users;

CREATE VIEW people AS
  SELECT contact.firstname, contact.lastname, contact.username,
         phoneline.extension, department.name,
         contact.field1, contact.field2, contact.field3, contact.field4, contact.field5
    FROM contact, contact_department, department, phoneline
   WHERE contact.id = contact_department.contact AND department.id = contact_department.department AND contact.id = phoneline.lookupid
         AND phoneline.function = 'contact' AND contact.status = 'active' AND phoneline.status = 'active';

GRANT UPDATE, SELECT, INSERT, DELETE ON people TO astium;
GRANT SELECT ON people TO GROUP astium_users;

CREATE VIEW loggedin_people AS
 SELECT contact.firstname, contact.lastname, contact.username, phoneline.extension, department.name,
        contact.field1, contact.field2, contact.field3, contact.field4, contact.field5
   FROM contact, contact_department, department, phoneline
   WHERE contact.id = contact_department.contact AND department.id = contact_department.department AND contact.id = phoneline.lookupid
         AND phoneline.function = 'contact' AND contact.status = 'active' AND phoneline.status = 'active' AND contact.phone > 1;

GRANT UPDATE, SELECT, INSERT, DELETE ON loggedin_people TO astium;
GRANT SELECT ON loggedin_people TO GROUP astium_users;


alter table fastdial_contact add column field1 text;
update fastdial_contact set field1 = '' where field1 IS NULL;
alter table fastdial_contact alter column field1 set default '';
alter table fastdial_contact alter column field1 set not null;

alter table fastdial_contact add column field2 text;
update fastdial_contact set field2 = '' where field2 IS NULL;
alter table fastdial_contact alter column field2 set default '';
alter table fastdial_contact alter column field2 set not null;

alter table fastdial_contact add column field3 text;
update fastdial_contact set field3 = '' where field3 IS NULL;
alter table fastdial_contact alter column field3 set default '';
alter table fastdial_contact alter column field3 set not null;

alter table fastdial_contact add column field4 text;
update fastdial_contact set field4 = '' where field4 IS NULL;
alter table fastdial_contact alter column field4 set default '';
alter table fastdial_contact alter column field4 set not null;

alter table fastdial_contact add column field5 text;
update fastdial_contact set field5 = '' where field5 IS NULL;
alter table fastdial_contact alter column field5 set default '';
alter table fastdial_contact alter column field5 set not null;


alter table fastdial_company add column field1 text;
update fastdial_company set field1 = '' where field1 IS NULL;
alter table fastdial_company alter column field1 set default '';
alter table fastdial_company alter column field1 set not null;

alter table fastdial_company add column field2 text;
update fastdial_company set field2 = '' where field2 IS NULL;
alter table fastdial_company alter column field2 set default '';
alter table fastdial_company alter column field2 set not null;

alter table fastdial_company add column field3 text;
update fastdial_company set field3 = '' where field3 IS NULL;
alter table fastdial_company alter column field3 set default '';
alter table fastdial_company alter column field3 set not null;

alter table fastdial_company add column field4 text;
update fastdial_company set field4 = '' where field4 IS NULL;
alter table fastdial_company alter column field4 set default '';
alter table fastdial_company alter column field4 set not null;

alter table fastdial_company add column field5 text;
update fastdial_company set field5 = '' where field5 IS NULL;
alter table fastdial_company alter column field5 set default '';
alter table fastdial_company alter column field5 set not null;
COMMIT;
