The OFFICIAL programming thread
-
@rote7 said in The OFFICIAL programming thread:
The Case Against SQL - Slashdot
Long-time Slashdot reader RoccamOccam shares "an interesting take on SQL and its issues from Jamie Brandon (who describes himself as an independent researcher who's built database engines, query planners, compilers, developer tools and interfaces). It's title? "Against SQL." The relational model...
Interesting article, I didn’t know that
“The SQL spec (part 2 = 1732) pages is more than twice the length of the Javascript 2021 spec (879 pages), almost matches the C++ 2020 spec (1853) pages and contains 411 occurrences of ‘implementation-defined’, occurrences which include type inference and error propagation.”
Sweet jesus, double the length of the JS specs…
One SQL problem that I couldn’t solve so far is finding a good way to use the output of a single column query (multiple rows, result count must be flexible) as headers and/or fields for a subsequent query.
I think you need to do it as a stored procedure if I remember right and using the PIVOT function. Basically instead of defining the static columns as in the base function, you dynamically insert them in the stored procedure. If you are doing this as part of another code project, you could do the same by just assembling the text of the SQL script to sent dynamically (e.g. “SELECT “& [column name varIable]&” FROM TABLE_NAME;”
-
This thread:
"Let's give it a week! Still a disaster? Let's give it another week…" -Tazz
-
Stored procedures are one possible solution. But, and this is a big but, they are DB dependent and I am looking for a way to achieve this with as little as possible DB specific SQL dialect.
As for the client generated SQL, that’s the current solution as a part of a homegrown ORM. I am just curious if it can be done purely in the DB as a way to replace a bunch of DB specific logic during the SQL generation in that ORM.
/* insert snappy comment here */
-
I’d snap one off in her
-
@rote7 said in The OFFICIAL programming thread:
Stored procedures are one possible solution. But, and this is a big but, they are DB dependent and I am looking for a way to achieve this with as little as possible DB specific SQL dialect.
As for the client generated SQL, that’s the current solution as a part of a homegrown ORM. I am just curious if it can be done purely in the DB as a way to replace a bunch of DB specific logic during the SQL generation in that ORM.
/* insert snappy comment here */
@rote7
Yeah, I couldn’t find a way other than a stored procedure in Oracle. The function requires you to define the columns in the SQL so it’s static. Nothing in many searches I did changed that, though the procedure thing was suggested. Seems like a stupid design since pivoting often requires dynamic results. -
@gators1 She bumped her chin on something.
-
@gustaf said in The OFFICIAL programming thread:
This thread:
This is what I was waiting for, the caterwauls of the computer illiterati! SUCCESS ROTE SUCCESS!
-
@rote7 said in The OFFICIAL programming thread:
Stored procedures are one possible solution. But, and this is a big but, they are DB dependent and I am looking for a way to achieve this with as little as possible DB specific SQL dialect.
As for the client generated SQL, that’s the current solution as a part of a homegrown ORM. I am just curious if it can be done purely in the DB as a way to replace a bunch of DB specific logic during the SQL generation in that ORM.
Suspected as much.
Do Dark Magic, pay with your soul. You know the rules.
-
SELL! SELL! SELL!
The end of open source?
Several weeks ago, the Linux community was rocked by the disturbing news that University of Minnesota researchers had developed (but, as it turned out, not fully executed) a method for introducing what they called "hypocrite commits" to the Linux kernel -- the idea being to distribute hard-to...
-
@lithu said in The OFFICIAL programming thread:
SELL! SELL! SELL!
Several weeks ago, the Linux community was rocked by the disturbing news that University of Minnesota researchers had developed (but, as it turned out, not fully executed) a method for introducing what they called "hypocrite commits" to the Linux kernel -- the idea being to distribute hard-to...
Yahoo news.
-
Mildly interesting article but the title is possibly the worst click bait I’ve ever seen.
-
@hog said in The OFFICIAL programming thread:
Mildly interesting article but the title is possibly the worst click bait I’ve ever seen.
Here’s a worser one…
-
@gators1 probably let out a six minute long fart
-
@rote7 said in The OFFICIAL programming thread:
Stored procedures are one possible solution. But, and this is a big but, they are DB dependent and I am looking for a way to achieve this with as little as possible DB specific SQL dialect.
As for the client generated SQL, that’s the current solution as a part of a homegrown ORM. I am just curious if it can be done purely in the DB as a way to replace a bunch of DB specific logic during the SQL generation in that ORM.
/* insert snappy comment here */
This is the kind of amazing posting that has won this website so many awards!
-
Programming with pictures
Back in the late 90’s I demonstrated a graphical workflow tool I was using to a colleague and he dubbed it “programming with pictures”. Later, similar tools started appearing in most 3D applications like Blender and Godot. Except there they call them “node editors”.
Anyway, just now I wanted to temporarily backdrop something I’m working on in Blender with a tiled floor and I thought, oh that would be a good case for using shader nodes. So I started building it and got this far before I had a problem:
The problem was that I needed a logical XOR operation on the output of the Column and Row. ie, if either the column or the row is odd (edit: but only one of them), then use the alternate color. But, surprisingly to me, I found out that Blender doesn’t have a node for boolean logic. I could have written some python code to do it but that seemed like cheating so I thought about for a bit before I realized I could add the row and column remainder and then do another modulo operation on it:
Fucking Bingo! My math skills are so rudimentary that I felt like Plato after I reasoned out that solution.
-
Flow graphs are pretty common in the data world as well. Our data movement tool, informatica, uses them at a couple levels to define the mapping of one column to another column and the process of when to run mappings in sequence. It is funny though how such tools often complicate something you could do easily in code. We ended up using a ton of SQL overrides in the ETL because we couldn’t be arsed to spell it out with all the boxes and lines, or in some cases you couldn’t even do it the way we wanted it.
I have something similar to your OR problem quite often actually. Our report creation tool, which is basically a SQL GUI can’t easily do the order of operations in the equivalent of the WHERE clause. So I get tripped up when I want to do something like WHERE (A=1 AND B=0) OR (A=0 AND B=1). It has AND and OR but to create the parentheses I have to create new filter objects with the AND conditions, which is a different GUI, and then use those objects in the report definition. It’s not a huge inconvenience as you can create them pretty quickly, but it does tend to clutter up the application when you have to create a bunch of these and you have duplicates over time.
-
@gators1 said in The OFFICIAL programming thread:
Flow graphs are pretty common in the data world as well. Our data movement tool, informatica, uses them at a couple levels to define the mapping of one column to another column and the process of when to run mappings in sequence. It is funny though how such tools often complicate something you could do easily in code. We ended up using a ton of SQL overrides in the ETL because we couldn’t be arsed to spell it out with all the boxes and lines, or in some cases you couldn’t even do it the way we wanted it.
I have something similar to your OR problem quite often actually. Our report creation tool, which is basically a SQL GUI can’t easily do the order of operations in the equivalent of the WHERE clause. So I get tripped up when I want to do something like WHERE (A=1 AND B=0) OR (A=0 AND B=1). It has AND and OR but to create the parentheses I have to create new filter objects with the AND conditions, which is a different GUI, and then use those objects in the report definition. It’s not a huge inconvenience as you can create them pretty quickly, but it does tend to clutter up the application when you have to create a bunch of these and you have duplicates over time.
The ugly part is under the covers you are probably adding 1000s of junky operations/instructions per decision/branch cycle where if they had the options you need in, your stuff would run faster.
In that case they are looking to save devbucks and making you pay for their profit margin by buying more computer.
https://i.imgur.com/hX2CMMZ.jpg
Never go full Lithu-
TwainNo editing is gonna save you now-
Wingmannhttp://s3.amazonaws.com/rrpa_photos/72217/DSC_2528.JPG
http://s3.amazonaws.com/rrpa_photos/20416/PTOB 101_resize.jpg
-
@kilemall said in The OFFICIAL programming thread:
@gators1 said in The OFFICIAL programming thread:
Flow graphs are pretty common in the data world as well. Our data movement tool, informatica, uses them at a couple levels to define the mapping of one column to another column and the process of when to run mappings in sequence. It is funny though how such tools often complicate something you could do easily in code. We ended up using a ton of SQL overrides in the ETL because we couldn’t be arsed to spell it out with all the boxes and lines, or in some cases you couldn’t even do it the way we wanted it.
I have something similar to your OR problem quite often actually. Our report creation tool, which is basically a SQL GUI can’t easily do the order of operations in the equivalent of the WHERE clause. So I get tripped up when I want to do something like WHERE (A=1 AND B=0) OR (A=0 AND B=1). It has AND and OR but to create the parentheses I have to create new filter objects with the AND conditions, which is a different GUI, and then use those objects in the report definition. It’s not a huge inconvenience as you can create them pretty quickly, but it does tend to clutter up the application when you have to create a bunch of these and you have duplicates over time.
The ugly part is under the covers you are probably adding 1000s of junky operations/instructions per decision/branch cycle where if they had the options you need in, your stuff would run faster.
In that case they are looking to save devbucks and making you pay for their profit margin by buying more computer.
In some cases yeah, but in ours I don’t think so. Data movement is all about efficiency so our software actually has some optimizations over straight SQL that speed it up for common operations. Some super genius data engineer probably could get it to run faster with straight code and some caching functions or whatever, but that adds a shitload of development/ops overhead. Also devbucks are generally more important than having to buy hardware in the long run. If you had a hand code an ETL in SQL vs. using a pretty drag and drop interface, the development investment and operating costs would be a huge difference.
-
@gators1 said in The OFFICIAL programming thread:
Flow graphs are pretty common in the data world as well. Our data movement tool, informatica, uses them at a couple levels to define the mapping of one column to another column and the process of when to run mappings in sequence. It is funny though how such tools often complicate something you could do easily in code. We ended up using a ton of SQL overrides in the ETL because we couldn’t be arsed to spell it out with all the boxes and lines, or in some cases you couldn’t even do it the way we wanted it.
I have something similar to your OR problem quite often actually. Our report creation tool, which is basically a SQL GUI can’t easily do the order of operations in the equivalent of the WHERE clause. So I get tripped up when I want to do something like WHERE (A=1 AND B=0) OR (A=0 AND B=1). It has AND and OR but to create the parentheses I have to create new filter objects with the AND conditions, which is a different GUI, and then use those objects in the report definition. It’s not a huge inconvenience as you can create them pretty quickly, but it does tend to clutter up the application when you have to create a bunch of these and you have duplicates over time.
… And it can’t just do where A!=B?
-
@tigger said in The OFFICIAL programming thread:
@gators1 said in The OFFICIAL programming thread:
Flow graphs are pretty common in the data world as well. Our data movement tool, informatica, uses them at a couple levels to define the mapping of one column to another column and the process of when to run mappings in sequence. It is funny though how such tools often complicate something you could do easily in code. We ended up using a ton of SQL overrides in the ETL because we couldn’t be arsed to spell it out with all the boxes and lines, or in some cases you couldn’t even do it the way we wanted it.
I have something similar to your OR problem quite often actually. Our report creation tool, which is basically a SQL GUI can’t easily do the order of operations in the equivalent of the WHERE clause. So I get tripped up when I want to do something like WHERE (A=1 AND B=0) OR (A=0 AND B=1). It has AND and OR but to create the parentheses I have to create new filter objects with the AND conditions, which is a different GUI, and then use those objects in the report definition. It’s not a huge inconvenience as you can create them pretty quickly, but it does tend to clutter up the application when you have to create a bunch of these and you have duplicates over time.
… And it can’t just do where A!=B?
No because A could be 2 and B could be 3. The point is that in general you have to do the operations in parentheses in a different way than the rest of the where clause in the GUI. So it makes it harder to do complex where clauses than with just coding.
