10 ms·

The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data d

The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data dictionary for a file you could create I-descriptors, which were computed columns much like this feature allows. The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. An INVOICES file, for example, would have fields like CUSTOMER.NAME, CUSTOMER.ADDRESS, etc, (note that the '.' is just another character in the field name, it doesn't actually mean anything to the database) which would pull the relevant information from the customer file or call a subroutine to find the relevant information (e.g., in a history file). The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. It also leads to some interesting gotchas. I don't have a good reference handy for UniVerse's I-descriptors, but the System Description document[1] has a section on it. It had a certain elegance that I miss in modern SQL databases. On the flip side, modern SQL databases are so much more powerful. [0] https://www.rocketsoftware.com/products/rocket-universe-0/rocket-universe https://www.rocketsoftware.com/products/rocket-universe-0/ro... [1] https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Funiverse%2Fprevious%20versions%2Fv11.1.15%2Funiverse_systemdescriptionuserguide_v11115.pdf https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Fu...

uhoh-itsmaciek 7y ago
Is that different from adding such columns in a view enriching the table?
- Mister_Snuggles 7y ago
  For most things no, but there are some things you can do that are difficult or impossible to do in an SQL view. I-Descriptors can call subroutines, which opens them up to doing anything that the underlying UniVerse BASIC runtime can do. I once made an I-Descriptor which called a subroutine to feed the postal code into a web service that returned the geographic location of that postal code. It also implemented caching so it would only call the service for a postal code once. You could simply list out the file and find customers in a particular geographic region just using the query language. I'm sure this is do-able using something like PL/Python, but I suspect it would be much more difficult to do it all in-database than how I did it in UniVerse. Whether doing it this way is a good idea is, of course, up for debate. In a modern application I wouldn't dream of doing this on the fly in a view. Just because I could build it the way I did doesn't mean it was a good idea.
dragonwriter 7y ago
> The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. So, just like columns in a view. > These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. Yeah, in SQL you could do that with a correlated subquery in a column definition in a view, instead of a join, but it's generally not optimal (I've seen people do it, though not recently.) There's other uses for correlated subqueries besides being the inefficient way to do joins, and they, plus normal functions, let you calculate just about anything you might want in a columns in a view. > The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. The calculated columns one that supports only immutable functions because the values are materialized which doesn't make sense otherwise can't, but then on-the-fly calculation makes reads expensive. If you need that, though, Postgres supports it in views since (approximately) forever, which are the classic SQL approach to the problem.
- Mister_Snuggles 7y ago
  Yes, for the common case a view does the trick. However, I-Descriptors have the power of the underlying UniVerse BASIC runtime at their disposal, so you can do a LOT of things. I mention one of these in another comment[0]. However, as I mentioned, doing it this way isn't necessarily a good idea. [0] https://news.ycombinator.com/item?id=21137935 https://news.ycombinator.com/item?id=21137935
  - dragonwriter 7y ago
    Postgres views have the power of the installed procedural language runtimes behind them; its pretty common this includes an unrestricted Python interpreter, among others.
fleetside72 7y ago
I've also used this product but it's more of an operating environment than just a database. Super convenient on the front end. On the down side, getting data out of it is extremely expensive if you aren't targeting a single record ID. It's also not ACID compliant.
- macdice 7y ago
  I encountered this PICK stuff a couple of decades ago. It was really enlightening. I began to understand how the features of relational databases (transactions, isolation levels, constraints, normal form) fit together to avoid anomalies and orphans. They had jobs that would search for orphan references etc at night.
  - Mister_Snuggles 7y ago
    Some of these things are making their way into the Pick world. UniVerse, for example, gained proper transaction support at some point in its lifetime. When I showed the consultant developer, an old-school Pick guy, how that worked he was very impressed. Granted, it was fairly limited since they somewhat faked it using the regular locking mechanisms and it was only usable within UniVerse BASIC, but it did what it said on the tin. I ended up writing my own simple command interpreter which allowed me to use the UniVerse command prompt and BEGIN/COMMIT/ROLLBACK transactions. Transactions are definitely foreign to a LOT of Pick code though. I also worked on an ERP system that ran on a Pick-style system - if it crashed during GL posting it was completely possible, and this did happen frequently, that you would get a half-posted entry and an out-of-balance ledger. Had they just used the transactions that the underlying database supported they would not have had that problem. The equivalent in the SQL world is to use your database connection in autocommit mode, which is pretty rare. Most developers know to use transactions or work with a framework that intelligently uses transactions.

