Exemple #1
0
        override internal void LoadForTable()
        {
            string query = @"SHOW COLUMNS FROM `" + this.Table.Name + "`";

            DataTable     metaData = new DataTable();
            DbDataAdapter adapter  = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);

            adapter.Fill(metaData);

            if (metaData.Columns.Contains("Extra"))
            {
                f_Extra = metaData.Columns["Extra"];
            }

            metaData.Columns["Field"].ColumnName   = "COLUMN_NAME";
            metaData.Columns["Type"].ColumnName    = "DATA_TYPE";
            metaData.Columns["Null"].ColumnName    = "IS_NULLABLE";
            metaData.Columns["Default"].ColumnName = "COLUMN_DEFAULT";

            PopulateArray(metaData);

            LoadTableColumnDescriptions();
        }
Exemple #2
0
        private void LoadTableDescriptions()
        {
            try
            {
                string query = @"SELECT TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + this.Database.Name + "'";

                DataTable     metaData = new DataTable();
                DbDataAdapter adapter  = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);

                adapter.Fill(metaData);

                if (this.Database.Tables.Count > 0)
                {
                    Table t = this.Database.Tables[0] as Table;

                    if (!t._row.Table.Columns.Contains("DESCRIPTION"))
                    {
                        t._row.Table.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
                        this.f_Description = t._row.Table.Columns["DESCRIPTION"];
                    }

                    if (!t._row.Table.Columns.Contains("TABLE_SCHEMA"))
                    {
                        t._row.Table.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
                        this.f_Schema = t._row.Table.Columns["TABLE_SCHEMA"];
                    }

                    if (!t._row.Table.Columns.Contains("DATE_CREATED"))
                    {
                        t._row.Table.Columns.Add("DATE_CREATED", Type.GetType("System.DateTime"));
                        this.f_DateCreated = t._row.Table.Columns["DATE_CREATED"];
                    }

                    if (!t._row.Table.Columns.Contains("DATE_MODIFIED"))
                    {
                        t._row.Table.Columns.Add("DATE_MODIFIED", Type.GetType("System.DateTime"));
                        this.f_DateModified = t._row.Table.Columns["DATE_MODIFIED"];
                    }
                }

                if (metaData.Rows.Count > 0)
                {
                    foreach (DataRow row in metaData.Rows)
                    {
                        Table t = this[row["TABLE_NAME"] as string] as Table;

                        t._row["DESCRIPTION"]  = row["TABLE_COMMENT"] as string;
                        t._row["TABLE_SCHEMA"] = this.Database.Name;

                        if (row["CREATE_TIME"] != DBNull.Value)
                        {
                            t._row["DATE_CREATED"] = (DateTime)row["CREATE_TIME"];
                        }

                        if (row["UPDATE_TIME"] != DBNull.Value)
                        {
                            t._row["DATE_MODIFIED"] = (DateTime)row["UPDATE_TIME"];
                        }
                    }
                }
            }
            catch {}
        }
