/* MS SQL
 * SOS GmbH, 2018-04-20, SP
 */
SET ANSI_DEFAULTS ON;

/* INVENTORY_CALENDAR_USAGE change type to IDENTITY if column exists and not already IDENTITY */
IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'ID' AND TABLE_NAME = 'INVENTORY_CALENDAR_USAGE' AND
    COLUMNPROPERTY(object_id('INVENTORY_CALENDAR_USAGE'), 'ID', 'isIdentity') = 0)
    BEGIN
        DROP TABLE IF EXISTS INVENTORY_CALENDAR_USAGE;
        CREATE TABLE INVENTORY_CALENDAR_USAGE(
            "ID"                    NUMERIC(11)   IDENTITY(1,1) NOT NULL,
            "INSTANCE_ID"           NUMERIC(11)   NOT NULL,
            "CALENDAR_ID"           NUMERIC(11)   NOT NULL,
            "OBJECT_TYPE"           VARCHAR(10)   NOT NULL,
            "PATH"                  VARCHAR(255)  NOT NULL,
            "EDITED"                NUMERIC(1)    DEFAULT 0 NOT NULL,
            "CONFIGURATION"         NTEXT         NULL,
            "CREATED"               DATETIME      NOT NULL,
            "MODIFIED"              DATETIME      NOT NULL,
            CONSTRAINT INVENTORY_CU_UNIQUE UNIQUE ("INSTANCE_ID", "CALENDAR_ID", "OBJECT_TYPE", "PATH"),
            PRIMARY KEY ("ID")
        );
    END;
COMMIT;
GO