Add DDL files for PostgreSQL and migrate production to PostgreSQL

[dboehmer]

This is especially desirable for the Docker images where you usually have no persistent storage for the SQLite file.

[dboehmer]

While developing on this I notice: This is looong overdue! I found several inconsistencies in the SQLite data:

• numeric column with German number format 0,1 with comma
• numeric and boolean columns with value of empty string
• dangling dish ingredients whose dish doesn’t exist anymore

[dboehmer]

@moseschmiedel Can you please try to migrate existing SQLite file(s) in your work dir to Pgsql?

Steps required:

• create Pgsql database
• checkout postgresql branch
• configure dbic.yaml to Pgsql (see current state of README.md!)
• script/coocook_deploy.pl install
• script/coocook_sqlite2pgsql.sh coocook.sqlite | tee tmp.sql
• <tmp.sql psql -h localhost -d coocook -U coocook >/dev/null
• verifiy if server runs and app works

As I wrote before I had to fix some issues in production data. This is my fix script:

-- German number format with comma
UPDATE   dish_ingredients SET value=REPLACE(value, ',' , '.') WHERE value LIKE '%,%';
UPDATE recipe_ingredients SET value=REPLACE(value, ',' , '.') WHERE value LIKE '%,%';
UPDATE items              SET value=REPLACE(value, ',' , '.') WHERE value LIKE '%,%';

-- boolean columns with empty string
UPDATE   dish_ingredients SET prepare=0 WHERE prepare='';
UPDATE recipe_ingredients SET prepare=0 WHERE prepare='';
UPDATE units              SET   space=0 WHERE   space='';

-- dangling dish_ingredients with dish not existing (17 rows on 2020-12-23)
DELETE FROM dish_ingredients
WHERE NOT EXISTS (
    SELECT * FROM dishes
    WHERE dishes.id = dish_ingredients.dish_id
);

-- dangling dishes_tags with dish not existing (4 rows on 2020-12-23)
DELETE FROM dishes_tags
WHERE NOT EXISTS (
    SELECT * FROM dishes
    WHERE dishes.id = dishes_tags.dish_id
);

-- dangling roles_users with user not existing (1 row on 2020-12-23)
DELETE FROM roles_users
WHERE NOT EXISTS (
    SELECT * FROM users
    WHERE users.id = roles_users.user_id
);