We decided to use a database for logging for this new project I'm working on, which presented us with a problem, how to automate the installation of the Logging database? The answer was Wix, of course, but how?
I believe there are two ways of using Wix to install a database. This is my favourite way, which simply entails using sqlcmd to run a sql script that creates a DB.
Product.wxs file:
<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"
xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">
<Product Id="702881b5-9a64-4ab8-ab47-f3babcd950a2"
Name="Logging Database" Language="1033"
Version="$(var.versionNumber)"
Manufacturer="SEPH"
UpgradeCode="42b7872b-78c8-4a0b-abcd-28a30c9804ab">
<Package InstallerVersion="200" Compressed="yes" Platform="x64" InstallScope="perMachine"/>
<Media Id="1" Cabinet="media1.cab" EmbedCab="yes" />
<Property Id="SQLBINDIR">
<RegistrySearch Id="SqlBinDir"
Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup"
Name="Path"
Type="raw"
Win64="yes"/>
</Property>
<Condition Message="Microsoft SQL Server 2012 needs to be installed before this installer can run">
<![CDATA[]]>
</Condition>
<Directory Id="TARGETDIR" Name="SourceDir">
<Directory Id="INSTALLLOCATION" Name="Logging Database">
<Component Id="Database" Guid="a8dc2Fcd-087d-e393-b059-c67877e51b8a">
<File Id="LoggingDBCreationScript" Source="SQLScripts\LoggingDatabase.sql"/>
<RemoveFile Id ="RemoveLoggingInstall.log" Name="LoggingInstall.log" On="uninstall" />
</Component>
</Directory>
</Directory>
<Feature Id="ProductFeature" Title="Database" Level="1">
<Feature Id="Database" Title="Logging Database" Description="Logging Database" Level="1" Display="expand" AllowAdvertise="no"
ConfigurableDirectory="INSTALLLOCATION" Absent="allow">
<ComponentRef Id="Database"/>
</Feature>
</Feature>
<InstallExecuteSequence>
install the DB
<Custom Action="logging.cmd" After="InstallFiles" >
<![CDATA[]]>
</Custom>
<Custom Action="logging" After="logging.cmd">
<![CDATA[]]>
</Custom>
</InstallExecuteSequence>
<UI>
<UIRef Id="GUI"/>
<UIRef Id="WixUI_ErrorProgressText" />
</UI>
<CustomAction Id="logging.cmd" Property="logging"
Value=""[SQLBINDIR]sqlcmd.exe" -E -S "[SQLSERVER]" -i "[#LoggingDBCreationScript]" -o "[INSTALLLOCATION]LoggingInstall.log""/>
<CustomAction Id="logging" BinaryKey="WixCA" DllEntry="CAQuietExec" Return="check" Execute="deferred" Impersonate="yes" />
</Product>
</Wix>
Gui.wxs file:
<?xml version="1.0" encoding="utf-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
<Fragment>
<WixVariable Id="WixUIBannerBmp" Value="Images\WixTopBanner.jpg" />
<WixVariable Id="WixUIDialogBmp" Value="Images\WixSideBanner.jpg" />
<UI Id="GUI">
<TextStyle Id="WixUI_Font_Normal" FaceName="Tahoma" Size="8" />
<TextStyle Id="WixUI_Font_Bigger" FaceName="Tahoma" Size="12" />
<TextStyle Id="WixUI_Font_Title" FaceName="Tahoma" Size="9" Bold="yes" />
<Property Id="DefaultUIFont" Value="WixUI_Font_Normal" />
<Property Id="WixUI_Mode" Value="FeatureTree" />
<DialogRef Id="BrowseDlg" />
<DialogRef Id="DiskCostDlg" />
<DialogRef Id="ErrorDlg" />
<DialogRef Id="FatalError" />
<DialogRef Id="FilesInUse" />
<DialogRef Id="MsiRMFilesInUse" />
<DialogRef Id="PrepareDlg" />
<DialogRef Id="ProgressDlg" />
<DialogRef Id="ResumeDlg" />
<DialogRef Id="UserExit" />
<Publish Dialog="ExitDialog" Control="Finish" Event="EndDialog" Value="Return" Order="999">1</Publish>
<Publish Dialog="WelcomeDlg" Control="Next" Event="NewDialog" Value="CustomizeDlg">1</Publish>
<Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="1">Installed</Publish>
<Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="WelcomeDlg" Order="2">NOT Installed</Publish>
<Publish Dialog="CustomizeDlg" Control="Next" Event="NewDialog" Value="DatabaseDlg">1</Publish>
<Publish Dialog="DatabaseDlg" Control="Back" Event="NewDialog" Value="CustomizeDlg">1</Publish>
<Publish Dialog="DatabaseDlg" Control="Next" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
<Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="DatabaseDlg" Order="1">NOT Installed OR WixUI_InstallMode = "Change"</Publish>
<Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="2">Installed</Publish>
<Publish Dialog="MaintenanceWelcomeDlg" Control="Next" Event="NewDialog" Value="MaintenanceTypeDlg">1</Publish>
<Publish Dialog="MaintenanceTypeDlg" Control="ChangeButton" Event="NewDialog" Value="CustomizeDlg">1</Publish>
<Publish Dialog="MaintenanceTypeDlg" Control="RepairButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
<Publish Dialog="MaintenanceTypeDlg" Control="RemoveButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
<Publish Dialog="MaintenanceTypeDlg" Control="Back" Event="NewDialog" Value="MaintenanceWelcomeDlg">1</Publish>
<Property Id="ARPNOMODIFY" Value="1" />
<Dialog Id="DatabaseDlg" Width="370" Height="270" Title="[ProductName] Setup">
<Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes" Text="{\WixUI_Font_Title}Database Settings" />
<Control Id="Description" Type="Text" X="25" Y="23" Width="280" Height="15" Transparent="yes" NoPrefix="yes" Text="Enter Database Details" />
<Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="!(loc.InstallDirDlgBannerBitmap)" />
<Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
<Control Id="DBServerLabel" Type="Text" X="20" Y="65" Width="290" Height="13" Text="Logging DB Database Server Instance:" />
<Control Id="DBServer" Type="Edit" X="20" Y="77" Width="320" Height="15" Property="SQLSERVER" ToolTip="Change for Test Environments"/>
<Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
<Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="Next">
<Condition Action="disable">
(SQLSERVER = "")
</Condition>
<Condition Action="enable">
<![CDATA[]]>
</Condition>
</Control>
<Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="Back" />
<Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="Cancel">
<Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
</Control>
</Dialog>
</UI>
<UIRef Id="WixUI_Common" />
</Fragment>
</Wix>
Sql Script
USE [master]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Logging')
DROP DATABASE [Logging]
GO
CREATE DATABASE [Logging]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'Logging', N'autoclose', N'false'
GO
exec sp_dboption N'Logging', N'bulkcopy', N'false'
GO
exec sp_dboption N'Logging', N'trunc. log', N'false'
GO
exec sp_dboption N'Logging', N'torn page detection', N'true'
GO
exec sp_dboption N'Logging', N'read only', N'false'
GO
exec sp_dboption N'Logging', N'dbo use', N'false'
GO
exec sp_dboption N'Logging', N'single', N'false'
GO
exec sp_dboption N'Logging', N'autoshrink', N'false'
GO
exec sp_dboption N'Logging', N'ANSI null default', N'false'
GO
exec sp_dboption N'Logging', N'recursive triggers', N'false'
GO
exec sp_dboption N'Logging', N'ANSI nulls', N'false'
GO
exec sp_dboption N'Logging', N'concat null yields null', N'false'
GO
exec sp_dboption N'Logging', N'cursor close on commit', N'false'
GO
exec sp_dboption N'Logging', N'default to local cursor', N'false'
GO
exec sp_dboption N'Logging', N'quoted identifier', N'false'
GO
exec sp_dboption N'Logging', N'ANSI warnings', N'false'
GO
exec sp_dboption N'Logging', N'auto create statistics', N'true'
GO
exec sp_dboption N'Logging', N'auto update statistics', N'true'
GO
use [Logging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](64) NOT NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CategoryLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CategoryLog](
[CategoryLogID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[LogID] [int] NOT NULL,
CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED
(
[CategoryLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Log]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Log](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NULL,
[Priority] [int] NOT NULL,
[Severity] [nvarchar](32) NOT NULL,
[Title] [nvarchar](256) NULL,
[Timestamp] [datetime] NOT NULL,
[MachineName] [nvarchar](32) NOT NULL,
[AppDomainName] [nvarchar](512) NOT NULL,
[ProcessID] [nvarchar](256) NOT NULL,
[ProcessName] [nvarchar](512) NOT NULL,
[ThreadName] [nvarchar](512) NULL,
[Win32ThreadId] [nvarchar](128) NULL,
[Message] [nvarchar](2500) NULL,
[FormattedMessage] [ntext] NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InsertCategoryLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE InsertCategoryLog
@CategoryID INT,
@LogID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatLogID INT
SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
IF @CatLogID IS NULL
BEGIN
INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)
RETURN @@IDENTITY
END
ELSE RETURN @CatLogID
END
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[AddCategory]
-- Add the parameters for the function here
@CategoryName nvarchar(64),
@LogID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatID INT
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO Category (CategoryName) VALUES(@CategoryName)
SELECT @CatID = @@IDENTITY
END
EXEC InsertCategoryLog @CatID, @LogID
RETURN @CatID
END
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClearLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE ClearLogs
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM CategoryLog
DELETE FROM [Log]
DELETE FROM Category
END
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WriteLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
/****** Object: Stored Procedure dbo.WriteLog Script Date: 10/1/2004 3:16:36 PM ******/
CREATE PROCEDURE [dbo].[WriteLog]
(
@EventID int,
@Priority int,
@Severity nvarchar(32),
@Title nvarchar(256),
@Timestamp datetime,
@MachineName nvarchar(32),
@AppDomainName nvarchar(512),
@ProcessID nvarchar(256),
@ProcessName nvarchar(512),
@ThreadName nvarchar(512),
@Win32ThreadId nvarchar(128),
@Message nvarchar(1500),
@FormattedMessage ntext,
@LogId int OUTPUT
)
AS
INSERT INTO [Log] (
EventID,
Priority,
Severity,
Title,
[Timestamp],
MachineName,
AppDomainName,
ProcessID,
ProcessName,
ThreadName,
Win32ThreadId,
Message,
FormattedMessage
)
VALUES (
@EventID,
@Priority,
@Severity,
@Title,
@Timestamp,
@MachineName,
@AppDomainName,
@ProcessID,
@ProcessName,
@ThreadName,
@Win32ThreadId,
@Message,
@FormattedMessage)
SET @LogID = @@IDENTITY
RETURN @LogID
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Category') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY( [CategoryID])
REFERENCES [dbo].[Category] ( [CategoryID])
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK_CategoryLog_Log') AND parent_obj = OBJECT_ID(N'[dbo].[CategoryLog]'))
ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY( [LogID])
REFERENCES [dbo].[Log] ( [LogID])
GO
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go
DECLARE @bErrors as bit
BEGIN TRANSACTION
SET @bErrors = 0
CREATE NONCLUSTERED INDEX [ixCategoryLog] ON [dbo].[CategoryLog] ([LogID] ASC, [CategoryID] ASC )
IF( @@error <> 0 ) SET @bErrors = 1
IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
