2 ms·

I think that comment goes in the right direction. It's kind of like relational programming but with focus on generation of data rather than querying it. While w

I think that comment goes in the right direction. It's kind of like relational programming but with focus on generation of data rather than querying it. While writing this kind of thing in SQL would be a headache, maybe a simpler language that would translate to SQL or maybe an extension to SQL would be a good idea.

Twisell 9y ago
So I guess is should order a metric ton of painkiller for my potentiel headache. "Relational programming using SQL with a focus on generation of data rather than querying it" is basically my job since 4-5 years. And i can't believe I'm the only one SQL guy out there doing that. It very effective for data manipulation once you get the correct mindset.
- rumcajz 9y ago
  Can you share your experience? I've tried to rewrite the testsuite example to SQL, but it's quite ugly. Also, I am not sure how to model the inheritance relationship: CREATE TEMP VIEW boxes AS SELECT 'box1' AS hostname, 'linux' AS os, 'x86-64' AS arch, 8 AS ram UNION SELECT 'box2', 'freebsd', 'arm', 16 UNION SELECT 'box3', 'windows', 'x86-64', 4 UNION SELECT 'box4', 'illumos', 'sparc', 4; CREATE TEMP VIEW compilers AS SELECT 'gcc' AS binary, '4.8.4' AS version, '-o' AS output_option UNION SELECT 'clang', '3.4.1', '-o' UNION SELECT 'msvc', '15.00.30729.01', '/Fe'; CREATE TEMP VIEW tests AS SELECT 'frobnicate' AS name, 'frbonicate.c' AS sources UNION SELECT 'loadtest', 'loadtest.c helpers.c' UNION SELECT 'end2end', 'end2end.c helpers.c'; CREATE TEMP view testsuite AS SELECT *, compilers.binary || ' ' || tests.sources || ' ' || compilers.output_option || ' ' || tests.name AS cmdline FROM boxes, compilers, tests WHERE NOT(tests.name = 'loadtest' AND boxes.ram < 8); SELECT * FROM testsuite;
  - Twisell 9y ago
    To make it less ugly, and assuming you are using PostgreSQL you could make the code a little more appealing rewriting it like that. (If you are not using postgres WITH will probably not work but VALUES instead of UNION should still work). Please also notice that "binary" is quoted because it's a reserved postgresql word. WITH boxes(hostname, os, arch, ram) AS (VALUES ('box1', 'linux', 'x86-64', 8), ('box2', 'freebsd', 'arm', 16), ('box3', 'windows', 'x86-64', 4), ('box4', 'illumos', 'sparc', 4) ), compilers("binary",version,output_option) AS (VALUES ('gcc', '4.8.4', '-o'), ('clang', '3.4.1', '-o'), ('msvc', '15.00.30729.01', '/Fe') ), tests(name,sources) AS (VALUES ('frobnicate' , 'frbonicate.c' ), ('loadtest', 'loadtest.c helpers.c'), ('end2end', 'end2end.c helpers.c') ) SELECT *, compilers.binary || ' ' || tests.sources || ' ' || compilers.output_option || ' ' || tests.name cmdline FROM boxes, compilers, tests WHERE NOT(tests.name = 'loadtest' AND boxes.ram < 8) Here is a fiddle to play around... http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/16606/0 http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1... But this is a stand alone query, SQL really become useful IMHO if data is stored inside the DB or gathered automatically by the db. For the inheritance I'm really clueless, it depend on what you try to achieve and i'm just a DB guy.
    - rumcajz 9y ago
      Thanks! That looks a bit nicer. As for the comment about stored data, that's actually the reason why I haven't considered SQL an option at first. The use case doesn't involve any stored data, so SQL intuitively doesn't sound like the right tool for the job. In the end, I think the cartesian paradigm can be expressed using SQL, but you have to twist your traditional SQL mindset to do that. You have to forget about tables etc.

Twisell 9y ago

So I guess is should order a metric ton of painkiller for my potentiel headache. "Relational programming using SQL with a focus on generation of data rather than querying it" is basically my job since 4-5 years. And i can't believe I'm the only one SQL guy out there doing that. It very effective for data manipulation once you get the correct mindset.

rumcajz 9y ago
Can you share your experience? I've tried to rewrite the testsuite example to SQL, but it's quite ugly. Also, I am not sure how to model the inheritance relationship: CREATE TEMP VIEW boxes AS SELECT 'box1' AS hostname, 'linux' AS os, 'x86-64' AS arch, 8 AS ram UNION SELECT 'box2', 'freebsd', 'arm', 16 UNION SELECT 'box3', 'windows', 'x86-64', 4 UNION SELECT 'box4', 'illumos', 'sparc', 4; CREATE TEMP VIEW compilers AS SELECT 'gcc' AS binary, '4.8.4' AS version, '-o' AS output_option UNION SELECT 'clang', '3.4.1', '-o' UNION SELECT 'msvc', '15.00.30729.01', '/Fe'; CREATE TEMP VIEW tests AS SELECT 'frobnicate' AS name, 'frbonicate.c' AS sources UNION SELECT 'loadtest', 'loadtest.c helpers.c' UNION SELECT 'end2end', 'end2end.c helpers.c'; CREATE TEMP view testsuite AS SELECT *, compilers.binary || ' ' || tests.sources || ' ' || compilers.output_option || ' ' || tests.name AS cmdline FROM boxes, compilers, tests WHERE NOT(tests.name = 'loadtest' AND boxes.ram < 8); SELECT * FROM testsuite;
- Twisell 9y ago
  To make it less ugly, and assuming you are using PostgreSQL you could make the code a little more appealing rewriting it like that. (If you are not using postgres WITH will probably not work but VALUES instead of UNION should still work). Please also notice that "binary" is quoted because it's a reserved postgresql word. WITH boxes(hostname, os, arch, ram) AS (VALUES ('box1', 'linux', 'x86-64', 8), ('box2', 'freebsd', 'arm', 16), ('box3', 'windows', 'x86-64', 4), ('box4', 'illumos', 'sparc', 4) ), compilers("binary",version,output_option) AS (VALUES ('gcc', '4.8.4', '-o'), ('clang', '3.4.1', '-o'), ('msvc', '15.00.30729.01', '/Fe') ), tests(name,sources) AS (VALUES ('frobnicate' , 'frbonicate.c' ), ('loadtest', 'loadtest.c helpers.c'), ('end2end', 'end2end.c helpers.c') ) SELECT *, compilers.binary || ' ' || tests.sources || ' ' || compilers.output_option || ' ' || tests.name cmdline FROM boxes, compilers, tests WHERE NOT(tests.name = 'loadtest' AND boxes.ram < 8) Here is a fiddle to play around... http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/16606/0 http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/1... But this is a stand alone query, SQL really become useful IMHO if data is stored inside the DB or gathered automatically by the db. For the inheritance I'm really clueless, it depend on what you try to achieve and i'm just a DB guy.
  - rumcajz 9y ago
    Thanks! That looks a bit nicer. As for the comment about stored data, that's actually the reason why I haven't considered SQL an option at first. The use case doesn't involve any stored data, so SQL intuitively doesn't sound like the right tool for the job. In the end, I think the cartesian paradigm can be expressed using SQL, but you have to twist your traditional SQL mindset to do that. You have to forget about tables etc.