Examples with MatcherStatement com.google.cloud.spanner.pgadapter.statements.MatcherStatement used on opensource projects

Example 11 with MatcherStatement

use of com.google.cloud.spanner.pgadapter.statements.MatcherStatement in project pgadapter by GoogleCloudPlatform.

the class PSQLTest method testDescribeTableAutocomplete.

@Test
public void testDescribeTableAutocomplete() {
    // PSQL equivalent: \\d <table>
    String sql = "SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE" + " substring(pg_catalog.quote_ident(c.relname),1,4)='user' AND" + " pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM" + " pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')\n" + "UNION\n" + "SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE" + " substring(pg_catalog.quote_ident(n.nspname) || '.',1,4)='user' AND (SELECT" + " pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE" + " substring(pg_catalog.quote_ident(nspname) || '.',1,4) =" + " substring('user',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1\n" + "UNION\n" + "SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname)" + " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid" + " AND substring(pg_catalog.quote_ident(n.nspname) || '.' ||" + " pg_catalog.quote_ident(c.relname),1,4)='user' AND" + " substring(pg_catalog.quote_ident(n.nspname) || '.',1,4) =" + " substring('user',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND" + " (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE" + " substring(pg_catalog.quote_ident(nspname) || '.',1,4) =" + " substring('user',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1\n" + "LIMIT 1000";
    String expected = "SELECT table_name AS quote_ident FROM information_schema.tables WHERE " + "table_schema = 'public' AND STARTS_WITH(LOWER(table_name), LOWER('user')) LIMIT 1000";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}

Example 12 with MatcherStatement

use of com.google.cloud.spanner.pgadapter.statements.MatcherStatement in project pgadapter by GoogleCloudPlatform.

the class PSQLTest method testDescribeTableMetadataHandlesBobbyTables.

@Test
public void testDescribeTableMetadataHandlesBobbyTables() {
    // PSQL equivalent: \d <table> (3)
    String sql = "SELECT a.attname,\n" + "  pg_catalog.format_type(a.atttypid, a.atttypmod),\n" + "  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)\n" + "   FROM pg_catalog.pg_attrdef d\n" + "   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),\n" + "  a.attnotnull,\n" + "  NULL AS attcollation,\n" + "  ''::pg_catalog.char AS attidentity,\n" + "  ''::pg_catalog.char AS attgenerated\n" + "FROM pg_catalog.pg_attribute a\n" + "WHERE a.attrelid = 'bobby'; DROP TABLE USERS; SELECT'' AND a.attnum > 0 AND NOT" + " a.attisdropped\n" + "ORDER BY a.attnum;";
    String expected = "SELECT" + " t.column_name as attname," + " t.data_type as format_type," + " '' as substring," + " t.is_nullable = 'NO' as attnotnull," + " null::INTEGER as attcollation," + " null::INTEGER as indexdef," + " null::INTEGER as attfdwoptions" + " FROM" + " information_schema.columns AS t" + " WHERE" + " t.table_schema='public'" + " AND t.table_name = 'bobby''; DROP TABLE USERS; SELECT'''";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}

Example 13 with MatcherStatement

use of com.google.cloud.spanner.pgadapter.statements.MatcherStatement in project pgadapter by GoogleCloudPlatform.

the class PSQLTest method testDescribeAllTableMetadataTranslates.

@Test
public void testDescribeAllTableMetadataTranslates() {
    // PSQL equivalent: \dt
    String sql = "SELECT n.nspname as \"Schema\",\n" + "  c.relname as \"Name\",\n" + "  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN" + " 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN" + " 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I'" + " THEN 'partitioned index' END as \"Type\",\n" + "  pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\"\n" + "FROM pg_catalog.pg_class c\n" + "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + "WHERE c.relkind IN ('r','p','')\n" + "      AND n.nspname <> 'pg_catalog'\n" + "      AND n.nspname <> 'information_schema'\n" + "      AND n.nspname !~ '^pg_toast'\n" + "  AND pg_catalog.pg_table_is_visible(c.oid)\n" + "ORDER BY 1,2;";
    String expected = "SELECT * FROM information_schema.tables";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}

Example 14 with MatcherStatement

use of com.google.cloud.spanner.pgadapter.statements.MatcherStatement in project pgadapter by GoogleCloudPlatform.

the class PSQLTest method testDescribeSelectedIndexMetadataTranslates.

@Test
public void testDescribeSelectedIndexMetadataTranslates() {
    // PSQL equivalent: \di <index>
    String sql = "SELECT n.nspname as \"Schema\",\n" + "  c.relname as \"Name\",\n" + "  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN" + " 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN" + " 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I'" + " THEN 'partitioned index' END as \"Type\",\n" + "  pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\",\n" + " c2.relname as \"Table\"\n" + "FROM pg_catalog.pg_class c\n" + "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + "     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + "     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n" + "WHERE c.relkind IN ('i','I','s','')\n" + "      AND n.nspname !~ '^pg_toast'\n" + "  AND c.relname OPERATOR(pg_catalog.~) '^(index)$'\n" + "  AND pg_catalog.pg_table_is_visible(c.oid)\n" + "ORDER BY 1,2;";
    String expected = "SELECT table_catalog, table_schema, table_name, index_name, index_type, parent_table_name, is_unique, is_null_filtered, index_state, spanner_is_managed FROM information_schema.indexes WHERE LOWER(index_name) =" + " LOWER('index')";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}

Example 15 with MatcherStatement

use of com.google.cloud.spanner.pgadapter.statements.MatcherStatement in project pgadapter by GoogleCloudPlatform.

the class PSQLTest method testDescribeTableAttributesTranslates.

@Test
public void testDescribeTableAttributesTranslates() {
    // PSQL equivalent: \d <table> (4)
    String sql = "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" + " WHERE c.oid=i.inhparent AND i.inhrelid = '-2264987671676060158' AND c.relkind !=" + " 'p' ORDER BY inhseqno;";
    String expected = "SELECT 1 LIMIT 0";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}