#region Usings

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using ICSharpCode.Data.Core.Common;
using ICSharpCode.Data.Core.Interfaces;
using System.Collections.ObjectModel;
using ICSharpCode.Data.Core.DatabaseObjects;
using System.Data.OleDb;
using System.Collections.Specialized;
using ICSharpCode.Data.Core.Enums;
using System.Threading;

#endregion

namespace ICSharpCode.Data.Access
{
    public class AccessDatabaseDriver: DatabaseDriver<AccessDatasource>
    {
        #region Consts

        private const string _getTables = @"SELECT MSysObjects.Owner as TABLE_SCHEMA, MSysObjects.Name as TABLE_NAME FROM MsysObjects WHERE (Left([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name";  //在Access中检索当前用户的所有表
        private const string _getViews = @"SELECT MSysObjects.Owner as TABLE_SCHEMA, MSysObjects.Name as TABLE_NAME FROM MsysObjects WHERE (Left([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name";  //在Access中检索当前用户的所有表
        //private const string _getViewDefiningQuery = @"EXEC sp_helptext '{0}'";
        //private const string _getProcedures = "SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_BODY, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'";
        //private const string _getProcedures = "SELECT PROCEDURE_NAME as ROUTINE_NAME, USER as ROUTINE_SCHEMA, 'SQL' as ROUTINE_BODY, NULL as DATA_TYPE, NULL as CHARACTER_MAXIMUM_LENGTH FROM USER_PROCEDURES WHERE PROCEDURE_NAME is not null";
        //private const string _getProcedureParameters = @"SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, PARAMETER_MODE, IS_RESULT FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = '{0}' AND SPECIFIC_SCHEMA = '{1}' AND SPECIFIC_CATALOG = '{2}'";

        #endregion

        public AccessDatabaseDriver()
		{
            Datasources = new DatabaseObjectsCollection<AccessDatasource>(null);
		}
		
        public override string Name
        {
            get { return "Access"; }
        }

        public override string ProviderName
        {
            get { return "Microsoft.Jet.OLEDB.4.0"; }
        }

        public override string ODBCProviderName
        {
            get { return "Ms Access 2007"; }
        }

        public override void PopulateDatasources()
        {
            DatabaseObjectsCollection<AccessDatasource> datasources = new DatabaseObjectsCollection<AccessDatasource>(null);
            
            DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();

            foreach (DataRow dr in dt.Rows)
            {
                string serverName = dr["ServerName"].ToString().Trim().ToUpper();
                string instanceName = null;
                Version version = null;

                if (dr["InstanceName"] != null && dr["InstanceName"] != DBNull.Value)
                    instanceName = dr["InstanceName"].ToString().Trim().ToUpper();

                if (dr["Version"] != null && dr["Version"] != DBNull.Value)
                	version = new Version(dr["Version"].ToString().Trim());

                AccessDatasource datasource = new AccessDatasource(this) { Name = serverName };

                datasource.ProviderManifestToken = GetManifestToken(version);

                if (!String.IsNullOrEmpty(instanceName))
                    datasource.Name += "\\" + instanceName;

                datasources.Add(datasource);
            }

            Datasources = datasources;
        }
		
		string GetManifestToken(Version version)
		{
			string manifestToken;
			if (!IsVersionSupported(version, out manifestToken))
				throw new NotSupportedException(string.Format("Version '{0}' is not supported!", version == null ? "unknown" : version.ToString()));
			return manifestToken;
		}
		
		bool IsVersionSupported(Version version, out string manifestToken)
		{
			manifestToken = "4";
			if (version == null)
				return false;
			switch (version.Major) {
				case 4:
					manifestToken = "4";
                    return true;
			}
			
			return false;
		}
        
