/// <summary>
/// Write the different create indeces/key constraint statements.
/// </summary>
/// <param name="conn"></param>
private static void GenerateBuildIndexes(NpgsqlConnection conn)
{
var indexPath = Path.Combine(
_params[Parameters.OtherWorkPath].ToString(), Constants.CreateIndexesEtAl);
// option column values:
// INDOPTION_DESC 0x0001 = values are in reverse order (DESC)
// INDOPTION_NULLS_FIRST 0x0002 = NULLs are first instead of last
const string sql =
@"SELECT n.nspname AS schema_name, c.relname AS table_name,
d.relname AS index_name, i.indisprimary,
i.indisunique, a.attname AS column_name,
i.indoption[a.attnum - 1] as option
FROM pg_index i
LEFT JOIN pg_attribute a
ON a.attrelid = i.indrelid
AND a.attnum = ANY ( i.indkey )
LEFT JOIN pg_class c
ON c.oid = i.indrelid
LEFT JOIN pg_class d
ON d.oid = i.indexrelid
LEFT JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE n.nspname NOT IN('pg_toast', 'pg_catalog', 'information_schema', 'public')
ORDER BY n.nspname ASC, c.relname ASC, d.relname ASC, a.attnum";
using (var cmd = new NpgsqlCommand(sql, conn))
using (var sw = new StreamWriter(indexPath, false, Encoding.Default))
{
sw.WriteBeginTrans();
var savedSchema = "";
var savedTable = "";
var savedIndex = "";
var savedType = ' ';
using (var reader = cmd.ExecuteReader())
{
var sb = new StringBuilder();
while (reader.Read())
{
var schema = reader["schema_name"].ToString();
var table = reader["table_name"].ToString();
var index = reader["index_name"].ToString();
// Schema, table or index changed: close index defenition.
if (!savedSchema.Equals(schema) || !savedTable.Equals(table)
|| !savedIndex.Equals(index))
{
if (sb.Length > 0)
sw.WriteIndex(
savedSchema,
savedTable,
savedIndex,
savedType,
sb.ToString());
sb.Clear();
savedSchema = schema;
savedTable = table;
savedIndex = index;
if ((bool) reader["indisprimary"]) { savedType = 'P'; }
else if ((bool) reader["indisunique"]) { savedType = 'U'; }
else savedType = 'I';
}
else sb.Append(", ");
sb.Append("[" + reader["column_name"] + "]");
if (reader["option"] == DBNull.Value) continue;
switch ((short) reader["option"])
{
case 0:
sb.Append(" ASC");
break;
case 1:
sb.Append(" DESC");
break;
}
}
if (sb.Length > 0)
sw.WriteIndex(
savedSchema,
savedTable,
savedIndex,
savedType,
sb.ToString());
sw.WriteCommitTrans();
}
}
}