Subject: Re: Creating files with testdata

From: H <[email-masked]>
Date: Mon, 10 Mar 2025 15:15:10 -0400

On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver <[email-masked]> wrote:
>
>
>On 3/10/25 11:09 AM, H wrote:
>> I am developing a complex multi-tenant application in postgresql 16
>in Linux. During the development I would like to be able to enter test
>data into various related tables at any given time for testing
>purposes.
>> 
>> While this can certainly be accomplished by predefined CTE INSERT
>statements in an SQL file, I would prefer something more flexible. I am
>thinking of using a markdown file as a source for these CTE INSERT
>statements. Specifically, having a certain structure in the markdown
>file where a given markdown heading level, bullet level etc. would
>correspond to specific tables and columns.
>
>Why CTE INSERTs?
>
>> 
>> After entering my test data into the markdown file for the given test
>scenario, I would then run an awk script or similar to create a SQL
>file with the various CTE INSERT statements. Howevever, it gets complex
>since I need to handle 1:N relationships between tables in the markdown
>file...
>> 
>> I hope the above outline is understandable and am interested in
>comments and thoughts on my above approach.
>> 
>> Thanks.
>> 
>> 

There are tables referencing each other using randomly generated IDs, ie. those IDs are not known until after the parent table row is inserted.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 10 Mar 2025 12:21:57 -0700

On Mon, Mar 10, 2025 at 12:17 PM H <[email-masked]> wrote:

> There are tables referencing each other using randomly generated IDs, ie.
> those IDs are not known until after the parent table row is inserted.
>

I just reserve half of the number space of bigint, the negatives, for test
data and assign known IDs as part of the test data specification.

David J.

---

From: Francisco Olarte <[email-masked]>
Date: Mon, 10 Mar 2025 20:27:02 +0100

> There are tables referencing each other using randomly generated IDs, ie. those IDs are not known until after the parent table row is inserted.

Random? Then they CAN collide. ;->

Do the IDs need to be random or are they just the default value?

I've encountered a similar problem with sequences and have bypassed it
by using explicit values into the test data, i.e.m, inserting an
invoice with id 1000 followed by its lines with IDs 100010, 100011,
.... and then setting start_values for the sequences at 99999999. This
helped me a lot in a debugging. You could do the same depending on
what domain your random ids are on.

Francisco Olarte.

---

From: H <[email-masked]>
Date: Mon, 10 Mar 2025 17:05:13 -0400

On March 10, 2025 3:27:02 PM GMT-04:00, Francisco Olarte <[email-masked]> wrote:
>> There are tables referencing each other using randomly generated IDs,
>ie. those IDs are not known until after the parent table row is
>inserted.
>

K am using UUIDs which the application will use as well.

---

From: H <[email-masked]>
Date: Mon, 10 Mar 2025 17:10:44 -0400

On March 10, 2025 3:21:57 PM GMT-04:00, "David G. Johnston" <[email-masked]> wrote:
>ie.

The finished application will also use UUIDs which is why am using it now.

---

From: H <[email-masked]>
Date: Mon, 10 Mar 2025 23:10:25 -0400

>ie. those IDs are not known until after the parent table row is

I thought I should give an example of why I need this. This is /not/ the application but might be a useful example:

Imagine a very simple Wikipedia-clone. You have a couple of DB tables for article subjects in a couple of levels, a table for body text and a couple of different tables for references and for images related to the article in question. References and images are not referenced inline in the body text but at the end.

Everything is generated manually, ie the body text is written, various references added, and a suitable subject area in different levels chosen, all by an author.

I want to be able to write this "article", with the appropriate references, images etc to be done outside this database application. My vision is it's done in a markdown document where a strict use of heading levels, bullet lists etc would correlate with subject areas, body text, references, images and what-nots.

A script of some kind that can parse markdown then generates the necessary SQL statements to insert all of this in the appropriate tables, all according to how the information was laid out in the markdown document.

My initial thought was to use awk but it's (awk)ward since it is strictly line/paragraph oriented. Pandoc parses markdown very well, generates an AST internally and then the output filter should be able to generate complex SQL statements such as CTEs.

Hope that might shed some more light on my thought process.