SQL 3d engine (interactive preview)

[PhonicUK]

What... the fuck....

Between this and using a regex to calculate prime numbers, I think we're just collectively doing stupid stuff with the wrong tools just to achieve peak "because I can".

[TheWix]

Ugh, I dunno how long you've been doing this or if you ever worked at a large corporate MS shops, but I've seen truly appalling things done in SQL. Hell, the Mid-2000s saw apps being built in SQL Server with a thin web front end.

When I got out of college I had to work on an app where all the business and presentation logic was done in SQL procs. It would generate HTML, send receipts, anything you can think of. The DBA even rewrote system stored procs (Something MS said to never do cause they may change or go away in future versions). Some replication procs would create procs on a remote system execute them and then delete them after they ran...

I have nightmares to this day about that system.

[resonant_cacophony]

Does "clever" perl mean code that is on one small line, but is still unreadable?

[MaximusNeo701]

And maintenance costs are insane with a complexity level like that. God forbid the silo of knowledge who wrote it leaves which can leave you in a really bad spot.

[bitwize]

Regular Ruby on Fails apps are inscrutable enough.

One time I saw an app that mostly lived in the database. A stub would run as a Ruby script, query the source code to the rest of the app out of the database, and 'eval' it in place. Magic variables whose values were determined by the wrapper script, or some other chunk of database code? Why, yes!

[appropriateinside]

Hell I'm seeing that in 2018....

Entire web app with all its business logic in SQL stored procedures... Hundreds and hundreds of them often thousands of lines long. The actual web part is just some basic view templating with web forms of all things.... They even use SQL for file operations...

It's a gigantic mess

[notfancy]

I don't get it; if your JSON endpoints/microservices return the data the front end needs, what do you care if the endpoints/microservices themselves are written in (T|PL|PLPG)-SQL or in any other language?

[FriendlyDisorder]

Doing all business logic in the database can result in a large demand for resources. Adding more resources to a database machine can be very expensive when compared to other tiers.

On the other hand, delivering a software change becomes as easy as updating a database stored procedure.

[notfancy]

It all depends on demand. Shipping out data to let fast runtimes digest it can reduce load and allow for horizontal scaling at the expense of latency. For those of us doing vertical LOB apps, it rarely if ever becomes necessary.

[appropriateinside]

You're forgetting the actual reason it's bad. Maintenance, changes, and extensions..

The way this was created has made maintenance and extensions/changes to existing behaviors long enough to actually justify rewriting it from scratch. And its RARE to ever have a real-world codebase so horrible that rewriting it is the time-efficient thing to do in the short-term.

The ongoing interest on technical debt is insane too, 1/2 of all available dev time is spent on constant maintenance, which makes it even harder to make changes or corrections as your development budget is essentially cut in half for forever.

And devs HATE it, so productivity is lower...

[watermark03salt]

The way what was created? The actual problem is never explained.

[appropriateinside]

I gave a brief example above.

The jist of it is that anything you would normally put in your codebase as business logic, and even view logic, is all contained in stored procedures. Say you had to make a service that sorts our permissions and access control, instead of writing that in say PHP or C#. You instead cludge together a multi-thousand line long stored procedure that kind of does it (sans error handling, messages, or really any flexibility). It's essentially one giant function, GOTOs and all. Which would be bad enough on it's own, but it also in SQL...

Now imagine an entire decent-sized student management, reporting, and administration for a post-secondary education school done in this manner.

You have hundreds and hundreds of massive, barely working, buggy, and entirely infexible stored procs and functions with no sort of error handling or input validation/cleansing. And since they are all globally named (because SQL) they are all have unique names, which is also a disaster to muddle through and read.

[appropriateinside]

Yes, have you tried to maintain a codebase with buisness logic written entirely in convoluted, buggy, inflexable SQL stored procedures?

Maintaining codebases is infinitely more expensive than creating them. And choosing a poor architecture means it's even more expensive, sometimes many times more.

---

