public string DropTable(Structure.Table table)
{
throw new NotImplementedException();
}
public void FetchSchemas()
{
List <Schema> schemas = new List <Schema>();
using (DbConnection conn = GetConnection(ConnectionString))
{
conn.Open();
// Retrieve all schemas
SchemaQuery schemaQuery = SchemaQuery;
using (DbCommand cmd = GetCommand(schemaQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
schemas.Add(new Schema()
{
Name = reader.GetString(schemaQuery.IndexOfName), Owner = reader.GetString(schemaQuery.IndexOfOwner)
});
}
}
// Retrieve all tables
TableQuery tableQuery = TableQuery;
using (DbCommand cmd = GetCommand(TableQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema schema = schemas.FirstOrDefault(s => s.Name == reader.GetString(TableQuery.IndexOfOwner));
schema?.Tables.Add(new Structure.Table()
{
Name = reader.GetString(TableQuery.IndexOfName)
});
}
}
// Retrieve all columns
ColumnQuery columnQuery = ColumnQuery;
using (DbCommand cmd = GetCommand(ColumnQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema schema = schemas.FirstOrDefault(s => s.Name == reader.GetString(ColumnQuery.IndexOfTableSchemaName));
Structure.Table table = schema?.Tables.FirstOrDefault(t => t.Name == reader.GetString(ColumnQuery.IndexOfTableName));
table?.Columns.Add(new Column()
{
Name = reader.GetString(ColumnQuery.IndexOfName), Type = reader.GetString(ColumnQuery.IndexOfType)
});
}
}
// Retrieve all primary keys
PrimaryKeyQuery primaryKeyQuery = PrimaryKeyQuery;
using (DbCommand cmd = GetCommand(primaryKeyQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema tableSchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(primaryKeyQuery.IndexOfTableSchemaName));
Schema primaryKeychema = schemas.FirstOrDefault(s => s.Name == reader.GetString(primaryKeyQuery.IndexOfPrimaryKeySchemaName));
Structure.Table table = tableSchema?.Tables.FirstOrDefault(t => t.Name == reader.GetString(primaryKeyQuery.IndexOfTableName));
PrimaryKey primaryKey = new PrimaryKey()
{
Name = reader.GetString(primaryKeyQuery.IndexOfName)
};
primaryKeychema?.PrimaryKeys.Add(primaryKey);
table?.PrimaryKeys.Add(primaryKey);
}
}
// Retrieve all primary keys column
PrimaryKeyColumnsQuery primaryKeyColumnsQuery = PrimaryKeyColumnsQuery;
using (DbCommand cmd = GetCommand(primaryKeyColumnsQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema primaryKeySchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(primaryKeyColumnsQuery.IndexOfPrimaryKeySchemaName));
Schema tableSchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(primaryKeyColumnsQuery.IndexOfTableSchemaName));
Structure.Table table = tableSchema?.Tables.FirstOrDefault(t => t.Name == reader.GetString(primaryKeyColumnsQuery.IndexOfTableName));
PrimaryKey primaryKey = primaryKeySchema?.PrimaryKeys.FirstOrDefault(t => t.Name == reader.GetString(primaryKeyColumnsQuery.IndexOfPrimaryKeyName));
Column column = table?.Columns.FirstOrDefault(c => c.Name == reader.GetString(primaryKeyColumnsQuery.IndexOfName));
primaryKey?.Columns.Add(column);
}
}
// Retrieve all foreign keys
ForeignKeyQuery foreignKeyQuery = ForeignKeyQuery;
using (DbCommand cmd = GetCommand(foreignKeyQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema primaryKeySchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(foreignKeyQuery.IndexOfReferencedConstraintSchemaName));
Schema foreignKeyShema = schemas.FirstOrDefault(s => s.Name == reader.GetString(foreignKeyQuery.IndexOfForeignKeySchemaName));
Schema tableSchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(foreignKeyQuery.IndexOfTableSchemaName));
PrimaryKey primaryKey = primaryKeySchema?.PrimaryKeys.FirstOrDefault(pk => pk.Name == reader.GetString(foreignKeyQuery.IndexOfReferencedConstraintName));
Structure.Table table = foreignKeyShema?.Tables.FirstOrDefault(t => t.Name == reader.GetString(foreignKeyQuery.IndexOfTableName));
ForeignKey foreignKey = new ForeignKey()
{
Name = reader.GetString(foreignKeyQuery.IndexOfName), ReferencedConstraint = primaryKey
};
table?.ForeignKeys.Add(foreignKey);
foreignKeyShema?.ForeignKeys.Add(foreignKey);
}
}
// Retrieve all foreign keys column
ForeignKeyColumnsQuery foreignKeyColumnsQuery = ForeignKeyColumnsQuery;
using (DbCommand cmd = GetCommand(foreignKeyColumnsQuery.Query, conn))
{
using DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Schema tableSchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(foreignKeyColumnsQuery.IndexOfTableSchemaName));
Schema foreignKeySchema = schemas.FirstOrDefault(s => s.Name == reader.GetString(foreignKeyColumnsQuery.IndexOfForeignKeySchemaName));
Structure.Table table = tableSchema?.Tables.FirstOrDefault(t => t.Name == reader.GetString(foreignKeyColumnsQuery.IndexOfTableName));
ForeignKey foreignKey = tableSchema?.ForeignKeys.FirstOrDefault(t => t.Name == reader.GetString(foreignKeyColumnsQuery.IndexOfForeignKeyName));
Column column = table?.Columns.FirstOrDefault(c => c.Name == reader.GetString(foreignKeyColumnsQuery.IndexOfName));
foreignKey?.Columns.Add(column);
}
}
conn.Close();
}
Schemas = schemas;
}
public string CreateDescription(Structure.Table table)
{
throw new NotImplementedException();
}