Open Source ERP Academy

SQL Primer

The purpose of this page is to help you get better at creating SQL and interacting with the iDempiere and ADempiere database.

Connecting to Your Database with phppgadmin and psql

Additional Resources:

Book Recommendation

I plan to create videos to help you get started. In the mean time, here is an excellent SQL getting starting guide. I have used this book to teach several people SQL.

w3schools.com also has a great tutorial on SQL in general.

How to Perform an iDempiere Database Backup

This section explains the build-in iDempiere database backup tools. See this section for details on backing up your database to a remote location.

cd /opt/idempiere-server/utils/
./RUN_DBExport.sh

More Advanced Database Backup Options

https://www.youtube.com/watch?v=FU7eqwNCD-I

How to Perform an iDempiere Database Restore

This section explains the build-in iDempiere database restore tools. See this section for details on restoring up your database from a remote location.

sudo service idempiere stop
cd /opt/idempiere-server/utils/
./RUN_DBRestore.sh
sudo service idempiere start

Create a Limited or Read-Only User

See dedicated page

script: https://github.com/chuboe/idempiere-installation-script/blob/master/utils/chuboe_bi_init_permissions.sql

See limited example here

Link to discussion with steps to create a read only user

To use the read-only user in Excel, You could install the PostgreSQL ODBC driver on the Windows machine, and then connect Excel to the database like explained in this blog post (except using ODBC rather than OLEDB).

Creating a New Database and User from Scratch

See dedicated page

Set Search Path

There are times when you connect to the iDempiere database; however, you may not have the adempiere schema in your search path. As a result, your query that looks like "select * from c_order" will return an error stating that the c_order table does not exist - even though you know the table exists. Execute the below statement during your session to update it (your current session) to know to look in the adempiere schema for details.

SET search_path = adempiere;

You can permanently set this value in PGAdmin 4 (IIII) by right-clicking on the 'idempiere' database tree node in the left-hand tree and adding a Properties record:

You can also modify the user/role with a default search path:

ALTER ROLE <your_login_role> SET search_path TO a,b,c;

Setting the search path is important for newly created roles (including bi roles). There are times when you get an error that a function (example: adddays()) does not exist. This error occurs because the schema is not prefixed in the view and the role/user does not have the adempiere schema set.

Connecting via Unix Socket

In the Nix/NixOS page, we discuss creating a new database and connecting via a unix socket. There might be times when you need to turn off TCP altogether. Below are the instructions on how to modify postgresql.conf:

If you need to connect a local psql client to a remove data server running postgresql configured for unix sockets, you can use the following command to create a tunnel:

ssh -L /tmp/.s.PGSQL.5432:/var/run/postgresql/.s.PGSQL.5432 user@remote_host

This command does the following:

To connect from the local machine to the remote db (assuming the ssh tunnel is up):

psql -h /tmp -U username postgres

Here, `/tmp` is the directory where we created the local Unix socket, and `postgres` is the name of the database you want to connect to.

Remove User from Database

revoke all privileges on all tables in schema adempiere from powerbiaccess_role;
revoke all privileges on all sequences in schema adempiere from powerbiaccess_role;
revoke all privileges on all functions in schema adempiere from powerbiaccess_role;
revoke all privileges on schema adempiere from powerbiaccess_role;
revoke usage on schema adempiere from powerbiaccess_role ;
alter default privileges in schema adempiere revoke select on tables from powerbiaccess_role ;
drop role powerbiaccess_role;

Comments on Tables and Columns

Comments using "--" in sql do not save with table and view definitions. If you ever need to create a comment, use the following (copied from postgrest.org):

COMMENT ON SCHEMA mammals IS
  'A warm-blooded vertebrate animal of a class that is distinguished by the secretion of milk by females for the nourishment of the young';

COMMENT ON TABLE monotremes IS
  'Freakish mammals lay the best eggs for breakfast';

