Home » U++ Library support » U++ SQL » PostgreSQL Support Classes [Experimental]
| PostgreSQL Support Classes [Experimental] [message #9037] |
Sun, 15 April 2007 00:28  |
zsolt
Messages: 702
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: 702
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: 14290
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: 14290
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
|
|
|
|
|
|
|
|
| Re: PostgreSQL Support Classes [Experimental] [message #9095 is a reply to message #9037] |
Tue, 17 April 2007 09:08 |
 |
jibe
Messages: 294
Registered: February 2007
Location: France
|
Experienced Member |
|
|
Hi zsolt,
Thanks for your reply ! Maybe I worry only because I'm just discovering UPP, and have no experience with it !
To go back to the topic :
| luzr wrote on Mon, 16 April 2007 20:37 |
PRIMARY_KEYS("KEY1, KEY2, KEY3")
does not sound that bad...
|
Yes, it looks like very much what I use with Firebird, and is also common with some other databases...
| unodgs wrote on Mon, 16 April 2007 22:42 |
It looks fine to me! Maybe except S letter at the end sugessting multiple primary keys. But if you could add it to sql schema do it please!
|
Yes, I think also that this S is not so usefull... Why not
PRIMARY KEY ("UNIQUE_KEY")
or
PRIMARY KEY ("KEY1, KEY2, ...")
It seems logical, and I don't see any ambiguity ? But maybe I don't know enough yet about all consequences in upp...
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PostgreSQL Support Classes [Experimental] [message #15675 is a reply to message #15633] |
Sun, 04 May 2008 10:15 |
|
|
Thank you! its worked! I will testing in continuation!
I have a situation: I need to make a server and client application. that in code i need decide where it will execute (server or client). Only application server can communicate with DB server, but client is connected with server. Server and client appliction need becouse:
1. The client station can/may be not so performace as server station.
2. Connection from DB server and application server can be more speedily.
If I wrong tell me please how is correct! How I can create this?
Thank you!
John!
P.S. The ultimate ++ is the most impression me!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Apr 26 13:56:06 GMT+2 2026
Total time taken to generate the page: 0.01356 seconds
|
U++ framework
Search
Help
Members
Register
Login
Home
Yes, this is a good reason ! 
