Transform INSERTs in share/test_data.sql from value-lists to key-value-lists

[dboehmer]

The test data in share/test_data.sql can be used for development and are used as fixtures for many of the Perl test files in t/. Unfortunately the SQL INSERT statements use pure values lists what makes it difficult to read.

The SQL shall be transformed from, e.g. for table articles_units:

INSERT INTO 'articles_units' VALUES(1,2);
INSERT INTO 'articles_units' VALUES(2,1);

to

INSERT INTO 'articles_units'
  ( article, unit ) VALUES
  ( 1,       2 ),
  ( 2,       1 ),
  ...
;

• The result of the SQL statements should stay the same, of course.
• Tests must still pass.
• Bonus: values and keys vertically aligned.

For the syntax in SQLite see https://sqlite.org/lang_insert.html

[dboehmer]

@moseschmiedel As discussed last Thursday: This would be a good first issue for you. You're already familiar with SQL, I think. But this also includes running the Perl tests. And it would be really helpful for me to be able to edit the test data more quickly.

[moseschmiedel]

@dboehmer Is it possible, that the testing framework for the database somehow doesn't like line breaks?

[dboehmer]

Yeah, that is absolutely perfectly possible just about right.

The file is read line by line and each line executed as a single SQL statement. Sorry that I didn't think of that.

We're not just piping the SQL file content to to a sqlite3 process but execute statements through the DBI interface. What do you think should we do?

• try to parse the SQL file to identify single statements
• insert any marker to split SQL statements, e.g. a special comment like --- or a blank line

Any other ideas?

[moseschmiedel]

I like the idea of having a special sequence in the sql file which indicates, that we want to continue the line or with other words: want to concatenate the following line to the current one with an added whitespace. As keyword sequence i would propose: --\-- as the backslash is the normal character used for this purpose (in other languages)

[moseschmiedel]

It came to my mind, that the easiest solution probably would be that we just read lines until a semicolon is found at the end of a line. Because then we wouldn't need a new special sequence to indicate line continuation.

[dboehmer]

It came to my mind, that the easiest solution probably would be that we just read lines until a semicolon is found at the end of a line.

Yeah, when I wrote try to parse the SQL file to identify single statements I thought of searching for semicolons. But then there could be a semicolon in a string. Stupid "parsing" is nearly always a bad idea.

You made a very good point: A semicolon at the of a line should be safe enough. :+1:

Do you want me to adapt the Perl code for you or do like to tackle this as part of this issue? I think the task is still small and consistent.

• Reading the SQL file and executing the statements is all in t/lib/TestDB.pm
• Run this test before and after your changes: $ prove --lib --verbose t/TestDB.pm

[moseschmiedel]

I'll do the changes in TestDB.pm also.