diff options
| author | Dan Goodliffe <dan@randomdan.homeip.net> | 2021-06-12 19:21:22 +0100 |
|---|---|---|
| committer | Dan Goodliffe <dan@randomdan.homeip.net> | 2021-06-12 19:21:22 +0100 |
| commit | de6ac08985252e7b5d36959896386a414e590d15 (patch) | |
| tree | 60abba81244c4aa484660b6ae15d7b6f4379f8b1 /lib/output/pq/sql | |
| parent | Simplify and allow offset when creating objects from rows (diff) | |
| download | mygrate-de6ac08985252e7b5d36959896386a414e590d15.tar.bz2 mygrate-de6ac08985252e7b5d36959896386a414e590d15.tar.xz mygrate-de6ac08985252e7b5d36959896386a414e590d15.zip | |
Load schema and config etc from target DB
Diffstat (limited to 'lib/output/pq/sql')
| -rw-r--r-- | lib/output/pq/sql/selectColumns.sql | 10 | ||||
| -rw-r--r-- | lib/output/pq/sql/selectSource.sql | 2 | ||||
| -rw-r--r-- | lib/output/pq/sql/selectTables.sql | 9 | ||||
| -rw-r--r-- | lib/output/pq/sql/updateSourcePosition.sql | 2 |
4 files changed, 10 insertions, 13 deletions
diff --git a/lib/output/pq/sql/selectColumns.sql b/lib/output/pq/sql/selectColumns.sql index e9cd107..9c1cbab 100644 --- a/lib/output/pq/sql/selectColumns.sql +++ b/lib/output/pq/sql/selectColumns.sql @@ -1,6 +1,6 @@ -SELECT t.table_name, STRING_AGG(c.column_name, '|' ORDER BY c.ordinal_position) +SELECT t.table_name, c.column_name, c.mysql_ordinal, k.column_name IS NOT NULL is_pk FROM mygrate.source s - JOIN information_schema.tables t USING(table_schema) - LEFT OUTER JOIN information_schema.columns c USING(table_schema, table_name) -WHERE s.id = $1 -GROUP BY t.table_name + JOIN mygrate.tables t USING(source_id) + JOIN mygrate.table_columns c USING(table_id) + LEFT OUTER JOIN information_schema.key_column_usage k USING(table_schema, table_name, column_name) +WHERE s.source_id = $1 diff --git a/lib/output/pq/sql/selectSource.sql b/lib/output/pq/sql/selectSource.sql index db76b4a..32b413d 100644 --- a/lib/output/pq/sql/selectSource.sql +++ b/lib/output/pq/sql/selectSource.sql @@ -1,3 +1,3 @@ SELECT host, username, password, port, filename, position, serverid FROM mygrate.source s -WHERE s.id = $1 +WHERE s.source_id = $1 diff --git a/lib/output/pq/sql/selectTables.sql b/lib/output/pq/sql/selectTables.sql index 389dfe2..ac372b6 100644 --- a/lib/output/pq/sql/selectTables.sql +++ b/lib/output/pq/sql/selectTables.sql @@ -1,6 +1,3 @@ -SELECT t.table_name, STRING_AGG(k.column_name, '|' ORDER BY k.ordinal_position) pk_cols -FROM mygrate.source s - JOIN information_schema.tables t USING(table_schema) - LEFT OUTER JOIN information_schema.key_column_usage k USING(table_schema, table_name) -WHERE s.id = $1 -GROUP BY t.table_name +SELECT t.table_name +FROM mygrate.tables t +WHERE t.source_id = $1 diff --git a/lib/output/pq/sql/updateSourcePosition.sql b/lib/output/pq/sql/updateSourcePosition.sql index 3d3d665..74e22fc 100644 --- a/lib/output/pq/sql/updateSourcePosition.sql +++ b/lib/output/pq/sql/updateSourcePosition.sql @@ -1,4 +1,4 @@ UPDATE mygrate.source SET filename = $1, position = $2 -WHERE id = $3 +WHERE source_id = $3 |