Home » U++ Library support » U++ SQL » PostgreSQL issues
| PostgreSQL issues [message #9654] |
Wed, 23 May 2007 22:34  |
zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary
|
Contributor |
|
|
I have checked current version and found some strange things. In PostgreSQLSchema.h there ar some buggy lines:
#define SERIAL(x) COLUMN("integer autoincrement", int64, x, 0, 0) //int is not enough, as it is unsigned
#define SERIAL_ARRAY(x, items) COLUMN_ARRAY("integer autoincrement", int64, x, 0, 0, items)
#define SERIAL_(x) COLUMN_("integer autoincrement", int64, x, 0, 0)
#define SERIAL_ARRAY_(x, items) COLUMN_ARRAY_("integer autoincrement", int64, x, 0, 0, items)
#define BIGSERIAL(x) COLUMN("integer autoincrement", int64, x, 0, 0)
#define BIGSERIAL_ARRAY(x, items) COLUMN_ARRAY("integer autoincrement", int64, x, 0, 0, items)
#define BIGSERIAL_(x) COLUMN_("integer autoincrement", int64, x, 0, 0)
#define BIGSERIAL_ARRAY_(x, items) COLUMN_ARRAY_("integer autoincrement", int64, x, 0, 0, items)
The above lines would be useful in SQLite (I wanted to propose it), but not in PG. The correct lines would be:
#define SERIAL(x) COLUMN("serial", int64, x, 0, 0) //int is not enough, as it is unsigned
#define SERIAL_ARRAY(x, items) COLUMN_ARRAY("serial", int64, x, 0, 0, items)
#define SERIAL_(x) COLUMN_("serial", int64, x, 0, 0)
#define SERIAL_ARRAY_(x, items) COLUMN_ARRAY_("serial", int64, x, 0, 0, items)
#define BIGSERIAL(x) COLUMN("bigserial", int64, x, 0, 0)
#define BIGSERIAL_ARRAY(x, items) COLUMN_ARRAY("bigserial", int64, x, 0, 0, items)
#define BIGSERIAL_(x) COLUMN_("bigserial", int64, x, 0, 0)
#define BIGSERIAL_ARRAY_(x, items) COLUMN_ARRAY_("bigserial", int64, x, 0, 0, items)
I would propose some other lines to SQLite for compatibility with PostgreSQL:
#define CLOB(x) COLUMN("text", String, x, 0, 0)
#define CLOB_(x) COLUMN_("text", String, x, 0, 0)
[Updated on: Wed, 23 May 2007 22:36] Report message to a moderator |
|
|
|
| Re: PostgreSQL issues [message #9656 is a reply to message #9654] |
Wed, 23 May 2007 22:47  |
zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary
|
Contributor |
|
|
BTW in my dev environment, I use SERIAL differently, because I wanted a common .sch file for my SQLite and PostgreSQL databases with the same behaviour.
So I use these lines for SQLite:
#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)
and these lines for PostgreSQL:
#define SERIAL(x) COLUMN("serial primary key", int64, x, 0, 0) //int is not enough, as it is unsigned
#define SERIAL_ARRAY(x, items) COLUMN_ARRAY("serial primary key", int64, x, 0, 0, items)
#define SERIAL_(x) COLUMN_("serial primary key", int64, x, 0, 0)
#define SERIAL_ARRAY_(x, items) COLUMN_ARRAY_("serial primary key", int64, x, 0, 0, items)
#define BIGSERIAL(x) COLUMN("bigserial primary key", int64, x, 0, 0)
#define BIGSERIAL_ARRAY(x, items) COLUMN_ARRAY("bigserial primary key", int64, x, 0, 0, items)
#define BIGSERIAL_(x) COLUMN_("bigserial primary key", int64, x, 0, 0)
#define BIGSERIAL_ARRAY_(x, items) COLUMN_ARRAY_("bigserial primary key", int64, x, 0, 0, items)
So SERIAL in my terminology means a field type as it is incrementing automatically, serves as a primary key, and records can never have the same (old) id after deleting rows.
|
|
|
|
|
|
| Re: PostgreSQL issues [message #9692 is a reply to message #9656] |
Fri, 25 May 2007 09:08 |
|
|
What is the problem with explicity defining column as PRIMARY_KEY?
I think primary_key attribute should be removed from sqlite schema to preserve consistency instead of adding it to postgresql.
Besides PostrgesSQL documentation says:
Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Apr 28 21:14:16 CEST 2024
Total time taken to generate the page: 0.05647 seconds
|
Members
Pages
Help
Register
Login
Home
