Pruteanu Dragos wrote:

> It is fine, we can deal this situation too ( using no catalog and no schema - null for bought ).
> I wanted to be sure about how this method works. We will pass null than to each other methods ( getTables(), getColumns(), getExportedKeys, etc. ).
>
> Could you also please review the 'alter statement...' list below ? It would be a great help since you know the best the right commands.
>
>
> Create View CREATE VIEW {name} AS {text}
> Create Primary Key ALTER TABLE {table} ADD CONSTRAINT {name} PRIMARY KEY( {columns} )
> Create Unique Index ALTER TABLE {table} ADD CONSTRAINT {name} UNIQUE( {columns} )
> Create Index CREATE INDEX {name} ON {table} ( {columns} )
> Create Column ALTER TABLE {table} ADD COLUMN {text}

So far ok.

> Create Sequence CREATE SEQUENCE {name} START WITH {text}

Only "CREATE SEQUENCE {name}" is supported.

To move the generator value, you have to use gen_id function like this:
SELECT gen_id({name}, {text}) FROM rdb$database. Skip it.

> Create Constraint ALTER TABLE {table} ADD CONSTRAINT {name} ( {text} )
> Drop table DROP TABLE {name}
> Drop Column ALTER TABLE {table} DROP COLUMN {column}
> Drop Index DROP INDEX {name}
> Drop view DROP VIEW {name}
> Drop Foreign Key ALTER TABLE {table} DROP CONSTRAINT {name}
> Drop Constraint ALTER TABLE {table} DROP CONSTRAINT {name}
> Drop Sequence DROP SEQUENCE {name}

So far ok.

> Change Column data type ALTER TABLE {table} MODIFY {column} {text}

ALTER TABLE {table} ALTER {column} TYPE {text}

In general syntax is:

ALTER TABLE table <operation> [, <operation> …];

<operation> = ...
| ALTER [COLUMN] column_name <alt_col_clause>
...

<alt_col_clause> = {TO new_col_name
| TYPE new_col_datatype
| POSITION new_col_position}

> Change Index to Primary Key ALTER INDEX {name} PRIMARY KEY

Not supported. The indexes that are used by constraints are managed by
the system. Also it is not possible to "bind" some index to constraint,
however it is possible to specify the name of the automatically created
index:

[ADD] [CONSTRAINT constraint-name]
<constraint-type> <constraint-definition>
[USING [ASC[ENDING] | DESC[ENDING]] INDEX index_name]

Not sure if it brings anything to you.

> Change Index to Unique ALTER INDEX {name} UNIQUE

Not supported. See above.

> Change Index to non-unique ALTER INDEX {name} NONUNIQUE

Not supported. See above.

> Change Foreign Key action ALTER CONSTRAINT {name} ON DELETE {delete_action} ON UPDATE {update_action}

Not supported, only via DROP/CREATE.

> Rename Index RENAME INDEX {name} TO {newname}

No explicit command. Possible via direct modification of system tables,
but I'm not sure you want to do it. So use DROP/CREATE.

> Rename Foreign Key RENAME {name} TO {newname}

Same as with index, use DROP/CREATE

> Rename Constraint ALTER TABLE {table} RENAME CONSTRAINT {name} TO {newname}

Same as with index, just DROP/CREATE.

> Set Table comment COMMENT ON TABLE {table} IS '{comment}'
> Set Column comment COMMENT ON COLUMN {table}.{name} IS '{comment}'

Ok.

> Set Column mandatory ALTER TABLE {table} MODIFY {column} NOT NULL

Not possible, only via direct system table manipulation, but might cause
database unrestorable (direct manipulation of system table does not
check the values, so you can have a database where all new values will
be not null, but old ones might contain nulls)

> Set Column unsigned ALTER TABLE {table} MODIFY {column} UNSIGNED
> Set Column autoincrement ALTER TABLE {table} MODIFY {column} AUTOINCREMENT