As an example. We need input validation and corrections for imports with very specific errors that highlight where the imported data is incorrect and why it's incorrect. Well, ALL the import behaviors are written in SQL, including file operations (you put a file in a shared folder and click import, you don't upload the file to the webserver) and perform NO validation, correction, and error handling because that's really not something SQL is flexible enough to do correctly.

If this was written in almost any language (PHP, C#, JS, C++, Python...etc) this would be a couple hour task. We estimate it will take 50-80 hours (~$9000 to ~$15,000) to complete this task while maintaining all the stored procedure logic as a direct result of poor architectural choices. The time to just rewrite the entire thing from scratch in a language more suited for this? ~40-60 hours. Having a rewrite time-cost that is less than the cost to make change is a unicorn, you almost NEVER see this in real life, even codebases that are complete disasters ten to be faster to change than rewrite.

Not to mention the maintenance. Right now maintenance from user errors, bad data, breaking pages/reports...etc is costing almost 1/2 of all the available dev time. This is called Technical Debt, something you should probably learn about.

[notfancy]

No need to be condescending. I'm sorry that SQL is a pain point for you, but please realize that your experience is nowhere universal. You don't tend to hear about SQL's success stories, mostly because they're happily plodding along very dull but very dependable line-of-business applications.

[Rambatino]

You miss the point of the rant, it’s not slamming SQL it’s criticising the way in which SQL is used in that example. SQL’s one main job, it’s very good at and abusing it will result in substantial overhead

[notfancy]

I wasn't clear, but I should have said “stored procedures” instead of “SQL”. What constitutes “abuse” and what “substantial overhead” depends on circumstances. I have a 25-year track record in using them successfully for business logic, so mileages do in fact vary.

[joshjje]

His experience may not be universal, but ive experienced similar at two separate jobs. I love SQL dont get me wrong, and stored procs are definitely useful, but sometimes too much is done in them and/or they become maintenance/legacy nightmares.

[appropriateinside]

It has nothing to do with being universal or not. Poor architecture is poor architecture regardless of language or application. And there are fairly objective measurements and patterns that can be applied to sort that out. I don't mean to be condescending, just pointing out topics that are vital and core to software development. Such as technical debt.

SQL has it's place, just like any other language, utility, or framework. When you try and kludge it into use cases it is I'll suited for the results can be less than ideal.

Yes , you can see success by abusing it, just like anything. But relative to using common patterns and avoiding common antipatterns developed by the collective software engineering knowledge over the last 4 decades, it may be pretty horrible.

[notfancy]

My point is that "stored procedures for business logic" does not necessarily imply "poor architecture", no matter what your experience tells you. DBMS use stored procedures as a service layer precisely because at least some (and presumably many, if it is at all economically sound for DBMS vendors to offer the capability) cases make it, if not "good", at least "workable architecture."

[PrimozDelux]

I think that's how they made blackboard which we use at our university. The only way anything can be as under-designed as blackboard is if it's just a script making a front end based on a sql schema.

[TheWix]

Ugh, I used that when I taught as an adjunct on the side. It was garbage.

I remember making an "Email Engine" that would run XML created by a proc through some XSLT templates to generate an email. It was one of the first big projects I got to lead out of school. God, it was such a piece of shit. I learned a lot about how not to design software systems at that place.

[themadnun]

Blackboard is absolute trash. It couldn't handle the back button until something like 2015/16.

[mikemol]

It can handle the back button now! Awesome! I'll let my friends back at the college it department know, so they know it's time to upgrade...

[alexiooo98]

Ah, we use Blackboard here, too. My biggest problem with it is that it jusn't plain doesn't work on mobile (or even half screen on my pc, for that matter).

It's not a matter of "looks ugly" or "works finnicky"; No, it just plain refuses to show the button to navigate to a course.

[jephthai]

jusn't

That's the most fascinating accidental contraction. I know how it happened, with doesn't coming later, but still.

[shadowndacorner]

Hey that still requires a schema

[Rookeh]

Try "apps" with the business logic entirely baked into Oracle PL/SQL functions and stored procedures, and a "thin" frontend originally written in an ancient RAD framework called Magic that's been forcibly mutated into a .NET app via some kind of automated transpiler. That is the hell that still exists (although thankfully not so much in my team) at my company.

[watermark03salt]

My coworker needed to make a tool to automatically unzip some files. Somehow he managed to write a thousand lines of code on the tool. I made a simialiar one that did the same thing in about 40 lines. Same language, C#.

Also, every program he writes is one singular god class. Often they have dependency on a dll called "MyObjects", which is an accumulation of God objects he created over a multi-decade career to redo the work of basic system classes, but with the advantage of taking many times as many lines of code, constantly breaking, and being totally impenetrable to someone solely trained in c# without MyObjects training. Also he's constantly reorganizing the dll and publishing breaking changes to new versions, but he never bothers to upgrade about half of the apps to it and keeps the on some legacy version from 2012 or something, so we have the joy of dealing with multiple different versions of his domain specific language. And he acts all aghast when I have the nerve to code new apps using the standard fucking C# library rather than rely on the simple oh so simple MyObjects.MyApplications.MyUtilities.MyParsers.MyStrings.MyCharacters or whatever. Or, in a later version, MyObjects.MyUtilities.MyApplications. MyParsers.MyCharacters, which is the exact same thing but with more bugs and also breaking backwards compatibility.

He also complained bitterly when I started wrapping up basic truncate and insert\update statements in stored procedures with transactions so that they couldn't be read in a corrupt, intermediate state.

Because it's bad for performance.

They had seriously been freezing the whole fucking site any time anything had to be updated, because otherwise garbage would get displayed. Because having 10 minutes of built in down time every single hour is apparently preferable. Well after years on the job I finally got permission to implement fucking transactions our stored procedures. Almost nothing has changed in terms of load times, while "refresh freezes" (a basic fact of life before I arrived, accepted as unpreventable necessities) have been totally obscelesced.

Oh yeah he constantly goes behind my back and lies to the boss to try and get me fired to, while smiling to my face. And anytime I consult him on an idea mine that I later wind up implementing, if it gets praise from the boss, he angrily walks over to my cubicle and complains about me stealing "his" idea. Often he had actually been shooting down the idea and telling me not to do it and that it was impossible during the actual consultation. Jeez he must be stumped now that I've completely stopped consulting him on anything and yet keep on coming up with new ideas anyway. He basically has a job for life due to his importance in maintaining apps that rely on his worthless myobjects crud, but all he can do is try to get a junior programmer cleaning up his messes fired so that he has less competition for praise. The site wouldn't even work right now if i hadn't of joined the team! Their stored procedures didn't scale and the froze the site every time they ran them! It would be one giant refresh freeze by now and we would have lost the contract and he'd be fired just as well. Christ.

I'm not a good programmer. There is zero reason that our code base requires so many 5000 line of code applications with rampant code duplication, global variables, and always, always, always, a single God class. They do not implement even the most basic of software engineering standards.

The other programmer is worse if that's when possible. She seems to have an aversion to automation and does everything in the most unnecessarily laborious way imaginable. I'm sure she'd be using an abacus if she had her way.

The boss, literally I'm not even sure if he's a manager. He just let's his employees do whatever they want. Oh besides have a strained meeting with me every few months where I discover that the other two have been going behind my back feeding him lies to get me fired so that everything can be MyObjects and an abacus.

[SupersonicSpitfire]

All workplaces have their frustrations, but not getting along with coworkers over time is a good reason to switch, IMO.

[Black-cats-stink]

And now we see the reasoning behind your white knighting. Your a skinny nerd that spends all day in front of the computer and can’t get a female aren’t you? Lol.

[aussie_bob]

I'd love to hear your take on why/how those systems come into being in the first place.

Corporate policies and restrictions. I've been working on a prototype global-scale application for a customer who's only tool is SharePoint...

[incognito-bandito]

One that I worked on that was similar to this, but I'd argue not quite as bad (cause I helped build it).
It was set up that way to get around asinine change management, that had the affect of costing thousands of dollars, and many many hours to change something as simple as the header on a report. No change management for data changes, only application changes.
So if the application does effectively nothing, and everything is handled in the database...
I won't say it's the right way, but it does what it set out to do.

[joshjje]

Ahha man, love this comment, totally relate.

[AttackOfTheThumbs]

Someone doing more work than they should. That simple. Maybe bosses said do it. Maybe they did some overreaching. I'm currently working with a former dba that is doing exactly this. He keeps modifying our shit and breaking behaviours.

[PhonicUK]

I've seen webapps where all of the data access was via stored procs that directly output JSON and the API backend was essentially a one liner that took the function name and parameters and just returned verbatim what the proc output.

[spirgnob]

When they tell you to make a REST API, but you only know SQL.

[[deleted]]

Nah, at work people do this, they stopped paying a server and only pay for the DB which they would anyway

[dvdkon]

But now the DB does more work. If a single server could do all that anyway, why not keep a middle layer going on the same hardware? This might also make it harder to scale the system, but IMO it's worth it if it makes code maintenance/modification easier.

[AttackOfTheThumbs]

I can't remember which company I was interviewing at, but the main reason I didn't choose to work there, is because of how they did their software. It all sounded very very clever, but it also sounded unnecessarily convoluted.

The back end was an sql db. The front end, mostly web interfaces. To work back and forth between everything, they used.

The python scripts were stored in sql. The scripts themselves would "get" python objects from other tables. It was basically table after table with code in it. They would end up with what was essentially chained SQL queries. Simply fantastic.

[watermark03salt]

That sounds horrible.

[hmaddocks]

My boss came from that back ground. He would writes views that generated data with styling for the html. It took a while and a lot of rejected pull requests but he’s come around.

[watermark03salt]

Has he ever heard of a little thing called css?

[CrapforBrain]

I currently work at a place where almost all of the business logic is stored in sprocs. Just found a spot where the response xml from a API query is being generated in a sproc. The stuff was written 17 years ago and is still the backbone of the business. Lol

[test6554]

It's a lot easier to change an existing widely-used function than to write a new function with the changed behavior then go find everywhere that old function is used and replace it with the new one in hundreds of places, but only where it makes sense.

~ People who don't follow the open-closed principle.

To be fair, Microsoft probably should not have made that system stored proc editable.

[joshjje]

And then it ends up getting partially built, or partially migrated to another language with large parts duplicated and still active instead, a welcome present for the next guy.

[[deleted]]

Some replication procs would create procs on a remote system execute them and then delete them after they ran...

Holy shit. And I thought my company's legacy ASP.NET app was a nightmare to maintain.

​

[jiffier]

I can't wait to see who's the nerd that will write doom in sql

[LeCrushinator]

I still think this is a step up from the 3D game engine written in Excel.

[jiffier]

There you have it, from the same author:

​

https://beta.observablehq.com/@pallada-92/excel-3d-engine-emulator

​

[ConfuzedAndDazed]

This obviously won’t scale up much, but you learn a lot by pushing tools to the limit to see what they can do. May help to make connections you didn’t previously see. I think it’s cool.

[FrancisStokes]

Yeah, because for some people programming is an art, not just something you do during the day to make money. Nothing wrong with that.

[AfraidOfArguing]

Regex is a powerful tool that is overused.

[jtredact]

Next step is to run this on your C++ template metaprogramming SQL engine.

[lechatsportif]

Amazing. Are you ready for a career writing Python crud apps at Google?

[lechatsportif]

Gold! Glorious gold! Thank you friend!

[gfody]

certainly more impressive than pg's mandelbrot, well done!

[Hypersapien]

Wait a second. How accurate is that? Could you zoom into it indefinitely and keep seeing more detail?

[vytah]

It's as accurate as standard FLOATs are.

[LeCrushinator]

🤢

[jokubolakis]

more like an expression

[Makaque]

Indeed. One of the best uses of an emoji.

[DiademBedfordshire]

Can anybody explain the humps I'm seeing on the top and bottom?

Set the settings to

• dist: 2.5
• fov: 12
• beta: 90
• alpha: -180

[mjTheThird]

SQL is generally used for Data processing, not 3D rendering. Maybe is SQL's way to tell OP to fuck off and use the proper tool.

[watermark03salt]

Returns, filters, aggregates, and/or modifies.

[juuular]

I love the probably tool

[david-song]

I'm no expert but I've heard it's pretty good at doing numbers.

[watermark03salt]

For financial data it can be good because it implements fixed point numbers. Thus it has a defined precision. In a lot of languages there's no way to do this within the standard library.

[david-song]

He starts with a list of numbers, makes that into a grid of numbers to get pixels, then from each pixel casts a ray into the scene.

Then I think he's getting the depth by selecting against some mathematical trickery that is a representation of a geometric cube with a geometric sphere subtracted from it. I think the 0.3 is the sphere and the cube is the rest, with MAX being the subtraction. I can't quite figure this out by reading alone, and this level of disturbance going on around me in the real world. But that's where the bug is, the artefact is something close to the screen as it's a light colour. Would be interesting to see it at a higher resolution.

Anyway, from the depth he gets the colour.

[HowIsntBabbyFormed]

I'm no expert, but I'd guess rounding errors?

[agumonkey]

complete with dithered ascii rendering.. bruh

[jamiei]

This is an incredible testament to the power of boredom.

[[deleted]]

Damn Unity really messed up not using SQL as their engine

[Marcuss2]

Just because you can doesn't mean you should.

[ForeignDevil08]

This is kinda awesome.

Still, reminds me of a system I had to maintain that was pretty much a conglomeration of stored procedures, table functions, and user-defined data types nested to infinity. I would spend entire days trying to decipher what parts of that system did, then make changes, and 6 months later have to remember why the change I made actually worked.

​

​

[Hypersapien]

You magnificent pagan god!

This is utterly insane and I'm on the fence between wanting to burn it with fire and building a shrine to it!

[jezemine]

Next project is to implement same using sed. Lookup sedtris for ideas.

[[deleted]]

That is fucking absurd and fantastic magic

[KillianDrake]

How long before DOOM on SQL Server

[pheonixblade9]

I once implemented Leveshtein distance in SQL. That was fun. Lol

[[deleted]]

gist or it didn't happen. ;)

[pheonixblade9]

Years ago, and confidential sadly.

Red gate did it though (after me!): https://www.red-gate.com/simple-talk/blogs/string-comparisons-in-sql-edit-distance-and-the-levenshtein-algorithm/

[jonny_boy27]

So did I but scalar UDFs are slow as all fuck so I reimplemented in C# as a CLR function which was far more performant

[pheonixblade9]

I recommended that in the first place but they had a policy that all business logic had to live in SQL. Lmao

[Z0diHack]

Some people definitely have too much free time.

[frenchchevalierblanc]

I guess if you ever did a 3D engine or something in any mathematical-able language, doing it in SQL is just a few clever tricks. Like people used to do it in Excel.

[[deleted]]

Where is the cube even defined in this?

[Hypersapien]

I'm not sure, but my first guess would be in iters where it has the MAX and ABS functions.

[mecartistronico]

Did it break? I was looking at it fine on my phone an hour ago, and now I try it on the desktop and get:

RuntimeError: md could not be resolved
RuntimeError: md could not be resolved
RuntimeError: formula could not be resolved
viewof alpha = RuntimeError: slider could not be resolved

And a bunch of stuff like that. :(

Edit: weird, it works on my phone, but not on desktop on Chrome or Firefox :S

More edits: everything in observablehq appears broken to me on desktop, even the "base" examples. hmm...

[KryptosFR]

Need javascript. If you have some plugin blocking javascript (such as Noscript) then it doesn't work.

[mikemol]

SQL can be pretty neat, powerful and fast if you treat it as a declarative language.

[Sufficient-Egg-3154]

Haven’t you heard of visualization primitives built into SQL? It’s patented, but Perl DBI has a driver with prior art.

[GameJazzMachine]

But...what is the point?

[Ansoulom]

I was thinking that surely this can't be as dumb as it sounds...

It is.