Creating Index during CREATE TABLE doesn't work across different platforms

[rarog]

Currently if I use DDL to add the basic (not unique) Index as constraint via CreateTable or AlterTable it does it via INDEX inside the CREATE TABLE statement like this:

CREATE TABLE "example" ( 
    "id" BIGINT NOT NULL,
    "anothercol" VARCHAR(10) NOT NULL,
    PRIMARY KEY ("id"),
    INDEX "example_index"("anothercol")
)

This works since ages on MySQL (which is probably the original and best supported part via LAMP environment and origin of ZF) and seemingly on MSSQL since SQL Server 2008 according to documentation. It's not supported by SQLite, PostgreSQL, IBM DB/2 and seemingly neither Oracle.

However every database seems to support the separate CREATE INDEX command including MySQL and MSSQL.

This would be a working series of commands:

CREATE TABLE "example" ( 
    "id" BIGINT NOT NULL,
    "anothercol" VARCHAR(10) NOT NULL,
    PRIMARY KEY ("id")
);
CREATE INDEX "example_index"("anothercol")

By rewriting the decorators this should be achievable and I could try contribute these parts of codes if such chaining of commands wouldn't contradict the guidelines. Alternatively additional DDL commands just for creating and dropping index would be needed to achieve the same. Perhaps this would even be a nice compromise. Creating the additional commands and later just call them from the decorators.

[alextech]

Already did it at #231 A look over before I remove WIP would be helpful.

[alextech]

Wrong one. That was SQL server compatibility in general. Index specifically I did at #163 which if goes through can be applied to SQL now that I have decorators for it too.

[rarog]

Ah, thx. That fits more my expectations. The first look of it is good. I'll try apply it locally and test it tomorrow.

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#67.