Skip to content

Commit b36e312

Browse files
rojiroji
committed
Style fixes in schema code
1 parent 8eb495f commit b36e312

1 file changed

Lines changed: 95 additions & 97 deletions

File tree

‎src/Npgsql/NpgsqlSchema.cs‎

Lines changed: 95 additions & 97 deletions
Original file line numberDiff line numberDiff line change
@@ -187,15 +187,13 @@ static DataTable GetSchemata(NpgsqlConnection conn, string?[]? restrictions)
187187
new DataColumn("schema_owner")
188188
});
189189

190-
var getSchemata = new StringBuilder();
191-
192-
getSchemata.Append(
193-
@"select * from(
194-
select current_database() as catalog_name,
190+
var getSchemata = new StringBuilder(@"
191+
SELECT * FROM (
192+
SELECT current_database() AS catalog_name,
195193
nspname AS schema_name,
196194
r.rolname AS schema_owner
197-
from
198-
pg_catalog.pg_namespace left join pg_catalog.pg_roles r on r.oid = nspowner
195+
FROM
196+
pg_catalog.pg_namespace LEFT JOIN pg_catalog.pg_roles r ON r.oid = nspowner
199197
) tmp");
200198

201199
using (var command = BuildCommand(conn, getSchemata, restrictions, "catalog_name", "schema_name", "schema_owner"))
@@ -227,7 +225,9 @@ static DataTable GetTables(NpgsqlConnection conn, string?[]? restrictions)
227225
getTables.Append(@"
228226
SELECT table_catalog, table_schema, table_name, table_type
229227
FROM information_schema.tables
230-
WHERE table_type IN ('BASE TABLE', 'FOREIGN', 'FOREIGN TABLE') AND table_schema NOT IN ('pg_catalog', 'information_schema')");
228+
WHERE
229+
table_type IN ('BASE TABLE', 'FOREIGN', 'FOREIGN TABLE') AND
230+
table_schema NOT IN ('pg_catalog', 'information_schema')");
231231

232232
using (var command = BuildCommand(conn, getTables, restrictions, false, "table_catalog", "table_schema", "table_name", "table_type"))
233233
using (var adapter = new NpgsqlDataAdapter(command))
@@ -257,10 +257,13 @@ static DataTable GetColumns(NpgsqlConnection conn, string?[]? restrictions)
257257
new DataColumn("character_set_name"), new DataColumn("collation_catalog")
258258
});
259259

260-
var getColumns = new StringBuilder();
261-
262-
getColumns.Append(
263-
"SELECT table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable, udt_name AS data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema, character_set_name, collation_catalog FROM information_schema.columns");
260+
var getColumns = new StringBuilder(@"
261+
SELECT
262+
table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable,
263+
udt_name AS data_type, character_maximum_length, character_octet_length, numeric_precision,
264+
numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema,
265+
character_set_name, collation_catalog
266+
FROM information_schema.columns");
264267

265268
using (var command = BuildCommand(conn, getColumns, restrictions, "table_catalog", "table_schema", "table_name", "column_name"))
266269
using (var adapter = new NpgsqlDataAdapter(command))
@@ -284,10 +287,7 @@ static DataTable GetViews(NpgsqlConnection conn, string?[]? restrictions)
284287
new DataColumn("check_option"), new DataColumn("is_updatable")
285288
});
286289

287-
var getViews = new StringBuilder();
288-
289-
//getViews.Append("SELECT table_catalog, table_schema, table_name, check_option, is_updatable FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema')");
290-
getViews.Append(@"
290+
var getViews = new StringBuilder(@"
291291
SELECT table_catalog, table_schema, table_name, check_option, is_updatable
292292
FROM information_schema.views
293293
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')");
@@ -331,25 +331,23 @@ static DataTable GetIndexes(NpgsqlConnection conn, string?[]? restrictions)
331331
new DataColumn("index_name")
332332
});
333333

