Project

General

Profile

Dbo Questions

Added by Sascha Kühl over 1 year ago

Hi,

currently we're evaluating if we can replace our own written db backend with Wt::Dbo. I've successfully integrated basic functionality into a sub module of our software. After discussion within our team some questions were raised. I could answer some of them, but there are a couples left, which I'll place here. I would appreciate it if I can get some answers.

  • Are unique key constraints suppported?
  • Is it possible to created indices with Wt::Dbo?
  • Can I override a db column type for a c++ datatype (e.g. double -> varchar)?
  • When using hasMany() without belongsTo(), will Wt::Dbo create a mapping table on its own?
  • When using Postgres how can one set/change explicit the db schema?
  • When using Postgres is it possible to use the db notification functionality?
  • Is there support for Oracle?

Thanks for your help in advance!


Replies (3)

RE: Dbo Questions - Added by Roel Standaert over 1 year ago

When using Postgres how can one set/change explicit the db schema?

As a general note: although Wt::Dbo can be used to map many existing schemas to C++, its schema generation capabilities are fairly basic. Wt::Dbo also doesn't have any support for database migrations.

Often, when we develop software with Wt::Dbo we will use it to generate the schema during the early stages of development, but then move to SQL to create the schema and set up any extra constraints or indices.

One project we work on uses Flyway to do migrations.

Are unique key constraints supported?

The primary surrogate key is unique by default, but for any other keys you'd have to define it in SQL yourself.

Is it possible to create indices with Wt::Dbo?

The primary surrogate key will have an index by default, but you have to create any other indices with SQL.

Can I override a db column type for a c++ datatype (e.g. double -> varchar)?

You can specialize sql_value_traits for any type that we don't already provide a specialization for. We already have specialized it for double, but you could introduce another type that wraps a double and specialize sql_value_traits for it. You could also override some of SqlConnection's functions (making sure to override clone() as well), but those functions are really intended to adapt to different backends.

When using hasMany() without belongsTo(), will Wt::Dbo create a mapping table on its own?

The schema generation code

When using Postgres is it possible to use the db notification functionality?

There's no built-in support in Wt::Dbo for it, but we have used this in projects that use Wt::Dbo. Essentially, we use a Wt::Dbo::backend::Postgres for the connection management, and we listen and poll on the underlying Postgres::connection().

Sending notifications can simply be done using pg_notify:

Wt::Dbo::Transaction t(session);
session.execute("SELECT pg_notify(?, ?);").bind(channel).bind(message);

We can share this code with you upon request.

Is there support for Oracle?

As you can see in the feature table at the bottom of our Downloads table we do have an Oracle backend that is available separately, though it would require some updating for the latest version of Wt,
contact sales (sales@emweb.be) if you're interested in it.

RE: Dbo Questions - Added by Sascha Kühl over 1 year ago

Thank you very much for your detailed answers to my questions!

So, if I understand you correctly, we have to do unique constraints and indices on our own. That should be no problem with the Wt::Dbo::backend::Postgres::executeSql() or Wt::Dbo::Session::execute() methods.

It is not possible to override an already specialized datatype, but there is a workaround for it, Great. We'll have to look, if it not too much overhead.

I think the answer to the hasMany()/belongsTo() question wasn't quite done ;-) But I looked to the testing code and as far as I understood the code, the mapping table will be generated by Wt::Dbo. Is that correct?

Concerning the Oracle backend, yes we need it. I'll come back to the sales contact if we've made a positive decision.

So, we'll discuss these answers in our team. They'll help us a lot!

One last question: If we decide to use your software, can we modify or enhance the source code to our needs?

Thanks again.

RE: Dbo Questions - Added by Roel Standaert over 1 year ago

But I looked to the testing code and as far as I understood the code, the mapping table will be generated by Wt::Dbo. Is that correct?

Yes

One last question: If we decide to use your software, can we modify or enhance the source code to our needs?

You are allowed to under the terms of the GPL, but our commercial license does not allow it without "our prior written consent".

    (1-3/3)