Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » PostgreSQL Support Classes [Experimental]
PostgreSQL Support Classes [Experimental] [message #9037] Sun, 15 April 2007 00:28 Go to next message
zsolt is currently offline  zsolt
Messages: 693
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 #9039 is a reply to message #9037] Sun, 15 April 2007 09:11 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

Wonderful news! I was about to create this myself. I will test it soon!
Re: PostgreSQL Support Classes [Experimental] [message #9041 is a reply to message #9039] Sun, 15 April 2007 09:19 Go to previous messageGo to next message
fallingdutch is currently offline  fallingdutch
Messages: 258
Registered: July 2006
Experienced Member
Thanks for that news!

Bas
Re: PostgreSQL Support Classes [Experimental] [message #9048 is a reply to message #9039] Sun, 15 April 2007 16:02 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
unodgs wrote on Sun, 15 April 2007 03:11

Wonderful news! I was about to create this myself. I will test it soon!


Please do and merge it into the uppsrc ASAP. (Of course, if we have Zsolt's persmission?)

Mirek
Re: PostgreSQL Support Classes [Experimental] [message #9050 is a reply to message #9048] Sun, 15 April 2007 17:44 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
luzr wrote on Sun, 15 April 2007 16:02

unodgs wrote on Sun, 15 April 2007 03:11

Wonderful news! I was about to create this myself. I will test it soon!


Please do and merge it into the uppsrc ASAP. (Of course, if we have Zsolt's persmission?)

Mirek

Of course you have Smile
Re: PostgreSQL Support Classes [Experimental] [message #9053 is a reply to message #9037] Sun, 15 April 2007 20:25 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

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 #9054 is a reply to message #9053] Sun, 15 April 2007 20:49 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

Ok. I removed the primary key from macros (if that is mistake we will restore it Wink ). I've added it to uvs (example and "driver")
Re: PostgreSQL Support Classes [Experimental] [message #9055 is a reply to message #9054] Sun, 15 April 2007 20:59 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
sorry, I didn't update test app the content of .sch file should be
TABLE_(TESTPARTNER)
   SERIAL_  (TESTPARTNER_ID)
   STRING_  (TESTPARTNER_NAME, 200) INDEX
   STRING_  (TESTPARTNER_ADDRESS, 200)
END_TABLE

TABLE_(TESTPRODUCT)
   SERIAL_  (TESTPRODUCT_ID)
   STRING_  (TESTPRODUCT_NAME, 200) INDEX
   STRING_  (TESTPRODUCT_UNIT, 20)
END_TABLE
Re: PostgreSQL Support Classes [Experimental] [message #9056 is a reply to message #9055] Sun, 15 April 2007 21:04 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
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 #9057 is a reply to message #9056] Sun, 15 April 2007 21:18 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
So more clearly, what I would like to see in UPP:

  1. Common typename for automatically incremented id fields, used as primary key (SERIAL, AUTOINC or something else)
  2. Common typename for text fields with size limit (CLOB, TEXT or something else)
  3. A common infrastructure to handle SERIAL like fields in SqlArray like cases

Re: PostgreSQL Support Classes [Experimental] [message #9062 is a reply to message #9057] Mon, 16 April 2007 09:20 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
zsolt wrote on Sun, 15 April 2007 15:18

So more clearly, what I would like to see in UPP:

  1. Common typename for automatically incremented id fields, used as primary key (SERIAL, AUTOINC or something else)
  2. Common typename for text fields with size limit (CLOB, TEXT or something else)
  3. A common infrastructure to handle SERIAL like fields in SqlArray like cases




What is wrong with GetSerialId?

Mirek
Re: PostgreSQL Support Classes [Experimental] [message #9063 is a reply to message #9056] Mon, 16 April 2007 10:22 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

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 #9064 is a reply to message #9063] Mon, 16 April 2007 10:37 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
unodgs wrote on Mon, 16 April 2007 04:22


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)



IMO, RDBMS are too different to provide single common .sch "syntax&semantics" for all of them.

Quote:


I think we should solve the problem of multi-field primary key.
Does it work in upp?:



In Oracle, yes:

Quote:


TABLE_(O_POLOZKA)
INT_ (O_POLOZKA_SEQ) INDEX
INT (O_DOKLAD_SEQ) REFERENCES(O_DOKLAD) INDEX
STRING (POPIS, 2000)
DOUBLE_ (CENAJ)
INT_ (MNC)
INT_ (MNJ)
DUAL_PRIMARY_KEY(O_POLOZKA_SEQ, O_DOKLAD_SEQ)
END_TABLE



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.

Mirek
Re: PostgreSQL Support Classes [Experimental] [message #9065 is a reply to message #9064] Mon, 16 April 2007 12:13 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

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 #9073 is a reply to message #9037] Mon, 16 April 2007 18:46 Go to previous messageGo to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi zsolt,

Sorry to be a little out of topic... I'm studying if I could develop an interface for Firebird, and experience some difficulties to get started. I'm downloading your work, maybe it will help. But I worry if you have some docs or notes that could help me ?

Thanks.
Re: PostgreSQL Support Classes [Experimental] [message #9075 is a reply to message #9063] Mon, 16 April 2007 19:43 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
unodgs wrote on 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 agree.
Re: PostgreSQL Support Classes [Experimental] [message #9076 is a reply to message #9065] Mon, 16 April 2007 19:44 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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 #9078 is a reply to message #9073] Mon, 16 April 2007 19:57 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Hi jibe,
it is not very complicated. I used SQLite classes as a starting point.
I opened sqlite and postgresql library docs and started implementation.
For checking the created things I made a dumb app.

You can start, based on my classes as well, but PostgreSQL datatype handling is a little bit complicated, so you don't have to use that part, I think.
Re: PostgreSQL Support Classes [Experimental] [message #9079 is a reply to message #9076] Mon, 16 April 2007 20:11 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

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 Wink
Re: PostgreSQL Support Classes [Experimental] [message #9080 is a reply to message #9079] Mon, 16 April 2007 20:37 Go to previous messageGo to previous message
mirek is currently offline  mirek
Messages: 13975
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)")

Smile

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
Previous Topic: Catch errors with Sql.Execute
Next Topic: Newbie help with PostgreSQL example
Goto Forum:
  


Current Time: Fri Mar 29 16:01:43 CET 2024

Total time taken to generate the page: 0.02062 seconds