12 ms·
What if an SQL statement returned a database?
- sargstuff 3y agosql RBAC views? Needs a corresponding 'lasso' command to merge old/new databases.
- liotier 3y agoIs this question a JSON joke ?
- sargstuff 3y agothink it's an api wrapper for old school NP complete/unbounded delimited query aka (system("dump all database tables in delimited format, grep for results, create new database with grep'd results while keeping original datatypes/functions/etc)). Hopefully with full 8/16/32/64 utf suport. Perhaps sql take on 'view to a kill' aka create view / kill -9 database view
- Animats 3y agoIt's currying, for SQL. Cute. The functional people have discovered SQL. This is presumably lazy evaluation - the "returned database" is just some object that implies a selection from the database. You don't want to copy all the records.
- codetrotter 3y ago> The functional people have discovered SQL Either that, or the SQL people have discovered functional
- sitkack 3y agoBoth parties are aware of the other. One of the premier authors of advanced sql concepts programs in Haskell. https://en.wikipedia.org/wiki/Joe_Celko https://en.wikipedia.org/wiki/Joe_Celko
- mrits 3y agoAlso the clojure guy Rich Hickey has done a ton of SQL stuff
- sargstuff 3y agoso, why 'one ton'/wanting more if have clojure?
- sitkack 3y agoDatomic, is a persistent database that runs over existing SQL engines. https://www.datomic.com/ https://www.datomic.com/
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- sargstuff 3y agoYes, but a working concept for sql duplicate database & populate was to automate the 'populate with just relevant data needed. Potential for github pulls/pushes of databases with 'select' relevant data to push/provide via sql. Bit easier to think of it as method(s) needed to make dbms a replacement for OS where duplicate dbms is equivalent to exec a shell with relevant copy of files.
- sargstuff 3y agoWhat about dbms system call wrapper to advanced OS concepts? aka meet on mountain slopes to spin up herds of select ram (disk) copies. The dbms version of OS dining philosopers.[0] Lot less haskell. [0] : https://en.wikipedia.org/wiki/Dining_philosophers_problem https://en.wikipedia.org/wiki/Dining_philosophers_problem
- sitkack 3y agoI like this. It is like a CTE but you can take a subset of a database that satisfies the join condition. I could see this being used in a select for update scenario.
- o11c 3y agoRant: we should avoid the term "database" in contexts like this. The standard terms are "schema" (for the definition of "database" that sqlite and mysql use) and "catalog" (for the definition of "database" that postgresql uses). It's further overloaded to sometimes mean DBMS (database management system, i.e. an implementation), and sometimes also "cluster" or "daemon instance" (which sometimes overlap with one of the previous senses, but in different ways per implementation).
- quietbritishjim 3y agoThe word schema is the format that the data must adhere to, not the data itself, at least in the rest of computing, and the literal meaning is very close to that. So that is a bad word to use for a bunch of data, whereas database is clearly means a bunch of data.
- quickthrower2 3y agoI think “multiple tables” would be clearest for the submission. Or if it is the case “all tables in the database schema” or “all tables in all schemas of the database the user can access” The overloading horse has bolted so might as well aim for clarity for the intended audience. When I see just the word “Database” my brain treats it as a wildcard could be many things (like State could be a country or a state of a country)
- drdaeman 3y agoA schema is not just tables, though - there are also views, types, functions and other stuff that lives in a schema too.
- hinkley 3y agoSchema covers the definition of a bunch of related tables. A database can run as many schemas at it wants, for as many applications as it wants. I might not be able to see them all. "My database" doesn't mean anything in an enterprise environment. It might not even be 'mine' for any of several competing definitions. Also there are several ways I could break "my database" which would result in every single employee at the company being pissed off, at the same time. Even if they've never heard of my project.
- js8 3y agoIIRC this (returning a full database with a schema as a result of a query) has been proposed before in categorical databases, see https://www.categoricaldata.net/ https://www.categoricaldata.net/
- crazygringo 3y agoI'm struggling to understand the purpose of this. If I understand it correctly, for a query with joins, then instead of returning a single combined set of results with the information you need, it returns multiple sets of results based on each separate table. But I don't understand why that would ever be desirable. I always write my queries to return the information I need, per whatever "key" I need, in each row. Splitting that up in multiple sets of results is not helpful. Or if I really need multiple sets of data, I write multiple queries. So I really don't get what this is for. It's like it takes one of the biggest features of databases -- joins -- and removes it? Why?
- sargstuff 3y ago(humor) if system not set up appropriately, expanded long form version of shell fork bomb[0] that drains bank account per pay per amount of resources used. [0] https://www.cyberciti.biz/faq/understanding-bash-fork-bomb/ https://www.cyberciti.biz/faq/understanding-bash-fork-bomb/
- irrational 3y agoJokes on them. My bank account is already empty.
- sargstuff 3y agoNot if switch to using boolean logic. It's at max capacity then.
- cmrdporcupine 3y agoReading the abstract... it feels to me like the author doesn't know what a relational database is. Chris Date does discuss the concept of nested tuples / relation-valued tuples of some kind in his books as an extension to the relational model. But that's something else entirely.
- sargstuff 3y ago
- mjhay 3y agoThis is pretty cool given the potential backward compatibility - which makes widespread adoption much more realistic. There is some prior art of queries as morphisms between DBs (or schemas) in the category theory community. e.g., https://www.categoricaldata.net/ https://www.categoricaldata.net/ https://arxiv.org/pdf/0904.2012.pdf https://arxiv.org/pdf/0904.2012.pdf
- paulsutter 3y agoThis is way better than views
- bob1029 3y agoI feel like we could build something like this with SQLite, application defined functions and in-memory DB pretty quickly. But then I ask myself... Why would I want to do any of this? If I do Database=>sql command=>Database... Don't I have to write yet-more-SQL (or something approximating SQL) to get at my final items? I can't write a foreach loop over pile-o-tables. Virtually all programming abstractions expect some serial flow and enumerations of types. At some point, you are going to have to map A to B, and I'd argue adding another database in the middle probably isn't strictly headed in the right direction. If you want to select 2 different result shapes, just write 2 different queries. Use some lateral concepts in your programming environment to make the SQL part not suck. If you stand on some weird "one query only" principle every time you have to populate a practical view, you are going to have a super rough time with life.
- freeqaz 3y agoMaybe if the client did the JOINs and other logic that were in the query, it would shed load from the upstream DB? That would be helpful for RDBMS which tend to be harder to scale when they are CPU bound. I have seen a lot of services move to microservices and do client-side joins to help distribute load. Maybe there is a way for a DB built around this approach to run each table on a dedicated instance and have each stream the data back async? That would reduce cross-talk between the DBs serving the query and effectively create a client-side JOIN again, but be transparent to the client (if it were handled by the DB driver instead). An interesting thought!
- DanielHB 3y agoI imagine that this could be useful when you want to perform multiple operations on a subset of the data from the database at the client-side. So you load the RESULTDB to client and then perform subsequent queries client-side and those actually do the JOINs This could dramatically reduce the load on the database for some applications as well as simplifying some queries that can be awkward to do fully in SQL (some GROUP BYs can get quite complicated to understand) Another useful thing I can see is using this to simplify the creation of edge databases backed by a central database, like imagine creating a RESULTDB "view" at the edge using this syntax.
- quickthrower2 3y agoI don't get why it is a paper? Just make multiple requests SELECT ... FROM TABLE. You can batch them if you like, or just send out multiple requests in parallel. Infact this is what some shitty ORMs do when you don't want them to do this and you actually wanted a join. (Grrrr!). Sometimes you want the opposite. The complaint is more that ORMs do silly things and there is less control. I am guessing the paper is really about some nice sugar for doing this?
- sargstuff 3y agoit's either 'select groups of tables in database and make a new database' or api for behind the scenes 'duplicate the database and remove data not interested in'
- da_chicken 3y agoMultiple selections have to transfer possibly redundant data. By returning the data once and querying it clientside, you avoid having to do that.
- butlerm 3y agoThe main application for this is where you have detail data for parent records in a snowflake pattern. In that case SQL tends to require a ridiculous number of queries, where common formats like JSON and XML are capable of transferring hierarchical data like that in a single response. That is a major weakness of SQL and the common inability to return a hierarchical set of relations in one response in particular. Also, running a ridiculous number of queries in parallel is not practical on many databases due to per connection overhead, a problem that is so severe that many databases have internal many-to-one connection demultiplexers already, i.e. they have N execution engines for M connections. That should sound familiar to those who are familiar with threading models.
- ilikehurdles 3y agoIsn’t this what datomic does? It’s incredibly useful to be able to reference the full state of the database as of some fixed point in time.
- sargstuff 3y agoDuplicating complete database not so helpful if need to grant access to specific subset of data in database (note: database incorporates multiple tables).
- 1659447091 3y agoIf I understand correctly, Datomic has filters[1] for that; to remove some data from the db before returning it for use. [1] https://docs.datomic.com/pro/time/filters.html#filtering-for-security https://docs.datomic.com/pro/time/filters.html#filtering-for...
- sargstuff 3y agoSaner alternative to copy/exec[0], if not as portable. [0] : https://medium.com/r3d-buck3t/command-execution-with-postgresql-copy-command-a79aef9c2767 https://medium.com/r3d-buck3t/command-execution-with-postgre...
- ilikehurdles 3y agoThere's no duplication involved. It's an immutable database with an eavt (entity, attribute, value, time) index model. Pick a point in time to filter entities existing only at that point. It's effectively a read-only pointer to the full db created for free after every transaction.
- slowmovintarget 3y agoSo... Like Datomic? Sometimes the result of function call in Datomic is a copy of the database with the updates applied. It allows you to preview how your transaction will affect the data, were that choice to be made. Datalog allows for this kind of return. Were Postgres to add something like this (and if I'm not inferring too much from the title and commentary here) it wouldn't the first time that the industry took a lesson from Rich Hickey's resurrection of older tech (Datalog).
- shrimpx 3y agoNo. This paper is not about returning a copy of the database with the updates applied. It is about a "normalized join" query type. Edit: clarification.
- deleted 3y ago[deleted]
- erichocean 3y ago> No. It does not return a copy of the database with the updates applied. At first I thought you were talking about Datomic (which does, indeed, "return a copy of the database with updates applied"). But you are talking about the paper, which is about something else apparently. Carry on…
- shrimpx 3y agoThanks I updated to clarify.
- qaq 3y agowell Postgres does not need to do anything you can start a transaction and you have exactly that.
- Fire-Dragon-DoL 3y agoKinda, the transaction works for writes, you would need to set the isolation level to repeatable read or serializable to properly achieve this
- catchnear4321 3y agothis was not the anticipated answer to orm hate. let’s just call it drm, have an ai figure out the interface and do the plumbing, and throw some decoupled compute and storage at the problem, behind a serverless veneer. adam sandler sang it best.
- sargstuff 3y agowhat about taylor swift song 'shake it off'?
- catchnear4321 3y agomy god they’re everywhere…
- sargstuff 3y agoYeah, blame it on Jaccard[0] needing a new tailor and auto correct swiftly recommended Taylor because of looming ticket issues. [0] : https://www.computerhistory.org/storageengine/punched-cards-control-jacquard-loom/ https://www.computerhistory.org/storageengine/punched-cards-...
- deleted 3y ago[deleted]
- moribvndvs 3y agoA sql statement can return a database… if you serialize your database entities w/ lazy loaded relationships straight from an ORM to your API response
- jerrygenser 3y agoWouldn't lazy loaded relationships mean that every time you access one of the entities it lazily loads, leading to n+1 fetching behavior?
- moribvndvs 3y agoYeah. Sorry, I was being sarcastic. Was thinking of the time a junior dev json serialized an NHibernate entity and it was essentially serializing the entire database.
- karmakaze 3y agoWhen I think of SQL, I think of relational databases where they were created and most commonly used. What queries return are not tables, but a relation that can be composed of data from one or more tables. What this paper is then saying is "What if SQL returned something other than a relation?" That question is being answered by all non-relational DBs being made. An example of one might be a graphdb that could return a graph with nodes, edges and properties thereof. If we want to stay within relational dbs, then we're saying that we want a single statement to return multiple relations. That sounds something like GraphQL query to me which is stitching of the relational parts.
- fyrn_ 3y agoSQL queries return data from tables. The abstract does mention that one downside of this is that the result of the query has to be constructed in memory, resulting in a lot of copying. This is particularly apparent for joins, where some join types can produce much more data than all their input tables combined. I don't see how this is "return something other than a relation" they are pretty specific about what they mean and what benefits it can bring
- karmakaze 3y ago> Our approach has clear semantics, i.e. our extended SQL returns subsets of all tables with only those tuples that would be part of the traditional (single-table) query result set, however without performing any denormalization through joins. Yes, that sounds just like a GraphQL connection result with associated nested connections. I believe that there's link deduplication for GraphQL which can also be enabled to avoid the copies. The main difference is avoiding multiple round-trips--one for each table--which within a datacenter network doesn't add a whole lot of latency compared to the time serving those queries and some can also be done in parallel.
- da_chicken 3y ago> What this paper is then saying is "What if SQL returned something other than a relation?" No, I think it's closer to "hey guys, remember that idea that domains don't need to be primitive data types?"
- zzzeek 3y agoI did some contract work for a company called Akiban, which was bought by FoundationDB, which was bought by Apple that took the whole product off the market at the time. What Akiban offered was SQL with an extra datatype called a "nested result set", the idea here seems similar if not more generalized; you didn't need a traditional join anymore and your ORM-like library could build up hierarchical models from a single SELECT statement that included nested queries. there is still logic in SQLAlchemy to accommodate this concept.
- spion 3y agoI'm surprised they didn't mention SQL Server's "FOR JSON", which doesn't solve the problem they're solving entirely (n:n cannot be deduplicated AFAIK) but it can get you 90% of the way https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&tabs=json-path https://learn.microsoft.com/en-us/sql/relational-databases/j... (I can't help but wonder if this whole thing would be a lot better if SQL had a more first-class concept of sum types...)
- BeefWellington 3y agoI'm just going off the abstract in my reply here. I'd wager that any performance gains achieved by returning a database file instead of a (well-known, long-established, well-understood) resultset is going to be negated by literally the rest of the owl. By that I mean, you then need to instrument loading the database file that is returned, applying specific local security and performance considerations that come with that choice, and then querying the normalized data within that returned file. Could that be fast? Maybe. The best bet would be scenarios where the wire speed / bandwidth / system memory of the consuming device are very very limited. In those situations there's already an established way of handling it though: cursors. A resultset is, for the most part, highly compressible already (barring scenarios where you're just returning blobs, which incidentally this is doing as well), so it's not clear to me this is even saving you that much in terms of the "data duplication" they're discussing.
- shrimpx 3y agoI don't think it's talking about returning a database per se. Their contribution seems to be a new type of query that, instead of using joins to join together results from many tables into a single denormalized output "table," it actually returns multiple output "tables" whose rows you can associate together in your code. It's kind of a "normalized join," if you will.
- leblancfg 3y agoYeah. I'm imagining e.g. a full database schema at my disposal in the ORM layer, but with only the handful of rows relevant to the logged-in user. Could be useful in many read-heavy scenarios, and would make a default way that skips DB fetches.
- shrimpx 3y agoThat’s kind of cool. Also related to local-first, maybe.
- crooked-v 3y agoOne thing to note is that with a row-level security implementation like Postgres you can pretty much do this already. For example, if you have a table `users` with appropriate RLS policies, `SELECT * FROM users` will only return the rows that whoever's doing the selecting is allowed to see.
- amichal 3y agoI suspect the article is about returning the contributing tables in a join as multiple relations... while not possible in SQL proper this is possible with stored procedures (at least in T-SQL) https://stackoverflow.com/questions/40013747/return-multiple-datasets-from-sql-server-stored-procedure https://stackoverflow.com/questions/40013747/return-multiple...
- netcraft 3y agoI want to read the paper further, but I have done similar things in the past using JSONB. Multiple CTEs to pull the individual parts, convert to JSON and then combine into objects keys of arrays.
- bunabhucan 3y agoThis is going to age me but a client CEO drank the CORBA kool-aid aid and insisted on a CORBA interface with a vendor famous for lock-in and their underlying database not being open. The implementation essentially returned the entire DB in its byzantine format without a decoder ring.
- claytongulick 3y agoThis reminds me of the old Microsoft DAO dynaset multi-table record set back in the VB days [1]. You used to be able to treat that as a mini-database, it was queryable and updateable and you could sync changes back to the db. What's old is new again... [1] https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-object-dao https://learn.microsoft.com/en-us/office/client-developer/ac...
- rockwotj 3y agoFWIW Google Cloud Datastore had similar functionality over a decade ago. They are called kindless queries: https://cloud.google.com/datastore/docs/samples/datastore-kindless-query https://cloud.google.com/datastore/docs/samples/datastore-ki... Essentially it allows for querying over all kinds (aka collection/table). One usecase where these can be useful to implement account wipeout. If all entities (aka document/row) have a commonly named field like “owner” which is an account ID you can delete all their records in a single query. I mean for deletes you can do a similar use case with cascading deletes and foreign keys, but those don’t always scale well because they have too all happen in one transaction (but then it’s not transparent to the application)
- paulddraper 3y agoHow do you query that database? With SQL?
- butlerm 3y agoThat would be convenient, and some programming environments have support for that kind of thing already. A hierarchical object valued expression would also be convenient in a different way.
- pickleberto 3y ago[dead]
- ysofunny 3y agoit'd be like returning executable code?
- ysofunny 3y agothen fonts are programs, like type faces
- gfody 3y ago> Also be aware that if one of the inputs to ARRAY_AGG is NULL, the entire output will be NULL and thus none of the non-NULL attribute values will be shown in the output! So extra logic is re-quired to avoid these cases. Good luck with that one! that's how null works in 3VL, use isnull or coalesce accordingly. > We present a backward compatible SQL extension (SELECT RESULTDB) to allow SQL SELECT statements to return a clearly defined subset of a database rather than just a single table. just use sql/json it's standardized and supported. you guys are way out of touch from modern sql. please go and read modern-sql.com
- tiku 3y agoI really don't see the need for it. Perhaps export tools could use it. For the rest of you have correct tables and joins you can get all the data you need in one query. You could already use multiple subqueries if you wanted.
- tlarkworthy 3y agoThe purpose of this is to support the annoying limitation that results must be a rectangular matrix. I tweeted this Oct 28th so clearly it solves a problem practitioners already agree with https://x.com/tomlarkworthy/status/1718329733274824783?s=20 https://x.com/tomlarkworthy/status/1718329733274824783?s=20 Many useful result sets are not rectangular, trees, graphs etc. There is no way to efficiently transfer these results sets in a single query. You either do multiple round trips or pad the leaves with repeated information which eats network bandwidth. So I am hugely in favour of this work.
- naasking 3y agoIsn't the repeated information trivially compressible using a suitable run-length encoding? As long as the result set is ordered properly of course.
- tlarkworthy 3y agoYes it should be compressible trivially
- lichtenberger 3y agoThis thread is also a good short summary of the key points: https://twitter.com/jensdittrich/status/1734142079012323480?t=OhpEFwix02rJEIMYMc1yyw&s=19 https://twitter.com/jensdittrich/status/1734142079012323480?...
- klysm 3y agoI think this solves the problem of Cartesian explosions in ORMs. The requirement of related data needing to be shoved into a single table means data shared by many entities has to be duplicated. The result set being split into distinct tables removes this constraint. The only way I know how to deal with this in Postgres while maintaining a single query is to lateral join into a json aggregate. However, I don’t think the tooling around that approach is mature enough yet.
- programmarchy 3y agoI’ve been doing this. Curious what you mean by tooling. All the pg client libraries I’ve used support json/jsonb return types, for example.
- eddd-ddde 3y agoI love postgres json aggregates, but since postgres cant do nested aggregates it is kinda limiting when I would like to build my whole query response in a single postgres query
- twojacobtwo 3y agoI'm new to web-dev (and databases), so my apologies for the potentially silly question, but what's an example situation where you would use nested aggregates and what kind of effort would it save, if it were possible?
- 3y ago
- runeks 3y ago> Our approach has clear semantics, i.e. our extended SQL returns subsets of all tables with only those tuples that would be part of the traditional (single-table) query result set, however without performing any denormalization through joins. Hmm, so if I understand this right, the query SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID would return two tables: (1) an Orders table with only the OrderID and OrderDate columns present; and (2) a Customers table with only the CustomerName column present — without any connection between CustomerName and either Orders.OrderID or Orders.OrderDate? EDIT: Or, perhaps both Orders.CustomerID and Customers.CustomerID would be present in the respective tables since they're referenced by the JOIN?
- frankfrank13 3y agoI think your edit is correct.
- frankfrank13 3y agoThe two parts of this I find really attractive are: > What is also irritating about the result table is the fact that the notion of a ‘key’ is not defined anymore, that notion is simply dropped when processing data in SQL or relational algebra (→Problem 5: key information loss). > Back to the result table, SQL discards even more information from the underlying relations: where did a specific attribute in the result table come from in the first place? Does that attribute value correspond to an attribute in one of the base relations? Or was it computed? (→Problem 6: schema information loss) I've definitely had cases in ETL where being able to track where data came from (e.g `columnA` is derived from a window function over `a.columnB` and `b.columnC). This doesn't quite get to that (what if `a.columnB` is derived!) but its a neat idea
- euoia 3y agoThis seems similar to how Eloquent (Laravel's ORM) will, by default, make separate IN queries when you ask for a collection plus its relationships (instead of using a JOIN). I have thought about how you might return normalised data to your front-end and, with support from a front-end library, rebuild the object relationships so you can access them naturally. I looked into JSON:API and indeed there is a Laravel library for working with JSON:API https://github.com/laravel-json-api/laravel https://github.com/laravel-json-api/laravel. One place I got stuck with JSON:API is that support for updating a row and its relationships in a single API request seems to have been kicked around for a while and is unresolved: https://github.com/json-api/json-api/issues/795 https://github.com/json-api/json-api/issues/795 There is an atomic operations extension: https://jsonapi.org/ext/atomic/ https://jsonapi.org/ext/atomic/ But is currently unsupported by laravel-json-api: https://github.com/laravel-json-api/laravel/issues/39 https://github.com/laravel-json-api/laravel/issues/39