SQL Queries
Tbayen (Diskussion | Beiträge) (Die Seite wurde neu angelegt: „For my personal small documentation index see PostgreSQL Documentation. = Interesting SQL Queries in PostgreSQL on the ADempiere Database = This page col…“) |
Version vom 26. August 2013, 11:18 Uhr
For my personal small documentation index see PostgreSQL Documentation.
Interesting SQL Queries in PostgreSQL on the ADempiere Database
This page collects code- and other pieces I have done with PostgreSQL while working with ADempiere.
PostgreSQL
ADempiere in Version 361 uses PostgreSQL Version 8.4. All queries here are tested with 8.4. TRy to avoid using 9.x because there are some incompatible changes regarding adempiere functionality. I had problems especially with binary data. It may be that the trick with "bytea_output" (see down here) solves this issue but I haven't tried yet.
Set a password for the admin user
The admin User in PostgreSQL is always the user postgres. In the Debian standard installation this User has no password so you can only connect via ident authentication if you are the Unix-User "postgresql". So begin as root and do this to set a password:
# su postgres $ psql postgres=# ALTER ROLE postgres UNENCRYPTED PASSWORD 'meinpasswort'; postgres=# \q $ exit #
I wrote another description in this area in the [LUG Krefeld Wiki].
create a new database
I want to create a new database to load a Backup into. So I have a db for my development environment.
CREATE DATABASE development WITH ENCODING='UTF8' OWNER=adempiere CONNECTION LIMIT=-1;
Find all Tables
SELECT * FROM pg_tables;
do a version independent dump
In version 9.x the output format for literals of type bytea that pg_dump uses has changed. So you can not make a dump in a 9.x database and execute it in a 8.x server. If you want to do it you have to set the user variable 'bytea_output' to 'escape' in the 9.x server.
ALTER ROLE username SET bytea_output TO 'escape';
You can reset it back to hex:
ALTER ROLE username RESET bytea_output;
large and small Letters
Make the first character of every word large and the other small:
Handwritten version that does omit words with less than three letters:
SELECT array_to_string( array_agg( CASE WHEN LENGTH(wort)>2 THEN UPPER(SUBSTRING(wort FROM 1 FOR 1))||LOWER(SUBSTRING(wort FROM 2)) ELSE wort END ),) FROM regexp_split_to_table( 'bla blaha foo FO bar bahaaar.', E'\\y' ) AS wort;
short version (see http://www.postgresql.org/docs/9.1/static/functions-string.html):
initcap('bla blaha foo FO bar bahaaar.')
compare records for equality
If you want to know wether a records has changed you can use something like this (I used it for kontinuierlicher Import:
CREATE TEMPORARY TABLE changedrecords ON COMMIT DROP AS SELECT tmp.* FROM tmp INNER JOIN importe.lieferanten USING("LieferantenNummer") WHERE ROW(tmp.*) IS DISTINCT FROM ROW(importe.lieferanten.*);
The row-Constructor seems to be postgresql-specific. If you want to solve this with other databases you may read this article.
create a foreign key constraint
ALTER TABLE adempiere.bay_tradingunit ADD FOREIGN KEY (depositpackage_id) REFERENCES adempiere.m_product (m_product_id) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
create a set (a table) of weeks
This query gives you the dates of all mondays in the next year.
SELECT generate_series( current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date), current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) + interval '1 year', interval '1 week' )
do an eval in SQL
At first glance there is nothing like an "eval" command in SQL. If you dig a bit deeper you see that there is the EXECUTE command in plsql (not to be confused with the EXECUTE command in SQL). You can use it only in functions. The EXECUTE command can not return a table (the return value may be a scalar or a record, but not a table of records). There is a way out by creating a temporary table.
CREATE OR REPLACE FUNCTION executestring(text) RETURNS VOID AS $$ DECLARE BEGIN EXECUTE 'CREATE TEMP table tmp ON COMMIT DROP AS SELECT 1 AS eins, '||quote_literal($1)||' AS zwei'; END; $$ LANGUAGE plpgsql; SELECT executestring('bla'); SELECT * FROM tmp;
If you want to return a table it is not that easy. I tried hard but I could not make it work without defining the returning record structure. You have to define columns with the OUT keyword. So you can't have a generic, dynamic method for every kind of return tables.
--DROP FUNCTION executestring(text); CREATE OR REPLACE FUNCTION executestring(text, OUT eins text, OUT zwei text) RETURNS SETOF RECORD AS $$ DECLARE r record; BEGIN EXECUTE 'CREATE TEMP table tmp ON COMMIT DROP AS SELECT 1 AS eins, '||quote_literal($1)||'::text AS zwei'; FOR r IN SELECT * FROM tmp LOOP eins=r.eins; zwei=r.zwei; RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM executestring('bla');
copy csv data into a table
You can use the \COPY directive with psql to get csv data into your database. Because very often the data has to be rearranged I prefer to read the data into a temporary table in the first step.
CREATE TEMPORARY TABLE tmp ( a varchar, b varchar, c varchar, d varchar, e varchar); \COPY tmp(a,b,c,d,e) from KontenMitOPsM002-erweitert.csv delimiter ';' csv header; select * from tmp; ...
Measure size in bytes of a database schema
use one of these to measure one or all schemas:
SELECT sum(pg_relation_size(schemaname || '.' || '"' || tablename || '"'))::bigint FROM pg_tables WHERE schemaname = blabla
SELECT schemaname, sum(pg_relation_size(schemaname || '.' || '"' || tablename || '"'))::bigint FROM pg_tables GROUP BY schemaname
ADempiere Database Structure
writing SQL inside ADempiere
If your code has a strange behaviour check the server logs and look whether your SQL goes without changes to the server. There is a facility to translate common Oracle SQL terms to PostgreSQL. If your code is already PostgreSQL-written this can be very annoying. If you call DB.prepareStatement(sql-Code, ...)
, your code will be translated via org.compiere.db.DB_PostgreSQL.convert(...)
which calls org.compier.dbPort.convert_PostgreSQL.convert(...)
. So the following command:
DELETE FROM BAY_Umsatzstatistik USING BAY_Statistikperiode WHERE BAY_Statistikperiode.BAY_Statistikperiode_ID=BAY_Umsatzstatistik.BAY_Statistikperiode_ID AND BAY_Statistikperiode.Value='bla'
in org.compier.dbPort.convert_PostgreSQL.convertAlias(...)
will be destroyed. The command work if you set an table alias (this will be deleted by the conversion but the term works):
DELETE FROM BAY_Umsatzstatistik BAY_Umsatzstatistik USING BAY_Statistikperiode WHERE BAY_Statistikperiode.BAY_Statistikperiode_ID=BAY_Umsatzstatistik.BAY_Statistikperiode_ID AND BAY_Statistikperiode.Value='bla'
I do not know why this happens. Perhaps in older versions of PostgreSQL you could not use alias with delete?!?
Find all Tables that deal with translations:
SELECT * FROM pg_tables WHERE tablename LIKE '%trl%';
create a new data table in ADempiere
Copied from http://www.adempiere.com/NewWindow:
CREATE TABLE XX_Material ( AD_CLIENT_ID NUMERIC(10) NOT NULL, AD_ORG_ID NUMERIC(10) NOT NULL, ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, CREATED DATE DEFAULT NOW() NOT NULL, CREATEDBY NUMERIC(10) NOT NULL, UPDATED DATE DEFAULT NOW() NOT NULL, UPDATEDBY NUMERIC(10) NOT NULL, XX_MATERIAL_ID NUMERIC(10,0) NOT NULL, MATNR SMALLINT NOT NULL, COLORNR SMALLINT NOT NULL, NAME VARCHAR(100) NOT NULL, NAMESHORT VARCHAR(100) NOT NULL );
ALTER TABLE XX_Material ADD PRIMARY KEY (XX_MATERIAL_ID );
create a anonymized version of the database for testing purposes
To get a database that looks good for testing but does not expose secret data I choose a strategy of mangling the order of the data of single columns while keeping all other columns the old value. First I make a backup of the original data and copy it to a second (anonymized) database. Then I mangle the columns I want to anonymize one after each other.
UPDATE C_BPartner AS bp SET name=uebersetzung.neuerwert FROM( SELECT tab1.value, tab2.neuerwert FROM( select row_number() over(ORDER BY value), value FROM c_bpartner WHERE AD_Client_ID=1000000 ) AS tab1 LEFT JOIN ( select row_number() over(ORDER BY md5(value||coalesce(name,''))), name AS neuerwert, * FROM c_bpartner WHERE AD_Client_ID=1000000 ) AS tab2 USING (row_number) ) AS uebersetzung WHERE bp.value=uebersetzung.value ;
Repeat this with other columns than "name" (name2, address etc.). If you are afraid to expose the anonymizing algorithm here you may vary "md5(name)" (e.g. "md5('bla'||name)" gives a completely other order).
ADempiere Database
Copy German Translation for Document Types (Belegarten) to a new Client:
Version from needle58 for Oracle
-- 'name' fuer client fuer 'de_DE' aktualisieren auf den wert aus gw UPDATE c_doctype_trl dtt SET dtt.name = ( SELECT dtt1.name FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ), dtt.printname = ( SELECT dtt1.printname FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ), dtt.documentnote = ( SELECT dtt1.documentnote FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ) WHERE dtt.c_doctype_id >=1000000 AND dtt.ad_language='de_DE' ;
My (derived) version (PostgreSQL 8.4/ADempiere 3.6.1)
SET search_path = adempiere; UPDATE c_doctype_trl dtt SET name = coalesce(( SELECT dtt1.name FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ),dtt.name), printname = coalesce(( SELECT dtt1.printname FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ),dtt.printname), documentnote = coalesce(( SELECT dtt1.documentnote FROM c_doctype_trl dtt1 WHERE dtt1.c_doctype_id = ( SELECT dt1.c_doctype_id FROM c_doctype dt, c_doctype dt1 WHERE dt1.c_doctype_id < 1000000 AND dt1.c_doctype_id <> dt.c_doctype_id AND dt1.name = dt.name AND dt.c_doctype_id = dtt.c_doctype_id ) AND dtt1.ad_language='de_DE' ),dtt.documentnote) WHERE dtt.c_doctype_id >=1000000 AND dtt.ad_language='de_DE' ;
extract description and help from ADempiere database
Table information:
SELECT tablename, name, description, help FROM AD_Table WHERE tablename LIKE 'BAY_%';
Same with translation data:
SELECT AD_Table.tablename, AD_Table.name, AD_Table.description, AD_Table.help, AD_Table_TRL.name AS name_de FROM AD_Table LEFT JOIN ad_table_trl USING(ad_table_id) WHERE AD_Table_TRL.ad_language = 'de_DE' AND tablename LIKE 'BAY_%'
Column information:
SELECT AD_Table.tablename, AD_Table.name, AD_Table.description, AD_Table.help, AD_Column.name, AD_Column.description, AD_Column.help FROM AD_Table LEFT JOIN AD_Column USING(AD_Table_ID) WHERE AD_Table.tablename LIKE 'BAY_%'
extract all values of a list reference (aka "enum values")
This example extracts all values (and names) of my own created list reference "BAY_ConditionLevel":
SELECT ad_ref_list.value, ad_ref_list.name FROM ad_reference LEFT JOIN ad_ref_list USING (ad_reference_id) WHERE ad_reference.name='BAY_ConditionLevel';
extract data about your own company
You can use this e.g. for a report header or footer.
SELECT /* Header */ AD_Client.name, C_Location.address1, /* Logo */ AD_Image.binarydata, /* Briefkopffenster */ /* AD_Client.name, */ /* C_Location.address1, */ C_Location.address2, C_Location.postal, C_Location.city, /* Footer */ AD_Org.Description, AD_OrgInfo.Phone, AD_OrgInfo.Fax, AD_OrgInfo.TaxID, AD_OrgInfo.RegisterEntry, C_Bank.Name AS BankName, C_Bank.RoutingNo, C_BAnkAccount.AccountNo, /* Benutzer */ AD_User.Name AS UserName FROM AD_PInstance LEFT JOIN AD_OrgInfo USING (AD_Org_ID, AD_Client_ID) LEFT JOIN AD_Org USING (AD_Org_ID, AD_Client_ID) LEFT JOIN AD_Client USING (AD_Client_ID) LEFT JOIN AD_ClientInfo USING(AD_Client_ID) LEFT JOIN AD_Image ON(AD_ClientInfo.logoreport_id = AD_Image.AD_Image_ID) LEFT JOIN C_Location USING(C_Location_ID) LEFT JOIN AD_User USING(AD_User_ID) LEFT JOIN C_BankAccount ON(C_BankAccount.AD_Org_ID=AD_OrgInfo.AD_Org_ID AND C_BAnkAccount.Description='Hauptkonto') LEFT JOIN C_Bank USING(C_Bank_ID) WHERE AD_PInstance.AD_PInstance_ID=1000421 ;
In JasperReports you should not use the hardcoded ID of "1000421" but extract it from the Parameters that ADempiere gives you. Create the Parameter AD_PINSTANCE_ID and use something like "1000421" as the default (for testing in iReport). When using from inside ADempiere it will get the right data for the calling user.
FreiBier Data Tables
read Statistics
Example from User:Srcfrge64:
SELECT p.value as "Artikel-Nummer", p.name as "Artikelbezeichnung", p.volume, sum(us.stueck) as "Gekauft Gebinde", sum(us.umsatz) as "Umsatz EUR", sum(us.stueck * p.volume)/100 as "(hl)" FROM adempiere.bay_umsatzstatistik us, adempiere.bay_statistikperiode sp, adempiere.m_product p WHERE c_bpartner_id = 1003095 --and p.m_product_id = 1000333 AND sp.bay_statistikperiode_id = us.bay_statistikperiode_id AND us.m_product_id = p.m_product_id AND sp.description IS NULL AND sp.enddate > '01.01.2011' AND sp.statistiktyp = 'MD' GROUP BY p.value, p.name, p.volume ORDER BY p.value
(re)create view BAY_Leihgut_V
DROP VIEW BAY_Leihgut_V; CREATE VIEW adempiere.BAY_Leihgut_V AS WITH verschiedene AS ( SELECT min(BAY_LeihgutZugang_ID) AS BAY_LeihgutZugang_ID, BAY_LeihgutArt_ID, serno, sum(quantity) AS quantity /* noch nicht Datums-gesteuert */ FROM BAY_LeihgutZugang GROUP BY BAY_LeihgutArt_ID,serno ) SELECT verschiedene.BAY_LeihgutZugang_ID AS BAY_Leihgut_V_ID, BAY_LeihgutArt.AD_Client_ID, BAY_LeihgutArt.AD_Org_ID, BAY_LeihgutArt.created, BAY_LeihgutArt.createdBy, BAY_LeihgutArt.updated, BAY_LeihgutArt.updatedBy, CASE WHEN BAY_LeihgutArt.Description IS NOT NULL THEN BAY_LeihgutArt.Description ELSE BAY_LeihgutZugang.Description END AS Description, CASE WHEN BAY_LeihgutArt.Help IS NOT NULL THEN BAY_LeihgutArt.Help ELSE BAY_LeihgutZugang.Help END AS Help, BAY_LeihgutArt.isActive, BAY_LeihgutArt.M_Product_ID, BAY_LeihgutArt.Name || CASE WHEN verschiedene.serno IS NOT NULL THEN ' - ' || verschiedene.serno ELSE '' END AS Name, verschiedene.quantity, ( SELECT max(dateDelivered) FROM BAY_LeihgutWartung WHERE BAY_Leihgut_V_ID=verschiedene.BAY_LeihgutZugang_ID ) AS letzteWartung FROM verschiedene LEFT JOIN BAY_LeihgutArt USING(BAY_LeihgutArt_ID) LEFT JOIN BAY_LeihgutZugang USING(BAY_LeihgutZugang_ID) ;
(re)create view BAY_Leihveranstaltung_Zeile_V
This is a helper view that adds some calculated fields to the table BAY_Leihveranstaltung_Zeile.
DROP VIEW BAY_Leihveranstaltung_Zeile_V; CREATE VIEW adempiere.BAY_Leihveranstaltung_Zeile_V AS SELECT ( SELECT sum(zugaenge.quantity) FROM BAY_LeihgutZugang AS ich, BAY_Leihgutart AS art, BAY_LeihgutZugang AS zugaenge, BAY_Leihveranstaltung AS event WHERE ich.BAY_LeihgutZugang_ID = BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID AND ich.BAY_Leihgutart_ID = art.BAY_Leihgutart_ID AND art.isActive='Y' AND art.BAY_Leihgutart_ID = zugaenge.BAY_Leihgutart_ID AND zugaenge.isActive='Y' AND BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = event.BAY_Leihveranstaltung_ID AND zugaenge.dateReceived <= event.startDate ) AS Bestand, ( SELECT sum(allzeilen.quantity) FROM BAY_Leihveranstaltung AS myevent, BAY_Leihveranstaltung AS allevents, BAY_Leihveranstaltung_Zeile AS allzeilen, BAY_LeihgutZugang AS zugang, BAY_LeihgutZugang AS myzugang WHERE BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = myevent.BAY_Leihveranstaltung_ID AND allevents.startDate <= myevent.endDate AND allevents.endDate >= myevent.startDate AND allevents.isActive='Y' AND allzeilen.BAY_Leihveranstaltung_ID = allevents.BAY_Leihveranstaltung_ID AND allzeilen.BAY_Leihgut_V_ID = zugang.BAY_LeihgutZugang_ID AND BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID = myzugang.BAY_LeihgutZugang_ID AND zugang.BAY_Leihgutart_ID = myzugang.BAY_Leihgutart_ID ) AS Bestellt, ( SELECT sum(allzeilen.quantity) FROM BAY_Leihveranstaltung AS myevent, BAY_Leihveranstaltung AS allevents, BAY_Leihveranstaltung_Zeile AS allzeilen WHERE BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = myevent.BAY_Leihveranstaltung_ID AND allevents.startDate >= myevent.startDate AND allevents.endDate <= myevent.endDate AND allevents.isActive='Y' AND allzeilen.BAY_Leihveranstaltung_ID = allevents.BAY_Leihveranstaltung_ID AND allzeilen.BAY_Leihgut_V_ID = BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID ) AS EinzelnBestellt ,* FROM BAY_Leihveranstaltung_Zeile
(re)create a view of the weeks of the next year (Kalenderwochen)
CREATE VIEW adempiere.BAY_Kalenderwoche_V AS WITH montage AS (SELECT generate_series( current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) - interval '7 days', current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) + interval '1 year', interval '1 week' ) AS montag) SELECT 0 AS AD_Client_ID, 0 AS AD_Org_ID, now() AS created, now() AS updated, 0 AS createdBy, 0 AS updatedBy, date_part('epoch', montag) AS BAY_Kalenderwoche_V_ID, date_part('week', montag) AS kw, lpad(date_part('week', montag)::text,2,'0') || '/' || substring(date_part('isoyear', montag)::text,3) AS kwBezeichnung, montag AS start, montag + interval '6 days' AS ende, ( SELECT count(*) FROM BAY_LeihVeranstaltung WHERE (NOT startDate > montag + interval '7 days') AND (NOT endDate < montag) ) AS veranstaltungen FROM montage ;
Tests for exclusive groups
Has a Product more than one Product group?
SELECT M_Product.M_Product_ID, M_Product.Value, count(*) FROM M_Product FULL JOIN ( BAY_Artikelgruppe INNER JOIN BAY_Gruppe USING(BAY_Gruppe_ID) INNER JOIN BAY_Gruppenkategorie ON( BAY_Gruppe.BAY_Gruppenkategorie_ID = BAY_Gruppenkategorie.BAY_Gruppenkategorie_ID AND BAY_Gruppenkategorie.Value='AZ' ) ) USING(M_Product_ID) WHERE M_Product.isActive='Y' AND M_Product.AD_Client_ID=1000000 GROUP BY M_Product.M_Product_ID, M_Product.Value HAVING count(*)!=1
Has a product no product group?
SELECT * FROM M_Product FULL JOIN ( BAY_Artikelgruppe INNER JOIN BAY_Gruppe USING(BAY_Gruppe_ID) INNER JOIN BAY_Gruppenkategorie ON( BAY_Gruppe.BAY_Gruppenkategorie_ID = BAY_Gruppenkategorie.BAY_Gruppenkategorie_ID AND BAY_Gruppenkategorie.Value='AZ' ) ) USING(M_Product_ID) WHERE M_Product.isActive='Y' AND M_Product.AD_Client_ID=1000000 AND BAY_Gruppe.BAY_Gruppe_ID IS NULL