public void Execute(ISemanticDatabase sdb, string sql)
{
NpgsqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
sdb.LogSqlStatement(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public void CreateTable(ISemanticDatabase sdb, string st, List <Tuple <string, Type> > fieldTypes)
{
StringBuilder sb = new StringBuilder("create table " + st + " (");
List <string> fields = new List <string>();
fields.Add("ID INTEGER PRIMARY KEY AUTOINCREMENT");
fields.AddRange(fieldTypes.Select(ft => ft.Item1));
string fieldList = String.Join(", ", fields);
sb.Append(fieldList);
sb.Append(");");
Execute(sdb, sb.ToString());
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List<string> GetTables(ISemanticDatabase sdb)
{
NpgsqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'";
sdb.LogSqlStatement(cmd.CommandText);
NpgsqlDataReader reader = cmd.ExecuteReader();
List<string> tableNames = new List<string>();
while (reader.Read())
{
tableNames.Add(reader[0].ToString());
}
return tableNames;
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List<string> GetColumns(ISemanticDatabase sdb, string tableName)
{
NpgsqlCommand cmd = conn.CreateCommand();
// currval() only works after an INSERT (which has executed nextval() ), in the same session.
cmd.CommandText = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = " + tableName.SingleQuote();
sdb.LogSqlStatement(cmd.CommandText);
NpgsqlDataReader reader = cmd.ExecuteReader();
List<string> columnNames = new List<string>();
while (reader.Read())
{
columnNames.Add(reader[0].ToString());
}
return columnNames;
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List <string> GetTables(ISemanticDatabase sdb)
{
NpgsqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'";
sdb.LogSqlStatement(cmd.CommandText);
NpgsqlDataReader reader = cmd.ExecuteReader();
List <string> tableNames = new List <string>();
while (reader.Read())
{
tableNames.Add(reader[0].ToString());
}
return(tableNames);
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List <string> GetColumns(ISemanticDatabase sdb, string tableName)
{
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "pragma table_info('" + tableName + "')";
sdb.LogSqlStatement(cmd.CommandText);
SQLiteDataReader reader = cmd.ExecuteReader();
List <string> columnNames = new List <string>();
while (reader.Read())
{
columnNames.Add(reader[1].ToString());
}
return(columnNames);
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List <string> GetTables(ISemanticDatabase sdb)
{
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table'";
sdb.LogSqlStatement(cmd.CommandText);
SQLiteDataReader reader = cmd.ExecuteReader();
List <string> tableNames = new List <string>();
while (reader.Read())
{
tableNames.Add(reader[0].ToString());
}
return(tableNames);
}
/// <summary>
/// Return a list of all tables in the database.
/// </summary>
public List <string> GetColumns(ISemanticDatabase sdb, string tableName)
{
NpgsqlCommand cmd = conn.CreateCommand();
// currval() only works after an INSERT (which has executed nextval() ), in the same session.
cmd.CommandText = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = " + tableName.SingleQuote();
sdb.LogSqlStatement(cmd.CommandText);
NpgsqlDataReader reader = cmd.ExecuteReader();
List <string> columnNames = new List <string>();
while (reader.Read())
{
columnNames.Add(reader[0].ToString());
}
return(columnNames);
}
public void CreateTable(ISemanticDatabase sdb, string st, List <Tuple <string, Type> > fieldTypes)
{
Dictionary <Type, string> typeMap = new Dictionary <Type, string>()
{
{ typeof(int), "integer" },
{ typeof(long), "int8" },
{ typeof(bool), "boolean" },
{ typeof(string), "text" },
{ typeof(char), "char" },
{ typeof(float), "float8" },
{ typeof(double), "float8" },
{ typeof(decimal), "decimal" },
{ typeof(DateTime), "timestamptz" },
};
StringBuilder sb = new StringBuilder("create table " + st + " (");
List <string> fields = new List <string>();
fields.Add("ID SERIAL PRIMARY KEY"); // Everybody does it differently!
fieldTypes.ForEach(ft =>
{
string typeName;
if (!typeMap.TryGetValue(ft.Item2, out typeName))
{
throw new Exception("Type " + ft.Item2.ToString() + " not supported.");
}
fields.Add(ft.Item1 + " " + typeName);
});
string fieldList = String.Join(", ", fields);
sb.Append(fieldList);
sb.Append(");");
Execute(sdb, sb.ToString());
}
public void Execute(ISemanticDatabase sdb, string sql)
{
NpgsqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
sdb.LogSqlStatement(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public void DropTable(ISemanticDatabase sdb, string tableName)
{
Execute(sdb, "drop table " + Delimited(tableName));
}
public void CreateTable(ISemanticDatabase sdb, string st, List<Tuple<string, Type>> fieldTypes)
{
Dictionary<Type, string> typeMap = new Dictionary<Type, string>()
{
{typeof(int), "integer"},
{typeof(long), "int8"},
{typeof(bool), "boolean"},
{typeof(string), "text"},
{typeof(char), "char"},
{typeof(float), "float8"},
{typeof(double), "float8"},
{typeof(decimal), "decimal"},
{typeof(DateTime), "timestamptz"},
};
StringBuilder sb = new StringBuilder("create table " + st + " (");
List<string> fields = new List<string>();
fields.Add("ID SERIAL PRIMARY KEY"); // Everybody does it differently!
fieldTypes.ForEach(ft =>
{
string typeName;
if (!typeMap.TryGetValue(ft.Item2, out typeName))
{
throw new Exception("Type " + ft.Item2.ToString() + " not supported.");
}
fields.Add(ft.Item1 + " " + typeName);
});
string fieldList = String.Join(", ", fields);
sb.Append(fieldList);
sb.Append(");");
Execute(sdb, sb.ToString());
}
public void DropTable(ISemanticDatabase sdb, string tableName)
{
Execute(sdb, "drop table " + Delimited(tableName));
}