334-
var getIndexes = new StringBuilder();
335-
336-
getIndexes.Append(
337-
@"select current_database() as table_catalog,
338-
n.nspname as table_schema,
339-
t.relname as table_name,
340-
i.relname as index_name
341-
from
342-
pg_catalog.pg_class i join
343-
pg_catalog.pg_index ix ON ix.indexrelid = i.oid join
344-
pg_catalog.pg_class t ON ix.indrelid = t.oid join
345-
pg_attribute a on t.oid = a.attrelid left join
346-
pg_catalog.pg_user u ON u.usesysid = i.relowner left join
347-
pg_catalog.pg_namespace n ON n.oid = i.relnamespace
348-
where
349-
i.relkind = 'i'
350-
and n.nspname not in ('pg_catalog', 'pg_toast')
351-
and a.attnum = ANY(ix.indkey)
352-
and t.relkind = 'r'");
334+
var getIndexes = new StringBuilder(@"
335+
SELECT current_database() AS table_catalog,
336+
n.nspname AS table_schema,
337+
t.relname AS table_name,
338+
i.relname AS index_name
339+
FROM
340+
pg_catalog.pg_class i
341+
JOIN pg_catalog.pg_index ix ON ix.indexrelid = i.oid
342+
JOIN pg_catalog.pg_class t ON ix.indrelid = t.oid
343+
JOIN pg_attribute a ON t.oid = a.attrelid
344+
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = i.relowner
345+
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
346+
WHERE
347+
i.relkind = 'i' AND
348+
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
349+
a.attnum = ANY(ix.indkey) AND
350+
t.relkind = 'r'");
353351

354352
using (var command = BuildCommand(conn, getIndexes, restrictions, false, "current_database()", "n.nspname", "t.relname", "i.relname"))
355353
using (var adapter = new NpgsqlDataAdapter(command))
@@ -367,26 +365,24 @@ static DataTable GetIndexColumns(NpgsqlConnection conn, string?[]? restrictions)
367365
new DataColumn("index_name"), new DataColumn("column_name")
368366
});
369367

