<?xml version="1.0"?>
<!DOCTYPE database_configuration [
    <!ENTITY DATABASE_NAME            SYSTEM       "dbcfg_CentralConfigDatabaseName.txt"> 
    <!ENTITY SERVER_NAME              SYSTEM       "dbcfg_ServerName.txt">
	<!ENTITY POSTFIX                  SYSTEM       "dbcfg_CCPostfix.txt">
	<!ENTITY BUILDNO                  SYSTEM       "dbcfg_BuildNo.txt">
]>
<database_configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="DBConf.xsd">
    <database_type>SQLSVR</database_type>
    <category>centralconfig</category>
    <database_name>&DATABASE_NAME;</database_name>
    <table_postfix>&POSTFIX;</table_postfix>
  
    <database_installation>
        <sql>
            BEGIN TRY
                -- Create database
                IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '&DATABASE_NAME;')
                BEGIN
                    CREATE DATABASE [&DATABASE_NAME;]
                    EXEC [&DATABASE_NAME;].sys.sp_addextendedproperty 
                           @name=N'PP_Created', @value=N'Yes' ,
                           @level0type=N'SCHEMA', @level0name=N'dbo', 
                           @level1type=NULL, @level1name=NULL, 
                           @level2type=NULL, @level2name=NULL
                END
            END TRY
            BEGIN CATCH
            END CATCH
        </sql>
        <sql>
            USE [&DATABASE_NAME;]
        </sql>
        
        <schema_definition src="dbSchema_CentralConfig.xml"/>
    
        <!-- Initialize TM_PortalProtectServers -->
        <sql>
            IF EXISTS (SELECT * FROM [TM_PortalProtectServers&POSTFIX;] WHERE ServerName='&SERVER_NAME;')
                DELETE FROM [TM_PortalProtectServers&POSTFIX;] WHERE ServerName='&SERVER_NAME;'
            INSERT INTO [TM_PortalProtectServers&POSTFIX;] (ServerName) VALUES('&SERVER_NAME;')
        </sql>
        
        <!-- Initialize TM_GlobalSetting -->
        <sql>
		    BEGIN TRY
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='SchemaVersion')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, StrValue) VALUES ('SchemaVersion', '_INSTALL_MACRO_SCHEMA_VERSION')
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='WCHookStatus')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, StrValue) VALUES ('WCHookStatus', 'Disabled')
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='ToDeployAction')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, StrValue) VALUES ('ToDeployAction', 'Unspecified')
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='DeployingGuard')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, IntValue) VALUES ('DeployingGuard', 0)
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='ToDeployGuard')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, IntValue) VALUES ('ToDeployGuard', 0) 
                IF NOT EXISTS (SELECT Name FROM [TM_GlobalSetting&POSTFIX;] WHERE Name='QuarantineAction')
                    INSERT INTO [TM_GlobalSetting&POSTFIX;] (Name, IntValue) VALUES ('QuarantineAction', 0)
			END TRY
			BEGIN CATCH
			END CATCH
        </sql>
    </database_installation>
    
    <database_uninstallation>
        <sql>
            USE [&DATABASE_NAME;]
            DELETE FROM [TM_PortalProtectServers&POSTFIX;] WHERE ServerName='&SERVER_NAME;'
        </sql>
        <sql>
            IF NOT EXISTS (SELECT * FROM [TM_PortalProtectServers&POSTFIX;])
            BEGIN
                DROP TABLE [TM_GlobalSetting&POSTFIX;]
                DROP TABLE [TM_PortalProtectServers&POSTFIX;]        
                DROP TABLE [TM_RecycleBin&POSTFIX;]        
            END
        </sql>
        <sql>
            IF EXISTS (SELECT * FROM fn_listextendedproperty(N'PP_Created', N'SCHEMA', N'dbo', default, default, default, default))
               AND NOT EXISTS (select * from sys.sysobjects where xtype = 'U')
            BEGIN
                USE master
                ALTER DATABASE [&DATABASE_NAME;] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
                DROP DATABASE [&DATABASE_NAME;]
            END
        </sql>
    </database_uninstallation>
    
    <database_upgrade>
	    <sql>
		    USE [&DATABASE_NAME;]
			UPDATE [TM_PortalProtectServers&POSTFIX;] SET PortalProtectVersion='2.6.&BUILDNO;' WHERE ServerName = '&SERVER_NAME;' 
		</sql>
    </database_upgrade>
    <simple_commands>
        <command name="SQL_BEGIN_TRAN"                > Begin Tran;                                                                                                                </command>
        <command name="SQL_TRY_DEPLOYING"             > Update [TM_GlobalSetting&POSTFIX;] set IntValue = 1 where Name = N'DeployingGuard';                                                   </command>
        <command name="SQL_END_DEPLOYING"             > Update [TM_GlobalSetting&POSTFIX;] set IntValue = 0 where Name = N'DeployingGuard';                                                   </command>
        <command name="SQL_COMMIT_TRAN"               > Commit Tran;                                                                                                               </command>
        <command name="SQL_TRY_TO_SET_DEPLOYED_ACTION"> Update [TM_GlobalSetting&POSTFIX;] set IntValue = 1 where Name = N'ToDeployGuard';                                                    </command>
        <command name="SQL_END_TO_SET_DEPLOYED_ACTION"> Update [TM_GlobalSetting&POSTFIX;] set IntValue = 0 where Name = N'ToDeployGuard';                                                    </command>
        <command name="SQL_WCHOOK_STATUS_QUERY"       > Select Name, StrValue from [TM_GlobalSetting&POSTFIX;] where Name=N'WCHookStatus';                                                    </command>
        <command name="SQL_WCHOOK_STATUS_ENABLE"      > Update [TM_GlobalSetting&POSTFIX;] set StrValue=N'Enabled' where Name=N'WCHookStatus';                                                </command>
        <command name="SQL_WCHOOK_STATUS_DISABLE"     > Update [TM_GlobalSetting&POSTFIX;] set StrValue=N'Disabled' where Name=N'WCHookStatus';                                               </command>
        <command name="SQL_TO_DEPLOY_QUERY"           > Select Name, StrValue from [TM_GlobalSetting&POSTFIX;] where Name=N'ToDeployAction';                                                  </command>
        <command name="SQL_SET_TO_DEPLOY_ADD"         > Update [TM_GlobalSetting&POSTFIX;] set StrValue=N'Add' where Name=N'ToDeployAction';                                                  </command>
        <command name="SQL_SET_TO_DEPLOY_REMOVE"      > Update [TM_GlobalSetting&POSTFIX;] set StrValue=N'Remove' where Name=N'ToDeployAction';                                               </command>
        <command name="SQL_CLEAR_TO_DEPLOY"           > Update [TM_GlobalSetting&POSTFIX;] set StrValue=N'Unspecified' where Name=N'ToDeployAction';                                          </command>
        <command name="SQL_LAST_DEPLOY_QUERY"         > Select CONVERT(nvarchar(30), DateValue, 120) as LastDeployedTime from [TM_GlobalSetting&POSTFIX;] where Name=N'LastDeployedTime';     </command>
        <command name="SQL_CURRENT_TIME_QUERY"        > Select CONVERT(nvarchar(30), CURRENT_TIMESTAMP, 120) as CurrentTime from [TM_GlobalSetting&POSTFIX;];                                 </command>
        <command name="SQL_LAST_DEPLOY_INSERT"        > Insert into [TM_GlobalSetting&POSTFIX;](Name, DateValue) Values (N'LastDeployedTime', CURRENT_TIMESTAMP);                             </command>
        <command name="SQL_LAST_DEPLOY_UPDATE"        > Update [TM_GlobalSetting&POSTFIX;] Set DateValue=CURRENT_TIMESTAMP where Name=N'LastDeployedTime';                                    </command>
        <command name="SQL_UP_QUA">Update [TM_GlobalSetting&POSTFIX;] set IntValue=1 where name='QuarantineAction'</command>
        <command name="SQL_GET_SERVER_COUNT">SELECT COUNT(*) FROM [TM_PortalProtectServers&POSTFIX;];</command>
        <command name="INSERT_RECYCLEBIN_SQL_2010">insert into [TM_RecycleBin&POSTFIX;] values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)</command>
        <command name="INSERT_RECYCLEBIN_SQL">insert into [TM_RecycleBin&POSTFIX;] values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0x0,0,0x0)</command>
        <command name="DELETE_RECYCLEBIN_SQL">delete from [TM_RecycleBin&POSTFIX;] where DeleteTransactionId=?</command>
        <command name="SELECT_RECYCLEBIN_SQL">select * from [TM_RecycleBin&POSTFIX;] where DeleteTransactionId=?</command>
        <command name="SUMMARY_SIZE_SQL">select sum(Size)/1024 as size from [TM_RecycleBin&POSTFIX;] where BinID &lt;&gt; 0</command>
        <command name="GET_RECYCLE_ITEM">select * from [TM_RecycleBin&POSTFIX;] where DeleteTransactionId=</command>
        <command name="UPDATE_SERVER_VER">UPDATE [TM_PortalProtectServers&POSTFIX;] SET PortalProtectVersion='%s.%s' WHERE ServerName = '%s'</command>
        <name_conversion src="dbSchema_CentralConfig.xml"/>
    </simple_commands>
</database_configuration>
