private static ResultSet GetTableResults(IDbConnection connection, TableInfo table)
{
// We could probably refactor QueryRunner to expose this functionality
// to us without having to recreate it here.
var command = connection.CreateCommand();
command.CommandText = String.Format("SELECT * FROM {0} ORDER BY Id ASC", table.Name);
var resultSet = new ResultSet();
using (var reader = command.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; ++i)
{
var column = new ResultColumnInfo
{
Name = reader.GetName(i)
};
ResultColumnType type;
if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out type))
{
column.Type = type;
}
resultSet.Columns.Add(column);
}
while (reader.Read())
{
var row = new List<object>();
for (int i = 0; i < reader.FieldCount; ++i)
{
object value = reader.GetValue(i);
if (value is DateTime)
{
value = ((DateTime)value).ToJavascriptTime();
}
row.Add(value);
}
resultSet.Rows.Add(row);
}
}
return resultSet;
}
/// <summary>
/// Get the sites schema
/// </summary>
/// <returns></returns>
public List<TableInfo> GetTableInfos()
{
List<ColumnInfo> columns;
var tables = new List<TableInfo>();
using (var cnn = GetOpenConnection())
{
string sql =
@"
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'public'
order by TABLE_NAME, ORDINAL_POSITION
";
using (var cmd = cnn.CreateCommand())
{
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
columns = new List<ColumnInfo>();
while (reader.Read())
{
var info = new ColumnInfo();
info.TableName = reader.GetString(0);
info.ColumnName = reader.GetString(1);
info.SetDataType(reader.GetString(2), reader.IsDBNull(3) ? null : (int?) reader.GetInt32(3));
columns.Add(info);
}
}
}
}
TableInfo tableInfo = null;
foreach (ColumnInfo column in columns)
{
if (tableInfo == null || tableInfo.Name != column.TableName)
{
tableInfo = new TableInfo();
tableInfo.Name = column.TableName;
tables.Add(tableInfo);
}
tableInfo.ColumnNames.Add(column.ColumnName);
tableInfo.DataTypes.Add(column.DataType);
}
tables.Sort((l, r) =>
{
if (l.Name == "Posts") return -1;
if (r.Name == "Posts") return 1;
if (l.Name == "Users") return -1;
if (r.Name == "Users") return 1;
if (l.Name == "Comments") return -1;
if (r.Name == "Comments") return 1;
if (l.Name == "Badges") return -1;
if (r.Name == "Badges") return 1;
return l.Name.CompareTo(r.Name);
});
return tables;
}
