How to apply SQL query on docker

[herrdeh (https://tryton-dach.org)]

Please apologize my stupid question - I guess the above are command lines - true? How do they work as docker commands?

Cheers,
Wolf

[dave (David Harper)]

@herrdeh

I guess the above are command lines

The migration items that start with [SQL] are SQL queries, you need to run them on your database.

I’m guessing that pgAdmin query tool will allow you to run the queries on the database, otherwise you can use psql in your postgres docker container:

sudo docker exec [ID-of-postgres-container] psql -U postgres -d [name-of-database]

Then at the prompt type in the query.

Hope that helps?!

[johnny]

Building on Dave’s answer on psql:
You can even pass your SQL-Script to your postgres-container like this:

sudo docker exec [ID-of-postgres-container] psql -U postgres -d [name-of-database] -c "[QUERY]"

So if you’re using the same names as described on Dockerhub and want to upgrade from 5.4 to 5.6, this should do the trick:

sudo docker exec tryton-postgres psql -U postgres -d tryton -c "UPDATE project_work SET status = db_id FROM ir_model_data WHERE module = 'project' AND fs_id = 'work_open_status' AND state = 'opened';"
sudo docker exec tryton-postgres psql -U postgres -d tryton -c "UPDATE project_work SET status = db_id FROM ir_model_data WHERE module = 'project' and fs_id = 'work_done_status' AND state = 'done';"
sudo docker exec tryton-postgres psql -U postgres -d tryton -c "ALTER TABLE sale_amendment_line DROP CONSTRAINT sale_amendment_line_shipment_party_fkey;"

[herrdeh (https://tryton-dach.org)]

Thank you both very much. That’s the level of complexity I can handle… (-;

I started both tryton-postgres and tryton containers, then tried @johnny’s first and second command line. The result is this line:

ERROR: relation "project_work" does not exist LINE 1: UPDATE project_work SET status = db_id FROM ir_model_data WH...

With the 3rd line, I get this:
ERROR: relation "sale_amendment_line" does not exist

Tryton version 5.4.7
My guess was that these manual changes should be applied BEFORE the command
trytond-admin -c <config file> -d <database name> --all

Correct?

Cheers,
Wolf

[pokoli (Sergi Almacellas Abellana)]

Those queries are only required if you have the project and sale_amendement modules activated.

If you do not have any of the activated it is normal that both of them fail. So you can ignore the errors

[herrdeh (https://tryton-dach.org)]

Thank you - this is a really easy solution for my problem… (-;