COMMENT ON COLUMN monotremes.has_venomous_claw IS
  'Sometimes breakfast is not worth it';

Great PostgreSQL Videos

Advanced SQL Concepts

There are times when a simple join or sub-select does not work or is not performant for your circumstances. Below  is a quick list of techniques and tools to help you improve your abilities.

Performance Tuning and Log Analyzer

Start here...

Also run postgresqltuner ubuntu cli application. Note that you can install it on any server (not necessarily the db server). Example usage:

./postgresqltuner.pl --host=somehost --database=idempiere --user=adempiere --password='somepassword'

pgbadger is a powerful postgresql log analyzer. It helps highlight and visualize historical sql events and trends.

See this installation script and notes for more details (including maintaining postgresql logs).

See PostgreSQL Performance Tuning page for more performance tuning information.

WAL (Write Ahead Log) Management

You have the ability to create a read replica. See the iDempiere installation script (search for the term "# IS_Replication") for how to create an instance with this feature turned on.

WAL logs are located in: /var/lib/postgresql/12/main/pg_wal

Convert Decimal to Interval

The following allows you convert a numeric/decimal/int to an interval.

select ts.amt_work, ts.amt_work * interval '1 hour' from timesheet_v ts;

Here is an alternate way.

select (ts.amt_work::text || ' hour')::interval from timesheet_v ts;

Convert Difference Between Two Timestamps

The following will calculate the number of hours between two timestamps:

SELECT created, updated, EXTRACT(EPOCH FROM updated - created) / 3600 AS hours from c_order limit 10;

Not that you can divide by 3600 and 24 (/3600/24) to get the number of days.

The following will calculate the difference in months:

(DATE_PART('year', now()) - DATE_PART('year', period.startdate)) * 12 + (DATE_PART('month', now()) - DATE_PART('month', period.startdate))

Good reference for SQL date difference:

http://www.sqlines.com/postgresql/how-to/datediff

Stop on psql Error

There are times when you want psql to return an error code when a sql file or command fails. You can use the '-v ON_ERROR_STOP=on' option to accomplish this task. Here is an example script

#!/usr/bash
set -e
psql -v ON_ERROR_STOP=on -d idempiere -U adempiere -f some.sql

In the above example,

Reference: https://dba.stackexchange.com/questions/3904/postgresql-exit-status-when-running-a-file

How to connect via the Command Line (CLI)

psql is an interactive command line tool for PostgreSQL. You can use it to administer the database or run SQL. You can connect two ways:

My script installs a .pgpass file in the user's home directory. This file allows you to connect using "psql -d idempiere -U adempiere" without using a password. Please be aware that this file only lists the password for the "adempiere" user. When you simply issue "psql", the system assume you are trying to log in with the "ubuntu" user (in my case).

If you connect using the command "sudo -u postgres psql", be aware that the idempiere database is not in postgres user's path. This fact means that you cannot issue the SQL statement "Select * From M_Product". Instead, you must issue "Select * From adempiere.M_Product" where adempiere is the name of the schema.

TO_CHAR Examples

Default Sequence for Primary Key (PostgreSQL)

iDempiere will create the primary key or ID for you when you create records through the user interface or through code. However, when you are importing directly into a table, there are times when you want the database to assign a primary key for you. Here are the commands to accomplish this task for a table named "i_bpartner".

CREATE SEQUENCE chuboe_ibp_seq START 900000000;

ALTER TABLE i_bpartner
ALTER COLUMN i_bpartner_id
SET DEFAULT NEXTVAL('chuboe_ibp_seq');

Notes:

Importing/Exporting Data from/to csv

The copy command is quite fast way to import and export data to and from PostgreSQL. This page gives good examples. The below commands are quick references:

copy (select * from some_table) to '/tmp'somemfile.csv' with csv;

Use the /copy psql command to execute the copy command from a remote server:

\copy (select * from c_order) to '/tmp/somefile.csv' with csv;

