I was on the hunt for a way to remove a client from ADempiere, couldn't find much appart from a posting by fer_luck. His script didn't want to work for me (??) so I modified it to the below. This way I can setup a testing client and remove when finished.
Thought I would share for who ever may need it :)
/**
* PostgresSQL Client remove function
*
* Use to remove a client from ADempiere, e.g. remove GardenWorld
*
* ///////////////////////////////////////////////////////////////
* WARNING: This function works fine with my current setup. Yours
* may be different so backup your database before using!
* I am not responsible for any problems this may cause.
* ///////////////////////////////////////////////////////////////
*
* - Usage:
* 1. Install drop_client function (Run this script)
* 2. Run the following sql:
* SELECT <db_name>.drop_client(<db_name>, <client_id>);
* e.g. SELECT adempiere.drop_client('adempiere', 11); --> Removes GardenWorld
*
* -Credits:
* Credits due to Fernando Lucktemberg(fer_luck) for the main workings
* Credit to the guys @ e-Nition.com for improving it :)
*/
CREATE OR REPLACE FUNCTION adempiere.drop_client(db_name text, client_id integer)
RETURNS integer AS
$BODY$
DECLARE
db_name text := $1;
c_id integer := $2;
tmp RECORD;
BEGIN
RAISE NOTICE 'Setting search_path=%', db_name;
EXECUTE 'SET search_path='
|| db_name;
RAISE NOTICE 'Deleting %.Client Where AD_Client_ID=%', db_name, c_id;
RAISE NOTICE 'Disable triggers & constraints';
update pg_trigger set tgenabled = '0' where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
RAISE NOTICE 'Removing records belonging to client %', c_id;
FOR tmp IN
select * from ad_table t where isview = 'N'
and ad_table_id in (select ad_table_id from ad_column where columnname = 'AD_Client_ID')
order by tablename
LOOP
raise notice 'Removing items from table - %', tmp.tablename;
EXECUTE 'DELETE FROM ' || tmp.tablename || ' WHERE AD_Client_ID = ' || c_id;
END LOOP;
RAISE NOTICE 'Enable triggers & constraints';
update pg_trigger set tgenabled = '0' where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
RAISE NOTICE 'Done';
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION adempiere.drop_client(text, integer) OWNER TO postgres;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The revision seems to have a different example snippet. The database name is missing.. On my setup the function didn't like running without the db name.. may just be me ;)
* e.g. SELECT adempiere.drop_client('adempiere', 11); --> Removes GardenWorld
revision 5051 is
* e.g. SELECT drop_client('adempiere', 11); --> Removes GardenWorld
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Adempiere can be installed with a different db name - so it's better not to have dbname on the scripts.
I think all the scripts (migration scripts and this one) must be run with adempiere (or whatever schema name you defined in RUN_setup) and against the adempiere database (or whatever database you defined in RUN_setup).
Thinking on that, maybe this line is wrong:
ALTER FUNCTION drop_client(text, integer) OWNER TO postgres;
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi, i may not of explained correctly, the bit that I was refering to was just the example of how to run the script.
so although i put 'e.g. SELECT adempiere.drop_client(......)' this would mean you replace the db with your own db name so maybe updating it to say 'e.g. SELECT myadempieredb.drop_client(....)'
Hopefully others will know this by following the 'SELECT <db_name>.drop_client(<db_name>,<client_id>)' instructions :)
As for the last part i have set the owner to the postgres user just to be 100% sure that the function has access to the postgres triggers and contants..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hey enition. Thanks for trying the script. Well, it worked for me and for some other people.. there're some things that needs to be changed in the original script, like the relnamespace that might change from db to db. But as a first shot in the problem, I guess the solution came out well.. Ok then, let me know if you need something else.
Regards,
Fernando
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I use the Client Removal Tool to remove GardenWorld,
but the problem is when i use the migration scripts in postgres (ex. 340a-350) because that scripts has explicit
references to gardenworld, the migration process don't work at all, use with precaution!
Javier
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The script drop_client does not work for me… What's wrong? I run the script but it stops with an error: update or delete on table "ad_role" violates foreign key constraint "adrole_adsession" on table "ad_session" - Key (ad_role_id)=(102) is still referenced from table "ad_session". I tried to remove a testing client….
I was on the hunt for a way to remove a client from ADempiere, couldn't find much appart from a posting by fer_luck. His script didn't want to work for me (??) so I modified it to the below. This way I can setup a testing client and remove when finished.
Thought I would share for who ever may need it :)
/**
* PostgresSQL Client remove function
*
* Use to remove a client from ADempiere, e.g. remove GardenWorld
*
* ///////////////////////////////////////////////////////////////
* WARNING: This function works fine with my current setup. Yours
* may be different so backup your database before using!
* I am not responsible for any problems this may cause.
* ///////////////////////////////////////////////////////////////
*
* - Usage:
* 1. Install drop_client function (Run this script)
* 2. Run the following sql:
* SELECT <db_name>.drop_client(<db_name>, <client_id>);
* e.g. SELECT adempiere.drop_client('adempiere', 11); --> Removes GardenWorld
*
* -Credits:
* Credits due to Fernando Lucktemberg(fer_luck) for the main workings
* Credit to the guys @ e-Nition.com for improving it :)
*/
CREATE OR REPLACE FUNCTION adempiere.drop_client(db_name text, client_id integer)
RETURNS integer AS
$BODY$
DECLARE
db_name text := $1;
c_id integer := $2;
tmp RECORD;
BEGIN
RAISE NOTICE 'Setting search_path=%', db_name;
EXECUTE 'SET search_path='
|| db_name;
RAISE NOTICE 'Deleting %.Client Where AD_Client_ID=%', db_name, c_id;
RAISE NOTICE 'Disable triggers & constraints';
update pg_trigger set tgenabled = '0' where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
RAISE NOTICE 'Removing records belonging to client %', c_id;
FOR tmp IN
select * from ad_table t where isview = 'N'
and ad_table_id in (select ad_table_id from ad_column where columnname = 'AD_Client_ID')
order by tablename
LOOP
raise notice 'Removing items from table - %', tmp.tablename;
EXECUTE 'DELETE FROM ' || tmp.tablename || ' WHERE AD_Client_ID = ' || c_id;
END LOOP;
RAISE NOTICE 'Enable triggers & constraints';
update pg_trigger set tgenabled = '0' where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
RAISE NOTICE 'Done';
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION adempiere.drop_client(text, integer) OWNER TO postgres;
Thanks fer_luck and enition.
I uploaded the script with revision 5051 here:
http://adempiere.svn.sourceforge.net/viewvc/adempiere/contributions/stuff/DeleteAdempiereClient_pg.SQL?view=log
Regards,
Carlos Ruiz
The revision seems to have a different example snippet. The database name is missing.. On my setup the function didn't like running without the db name.. may just be me ;)
* e.g. SELECT adempiere.drop_client('adempiere', 11); --> Removes GardenWorld
revision 5051 is
* e.g. SELECT drop_client('adempiere', 11); --> Removes GardenWorld
:-) Good eye Adam.
Adempiere can be installed with a different db name - so it's better not to have dbname on the scripts.
I think all the scripts (migration scripts and this one) must be run with adempiere (or whatever schema name you defined in RUN_setup) and against the adempiere database (or whatever database you defined in RUN_setup).
Thinking on that, maybe this line is wrong:
ALTER FUNCTION drop_client(text, integer) OWNER TO postgres;
Regards,
Carlos Ruiz
Hi, i may not of explained correctly, the bit that I was refering to was just the example of how to run the script.
so although i put 'e.g. SELECT adempiere.drop_client(......)' this would mean you replace the db with your own db name so maybe updating it to say 'e.g. SELECT myadempieredb.drop_client(....)'
Hopefully others will know this by following the 'SELECT <db_name>.drop_client(<db_name>,<client_id>)' instructions :)
As for the last part i have set the owner to the postgres user just to be 100% sure that the function has access to the postgres triggers and contants..
Hi Adam
What I understand (not tested) is that you must enter to postgres like:
psql -d mydbadempiere -U myuseradempiere
and execute
SELECT drop_client(......)
No need to do it with another user or from another db.
Regards,
Carlos Ruiz
Hey enition. Thanks for trying the script. Well, it worked for me and for some other people.. there're some things that needs to be changed in the original script, like the relnamespace that might change from db to db. But as a first shot in the problem, I guess the solution came out well.. Ok then, let me know if you need something else.
Regards,
Fernando
I use the Client Removal Tool to remove GardenWorld,
but the problem is when i use the migration scripts in postgres (ex. 340a-350) because that scripts has explicit
references to gardenworld, the migration process don't work at all, use with precaution!
Javier
The script drop_client does not work for me… What's wrong? I run the script but it stops with an error: update or delete on table "ad_role" violates foreign key constraint "adrole_adsession" on table "ad_session" - Key (ad_role_id)=(102) is still referenced from table "ad_session". I tried to remove a testing client….
psql -d adempiere -U adempiere;
\i /home/drop_client.sql
SELECT adempiere.drop_client('adempiere', 1000012);
all versions of the script do not work!! :(
My database is postgresql.
Thanks for help!
The way this works, using pgAdmin against 3.7.0LTS is:
In pgAdmin, connect to the database, then select PSQL Console then
\i /<local Path to file>/DeleteAdempiereClient_pg.SQL
SELECT drop_client('adempiere',"xx"); // in this case xx = 11 for Gardenworld
That should be all.. don't need the adempiere.drop_client..
Hope that helps