[DOC] Converting the AUTO_INCREMENT attribute

[markus_petrux]

Actually, the MySQL attribute AUTO_INCREMENT is converted as follows:

• PostgreSQL: SERIAL (or BIGSERIAL depending on integer size)
• MS-SQL: IDENTITY(1, 1)

Other checks performed when AUTO_INCREMENT is detected:

• AUTO_INCREMENT can only be used for integer data types.
• AUTO_INCREMENT columns should be part of an index.
• DEFAULT clausule is not allowed.

Feel free to post your comments, so we could fix the convertor with better (or more accurate) rules.

[Ptirhiik_]

Here is what I have about auto-inc :

Code: Select all

# mySQL &#58
CREATE TABLE phpbb_presets (
  preset_id MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  preset_type VARCHAR(5) NOT NULL DEFAULT '',
  preset_name VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY ( preset_id )
);

# postgresql
CREATE SEQUENCE phpbb_presets_id_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;

CREATE TABLE phpbb_presets (
  preset_id int4 NOT NULL DEFAULT nextval('phpbb_presets_id_seq'&#58&#58text),
  preset_type varchar(5) NOT NULL DEFAULT '',
  preset_name varchar(50) NOT NULL DEFAULT '',
  CONSTRAINT phpbb_presets_pkey PRIMARY KEY ( preset_id )
);

# mssql
CREATE TABLE [phpbb_presets] (
  [preset_id] [bigint] IDENTITY (1,1) NOT NULL,
  [preset_type] [varchar] (5),
  [preset_name] [varchar] (50)
) ON [PRIMARY]
GO

ALTER TABLE [phpbb_presets] WITH NOCHECK ADD
	CONSTRAINT [DF_phpbb_presets_preset_type] NOT NULL DEFAULT ('') FOR [preset_type],
	CONSTRAINT [DF_phpbb_presets_preset_name] NOT NULL DEFAULT ('') FOR [preset_name],
	CONSTRAINT [PK_phpbb_presets] PRIMARY KEY CLUSTERED ([preset_id]) ON [PRIMARY]
GO

Note I haven't yet the case where a modification of an existing table require to change a field into an auto-inc one : I'm not even sure it can be easely done without droping the field.

[markus_petrux]

First, thanks for the feedback. Let's see how it goes...

Second, I should say I'm not an expert in MySQL, PostgreSQL nor MSSQL. I just spent some time searching and reading a lot of reference guides.

PostgreSQL, at least since 7.2 (based on a comment in db/postgres.php Olympus PostgreSQL support starts at 7.3+), creates the sequence automatically when using SERIAL.See chapter "Serial Types":
http://www.postgresql.org/docs/8.0/inte ... atype.html" target="_blank
http://www.postgresql.org/docs/7.2/inte ... atype.html" target="_blank

PostgreSQL and MSSQL do not support the UNSIGNED attribute. However, the same effect can be achieved by using a CHECK constraint.

PostgreSQL and MSSQL do not support the MEDIUMINT data type, so it should be promoted to INTEGER (in PostgreSQL INT4 is a synonym for INT or INTEGER).

For MSSQL, identifiers only need to be enclosed between [] (or double quotes) if reserved keywords or special chars are used. There is no need to "quote" data types either. As per the ALTERs, MSSQL 2000 allows to define those attributes in the CREATE TABLE statement itself.

This is what the SQL Parser would actually do with the MySQL statement

PostgreSQL:

Code: Select all

CREATE TABLE phpbb_presets (
    preset_id SERIAL,
    preset_type VARCHAR(5) NOT NULL DEFAULT '',
    preset_name VARCHAR(50) NOT NULL DEFAULT '',
    CONSTRAINT phpbb_presets_pk PRIMARY KEY (preset_id),
    CHECK (preset_id>=0)
);

MSSQL:

Code: Select all

CREATE TABLE phpbb_presets (
    preset_id INTEGER NOT NULL IDENTITY(1, 1),
    preset_type VARCHAR(5) NOT NULL DEFAULT (''),
    preset_name VARCHAR(50) NOT NULL DEFAULT (''),
    CONSTRAINT phpbb_presets_pk PRIMARY KEY (preset_id) ON [PRIMARY],
    CHECK (preset_id>=0)
) ON [PRIMARY];

As per implementation of ALTER TABLE conversions, there is a bit of work to put into the SQL Parser. For instance, to implement DROP COLUMN in certain versions, it is certainly required to re-create the table. That will be implemented by reading the system catalogs (aka. INFORMATION_SCHEMA views) to retrieve information about columns, primary keys, indexes, sequences or other contraints being used by the table. Ugh! There's a lot stuff already, but there is lot to do yet.