internal MyCatSchemaRow AddRow() { MyCatSchemaRow r = new MyCatSchemaRow(this); rows.Add(r); return(r); }
private MyCatSchemaCollection GetTable(string sql) { MyCatSchemaCollection c = new MyCatSchemaCollection(); MyCatCommand cmd = new MyCatCommand(sql, connection); MyCatDataReader reader = cmd.ExecuteReader(); // add columns for (int i = 0; i < reader.FieldCount; i++) { c.AddColumn(reader.GetName(i), reader.GetFieldType(i)); } using (reader) { while (reader.Read()) { MyCatSchemaRow row = c.AddRow(); for (int i = 0; i < reader.FieldCount; i++) { row[i] = reader.GetValue(i); } } } return(c); }
private MyCatParameter GetAndFixParameter(string spName, MyCatSchemaRow param, bool realAsFloat, MyCatParameter returnParameter) { string mode = (string)param["PARAMETER_MODE"]; string pName = (string)param["PARAMETER_NAME"]; if (param["ORDINAL_POSITION"].Equals(0)) { if (returnParameter == null) { throw new InvalidOperationException( String.Format(Resources.RoutineRequiresReturnParameter, spName)); } pName = returnParameter.ParameterName; } // make sure the parameters given to us have an appropriate type set if it's not already MyCatParameter p = command.Parameters.GetParameterFlexible(pName, true); if (!p.TypeHasBeenSet) { string datatype = (string)param["DATA_TYPE"]; bool unsigned = GetFlags(param["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1; p.MyCatDbType = MetaData.NameToType(datatype, unsigned, realAsFloat, Connection); } return(p); }
public MyCatSchemaCollection(DataTable dt) : this() { // cache the original datatable to avoid the overhead of creating again whenever possible. _table = dt; int i = 0; foreach (DataColumn dc in dt.Columns) { columns.Add(new SchemaColumn() { Name = dc.ColumnName, Type = dc.DataType }); Mapping.Add(dc.ColumnName, i++); LogicalMappings[columns.Count - 1] = columns.Count - 1; } foreach (DataRow dr in dt.Rows) { MyCatSchemaRow row = new MyCatSchemaRow(this); for (i = 0; i < columns.Count; i++) { row[i] = dr[i]; } rows.Add(row); } }
private static void ParseConstraint(MyCatSchemaCollection fkTable, MyCatSchemaRow table, MyCatTokenizer tokenizer, bool includeColumns) { string name = tokenizer.NextToken(); MyCatSchemaRow row = fkTable.AddRow(); // make sure this constraint is a FK string token = tokenizer.NextToken(); if (token != "foreign" || tokenizer.Quoted) { return; } tokenizer.NextToken(); // read off the 'KEY' symbol tokenizer.NextToken(); // read off the '(' symbol row["CONSTRAINT_CATALOG"] = table["TABLE_CATALOG"]; row["CONSTRAINT_SCHEMA"] = table["TABLE_SCHEMA"]; row["TABLE_CATALOG"] = table["TABLE_CATALOG"]; row["TABLE_SCHEMA"] = table["TABLE_SCHEMA"]; row["TABLE_NAME"] = table["TABLE_NAME"]; row["REFERENCED_TABLE_CATALOG"] = null; row["CONSTRAINT_NAME"] = name.Trim(new char[] { '\'', '`' }); List <string> srcColumns = includeColumns ? ParseColumns(tokenizer) : null; // now look for the references section while (token != "references" || tokenizer.Quoted) { token = tokenizer.NextToken(); } string target1 = tokenizer.NextToken(); string target2 = tokenizer.NextToken(); if (target2.StartsWith(".", StringComparison.Ordinal)) { row["REFERENCED_TABLE_SCHEMA"] = target1; row["REFERENCED_TABLE_NAME"] = target2.Substring(1).Trim(new char[] { '\'', '`' }); tokenizer.NextToken(); // read off the '(' } else { row["REFERENCED_TABLE_SCHEMA"] = table["TABLE_SCHEMA"]; row["REFERENCED_TABLE_NAME"] = target1.Substring(1).Trim(new char[] { '\'', '`' });; } // if we are supposed to include columns, read the target columns List <string> targetColumns = includeColumns ? ParseColumns(tokenizer) : null; if (includeColumns) { ProcessColumns(fkTable, row, srcColumns, targetColumns); } else { fkTable.Rows.Add(row); } }
public virtual MyCatSchemaCollection GetIndexes(string[] restrictions) { MyCatSchemaCollection dt = new MyCatSchemaCollection("Indexes"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("UNIQUE", typeof(bool)); dt.AddColumn("PRIMARY", typeof(bool)); dt.AddColumn("TYPE", typeof(string)); dt.AddColumn("COMMENT", typeof(string)); // Get the list of tables first int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) { restrictions.CopyTo(tableRestrictions, 0); } tableRestrictions[3] = "BASE TABLE"; MyCatSchemaCollection tables = GetTables(tableRestrictions); foreach (MyCatSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", MyCatHelper.DoubleQuoteString((string)table["TABLE_SCHEMA"]), MyCatHelper.DoubleQuoteString((string)table["TABLE_NAME"])); MyCatSchemaCollection indexes = QueryCollection("indexes", sql); foreach (MyCatSchemaRow index in indexes.Rows) { long seq_index = (long)index["SEQ_IN_INDEX"]; if (seq_index != 1) { continue; } if (restrictions != null && restrictions.Length == 4 && restrictions[3] != null && !index["KEY_NAME"].Equals(restrictions[3])) { continue; } MyCatSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = index["KEY_NAME"]; row["TABLE_NAME"] = index["TABLE"]; row["UNIQUE"] = (long)index["NON_UNIQUE"] == 0; row["PRIMARY"] = index["KEY_NAME"].Equals("PRIMARY"); row["TYPE"] = index["INDEX_TYPE"]; row["COMMENT"] = index["COMMENT"]; } } return(dt); }
/// <summary> /// Initializes a new row for the procedure parameters table. /// </summary> private static void InitParameterRow(MyCatSchemaRow procedure, MyCatSchemaRow parameter) { parameter["SPECIFIC_CATALOG"] = null; parameter["SPECIFIC_SCHEMA"] = procedure["ROUTINE_SCHEMA"]; parameter["SPECIFIC_NAME"] = procedure["ROUTINE_NAME"]; parameter["PARAMETER_MODE"] = "IN"; parameter["ORDINAL_POSITION"] = 0; parameter["ROUTINE_TYPE"] = procedure["ROUTINE_TYPE"]; }
protected static void FillTable(MyCatSchemaCollection dt, object[][] data) { foreach (object[] dataItem in data) { MyCatSchemaRow row = dt.AddRow(); for (int i = 0; i < dataItem.Length; i++) { row[i] = dataItem[i]; } } }
internal void CopyRow(MyCatSchemaRow row) { if (Collection.Columns.Count != row.Collection.Columns.Count) { throw new InvalidOperationException("column count doesn't match"); } for (int i = 0; i < Collection.Columns.Count; i++) { row[i] = this[i]; } }
private static void ProcessColumns(MyCatSchemaCollection fkTable, MyCatSchemaRow row, List <string> srcColumns, List <string> targetColumns) { for (int i = 0; i < srcColumns.Count; i++) { MyCatSchemaRow newRow = fkTable.AddRow(); row.CopyRow(newRow); newRow["COLUMN_NAME"] = srcColumns[i]; newRow["ORDINAL_POSITION"] = i; newRow["REFERENCED_COLUMN_NAME"] = targetColumns[i]; fkTable.Rows.Add(newRow); } }
private MyCatSchemaCollection GetDataSourceInformation() { #if NETSTANDARD1_3 throw new NotSupportedException(); #else MyCatSchemaCollection dt = new MyCatSchemaCollection("DataSourceInformation"); dt.AddColumn("CompositeIdentifierSeparatorPattern", typeof(string)); dt.AddColumn("DataSourceProductName", typeof(string)); dt.AddColumn("DataSourceProductVersion", typeof(string)); dt.AddColumn("DataSourceProductVersionNormalized", typeof(string)); dt.AddColumn("GroupByBehavior", typeof(GroupByBehavior)); dt.AddColumn("IdentifierPattern", typeof(string)); dt.AddColumn("IdentifierCase", typeof(IdentifierCase)); dt.AddColumn("OrderByColumnsInSelect", typeof(bool)); dt.AddColumn("ParameterMarkerFormat", typeof(string)); dt.AddColumn("ParameterMarkerPattern", typeof(string)); dt.AddColumn("ParameterNameMaxLength", typeof(int)); dt.AddColumn("ParameterNamePattern", typeof(string)); dt.AddColumn("QuotedIdentifierPattern", typeof(string)); dt.AddColumn("QuotedIdentifierCase", typeof(IdentifierCase)); dt.AddColumn("StatementSeparatorPattern", typeof(string)); dt.AddColumn("StringLiteralPattern", typeof(string)); dt.AddColumn("SupportedJoinOperators", typeof(SupportedJoinOperators)); DBVersion v = connection.driver.Version; string ver = String.Format("{0:0}.{1:0}.{2:0}", v.Major, v.Minor, v.Build); MyCatSchemaRow row = dt.AddRow(); row["CompositeIdentifierSeparatorPattern"] = "\\."; row["DataSourceProductName"] = "MySQL"; row["DataSourceProductVersion"] = connection.ServerVersion; row["DataSourceProductVersionNormalized"] = ver; row["GroupByBehavior"] = GroupByBehavior.Unrelated; row["IdentifierPattern"] = @"(^\`\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\`[^\`\0]|\`\`+\`$)|(^\"" + [^\""\0]|\""\""+\""$)"; row["IdentifierCase"] = IdentifierCase.Insensitive; row["OrderByColumnsInSelect"] = false; row["ParameterMarkerFormat"] = "{0}"; row["ParameterMarkerPattern"] = "(@[A-Za-z0-9_$#]*)"; row["ParameterNameMaxLength"] = 128; row["ParameterNamePattern"] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)"; row["QuotedIdentifierPattern"] = @"(([^\`]|\`\`)*)"; row["QuotedIdentifierCase"] = IdentifierCase.Sensitive; row["StatementSeparatorPattern"] = ";"; row["StringLiteralPattern"] = "'(([^']|'')*)'"; row["SupportedJoinOperators"] = 15; dt.Rows.Add(row); return(dt); #endif }
private void FindTables(MyCatSchemaCollection schema, string[] restrictions) { StringBuilder sql = new StringBuilder(); StringBuilder where = new StringBuilder(); sql.AppendFormat(CultureInfo.InvariantCulture, "SHOW TABLE STATUS FROM `{0}`", restrictions[1]); if (restrictions != null && restrictions.Length >= 3 && restrictions[2] != null) { where.AppendFormat(CultureInfo.InvariantCulture, " LIKE '{0}'", restrictions[2]); } sql.Append(where.ToString()); string table_type = restrictions[1].ToLower() == "information_schema" ? "SYSTEM VIEW" : "BASE TABLE"; MyCatCommand cmd = new MyCatCommand(sql.ToString(), connection); using (MyCatDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MyCatSchemaRow row = schema.AddRow(); row["TABLE_CATALOG"] = null; row["TABLE_SCHEMA"] = restrictions[1]; row["TABLE_NAME"] = reader.GetString(0); row["TABLE_TYPE"] = table_type; row["ENGINE"] = GetString(reader, 1); row["VERSION"] = reader.GetValue(2); row["ROW_FORMAT"] = GetString(reader, 3); row["TABLE_ROWS"] = reader.GetValue(4); row["AVG_ROW_LENGTH"] = reader.GetValue(5); row["DATA_LENGTH"] = reader.GetValue(6); row["MAX_DATA_LENGTH"] = reader.GetValue(7); row["INDEX_LENGTH"] = reader.GetValue(8); row["DATA_FREE"] = reader.GetValue(9); row["AUTO_INCREMENT"] = reader.GetValue(10); row["CREATE_TIME"] = reader.GetValue(11); row["UPDATE_TIME"] = reader.GetValue(12); row["CHECK_TIME"] = reader.GetValue(13); row["TABLE_COLLATION"] = GetString(reader, 14); row["CHECKSUM"] = reader.GetValue(15); row["CREATE_OPTIONS"] = GetString(reader, 16); row["TABLE_COMMENT"] = GetString(reader, 17); } } }
private string GetProcedureParameterLine(MyCatSchemaRow isRow) { string sql = "SHOW CREATE {0} `{1}`.`{2}`"; sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"], isRow["ROUTINE_NAME"]); MyCatCommand cmd = new MyCatCommand(sql, connection); using (MyCatDataReader reader = cmd.ExecuteReader()) { reader.Read(); // if we are not the owner of this proc or have permissions // then we will get null for the body if (reader.IsDBNull(2)) { return(null); } string sql_mode = reader.GetString(1); string body = reader.GetString(2); MyCatTokenizer tokenizer = new MyCatTokenizer(body); tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; string token = tokenizer.NextToken(); while (token != "(") { token = tokenizer.NextToken(); } int start = tokenizer.StartIndex + 1; token = tokenizer.NextToken(); while (token != ")" || tokenizer.Quoted) { token = tokenizer.NextToken(); // if we see another ( and we are not quoted then we // are in a size element and we need to look for the closing paren if (token == "(" && !tokenizer.Quoted) { while (token != ")" || tokenizer.Quoted) { token = tokenizer.NextToken(); } token = tokenizer.NextToken(); } } return(body.Substring(start, tokenizer.StartIndex - start)); } }
private static List <string> GetPossibleValues(MyCatSchemaRow row) { string[] types = new string[] { "ENUM", "SET" }; string dtdIdentifier = row["DTD_IDENTIFIER"].ToString().Trim(); int index = 0; for (; index < 2; index++) { if (dtdIdentifier.StartsWith(types[index], StringComparison.OrdinalIgnoreCase)) { break; } } if (index == 2) { return(null); } dtdIdentifier = dtdIdentifier.Substring(types[index].Length).Trim(); dtdIdentifier = dtdIdentifier.Trim('(', ')').Trim(); List <string> values = new List <string>(); MyCatTokenizer tokenzier = new MyCatTokenizer(dtdIdentifier); string token = tokenzier.NextToken(); int start = tokenzier.StartIndex; while (true) { if (token == null || token == ",") { int end = dtdIdentifier.Length - 1; if (token == ",") { end = tokenzier.StartIndex; } string value = dtdIdentifier.Substring(start, end - start).Trim('\'', '\"').Trim(); values.Add(value); start = tokenzier.StopIndex; } if (token == null) { break; } token = tokenzier.NextToken(); } return(values); }
public virtual MyCatSchemaCollection GetUDF(string[] restrictions) { string sql = "SELECT name,ret,dl FROM mysql.func"; if (restrictions != null) { if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0])) { sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]); } } MyCatSchemaCollection dt = new MyCatSchemaCollection("User-defined Functions"); dt.AddColumn("NAME", typeof(string)); dt.AddColumn("RETURN_TYPE", typeof(int)); dt.AddColumn("LIBRARY_NAME", typeof(string)); MyCatCommand cmd = new MyCatCommand(sql, connection); try { using (MyCatDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MyCatSchemaRow row = dt.AddRow(); row[0] = reader.GetString(0); row[1] = reader.GetInt32(1); row[2] = reader.GetString(2); } } } catch (MyCatException ex) { if (ex.Number != (int)MyCatErrorCode.TableAccessDenied) { throw; } throw new MyCatException(Resources.UnableToEnumerateUDF, ex); } return(dt); }
private static string GetDataTypeDefaults(string type, MyCatSchemaRow row) { string format = "({0},{1})"; object precision = row["NUMERIC_PRECISION"]; if (MetaData.IsNumericType(type) && string.IsNullOrEmpty(( string )row["NUMERIC_PRECISION"])) { row["NUMERIC_PRECISION"] = 10; row["NUMERIC_SCALE"] = 0; if (!MetaData.SupportScale(type)) { format = "({0})"; } return(String.Format(format, row["NUMERIC_PRECISION"], row["NUMERIC_SCALE"])); } return(String.Empty); }
private static void ParseDataTypeSize(MyCatSchemaRow row, string size) { size = size.Trim('(', ')'); string[] parts = size.Split(','); if (!MetaData.IsNumericType(row["DATA_TYPE"].ToString())) { row["CHARACTER_MAXIMUM_LENGTH"] = Int32.Parse(parts[0]); // will set octet length in a minute } else { row["NUMERIC_PRECISION"] = Int32.Parse(parts[0]); if (parts.Length == 2) { row["NUMERIC_SCALE"] = Int32.Parse(parts[1]); } } }
/// <summary> /// GetForeignKeysOnTable retrieves the foreign keys on the given table. /// Since MySQL supports foreign keys on versions prior to 5.0, we can't use /// information schema. MySQL also does not include any type of SHOW command /// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing /// the output. /// </summary> /// <param name="fkTable">The table to store the key info in.</param> /// <param name="tableToParse">The table to get the foeign key info for.</param> /// <param name="filterName">Only get foreign keys that match this name.</param> /// <param name="includeColumns">Should column information be included in the table.</param> private void GetForeignKeysOnTable(MyCatSchemaCollection fkTable, MyCatSchemaRow tableToParse, string filterName, bool includeColumns) { string sqlMode = GetSqlMode(); if (filterName != null) { filterName = StringUtility.ToLowerInvariant(filterName); } string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]); string lowerBody = null, body = null; MyCatCommand cmd = new MyCatCommand(sql, connection); using (MyCatDataReader reader = cmd.ExecuteReader()) { reader.Read(); body = reader.GetString(1); lowerBody = StringUtility.ToLowerInvariant(body); } MyCatTokenizer tokenizer = new MyCatTokenizer(lowerBody); tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; while (true) { string token = tokenizer.NextToken(); // look for a starting contraint while (token != null && (token != "constraint" || tokenizer.Quoted)) { token = tokenizer.NextToken(); } if (token == null) { break; } ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns); } }
private static ParameterDirection GetDirection(MyCatSchemaRow row) { string mode = row["PARAMETER_MODE"].ToString(); int ordinal = Convert.ToInt32(row["ORDINAL_POSITION"]); if (0 == ordinal) { return(ParameterDirection.ReturnValue); } else if (mode == "IN") { return(ParameterDirection.Input); } else if (mode == "OUT") { return(ParameterDirection.Output); } return(ParameterDirection.InputOutput); }
private void LoadTableColumns(MyCatSchemaCollection schemaCollection, string schema, string tableName, string columnRestriction) { string sql = String.Format("SHOW FULL COLUMNS FROM `{0}`.`{1}`", schema, tableName); MyCatCommand cmd = new MyCatCommand(sql, connection); int pos = 1; using (MyCatDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string colName = reader.GetString(0); if (columnRestriction != null && colName != columnRestriction) { continue; } MyCatSchemaRow row = schemaCollection.AddRow(); row["TABLE_CATALOG"] = DBNull.Value; row["TABLE_SCHEMA"] = schema; row["TABLE_NAME"] = tableName; row["COLUMN_NAME"] = colName; row["ORDINAL_POSITION"] = pos++; row["COLUMN_DEFAULT"] = reader.GetValue(5); row["IS_NULLABLE"] = reader.GetString(3); row["DATA_TYPE"] = reader.GetString(1); row["CHARACTER_MAXIMUM_LENGTH"] = DBNull.Value; row["CHARACTER_OCTET_LENGTH"] = DBNull.Value; row["NUMERIC_PRECISION"] = DBNull.Value; row["NUMERIC_SCALE"] = DBNull.Value; row["CHARACTER_SET_NAME"] = reader.GetValue(2); row["COLLATION_NAME"] = row["CHARACTER_SET_NAME"]; row["COLUMN_TYPE"] = reader.GetString(1); row["COLUMN_KEY"] = reader.GetString(4); row["EXTRA"] = reader.GetString(6); row["PRIVILEGES"] = reader.GetString(7); row["COLUMN_COMMENT"] = reader.GetString(8); ParseColumnRow(row); } } }
public virtual MyCatSchemaCollection GetDatabases(string[] restrictions) { Regex regex = null; int caseSetting = Int32.Parse(connection.driver.Property("lower_case_table_names")); string sql = "SHOW DATABASES"; // if lower_case_table_names is zero, then case lookup should be sensitive // so we can use LIKE to do the matching. if (caseSetting == 0) { if (restrictions != null && restrictions.Length >= 1) { sql = sql + " LIKE '" + restrictions[0] + "'"; } } MyCatSchemaCollection c = QueryCollection("Databases", sql); if (caseSetting != 0 && restrictions != null && restrictions.Length >= 1 && restrictions[0] != null) { regex = new Regex(restrictions[0], RegexOptions.IgnoreCase); } MyCatSchemaCollection c2 = new MyCatSchemaCollection("Databases"); c2.AddColumn("CATALOG_NAME", typeof(string)); c2.AddColumn("SCHEMA_NAME", typeof(string)); foreach (MyCatSchemaRow row in c.Rows) { if (regex != null && !regex.Match(row[0].ToString()).Success) { continue; } MyCatSchemaRow newRow = c2.AddRow(); newRow[1] = row[0]; } return(c2); }
private static MyCatSchemaCollection GetReservedWords() { MyCatSchemaCollection dt = new MyCatSchemaCollection("ReservedWords"); #if !NETSTANDARD1_3 dt.AddColumn(DbMetaDataColumnNames.ReservedWord, typeof(string)); Stream str = Assembly.GetExecutingAssembly().GetManifestResourceStream( "Pomelo.Data.MyCat.Properties.ReservedWords.txt"); #else dt.AddColumn("ReservedWord", typeof(string)); Stream str = typeof(SchemaProvider).GetTypeInfo().Assembly.GetManifestResourceStream("Pomelo.Data.MyCat.Properties.ReservedWords.txt"); #endif StreamReader sr = new StreamReader(str); string line = sr.ReadLine(); while (line != null) { string[] keywords = line.Split(new char[] { ' ' }); foreach (string s in keywords) { if (String.IsNullOrEmpty(s)) { continue; } MyCatSchemaRow row = dt.AddRow(); row[0] = s; } line = sr.ReadLine(); } #if NETSTANDARD1_3 sr.Dispose(); str.Dispose(); #else sr.Close(); str.Close(); #endif return(dt); }
private static void ParseColumnRow(MyCatSchemaRow row) { // first parse the character set name string charset = row["CHARACTER_SET_NAME"].ToString(); int index = charset.IndexOf('_'); if (index != -1) { row["CHARACTER_SET_NAME"] = charset.Substring(0, index); } // now parse the data type string dataType = row["DATA_TYPE"].ToString(); index = dataType.IndexOf('('); if (index == -1) { return; } row["DATA_TYPE"] = dataType.Substring(0, index); int stop = dataType.IndexOf(')', index); string dataLen = dataType.Substring(index + 1, stop - (index + 1)); string lowerType = row["DATA_TYPE"].ToString().ToLower(); if (lowerType == "char" || lowerType == "varchar") { row["CHARACTER_MAXIMUM_LENGTH"] = dataLen; } else if (lowerType == "real" || lowerType == "decimal") { string[] lenparts = dataLen.Split(new char[] { ',' }); row["NUMERIC_PRECISION"] = lenparts[0]; if (lenparts.Length == 2) { row["NUMERIC_SCALE"] = lenparts[1]; } } }
public virtual MyCatSchemaCollection GetIndexColumns(string[] restrictions) { MyCatSchemaCollection dt = new MyCatSchemaCollection("IndexColumns"); dt.AddColumn("INDEX_CATALOG", typeof(string)); dt.AddColumn("INDEX_SCHEMA", typeof(string)); dt.AddColumn("INDEX_NAME", typeof(string)); dt.AddColumn("TABLE_NAME", typeof(string)); dt.AddColumn("COLUMN_NAME", typeof(string)); dt.AddColumn("ORDINAL_POSITION", typeof(int)); dt.AddColumn("SORT_ORDER", typeof(string)); int max = restrictions == null ? 4 : restrictions.Length; string[] tableRestrictions = new string[Math.Max(max, 4)]; if (restrictions != null) { restrictions.CopyTo(tableRestrictions, 0); } tableRestrictions[3] = "BASE TABLE"; MyCatSchemaCollection tables = GetTables(tableRestrictions); foreach (MyCatSchemaRow table in tables.Rows) { string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`", table["TABLE_SCHEMA"], table["TABLE_NAME"]); MyCatCommand cmd = new MyCatCommand(sql, connection); using (MyCatDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME")); string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME")); if (restrictions != null) { if (restrictions.Length >= 4 && restrictions[3] != null && key_name != restrictions[3]) { continue; } if (restrictions.Length >= 5 && restrictions[4] != null && col_name != restrictions[4]) { continue; } } MyCatSchemaRow row = dt.AddRow(); row["INDEX_CATALOG"] = null; row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"]; row["INDEX_NAME"] = key_name; row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE")); row["COLUMN_NAME"] = col_name; row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX")); row["SORT_ORDER"] = reader.GetString("COLLATION"); } } } return(dt); }
private void ParseProcedureBody(MyCatSchemaCollection parametersTable, string body, MyCatSchemaRow row, string nameToRestrict) { List <string> modes = new List <string>(new string[3] { "IN", "OUT", "INOUT" }); string sqlMode = row["SQL_MODE"].ToString(); int pos = 1; MyCatTokenizer tokenizer = new MyCatTokenizer(body); tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; tokenizer.ReturnComments = false; string token = tokenizer.NextToken(); // this block will scan for the opening paren while also determining // if this routine is a function. If so, then we need to add a // parameter row for the return parameter since it is ordinal position // 0 and should appear first. while (token != "(") { if (String.Compare(token, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0 && nameToRestrict == null) { parametersTable.AddRow(); InitParameterRow(row, parametersTable.Rows[0]); } token = tokenizer.NextToken(); } token = tokenizer.NextToken(); // now move to the next token past the ( while (token != ")") { MyCatSchemaRow parmRow = parametersTable.NewRow(); InitParameterRow(row, parmRow); parmRow["ORDINAL_POSITION"] = pos++; // handle mode and name for the parameter string mode = StringUtility.ToUpperInvariant(token); if (!tokenizer.Quoted && modes.Contains(mode)) { parmRow["PARAMETER_MODE"] = mode; token = tokenizer.NextToken(); } if (tokenizer.Quoted) { token = token.Substring(1, token.Length - 2); } parmRow["PARAMETER_NAME"] = token; // now parse data type token = ParseDataType(parmRow, tokenizer); if (token == ",") { token = tokenizer.NextToken(); } // now determine if we should include this row after all // we need to parse it before this check so we are correctly // positioned for the next parameter if (nameToRestrict == null || String.Compare(parmRow["PARAMETER_NAME"].ToString(), nameToRestrict, StringComparison.OrdinalIgnoreCase) == 0) { parametersTable.Rows.Add(parmRow); } } // now parse out the return parameter if there is one. token = StringUtility.ToUpperInvariant(tokenizer.NextToken()); if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0) { MyCatSchemaRow parameterRow = parametersTable.Rows[0]; parameterRow["PARAMETER_NAME"] = "RETURN_VALUE"; ParseDataType(parameterRow, tokenizer); } }
internal MyCatSchemaRow NewRow() { MyCatSchemaRow r = new MyCatSchemaRow(this); return(r); }
/// <summary> /// Parses out the elements of a procedure parameter data type. /// </summary> private string ParseDataType(MyCatSchemaRow row, MyCatTokenizer tokenizer) { StringBuilder dtd = new StringBuilder( StringUtility.ToUpperInvariant(tokenizer.NextToken())); row["DATA_TYPE"] = dtd.ToString(); string type = row["DATA_TYPE"].ToString(); string token = tokenizer.NextToken(); if (token == "(") { token = tokenizer.ReadParenthesis(); dtd.AppendFormat(CultureInfo.InvariantCulture, "{0}", token); if (type != "ENUM" && type != "SET") { ParseDataTypeSize(row, token); } token = tokenizer.NextToken(); } else { dtd.Append(GetDataTypeDefaults(type, row)); } while (token != ")" && token != "," && String.Compare(token, "begin", StringComparison.OrdinalIgnoreCase) != 0 && String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0) { if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0) { } // we don't need to do anything with this else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = tokenizer.NextToken(); } else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "latin1"; } else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "ucs2"; } else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 0) { row["COLLATION_NAME"] = tokenizer.NextToken(); } else { dtd.AppendFormat(CultureInfo.InvariantCulture, " {0}", token); } token = tokenizer.NextToken(); } if (dtd.Length > 0) { row["DTD_IDENTIFIER"] = dtd.ToString(); } // now default the collation if one wasn't given if (string.IsNullOrEmpty(( string )row["COLLATION_NAME"]) && !string.IsNullOrEmpty(( string )row["CHARACTER_SET_NAME"])) { row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation( row["CHARACTER_SET_NAME"].ToString(), connection); } // now set the octet length if (row["CHARACTER_MAXIMUM_LENGTH"] != null) { if (row["CHARACTER_SET_NAME"] == null) { row["CHARACTER_SET_NAME"] = ""; } row["CHARACTER_OCTET_LENGTH"] = CharSetMap.GetMaxLength(( string )row["CHARACTER_SET_NAME"], connection) * (int)row["CHARACTER_MAXIMUM_LENGTH"]; } return(token); }
public virtual MyCatSchemaCollection GetProcedures(string[] restrictions) { MyCatSchemaCollection dt = new MyCatSchemaCollection("Procedures"); dt.AddColumn("SPECIFIC_NAME", typeof(string)); dt.AddColumn("ROUTINE_CATALOG", typeof(string)); dt.AddColumn("ROUTINE_SCHEMA", typeof(string)); dt.AddColumn("ROUTINE_NAME", typeof(string)); dt.AddColumn("ROUTINE_TYPE", typeof(string)); dt.AddColumn("DTD_IDENTIFIER", typeof(string)); dt.AddColumn("ROUTINE_BODY", typeof(string)); dt.AddColumn("ROUTINE_DEFINITION", typeof(string)); dt.AddColumn("EXTERNAL_NAME", typeof(string)); dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string)); dt.AddColumn("PARAMETER_STYLE", typeof(string)); dt.AddColumn("IS_DETERMINISTIC", typeof(string)); dt.AddColumn("SQL_DATA_ACCESS", typeof(string)); dt.AddColumn("SQL_PATH", typeof(string)); dt.AddColumn("SECURITY_TYPE", typeof(string)); dt.AddColumn("CREATED", typeof(DateTime)); dt.AddColumn("LAST_ALTERED", typeof(DateTime)); dt.AddColumn("SQL_MODE", typeof(string)); dt.AddColumn("ROUTINE_COMMENT", typeof(string)); dt.AddColumn("DEFINER", typeof(string)); StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1"); if (restrictions != null) { if (restrictions.Length >= 2 && restrictions[1] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND db LIKE '{0}'", restrictions[1]); } if (restrictions.Length >= 3 && restrictions[2] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND name LIKE '{0}'", restrictions[2]); } if (restrictions.Length >= 4 && restrictions[3] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND type LIKE '{0}'", restrictions[3]); } } MyCatCommand cmd = new MyCatCommand(sql.ToString(), connection); using (MyCatDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MyCatSchemaRow row = dt.AddRow(); row["SPECIFIC_NAME"] = reader.GetString("specific_name"); row["ROUTINE_CATALOG"] = DBNull.Value; row["ROUTINE_SCHEMA"] = reader.GetString("db"); row["ROUTINE_NAME"] = reader.GetString("name"); string routineType = reader.GetString("type"); row["ROUTINE_TYPE"] = routineType; row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ? (object)reader.GetString("returns") : DBNull.Value; row["ROUTINE_BODY"] = "SQL"; row["ROUTINE_DEFINITION"] = reader.GetString("body"); row["EXTERNAL_NAME"] = DBNull.Value; row["EXTERNAL_LANGUAGE"] = DBNull.Value; row["PARAMETER_STYLE"] = "SQL"; row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic"); row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access"); row["SQL_PATH"] = DBNull.Value; row["SECURITY_TYPE"] = reader.GetString("security_type"); row["CREATED"] = reader.GetDateTime("created"); row["LAST_ALTERED"] = reader.GetDateTime("modified"); row["SQL_MODE"] = reader.GetString("sql_mode"); row["ROUTINE_COMMENT"] = reader.GetString("comment"); row["DEFINER"] = reader.GetString("definer"); } } return(dt); }