370-
var getIndexColumns = new StringBuilder();
371-
372-
getIndexColumns.Append(
373-
@"select current_database() as table_catalog,
374-
n.nspname as table_schema,
375-
t.relname as table_name,
376-
i.relname as index_name,
377-
a.attname as column_name
378-
from
379-
pg_class t join
380-
pg_index ix on t.oid = ix.indrelid join
381-
pg_class i on ix.indexrelid = i.oid join
382-
pg_attribute a on t.oid = a.attrelid left join
383-
pg_namespace n on i.relnamespace = n.oid
368+
var getIndexColumns = new StringBuilder(@"
369+
SELECT current_database() AS table_catalog,
370+
n.nspname AS table_schema,
371+
t.relname AS table_name,
372+
i.relname AS index_name,
373+
a.attname AS column_name
374+
FROM
375+
pg_class t
376+
JOIN pg_index ix ON t.oid = ix.indrelid
377+
JOIN pg_class i ON ix.indexrelid = i.oid
378+
JOIN pg_attribute a ON t.oid = a.attrelid
379+
LEFT JOIN pg_namespace n ON i.relnamespace = n.oid
384380
where
385-
i.relkind = 'i'
386-
and n.nspname not in ('pg_catalog', 'pg_toast')
387-
and pg_catalog.pg_table_is_visible(i.oid)
388-
and a.attnum = ANY(ix.indkey)
389-
and t.relkind = 'r'");
381+
i.relkind = 'i' AND
382+
n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
383+
pg_catalog.pg_table_is_visible(i.oid) AND
384+
a.attnum = ANY(ix.indkey) AND
385+
t.relkind = 'r'");
390386

391387
using (var command = BuildCommand(conn, getIndexColumns, restrictions, false, "current_database()", "n.nspname", "t.relname", "i.relname", "a.attname"))
392388
using (var adapter = new NpgsqlDataAdapter(command))
@@ -397,28 +393,29 @@ and pg_catalog.pg_table_is_visible(i.oid)
397393

398394
static DataTable GetConstraints(NpgsqlConnection conn, string?[]? restrictions, string? constraintType)
399395
{
400-
var getConstraints = new StringBuilder();
401-
402-
getConstraints.Append(
403-
@"select
404-
current_database() as ""CONSTRAINT_CATALOG"",
405-
pgn.nspname as ""CONSTRAINT_SCHEMA"",
406-
pgc.conname as ""CONSTRAINT_NAME"",
407-
current_database() as ""TABLE_CATALOG"",
408-
pgtn.nspname as ""TABLE_SCHEMA"",
409-
pgt.relname as ""TABLE_NAME"",
410-
""CONSTRAINT_TYPE"",
411-
pgc.condeferrable as ""IS_DEFERRABLE"",
412-
pgc.condeferred as ""INITIALLY_DEFERRED""
413-
from pg_catalog.pg_constraint pgc
414-
inner join pg_catalog.pg_namespace pgn on pgc.connamespace = pgn.oid
415-
inner join pg_catalog.pg_class pgt on pgc.conrelid = pgt.oid
416-
inner join pg_catalog.pg_namespace pgtn on pgt.relnamespace = pgtn.oid
417-
inner join (
418-
select 'PRIMARY KEY' as ""CONSTRAINT_TYPE"", 'p' as ""contype"" union all
419-
select 'FOREIGN KEY' as ""CONSTRAINT_TYPE"", 'f' as ""contype"" union all
420-
select 'UNIQUE KEY' as ""CONSTRAINT_TYPE"", 'u' as ""contype""
421-
) mapping_table on mapping_table.contype = pgc.contype");
396+
var getConstraints = new StringBuilder(@"
397+
SELECT
398+
current_database() AS ""CONSTRAINT_CATALOG"",
399+
pgn.nspname AS ""CONSTRAINT_SCHEMA"",
400+
pgc.conname AS ""CONSTRAINT_NAME"",
401+
current_database() AS ""TABLE_CATALOG"",
402+
pgtn.nspname AS ""TABLE_SCHEMA"",
403+
pgt.relname AS ""TABLE_NAME"",
404+
""CONSTRAINT_TYPE"",
405+
pgc.condeferrable AS ""IS_DEFERRABLE"",
406+
pgc.condeferred AS ""INITIALLY_DEFERRED""
407+
FROM
408+
pg_catalog.pg_constraint pgc
409+
JOIN pg_catalog.pg_namespace pgn ON pgc.connamespace = pgn.oid
410+
JOIN pg_catalog.pg_class pgt ON pgc.conrelid = pgt.oid
411+
JOIN pg_catalog.pg_namespace pgtn ON pgt.relnamespace = pgtn.oid
412+
JOIN (
413+
SELECT 'PRIMARY KEY' AS ""CONSTRAINT_TYPE"", 'p' AS ""contype""
414+
UNION ALL
415+
SELECT 'FOREIGN KEY' AS ""CONSTRAINT_TYPE"", 'f' AS ""contype""
416+
UNION ALL
417+
SELECT 'UNIQUE KEY' AS ""CONSTRAINT_TYPE"", 'u' AS ""contype""
418+
) mapping_table ON mapping_table.contype = pgc.contype");
422419
if ("ForeignKeys".Equals(constraintType))
423420
getConstraints.Append(" and pgc.contype='f'");
424421
else if ("PrimaryKey".Equals(constraintType))
@@ -438,28 +435,29 @@ inner join (
438435

439436
static DataTable GetConstraintColumns(NpgsqlConnection conn, string?[]? restrictions)
440437
{
441-
var getConstraintColumns = new StringBuilder();
442-
443-
getConstraintColumns.Append(
444-
@"select current_database() as constraint_catalog,
445-
n.nspname as constraint_schema,
446-
c.conname as constraint_name,
447-
current_database() as table_catalog,
448-
n.nspname as table_schema,
449-
t.relname as table_name,
450-
a.attname as column_name,
451-
a.attnum as ordinal_number,
438+
var getConstraintColumns = new StringBuilder(@"
439+
SELECT current_database() AS constraint_catalog,
440+
n.nspname AS constraint_schema,
441+
c.conname AS constraint_name,
442+
current_database() AS table_catalog,
443+
n.nspname AS table_schema,
444+
t.relname AS table_name,
445+
a.attname AS column_name,
446+
a.attnum AS ordinal_number,
452447
mapping_table.constraint_type
453-
from pg_constraint c
454-
inner join pg_namespace n on n.oid = c.connamespace
455-
inner join pg_class t on t.oid = c.conrelid and t.relkind = 'r'
456-
inner join pg_attribute a on t.oid = a.attrelid and a.attnum = ANY(c.conkey)
457-
inner join (
458-
select 'PRIMARY KEY' as constraint_type, 'p' as contype union all
459-
select 'FOREIGN KEY' as constraint_type, 'f' as contype union all
460-
select 'UNIQUE KEY' as constraint_type, 'u' as contype
461-
) mapping_table on mapping_table.contype = c.contype
462-
and n.nspname not in ('pg_catalog', 'pg_toast')");
448+
FROM pg_constraint c
449+
JOIN pg_namespace n on n.oid = c.connamespace
450+
JOIN pg_class t on t.oid = c.conrelid AND t.relkind = 'r'
451+
JOIN pg_attribute a on t.oid = a.attrelid AND a.attnum = ANY(c.conkey)
452+
JOIN (
453+
SELECT 'PRIMARY KEY' AS constraint_type, 'p' AS contype
454+
UNION ALL
455+
SELECT 'FOREIGN KEY' AS constraint_type, 'f' AS contype
456+
UNION ALL
457+
SELECT 'UNIQUE KEY' AS constraint_type, 'u' AS contype
458+
) mapping_table ON
459+
mapping_table.contype = c.contype
460+
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')");
463461

464462
using var command = BuildCommand(conn, getConstraintColumns, restrictions, false, "current_database()", "n.nspname", "t.relname", "c.conname", "a.attname");
465463
using var adapter = new NpgsqlDataAdapter(command);

0 commit comments

Comments
 (0)