Example #1
0
        /// <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();
                }
            }
        }