uhoh-itsmaciek 7y ago

Is that different from adding such columns in a view enriching the table?

Mister_Snuggles 7y ago
For most things no, but there are some things you can do that are difficult or impossible to do in an SQL view. I-Descriptors can call subroutines, which opens them up to doing anything that the underlying UniVerse BASIC runtime can do. I once made an I-Descriptor which called a subroutine to feed the postal code into a web service that returned the geographic location of that postal code. It also implemented caching so it would only call the service for a postal code once. You could simply list out the file and find customers in a particular geographic region just using the query language. I'm sure this is do-able using something like PL/Python, but I suspect it would be much more difficult to do it all in-database than how I did it in UniVerse. Whether doing it this way is a good idea is, of course, up for debate. In a modern application I wouldn't dream of doing this on the fly in a view. Just because I could build it the way I did doesn't mean it was a good idea.

dragonwriter 7y ago

> The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. So, just like columns in a view. > These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. Yeah, in SQL you could do that with a correlated subquery in a column definition in a view, instead of a join, but it's generally not optimal (I've seen people do it, though not recently.) There's other uses for correlated subqueries besides being the inefficient way to do joins, and they, plus normal functions, let you calculate just about anything you might want in a columns in a view. > The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. The calculated columns one that supports only immutable functions because the values are materialized which doesn't make sense otherwise can't, but then on-the-fly calculation makes reads expensive. If you need that, though, Postgres supports it in views since (approximately) forever, which are the classic SQL approach to the problem.

Mister_Snuggles 7y ago
Yes, for the common case a view does the trick. However, I-Descriptors have the power of the underlying UniVerse BASIC runtime at their disposal, so you can do a LOT of things. I mention one of these in another comment[0]. However, as I mentioned, doing it this way isn't necessarily a good idea. [0] https://news.ycombinator.com/item?id=21137935 https://news.ycombinator.com/item?id=21137935
- dragonwriter 7y ago
  Postgres views have the power of the installed procedural language runtimes behind them; its pretty common this includes an unrestricted Python interpreter, among others.

fleetside72 7y ago

I've also used this product but it's more of an operating environment than just a database. Super convenient on the front end. On the down side, getting data out of it is extremely expensive if you aren't targeting a single record ID. It's also not ACID compliant.

macdice 7y ago
I encountered this PICK stuff a couple of decades ago. It was really enlightening. I began to understand how the features of relational databases (transactions, isolation levels, constraints, normal form) fit together to avoid anomalies and orphans. They had jobs that would search for orphan references etc at night.
- Mister_Snuggles 7y ago
  Some of these things are making their way into the Pick world. UniVerse, for example, gained proper transaction support at some point in its lifetime. When I showed the consultant developer, an old-school Pick guy, how that worked he was very impressed. Granted, it was fairly limited since they somewhat faked it using the regular locking mechanisms and it was only usable within UniVerse BASIC, but it did what it said on the tin. I ended up writing my own simple command interpreter which allowed me to use the UniVerse command prompt and BEGIN/COMMIT/ROLLBACK transactions. Transactions are definitely foreign to a LOT of Pick code though. I also worked on an ERP system that ran on a Pick-style system - if it crashed during GL posting it was completely possible, and this did happen frequently, that you would get a half-posted entry and an out-of-balance ledger. Had they just used the transactions that the underlying database supported they would not have had that problem. The equivalent in the SQL world is to use your database connection in autocommit mode, which is pretty rare. Most developers know to use transactions or work with a framework that intelligently uses transactions.