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 MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.IO;

namespace Orciid.Core
{
	/// <summary>
	/// The MySQL connection class
	/// </summary>
	/// <remarks>
	/// This connection class is compatible with MySQL 4.1.
	/// </remarks>
	public class MySQLDBConnection: 
		DBConnection
	{
		/// <summary>
		/// The actual database connection object
		/// </summary>
		private MySqlConnection connection;
		
		/// <summary>
		/// Constructor
		/// </summary>
		/// <remarks>
		/// Creates a new MySQL 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 MySQLDBConnection(string cs, bool open):
			base(cs)
		{
			if (cs != null)
			{
				connection = new MySqlConnection(cs);
				if (open)
				{
					connection.Open();
					// to work around collation problem in MySQL 4.1
					// this is called every time a connection is requested, is there a better way?
					// AK 4/22/05 apparently this has been fixed, if not, it can also be
					// configured using my.ini (MySQL configuration file)
					// this.ExecQuery("SET CHARACTER SET utf8");
				}
			}
		}
	
		/// <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)
		{
			MySqlConnection conn = new MySqlConnection(cs);
			try
			{
				conn.Open();
				DataSet s = new DataSet();
				new MySqlDataAdapter("SHOW TABLES", conn).Fill(s);
				if (s.Tables[0].Rows.Count == 0)
				{
					CreateDatabaseTables(conn);
					PopulateDatabase(conn);
				}
				string version;
				do
				{
					s = new DataSet();
					new MySqlDataAdapter("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(MySqlConnection conn)
		{
			new MySqlCommand(@"
						CREATE TABLE RemoteCollectionAccess (
						ID int(11) NOT NULL auto_increment primary key,
						CollectionID int(11) NOT NULL,
						UserID int(11) NOT NULL,
						Subnet varchar(15) NOT NULL default '',
						Mask varchar(15) NOT NULL default '',
						CacheTimeSpan int(11) NOT NULL,
						ExpirationTimeSpan int(11) NOT NULL,
						SearchResults int(11) NOT NULL
						) ENGINE=MyISAM DEFAULT CHARSET=latin1",		
				conn).ExecuteNonQuery();

			new MySqlCommand(@"
						ALTER TABLE Images
						ADD UserID int(11) default NULL,
						ADD Flags int(11) default NULL,
						ADD CachedUntil datetime default NULL,
						ADD Expires datetime default NULL,
						ADD RemoteID varchar(255) default NULL,
						ADD RecordStatus int(11) default 0,
						ADD MimeType varchar(255) default NULL,
						ADD INDEX (UserID),
						ADD INDEX (RemoteID)",
				conn).ExecuteNonQuery();

			new MySqlCommand(@"
						ALTER TABLE Collections
						ADD RemoteUrl varchar(255) default NULL,
						ADD FullImageBGColor int default 0 AFTER FullImageFixedSize,
						ADD MediumImageBGColor int default 0 AFTER MediumImageFixedSize,
						ADD ThumbImageBGColor int default 0 AFTER ThumbImageFixedSize",
				conn).ExecuteNonQuery();

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

			//copy all personal images

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

			MySqlDataAdapter a = new MySqlDataAdapter(
				@"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 MySqlCommand(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();

				long lastid = (long)(new MySqlCommand(@"SELECT LAST_INSERT_ID()", conn).ExecuteScalar());

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

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

			new MySqlCommand(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 MySqlCommand(@"UPDATE DatabaseVersion SET Version='00002'", conn).ExecuteNonQuery();								
		}


		private void UpgradeDatabaseFromVersion00002(MySqlConnection conn)
		{
			new MySqlCommand(@"
						ALTER TABLE FieldData
						ADD StartDate int(11) default NULL,
						ADD EndDate int(11) default NULL,
						ADD OriginalValue mediumtext",
				conn).ExecuteNonQuery();

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

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

			DataSet s = new DataSet();
			new MySqlDataAdapter("SHOW TABLES", conn).Fill(s);
			foreach (DataRow row in s.Tables[0].Rows)
			{
				new MySqlCommand(String.Format(
					@"ALTER TABLE {0} CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci", row[0]),
					conn).ExecuteNonQuery();
			}

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

		private void UpgradeDatabaseFromVersion00003(MySqlConnection conn)
		{
			try
			{
				new MySqlCommand(@"
		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 MySqlCommand(@"UPDATE DatabaseVersion SET Version='00004'", conn).ExecuteNonQuery();
		}

		private void UpgradeDatabaseFromVersion00004(MySqlConnection conn)
		{
			new MySqlCommand(@"
				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 MySqlCommand(@"
				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 MySqlCommand(@"UPDATE DatabaseVersion SET Version='00005'", conn).ExecuteNonQuery();
		}

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

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

		private void UpgradeDatabaseFromVersion(string version, MySqlConnection 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(MySqlConnection conn)
		{
			string[] commands = new string[] {
			
@"CREATE TABLE AccessControl (
  ID int(11) NOT NULL auto_increment primary key,
  ObjectType char(1) default NULL,
  ObjectID int(11) default NULL,
  UserID int(11) default NULL,
  GroupID int(11) default NULL,
  GrantPriv int(11) default NULL,
  DenyPriv int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Announcements (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) NOT NULL default '0',
  Posted datetime NOT NULL default '0000-00-00 00:00:00',
  Title varchar(255) NOT NULL default '',
  Text mediumtext NOT NULL,
  Link varchar(255) default NULL,
  LinkTitle varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE CollectionGroups (
  ID int(11) NOT NULL auto_increment primary key,
  Title varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Collections (
  ID int(11) NOT NULL auto_increment primary key,
  Title varchar(50) NOT NULL default '',
  Description mediumtext NOT NULL,
  ResourcePath varchar(255) NOT NULL default '',
  Type char(1) NOT NULL default '',
  UsageAgreement mediumtext,
  FullImageHeight int(11) NOT NULL default '0',
  FullImageWidth int(11) NOT NULL default '0',
  FullImageQuality int(11) NOT NULL default '0',
  FullImageFixedSize tinyint(1) NOT NULL default '0',
  MediumImageHeight int(11) NOT NULL default '0',
  MediumImageWidth int(11) NOT NULL default '0',
  MediumImageQuality int(11) NOT NULL default '0',
  MediumImageFixedSize tinyint(1) NOT NULL default '0',
  ThumbImageHeight int(11) NOT NULL default '0',
  ThumbImageWidth int(11) NOT NULL default '0',
  ThumbImageQuality int(11) NOT NULL default '0',
  ThumbImageFixedSize tinyint(1) NOT NULL default '0',
  GroupID int(11) NOT NULL default '0',
  CacheRestriction int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE ControlledLists (
  ID int(11) NOT NULL auto_increment primary key,
  Title varchar(50) NOT NULL default '',
  Description mediumtext,
  Standard tinyint(1) default NULL,
  Origin mediumtext,
  CollectionID int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE ControlledListValues (
  ID int(11) NOT NULL auto_increment primary key,
  ControlledListID int(11) NOT NULL default '0',
  ItemValue varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE DatabaseVersion (
  Version char(5) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE DateFieldsIndex (
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0',
  FieldID int(11) NOT NULL default '0',
  FieldInstance int(11) NOT NULL default '0',
  StartDate int(11) default NULL,
  EndDate int(11) default NULL,
  OriginalValue mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE FavoriteImages (
  UserID int(11) NOT NULL default '0',
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE FieldData (
  ID int(11) NOT NULL auto_increment primary key,
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0',
  FieldID int(11) NOT NULL default '0',
  FieldInstance int(11) NOT NULL default '0',
  FieldValue mediumtext NOT NULL,
  ControlledListValue int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE FieldDefinitions (
  ID int(11) NOT NULL auto_increment primary key,
  CollectionID int(11) NOT NULL default '0',
  Label varchar(255) NOT NULL default '',
  DisplayOrder int(11) NOT NULL default '0',
  Name varchar(255) NOT NULL default '',
  Type int(11) NOT NULL default '0',
  DCElement varchar(255) default NULL,
  DCRefinement varchar(255) default NULL,
  Searchable tinyint(1) NOT NULL default '0',
  KeywordSearchable tinyint(1) NOT NULL default '0',
  Sortable tinyint(1) NOT NULL default '0',
  Browsable tinyint(1) NOT NULL default '0',
  ControlledListID int(11) NOT NULL default '0',
  ShortView int(11) NOT NULL default '0',
  MediumView int(11) NOT NULL default '0',
  LongView int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Folders (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) NOT NULL default '0',
  Title varchar(50) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE ImageAnnotations (
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0',
  UserID int(11) NOT NULL default '0',
  Annotation mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Images (
  ID int(11) NOT NULL auto_increment,
  INDEX (ID),
  CollectionID int(11) NOT NULL default '0',
  Resource varchar(255) NOT NULL default '',
  Created datetime default NULL,
  Modified datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE PersonalImages (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) NOT NULL default '0',
  Shared tinyint(1) default NULL,
  Created datetime default NULL,
  Modified datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Properties (
  ID int(11) NOT NULL auto_increment,
  INDEX (ID),
  ObjectType char(1) NOT NULL default '',
  ObjectID int(11) NOT NULL default '0',
  Property varchar(16) NOT NULL default '',
  PropertyValue mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE RecordMaintenance (
  ID int(11) NOT NULL auto_increment primary key,
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0',
  UserID int(11) NOT NULL default '0',
  ModificationDate datetime NOT NULL default '0000-00-00 00:00:00',
  Modification char(1) NOT NULL default '',
  ImageData mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE SavedSearches (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) NOT NULL default '0',
  Title varchar(255) NOT NULL default '',
  Parameters mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Slides (
  ID int(11) NOT NULL auto_increment primary key,
  SlideshowID int(11) NOT NULL default '0',
  ImageID int(11) NOT NULL default '0',
  CollectionID int(11) NOT NULL default '0',
  DisplayOrder int(11) NOT NULL default '0',
  Annotation mediumtext,
  Scratch tinyint(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Slideshows (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) NOT NULL default '0',
  FolderID int(11) default NULL,
  Title varchar(50) NOT NULL default '',
  AccessPassword varchar(50) default NULL,
  CreationDate datetime NOT NULL default '0000-00-00 00:00:00',
  ModificationDate datetime NOT NULL default '0000-00-00 00:00:00',
  ArchiveFlag tinyint(1) NOT NULL default '0',
  Description mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE TrackUsageAgreements (
  CollectionID int(11) NOT NULL default '0',
  UserID int(11) NOT NULL default '0',
  DateAccepted datetime NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE TransactionLog (
  ID int(11) NOT NULL auto_increment primary key,
  UserID int(11) default NULL,
  EventTime datetime NOT NULL default '0000-00-00 00:00:00',
  Title varchar(50) NOT NULL default '',
  Data mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE UserGroupAttributes (
  GroupID int(11) NOT NULL default '0',
  Attribute varchar(255) NOT NULL default '',
  AttributeValueInstance int(11) NOT NULL default '0',
  AttributeValue varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE UserGroupIPRanges (
  GroupID int(11) NOT NULL default '0',
  Subnet varchar(15) NOT NULL default '',
  Mask varchar(15) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE UserGroupMembers (
  UserID int(11) NOT NULL default '0',
  GroupID int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE UserGroups (
  ID int(11) NOT NULL auto_increment primary key,
  Title varchar(255) NOT NULL default '',
  Type char(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"CREATE TABLE Users (
  ID int(11) NOT NULL auto_increment primary key,
  Login varchar(50) NOT NULL default '',
  Password varchar(32) default NULL,
  Name varchar(100) default NULL,
  FirstName varchar(100) default NULL,
  Email varchar(100) default NULL,
  Administrator tinyint(1) default NULL,
  LastAuthenticated datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1",

@"ALTER TABLE DateFieldsIndex  ADD
CONSTRAINT PK_DateFieldsIndex PRIMARY KEY
(
ImageID,
CollectionID,
FieldID,
FieldInstance
)",

@"ALTER TABLE FavoriteImages  ADD
CONSTRAINT PK_FavoriteImages PRIMARY KEY
(
UserID,
ImageID,
CollectionID
)",

@"CREATE FULLTEXT INDEX FieldData_FullTextIndex
ON FieldData (FieldValue)",

@"ALTER TABLE ImageAnnotations  ADD
CONSTRAINT PK_ImageAnnotations PRIMARY KEY
(
ImageID,
CollectionID,
UserID
)",

@"ALTER TABLE Images  ADD
CONSTRAINT PK_Images PRIMARY KEY
(
ID,
CollectionID
)",

@"ALTER TABLE Properties  ADD
CONSTRAINT PK_Properties PRIMARY KEY
(
ID,
ObjectType,
ObjectID,
Property
)",

@"ALTER TABLE TrackUsageAgreements  ADD
CONSTRAINT PK_TrackUsageAgreements PRIMARY KEY
(
CollectionID,
UserID
)",

@"ALTER TABLE UserGroupAttributes  ADD
CONSTRAINT PK_UserGroupAttributes PRIMARY KEY
(
GroupID,
Attribute,
AttributeValueInstance
)",


@"ALTER TABLE UserGroupIPRanges  ADD
CONSTRAINT PK_UserGroupIPRanges PRIMARY KEY
(
GroupID,
Subnet,
Mask
)",

@"ALTER TABLE UserGroupMembers  ADD
CONSTRAINT PK_UserGroupMembers PRIMARY KEY
(
UserID,
GroupID
)",

@"CREATE  INDEX IX_ControlledListValues ON ControlledListValues(ControlledListID)",

@"CREATE  INDEX IX_DateFieldsIndex ON DateFieldsIndex(CollectionID)",

@"CREATE  INDEX IX_DateFieldsIndex_1 ON DateFieldsIndex(ImageID)",

@"CREATE  INDEX IX_DateFieldsIndex_2 ON DateFieldsIndex(FieldID)",

@"CREATE  INDEX IX_FieldData ON FieldData(CollectionID)",

@"CREATE  INDEX IX_IndividualField ON FieldData(ImageID, CollectionID, FieldID)",

@"CREATE  INDEX IX_FieldData_2 ON FieldData(FieldID)",

@"CREATE  INDEX IX_FieldData_3 ON FieldData(ImageID)",

@"CREATE  INDEX IX_Fields ON FieldDefinitions(CollectionID)",

@"CREATE  INDEX IX_Folders ON Folders(UserID)",

@"CREATE  INDEX IX_Images ON Images(CollectionID)",

@"CREATE  INDEX IX_ImageResource ON Images(Resource)",

@"CREATE  INDEX IX_PersonalImages ON PersonalImages(UserID)",

@"CREATE  INDEX IX_SavedSearches ON SavedSearches(UserID)",

@"CREATE  INDEX SlidesIdx ON Slides(SlideshowID, ImageID, CollectionID, DisplayOrder)",

@"CREATE  INDEX IDX_TransactionLog_EventTime ON TransactionLog(EventTime DESC )",

@"CREATE  INDEX IDX_TransactionLog_Title ON TransactionLog(Title)",

@"CREATE  INDEX IX_Users ON Users(Login)"
										 
			};

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

		private void PopulateDatabase(MySqlConnection 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").Replace(@"\", @"\\")),

@"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 MySqlCommand(c, conn).ExecuteNonQuery();

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

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

			new MySqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('C',-1,{0},24704)", facid), conn).ExecuteNonQuery();
			new MySqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('S',-1,{0},2048)", authid), conn).ExecuteNonQuery();
			new MySqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('O',1,{0},67108864)", authid), conn).ExecuteNonQuery();
			new MySqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('O',1,{0},131328|67108864)", facid), conn).ExecuteNonQuery();
			new MySqlCommand(String.Format(
				@"INSERT INTO AccessControl (ObjectType,ObjectID,GroupID,GrantPriv)
				VALUES ('C',{0},{1},24736)", pcid, facid), conn).ExecuteNonQuery();
			new MySqlCommand(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 MySqlCommand(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");
			DataSet s = new DataSet();
			MySqlDataAdapter a = new MySqlDataAdapter(sql, connection);
			a.Fill(s);
			return s.Tables[0];
		}

		private int ExecQuery(string sql)
		{
			Trace.WriteLineIf(DBConnector.dbTraceSwitch.TraceVerbose, sql, "DB");
			IDbCommand c = connection.CreateCommand();
			c.CommandText = sql;
			// this does not seem to be supported in the MySQL driver
			//			if (Configuration.ContainsKey("database.cmdtimeout"))
			//				c.CommandTimeout = Configuration.GetInt("database.cmdtimeout");
			return c.ExecuteNonQuery();
		}

		/// <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)
		{
			return ExecQuery(query.SQL);
		}

        /// <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)
		{
			IDbCommand c = connection.CreateCommand();
			c.CommandText = query.SQL;
			return c.ExecuteReader();
		}

		/// <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");
			IDbCommand c = connection.CreateCommand();
			c.CommandText = sql;
// this does not seem to be supported in the MySQL driver
//			if (Configuration.ContainsKey("database.cmdtimeout"))
//				c.CommandTimeout = Configuration.GetInt("database.cmdtimeout");
			return c.ExecuteScalar();
		}

		/// <summary>
		/// Retrieves the identity created by the database for the record inserted last
		/// </summary>
		/// <remarks>
		/// Runs <c>SELECT LAST_INSERT_ID()</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, 
				"SELECT LAST_INSERT_ID()", "DB");
			IDbCommand c = connection.CreateCommand();
			c.CommandText = "SELECT LAST_INSERT_ID()";
			return (int)(long)c.ExecuteScalar();
		}

		/// <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 MySQL 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)
		{
			char[] escapechars = new char[] {
												'\u0092', // forward quote
												'\u0027', // regular quote
												'\u0060', // backward quote
			};
			if (sql == null)
				return null;
			else
			{
				string s = sql.Replace(@"\", @"\\");
				foreach (char c in escapechars)
					s = s.Replace(c.ToString(), @"\" + c.ToString());
				return s;
			}
		}

		/// <summary>
		/// Encodes a string so it can be used in an SQL LIKE comparison.
		/// </summary>
		/// <remarks>
		/// For MySQL, this encodes 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("_", @"\_");
			}
		}

		/// <summary>
		/// Creates text comparison condition
		/// </summary>
		/// <remarks>
		/// Uses MySQL's <c>MATCH AGAINST</c> comparison operator.
		/// </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 (keyword.IndexOf(" ") < 0)
				// looking for individual keyword
				return String.Format("(MATCH ({0}) AGAINST ('{1}'))", field, Encode(keyword));
			else
				// looking for an exact phrase
				return String.Format("(MATCH ({0}) AGAINST ('\"{1}\"' IN BOOLEAN MODE))", field, Encode(keyword));
		}

		/// <summary>
		/// Convert data object to boolean
		/// </summary>
		/// <remarks>
		/// This method converts a data object retrieved through a database query to a boolean.
		/// </remarks>
		/// <param name="data">The data object</param>
		/// <param name="defaultvalue">The default value to return if data is <c>null</c></param>
		/// <returns>The boolean value represented by the data object.</returns>
		public override bool DataToBool(Object data, bool defaultvalue)
		{
			return (data == null || data.Equals(DBNull.Value) ? defaultvalue : ((sbyte)data != 0));
		}

		/// <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 MySQL 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)
		{
			return date.ToString("s");
		}

		/// <summary>
		/// Adjust SQL query syntax
		/// </summary>
		/// <remarks>
		/// Different databases have a slightly different SQL syntax.  All queries in
		/// Orciid.Core classes use Microsoft SQL Server syntax, other databases like
		/// MySQL must override this method and adjust the query string.
		/// </remarks>
		/// <param name="query">The SQL query to be run</param>
		/// <returns>The adjusted SQL query</returns>
		public override string AdjustSqlSyntax(string query)
		{
			// regex that matches anything, but if there is an opening quote there
			// must be a closing quote as well
			const string dot = 
				@"[^']*(?:'[^'\\]*(?:\\.[^'\\]*)*')*[^']*";
			//    ^^^^^                                    start with any number of characters but quotes
			//                                      ^^^^^  end with any number of characters but quotes
			//            ^                      ^         match an opening and a closing quote
			//             ^^^^^^^                         match any number of non-quote and non-escape characters
			//                       ^^^                   match an escape character followed by any other character
			//                          ^^^^^^^            match any number of non-quote and non-escape characters

			// find multi-table update statements and change syntax
			Regex regex = new Regex(
				@"^\s*UPDATE\s+(\w+)\s+(SET\s+"+dot+@")\s+FROM\s+([^']+)\s+(WHERE\s+"+dot+@")", 
				RegexOptions.IgnoreCase | RegexOptions.Singleline);
			Match m = regex.Match(query);
			if (m.Success)
				query = String.Format("UPDATE {0},{1} {2} {3}", 
					m.Groups[1].Value, m.Groups[3].Value,
					m.Groups[2].Value, m.Groups[4].Value);

			// find TOP x qualifier and replace with LIMIT x
			regex = new Regex(@"^\s*SELECT\s+(DISTINCT\s+)?TOP\s+(\S+)\s+(.+)", 
				RegexOptions.IgnoreCase | RegexOptions.Singleline);
			m = regex.Match(query);
			if (m.Success)
				query = String.Format("SELECT {2}{1} LIMIT {0}", 
					m.Groups[2].Value, m.Groups[3].Value, m.Groups[1].Value);

			return query;
		}

		/// <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.
		/// MySQL uses "_utf8" as the Unicode string prefix.
		/// </remarks>
		/// <returns>"_utf8"</returns>
		public override string UnicodeStringPrefix()
		{
			return "_utf8";
		}

	}
}
