jooq / sakila Goto Github PK

Add a H2 port.

Triggers or stored procedures are not required.

This allows SQLite to set ID columns as rowid which basically allows not setting IDs when inserting or performing similar operations.

Hi there,

I've just ported this over to Informix, and I'll happily submit a pull request shortly.

However, because I've had to guess at some of the behaviour in MySQL (which I've never used) I wondered if you had any tests, such as "SELECT function(x, y) should return 10 rows of data with these columns and these values". Even allowing for formatting differences it would still prove the basic functionality exists and is correct?

It would be convenient if a port of the PostgreSQL schema existed also for CockroachDB. The syntax is almost the same, though some PG features aren't supported in CRDB.

There are views like this:

CREATE VIEW film_list
AS
SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
	film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id
        JOIN film_actor ON film.film_id = film_actor.film_id
	JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id;

This view can't be fetched from, as there's an error in strict mode:

Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.category.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

There are a few problems when trying to import the scripts in MySQL workbench:

• Add USE sakila in the script
• ALTER TABLE store CHANGE COLUMN manager_staff_id manager_staff_id TINYINT UNSIGNED NULL produces "Error Code: 3780. Referencing column 'manager_staff_id' and referenced column 'staff_id' in foreign key constraint 'fk_store_staff' are incompatible."

ALTER TABLE store ADD CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id);

fails because the script earlier is altering the column to TINYINT, should just be INT when referencing that column.

Hi @lukaseder,

First, Many thanks for this repository :)

I was reviewing this repository because I have interest in the sakila support for Postgres and I reviewed how the jooq demo is using the scripts about sakila:
https://github.com/jOOQ/demo/blob/main/jOOQ-demo-oss/jOOQ-demo-utils/src/main/java/org/jooq/demo/AbstractDemo.java

Using the scripts from this repo:
https://github.com/jOOQ/sakila/tree/main/postgres-sakila-db

I tried to create a Docker image with the data, but I was not able to do it because in the execution it raise some error:

FROM postgres:alpine
ADD postgres-sakila-db/postgres-sakila-schema.sql postgres-sakila-db/postgres-sakila-insert-data.sql /docker-entrypoint-initdb.d/

I tried with the scripts from the demo too without success:

FROM postgres:alpine
ADD \
  postgres/V1__postgres-sakila-schema-init.sql \
  postgres/V2__postgres-sakila-schema-tables.sql \
  postgres/V3__postgres-sakila-schema-functions.sql \
  postgres/V4__postgres-sakila-schema-constraints.sql \
  postgres/V5__postgres-sakila-insert-data.sql \
  /docker-entrypoint-initdb.d/

Does exist a way to populate to emulate the following repo:
https://github.com/fspacek/docker-postgres-sakila

The main motivation to this issue is run a docker image with the date prepopulated.

Many thanks in advance

Juan Antonio

The CockroachDB port uses COPY: #2, just like the YugabyteDB port: https://raw.githubusercontent.com/jOOQ/sakila/main/yugabytedb-sakila-db/yugabytedb-sakila-insert-data.sql

We should do that as well for PostgreSQL