Looking for opinions: supporting CREATE DATABASE

[Mark Rotteveel]

Currently, Jaybird doesn't support CREATE DATABASE, and the only way to
create a database is using FBManager.

The implementation of the CREATE DATABASE statement is rather complex:
part of it is parsed and handled by fbclient.dll (or possibly ISQL), it
creates a new database, and then executes the CREATE DATABASE on that
new database where other parts are handled by the server parsing the
statement (and ignoring the parts already handled by fbclient.dll/ISQL).

In ISQL, the execution of CREATE DATABASE results in a new connection
(to the new DB), so subsequent statements are executed to that new DB.

Translating such behaviour to Jaybird results in some potential problems
because the current connection is already connected to another database.

All solutions have their drawbacks:

1. Switch the connection to the new database.

This might cause confusion/problems, especially if the connection is
held in a connection pool (e.g. it is returned to the pool, and the next
user assumes it is connected to the original DB, while it is connected
to the new DB)

2. Keep the connection to the old database.

This will cause confusion because the user likely expects that
subsequent statements are executed against the new DB

3. Terminate/kill connection after execution.

Avoids the confusion of points 1 and 2, but is not very user-friendly.

4. Just don't support it, but maybe add a connection property to create
a database if it doesn't exist

Although this avoids the previous problems, it leaves open other issues
like the additional configuration of the database.

5. Allow "database-less" connections (for example
`jdbc:firebird://localhost/`) which can (only) be used to create a
database, and will switch to the new database after creation.

Complex to implement, and results in inconsistent API (i.e. why allow
database creation on one connection, but not the other; and the majority
of JDBC methods on such connection will not work or not work correctly).

For now, I think option 4 is probably the way to go, though it will need
some additional thought on the configuration side of things.

Which way would you prefer, or do you have any alternative ideas for this?

Mark
--
Mark Rotteveel

[Vasiliy Yashkov]

07.06.2023 13:48, Mark Rotteveel пишет:

> Currently, Jaybird doesn't support CREATE DATABASE, and the only way
> to create a database is using FBManager.

For me, creating a database with `org.firebirdsql.management.FBManager`
is enough. In practice, I have not encountered any problems when an
alternative to `FBManeger` is required.

> For now, I think option 4 is probably the way to go, though it will
> need some additional thought on the configuration side of things.

+1. I think if there is no really important reason, there is no need for
implementation.

Vasiliy

[Mark Rotteveel]

On 07-06-2023 15:41, Vasiliy Yashkov wrote:
> 07.06.2023 13:48, Mark Rotteveel пишет:
>> Currently, Jaybird doesn't support CREATE DATABASE, and the only way
>> to create a database is using FBManager.
> For me, creating a database with `org.firebirdsql.management.FBManager`
> is enough. In practice, I have not encountered any problems when an
> alternative to `FBManeger` is required.

FBManager is only useful if you actually have access to FBManager, which
might not be the case when using a generic tool or library using JDBC.

>> For now, I think option 4 is probably the way to go, though it will
>> need some additional thought on the configuration side of things.
>
> +1. I think if there is no really important reason, there is no need for
> implementation.

Mark
--
Mark Rotteveel

[Fabio Luis - Vanguarda TI]

+1 option 4

Kind Regards

Fabio Silva

--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/bebb2973-7620-7b7c-e124-3a6f4027a7e7%40lawinegevaar.nl.

[Arioch The]

Maybe just copying iSQL behavior? 

You can open the connection either by "connect" or by "create database", period. 

Both commands are "constructors", they create a connection object, both can not be called on the already constructed connection. 

Think OOP. 

Databases are members of server, so both connect or create-database methods exist in server namespace but not in database namespace.

Similarly inside "table methods" select-insert-merge-delete you can not embed "database methods" like create-view or drop-procedure

