diff options
Diffstat (limited to 'src/FsMySql.cs')
| -rw-r--r-- | src/FsMySql.cs | 383 |
1 files changed, 383 insertions, 0 deletions
diff --git a/src/FsMySql.cs b/src/FsMySql.cs new file mode 100644 index 0000000..ce87748 --- /dev/null +++ b/src/FsMySql.cs @@ -0,0 +1,383 @@ +/* + * FsGateway - navigate a database structure as directory tree + * Copyright (C) 2009-2010 Torello Querci <torello@torosoft.com> + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License along + * with this program; if not, write to the Free Software Foundation, Inc., + * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + */ + +using System; +using System.Data; +using System.Collections.Generic; +using MySql; +using Mono.Fuse; +using System.Text.RegularExpressions; + +namespace FsGateway +{ + + public class FsMySql : IFsDb + { + private IDbConnection dbcon=null; + private string connectionString=null; + private bool _isConnected=false; + private int version_number_release=0; // e.s. 8.x.x + private int version_number_major=0; // e.s. x.3.0 + private int version_number_minor=0; // e.s. x.x.0 + + public FsMySql() + { + } + + public FsMySql(string host, string database, string user, string password, string port) + { + + string connectionString = "Server="+host+";" + + "Database="+database+";" + + "User ID="+user+";" + + "Password="+password+";"+ + "Port="+port+";"; + Connect(connectionString); + } + + public List<string> getTypeOfObjects() { + List<string> names=new List<string>(); + + names.Add ("/tables"); + names.Add ("/views"); + names.Add ("/indexes"); + + return names; + } + + + public void Dispose() { + Unconnect(); + _isConnected=false; + } + + public string storageType { + get { + return "MySQL5"; + } + } + + public string Usage { + get { + return "Specify the connection parameter like this one: \"Server=localhost; Database=mydb; User ID=username;Password=password;Port=3306;"; + } + } + + public bool isConnect { + get { + return _isConnected; + } + } + + public bool Connect() { + return Connect(this.connectionString); + } + + public bool Connect(string connectionString) { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + Unconnect(); + } + + try { + dbcon = new MySql.Data.MySqlClient.MySqlConnection(connectionString); + dbcon.Open(); + this.connectionString=connectionString; + res=true; + _isConnected=true; + +// readVersionNumber(); + + } catch (Exception ex) { + System.Console.Out.WriteLine("Exception during database connection opening. Error message: "+ex.Message); + dbcon=null; + } + return res; + } + +/* + private string readVersionNumber() { + string sql="select version()"; + string version=""; + + IDbCommand dbcmd = dbcon.CreateCommand(); + + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + if (!reader.Read()) { + reader.Close(); + return null; + } + + // Get the version number + version=reader.GetString(0); + + // Parse the version number string to get the single part + Regex fileMultipleRegex = null; + + try { + fileMultipleRegex = new Regex(@"(?<postgres>[^ ]+) ((?<numberVersion>[0-9]+)\.(?<numberMajor>[0-9]+)\.(?<numberMinor>[0-9]+))", RegexOptions.Compiled | RegexOptions.IgnoreCase); + } catch (Exception ex) { + System.Console.WriteLine("Exception during the Regex creation: "+ex.Message); + } + + // Check for namefile with number + int index=0; + if (fileMultipleRegex!=null) { + + Match match=null; + try { + match = fileMultipleRegex.Match(version); + } catch (Exception ex) { + System.Console.WriteLine("Exception during match routine. Message: "+ex.Message); + } + + try { + if (match != null && match.Success) { + version_number_release =System.Int32.Parse(match.Groups["numberVersion"].Value); + version_number_major =System.Int32.Parse(match.Groups["numberMajor"].Value); + version_number_minor =System.Int32.Parse(match.Groups["numberMinor"].Value); + } + } catch (Exception ex) { + Console.WriteLine("Exception: "+ex.Message); + } + } + + reader.Close(); + return version; + } +*/ + public void Unconnect() { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + dbcon.Close(); + dbcon=null; + res=false; + } + + _isConnected=true; + return; + } + + public SortedList<string,Table> getTables() { + + SortedList<string,Table> tableList=null; + string sql; + IDataReader reader=null; + + // Check for DB Connection + if (dbcon!=null) { + + tableList=new SortedList<string,Table>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + sql = "SHOW TABLES" + ; + + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + Table table=new Table(null,reader.GetString(0)); + tableList.Add(table.ToString(), table); + } + } catch (Exception ex) { + Console.WriteLine("Exception reading of the tables list : "+ex.Message); + Console.WriteLine("Postgresql Version: "+version_number_release+"."+version_number_major+"."+version_number_minor); + Console.WriteLine("List tables: SQL=" + sql); + } + reader.Close(); + + // ReRead data for fetching detail + foreach (Table table in tableList.Values) { + + sql = "DESCRIBE "+table.Name; + string listFilesStr=""; + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + listFilesStr=""; + while (reader.Read()) { + Field field=new Field(reader.GetString(reader.GetOrdinal("Field")),reader.GetString(reader.GetOrdinal("Type"))); + table.Fields.Add(field.Name,field); + } + + reader.Close(); + + table.Script = "CREATE TABLE "+table.ToString()+"\n" + + "(\t"; + string separator=""; + foreach (Field field in table.Fields.Values) { + table.Script += separator+field.Name+"\t"+field.Type; + separator=",\n\t"; + } + table.Script += "\n);\n"; + + } catch (Exception ex) { + Console.WriteLine("Exception reading the tables fields detail for table: " + table.ToString()+ " message : "+ex.Message); + Console.WriteLine("SQL used: "+sql); + Console.WriteLine("FIELDS: "+listFilesStr); + } + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + + } + + return tableList; + } + + public SortedList<string,View> getViews() { + + System.Collections.Generic.SortedList<string,View> viewList=null; + View view; + + // Check for DB Connection + if (dbcon!=null) { + + viewList=new SortedList<string,View>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + string sql = "select table_name, view_definition from information_schema.views"; + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + view=new View(null, + reader.GetString(reader.GetOrdinal("table_name")), + reader.GetString(reader.GetOrdinal("view_definition"))+"\n"); + viewList.Add(view.ToString(),view); + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + + } + + return viewList; + } + + public SortedList<string,Index> getIndexes() { + + System.Collections.Generic.SortedList<string,Index> indexList=null; + Index index; + System.Collections.Generic.List<string> listTables=new System.Collections.Generic.List<string>(); + + // Check for DB Connection + if (dbcon!=null) { + + indexList=new SortedList<string,Index>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + string sql = "SHOW TABLES"; + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + listTables.Add(reader.GetString(0)); + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + + foreach (string tableName in listTables) { + string sqlShowIndex="show index in "+tableName; + dbcmd = dbcon.CreateCommand(); + dbcmd.CommandText=sqlShowIndex; + reader = dbcmd.ExecuteReader(); + while (reader.Read()) { + index=new Index(reader.GetString(reader.GetOrdinal("Table")), + reader.GetString(reader.GetOrdinal("Key_name")), + reader.GetString(reader.GetOrdinal("Key_name")), + "CREATE INDEX "+reader.GetString(reader.GetOrdinal("Key_name")) + +" ON "+reader.GetString(reader.GetOrdinal("Table")) + +" ("+reader.GetString(reader.GetOrdinal("Column_name"))+")" + +";\n"); + if (!indexList.ContainsKey(index.ToString())) { + indexList.Add(index.ToString(),index); + } else { + Index oldIndex=indexList[index.ToString()]; + oldIndex.Script = oldIndex.Script.Substring(0,oldIndex.Script.Length-3)+","+reader.GetString(reader.GetOrdinal("Column_name"))+");\n"; + } + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + } + + } + + return indexList; + } + + public SortedList<string,Sequence> getSequences() { + + SortedList<string,Sequence> sequencesList=null; + sequencesList=new SortedList<string,Sequence>(); + + return sequencesList; + } + + /* + * Just for class testing only + */ + public static void Main(string[] args) + { + FsMySql pg=new FsMySql("localhost", "dghome", "dghome", "dghome", "3306"); + + System.Console.Out.WriteLine("Tables:"); + SortedList<string,Table> tables=pg.getTables(); + foreach (Table table in tables.Values) { + System.Console.Out.WriteLine(table.ToString()); + } + + System.Console.Out.WriteLine("Views:"); + SortedList<string,View> views=pg.getViews(); + foreach (View view in views.Values) { + System.Console.Out.WriteLine(view.ToString()); + } + + System.Console.Out.WriteLine("Indexes:"); + SortedList<string,Index> indexes=pg.getIndexes(); + foreach (Index index in indexes.Values) { + System.Console.Out.WriteLine(index.ToString()); + } + + System.Console.Out.WriteLine("Sequences:"); + SortedList<string,Sequence> sequences=pg.getSequences(); + foreach (Sequence sequence in sequences.Values) { + System.Console.Out.WriteLine(sequence.ToString()); + } + + } + } +} |