Both not supported.

> Set Column default ALTER TABLE {table} MODIFY {column} DEFAULT {defo}

ALTER TABLE {table} ALTER {column} SET DEFAULT {defo}

> Set Column check constraint ALTER TABLE {table} MODIFY {column} CONSTRAINT {name} CHECK ( {text} )

Only via

ALTER TABLE {table} DROP CONSTRAINT {name}
ALTER TABLE {table} ADD CONSTRAINT {name} CHECK ({column} {text})

> Set Column unnamed check constraint ALTER TABLE {table} MODIFY {column} CHECK ( {text} )

Same as above.

> Set Column enumeration ALTER TABLE {table} MODIFY {column} ENUMERATION ( {text} )

Not supported.

> Unset Column mandatory ALTER TABLE {table} MODIFY {column} NULL
> Unset Column unsigned ALTER TABLE {table} MODIFY {column} SIGNED
> Unset Column autoincrement ALTER TABLE {table} MODIFY {column} NOAUTOINCREMENT

All not supported

> Unset Column default ALTER TABLE {table} MODIFY {column} DEFAULT null

ALTER TABLE {table} ALTER {column} DROP DEFAULT

> Unset Column check constraint ALTER TABLE {table} MODIFY {column} DROP CONSTRAINT {table}

Only if you know the constraint name:

ALTER TABLE {table} DROP CONSTRAINT {constraint}

> Unset Column enumeration ALTER TABLE {table} MODIFY {column} NOENUMERATION

Not supported.

> default date function -> sysdate

CURRENT_DATE

> default time function -> systime

CURRENT_TIME

There is also CURRENT_TIMESTAMP

> date format -> dd-MMM-yy

Good question... I think it is dd-MMM-yyyy

> When connecting, we got this data types. Is this list correct ?

> <type name="BIGINT" java="-5" precision="NONE" />
> <type name="BLOB SUB_TYPE 0" java="-4" precision="NONE" />
> <type name="BLOB SUB_TYPE 1" java="-1" precision="NONE" />
> <type name="CHAR" java="1" precision="LENGTH" />
> <type name="NUMERIC" java="2" precision="DECIMAL" />
> <type name="DECIMAL" java="3" precision="PRECISION" />
> <type name="INTEGER" java="4" precision="NONE" />
> <type name="SMALLINT" java="5" precision="NONE" />
> <type name="FLOAT" java="6" precision="NONE" />
> <type name="DOUBLE PRECISION" java="8" precision="NONE" />
> <type name="VARCHAR" java="12" precision="LENGTH" />
> <type name="DATE" java="91" precision="NONE" />
> <type name="TIME" java="92" precision="NONE" />
> <type name="TIMESTAMP" java="93" precision="NONE" />
> <type name="BLOB SUB_TYPE <0 " java="2004" precision="NONE" />

All ok, except BLOB. The situation with BLOBs is following:

All BLOBs have so-called subtype. It tells the format of the binary data
stored in the BLOB. It was implemented in the days of InterBase and
provide a possibility to define so-called filters which convert data
from one format into another on-the-fly, e.g. one can store data in
GZIP-ed form, but they will be decompressed when accessed using
CAST(<value> BLOB SUB_TYPE -2) for example. The positive subtype values
are reserved for system use, negative - for user-defined subtypes. 0
means "raw data".

Why do I explain you this? Issue is that you won't see the "BLOB
SUB_TYPE < 0" via driver - it will be "BLOB SUB_TYPE -1" or "BLOB
SUB_TYPE -20", depending on the subtype.

So, I would treat all BLOBs as LONGVARBINARY (or BLOB, your choice)
except BLOB SUB_TYPE 1, which, strictly speaking, is LONGVARCHAR and its
contents you can display to the user.

Good news are that BLOB subtypes are used mainly by hardcore Firebird
programmers, which can be rarely met in the Java jungles.

Roman