Setting Time Zone

Note: Start with simply changing the application server's operating system timezone first. Only change the database timezone if you determine there is an additional need.

It is a common request to want iDempiere records to represent the dominant timezone of its users. Here are two approaches to setting the timezone:

  1. Set the operating system's timezone.
    1. In Linux, you use the following command: sudo dpkg-reconfigure tzdata
    2. After performing this change:
      1. shut down the iDempiere service
      2. restart the postgresql  service
      3. Perform select now() to determine if the result is the correct time.
  2. If the above does not work, you can also set the timezone at the database itself.
    1. This page offers good guidance
      1. SELECT * FROM pg_timezone_names;
      2. ALTER DATABASE idempiere SET timezone TO 'Europe/Berlin';
      3. You can also set the timezone in postgresql.conf. Here are examples:
        1. timezone = 'US/Eastern'
        2. log_timezone = 'US/Eastern'

SQL Migrations

There are times when you need to manage and perform sql migrations outside of plugins.

Here is an example using sqlx-cli. Before we start, we need to make some assumptions and disclaimers:

Create the needed environment variable:

export DATABASE_URL=postgres://todo_admin@"[fd42:6b68:d9b4:b79e:216:3eff:fe9e:c497]"/todo

Notes about the above command:

Confirm the database exists:

sqlx database create

Create a new local sql migration file:

sqlx migrate add test01

Modify the newly created file to create a table my adding the following text:

CREATE TABLE public.Employees (
EmployeeID INT PRIMARY KEY,
FirstName TEXT,
LastName TEXT,
Email TEXT,
PhoneNumber TEXT,
HireDate DATE,
JobTitle TEXT,
Department TEXT,
Salary NUMERIC
);

Run the migration:

sqlx migrate run

Epoch - Convert to Date

The below takes iDempiere's processedon date and converts it to a timestamp. This is a way to know when a invoice, payment, order, etc... was completed.

to_timestamp((processedon / 1000::numeric)::double precision)::date AS closeddate

PostgreSQL Function Basics

Good PPT about wide variety of functions

Using Variables in Scripts

See PSQL reference in answers

Time Series Sequence

There are times when you need to report against a series (for example date or time). Below is an example showing you how to create the date series in a CTE (with clause) and using that series as the basis to join in other data.

with tseries as 
(
  select 
    date_trunc('day',dd)::date as date_series,
    extract(dow from dd) as dow
  from generate_series(now()-10,now()+100,'1 day'::interval) dd
)
select tseries.date_series, tseries.dow
from tseries
join some_table st on tseries.date_series between st.startdate and st.enddate

PostgreSQL Generate Series in Report - Great When Reporting Over Time/Dates

Using Impersonation with psql

Why impersonation matters: see PostgREST.org

Let's update an iDempiere database first with the needed impersonation artifacts. Start by connecting to the database and issuing the following commands:

create role web_anon nologin;
grant usage on schema adempiere to web_anon;
grant select on adempiere.c_bpartner to web_anon;
create role authenticator noinherit login password 'Silly';
grant web_anon to authenticator;

Now, you can issue the following psql command from a terminal to authenticate via the 'authenticator' role, but execute the query as a different role:

psql -h some-server -d idempiere -U authenticator -c "set SESSION ROLE web_anon" -c "select * from adempiere.c_bpartner limit 1"

If you remove the "-c ... set ROLE ..." command, the psql statement will fail with a "permission denied" error because the authenticator role does not have any privileges.

Working with the json Type

This is a work in progress. Here are the details so far:

psql to json (and other structured languages)

The purpose of this section is to document how to get structured data out of a database.

psqli -AXqtc "SELECT JSON_AGG(t) FROM (select * from c_order)) t"

or

psqli -AXqtc "SELECT JSON_AGG(t) FROM ($(cat some.sql)) t"

Where some.sql can represent any sql you wish to run. This method is handy when you have long, complex or difficult sql.

