Comments (5)
All tasks except creating a database are now accomplished in initializedDB.sql
in v1.0.
A possible solution is to build on the .psql batch script developed in #99.
from classdb.
I agree that prepareDB.psql
is the right place to do database creation. One possible approach is to have the user supply a desired database name prior to running prepareDB.psql
. This can be accomplished using psql's script variables.
The user would set the value of a script variable (in this example, DB_NAME) by either invoking the -v
switch when starting psql (-v DB_NAME=<databaseName>
), or running the meta-command \set
(\set DB_NAME <databaseName>
) once inside psql.
Then, the following code added to the beginning of prepareDB.psql
will create a new database with the name stored in DB_NAME
, and then connect to it prior to running the ClassDB preparation scripts.
CREATE DATABASE :DB_NAME WITH OWNER = classDB;
\connect :DB_NAME
I did a quick test of this code, and it appears to work. If we do use this method, it will be necessary to add the CREATEDB
assertion to prepareDB.psql
, as mentioned in the first comment.
from classdb.
I like the approach to using the -v
psql switch. However, I highly recommend using the variable name NEW_DB_NAME
, because DB_NAME
is too close to the psql variable DBNAME
.
After much thinking and research, I have identified two alternative approaches: a simple alternative and a rigorous alternative. I recommend taking the simple alternative for M2. Also, because the existing solution does everything except create the database, this issue gets low priority in the interest of time: it should be addressed only if no other issues of higher priority exist.
The simple alternative is to create a createAndPrepareDB.psql
which blindly creates the new database, switches connection, and invokes the existing prepareDB.psql
. Any error in creating the new DB and connecting to it will just stop the script. The most likely error is that the specified DB already exists.
The rigorous alternative takes the following approach:
- Use the existing
prepareDB.psql
as the starting point - Create the new DB (and connect to new DB) only if variable
NEW_DB_NAME
is non-empty no database with that name already exists. The conditional execution is necessary to accommodate the cases where the current DB is the DB to be initialized (which is actually the current usage) - Complete DB creation and connection before invoking
initializeDB.sql
. This wayinitializeDB.sql
will be independent of psql (that script presently works oncurrent_database()
)
Here are some considerations:
- psql does not support conditional execution: means a OS script is likely needed
- Executing psql with
-l
switch prints a list of databases and exits psql. An OS script can grep the result to see if a database with a given name already exists - We have stayed away from OS scripting because that adds new dependencies, not to mention a lot of development effort
- Executing psql with
CREATE DATABASE
cannot be executed in a transaction- I assume
CREATE DATABASE
can be executed inside a PL/pgSQLIF
block: this assumption should be tested first before proceeding
- I assume
- It is possible to test if a database exists in a server using the query
SELECT COUNT(*) FROM pg_database WHERE datname=':NEW_DB_NAME';
- Once we are sure
CREATE DATABASE
can be conditionally executed, I propose we put the DB creation and the connection switching in a separate scriptcreateDB.psql
- It should be a
psql
file because it will contain a psql meta command - I assume it is possible to contain a meta command inside a PL/pgSQL code block (but I have my doubts)
- It should be a
from classdb.
Removing from M2 due to time constraint.
from classdb.
I am closing this issue because creating a database simply requires executing the CREATE DATABASE and the installation scripts in ClassDB 2.0 make it extremely easy to add ClassDB to any database.
from classdb.
Related Issues (20)
- Add session id to table DDLActivity (N) HOT 2
- Test scripts are not organized as components (M) HOT 1
- SET SESSION AUTHORIZATION breaks ClassDB.getSessionID() (W) HOT 1
- Not all object types are listed by listOwnedObjects (M)
- Automate removal of superuser-owned objects (N)
- Permit users to create schemas (N)
- Server-version comparison functions are too long to be part of another script (E)
- Unit tests for shared ownership of team objects are insufficient (M)
- Guard uses of ADD COLUMN IF NOT EXISTS; changed in pg9.6 (N)
- The documentation for function ClassDB.importConnectionLog is incomplete (M)
- Server version comparison uses incorrect preset option (W) HOT 1
- DDL activity logging inserts a duplicate entry for some ALTER TABLE statments (WE)
- Server-version comparers are not marked immutable (M) HOT 1
- Guard uses of DROP OWNED: changed in pg9.5 (N) HOT 1
- Incorrect script filename used for server initialization (W)
- Dropping a user as student from a DB removes the user from student role in all DBs on the server HOT 6
- Users known in one ClassDB database are able to log in to all ClassDB databases on the same server (W) HOT 1
- Parameter initialPwd in function createRole is probably unnecessary (E)
- Roles with login are not granted login privilege HOT 2
- Connection log import fails on Postgres 13 (W) HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from classdb.