Exemple #3
0
        internal void AddMyHalf()
        {
            string query = @"SHOW CREATE TABLE `" + this.Table.Name + "`";

            DataTable     dt      = new DataTable();
            DbDataAdapter adapter = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);

            adapter.Fill(dt);

            string text = dt.Rows[0][1] as string;

            // CONSTRAINT `FK_mastertypes_3` FOREIGN KEY (`TheINT1`, `TheINT2`) REFERENCES `employee` (`TheInt1`, `TheInt2`),
            // CONSTRAINT `FK_mastertypes_1` FOREIGN KEY (`MyPK`) REFERENCES `employee` (`EmployeeID`),
            // CONSTRAINT `FK_mastertypes_2` FOREIGN KEY (`TheVARCHAR`) REFERENCES `employee` (`LastName`)
            // CONSTRAINT `ShippersOrders` FOREIGN KEY (`ShipVia`) REFERENCES `shippers` (`ShipperID`) ON DELETE NO ACTION ON UPDATE NO ACTION\n)

            DataTable metaData = new DataTable();

            metaData.Columns.Add("PK_TABLE_CATALOG");
            metaData.Columns.Add("PK_TABLE_SCHEMA");
            metaData.Columns.Add("PK_TABLE_NAME");
            metaData.Columns.Add("FK_TABLE_CATALOG");
            metaData.Columns.Add("FK_TABLE_SCHEMA");
            metaData.Columns.Add("FK_TABLE_NAME");
            metaData.Columns.Add("ORDINAL");
            metaData.Columns.Add("UPDATE_RULE");
            metaData.Columns.Add("DELETE_RULE");
            metaData.Columns.Add("PK_NAME");
            metaData.Columns.Add("FK_NAME");
            metaData.Columns.Add("DEFERRABILITY");
            metaData.Columns.Add("PK_COLUMN_NAME");
            metaData.Columns.Add("FK_COLUMN_NAME");

            string s = "";

            string[] fkRec     = null;
            string[] pkColumns = null;
            string[] fkColumns = null;

            int iStart = 0;

            while (true)
            {
                iStart = text.IndexOf("CONSTRAINT", iStart);
                if (iStart == -1)
                {
                    break;
                }
                int iEnd = text.IndexOf('\n', iStart);

                string fk = text.Substring(iStart, iEnd - iStart);

                iStart = iEnd + 2;

                if (-1 != fk.IndexOf("FOREIGN KEY"))
                {
                    // MySQL 5.0 trick !!
                    int index = fk.IndexOf(")");
                    index = fk.IndexOf(")", index + 1);
                    s     = fk.Substring(0, index);
                    //


                    // Munge it down it a record I can split with a ',' seperator
                    s = s.Replace("`", "");
                    s = s.Replace(" ", "");
                    s = s.Replace("),", "");
                    s = s.Replace(",", "|");
                    s = s.Replace("CONSTRAINT", "");
                    s = s.Replace("FOREIGNKEY", "");
                    s = s.Replace("REFERENCES", ",");
                    s = s.Replace("(", ",");
                    s = s.Replace(")", "");

                    fkRec = s.Split(',');

                    fkColumns = fkRec[1].Split('|');
                    pkColumns = fkRec[3].Split('|');

                    for (int i = 0; i < pkColumns.Length; i++)
                    {
                        DataRow row = metaData.NewRow();
                        metaData.Rows.Add(row);

                        row["PK_TABLE_CATALOG"] = this.Table.Database.Name;
                        row["FK_TABLE_CATALOG"] = this.Table.Database.Name;

                        row["PK_TABLE_NAME"]  = fkRec[2];
                        row["FK_TABLE_NAME"]  = this.Table.Name;
                        row["FK_NAME"]        = fkRec[0];
                        row["PK_COLUMN_NAME"] = pkColumns[i];
                        row["FK_COLUMN_NAME"] = fkColumns[i];

                        row["ORDINAL"] = i;

                        //  ON DELETE NO ACTION ON UPDATE NO ACTION\n)
                        try
                        {
                            row["DELETE_RULE"] = "RESTRICT";
                            int ond = fk.IndexOf("ON DELETE");
                            if (-1 != ond)
                            {
                                char c = fk[ond + 10];

                                switch (c)
                                {
                                case 'R':
                                case 'r':

                                    row["DELETE_RULE"] = "RESTRICT";
                                    break;

                                case 'C':
                                case 'c':

                                    row["DELETE_RULE"] = "CASCADE";
                                    break;

                                case 'S':
                                case 's':

                                    row["DELETE_RULE"] = "SET NULL";
                                    break;

                                case 'N':
                                case 'n':

                                    row["DELETE_RULE"] = "NO ACTION";
                                    break;
                                }
                            }


                            row["UPDATE_RULE"] = "RESTRICT";
                            int onu = fk.IndexOf("ON UPDATE");
                            if (-1 != onu)
                            {
                                char c = fk[onu + 10];

                                switch (c)
                                {
                                case 'R':
                                case 'r':

                                    row["UPDATE_RULE"] = "RESTRICT";
                                    break;

                                case 'C':
                                case 'c':

                                    row["UPDATE_RULE"] = "CASCADE";
                                    break;

                                case 'S':
                                case 's':

                                    row["UPDATE_RULE"] = "SET NULL";
                                    break;

                                case 'N':
                                case 'n':

                                    row["UPDATE_RULE"] = "NO ACTION";
                                    break;
                                }
                            }
                        }
                        catch {}
                    }
                }
            }

            PopulateArray(metaData);
        }