miniSql

创建
zgc123@gmail.com authored at 11/19/2023 1:40:15 AM
6136600
Tree
0 Parent(s)
Summary: 1 changed files with 323 additions and 0 deletions.
Added +323 -0
Added +323 -0
diff --git a/minisqlquery-master/src/MiniSqlQuery.Core/DbModel/SqlCeSchemaService.cs b/minisqlquery-master/src/MiniSqlQuery.Core/DbModel/SqlCeSchemaService.cs
new file mode 100644
index 0000000..65fec8a
--- /dev/null
+++ b/minisqlquery-master/src/MiniSqlQuery.Core/DbModel/SqlCeSchemaService.cs
@@ -0,0 +1,323 @@
+#region License
+
+// Copyright 2005-2019 Paul Kohler (https://github.com/paulkohler/minisqlquery). All rights reserved.
+// This source code is made available under the terms of the GNU Lesser General Public License v3.0
+// https://github.com/paulkohler/minisqlquery/blob/master/LICENSE
+#endregion
+
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.Common;
+
+namespace MiniSqlQuery.Core.DbModel
+{
+    /// <summary>SQL Compact Edition schema service.
+    /// Made possible with contributions form ExportSQLCE project.
+    /// http://exportsqlce.codeplex.com/
+    /// http://erikej.blogspot.com/</summary>
+    public class SqlCeSchemaService : GenericSchemaService
+    {
+        /// <summary>The ma x_ binar y_ colum n_ size.</summary>
+        internal static readonly int MAX_BINARY_COLUMN_SIZE = 8000;
+
+        /// <summary>The ma x_ imag e_ colum n_ size.</summary>
+        internal static readonly int MAX_IMAGE_COLUMN_SIZE = 1073741823;
+
+        /// <summary>The ma x_ ncha r_ colum n_ size.</summary>
+        internal static readonly int MAX_NCHAR_COLUMN_SIZE = 4000;
+
+        /// <summary>The ma x_ ntex t_ colum n_ size.</summary>
+        internal static readonly int MAX_NTEXT_COLUMN_SIZE = 536870911;
+
+        /// <summary>The _connection.</summary>
+        private string _connection;
+
+        /// <summary>The get db object model.</summary>
+        /// <param name="connection">The connection.</param>
+        /// <returns></returns>
+        public override DbModelInstance GetDbObjectModel(string connection)
+        {
+            _connection = connection;
+
+            DbModelInstance model = new DbModelInstance();
+            DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
+
+            using (DbConnection dbConn = factory.CreateConnection())
+            {
+                dbConn.ConnectionString = connection;
+                dbConn.Open();
+
+                QueryTableNames(dbConn, model);
+                Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn);
+                model.Types = dbTypes;
+                model.ProviderName = ProviderName;
+                model.ConnectionString = _connection;
+
+                // build all table info
+                foreach (DbModelTable table in model.Tables)
+                {
+                    DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, null, table.Name);
+                    GetColumnsForTable(table, schemaTableKeyInfo, dbTypes);
+                }
+
+                // build FK relationships
+                foreach (DbModelTable table in model.Tables)
+                {
+                    GetForeignKeyReferencesForTable(dbConn, table);
+                    ProcessForeignKeyReferencesForTable(dbConn, table);
+                }
+
+                return model;
+            }
+        }
+
+        /// <summary>Gets the db types for the SQL CE provider.</summary>
+        /// <param name="connection">The connection (not required).</param>
+        /// <returns></returns>
+        public override Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
+        {
+            Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
+            string dataTypesSql = "SELECT * FROM information_schema.provider_types";
+            using (var cmd = connection.CreateCommand())
+            {
+                cmd.CommandText = dataTypesSql;
+                cmd.CommandType = CommandType.Text;
+                using (var reader = cmd.ExecuteReader())
+                {
+                    while (reader.Read())
+                    {
+                        string typeName = (string)reader["TYPE_NAME"];
+                        int columnSize = Convert.ToInt32(reader["COLUMN_SIZE"]);
+                        DbModelType dbType = new DbModelType(typeName, columnSize);
+
+                        dbType.CreateParameters = Convert.ToString(reader["CREATE_PARAMS"]);
+                        dbType.LiteralPrefix = Convert.ToString(reader["LITERAL_PREFIX"]);
+                        dbType.LiteralSuffix = Convert.ToString(reader["LITERAL_SUFFIX"]);
+                        dbType.ProviderDbType = Convert.ToString(reader["DATA_TYPE"]);
+
+                        FixCreateFormat(dbType);
+                        FixMaxLengths(dbType);
+                        AssignSystemTypes(dbType);
+
+                        dbTypes.Add(typeName, dbType);
+                    }
+                }
+            }
+
+            return dbTypes;
+        }
+
+        /// <summary>The get data type name for column.</summary>
+        /// <param name="dbTable">The db table.</param>
+        /// <param name="schemaTableKeyInfo">The schema table key info.</param>
+        /// <param name="columnRow">The column row.</param>
+        /// <returns>The get data type name for column.</returns>
+        protected override string GetDataTypeNameForColumn(DbModelTable dbTable, DataTable schemaTableKeyInfo, DataRow columnRow)
+        {
+            return SafeGetString(columnRow, "ProviderType");
+        }
+
+        /// <summary>The get foreign key references for table.</summary>
+        /// <param name="dbConn">The db conn.</param>
+        /// <param name="dbTable">The db table.</param>
+        protected override void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
+        {
+            ForeignKeyInformationAvailable = true;
+            try
+            {
+                using (var cmd = dbConn.CreateCommand())
+                {
+                    cmd.CommandText =
+                        string.Format(
+                            @"SELECT 
+	KCU1.TABLE_NAME AS FK_TABLE_NAME,  
+	KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, 
+	KCU1.COLUMN_NAME AS FK_COLUMN_NAME,
+	KCU2.TABLE_NAME AS UQ_TABLE_NAME, 
+	KCU2.CONSTRAINT_NAME AS UQ_CONSTRAINT_NAME, 
+	KCU2.COLUMN_NAME AS UQ_COLUMN_NAME, 
+	RC.UPDATE_RULE, 
+	RC.DELETE_RULE, 
+	KCU2.ORDINAL_POSITION AS UQ_ORDINAL_POSITION, 
+	KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
+FROM 
+	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
+		JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
+			JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON  KCU2.CONSTRAINT_NAME =  RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME 
+WHERE KCU1.TABLE_NAME = '{0}'
+ORDER BY 
+	FK_TABLE_NAME, 
+	FK_CONSTRAINT_NAME, 
+	FK_ORDINAL_POSITION
+",
+                            dbTable.Name);
+                    cmd.CommandType = CommandType.Text;
+                    using (var dr = cmd.ExecuteReader())
+                    {
+                        while (dr.Read())
+                        {
+                            dbTable.Constraints.Add(new DbModelConstraint
+                            {
+                                ConstraintTableName = dr.GetString(0),
+                                ConstraintName = dr.GetString(1),
+                                ColumnName = dr.GetString(2),
+                                UniqueConstraintTableName = dr.GetString(3),
+                                UniqueConstraintName = dr.GetString(4),
+                                UniqueColumnName = dr.GetString(5),
+                                UpdateRule = dr.GetString(6),
+                                DeleteRule = dr.GetString(7)
+                            });
+                        }
+                    }
+                }
+            }
+            catch (DbException)
+            {
+                ForeignKeyInformationAvailable = false;
+            }
+        }
+
+        /// <summary>The process foreign key references for table.</summary>
+        /// <param name="dbConn">The db conn.</param>
+        /// <param name="dbTable">The db table.</param>
+        protected override void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
+        {
+            // todo - check GetGroupForeingKeys
+            foreach (DbModelConstraint constraint in dbTable.Constraints)
+            {
+                var column = dbTable.Columns.Find(c => c.Name == constraint.ColumnName);
+                var refTable = dbTable.ParentDb.FindTable(
+                    Utility.RenderSafeSchemaObjectName(constraint.UniqueConstraintTableSchema, constraint.UniqueConstraintTableName));
+                var refColumn = refTable.Columns.Find(c => c.Name == constraint.UniqueColumnName);
+                DbModelForeignKeyReference fk = new DbModelForeignKeyReference(column, refTable, refColumn);
+                fk.UpdateRule = constraint.UpdateRule;
+                fk.DeleteRule = constraint.DeleteRule;
+                column.ForeignKeyReference = fk;
+            }
+        }
+
+        /// <summary>The assign system types.</summary>
+        /// <param name="dbType">The db type.</param>
+        private void AssignSystemTypes(DbModelType dbType)
+        {
+            switch (dbType.Name.ToLower())
+            {
+                case "smallint":
+                    dbType.SystemType = typeof(byte);
+                    break;
+
+                case "int":
+                    dbType.SystemType = typeof(int);
+                    break;
+
+                case "tinyint":
+                    dbType.SystemType = typeof(byte);
+                    break;
+
+                case "bigint":
+                    dbType.SystemType = typeof(long);
+                    break;
+
+                case "float":
+                    dbType.SystemType = typeof(double); // yes, float is double ;-)
+                    break;
+
+                case "numeric":
+                case "money":
+                case "real":
+                    dbType.SystemType = typeof(decimal);
+                    break;
+
+                case "bit":
+                    dbType.SystemType = typeof(bool);
+                    break;
+
+                case "uniqueidentifier":
+                    dbType.SystemType = typeof(Guid);
+                    break;
+
+                case "nvarchar":
+                case "nchar":
+                case "ntext":
+                    dbType.SystemType = typeof(string);
+                    break;
+
+                case "datetime":
+                    dbType.SystemType = typeof(DateTime);
+                    break;
+
+                case "varbinary":
+                case "binary":
+                case "image":
+                case "rowversion":
+                    dbType.SystemType = typeof(byte[]);
+                    break;
+            }
+        }
+
+        /// <summary>The fix create format.</summary>
+        /// <param name="dbType">The db type.</param>
+        private void FixCreateFormat(DbModelType dbType)
+        {
+            switch (dbType.Name.ToLower())
+            {
+                case "nchar":
+                case "nvarchar":
+                case "binary":
+                case "varbinary":
+                    dbType.CreateFormat = dbType.Name.ToLower() + "({0})";
+                    break;
+
+                case "decimal":
+                case "numeric":
+                    dbType.CreateFormat = dbType.Name.ToLower() + "({0}, {1})";
+                    break;
+            }
+        }
+
+        /// <summary>The fix max lengths.</summary>
+        /// <param name="dbType">The db type.</param>
+        private void FixMaxLengths(DbModelType dbType)
+        {
+            switch (dbType.Name.ToLower())
+            {
+                case "nchar":
+                case "nvarchar":
+                    dbType.Length = MAX_NCHAR_COLUMN_SIZE;
+                    break;
+                case "ntext":
+                    dbType.Length = MAX_NTEXT_COLUMN_SIZE;
+                    break;
+                case "binary":
+                case "varbinary":
+                    dbType.Length = MAX_BINARY_COLUMN_SIZE;
+                    break;
+                case "image":
+                    dbType.Length = MAX_IMAGE_COLUMN_SIZE;
+                    break;
+            }
+        }
+
+        /// <summary>The query table names.</summary>
+        /// <param name="dbConn">The db conn.</param>
+        /// <param name="model">The model.</param>
+        private void QueryTableNames(DbConnection dbConn, DbModelInstance model)
+        {
+            using (var cmd = dbConn.CreateCommand())
+            {
+                cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE = N'TABLE'";
+                cmd.CommandType = CommandType.Text;
+                using (var reader = cmd.ExecuteReader())
+                {
+                    while (reader.Read())
+                    {
+                        DbModelTable table = new DbModelTable();
+                        table.Name = (string)reader["table_name"];
+                        model.Add(table);
+                    }
+                }
+            }
+        }
+    }
+}
\ No newline at end of file