        public override void PopulateDatabases(IDatasource datasource)
        {
            DatabaseObjectsCollection<IDatabase> databases = new DatabaseObjectsCollection<IDatabase>(datasource);

            string[] constrs = datasource.ConnectionString.Split(new char[] { ';' });
            foreach (string constr in constrs)
            {
                if (constr.StartsWith("data source", StringComparison.CurrentCultureIgnoreCase))
                {
                    string path = constr.Substring(constr.IndexOf("=") + 1);
                    string databaseName = System.IO.Path.GetFileNameWithoutExtension(path);
                    databases.Add(new Database(datasource) { Name = databaseName });
                }
            }
            datasource.Databases = databases;
            
            OleDbConnection oleDbConnection = null;
            oleDbConnection = new OleDbConnection();
            oleDbConnection.ConnectionString = datasource.ConnectionString; 
            try
            {
                oleDbConnection.Open();
            }
            catch (OleDbException ex)
            {
                switch (ex.ErrorCode)
                { 
                    case 2:
                    case 3:
                    case 53:
                        new Thread(new ThreadStart(delegate() { Datasources.Remove(datasource as AccessDatasource); })).Start();
                        break;
                    default:
                        break;
                }
                ICSharpCode.Core.LoggingService.Error(ex.Message);
                ICSharpCode.Core.LoggingService.Error(ex.StackTrace);
                return;
                //throw ex;
            }

            string strVersion = oleDbConnection.ServerVersion;
            string[] vs = strVersion.Split(new char[] { '.' });
            if (vs.Length > 4)
            {
                strVersion = strVersion.Substring(0, strVersion.LastIndexOf("."));
            }
			Version version = new Version(strVersion);
			datasource.ProviderManifestToken = GetManifestToken(version);

            if (oleDbConnection != null && oleDbConnection.State == ConnectionState.Open)
                oleDbConnection.Close();
        }

        private void LoadColumns2(string version, OleDbConnection oleDbConnection, ITable table, TableType tableType)
        {
            if (oleDbConnection.State != ConnectionState.Open)
            {
                oleDbConnection.Open();
            }
            DataTable dtColumns = this.GetOleDbColumns(oleDbConnection, table.TableName).Tables[0];

            for (int j = 0; j < dtColumns.Rows.Count; j++)
            {
                Column column = new Column(table);
                column.ColumnId = Convert.ToInt32(dtColumns.Rows[j]["ColumnId"]);
                column.Name = (string)dtColumns.Rows[j]["Name"];
                column.DataType = (string)dtColumns.Rows[j]["DataType"];
                column.SystemType = (string)dtColumns.Rows[j]["SystemType"];
                column.Length = 0;
                if (dtColumns.Rows[j]["Length"] != null && dtColumns.Rows[j]["Length"] != System.DBNull.Value)
                {
                    int length = 0;
                    int.TryParse(dtColumns.Rows[j]["Length"].ToString(), out length);
                    column.Length = length;
                }

                if (column.Length == -1)
                {
                    switch (column.DataType.ToLower())
                    {
                        case "varchar":
                        case "nvarchar":
                        case "varchar2":
                            column.DataType += "(max)";
                            break;
                        default:
                            break;
                    }

                    switch (column.SystemType.ToLower())
                    {
                        case "varchar":
                        case "nvarchar":
                        case "varchar2":
                            column.SystemType += "(max)";
                            break;
                        default:
                            break;
                    }
                }
                object objNumericPrecision = dtColumns.Rows[j]["NumericPrecision"];
                if (objNumericPrecision != null && objNumericPrecision != System.DBNull.Value)
                {
                    int precision = 0;
                    int.TryParse(objNumericPrecision.ToString(), out precision);
                    column.Precision = precision;
                }
                else
                {
                    column.Precision = 0;
                }
                object objScale = dtColumns.Rows[j]["Scale"];
                if (objScale != null && objScale != System.DBNull.Value)
                {
                    int scale = 0;
                    int.TryParse(objScale.ToString(), out scale);
                    column.Scale = scale;
                }
                else
                {
                    column.Scale = 0;
                }
                object objIsIdentity = dtColumns.Rows[j]["IsIdentity"];
                if (objIsIdentity != null && objIsIdentity != System.DBNull.Value)
                {
                    int isIdentity = 0;
                    int.TryParse(objIsIdentity.ToString(), out isIdentity);
                    column.IsIdentity = Convert.ToBoolean(isIdentity);
                }
                else
                {
                    column.IsIdentity = false;
                }
                object objIsNullable = dtColumns.Rows[j]["IsNullable"];
                if (objIsNullable != null && objIsNullable != System.DBNull.Value)
                {
                    int isNullable = 0;
                    int.TryParse(objIsNullable.ToString(), out isNullable);
                    column.IsNullable = Convert.ToBoolean(isNullable);
                }
                else
                {
                    column.IsNullable = false;
                }
                object objIsPrimaryKey = dtColumns.Rows[j]["IsPrimaryKey"];
                if (objIsPrimaryKey != null && objIsPrimaryKey != System.DBNull.Value)
                {
                    int isPrimaryKey = 0;
                    int.TryParse(objIsPrimaryKey.ToString(), out isPrimaryKey);
                    column.IsPrimaryKey = Convert.ToBoolean(isPrimaryKey);
                }
                else
                {
                    column.IsPrimaryKey = false;
                }

                table.Items.Add(column);
            }
        }

