/************************************************************/
    /*****       SqlDataProvider                            *****/
    /*****       Install/Upgrade for UserDefinedTable 3.4.0 *****/
    /*****       [sleupold]  01/22/2007                     *****/
    /*****       [sleupold]  02/23/2007                     *****/
    /*****                                                  *****/
    /***** Note: To manually execute this script you must   *****/
    /*****       perform a search AND replace operation     *****/
    /*****       for {databaseOwner} AND {objectQualifier}  *****/
    /*****       or use 'SQL' FROM Host Menu AND run this.  *****/
    /*****                                                  *****/
    /*****                                                  *****/
    /************************************************************/

    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO


    /** check for compatible DNN version **/
    IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'UDT_Install_Semaphore') AND OBJECTPROPERTY(id, N'IsTable') = 1)
    DROP Table UDT_Install_Semaphore
    GO

    CREATE TABLE UDT_Install_Semaphore (value INT PRIMARY KEY, flag BIT NOT Null)
    GO

    DECLARE @dnnVer INT 
    SET @dnnver = (SELECT TOP 1 Major*10000+Minor*100+Build FROM {databaseOwner}[{objectQualifier}Version] ORDER BY Major DESC, Minor DESC, Build DESC)
    IF @dnnver < 30305 OR ( @dnnver >= 40000 AND @dnnver < 40305)
        BEGIN
            INSERT INTO UDT_Install_Semaphore (value, Flag) VALUES (0, 0)
            RAISERROR ('Incompatible DotNetNuke framework version installed. Please upgrade to V. 3.3.5+ or V. 4.3.5+ or re-install previous version of this module!', 16, 1)
        END
     ELSE
        INSERT INTO UDT_Install_Semaphore (value, Flag) VALUES (1, 0)
    GO

    IF (SELECT value FROM UDT_Install_Semaphore) = 1  AND (SELECT COLUMNPROPERTY( OBJECT_ID(N'{databaseOwner}[{objectQualifier}UserDefinedFields]'),'Searchable','AllowsNull')) IS NULL   
       ALTER TABLE {databaseOwner}[{objectQualifier}UserDefinedFields] 
       ADD Searchable BIT DEFAULT 0 Not Null
    GO
    
    IF (SELECT value FROM UDT_Install_Semaphore) = 1  AND (SELECT COLUMNPROPERTY( OBJECT_ID(N'{databaseOwner}[{objectQualifier}UserDefinedFields]'),'ShowOnEdit','AllowsNull')) IS NULL   
    BEGIN
        UPDATE UDT_Install_Semaphore SET flag = 1
        ALTER TABLE {databaseOwner}[{objectQualifier}UserDefinedFields] 
        ADD ShowOnEdit BIT DEFAULT 1 Not Null
    END
    GO

    IF (SELECT Flag FROM UDT_Install_Semaphore) = 1    
        UPDATE {databaseOwner}[{objectQualifier}UserDefinedFields] 
        SET ShowOnEdit = 0 
        WHERE (FieldType Like 'Created%' OR FieldType Like 'Changed%') AND visible = 0

    IF (SELECT value FROM UDT_Install_Semaphore) = 1 AND exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UserDefinedTable_DeleteRows]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_DeleteRows]
    GO

    CREATE PROCEDURE {databaseOwner}{objectQualifier}UserDefinedTable_DeleteRows
        @ModuleID    int
    AS
    DELETE 
    FROM {databaseOwner}{objectQualifier}UserDefinedRows
    WHERE  ModuleId=@ModuleID
    GO

    /* replace stored procedures with updated versions: */

    IF (SELECT value FROM UDT_Install_Semaphore) = 1 AND exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UserDefinedTable_AddField]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_AddField]
    GO

    CREATE PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_AddField]
    @ModuleId       INT,
    @FieldTitle     NVARCHAR(50),
    @BeforePos      INT,
    @HelpText       NVARCHAR(512),
    @Required       BIT,
    @FieldType      VARCHAR(20),
    @Default       NVARCHAR(2000),
    @Visible        BIT,
    @ShowOnEdit     BIT,
    @Searchable     BIT,
    @InputSettings  NVARCHAR(2000),
    @OutputSettings NVARCHAR(2000),
    @NormalizeFlag  BIT, 
    @ValidationRule NVARCHAR(512),
    @ValidationMessage NVARCHAR(512)
    AS
    DECLARE @InsPos INT
    /* find insert position and adjust field order of subsequent fields: */
    SET @InsPos = (SELECT MAX(FieldOrder) AS MaxPos FROM {databaseOwner}{objectQualifier}UserDefinedFields WHERE ModuleID = @ModuleID GROUP BY ModuleID) + 1 
    IF @InsPos IS Null SET @InsPos = 1
    IF @BeforePos Is Null SET @BeforePos = @InsPos
    IF @InsPos > @BeforePos BEGIN
            UPDATE [{objectQualifier}UserDefinedFields]
            SET FieldOrder = FieldOrder + 1
            WHERE ModuleID = @ModuleID AND FieldOrder >= @BeforePos
            SET  @InsPos = @BeforePos 
        END
        
    INSERT INTO {databaseOwner}[{objectQualifier}UserDefinedFields] ( 
      ModuleId,
      FieldTitle,
      HelpText,
      Required,
      FieldType,
      FieldOrder,
      [Default],
      Visible,
      ShowOnEdit,
      Searchable,
      InputSettings,
      OutputSettings, 
      NormalizeFlag,
      ValidationRule,
      ValidationMessage 
    )
    VALUES (
      @ModuleId,
      @FieldTitle,
      @HelpText,
      @Required,
      @FieldType,
      @InsPos,
      @Default,
      @Visible,
      @ShowOnEdit,
      @Searchable,
      @InputSettings,
      @OutputSettings,
      @NormalizeFlag,
      @ValidationRule, 
      @ValidationMessage
    )
    SELECT SCOPE_IDENTITY()
    GO

    IF (SELECT value FROM UDT_Install_Semaphore) = 1 AND exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UserDefinedTable_UpdateField]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_UpdateField]
    GO

    CREATE PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_UpdateField]
    @UserDefinedFieldId   INT,
    @FieldTitle           NVARCHAR(50),
    @HelpText             NVARCHAR(512),
    @Required             BIT,
    @FieldType            VARCHAR(20),
    @default			  NVARCHAR(2000), 
    @Visible              BIT,
    @ShowOnEdit           BIT,
    @Searchable			  BIT,
    @InputSettings        NVARCHAR(2000),
    @OutputSettings       NVARCHAR(2000),
    @NormalizeFlag        BIT,
    @ValidationRule       NVARCHAR(512),
    @ValidationMessage    NVARCHAR(512)

    AS
    UPDATE {databaseOwner}[{objectQualifier}UserDefinedFields]
    SET    FieldTitle = @FieldTitle,
           HelpText = @HelpText,
           Required = @Required,
           FieldType = @FieldType,
           [default] = @default,
           Visible = @Visible,
           ShowOnEdit = @ShowOnEdit,
           Searchable = @Searchable,
           InputSettings = @InputSettings, 
           OutputSettings = @OutputSettings, 
           NormalizeFlag = @NormalizeFlag,
           ValidationRule = @ValidationRule,
           ValidationMessage = @ValidationMessage
    WHERE UserDefinedFieldId = @UserDefinedFieldId
    GO

    IF (SELECT value FROM UDT_Install_Semaphore) = 1 AND exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UserDefinedTable_GetField]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_GetField]
    GO

    CREATE PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_GetField]
    @UserDefinedFieldId  INT
    AS
    SELECT UserDefinedFieldId,
        ModuleID,
        FieldTitle,
        HelpText,
        Required,
        FieldOrder,
        FieldType, 
        [default],
        Visible,
        ShowOnEdit,
        Searchable,
        InputSettings,
        OutputSettings, 
        NormalizeFlag,
        ValidationRule,
        ValidationMessage
    FROM {databaseOwner}[{objectQualifier}UserDefinedFields]
    WHERE UserDefinedFieldId = @UserDefinedFieldId
    GO

    IF (SELECT value FROM UDT_Install_Semaphore) = 1 AND exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}UserDefinedTable_GetFields]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_GetFields]
    GO

    CREATE PROCEDURE {databaseOwner}[{objectQualifier}UserDefinedTable_GetFields]
    @ModuleId  INT
    AS
    SELECT UserDefinedFieldId,
           ModuleID,
           FieldTitle,
           HelpText,
           Required,
           FieldOrder,
           FieldType,
           [default],
           Visible,
           ShowOnEdit,
           Searchable,
           InputSettings,
           OutputSettings, 
           NormalizeFlag,
           ValidationRule,
           ValidationMessage
    FROM {databaseOwner}[{objectQualifier}UserDefinedFields]
    WHERE  ModuleId = @ModuleId
    ORDER BY FieldOrder
    GO

    /** delete version table **/
    DROP TABLE UDT_Install_Semaphore 
    GO