Description:
This is a request for a non-SQL-standard feature.
I would like to be able to create subdatabases, that is to say, a database within a database. such db nesting could be limited to a practical number of levels.
A subdatabase could be referenced as top_db.sub_db1.tablename, etc. A USE command could specify a top-level database or a subdatabase:
USE top_level1;
USE top_level1.sub1;
I do not feel that the ability to sepcify relative references wotu be necessary, but they might look like this:
USE ..; (go up one level. if at top level, this command dereferences and used database)
USE .sub1; (go down one level from the current used database)
Here is why I want this. I write custom (usually web) applications, each application gets its own database. several tables appear in almost every application database, for example Users, Utils, Log, Archive, etc., these perform standardized functions in my application architecture. IT would be nice to visually/conceptually separate these standards tables from application specific tables.
If I were to keep all the standard tables in one separate database ("common"), backup organization and restoring from backup becomes very difficult, the application database is more difficult to transport to another server, etc.
With subdatabases, it would be nice to have a command like:
CREATE DATABASE newapp.common LIKE oldapp.common;
INSERT INTO newapp.common.utils SELECT * FROM oldapp.common.utils u WHERE u.type='default';
and carry over an existing schema with ease.
It would also free up the namespace of a database in some cases. For example, two subdatabases could be named app.debug and app.production, allowing identical table names be used for different purposes within the application, switching operational functions just by changing a single USE command.
It might also be interesting to be able to create temporary subdatabases, that are scoped to a session or procedure, like this:
CREATE TEMPORARY DATABASE thisapp.temp LIKE thisapp.temp_template;
USE thisapp.temp; (or USE .temp;)
...
But this is not really necessary.
This is by no means an essential need, but it would be very nice to have, imo.
How to repeat:
Feature request, not repeatable, please see Description for examples.
© 2024, Oracle Corporation and/or its affiliates
Description: This is a request for a non-SQL-standard feature. I would like to be able to create subdatabases, that is to say, a database within a database. such db nesting could be limited to a practical number of levels. A subdatabase could be referenced as top_db.sub_db1.tablename, etc. A USE command could specify a top-level database or a subdatabase: USE top_level1; USE top_level1.sub1; I do not feel that the ability to sepcify relative references wotu be necessary, but they might look like this: USE ..; (go up one level. if at top level, this command dereferences and used database) USE .sub1; (go down one level from the current used database) Here is why I want this. I write custom (usually web) applications, each application gets its own database. several tables appear in almost every application database, for example Users, Utils, Log, Archive, etc., these perform standardized functions in my application architecture. IT would be nice to visually/conceptually separate these standards tables from application specific tables. If I were to keep all the standard tables in one separate database ("common"), backup organization and restoring from backup becomes very difficult, the application database is more difficult to transport to another server, etc. With subdatabases, it would be nice to have a command like: CREATE DATABASE newapp.common LIKE oldapp.common; INSERT INTO newapp.common.utils SELECT * FROM oldapp.common.utils u WHERE u.type='default'; and carry over an existing schema with ease. It would also free up the namespace of a database in some cases. For example, two subdatabases could be named app.debug and app.production, allowing identical table names be used for different purposes within the application, switching operational functions just by changing a single USE command. It might also be interesting to be able to create temporary subdatabases, that are scoped to a session or procedure, like this: CREATE TEMPORARY DATABASE thisapp.temp LIKE thisapp.temp_template; USE thisapp.temp; (or USE .temp;) ... But this is not really necessary. This is by no means an essential need, but it would be very nice to have, imo. How to repeat: Feature request, not repeatable, please see Description for examples.