        public override DatabaseObjectsCollection<ITable> LoadTables(IDatabase database)
        {
            DatabaseObjectsCollection<ITable> tables = new DatabaseObjectsCollection<ITable>(database);

            using (OleDbConnection oleDbConnection = new OleDbConnection(database.ConnectionString))
            {
                oleDbConnection.Open();

                using (OleDbDataAdapter da = new OleDbDataAdapter(_getTables, oleDbConnection))
                {
                    DataTable dtTables = new DataTable("Tables");
                    da.Fill(dtTables);

                    for (int i = 0; i < dtTables.Rows.Count; i++)
                    {
                        string schemaName = dtTables.Rows[i]["TABLE_SCHEMA"] as string;
                        string tableName = dtTables.Rows[i]["TABLE_NAME"] as string;

                        Table table = new Table() { SchemaName = schemaName, TableName = tableName };
                        LoadColumns2(database.Datasource.ProviderManifestToken, oleDbConnection, table, TableType.Table);

                        table.Constraints = database.Constraints.Where(constraint => constraint.FKTableName == tableName).ToDatabaseObjectsCollection(table);
                        tables.Add(table);
                    }
                }
                oleDbConnection.Close();
            }

            return tables;
        }

        public override DatabaseObjectsCollection<IView> LoadViews(IDatabase database)
        {
            DatabaseObjectsCollection<IView> views = new DatabaseObjectsCollection<IView>(database);

            OleDbConnection oleDbConnection = new OleDbConnection(database.ConnectionString);

            using (OleDbDataAdapter da = new OleDbDataAdapter(_getViews, oleDbConnection))
            {
                DataTable dtViews = new DataTable("Views");
                da.Fill(dtViews);

                for (int i = 0; i < dtViews.Rows.Count; i++)
                {
                    string schemaName = dtViews.Rows[i]["TABLE_SCHEMA"] as string;
                    string viewName = dtViews.Rows[i]["TABLE_NAME"] as string;

                    View view = new View() { SchemaName = schemaName, TableName = viewName, Query = LoadViewQuery(oleDbConnection, schemaName, viewName) };            
                    LoadColumns2(database.Datasource.ProviderManifestToken, oleDbConnection, view, TableType.View);
                    views.Add(view);
                }
            }

            return views;
        }

        private string LoadViewQuery(OleDbConnection oracleConnection, string schemaName, string tableName)
        {
            string definingQuery = string.Empty;

            return definingQuery;
        }

        public override DatabaseObjectsCollection<IProcedure> LoadProcedures(IDatabase database)
        {
            DatabaseObjectsCollection<IProcedure> procedures = new DatabaseObjectsCollection<IProcedure>(database);

            OleDbConnection oleDbConnection = new OleDbConnection(database.ConnectionString);

            //using (OleDbDataAdapter da = new OleDbDataAdapter(_getProcedures, oleDbConnection))
            //{
            //    DataTable dtProcedures = new DataTable("Procedures");
            //    da.Fill(dtProcedures);

            //    for (int i = 0; i < dtProcedures.Rows.Count; i++)
            //    {
            //        Procedure procedure = new Procedure();
            //        procedure.Name = dtProcedures.Rows[i]["ROUTINE_NAME"] as string;
            //        procedure.SchemaName = dtProcedures.Rows[i]["ROUTINE_SCHEMA"] as string;
            //        if (dtProcedures.Rows[i]["DATA_TYPE"] != DBNull.Value)
            //            procedure.DataType = (string)dtProcedures.Rows[i]["DATA_TYPE"];
            //        if (dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
            //            procedure.Length = Convert.ToInt32(dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"]);

            //        if (procedure.Length == -1)
            //        {
            //            switch (procedure.DataType.ToLower())
            //            {
            //                case "varchar":
            //                case "nvarchar":
            //                case "varchar2":
            //                    procedure.DataType += "(max)";
            //                    break;
            //                default:
            //                    break;
            //            }
            //        }

            //        string procedureType = dtProcedures.Rows[i]["ROUTINE_BODY"] as string;
            //        if (procedureType == "SQL")
            //            procedure.ProcedureType = ProcedureType.SQL;
            //        else
            //            procedure.ProcedureType = ProcedureType.External;


            //        procedures.Add(procedure);
            //    }
            //}

            return procedures;
        }

        public override string ToString()
        {
            return this.Name;
        }

        public override IDatasource CreateNewIDatasource( string server , string userid , string password )
        {
            AccessDatasource ds = new AccessDatasource(this);
            //connectionString//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\临时资料\MyDb.mdb;User Id=Admin;Password=;Jet OLEDB:Database Password=123;
            ds.Provider = "Microsoft.Jet.OLEDB.4.0";
            ds.Name = server;
            ds.Server = server;
            ds.UserId = userid;
            //ds.Password = password;
            ds.DataBasePassword = password;
            return ds;
        }

        #region 获取Access数据库中表的列信息

        /// <summary>
        /// 获取Access数据库中表的列信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">要获取列信息的表名</param>
        /// <returns>返回表的列信息数据集</returns>
        private DataSet GetOleDbColumns(IDatabase database, string tableName)
        {
            DataTable dtKey = this.GetPrimaryInfo(database, tableName);//获取主键信息  
            DataTable result = this.GetColumnInfo(database, tableName);//获取列信息  
            DataSet ds = GetAccessTableColumns(dtKey, result);
            return ds;
        }

        /// <summary>
        /// 获取Access表列信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回列信息结果表</returns>
        private DataTable GetColumnInfo(IDatabase database, string tableName)
        {
            using (OleDbConnection connection = new OleDbConnection(database.ConnectionString))
            {
                connection.Open();
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null });
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = string.Format("table_name='{0}'", tableName);
                view.Sort = "ORDINAL_POSITION";
                return view.ToTable();
            }
        }

        /// <summary>
        /// 获取Access表主键信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回主键信息结果表</returns>
        private DataTable GetPrimaryInfo(IDatabase database, string tableName)
        {
            using (OleDbConnection connection = new OleDbConnection(database.ConnectionString))
            {
                connection.Open();
                DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null });
                DataView view = new DataView();
                view.Table = dt;
                view.RowFilter = string.Format("table_name='{0}'", tableName);
                return view.ToTable();
            }
        }

        /// <summary>
        /// 获取Access数据库中表的列信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">要获取列信息的表名</param>
        /// <returns>返回表的列信息数据集</returns>
        private DataSet GetOleDbColumns(OleDbConnection connection, string tableName)
        {
            DataTable dtKey = this.GetPrimaryInfo(connection, tableName);//获取主键信息  
            DataTable result = this.GetColumnInfo(connection, tableName);//获取列信息  
            DataSet ds = GetAccessTableColumns(dtKey, result);
            return ds;
        }

        

        /// <summary>
        /// 获取Access表列信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回列信息结果表</returns>
        private DataTable GetColumnInfo(OleDbConnection connection, string tableName)
        {
            DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null });
            DataView view = new DataView();
            view.Table = dt;
            view.RowFilter = string.Format("table_name='{0}'", tableName);
            view.Sort = "ORDINAL_POSITION";
            return view.ToTable();
        }

        /// <summary>
        /// 获取Access表主键信息
        /// </summary>
        /// <param name="database">Access数据库对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回主键信息结果表</returns>
        private DataTable GetPrimaryInfo(OleDbConnection connection, string tableName)
        {
            DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null });
            DataView view = new DataView();
            view.Table = dt;
            view.RowFilter = string.Format("table_name='{0}'", tableName);
            return view.ToTable();
        }

        private DataSet GetAccessTableColumns(DataTable dtKey, DataTable result)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ColumnId", typeof(string)));
            dt.Columns.Add(new DataColumn("Name", typeof(string)));
            dt.Columns.Add(new DataColumn("DataType", typeof(string)));
            dt.Columns.Add(new DataColumn("SystemType", typeof(string)));
            dt.Columns.Add(new DataColumn("Length", typeof(string)));
            dt.Columns.Add(new DataColumn("NumericPrecision", typeof(string)));
            dt.Columns.Add(new DataColumn("IsAnsiPadded", typeof(int)));
            dt.Columns.Add(new DataColumn("IsComputed", typeof(int)));
            dt.Columns.Add(new DataColumn("LsDtsReplicated", typeof(int)));
            dt.Columns.Add(new DataColumn("IsFileStream", typeof(int)));
            dt.Columns.Add(new DataColumn("IsIdentity", typeof(int)));
            dt.Columns.Add(new DataColumn("IsMergePublished", typeof(int)));
            dt.Columns.Add(new DataColumn("IsNullable", typeof(int)));
            dt.Columns.Add(new DataColumn("IsRowGuidCol", typeof(int)));
            dt.Columns.Add(new DataColumn("IsSpares", typeof(int)));
            dt.Columns.Add(new DataColumn("IsXmlDocument", typeof(int)));
            dt.Columns.Add(new DataColumn("ObjectId", typeof(int)));
            dt.Columns.Add(new DataColumn("RuleObjectId", typeof(int)));
            dt.Columns.Add(new DataColumn("Scale", typeof(int)));
            dt.Columns.Add(new DataColumn("SystemTypeId", typeof(int)));
            dt.Columns.Add(new DataColumn("UserTypeId", typeof(int)));
            dt.Columns.Add(new DataColumn("XMLCollectionId", typeof(int)));
            dt.Columns.Add(new DataColumn("IsPrimaryKey", typeof(int)));
            foreach (DataRow row in result.Rows)
            {
                DataRow r = dt.NewRow();
                r["ColumnId"] = row["ORDINAL_POSITION"].ToString();//  
                r["Name"] = row["COLUMN_NAME"].ToString();
                r["DataType"] = row["DATA_TYPE"].ToString();
                r["SystemType"] = row["DATA_TYPE"].ToString();
                r["Length"] = row["CHARACTER_MAXIMUM_LENGTH"].ToString();
                r["NumericPrecision"] = row["NUMERIC_PRECISION"].ToString();
                r["IsAnsiPadded"] = 0;
                r["IsComputed"] = 0;
                r["LsDtsReplicated"] = 0;
                r["IsFileStream"] = 0;
                r["IsIdentity"] = 0;
                r["IsMergePublished"] = 0;
                r["IsNullable"] = row["IS_NULLABLE"];
                r["IsRowGuidCol"] = 0;
                r["IsSpares"] = 0;
                r["IsXmlDocument"] = 0;
                r["ObjectId"] = 0;
                r["RuleObjectId"] = 0;
                r["Scale"] = row["NUMERIC_SCALE"];
                r["SystemTypeId"] = 0;
                r["UserTypeId"] = 0;
                r["XMLCollectionId"] = 0;
                r["IsPrimaryKey"] = dtKey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? 1 : 0;//是否是主键  
                dt.Rows.Add(r);
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            return ds;
        }

        #endregion
    }
}