Re: [gnome-db] gnome-db and complete C/GTK support for SQL DDL???

[Vivien Malerba <vmalerba gmail com>]

On Tue, 26 Oct 2004 19:05:06 -0600, Neil Zanella <nzanella gmail com> wrote:
> Hello people,
> 
> ----------------------------------------------------------------
> 
> Summary: I need an API that will let me
> define tables in C/GTK in an OO manner, write
> the corresponding CREATE statements in
> SQL, retrieve metadata about those statemnts
> in C/GTK, allow me to subclass tables to define
> what needs to be done on inserts, updates, etc...
> to other tables (foreign keys would have built
> in handles to other table types).
> 
> So, does gda provide me with this too, or am I
> just about to code all of this myself from scratch
> and make my own library for this?
> 

The Libmergeant (to be merged into Libgnomedb) can perform some of the
tasks you mentionned above; read on...

> 
> ------------------------------------------------------------------
> 
> Let me explain to me my problem. Consider a database with many
> interrelated tables.
> There are some issues involved with such an application that deserve
> some comments.
> 
>    Tables have data types. The issue of mapping GTK/C data types to
> SQL data types
>     is seemingly complex because of cross vendor variations. It seems
> like somehow
>     libgda should provide middleware that allows the user to enter
> data compatible with
>     the one described by the provider. Moreover, it seems to me that
> it should make it
>     possible to define tables right there, without having to compose
> strings containing
>     CREATE TABLE statements...
> 
>     Basically, I should be able to create a database object, add
> database table objects
>     to such an object, and then issue a call datbase->create() which
> iterates through all
>     table objects and creates them right there.
> 
>     Each table object described above should be a member of an SQLTable calss.
>     Each SQLTable instance should have member functions that allow you to query
>     stuff like what rows does this table have?, what are the names of the column
>     hearders?, what is the name of the table? what is its primary key? what are
>     its foreign keys? what are the unique keys? what are the check constraints?
>     can we describe the triggers associated with these tables (ok, this latter
>     question leads to some complexities, so I will drop it, the one concerning
>     triggers that is)?
> 

About DDL code generation:
=====================
The Libmergeant, to be merged soon into Libgnomedb, has such a table
object (to be name GnomeDbTable). That object represents a database
table: you can have a look at its fields and constraints.

For the moment, it does not generate any DDL SQL code. This is on the
TODO list. However that code generation should be quite easy to add.

About DML code generation:
=====================
Libmergeant uses a MgQuery (soon to be a GnomeDbQuery) object to
represent any DDL query (SELECT, INSERT, etc), and has anither object
which takes a SELECT query and generates the UPDATE, INSERT and DELETE
statements to work on the result of the SELECT provided query.

A simple example: consider the tables customers and orders (in
Postgres SQL syntax here)
CREATE TABLE customers (
    id serial NOT NULL,
    name character varying(35) NOT NULL,
    default_served_by integer,
    country character varying(20),
    city character varying(30)
);
CREATE TABLE orders (
    id serial NOT NULL,
    customer integer NOT NULL,
    creation_date date DEFAULT now() NOT NULL,
    delivery_before date,
    delivery_date date
);

the orders table refers to the customers one.
A query like "SELECT name, creation_date FROM customers INNER JOIN
orders ON (orders.customer=customers.id)" which lists the orders
placed by each customer.

The corresponding generated queries will be (to modify the orders table):
UPDATE orders set customer=##, creation_date=## WHERE id=##;
DELETE FROM orders WHERE id=##;
INSERT INTO orders (id, customer, creation_date) VALUES (DEFAULT, ##, ##);

where ## represents a parameter to be filled.


>     Why all this? For a simple reason: I want to execute some generic
> code, like:
> 
>     I've got this tuple object. I don't know what table I'm looking
> at, but I want to
>     insert this tuple. Please check the primary key.

If you don't know the table, then how can you make any SQL statement?

> 
>     See, this kind of generic programming can be useful for large databases.
> 
> So, I want to do stuff like this (want more examples, just ask of
> generic programming
> in the context of databases?) My question is does libgda do this
> already? Cause if
> not I will end up writing a library to support this myself in the next
> timeframe. It
> seems to me that libgda should let me do this kind of stuff:
> 
> define tables in C
> write them out in SQL
> query their metadata in C
> 
> Furthermore, PostgreSQL, Oracle, and so on, each have their own PL/SQL or
> whatever, and they are incompatible, so triggers and doing updates on tables
> C and D automatically when A is updated need be done in source code for
> maximum portability.
> 
> Comments on all or any of the above EXTREMELY welcome,
> 

If you want to see if Libmergeant/Libgnomedb can (partially) fulfill
your needs, then have a look at the code and at the documentation
(Libmergeant is bundled into the Mergeant module).

Cheers,

Vivien