public inbox for [email protected]help / color / mirror / Atom feed
Re: Creating files with testdata 6+ messages / 3 participants [nested] [flat]
* Re: Creating files with testdata @ 2025-03-10 19:15 H <[email protected]> 2025-03-10 19:21 ` Re: Creating files with testdata David G. Johnston <[email protected]> 2025-03-10 19:27 ` Re: Creating files with testdata Francisco Olarte <[email protected]> 2025-03-11 03:10 ` Re: Creating files with testdata H <[email protected]> 0 siblings, 3 replies; 6+ messages in thread From: H @ 2025-03-10 19:15 UTC (permalink / raw) To: [email protected] On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating files with testdata 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> @ 2025-03-10 19:21 ` David G. Johnston <[email protected]> 2025-03-10 21:10 ` Re: Creating files with testdata H <[email protected]> 2 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2025-03-10 19:21 UTC (permalink / raw) To: H <[email protected]>; +Cc: [email protected] On Mon, Mar 10, 2025 at 12:17 PM H <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating files with testdata 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> 2025-03-10 19:21 ` Re: Creating files with testdata David G. Johnston <[email protected]> @ 2025-03-10 21:10 ` H <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: H @ 2025-03-10 21:10 UTC (permalink / raw) To: [email protected] On March 10, 2025 3:21:57 PM GMT-04:00, "David G. Johnston" <[email protected]> wrote: >On Mon, Mar 10, 2025 at 12:17 PM H <[email protected]> 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. The finished application will also use UUIDs which is why am using it now. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating files with testdata 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> @ 2025-03-10 19:27 ` Francisco Olarte <[email protected]> 2025-03-10 21:05 ` Re: Creating files with testdata H <[email protected]> 2 siblings, 1 reply; 6+ messages in thread From: Francisco Olarte @ 2025-03-10 19:27 UTC (permalink / raw) To: H <[email protected]>; +Cc: [email protected] On Mon, 10 Mar 2025 at 20:18, H <[email protected]> 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. 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating files with testdata 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> 2025-03-10 19:27 ` Re: Creating files with testdata Francisco Olarte <[email protected]> @ 2025-03-10 21:05 ` H <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: H @ 2025-03-10 21:05 UTC (permalink / raw) To: [email protected] On March 10, 2025 3:27:02 PM GMT-04:00, Francisco Olarte <[email protected]> wrote: >On Mon, 10 Mar 2025 at 20:18, H <[email protected]> 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. > >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. K am using UUIDs which the application will use as well. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating files with testdata 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> @ 2025-03-11 03:10 ` H <[email protected]> 2 siblings, 0 replies; 6+ messages in thread From: H @ 2025-03-11 03:10 UTC (permalink / raw) To: [email protected] On March 10, 2025 3:15:10 PM EDT, H <[email protected]> wrote: >On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver ><[email protected]> 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. 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-03-11 03:10 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-10 19:15 Re: Creating files with testdata H <[email protected]> 2025-03-10 19:21 ` David G. Johnston <[email protected]> 2025-03-10 21:10 ` H <[email protected]> 2025-03-10 19:27 ` Francisco Olarte <[email protected]> 2025-03-10 21:05 ` H <[email protected]> 2025-03-11 03:10 ` H <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox