Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
prisoner of waffles
May 8, 2007

Ah! well a-day! what evil looks
Had I from old and young!
Instead of the cross, the fishmech
About my neck was hung.

DELETE CASCADE posted:

did you know: the original postgres research project from 1986 had versioned tables! but they got rid of them around the time they added sql support i think :sigh:

thosgres

sincerely, thosgres

Adbot
ADBOT LOVES YOU

Finster Dexter
Oct 20, 2014

Beyond is Finster's mad vision of Earth transformed.
What did you do with a postgres table before sql support?

TheFluff
Dec 13, 2006

FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

Finster Dexter posted:

What did you do with a postgres table before sql support?

relational databases as a concept predate sql (of course - you don't write a dsl for querying databases if you don't have any databases to query), and the original postgres research project predates the sql standard as far as I know.

Janitor Prime
Jan 22, 2004

PC LOAD LETTER

What da fuck does that mean

Fun Shoe

Finster Dexter posted:

What did you do with a postgres table before sql support?

Write your own query dsl to issue searches against it?

prisoner of waffles
May 8, 2007

Ah! well a-day! what evil looks
Had I from old and young!
Instead of the cross, the fishmech
About my neck was hung.

Finster Dexter posted:

What did you do with a postgres table before sql support?

very carefully

Star War Sex Parrot
Oct 2, 2003

Finster Dexter posted:

What did you do with a postgres table before sql support?
let me tell you about Quel

DELETE CASCADE
Oct 25, 2017

i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

Finster Dexter posted:

What did you do with a postgres table before sql support?

you used postquel, a descendant of the ingres query language quel

this was before sql took over as the de facto query language for every rdbms

Star War Sex Parrot
Oct 2, 2003

DELETE CASCADE posted:

did you know: the original postgres research project from 1986 had versioned tables! but they got rid of them around the time they added sql support i think :sigh:
probably when it was still written in Lisp

Star War Sex Parrot
Oct 2, 2003

https://www.youtube.com/watch?v=poEfLYH9W2M&t=1734s

timestamped this to the relevant part if anyone wants an abridged history of databases

dbms's are fun

karms
Jan 22, 2006

by Nyc_Tattoo
Yam Slacker

cinci zoo sniper posted:

whatís worse in our case that not even developers of that system are able to reproduce the dynamic calculation for yesterday or any other day in the past - to paraphrase, the output is a checksum of a particular table as of the script execution, and since our guys still havenít unlearned to do destructive updates or deletes the whole thing blows up if you want truth for last Monday but 50 rows are missing since then

jowzers :chanpop:

redleader
Aug 18, 2005

Engage according to operational parameters

floatman posted:

Imagine building an app, where the final build of the app depends on the shittest compiler, also known as the SELECT statement.
Imagine building an app where extensions to features do not need to be done by writing any code at all. Imagine instead of writing code, you can generate and janitor 6 pages of jank SQL statements. Imagine having to keep these 6 pages of jank statements in your cvs repository.
Now imagine someone changes the schema, making your 6 pages of jank poo poo now a ticking time bomb that doesn't work because nobody realised that that is actually the source code that builds your software.

Imagine a world with no programming,
Just endless rerunning of poo poo SQL statements, trying to fix everything that breaks withing the ecosystem of modern programming languages and with the need to keep some lovely obscure db model in your head

hits close to home, ouch

Powerful Two-Hander
Mar 10, 2004

Mods please change my name to "Tooter Skeleton" TIA.


wait wait, in what sane world are people able to make changes to the db schema outside of the control/input of the app team?

you don't let your dbas actually change your database do you?

DELETE CASCADE
Oct 25, 2017

i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

Powerful Two-Hander posted:

wait wait, in what sane world are people able to make changes to the db schema outside of the control/input of the app team?

you don't let your dbas actually change your database do you?

uh i'm pretty sure the sane world is the exact opposite of this. the app doesn't own the database, and the data in it is far more important than whatever stupid hack the app team wants this sprint

besides, the app team devs are probably not sql design/optimization experts

Janitor Prime
Jan 22, 2004

PC LOAD LETTER

What da fuck does that mean

Fun Shoe
lol at inviting the dbas to our daily scrum

they need to stay in their cages and feed the query planner

floatman
Mar 17, 2009

prisoner of waffles posted:

hmmm, "SELECT statement is the shittiest compiler" is just misleading. query optimizer is your friend and what's more, ORMs have to generate select statements too, bud.

How is query optimiser my friend in this case when it's literally affecting the application features being generated? It's selecting software, not data.

We had an issue where the application crashed because a guy removed a file containing a class that was thought to be unused. Turns out the application selects records from a table. These records contain a varchar field, which is the class name, and another varchar field which contains an XML string which would be deserialised to form the constructor args for the class, which would be dynamically loaded via the language version of "eval" in the code base.
This class was never mentioned in the code base at all outside of its class definition; the only place where it was used was in a db trigger that would insert a row into aforementioned table.

Db trigger was never in the SQL migrations repository. Only in production.

redleader
Aug 18, 2005

Engage according to operational parameters
so is jooq good? it sounds ok and the maintainer sounds like the reasonable sort of crazy

e:

floatman posted:

How is query optimiser my friend in this case when it's literally affecting the application features being generated? It's selecting software, not data.

We had an issue where the application crashed because a guy removed a file containing a class that was thought to be unused. Turns out the application selects records from a table. These records contain a varchar field, which is the class name, and another varchar field which contains an XML string which would be deserialised to form the constructor args for the class, which would be dynamically loaded via the language version of "eval" in the code base.
This class was never mentioned in the code base at all outside of its class definition; the only place where it was used was in a db trigger that would insert a row into aforementioned table.

Db trigger was never in the SQL migrations repository. Only in production.

hell yeah, this is the poo poo i come to the coding horrors thread for

redleader fucked around with this message at 00:33 on Nov 2, 2018

CPColin
Sep 9, 2003

Big ol' smile.
jOOQ barely seems different from raw SQL strings. Like, you're just writing SQL with a different syntax. I guess the compile-time sanity checking is nice, but I don't see the appeal.

*misses the custom ORM he maintained at Experts Exchange*

DELETE CASCADE
Oct 25, 2017

i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

redleader posted:

so is jooq good? it sounds ok and the maintainer sounds like the reasonable sort of crazy

i haven't used jooq extensively but i like the middle ground it provides. i don't think the so-called "object-relational impedance mismatch" that orms are supposed to solve is really a thing. it is almost always pretty obvious how to map; oh, this field of my class x is a list of ys, so i'll have a y table with x.id as a foreign key, loving duh. oh you have inheritance, whatever, fill the unused columns with nulls. the actual "problem" people are encountering is that the language for working with your rdbms is sql, you have no other choice, that is the only query interface to the database. if you hate sql then you will be inclined to use an orm like hibernate that hides it all behind the scenes for you. this is bad because it just hides the translation to sql under its hood, so when the abstraction breaks and you need to debug it, you get to struggle with some auto-generated sql that is even more worthy of your hate than the hand-written sql you were trying to avoid. on the other hand, if you love sql, you will want to write your whole app in the database as much as possible. this is bad because you ultimately can't do that, you will need some java code or whatever for the frontend, and that code isn't sql, so now you are embedding the actual logic of your app as (probably dynamically constructed) sql strings in java. shaggar would tell you here to use stored procs and he's kinda right, but are you gonna put every single simple sql query into an sproc? probably not, so it'd be nice if your java code had a way to write some sql queries in a safer way than embedded strings of another language. jooq provides this without going completely overboard into xml mapping files and other orm bullshit

Captain Foo
May 11, 2004

we vibin'
we slidin'
we breathin'
we dyin'

DELETE CASCADE posted:

i haven't used jooq extensively but i like the middle ground it provides. i don't think the so-called "object-relational impedance mismatch" that orms are supposed to solve is really a thing. it is almost always pretty obvious how to map; oh, this field of my class x is a list of ys, so i'll have a y table with x.id as a foreign key, loving duh. oh you have inheritance, whatever, fill the unused columns with nulls. the actual "problem" people are encountering is that the language for working with your rdbms is sql, you have no other choice, that is the only query interface to the database. if you hate sql then you will be inclined to use an orm like hibernate that hides it all behind the scenes for you. this is bad because it just hides the translation to sql under its hood, so when the abstraction breaks and you need to debug it, you get to struggle with some auto-generated sql that is even more worthy of your hate than the hand-written sql you were trying to avoid. on the other hand, if you love sql, you will want to write your whole app in the database as much as possible. this is bad because you ultimately can't do that, you will need some java code or whatever for the frontend, and that code isn't sql, so now you are embedding the actual logic of your app as (probably dynamically constructed) sql strings in java. shaggar would tell you here to use stored procs and he's kinda right, but are you gonna put every single simple sql query into an sproc? probably not, so it'd be nice if your java code had a way to write some sql queries in a safer way than embedded strings of another language. jooq provides this without going completely overboard into xml mapping files and other orm bullshit

add some newlines jfc

DELETE CASCADE
Oct 25, 2017

i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003
but then i have to think about where the newlines go

Captain Foo
May 11, 2004

we vibin'
we slidin'
we breathin'
we dyin'

DELETE CASCADE posted:

but then i have to think about where the newlines go

bing em up your rear end

carry on then
Jul 10, 2010

by VideoGames

(and can't post for 11 years!)

DELETE CASCADE posted:

but then i have to think

first time for everything

Powerful Two-Hander
Mar 10, 2004

Mods please change my name to "Tooter Skeleton" TIA.


DELETE CASCADE posted:

uh i'm pretty sure the sane world is the exact opposite of this. the app doesn't own the database, and the data in it is far more important than whatever stupid hack the app team wants this sprint

besides, the app team devs are probably not sql design/optimization experts

i am perhaps severely institutionalised but as an organisation we have thousands of applications and their databases are all managed by those applications because it is wildly unfeasible to have adba on point that knows the specifics of that business area to do poo poo so app teams effectively control their own dbs

i work in the financial industry though so I'm maybe dealing with a slightly different scenario where we have lots of small single purpose applications rather than one big one. like I'm directly the owner of three databases and indirectly linked to maybe 10 more and that's in one small area of what we deal with

pokeyman
Nov 26, 2006

That elephant ate my entire platoon.

I canít read impedance mismatch without hearing impotence mismatch

DELETE CASCADE
Oct 25, 2017

i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

Powerful Two-Hander posted:

i am perhaps severely institutionalised but as an organisation we have thousands of applications and their databases are all managed by those applications because it is wildly unfeasible to have adba on point that knows the specifics of that business area to do poo poo so app teams effectively control their own dbs

i work in the financial industry though so I'm maybe dealing with a slightly different scenario where we have lots of small single purpose applications rather than one big one. like I'm directly the owner of three databases and indirectly linked to maybe 10 more and that's in one small area of what we deal with

you're probably not the only one, i mean technically if you're doing a strict "microservices architecture" then you get a database per service, right? my question in that case would be the same: how do you join across them? even if your "database" is really just a schema or set of tables in a centralized server or cluster, if your team's ids don't match any other team's because everything was developed in isolation, unless you have id mapping tables all over the place, how do you combine information from the backends of separate applications? do you just not ever need to do that?

floatman
Mar 17, 2009
The question becomes, "why are you trying to tightly couple databases and database implementation across microservices?"

Like, what are you trying to do that needs "secret" implementation details that are not publicly visible from a microservice?

What if the microservice is not even storing it's data in a format that has primary key IDs? (I.e. flat XML file stored in MongoDB)

It's kinda a design smell I guess.

Jabor
Jul 16, 2010

#1 Loser at SpaceChem
microservices are a design smell, yes

Powerful Two-Hander
Mar 10, 2004

Mods please change my name to "Tooter Skeleton" TIA.


DELETE CASCADE posted:

you're probably not the only one, i mean technically if you're doing a strict "microservices architecture" then you get a database per service, right? my question in that case would be the same: how do you join across them? even if your "database" is really just a schema or set of tables in a centralized server or cluster, if your team's ids don't match any other team's because everything was developed in isolation, unless you have id mapping tables all over the place, how do you combine information from the backends of separate applications? do you just not ever need to do that?

haha well

you would be astonished and/or shocked (or maybe not) about how large financial institutions handle reference data. up until recently there was nothing that really required you to have any sort of concept of a central data store and the approach most taken was "good enough as long as we aren't spending too much money on IT" and it's only recently (like 2016)that this poo poo got regulated as part of the whole "you better be drat sure who you're dealing with and who you owe money to" drive.

so what you have is loads of separate applications that may or may not have any common data points. where I work, we do because people were smart and /or lucky enough to figure that centralising key data was important 20 years ago, so there can be hundreds of systems that can all roughly agree on certain things as long as they're synced to the central store*.

a weird thing about this is that whenever someone talks to me about microservices I'm like 'uh, aren't those just services?' because the app architecture I inherited and built on was based around using service components to maintain consistency with the central source, add data to it and then distribute meta data out through separate channels rather than being a monolithic block.

I mean even then we easily have 50+ tables in our application database solely handling one specific dataset that sits alongside everything else. we use those shared keys to link our data to the central store so that everything links up, multiply that by a hundred different domain specific use cases thought and you have a lot more data than could ever be practically maintained in one place without going nuts.

Also needless to say, nobody is allowed to yolo scripts on the main data store, that has serious controls whereas individual applications have way more flexibility.


*and someone didn't gently caress up the data entry which happens at least 30% of the time because eit got offshored

Powerful Two-Hander fucked around with this message at 02:19 on Nov 2, 2018

akadajet
Sep 14, 2003

pokeyman posted:

I canít read impedance mismatch without hearing impotence mismatch

:flaccid: :(

pangstrom
Jan 25, 2003

Wedge Regret

Star War Sex Parrot posted:

https://www.youtube.com/watch?v=poEfLYH9W2M&t=1734s

timestamped this to the relevant part if anyone wants an abridged history of databases

dbms's are fun
good stuff for me, a terrible programmer. Was excited to see one of our major and terrible systems not even warrant a mention in the dozens and dozens of things he listed :)

Star War Sex Parrot
Oct 2, 2003

pangstrom posted:

good stuff for me, a terrible programmer. Was excited to see one of our major and terrible systems not even warrant a mention in the dozens and dozens of things he listed :)
Is it in https://dbdb.io ?

pangstrom
Jan 25, 2003

Wedge Regret
Maybe it's there under a different name. It's a 4d database
https://en.wikipedia.org/wiki/4th_Dimension_(software)

Nomnom Cookie
Aug 30, 2009



DELETE CASCADE posted:

you used postquel, a descendant of the ingres query language quel

this was before sql took over as the de facto query language for every rdbms

stonebraker is still salty about sql winning lol

prisoner of waffles
May 8, 2007

Ah! well a-day! what evil looks
Had I from old and young!
Instead of the cross, the fishmech
About my neck was hung.

floatman posted:

How is query optimiser my friend in this case when it's literally affecting the application features being generated? It's selecting software, not data.

We had an issue where the application crashed because a guy removed a file containing a class that was thought to be unused. Turns out the application selects records from a table. These records contain a varchar field, which is the class name, and another varchar field which contains an XML string which would be deserialised to form the constructor args for the class, which would be dynamically loaded via the language version of "eval" in the code base.
This class was never mentioned in the code base at all outside of its class definition; the only place where it was used was in a db trigger that would insert a row into aforementioned table.

Db trigger was never in the SQL migrations repository. Only in production.

sorry you got hit with "eval DB contents in your app" + failure to control your production DB + failure to fully investigate the set of values in prod. that sounds like a pain in the generative organs.

this has literally nothing to do with the query planner, it's a self-inflicted wound of hard-to-trace complexity.

your team didn't fully chase to ground the values that were going to go through that (designed by an idiot hellfucker) part of your app and so a "safe change" turned out to be an unsafe change.

yes, had an ORM been in use you would have had different problems.

floatman
Mar 17, 2009
"Designed by idiot hellfucker" has a nice ring to it.
Look in my dead souless eyes; I'm beyond caring about ORMs vs SPROCs or whatever.
I just want databases to store data, not serve as some bespoke handcrafted artisan way of extending a programming language.

prisoner of waffles
May 8, 2007

Ah! well a-day! what evil looks
Had I from old and young!
Instead of the cross, the fishmech
About my neck was hung.

floatman posted:

"Designed by idiot hellfucker" has a nice ring to it.
Look in my dead souless eyes; I'm beyond caring about ORMs vs SPROCs or whatever.
I just want databases to store data, not serve as some bespoke handcrafted artisan way of extending a programming language.

wilkommen. you've reached what passes for enlightenment on this particular topic

the talent deficit
Dec 20, 2003

self-deprecation is a very british trait, and problems can arise when the british attempt to do so with a foreign culture





DELETE CASCADE posted:

you're probably not the only one, i mean technically if you're doing a strict "microservices architecture" then you get a database per service, right? my question in that case would be the same: how do you join across them? even if your "database" is really just a schema or set of tables in a centralized server or cluster, if your team's ids don't match any other team's because everything was developed in isolation, unless you have id mapping tables all over the place, how do you combine information from the backends of separate applications? do you just not ever need to do that?

in practice you just join the application layer and make sure you handle referential integrity problems at that level. like if someone wants to see all `tags` for a `post` or something you shouldn't just retrieve the tags via the post id, you should also check that the post id is still valid. you can't do this transactionally but it rarely matters with this kind of thing. if you do need transaction and strict referential integrity, just put the related things in the same db and use the same microservice to manage them

redleader
Aug 18, 2005

Engage according to operational parameters

pangstrom posted:

a 4d database

:2bong:

distortion park
Apr 25, 2011


DELETE CASCADE posted:

uh i'm pretty sure the sane world is the exact opposite of this. the app doesn't own the database, and the data in it is far more important than whatever stupid hack the app team wants this sprint

besides, the app team devs are probably not sql design/optimization experts

Yeah letting the applications control the data structures is a surefire way to end up with a lovely db, loads of duplication and misrecs everywhere

Adbot
ADBOT LOVES YOU

distortion park
Apr 25, 2011


If your app needs a different data shape you can write a sproc to give it to you

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply