Exemplo n.º 1
0
        public EntityInfo[] GetEntityDefinitions()
        {
            ValidateConnection();

            var entities = new List <EntityInfo>();

            using (var connection = new SqlCeConnection(m_connectionString))
                using (var cmd = new SqlCeCommand(GetTablesSQL, connection))
                {
                    connection.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var info      = new EntityInfo();
                            var indexInfo = new Dictionary <string, IndexInfo>();

                            info.Entity             = new EntityAttribute();
                            info.Entity.NameInStore = reader.GetString(0);

                            using (var indexCommand = new SqlCeCommand(
                                       string.Format("SELECT INDEX_NAME, PRIMARY_KEY, COLUMN_NAME, COLLATION FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = '{0}'", info.Entity.NameInStore),
                                       connection))
                                using (var indexReader = indexCommand.ExecuteReader())
                                {
                                    while (indexReader.Read())
                                    {
                                        var indexName  = indexReader.GetString(0);
                                        var primaryKey = indexReader.GetBoolean(1);
                                        var columnName = indexReader.GetString(2);
                                        var sortOrder  = indexReader.GetInt16(3) == 1 ? FieldSearchOrder.Ascending : FieldSearchOrder.Descending;
                                        // collation of 1 == ascending, 2 == descending (based on a quick test, this might be incorrect)

                                        // TODO: handle cases where a column is in multiple indexes (ORM doesn't support that scenario for now)
                                        if (!indexInfo.ContainsKey(columnName))
                                        {
                                            indexInfo.Add(columnName, new IndexInfo()
                                            {
                                                ColumnName  = columnName,
                                                IndexName   = indexName,
                                                PrimaryKey  = primaryKey,
                                                SearchOrder = sortOrder
                                            });
                                        }
                                    }
                                }

                            // TODO: look for primary key to set key scheme
                            info.Entity.KeyScheme = KeyScheme.None;

                            using (var fieldCommand = new SqlCeCommand(
                                       string.Format("SELECT COLUMN_NAME, COLUMN_HASDEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, AUTOINC_SEED FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", info.Entity.NameInStore),
                                       connection))
                            {
                                using (var fieldReader = fieldCommand.ExecuteReader())
                                {
                                    while (fieldReader.Read())
                                    {
                                        var field = new FieldAttribute();
                                        field.FieldName   = fieldReader.GetString(0);
                                        field.AllowsNulls = string.Compare(fieldReader.GetString(2), "YES", true) == 0;
                                        field.DataType    = fieldReader.GetString(3).ParseToDbType();
                                        object val = fieldReader[4];
                                        if (!val.Equals(DBNull.Value))
                                        {
                                            field.Length = Convert.ToInt32(val);
                                        }
                                        val = fieldReader[5];
                                        if (!val.Equals(DBNull.Value))
                                        {
                                            field.Precision = Convert.ToInt32(val);
                                        }
                                        val = fieldReader[6];
                                        if (!val.Equals(DBNull.Value))
                                        {
                                            field.Scale = Convert.ToInt32(val);
                                        }
                                        val = fieldReader[7];
                                        if (!val.Equals(DBNull.Value))
                                        {
                                            // identity field, so it must be the PK (or part of it)
                                            info.Entity.KeyScheme = KeyScheme.Identity;
                                        }

                                        // check for indexes
                                        if (indexInfo.ContainsKey(field.FieldName))
                                        {
                                            var idx = indexInfo[field.FieldName];

                                            if (idx.PrimaryKey)
                                            {
                                                field.IsPrimaryKey = true;

                                                if (field.DataType == DbType.Guid)
                                                {
                                                    info.Entity.KeyScheme = KeyScheme.GUID;
                                                }
                                            }
                                            field.SearchOrder = idx.SearchOrder;
                                        }

                                        // TODO: populate the remainder of the field info
                                        info.Fields.Add(field);
                                    }
                                }
                            }

                            // check for references
                            using (var referenceSourceCommand = new SqlCeCommand(
                                       string.Format(
                                           "SELECT a.Constraint_name, a.TABLE_NAME, b.COLUMN_NAME " +
                                           "FROM information_schema.table_constraints AS a " +
                                           "INNER JOIN information_schema.KEY_COLUMN_USAGE AS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME " +
                                           "WHERE (a.TABLE_NAME = '{0}') AND (a.CONSTRAINT_TYPE = 'FOREIGN KEY')",
                                           info.Entity.NameInStore),
                                       connection))
                            {
                                string constraintName  = null;
                                string localField      = null;
                                string remoteTable     = null;
                                string remoteFieldName = null;
                                bool   referenceExists = false;

                                using (var srcReader = referenceSourceCommand.ExecuteReader())
                                {
                                    while (srcReader.Read())
                                    {
                                        constraintName = (string)srcReader[0];
                                        localField     = (string)srcReader[2];

                                        using (var referenceTargetCommand = new SqlCeCommand(
                                                   string.Format(
                                                       "SELECT a.UNIQUE_CONSTRAINT_TABLE_NAME, b.COLUMN_NAME " +
                                                       "FROM information_schema.REFERENTIAL_CONSTRAINTS AS a INNER JOIN " +
                                                       "information_schema.KEY_COLUMN_USAGE AS b ON a.Constraint_name = b.CONSTRAINT_NAME " +
                                                       "WHERE a.CONSTRAINT_NAME = '{0}'",
                                                       constraintName),
                                                   connection))
                                        {
                                            using (var targetReader = referenceTargetCommand.ExecuteReader())
                                            {
                                                while (targetReader.Read())
                                                {
                                                    remoteTable     = (string)targetReader[0];
                                                    remoteFieldName = (string)targetReader[1];
                                                    referenceExists = true;
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }

                                if (referenceExists)
                                {
                                    var reference = new ReferenceInfo()
                                    {
                                        ReferenceTable  = remoteTable,
                                        LocalFieldName  = localField,
                                        RemoteFieldName = remoteFieldName
                                    };
                                    info.References.Add(reference);
                                }
                            }
                            entities.Add(info);
                        }
                    }
                }

            return(entities.ToArray());
        }
Exemplo n.º 2
0
        public EntityInfo[] GetEntityDefinitions()
        {
            ValidateConnection();

            var entities = new List <EntityInfo>();

            // PRAGMA table_info(Clienti)
            // PRAGMA index_list(Clienti)

            using (var connection = new SQLiteConnection(m_connectionString))
                using (var cmd = new SQLiteCommand(GetTablesSQL, connection))
                {
                    connection.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var info = new EntityInfo();

                            info.Entity             = new EntityAttribute();
                            info.Entity.NameInStore = reader.GetString(0);

                            if (string.Compare(info.Entity.NameInStore, "sqlite_sequence", true) == 0)
                            {
                                // this is the auto-increment meta table, skip it
                                continue;
                            }

                            using (var ticmd = new SQLiteCommand(string.Format("PRAGMA table_info({0})", info.Entity.NameInStore), connection))
                                using (var tireader = ticmd.ExecuteReader())
                                {
                                    while (tireader.Read())
                                    {
                                        var cid        = tireader["cid"];
                                        var name       = tireader["name"];
                                        var type       = tireader["type"];
                                        var notnull    = tireader["notnull"];
                                        var dflt_value = tireader["dflt_value"];
                                        var pk         = tireader["pk"];

                                        var field = new FieldAttribute();
                                        field.FieldName    = (string)name;
                                        field.AllowsNulls  = !Convert.ToBoolean(notnull);
                                        field.IsPrimaryKey = Convert.ToBoolean(pk);

                                        field.DataType = ((string)type).ParseToDbType(true);


                                        // TODO: handle default values
                                        // TODO: determine if we have auto-increment

                                        info.Fields.Add(field);
                                    }
                                }

                            // check for indexes (for sort order)
                            using (var idxcmd = new SQLiteCommand(string.Format("SELECT sql FROM sqlite_master WHERE type = 'index' AND tbl_name = '{0}'", info.Entity.NameInStore), connection))
                                using (var idxreader = idxcmd.ExecuteReader())
                                {
                                    while (idxreader.Read())
                                    {
                                        if (idxreader[0] == DBNull.Value)
                                        {
                                            // PK or UNIQUE index
                                            continue;
                                        }
                                        var sql       = idxreader.GetString(0);
                                        var indexInfo = sql.ParseToIndexInfo();

                                        if (indexInfo.IsComposite)
                                        {
                                            Debug.WriteLine("Composite indexes not currently supported!");
                                            continue;
                                        }

                                        var indexedField = (from f in info.Fields
                                                            where string.Compare(f.FieldName, indexInfo.Fields[0], true) == 0
                                                            select f).FirstOrDefault();

                                        if (indexedField != null)
                                        {
                                            indexedField.SearchOrder = indexInfo.SearchOrder;
                                        }
                                    }
                                }


                            // check for references
                            using (var fkcmd = new SQLiteCommand(String.Format("PRAGMA foreign_key_list({0})", info.Entity.NameInStore), connection))
                                using (var fkreader = fkcmd.ExecuteReader())
                                {
                                    while (fkreader.Read())
                                    {
                                        var reference = new ReferenceInfo();
                                        reference.ReferenceTable  = (string)fkreader["table"];
                                        reference.LocalFieldName  = (string)fkreader["from"];
                                        reference.RemoteFieldName = (string)fkreader["to"];
                                        info.References.Add(reference);
                                    }
                                }

                            entities.Add(info);
                        }
                    }
                }

            return(entities.ToArray());
        }