Select * from (create view V1 as select.. 

This makes no sense and want work. 

Similarly create-database makes no sense inside database connection but only inside server connection

[Arioch The]

5. Allow "database-less" connections (for example
`jdbc:firebird://localhost/`) 

Which methods of which classes are constructing connection objects in JDBC? 

Could you extend those classes and add methods to create connections by creating DB? 

[Hugo Eyng | Datamais]

Hello, Mark.

Option 3

Atenciosamente,

Hugo

Em qua., 7 de jun. de 2023 às 07:48, Mark Rotteveel <ma...@lawinegevaar.nl> escreveu:

[Mark Rotteveel]

On 07-06-2023 19:45, Arioch The wrote:
> Maybe just copying iSQL behavior?
>
> You can open the connection either by "connect" or by "create database",
> period.
>
> Both commands are "constructors", they create a connection object, both
> can not be called on the already constructed connection.

That is not correct. ISQL allows you to create a new database when
currently connected to the database, and as far as I'm aware, this is
done on the existing connection handle, but there is specific
client-side code which triggers specific handling of a CREATE DATABASE
statement.

> Think OOP.

I am thinking OOP, in JDBC you cannot execute statements without
creating a `java.sql.Connection`, from which you create
`java.sql.Statement`, which you can then use to execute something.

My points 1 - 3 and 5 are to be able to execute the statement CREATE
DATABASE through normal JDBC API access, so you need a connection and a
statement before you can execute them.

My point 4 is to create a database when connecting (e.g. if it doesn't
exist yet).

If it was just about creating databases per se, then Jaybird already has
FBManager (and has had that since 2001), but this is about creating
database in a way which is accessible through the JDBC API.

> Databases are members of server, so both connect or create-database
> methods exist in server namespace but not in database namespace.

That is not actually true for Firebird CREATE DATABASE is partially
handled client-side and partially handled server-side.

> Similarly inside "table methods" select-insert-merge-delete you can not
> embed "database methods" like create-view or drop-procedure
>
> Select * from (create view V1 as select..
>
> This makes no sense and want work.
>
> Similarly create-database makes no sense inside database connection but
> only inside server connection

Most other DBMS implementations would disagree with this assertion.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

I want to do this through the JDBC API, so I need to do it within the
bounds of the JDBC API (so no adding additional methods). And that would
be alternative 4, handling creation through a connection property (e.g.
create=ifnotexists and maybe some additional properties for
configuration like page size, default character set, etc.), or
alternative 5, by allowing "database-less" connections, which would only
allow execution of CREATE DATABASE (and maybe some basic metadata
querying about the server), and then switch to being a connection to the
newly created database.

Mark
--
Mark Rotteveel

[Arioch The]

> Both commands are "constructors", they create a connection object, both
> can not be called on the already constructed connection.

That is not correct. ISQL allows you to create a new database when
currently connected to the database,

Well, let's say "initial behavior of ISQL" 

And the rest is enabled by ISQL being a single-connection tool, singleton or top of the stack, whatever. 

But there is no ambiguity as there can be no 2+ simultaneously interactive connections, like it normally can in JDBC. 

So this latter trick would indeed lead to ambiguity. However the initial behavior then the connection is created this or that way looks sound. 

> Think OOP.

I am thinking OOP, in JDBC you cannot execute statements without
creating a `java.sql.Connection`

So, which methods repertoire allows it? 

Can you add overloaded methods that would accept extra Create Database statement? 

And/or some ad hoc "create database statement constructor" where Jaybird could copy required values directly from setters (something like Fluent API perhaps) rather than having to parse the stmt later? 

That was why asked "how exactly connection is created". 

My understanding of Java is it has no "1st class" citizens but only class and instance methods. 

So there has to be some JDBCConnection-like class having some static JDBCConnection JDBCConnection.GetConnection(string URL) method (mimicking C++ conventions here) 

Adding one more method with same or altered name would be the first thing I'd think about. 

My points 1 - 3 and 5 are to be able to execute the statement CREATE
DATABASE through normal JDBC API access, so you need a connection and a
statement before you can execute them.

Feels misdirected to me. 

Like the aforementioned hypothetical select from create view. 

While for the sake of full orthogonality this might look a good idea, the implementation (error reporting for example) complexities would be huge and practical use cases very rare. 

ISQL/scripts have to have it all in one "enumeration type" , because "line of text" is their only structure. 

Same for Embedded SQL I think, though it was years since i read those specs last. 

But for libraries having different means to structure API than "lines of text" and supposed to separate API into namespaces that feels to be misaimed. 

If it was just about creating databases per se, then Jaybird already has
FBManager (and has had that since 2001), but this is about creating
database in a way which is accessible through the JDBC API.

FB Manager might be harder to find. 

I would, from gut feeling, to have extra doors in the same wall the normal entryways are built into. 

And, is database creation already spec'ced on JDBC (or in dropped JPA)? I don't think so, or there won't be the question. Hence whatever you choose, it still would be extending JDBC. Just the choice is how exactly to extend it. 

Personally I would be extending the "open connection" API namespace, because that, I feel, would be the first place newcomers would look into writing their first FB/JB hello worlds. 

That is not actually true for Firebird CREATE DATABASE is partially
handled client-side and partially handled server-side.

Implementation details, and exactly those I think you want to encapsulate away from the library users. I was talking about "wide brush" logic model. 

> Similarly create-database makes no sense inside database connection but
> only inside server connection

Most other DBMS implementations would disagree with this assertion.

Did it translate into JDBC realm? 

Is there de facto if not de jure standard how all/most other JDBC drivers handle it, thus how most of seasoned Java-but-not-FB-yet developers would expect it to be done, because "everyone do it like that"? 

If yes, then just follow "least surprise" principle for your lib users. 

If no, then design it in the way that your guts feel consistent. Eventually year or more later you would have to mantain it, so apply "least surprise" to yourself. When you would have this whole discussion washed out of your cache, and would suddenly have a maintaining task, where would you intuitively expect that functionality to live? 

[Mark Rotteveel]

On 08-06-2023 09:24, Arioch The wrote:
> > Think OOP.
>
> I am thinking OOP, in JDBC you cannot execute statements without
> creating a `java.sql.Connection`
>
>
> So, which methods repertoire allows it?

java.sql.DriverManager.getConnection(..), which is a class provided by
the JDBC API, so I cannot add anything there.

or

java.sql.DataSource.getConnection(),

which is an interface specified by JDBC

> Can you add overloaded methods that would accept extra Create Database
> statement?

No, I cannot add overloaded methods, because that would take us outside
of the JDBC API, and the whole point is to do this within the JDBC API.

> And/or some ad hoc "create database statement constructor" where Jaybird
> could copy required values directly from setters (something like Fluent
> API perhaps) rather than having to parse the stmt later?

We already have that, that is FBManager, but that is outside the JDBC API.

> That was why asked "how exactly connection is created".
>
> My understanding of Java is it has no "1st class" citizens but only
> class and instance methods.

Classes and objects are first class citizens...

> So there has to be some JDBCConnection-like class having some static
> JDBCConnection JDBCConnection.GetConnection(string URL) method
> (mimicking C++ conventions here)
>
> Adding one more method with same or altered name would be the first
> thing I'd think about.

No, because that would take it outside the JDBC API.

> My points 1 - 3 and 5 are to be able to execute the statement CREATE
> DATABASE through normal JDBC API access, so you need a connection and a
> statement before you can execute them.
>
>
> Feels misdirected to me.

You're missing the point that I'm trying to do something *within* the
confines of the JDBC API.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 08-06-2023 09:24, Arioch The wrote:

> Did it translate into JDBC realm?

They allow you to execute a CREATE DATABASE on an existing connection,
and switch the catalog of the connection to that new database. If the
connection is pooled, it likely resets the catalog back when the
connection is returned to the pool.

> Is there de facto if not de jure standard how all/most other JDBC
> drivers handle it, thus how most of seasoned Java-but-not-FB-yet
> developers would expect it to be done, because "everyone do it like that"?
>
> If yes, then just follow "least surprise" principle for your lib users.

There is the hitch, neither Jaybird nor Firebird have a concept of a
catalog of the current connection, so such solutions are painful to emulate.

> If no, then design it in the way that your guts feel consistent.
> Eventually year or more later you would have to mantain it, so apply
> "least surprise" to yourself. When you would have this whole discussion
> washed out of your cache, and would suddenly have a maintaining task,
> where would you intuitively expect that functionality to live?

That is why I write Jaybird Design Proposals these days when
implementing anything non-trivial, so I can read up on my considerations
and thoughts later.

Mark
--
Mark Rotteveel

[Arioch The]

>>×My understanding of Java is it has no "1st class" citizens but only class and instance methods.

> Classes and objects are first class citizens...

Oops. I mean standalone function there

[Mark Rotteveel]

Well, sort of since Java 8, with Lambda's and method references, but in
practice those are implemented as on-the-fly implementations of
so-called functional interfaces, so they are still objects of some form.

Mark
--
Mark Rotteveel

[Pierre Vacher]

Perhaps the best way to support database creation would be an option in the connection url as is the case for many JDBC drivers.

This does not pose the problem of two established connections and this makes possible to create a new database when connecting and without code...

[Pierre Vacher]

Well, this is an option that I am looking forward to because it will allow Jaybird to work in embedded mode as is already the case for HyperSQLOOo and SQLiteOOo

[Mark Rotteveel]

On 19/03/2024 14:12, Pierre Vacher wrote:
> Well, this is an option that I am looking forward to because it will
> allow Jaybird to work in embedded mode as is already the case for

> HyperSQLOOo <https://prrvchr.github.io/HyperSQLOOo/> and SQLiteOOo
> <https://prrvchr.github.io/SQLiteOOo/>

Note that you can already programmatically do it with FBManager[1]. For
embedded, you will need to use `new FBManager("EMBEDDED")`, so if you're
not limited to just using the JDBC API, you can already create databases.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/drivers_documentation/java/5.0.x/docs/org/firebirdsql/management/FBManager.html
--
Mark Rotteveel

[Pierre Vacher]

Thanks Mark for the information, but I think I'll be lazy and wait for this option. In addition the HyperSQLOOo and SQLiteOOo extensions are only written in python and I would like it to be the same for JaybirdOOo.

[Pierre Vacher]

> For embedded, you will need to use `new FBManager("EMBEDDED")

Maybe if we can define how we implement this option, I can submit a PR?

[Mark Rotteveel]

On 26/03/2024 19:45, Pierre Vacher wrote:
> > For embedded, you will need to use `new FBManager("EMBEDDED")
>
> Maybe if we can define how we implement this option, I can submit a PR?

I'm finishing up something else, and then I'll write a JDP for this for
discussion. Hopefully that document will be ready early April.

Mark
--
Mark Rotteveel

[Pierre Vacher]

Ok, thanks, I'm waiting...