Conversation

Notices

1. Embed this notice
   Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 11:59:10 JST Evan Prodromou

   So, I'm taking Database Systems in as a class for my master's degree in CS. As usual, I thought I knew everything before starting the class, and I've been pleasantly surprised by how much is new to me.

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:01:45 JST Evan Prodromou

     in reply to

     One of these was the object-relational features that were added to SQL/Foundation 2008. They include type creation with CREATE TYPE, which can be used for tables or columns or both; inheritance with UNDER; and instance methods with CREATE INSTANCE METHOD. It looks like a really interesting language to program with.

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:03:44 JST Evan Prodromou

     in reply to

     There are also a lot of neat built-in collection types like lists, sets, arrays, and dictionaries.

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:05:23 JST Evan Prodromou

     in reply to

     Unfortunately, I can't find an Open Source database management system that supports all these features.

     PostgreSQL has CREATE TYPE, but no inheritance or methods nor the collection types. Yes, I could probably work around it with other features, but my main goal is experimenting with this subset of standard SQL.

     Neither MySQL nor SQLite seem to even have user-defined types.

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:11:55 JST Evan Prodromou

     in reply to

     So: do you know of an Open Source DBMS that implements the SQL/Foundation object-relational features pretty faithfully?

     (It's not PostgreSQL; see above.)

     I know there have been a few new RDBMS entrants lately. I wonder if any of them have this feature set.

     Suggestions welcome! Denying that what I want is what I want is not welcome! Don't be that guy!

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:30:48 JST Evan Prodromou

     in reply to

     • Christopher :coffefied:

     @Christopher thanks, will do!

   • Embed this notice
     Christopher :coffefied: (christopher@mastodon.coffee)'s status on Tuesday, 12-Nov-2024 12:30:49 JST Christopher :coffefied:

     in reply to

     @evan

     The only thing I could think of is Firebird. I don't know if it has all the features you're looking for, but give it a shot.

     https://www.firebirdsql.org/

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:42:22 JST Evan Prodromou

     in reply to

     Oracle XE is gratis but not Open Source, and I think it implements these features. I'll give it a try.

   • Embed this notice
     Bruce Elrick (virtuous_sloth@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:42:52 JST Bruce Elrick

     in reply to

     @evan Now you have me curious. How is the inheritance that you are seeking different from the Postgres INHERIT?

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:45:36 JST Evan Prodromou

     in reply to

     • Bruce Elrick

     @virtuous_sloth I don't know. Tell me when you find out!

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 12:59:56 JST Evan Prodromou

     in reply to

     • Bruce Elrick

     @virtuous_sloth table inheritance is interesting but different from the type inheritance in CREATE TYPE ... UNDER. PostgreSQL has array-type columns but none of the other container types. It does not have methods, but I could probably get by with just stored procedures, without polymorphism. I'm pretty sure I could make PostgreSQL work, but it doesn't have the syntax I'm actually looking for.

     https://www.postgresql.org/docs/17/ddl-inherit.html

   • Embed this notice
     Bruce Elrick (virtuous_sloth@cosocial.ca)'s status on Tuesday, 12-Nov-2024 13:07:50 JST Bruce Elrick

     in reply to

     @evan
     PG docs say "SQL:1999 and later define a type inheritance feature, which differs in many respects from the features described here."

     One major caveat I see in PG's INHERIT is that indexes & constraints do not extend into child tables.

     Informix doc, however:

     "In a table hierarchy, a subtable automatically inherits the following properties from its supertable:

     All constraint definitions (primary key, unique, & referential constraints)
     Storage option, All triggers, Indexes,... "

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Tuesday, 12-Nov-2024 23:35:59 JST Evan Prodromou

     in reply to

     • andyc

     @andyc Oh, neat. I'm trying the Docker container, since I'm on a Mac.

   • Embed this notice
     andyc (andyc@mastodon.me.uk)'s status on Tuesday, 12-Nov-2024 23:36:00 JST andyc

     in reply to

     @evan The DB developer VirtualBox image is useful if you just want to play with the functionality (rather than have to install XE). Plus it's the latest version (23AI) with vector search and improved JSON support.

     https://www.oracle.com/database/technologies/databaseappdev-vm.html

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Wednesday, 13-Nov-2024 00:19:27 JST Evan Prodromou

     in reply to

     • Bruce Elrick

     @virtuous_sloth Wikipedia seems to think that Postgres handles most of these features:

     https://en.wikipedia.org/wiki/Comparison_of_object%E2%80%93relational_database_management_systems

     It lists UDTs, methods, type inheritance, and table inheritance as features. Maybe I need to read further!

     It also says that Postgres supports Array, List, Set, Multiset, and Object reference.

   • Embed this notice
     Evan Prodromou (evan@cosocial.ca)'s status on Wednesday, 13-Nov-2024 01:44:02 JST Evan Prodromou

     in reply to

     • andyc

     @andyc I was kind of surprised how much has been added to SQL since the early 2000s. I've thought of features like time series, object storage, and so on as incentives for creating NoSQL services.

   • Embed this notice
     andyc (andyc@mastodon.me.uk)'s status on Wednesday, 13-Nov-2024 01:44:03 JST andyc

     in reply to

     @evan That's so true. I work for Oracle and see these new features like JSON duality and AI vector search announced and think I'll never need that but normally I do. Eventually. Never think you know everything.