[Phpgroupware-developers] Follow Up: Questions from New User/Developer

[Ashleigh Gordon]

Thanks for quick help. From what I've found, it looks like postgresql 
doesn't have a global parameter to have case insensitive searches. So 
I've made a quick hack in the query string in 
class.contacts_sql.inc.php, to make it case insensitive. Here it is:

       /* Replace $sql .= " $f LIKE '%$query%' OR "; with */
       $sql .= " $f LIKE '%$query%' OR $f LIKE lower('%$query%') OR $f 
LIKE upper('%$query%') OR $f LIKE initcap('$query%') OR ";

This does makethe query rather large however, but it will do for now

I've also attached my update and insert triggers. In order to enable 
triggers in postgresql, at the command prompt, type "createlang plpgsql 
phpgroupware". I think that I should probably document this and some 
other things to make it a bit easier for others. Who is currently 
co-ordinating/working on the documentation? I think that I could 
probably write the documentation for the addressbook now if you would like.

Ashleigh Gordon

/*When an organisations details are updated
this trigger will update them globally*/

drop function update_org_name();

CREATE FUNCTION update_org_name () RETURNS OPAQUE AS '

DECLARE

myInt int;

BEGIN
        RAISE NOTICE ''update_org_name'';

        select count(*)
        into myInt 
        FROM phpgw_addressbook 
        WHERE org_name = NEW.org_name AND
        adr_one_street = NEW.adr_one_street AND
        adr_one_locality = NEW.adr_one_locality AND
        adr_one_region = NEW.adr_one_region AND
        adr_one_postalcode = NEW.adr_one_postalcode AND 
        adr_one_countryname = NEW.adr_one_countryname AND 
        adr_one_type = NEW.adr_one_type AND
        label = NEW.label AND 
        url = NEW.url AND  
        pubkey = NEW.pubkey AND
        note = NEW.note AND
        adr_two_street = NEW.adr_one_street AND
        adr_two_locality = NEW.adr_one_locality AND
        adr_two_region = NEW.adr_one_region AND
        adr_two_postalcode = NEW.adr_one_postalcode AND 
        adr_two_countryname = NEW.adr_one_countryname AND 
        adr_two_type = NEW.adr_one_type AND 
        org_unit = NEW.org_unit AND
        tel_work = NEW.tel_work AND
        tel_fax = NEW.tel_fax AND
        tel_isdn = NEW.tel_isdn AND
        tel_msg = NEW.tel_msg AND
        cat_id = NEW.cat_id AND
        email = NEW.email;      

        RAISE NOTICE ''Count is equal to %'', myInt;

        IF myInt < 2 THEN
        update phpgw_addressbook set
        adr_one_street = NEW.adr_one_street,
        adr_one_locality = NEW.adr_one_locality,
        adr_one_region = NEW.adr_one_region,
        adr_one_postalcode = NEW.adr_one_postalcode, 
        adr_one_countryname = NEW.adr_one_countryname, 
        adr_one_type = NEW.adr_one_type, 
        label = NEW.label, 
        url = NEW.url,  
        pubkey = NEW.pubkey,
        note = NEW.note,
        adr_two_street = NEW.adr_one_street,
        adr_two_locality = NEW.adr_one_locality,
        adr_two_region = NEW.adr_one_region,
        adr_two_postalcode = NEW.adr_one_postalcode, 
        adr_two_countryname = NEW.adr_one_countryname, 
        adr_two_type = NEW.adr_one_type, 
        org_unit = NEW.org_unit,
        tel_work = NEW.tel_work,
        tel_fax = NEW.tel_fax,
        tel_isdn = NEW.tel_isdn,
        tel_msg = NEW.tel_msg,
        cat_id = NEW.cat_id,
        email = NEW.email
        where org_name = NEW.org_name;
        END IF;
        
        RAISE NOTICE ''Details updated'';

        return OLD;

END;
'        LANGUAGE 'plpgsql';

drop trigger update_org_name ON phpgw_addressbook;

CREATE TRIGGER update_org_name AFTER UPDATE ON phpgw_addressbook
    FOR EACH ROW EXECUTE PROCEDURE update_org_name();

/*When a new entry is made, this trigger will
populate the organisations details if it alreay exists*/

drop function insert_org_detail();

CREATE FUNCTION insert_org_detail () RETURNS OPAQUE AS '
DECLARE 

myrec RECORD;
myInt int;

BEGIN
        RAISE NOTICE ''insert_org_detail'';

        select count(*)
        into myInt
        from phpgw_addressbook 
        where org_name = NEW.org_name;

        RAISE NOTICE ''Count is equal to %'', myInt;

        IF myInt > 1 THEN

        RAISE NOTICE ''Inside IF statement'';

        select * 
        into myrec
        from phpgw_addressbook 
        where org_name = NEW.org_name AND
        NEW.id != id;

        RAISE NOTICE ''Myrec id is %'', myrec.id;
        RAISE NOTICE ''New id is %'', NEW.id;

        update phpgw_addressbook set
        adr_one_street = myrec.adr_one_street,
        adr_one_locality = myrec.adr_one_locality,
        adr_one_region = myrec.adr_one_region,
        adr_one_postalcode = myrec.adr_one_postalcode, 
        adr_one_countryname = myrec.adr_one_countryname, 
        adr_one_type = myrec.adr_one_type, 
        label = myrec.label, 
        url = myrec.url,  
        pubkey = myrec.pubkey,
        note = myrec.note,
        adr_two_street = myrec.adr_two_street,
        adr_two_locality = myrec.adr_two_locality,
        adr_two_region = myrec.adr_two_region,
        adr_two_postalcode = myrec.adr_two_postalcode, 
        adr_two_countryname = myrec.adr_two_countryname, 
        adr_two_type = myrec.adr_two_type, 
        org_unit = myrec.org_unit,
        tel_work = myrec.tel_work,
        tel_fax = myrec.tel_fax,
        tel_isdn = myrec.tel_isdn,
        tel_msg = myrec.tel_msg,
        cat_id = myrec.cat_id,
        email = myrec.email
        where id = NEW.id;

        RAISE NOTICE ''Details entered in new contact'';

        END IF;

        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

drop trigger insert_org_name ON phpgw_addressbook;

CREATE TRIGGER insert_org_name AFTER INSERT ON phpgw_addressbook
    FOR EACH ROW EXECUTE PROCEDURE insert_org_detail();