References:

Order Header with Lines in json Column

with lines as (
select c_invoice_id,
json_agg(
json_build_object('line',line)
) as line_detail
from c_invoiceline
group by c_invoice_id
)
select i.documentno, l.c_invoice_id, l.line_detail
from c_invoice i
join lines l on i.c_invoice_id = l.c_invoice_id
;

psql with pset Options

There are times when you needs to use psql as a command (not a REPL), and you need to use pset to describe a setting or output. Here is an example:

psql -c "\pset format csv" -f "$sql_file"

Where -c is tells psql to use this command.

psql \d Showing Query

There are times when you want to know how the following psql commends generate their data: \d \dt \dt+

The first thing you do is set:

\set ECHO_HIDDEN on

Then, when you issue "\dt+" in psql, you will get something like this:

Detailed query... plus the results (including comments)

List of relations
-[ RECORD 1 ]-+--------------------------------------------------------------------
Schema        | wf_private
Name          | stack_user
Type          | table
Owner         | user_20240527_01
Persistence   | permanent
Access method | heap
Size          | 16 kB
Description   | Table that contains users...

Cross Join for Translation

Here is an example:

CREATE OR REPLACE VIEW adempiere.jsm_bpartner_address_vt
 AS
 SELECT bpl.ad_client_id,
   bpl.ad_org_id,
   bpl.c_bpartner_id,
   bpl.c_bpartner_location_id,
   l.c_location_id,
   l.isactive,
   lang.ad_language,
   ctry.isaddresslinesreverse AS isreversed,
   ctry.isaddresslineslocalreverse AS islocalreversed,
   jsonb_build_object('lines', jsonb_build_array(l.address1, l.address2, l.address3, 
l.address4), 'city', l.city, 'postcode', l.postal, 'region', 
   COALESCE(rt.name, r.name), 'country', COALESCE(ctryt.name, ctry.name)) AS json
 FROM c_bpartner_location bpl
   LEFT JOIN c_location l ON l.c_location_id = bpl.c_location_id
CROSS JOIN (
     SELECT ad_language.ad_language
     FROM ad_language
     WHERE ad_language.issystemlanguage = 'Y'::bpchar
   ) lang
   LEFT JOIN c_region r ON r.c_region_id = l.c_region_id
   LEFT JOIN c_country ctry ON ctry.c_country_id = l.c_country_id
   LEFT JOIN c_region_trl rt ON r.c_region_id = rt.c_region_id 
     AND rt.ad_language = lang.ad_language
   LEFT JOIN c_country_trl ctryt ON ctry.c_country_id = ctryt.c_country_id 
     AND ctryt.ad_language = lang.ad_language;

 

Trigger to Create Audit Table

There are times when you need to know if/when something was deleted. While the change log (Change Audit window) will track delete record details if the record was deleted as a result of the model, there are times when iDempiere or the database will issue a delete sql statement directly (bypassing the model). Here are a couple of scenarios where this happens:

Below is a PostgreSQL example showing you how to create an 'audit' table to track deleted records.

-- create a table you wish to audit
create table delme_source (id BIGSERIAL PRIMARY KEY, name text);
insert into delme_source (name) values ('test01');
select * from delme_source;

-- create a table to store audit results
create table delme_audit (id BIGSERIAL, name text, deleted timestamp without time zone);

-- create function to insert audit results
CREATE OR REPLACE FUNCTION process_delme_audit() RETURNS TRIGGER AS $delme_audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO delme_audit SELECT OLD.id, OLD.name, now();
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$delme_audit$ LANGUAGE plpgsql;

-- create trigger to automatically call above function when needed
CREATE TRIGGER delme_audit
AFTER DELETE ON delme_source
    FOR EACH ROW EXECUTE FUNCTION process_delme_audit();

-- test the solution
insert into delme_source (name) values ('test02');
select * from delme_audit;
delete from delme_source where name = 'test02';
select * from delme_audit;

