Home » U++ Library support » U++ SQL » PostgreSQL Support Classes [Experimental]
PostgreSQL Support Classes [Experimental] [message #9037] |
Sun, 15 April 2007 00:28 |
zsolt
Messages: 698 Registered: December 2005 Location: Budapest, Hungary
|
Contributor |
|
|
I have created experimental PostgreSQL classes and a dumb example.
The problems of it:
- it is incomplete yet, you can see the empty method bodies and comments
- blobs and reference constraints are untested.
- tested on windows only
- Auto increment field handling needs some unification in UPP. mysql has some API function to query the last generated value, just like sqlite. But Pg has a different architecture, as it uses per table sequences with names generated from table and field name.
- I created SERIAL and BIGSERIAL types in schema
Upp needs a modification to use this:
In Sql/SqlSchema.cpp in SqlSchema::FlushColumn():
else if (dialect == SQLITE3)
Upgrade() << Expand("alter table @t add ") << cd << ";\n";
else if (dialect == POSTGRESS)
Upgrade() << Expand("alter table @t add \n") << cd << "\n;\n\n";
else
Upgrade() << Expand("alter table @t add (\n") << cd << "\n);\n\n";
comments, patches are welcome.
|
|
|
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9053 is a reply to message #9037] |
Sun, 15 April 2007 20:25 |
|
I tried the plugin. It works great but I had to modify the PostrgreSQLTest databse schema file. In this file there is:
TABLE_(TESTPARTNER)
SERIAL_ (TESTPARTNER_ID) PRIMARY_KEY
STRING_ (TESTPARTNER_NAME, 200) INDEX
STRING_ (TESTPARTNER_ADDRESS, 200)
END_TABLE
TABLE_(TESTPRODUCT)
SERIAL_ (TESTPRODUCT_ID) PRIMARY_KEY
STRING_ (TESTPRODUCT_NAME, 200) INDEX
STRING_ (TESTPRODUCT_UNIT, 20)
END_TABLE
In postrgre schema macros SERIAL is defined as
#define SERIAL(x) COLUMN("serial primary key", int64, x, 0, 0)
so in final sql file there is double primary key attribute:
create table TESTPARTNER (
TESTPARTNER_ID serial primary key primary key,
TESTPARTNER_NAME varchar(200),
TESTPARTNER_ADDRESS varchar(200)
);
create table TESTPRODUCT (
TESTPRODUCT_ID serial primary key primary key,
TESTPRODUCT_NAME varchar(200),
TESTPRODUCT_UNIT varchar(20)
);
I think we should remove primary key from SERIAL and BIGSERIAL macros and let the user explicitly define it in schema file.
[Updated on: Sun, 15 April 2007 20:26] Report message to a moderator
|
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9056 is a reply to message #9055] |
Sun, 15 April 2007 21:04 |
zsolt
Messages: 698 Registered: December 2005 Location: Budapest, Hungary
|
Contributor |
|
|
My original idea was to have a database independent app.
So I created this SERIAL field to generate a serial id.
I put SERIAL, BIGSERIAL and CLOB into my sqlite schema also:
#define SERIAL(x) COLUMN("integer primary key autoincrement", int64, x, 0, 0) //int is not enough, as it is unsigned
#define SERIAL_ARRAY(x, items) COLUMN_ARRAY("integer primary key autoincrement", int64, x, 0, 0, items)
#define SERIAL_(x) COLUMN_("integer primary key autoincrement", int64, x, 0, 0)
#define SERIAL_ARRAY_(x, items) COLUMN_ARRAY_("integer primary key autoincrement", int64, x, 0, 0, items)
#define BIGSERIAL(x) COLUMN("integer primary key autoincrement", int64, x, 0, 0)
#define BIGSERIAL_ARRAY(x, items) COLUMN_ARRAY("integer primary key autoincrement", int64, x, 0, 0, items)
#define BIGSERIAL_(x) COLUMN_("integer primary key autoincrement", int64, x, 0, 0)
#define BIGSERIAL_ARRAY_(x, items) COLUMN_ARRAY_("integer primary key autoincrement", int64, x, 0, 0, items)
#define CLOB(x) COLUMN("text", String, x, 0, 0)
#define CLOB_(x) COLUMN_("text", String, x, 0, 0)
I don't know if this is correct or not, but we have to find some common schema requirement for all kinds of databases to achieve portability.
|
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9063 is a reply to message #9056] |
Mon, 16 April 2007 10:22 |
|
I think one should manualy write in schema file that a field is primary key, even if serial generates unique values. That's better IMO. Someone else without deep knowledge about given database know immediately what is primary key. And we should remember that we provide a COMMON way to define databse structure. In others databases serial not awlays means primary key (rather auto increment)
I think we should solve the problem of multi-field primary key.
Does it work in upp?:
TABLE(Test)
INT ID PRIMARY_KEY
INT VERSION PRIMARY_KEY
...
END_TABLE
Antoher thing are database scripts. There should be ONE funtcion to create/update database schema like:
OleDBSession db;
db.Prepare() - creates db or update it
and some additional funtions
db.Create() - only creating db
db.Update() - only updating db
db.Drop() - removing all tables
rather than
SqlSchema sch(SQLITE3);
StdStatementExecutor se(sqlite3);
All_Tables(sch);
if(sch.ScriptChanged(SqlSchema::UPGRADE))
Sqlite3PerformScript(sch.Upgrade(),se);
if(sch.ScriptChanged(SqlSchema::ATTRIBUTES)) {
Sqlite3PerformScript(sch.Attributes(),se);
}
if(sch.ScriptChanged(SqlSchema::CONFIG)) {
Sqlite3PerformScript(sch.ConfigDrop(),se);
Sqlite3PerformScript(sch.Config(),se);
}
sch.SaveNormal();
It is too complicated.
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9065 is a reply to message #9064] |
Mon, 16 April 2007 12:13 |
|
Quote: | Obviously, not as sexy as simply puttin more "PRIMARY_KEY" columns, OTOH at least Oracle SQL syntax is similar - you have to add it in single contraint, not as several "PRIMARY_KEY" comlums.
|
If databse is SQL 92 compatible there is posibility to define primary key in single line. It seems that most of db engines support it.
create table t
(
id integer,
ver integer,
type integer,
...,
primary key(id, ver, type)
)
I proposed putting PRIMARY_KEY attribute next to every column to avoid several macros like:
SINGLE_PRIMARY_KEY
DUAL_PRIMARY_KEY
TRIO_PRIMARY_KEY etc..
Of course I don't know if it is possible to implement it using c preprocessor. If not - I suggest to get rid of primary_key attribute as well as DUAL_PRIMARY_KEY and define new macro
PRIMARY_KEY(...) which allow you to define primary key for maximum 5 columns.
|
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9076 is a reply to message #9065] |
Mon, 16 April 2007 19:44 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
unodgs wrote on Mon, 16 April 2007 06:13 |
Quote: | Obviously, not as sexy as simply puttin more "PRIMARY_KEY" columns, OTOH at least Oracle SQL syntax is similar - you have to add it in single contraint, not as several "PRIMARY_KEY" comlums.
|
If databse is SQL 92 compatible there is posibility to define primary key in single line. It seems that most of db engines support it.
create table t
(
id integer,
ver integer,
type integer,
...,
primary key(id, ver, type)
)
|
Sure, above is a direct equivalent of what have now. SQL allows to define single-column keys in the column and multi-column keys as additional contraint...
Quote: |
I proposed putting PRIMARY_KEY attribute next to every column to avoid several macros.
Of course I don't know if it is possible to implement it using c preprocessor.
|
You would have radically change schema code, but I guess it is possible somehow.
Quote: |
If not - I suggest to get rid of primary_key attribute as well as DUAL_PRIMARY_KEY and define new macro
PRIMARY_KEY(...) which allow you to define primary key for maximum 5 columns.
|
Well, but C++ does not have variadic macros (yet?).
BTW, I have just checked and I have used DUAL_PRIMARY_KEY once in my "grand IDIS DB schema" of 500 tables... (OTOH, it is also true that I perhaps deliberately avoid it).
My suggestion is to use current way for now; maybe we can do better when we have all RDBMs implemented and have some experience with it; maybe we will then develop some "common .sch language" to have single .sch for all of them.
Mirek
|
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9079 is a reply to message #9076] |
Mon, 16 April 2007 20:11 |
|
luzr wrote on Mon, 16 April 2007 13:44 |
BTW, I have just checked and I have used DUAL_PRIMARY_KEY once in my "grand IDIS DB schema" of 500 tables... (OTOH, it is also true that I perhaps deliberately avoid it).
|
Sure, in my project the biggest primary key consist of 3 columns. But there is no macro to define it and probably there is someone out there who needs more
|
|
|
Re: PostgreSQL Support Classes [Experimental] [message #9080 is a reply to message #9079] |
Mon, 16 April 2007 20:37 |
|
mirek
Messages: 13984 Registered: November 2005
|
Ultimate Member |
|
|
unodgs wrote on Mon, 16 April 2007 14:11 |
Sure, in my project the biggest primary key consist of 3 columns. But there is no macro to define it
|
INLINE_ATTRIBUTE(", primary key (KEY1, KEY2, KEY3, KEY4)")
OTOH, perhaps not a bad idea is this:
#define PRIMARY_KEYS(keys) INLINE_ATTRIBUTE(", primary key (" keys ")")
PRIMARY_KEYS("KEY1, KEY2, KEY3")
does not sound that bad...
Mirek
|
|
|
Goto Forum:
Current Time: Mon Jun 10 12:31:06 CEST 2024
Total time taken to generate the page: 0.01887 seconds
|