171–180 of 322 posts
We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage of your migration scripts this way too.
Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
Excellent for checking delete queries before running them.
Earlier quoted context omitted.
>This is exactly what SQL is. There are exceptions, but they're exceptions. no, i meant the query is literally this: GIVE ME ALL THE COST OF THE EMPLOYEES or if we want to formalize more: ALL COST OF ALL EMPLOYEES the point here that in a declarative mindset EMPLOYEE is a word that SQL server understands, like HTML understand paragraph or table with rows and cells >I don't think you have a correct model of SQL. SQL i…
You do not understand SQL. HTML and SQL are essentially unrelated. It would be to your benefit to spend some time reading and learning on these topics.
Earlier quoted context omitted.
>This is exactly what SQL is. There are exceptions, but they're exceptions. no, i meant the query is literally this: GIVE ME ALL THE COST OF THE EMPLOYEES or if we want to formalize more: ALL COST OF ALL EMPLOYEES the point here that in a declarative mindset EMPLOYEE is a word that SQL server understands, like HTML understand paragraph or table with rows and cells >I don't think you have a correct model of SQL. SQL i…
it is possible to reduce your query w/case statements to essentially "all cost of all employees" if you would model every type of employee and cost relationally (and then some view for all-employees which is the union of each type projecting the approptiate cost and so on) then your query gets to just ask for records again without having to "tell how" those things are to become records. I dont think html is anything…
You are describe here the abstraction you create on a primitive (related to the system you design) system, and introduce ideas like "employee" "unions" etc If you do good job, when you programming you combine generic purpose systems and introducing new ideas in a way that another human can understand (words), but in certain way you "made the computer system understand" those things too. That's your job. So basically you teach SQL server that a union employee is a thing with the dbo.IsEmployeeUnion function.
In case of html you are not introducing new things. You actually can, because the browser is really flexible (like i make a non-functional quasi elements a lot, to make the html code itself more understandable), but HTML is not designed for you to introduce new elements/ideas (originally, nowadays kinda with CSS). Anyways this is how you see the difference in another way.
I try to tell all of you how the technical details, the implementation does not matter, only just the language itself, when we speak about paradigms, but it seems i'm failing.
By the way, i think training AI models does not fit to any of the paradigms we have here, it's gonna be (or it is?) a new way to solve problems, and that also has nothing to do with files, or any kind of implementation. The important is what you see on the screen, and what you do.
Earlier quoted context omitted.
"Comparing to html is nonsensical" No, absoltely not. We are speaking about programming paradigms. We can speak about those without computers, we never have to speak about HTML. A food recipe is a program, what you are executing when you cook, and it is imperative. Going to the restaurant you can achieve the same (the dish) but in a declarative way. But I compare SQL and HTML for you. In both cases you are creating a…
You keep retreading the same ground. Have you actually gone out and researched why so many people are telling you that SQL is declarative? Right now, it is reading as though you are being willfully ignorant of the entire field. Worse than just ignoring the entire field. You are caught up in a strict taxonomy that just doesn't work. Cooking recipes are, amusingly, mixed. They are a declaration of all ingredients and s…
But if you want: In the recipe the ingredients can be "int y;", like declaration, or something else like malloc() Stating that i will need something is not declarative programming. Declarative is defining the end result. So none of the ingredients or the declaration are declarative programming (if you want to turn recipe to declarative, you go to a restaurant), but as i told, you can't do this with elements of languages, it just does not make sense.
In practice language paradigms can overlap related to concrete products. You can use (and actually should) c# for functional programming, but that does not make the language functional, the fundamental design is imperative. In the future, everything will be declarative (chatgpt is the first glimpse), so in some way we're just wasting our time here. Also, speaking about AI, model training does not fit any of the current paradigms, it's gonna be a new one, and maybe the only one, who knows...
Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…
> Try and write any complex SQL as a series of semantically meaningful CTEs. Could you or anyone else on the post provide an example?
if you are storing fully denormalized concrete data about the value of salary/medical/retirement both pre- and post-tax that was actually paid to each pay period (because this can vary!), then you can define a view that does salary-per-employee (taxable, untaxable, etc), and then a view that rolls up employees-per-department. And you can write unit tests for all of those.
that's a super contrived example but basically once group aggregate or window functions and other complex sub-queries start coming into the picture it becomes highly desirable to write those as their own views. And you can write some simple unit tests for those views. there are tons of shitty weird sql quirks that come from nullity/etc and you can have very weird specific sum(mycol where condition) and other non-trivial sub-subquery logic, and it's simple to just write an expression that you think is true and validate that it works like you think, that all output groups (including empty/null groups etc) that you expect to be present or not present actually are/aren't, etc.
I'm not personally advocating for writing those as CTEs specifically as a design goal in preference to views, personally I'd rather write views where possible. But recursive CTEs are the canonical approach for certain kinds of queries (particularly node/tree structures) and at minimum a CTE certainly is a "less powerful context" than a generalized WHERE EXISTS (select 1 from ... WHERE myVal = outerVal) or value-select subquery. it's desirable to have that isolation from the outer SQL query cursor imo (and depending on what you're asking, it may optimize to something different in terms of plan).
Writing everything as a single query, where the sub-sub-query needs to be 100% sure not to depend on the outer-outer-cursor, is painful. What even is "DEEP_RANK()" in the context of this particular row/window? If you've got some bizarre (RANK(myId order by timestamp) or whatever, does it really work right? Etc. It's just a lot easier to conceptually write each "function" as a level with its own unit tests. Same as any other unit-testable function block, it's ideal if it's Obviously Correct and then you define compositions of Obvious Correctness with their own provable correctness.
And if it's not Obviously Correct then you need the proof even more. Encapsulate whatever dumb shit you have to do to make it run correctly and quick into a subquery and just do a "inner join where outerQuery.myId = myView.myId". Hide the badness.
Earlier quoted context omitted.
You are speak about things which called delegates, typed functions, defined by the parameters it gets, and the data it returns. Using delegates does not make a language functional. If you really want to speak about the underlying technology, than every high level language is declarative. You can draw a triangle by drawing three sides, or you can just put this in a function like drawtriangle(v1,v2,v3), and tadam, your…
Your comments in this thread have made for a pretty tough read, but I think your angle is finally made clear here. Supposedly, “every high level language is declarative”. It’s an opinion I suppose, but I doubt it’s one you’re going to find much support for. What I think this feels like to most people is that things aren’t what people think they are because you’ve decided to reimagine the commonly used definitions of…
But this is not my angle, i say the borders drawn up by the way how you, as a programmer, approach solving a problem. My take is declarative when you define the *end result* you want to get. You certainly don't do this with SQL, because that case the your product (what you write in the query) would be the end result set, the data itself. Of course in case of HTML you don't literally draw in the rectangle in the code, but you describe the end result. This is the difference.
Other arguments you wrote is out of scope for me regarding this conversation.
Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…
> Try and write any complex SQL as a series of semantically meaningful CTEs. Could you or anyone else on the post provide an example?