-- clean up and remove above artifacts if/when needed
--drop table delme_audit;
--drop table delme_source;
--drop function process_delme_audit;

Reference: plpgsql-trigger

Monitoring

PostgreSQL has a list of tools to use for monitoring: see documentation

Troubleshooting

Here are the quick instructions:

Here is an example of SQL History:

--select * from chuboe_lock_recursive_v;
--select * from chuboe_lock_detail_v;
--SELECT * FROM pg_stat_activity;
--SELECT pg_cancel_backend(29687);
--select * from chuboe_lock_recursive_v;
--SELECT pg_cancel_backend(29686);
--select * from chuboe_lock_recursive_v;
--SELECT pg_cancel_backend(26941);
--select * from chuboe_lock_recursive_v;
--select * from chuboe_lock_detail_v;
--SELECT * FROM pg_stat_activity;--select name from c_bpartner where c_bpartner_id = 1000374;

Locking is by far the most common issue you will have with a database. If a lock exists, there is a bug with the application logic (either in your code or the core code). Here are the tools I use most frequently. Below are other resources I have found over the years.

How to search for a pid from command line and show in vim:

top -H -b -n1 -c | vim -

How to end a blocking PID

SELECT pg_cancel_backend(<pid of the process>);

PostgreSQL Lock (blocking-locking/contention) Monitoring

Lock Dependency Information - includes recursive lock query

Exploring Query Locks in Postgres

iDempiere Google Group Discussion on the Topic

Looking for long running queries - how to kill them

Database Lock Notes

There should never be a lock in the database if the code is well written. Locks arise when one or more of the following occur:

Recursive Database Functions - Example bompricelimit(...)

There are times when you need to perform a database calculation (using a function) when the data structure is somewhat complex.

One such example is calculating the total cost of a product based on the sum of its BOM (bill of materials) components. The challenge is particularly tough because a product can have an near infinite number of sub-products.

The example solution: bompricelimit(...), in both ADempiere and iDempiere, is a good example of how to traverse a tree of sub-products to calculate a cumulative cost. It uses the concept of recursion to solve the problem. In the case of bompricelimit(), it is a recursive function that calls on itself until it reaches the lowest most BOM product. Each time it calls on itself, it takes one step down. Once it reaches the bottom, it starts to aggregate the results of all the calls into one single total.

To see an example of bompricelimit(...), search for "bompricelimit".

Grant Execute on Function

Here is an example of how to grant execute privileges to a read-only user:

grant execute on function currencyconvert(numeric, numeric, numeric, timestamp with time zone, numeric, numeric, numeric) to biaccess;

Change Function Security to Invoker

Many times you wish for a fuction to be called from a view. If the user has access to the view, then they should by proxy have access to the functions as well. The below statement updates the function's security/priviledge to do just that. Also see "SET search_path" comment on this page to resolve function not found error.

alter function currencyconvert(numeric, numeric, numeric, timestamp with time zone, numeric, numeric, numeric) SECURITY INVOKER;

Connect to a Remote PostgreSQL Instance

Good introduction to DBLink - tool used to query a remote postgresql server DBLink reference docs How to create a linux script to push data over SSH on a schedule

Check to see when Materialized View Last Refreshed

psql -c "select relfilenode from pg_class where relname = 'bi_invoice_line_cache'"

==> relfilenode = 4864145

sudo fdfind 4864145 /var/lib/postgresql/

==> /var/lib/postgresql/12/main/base/4856550/4864145

sudo ls -la /var/lib/postgresql/12/main/base/4856550/4864145

==> postgres postgres 0 Jul 31 08:27

Postgresql 15 - Changes to Privileges

I experienced this error when installing metabase: ERROR: permission denied for schema public. The below reference explains the issue. The quick fix was to modify the 'createdb' command to use the -O option to specify metabase as the owner of the db.

Reference: https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public