using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Xml;
using System.Xml.Schema;
using System.IO;

namespace Orciid.Core
{
	/// <summary>
	/// The Microsoft SQL Server connector class
	/// </summary>
	/// <remarks>
	/// This connection class is compatible with Microsoft SQL Server 2000.
	/// </remarks>
	public class SQLServerDBConnection: 
		DBConnection
	{
		/// <summary>
		/// The actual database connection object
		/// </summary>
		private SqlConnection connection;

		/// <summary>
		/// Constructor
		/// </summary>
		/// <remarks>
		/// Creates a new SQL Server connection and optionally opens the connection
		/// </remarks>
		/// <param name="cs">The connection string used to create the connection</param>
		/// <param name="open">Boolean flag specifying if connection should automatically be opened</param>
		public SQLServerDBConnection(string cs, bool open):
			base(cs)
		{
			connection = new SqlConnection(cs);
			if (open)
				connection.Open();
		}

		/// <summary>
		/// Check database version
		/// </summary>
		/// <remarks>
		/// This method checks if the database specified by the connection string fulfills the
		/// requirements of this code version and if not, attempt to upgrade the database.
		/// </remarks>
		/// <param name="cs">The database connection string</param>
		/// <returns><c>true</c> if the database fulfills the requirements,
		/// <c>false</c> otherwise.</returns>
		protected override bool CheckDatabaseVersion(string cs)
		{
			SqlConnection conn = new SqlConnection(cs);
			try
			{
				conn.Open();
				DataSet s = new DataSet();
				new SqlDataAdapter("SELECT name FROM dbo.sysobjects " +
					"WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1", conn).Fill(s);
				// sometimes a system table called dtproperties is listed as a
				// user table, so if there is at most one table, assume the database
				// is empty and needs to be populated
				if (s.Tables[0].Rows.Count <= 1)
				{
					CreateDatabaseTables(conn);	
					PopulateDatabase(conn);
				}

				string version;
				do
				{
					s = new DataSet();
					new SqlDataAdapter("SELECT Version FROM DatabaseVersion", conn).Fill(s);
					DataTable table = s.Tables[0];
					if (table.Rows.Count == 1)
					{
						version = DataToString(table.Rows[0]["Version"], null);
						if (version == null)
							throw new CoreException("Cannot retrieve database version [1]");
						if (version != RequiredDatabaseVersion)
							UpgradeDatabaseFromVersion(version, conn);
						else
							return true;
					}
					else
						throw new CoreException("Cannot retrieve database version [2]");
				} while (true);
			}
			catch (Exception e)
			{
				throw new Exception("Database check failed", e);
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}

		private void UpgradeDatabaseFromVersion00001(SqlConnection conn)
		{
			new SqlCommand(@"
						CREATE TABLE [dbo].[RemoteCollectionAccess] (
						[ID] [int] IDENTITY (1, 1) NOT NULL ,
						[CollectionID] [int] NOT NULL,
						[UserID] [int] NOT NULL,
						[Subnet] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
						[Mask] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
						[CacheTimeSpan] [int] NOT NULL,
						[ExpirationTimeSpan] [int] NOT NULL,
						[SearchResults] [int] NOT NULL
						) ON [PRIMARY]",		
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						ALTER TABLE [dbo].[RemoteCollectionAccess] WITH NOCHECK ADD 
						CONSTRAINT [PK_RemoteCollectionAccess_1] PRIMARY KEY  CLUSTERED 
						(
						[ID]
						)  ON [PRIMARY] ",
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						ALTER TABLE [dbo].[Images] ADD
						[UserID] [int] NULL,
						[Flags] [int] NULL,
						[CachedUntil] [datetime] NULL,
						[Expires] [datetime] NULL,
						[RemoteID] [nvarchar] (255) NULL,
						[RecordStatus] [int] NULL,
						[MimeType] [nvarchar] (255) NULL",
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						CREATE INDEX [IX_Images_UserID] ON [dbo].[Images]([UserID]) ON [PRIMARY]",
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						CREATE INDEX [IX_Images_RemoteID] ON [dbo].[Images]([RemoteID]) ON [PRIMARY]",
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						ALTER TABLE [dbo].[Collections] ADD
						[RemoteUrl] [nvarchar] (255) NULL,
						[FullImageBGColor] [int] DEFAULT 0,
						[MediumImageBGColor] [int] DEFAULT 0,
						[ThumbImageBGColor] [int] DEFAULT 0",
				conn).ExecuteNonQuery();

			int pcid = Convert.ToInt32(
				new SqlCommand(@"SELECT ID FROM Collections WHERE Type='P'", 
				conn).ExecuteScalar());

			//copy all personal images

			string[] dependenttables = 
					{ 
						"FieldData",
						"DateFieldsIndex",
						"FavoriteImages",
						"ImageAnnotations",
						"Slides",
						"RecordMaintenance"
					};

			SqlDataAdapter a = new SqlDataAdapter(
				@"SELECT ID,UserID,Shared,Created,Modified FROM PersonalImages", conn);
			DataSet s = new DataSet();
			a.Fill(s);

			foreach (DataRow row in s.Tables[0].Rows)
			{
				int id = Convert.ToInt32(row["ID"]);
				int userid = Convert.ToInt32(row["UserID"]);
				bool shared = DataToBool(row["Shared"], false);
				DateTime created = DataToDateTime(row["Created"], DateTime.Now);
				DateTime modified = DataToDateTime(row["Modified"], DateTime.Now);

				new SqlCommand(String.Format(@"
							INSERT INTO Images (CollectionID,UserID,Resource,Created,Modified,Flags)
							VALUES ({0},{1},'{2}','{3}','{4}',{5})",
					pcid, 
					userid, 
					String.Format("{0}.jpg", id), 
					DateToDBFormatString(created),
					DateToDBFormatString(modified),
					(shared ? 1 : 0)), 
					conn).ExecuteNonQuery();

				int lastid = Decimal.ToInt32((System.Decimal)(new SqlCommand(
					@"SELECT @@IDENTITY FROM Images", conn).ExecuteScalar()));

				foreach (string table in dependenttables)
					new SqlCommand(String.Format(@"UPDATE {2} SET ImageID={0} WHERE ImageID={1} AND CollectionID={3}",
						lastid, id, table, pcid), conn).ExecuteNonQuery();
			}
					
			new SqlCommand(@"DROP TABLE PersonalImages", conn).ExecuteNonQuery();

			new SqlCommand(@"UPDATE Collections SET Type='I' WHERE Type='P'", conn).ExecuteNonQuery();

			new SqlCommand(String.Format(@"
						UPDATE AccessControl
						SET GrantPriv=(GrantPriv|{0})&~{1} 
						WHERE ObjectType='C' and ObjectID={2}",
				(int)(Privilege.PersonalImages | Privilege.ShareImages),
				(int)Privilege.ModifyImages,
				pcid), conn).ExecuteNonQuery();

			new SqlCommand(@"UPDATE DatabaseVersion SET Version='00002'", conn).ExecuteNonQuery();								
		}
		
		private void UpgradeDatabaseFromVersion00002(SqlConnection conn)
		{
			new SqlCommand(@"
						ALTER TABLE FieldData ADD
						StartDate int default NULL,
						EndDate int default NULL,
						OriginalValue [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL",
				conn).ExecuteNonQuery();

			new SqlCommand(@"
						UPDATE FieldData
						SET FieldData.StartDate=DateFieldsIndex.StartDate,
						FieldData.EndDate=DateFieldsIndex.EndDate,
						FieldData.OriginalValue=DateFieldsIndex.OriginalValue
						FROM DateFieldsIndex
						WHERE FieldData.ImageID=DateFieldsIndex.ImageID
						AND FieldData.CollectionID=DateFieldsIndex.CollectionID
						AND FieldData.FieldID=DateFieldsIndex.FieldID
						AND FieldData.FieldInstance=DateFieldsIndex.FieldInstance",
				conn).ExecuteNonQuery();

			new SqlCommand(@"DROP TABLE DateFieldsIndex", conn).ExecuteNonQuery();

			new SqlCommand(@"ALTER TABLE Users 
							ALTER COLUMN Email nvarchar(100) NULL", conn).ExecuteNonQuery();

			new SqlCommand(@"ALTER TABLE UserGroups 
							ALTER COLUMN Title nvarchar(255) NOT NULL", conn).ExecuteNonQuery();
			
			new SqlCommand(@"ALTER TABLE UserGroupAttributes 
							DROP CONSTRAINT [PK_UserGroupAttributes]", conn).ExecuteNonQuery();
			
			new SqlCommand(@"ALTER TABLE UserGroupAttributes 
							ALTER COLUMN Attribute nvarchar(255) NOT NULL", conn).ExecuteNonQuery();

			new SqlCommand(@"ALTER TABLE [dbo].[UserGroupAttributes] WITH NOCHECK ADD 
							CONSTRAINT [PK_UserGroupAttributes] PRIMARY KEY  CLUSTERED 
							(
							[GroupID],
							[Attribute],
							[AttributeValueInstance]
							)  ON [PRIMARY]", conn).ExecuteNonQuery();


			new SqlCommand(@"ALTER TABLE UserGroupAttributes 
							ALTER COLUMN AttributeValue nvarchar(255) NOT NULL", conn).ExecuteNonQuery();

			new SqlCommand(@"ALTER TABLE Collections
							ALTER COLUMN ResourcePath nvarchar(255) NOT NULL", conn).ExecuteNonQuery();

			new SqlCommand(@"ALTER TABLE CollectionGroups 
							ALTER COLUMN Title nvarchar(255) NOT NULL", conn).ExecuteNonQuery();

			new SqlCommand(@"UPDATE DatabaseVersion SET Version='00003'", conn).ExecuteNonQuery();
		}

		private void UpgradeDatabaseFromVersion00003(SqlConnection conn)
		{
			try
			{
				new SqlCommand(@"
		CREATE INDEX IX_accesscontrol_ObjectType_ObjectID ON accesscontrol (ObjectType,ObjectID);
		CREATE INDEX IX_accesscontrol_UserID ON accesscontrol (UserID);
		CREATE INDEX IX_accesscontrol_GroupID ON accesscontrol (GroupID);
		CREATE INDEX IX_recordmaintenance_ImageID_CollectionID ON recordmaintenance (ImageID,CollectionID);
		CREATE INDEX IX_slides_ImageID_CollectionID ON slides (ImageID,CollectionID);
		CREATE INDEX IX_slideshows_UserID ON slideshows (UserID);
		CREATE INDEX IX_slideshows_FolderID ON slideshows (FolderID);
				", conn).ExecuteNonQuery();
			}
			catch
			{
				// ignore any error messages, since some users may have already added the indexes
			}

			new SqlCommand(@"UPDATE DatabaseVersion SET Version='00004'", conn).ExecuteNonQuery();
		}

		private void UpgradeDatabaseFromVersion00004(SqlConnection conn)
		{
			new SqlCommand(@"
				INSERT INTO Collections (Title,Description,ResourcePath,Type,
				UsageAgreement,FullImageHeight,FullImageWidth,FullImageQuality,
				FullImageFixedSize,MediumImageHeight,MediumImageWidth,MediumImageQuality,
				MediumImageFixedSize,ThumbImageHeight,ThumbImageWidth,
				ThumbImageQuality,ThumbImageFixedSize,GroupID,CacheRestriction) 
				VALUES ('My Images','Your personal images','n/a','P',NULL,
				0,0,0,0,0,0,0,0,0,0,0,0,0,2)", conn).ExecuteNonQuery();

			new SqlCommand(@"
				INSERT INTO Collections (Title,Description,ResourcePath,Type,
				UsageAgreement,FullImageHeight,FullImageWidth,FullImageQuality,
				FullImageFixedSize,MediumImageHeight,MediumImageWidth,MediumImageQuality,
				MediumImageFixedSize,ThumbImageHeight,ThumbImageWidth,
				ThumbImageQuality,ThumbImageFixedSize,GroupID,CacheRestriction) 
				VALUES ('Shared Images','Shared images belonging to other users','n/a','S',NULL,
				0,0,0,0,0,0,0,0,0,0,0,0,0,2)", conn).ExecuteNonQuery();

			new SqlCommand(@"UPDATE DatabaseVersion SET Version='00005'", conn).ExecuteNonQuery();
		}

		private void UpgradeDatabaseFromVersion00005(SqlConnection conn)
		{
			new SqlCommand(@"
						ALTER TABLE SavedSearches ADD
						LastRun [datetime] default NULL,
						NewRecords int default NULL",
				conn).ExecuteNonQuery();

			new SqlCommand(@"UPDATE DatabaseVersion SET Version='00006'", conn).ExecuteNonQuery();
		}

		private void UpgradeDatabaseFromVersion(string version, SqlConnection conn)
		{
			FailIfNotUpgradeable();
			switch (version)
			{
				case "00001":
					UpgradeDatabaseFromVersion00001(conn);
					break;
				case "00002":
					UpgradeDatabaseFromVersion00002(conn);
					break;
				case "00003":
					UpgradeDatabaseFromVersion00003(conn);
					break;
				case "00004":
					UpgradeDatabaseFromVersion00004(conn);
					break;
				case "00005":
					UpgradeDatabaseFromVersion00005(conn);
					break;
				default:
					throw new CoreException(
						String.Format("Database upgrade from version {0} not supported", version));
			}
		}

		private void CreateDatabaseTables(SqlConnection conn)
		{
			string[] commands = new string[] {
			
												 @"CREATE TABLE [dbo].[AccessControl] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[UserID] [int] NULL ,
[GroupID] [int] NULL ,
[GrantPriv] [int] NULL ,
[DenyPriv] [int] NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Announcements] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[Posted] [datetime] NOT NULL ,
[Title] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Text] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Link] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LinkTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[CollectionGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Collections] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResourcePath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UsageAgreement] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullImageHeight] [int] NOT NULL ,
[FullImageWidth] [int] NOT NULL ,
[FullImageQuality] [int] NOT NULL ,
[FullImageFixedSize] [bit] NOT NULL ,
[MediumImageHeight] [int] NOT NULL ,
[MediumImageWidth] [int] NOT NULL ,
[MediumImageQuality] [int] NOT NULL ,
[MediumImageFixedSize] [bit] NOT NULL ,
[ThumbImageHeight] [int] NOT NULL ,
[ThumbImageWidth] [int] NOT NULL ,
[ThumbImageQuality] [int] NOT NULL ,
[ThumbImageFixedSize] [bit] NOT NULL ,
[GroupID] [int] NOT NULL ,
[CacheRestriction] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[ControlledListValues] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ControlledListID] [int] NOT NULL ,
[ItemValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[ControlledLists] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Standard] [bit] NULL ,
[Origin] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CollectionID] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[DatabaseVersion] (
[Version] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[DateFieldsIndex] (
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL ,
[FieldID] [int] NOT NULL ,
[FieldInstance] [int] NOT NULL ,
[StartDate] [int] NULL ,
[EndDate] [int] NULL ,
[OriginalValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[FavoriteImages] (
[UserID] [int] NOT NULL ,
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[FieldData] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL ,
[FieldID] [int] NOT NULL ,
[FieldInstance] [int] NOT NULL ,
[FieldValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ControlledListValue] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[FieldDefinitions] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CollectionID] [int] NOT NULL ,
[Label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayOrder] [int] NOT NULL ,
[Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [int] NOT NULL ,
[DCElement] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCRefinement] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Searchable] [bit] NOT NULL ,
[KeywordSearchable] [bit] NOT NULL ,
[Sortable] [bit] NOT NULL ,
[Browsable] [bit] NOT NULL ,
[ControlledListID] [int] NOT NULL ,
[ShortView] [int] NOT NULL ,
[MediumView] [int] NOT NULL ,
[LongView] [int] NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Folders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[ImageAnnotations] (
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[Annotation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Images] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CollectionID] [int] NOT NULL ,
[Resource] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Created] [datetime] NULL ,
[Modified] [datetime] NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[PersonalImages] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[Shared] [bit] NULL ,
[Created] [datetime] NULL ,
[Modified] [datetime] NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Properties] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ObjectID] [int] NOT NULL ,
[Property] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[RecordMaintenance] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[ModificationDate] [datetime] NOT NULL ,
[Modification] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ImageData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[SavedSearches] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[Title] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Parameters] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Slides] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SlideshowID] [int] NOT NULL ,
[ImageID] [int] NOT NULL ,
[CollectionID] [int] NOT NULL ,
[DisplayOrder] [int] NOT NULL ,
[Annotation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Scratch] [bit] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Slideshows] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[FolderID] [int] NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AccessPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NOT NULL ,
[ModificationDate] [datetime] NOT NULL ,
[ArchiveFlag] [bit] NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[TrackUsageAgreements] (
[CollectionID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[DateAccepted] [datetime] NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[TransactionLog] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[EventTime] [datetime] NOT NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Data] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[UserGroupAttributes] (
[GroupID] [int] NOT NULL ,
[Attribute] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AttributeValueInstance] [int] NOT NULL ,
[AttributeValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[UserGroupIPRanges] (
[GroupID] [int] NOT NULL ,
[Subnet] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Mask] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[UserGroupMembers] (
[UserID] [int] NOT NULL ,
[GroupID] [int] NOT NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[UserGroups] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]",

												 @"CREATE TABLE [dbo].[Users] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Login] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Administrator] [bit] NULL ,
[LastAuthenticated] [datetime] NULL 
) ON [PRIMARY]",

												 @"ALTER TABLE [dbo].[AccessControl] WITH NOCHECK ADD 
CONSTRAINT [PK_AccessControl_1] PRIMARY KEY  CLUSTERED 
(
[ID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Announcements] WITH NOCHECK ADD 
CONSTRAINT [PK_Announcements] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[CollectionGroups] WITH NOCHECK ADD 
CONSTRAINT [PK_CollectionGroups] PRIMARY KEY  CLUSTERED 
(
[ID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Collections] WITH NOCHECK ADD 
CONSTRAINT [PK_Collections] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[ControlledListValues] WITH NOCHECK ADD 
CONSTRAINT [PK_ControlledListValues] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[ControlledLists] WITH NOCHECK ADD 
CONSTRAINT [PK_ControlledLists] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[DateFieldsIndex] WITH NOCHECK ADD 
CONSTRAINT [PK_DateFieldsIndex] PRIMARY KEY  CLUSTERED 
(
[ImageID],
[CollectionID],
[FieldID],
[FieldInstance]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[FavoriteImages] WITH NOCHECK ADD 
CONSTRAINT [PK_FavoriteImages] PRIMARY KEY  CLUSTERED 
(
[UserID],
[ImageID],
[CollectionID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[FieldData] WITH NOCHECK ADD 
CONSTRAINT [PK_FieldData_DEFAULT] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"exec sp_fulltext_database N'enable' ",

												 @"if exists (select * from dbo.sysfulltextcatalogs where name = N'FieldData_FullTextIndex')
exec sp_fulltext_catalog N'FieldData_FullTextIndex', N'drop'",

												 @"exec sp_fulltext_catalog N'FieldData_FullTextIndex', N'create' ",

												 @"exec sp_fulltext_table N'[dbo].[FieldData]', N'create', N'FieldData_FullTextIndex', N'PK_FieldData_DEFAULT'",

												 @"exec sp_fulltext_column N'[dbo].[FieldData]', N'FieldValue', N'add', 1033  ",

												 @"exec sp_fulltext_table N'[dbo].[FieldData]', N'activate' ",

												 @"exec sp_fulltext_table N'[dbo].[FieldData]', N'start_change_tracking' ",

												 @"exec sp_fulltext_table N'[dbo].[FieldData]', N'start_background_updateindex' ",

												 @"ALTER TABLE [dbo].[FieldDefinitions] WITH NOCHECK ADD 
CONSTRAINT [PK_Fields] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Folders] WITH NOCHECK ADD 
CONSTRAINT [PK_Folders] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[ImageAnnotations] WITH NOCHECK ADD 
CONSTRAINT [PK_ImageAnnotations] PRIMARY KEY  CLUSTERED 
(
[ImageID],
[CollectionID],
[UserID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Images] WITH NOCHECK ADD 
CONSTRAINT [PK_Images] PRIMARY KEY  CLUSTERED 
(
[ID],
[CollectionID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[PersonalImages] WITH NOCHECK ADD 
CONSTRAINT [PK_PersonalImages] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD 
CONSTRAINT [PK_Properties] PRIMARY KEY  CLUSTERED 
(
[ID],
[ObjectType],
[ObjectID],
[Property]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[RecordMaintenance] WITH NOCHECK ADD 
CONSTRAINT [PK_RecordMaintenance] PRIMARY KEY  CLUSTERED 
(
[ID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[SavedSearches] WITH NOCHECK ADD 
CONSTRAINT [PK_SavedSearches] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Slides] WITH NOCHECK ADD 
CONSTRAINT [PK_Slides] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Slideshows] WITH NOCHECK ADD 
CONSTRAINT [PK_Slideshows] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[TrackUsageAgreements] WITH NOCHECK ADD 
CONSTRAINT [PK_TrackUsageAgreements] PRIMARY KEY  CLUSTERED 
(
[CollectionID],
[UserID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[TransactionLog] WITH NOCHECK ADD 
CONSTRAINT [PK_TransactionLog] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[UserGroupAttributes] WITH NOCHECK ADD 
CONSTRAINT [PK_UserGroupAttributes] PRIMARY KEY  CLUSTERED 
(
[GroupID],
[Attribute],
[AttributeValueInstance]
)  ON [PRIMARY]",

												 @"ALTER TABLE [dbo].[UserGroupIPRanges] WITH NOCHECK ADD 
CONSTRAINT [PK_UserGroupIPRanges] PRIMARY KEY  CLUSTERED 
(
[GroupID],
[Subnet],
[Mask]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[UserGroupMembers] WITH NOCHECK ADD 
CONSTRAINT [PK_UserGroupMembers] PRIMARY KEY  CLUSTERED 
(
[UserID],
[GroupID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[UserGroups] WITH NOCHECK ADD 
CONSTRAINT [PK_UserGroups] PRIMARY KEY  CLUSTERED 
(
[ID]
)  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
(
[ID]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ",

												 @"ALTER TABLE [dbo].[Collections] WITH NOCHECK ADD 
CONSTRAINT [DF_Collections_Type] DEFAULT ('I') FOR [Type]",

												 @"ALTER TABLE [dbo].[FieldDefinitions] WITH NOCHECK ADD 
CONSTRAINT [DF_FieldDefinitions_DisplayOrder] DEFAULT (0) FOR [DisplayOrder],
CONSTRAINT [DF_Fields_Sortable] DEFAULT (0) FOR [Sortable],
CONSTRAINT [DF_FieldDefinitions_Browsable] DEFAULT (0) FOR [Browsable]",

												 @"ALTER TABLE [dbo].[RecordMaintenance] WITH NOCHECK ADD 
CONSTRAINT [DF_RecordMaintenance_Deletion] DEFAULT (0) FOR [Modification]",

												 @"ALTER TABLE [dbo].[Slideshows] WITH NOCHECK ADD 
CONSTRAINT [DF_Slideshows_ArchiveFlag] DEFAULT (0) FOR [ArchiveFlag]",

												 @"CREATE  INDEX [IX_ControlledListValues] ON [dbo].[ControlledListValues]([ControlledListID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_DateFieldsIndex] ON [dbo].[DateFieldsIndex]([CollectionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_DateFieldsIndex_1] ON [dbo].[DateFieldsIndex]([ImageID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_DateFieldsIndex_2] ON [dbo].[DateFieldsIndex]([FieldID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_FieldData] ON [dbo].[FieldData]([CollectionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_IndividualField] ON [dbo].[FieldData]([ImageID], [CollectionID], [FieldID]) ON [PRIMARY]",

												 @"CREATE  INDEX [IX_FieldData_2] ON [dbo].[FieldData]([FieldID]) ON [PRIMARY]",

												 @"CREATE  INDEX [IX_FieldData_3] ON [dbo].[FieldData]([ImageID]) ON [PRIMARY]",

												 @"CREATE  INDEX [IX_Fields] ON [dbo].[FieldDefinitions]([CollectionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_Folders] ON [dbo].[Folders]([UserID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_Images] ON [dbo].[Images]([CollectionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_ImageResource] ON [dbo].[Images]([Resource]) ON [PRIMARY]",

												 @"CREATE  INDEX [IX_PersonalImages] ON [dbo].[PersonalImages]([UserID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [IX_SavedSearches] ON [dbo].[SavedSearches]([UserID]) WITH  FILLFACTOR = 90 ON [PRIMARY]",

												 @"CREATE  INDEX [SlidesIdx] ON [dbo].[Slides]([SlideshowID], [ImageID], [CollectionID], [DisplayOrder]) ON [PRIMARY]",

												 @"CREATE  INDEX [IDX_TransactionLog_EventTime] ON [dbo].[TransactionLog]([EventTime] DESC ) ON [PRIMARY]",

												 @"CREATE  INDEX [IDX_TransactionLog_Title] ON [dbo].[TransactionLog]([Title]) ON [PRIMARY]",

												 @"CREATE  INDEX [IX_Users] ON [dbo].[Users]([Login]) WITH  FILLFACTOR = 90 ON [PRIMARY]",
			};

			foreach (string c in commands)
				new SqlCommand(c, conn).ExecuteNonQuery();
		}
		
		
		private void PopulateDatabase(SqlConnection conn)
		{
			string[] commands = new string[] {

												 @"INSERT INTO DatabaseVersion (Version) VALUES ('00001')",

												 @"INSERT INTO Users (Login,Password,Name,FirstName,Administrator)
VALUES ('admin','21232F297A57A5A743894A0E4A801FC3','Admin','Admin',1)",

												 @"INSERT INTO UserGroups (Title,Type)
VALUES ('Faculty','M')",

												 @"INSERT INTO UserGroups (Title,Type)
VALUES ('Authenticated Users','A')",

												 String.Format(@"INSERT INTO Collections (Title,Description,ResourcePath,Type,
UsageAgreement,FullImageHeight,FullImageWidth,FullImageQuality,
FullImageFixedSize,MediumImageHeight,MediumImageWidth,MediumImageQuality,
MediumImageFixedSize,ThumbImageHeight,ThumbImageWidth,
ThumbImageQuality,ThumbImageFixedSize,GroupID,CacheRestriction) 
VALUES ('Personal Images','Personal images',
'{0}','P',NULL,
3000,3000,97,0,800,800,97,0,72,96,97,1,0,2)", 
												 Path.Combine(Configuration.Instance.GetString("content.resourcepath"), "personal")),

												 @"INSERT INTO Collections (Title,Description,ResourcePath,Type,
UsageAgreement,FullImageHeight,FullImageWidth,FullImageQuality,
FullImageFixedSize,MediumImageHeight,MediumImageWidth,MediumImageQuality,
MediumImageFixedSize,ThumbImageHeight,ThumbImageWidth,
ThumbImageQuality,ThumbImageFixedSize,GroupID,CacheRestriction) 
VALUES ('Favorites','Your favorite images','n/a','F',NULL,
0,0,0,0,0,0,0,0,0,0,0,0,0,2)"
											 };

			foreach (string c in commands)
				new SqlCommand(c, conn).ExecuteNonQuery();

			new ResourcePath(Path.Combine(Configuration.Instance.GetString("content.resourcepath"), "personal")).CreateImageDirectories();

			int pcid = Convert.ToInt32(new SqlCommand(
				"SELECT ID FROM Collections WHERE Type='P'", conn).ExecuteScalar());
			int favid = Convert.ToInt32(new SqlCommand(
				"SELECT ID FROM Collections WHERE Type='F'", conn).ExecuteScalar());
			int facid = Convert.ToInt32(new SqlCommand(
				"SELECT ID FROM UserGroups WHERE Type='M'", conn).ExecuteScalar());
			int authid = Convert.ToInt32(new SqlCommand(
				"SELECT ID FROM UserGroups WHERE Type='A'", conn).ExecuteScalar());

			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('C',-1,{0},24704)", facid), conn).ExecuteNonQuery();
			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('S',-1,{0},2048)", authid), conn).ExecuteNonQuery();
			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('O',1,{0},67108864)", authid), conn).ExecuteNonQuery();
			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('O',1,{0},131328|67108864)", facid), conn).ExecuteNonQuery();
			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('C',{0},{1},24736)", pcid, facid), conn).ExecuteNonQuery();
			new SqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('C',{0},{1},16512)", favid, facid), conn).ExecuteNonQuery();

			string[] pccommands = new string[] {

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Title',1,'Title',1,'Title',NULL,1,1,1,1,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Creator',2,'Creator',1,'Creator',NULL,1,1,1,1,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Subject',3,'Subject',1,'Subject',NULL,1,1,1,1,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Description',4,'Description',1,'Description',NULL,1,1,1,1,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Publisher',5,'Publisher',1,'Publisher',NULL,1,1,1,1,0,0,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Contributor',6,'Contributor',1,'Contributor',NULL,1,1,1,1,0,0,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Date',7,'Date',2,'Date',NULL,1,0,1,0,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Type',8,'Type',1,'Type',NULL,1,1,1,1,0,0,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Format',9,'Format',1,'Format',NULL,1,1,1,1,0,0,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Identifier',10,'Identifier',1,'Identifier',NULL,1,1,1,1,0,1,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Source',11,'Source',1,'Source',NULL,1,1,1,1,0,0,1,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Language',12,'Language',1,'Language',NULL,1,1,1,1,0,0,0,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Relation',13,'Relation',1,'Relation',NULL,1,1,1,1,0,0,0,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Coverage',14,'Coverage',1,'Coverage',NULL,1,1,1,1,0,0,0,1)",

												   @"INSERT INTO FieldDefinitions (CollectionID,Label,DisplayOrder,
Name,Type,DCElement,DCRefinement,Searchable,KeywordSearchable,
Sortable,Browsable,ControlledListID,ShortView,MediumView,LongView) 
VALUES ({0},'Rights',15,'Rights',1,'Rights',NULL,1,1,0,0,0,0,0,1)",
			};

			foreach (string c in pccommands)
				new SqlCommand(String.Format(c, pcid), conn).ExecuteNonQuery();
		}

		/// <summary>
		/// Runs a select query.
		/// </summary>
		/// <remarks>
		/// Use this method for queries returning more than a single value.
		/// </remarks>
		/// <param name="query">The SQL query to run.</param>
		/// <returns>A DataTable containing the records retrieved by the query</returns>
		public override DataTable SelectQuery(Query query)
		{
			string sql = query.SQL;
			Trace.WriteLineIf(DBConnector.dbTraceSwitch.TraceVerbose, sql, "DB");
			//			DateTime start = DateTime.Now;
			DataSet s = new DataSet();
			SqlCommand c = new SqlCommand(sql, connection);
			if (Configuration.Instance.ContainsKey("database.cmdtimeout"))
				c.CommandTimeout = Configuration.Instance.GetInt("database.cmdtimeout");
			SqlDataAdapter a = new SqlDataAdapter(c);
			a.Fill(s);
			//			DateTime end = DateTime.Now;
			return s.Tables[0];
		}

        /// <summary>
        /// Execute a query and return the resulting data reader
        /// </summary>
        /// <remarks>
        /// This method returns a <see cref="IDataReader"/> instead of
        /// preloading all rows into a <see cref="DataTable"/>.
        /// </remarks>
        /// <param name="query">The SQL query to run</param>
        /// <returns>An <see cref="IDataReader"/> with the resulting rows</returns>
        public override IDataReader ExecReader(Query query)
		{
			string sql = query.SQL;
			SqlCommand c = new SqlCommand(sql, connection);
			if (Configuration.Instance.ContainsKey("database.cmdtimeout"))
				c.CommandTimeout = Configuration.Instance.GetInt("database.cmdtimeout");
			return c.ExecuteReader();
		}

		/// <summary>
		/// Executes a query without returning records
		/// </summary>
		/// <remarks>
		/// Use this method for UPDATE or INSERT statements.
		/// </remarks>
		/// <param name="query">The SQL query to run</param>
		/// <returns>The number of affected records</returns>
		public override int ExecQuery(Query query)
		{
			string sql = query.SQL;
			Trace.WriteLineIf(DBConnector.dbTraceSwitch.TraceVerbose, sql, "DB");
			SqlCommand c = new SqlCommand(sql, connection);
			if (Configuration.Instance.ContainsKey("database.cmdtimeout"))
				c.CommandTimeout = Configuration.Instance.GetInt("database.cmdtimeout");
			return c.ExecuteNonQuery();
		}

		/// <summary>
		/// Executes a query returning a single value
		/// </summary>
		/// <remarks>
		/// Use this method for <c>SELECT COUNT(*)</c> and similar queries.
		/// </remarks>
		/// <param name="query">The SQL query to run</param>
		/// <returns>The value returned by the query</returns>
		public override object ExecScalar(Query query)
		{
			string sql = query.SQL;
			Trace.WriteLineIf(DBConnector.dbTraceSwitch.TraceVerbose, sql, "DB");
			SqlCommand c = new SqlCommand(sql, connection);
			if (Configuration.Instance.ContainsKey("database.cmdtimeout"))
				c.CommandTimeout = Configuration.Instance.GetInt("database.cmdtimeout");
			return c.ExecuteScalar();
		}

		/// <summary>
		/// Retrieves the identity created by the database for the record inserted last
		/// </summary>
		/// <remarks>Runs <c>SELECT @@IDENTITY FROM table</c>.</remarks>
		/// <param name="table">The table the record was inserted into</param>
		/// <returns>The identity created for the record</returns>
		public override int LastIdentity(string table)
		{
			Trace.WriteLineIf(DBConnector.dbTraceSwitch.TraceVerbose, 
				String.Format("SELECT @@IDENTITY FROM {0}", table), "DB");
			SqlCommand c = new SqlCommand(String.Format("SELECT @@IDENTITY FROM {0}", table), connection);
			System.Decimal id = (System.Decimal)c.ExecuteScalar();
			return Decimal.ToInt32(id);
		}

		/// <summary>
		/// Closes the connection
		/// </summary>
		/// <remarks>
		/// This method must be called once the connection object is no longer needed.  For
		/// performance reasons, this method should be called as soon as possible.  It is
		/// recommended to use a <c>using</c> block to guarantee the connection will be closed.
		/// </remarks>
		public override void Close()
		{
			if (connection != null && connection.State != ConnectionState.Closed)
				connection.Close();
			connection = null;
		}

		/// <summary>
		/// Encodes a string so it can be used in an SQL query. 
		/// </summary>
		/// <remarks>
		/// For Microsoft SQL Server this method escapes the single quote 
		/// by adding another single quote.
		/// </remarks>
		/// <param name="sql">The string to encode</param>
		/// <returns>The encoded string</returns>
		public override string Encode(string sql)
		{
			if (sql == null)
				return null;
			else
				return sql.Replace("'", "''");
		}

		/// <summary>
		/// Encodes a string so it can be used in an SQL LIKE comparison. 
		/// </summary>
		/// <remarks>
		/// For Microsoft SQL Server,
		/// this encodes the opening bracket <c>[</c>, the percent sign <c>%</c> and the
		/// underscore <c>_</c>.
		/// </remarks>
		/// <param name="sql">The string to encode</param>
		/// <returns>The encoded string</returns>
		public override string LikeEncode(string sql)
		{
			if (sql == null)
				return null;
			else
			{
				string s = Encode(sql);
				return s.Replace("[", "[[]").Replace("%", "[%]").Replace("_", "[_]");
			}
		}

		/// <summary>
		/// Creates a text comparison condition
		/// </summary>
		/// <remarks>
		/// Uses SQL Server's <c>CONTAINS</c> condition for phrases (keywords containing spaces)
		/// or <c>CONTAINS FORMSOF(INFLECTIONAL)</c> for individual keywords.
		/// </remarks>
		/// <param name="field">The name of the field to compare</param>
		/// <param name="keyword">The keyword to find</param>
		/// <returns>a WHERE condition comparing field values to the specified keyword</returns>
		public override string TextComparison(string field, string keyword)
		{
#if false
			return String.Format("{0} LIKE '%{1}%'", field, Encode(keyword));
#else
			if (keyword.IndexOf(" ") < 0)
				// looking for individual keyword, word forms are ok
				return String.Format("(CONTAINS ({0}, 'FORMSOF(INFLECTIONAL, {1})'))", field, Encode(keyword));
			else
				// looking for a phrase, no word forms
				return String.Format("(CONTAINS ({0}, '\"{1}\"'))", field, Encode(keyword));
#endif
		}

		// list of reserved words that cannot be used in a full-text query
		private static string[] reservedwords = new string[]
			{
				"@", "*", "$", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", 
				"a", "about", "after", "all", "also", "an", "and", "another", "any", 
				"are", "as", "at", "b", "be", "because", "been", "before", "being", 
				"between", "both", "but", "by", "c", "came", "can", "come", "could", "d", 
				"did", "do", "does", "e", "each", "else", "f", "for", "from", "g", "get", 
				"got", "h", "had", "has", "have", "he", "her", "here", "him", "himself", 
				"his", "how", "i", "if", "in", "into", "is", "isabout", "it", "its", "j", 
				"just", "k", "l", "like", "m", "make", "many", "me", "might", "more", "most", 
				"much", "must", "my", "n", "near", "never", "not", "now", "o", "of", "on", 
				"only", "or", "other", "our", "out", "over", "p", "q", "r", "re", "s", "said", 
				"same", "see", "should", "since", "so", "some", "still", "such", "t", "take", 
				"than", "that", "the", "their", "them", "then", "there", "these", "they", "this", 
				"those", "through", "to", "too", "u", "under", "up", "use", "v", "very", "w", 
				"want", "was", "way", "we", "weight", "well", "were", "what", "when", "where", 
				"which", "while", "who", "will", "with", "would", "x", "y", "you", "your", "z"
			};

		/// <summary>
		/// Validate keyword
		/// </summary>
		/// <remarks>
		/// For SQL Server, not every word is valid in a full text search. This method checks the validity
		/// of a certain keyword.
		/// </remarks>
		/// <param name="keyword">The keyword to check.</param>
		/// <returns><c>true</c> if this keyword can be searched for in a full-text search, <c>false</c>
		/// otherwise.</returns>
		public override bool IsValidKeyword(string keyword)
		{
			string k = keyword.ToLower();
			foreach (string r in reservedwords)
				if (k == r)
					return false;
			return true;
		}

		/// <summary>
		/// Allow for sorting of large text fields
		/// </summary>
		/// <remarks>
		/// MS SQL does not allow sorting of results by large text fields. These fields
		/// need to be converted to VARCHAR.
		/// </remarks>
		/// <param name="fieldname">The name of the field to sort by</param>
		/// <returns>A string representing the field with a sortable type.</returns>
		public override string SortableTextField(string fieldname)
		{
			return String.Format("CAST({0} AS VARCHAR(255))", fieldname);
		}

		/// <summary>
		/// Format date to store in database
		/// </summary>
		/// <remarks>
		/// This method converts a <see cref="DateTime"/> object to a string formatted so
		/// it is understood by the SQL Server database. 
		/// </remarks>
		/// <param name="date">The <see cref="DateTime"/> object to format</param>
		/// <returns>The date formatted in a string</returns>
		public override string DateToDBFormatString(DateTime date)
		{
			if (date.Year < 1753)
				return "1753-01-01 12:00:00 am";
			else
				return date.ToString("yyyy-MM-dd hh:mm:ss tt");
		}

		/// <summary>
		/// Convert database date object to DateTime object
		/// </summary>
		/// <remarks>
		/// Different databases store date object differently.  Microsoft SQL Server
		/// does not allow dates earlier than 1/1/1753, so that date represents the minimum
		/// value and is converted to <c>DateTime.MinValue</c> if encountered.
		/// </remarks>
		/// <param name="data">The object retrieved from the database</param>
		/// <param name="defaultvalue">The default value that should be used if the object
		/// is <c>null</c>.</param>
		/// <returns>A DateTime object</returns>
		public override DateTime DataToDateTime(Object data, DateTime defaultvalue)
		{
			DateTime d = base.DataToDateTime(data, defaultvalue);
			if (d.Year == 1753 && d.Month == 1 && d.Day == 1)
				return DateTime.MinValue;
			else
				return d;
		}

		/// <summary>
		/// Prefix for Unicode strings in SQL statements
		/// </summary>
		/// <remarks>
		/// Different databases use different prefixes to mark Unicode strings.  The 'N' prefix
		/// should be standard, but some versions of MySQL have a bug that prevents this from
		/// working. See http://bugs.mysql.com/bug.php?id=17313.
		/// SQL Server uses "N" as the Unicode string prefix.
		/// </remarks>
		/// <returns>"N"</returns>
		public override string UnicodeStringPrefix()
		{
			return "N";
		}

	}
}