Search in sources :

Example 6 with MatcherStatement

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

the class PSQLTest method testDescribeTableMatchHandlesBobbyTables.

@Test
public void testDescribeTableMatchHandlesBobbyTables() {
    // PSQL equivalent: \d <table> (1)
    String sql = "SELECT c.oid,\n" + "  n.nspname,\n" + "  c.relname\n" + "FROM pg_catalog.pg_class c\n" + "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + "WHERE c.relname OPERATOR(pg_catalog.~) '^(bobby'; DROP TABLE USERS; SELECT')$'\n" + "  AND pg_catalog.pg_table_is_visible(c.oid)\n" + "ORDER BY 2, 3;";
    String expected = "SELECT" + " t.table_name as oid," + " 'public' as nspname," + " t.table_name as relname" + " FROM" + " information_schema.tables AS t" + " WHERE" + " t.table_schema='public'" + " AND" + " LOWER(t.table_name) = LOWER('bobby''; DROP TABLE USERS; SELECT''')";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}
Also used : MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 7 with MatcherStatement

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

the class PSQLTest method testDescribeTableMetadataAutocomplete.

@Test
public void testDescribeTableMetadataAutocomplete() {
    // PSQL equivalent: \\dt <table>
    String sql = "SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN" + " ('r', 'p') AND 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 c.relkind IN ('r', 'p') 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" + " STARTS_WITH(LOWER(table_name), LOWER('user')) LIMIT 1000";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}
Also used : MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 8 with MatcherStatement

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

the class PSQLTest method testDynamicCommands.

@Test
public void testDynamicCommands() throws Exception {
    String inputJSON = "" + "{" + " \"commands\": " + "   [ " + "     {" + "       \"input_pattern\": \"^SELECT \\* FROM USERS;?$\", " + "       \"output_pattern\": \"RESULT 1\", " + "       \"matcher_array\": []" + "     }," + "     {" + "       \"input_pattern\": \"^SELECT (?<selector>.*) FROM USERS WHERE (?<arg1>.*) = (?<arg2>.*);?$\", " + "       \"output_pattern\": \"RESULT 2: selector=%s, arg2=%s, arg1=%s\", " + "       \"matcher_array\": [ \"selector\", \"arg2\", \"arg1\" ]" + "     }" + "   ]" + "}";
    JSONParser parser = new JSONParser();
    Mockito.when(server.getOptions()).thenReturn(options);
    Mockito.when(options.getCommandMetadataJSON()).thenReturn((JSONObject) parser.parse(inputJSON));
    String firstSQL = "SELECT * FROM USERS;";
    String expectedFirstResult = "RESULT 1";
    String secondSQL = "SELECT name FROM USERS WHERE age = 30;";
    String expectedSecondResult = "RESULT 2: selector=name, arg2=30, arg1=age";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(firstSQL), connectionHandler);
    assertEquals(expectedFirstResult, matcherStatement.getSql());
    matcherStatement = new MatcherStatement(options, parse(secondSQL), connectionHandler);
    assertEquals(expectedSecondResult, matcherStatement.getSql());
}
Also used : JSONParser(org.json.simple.parser.JSONParser) MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 9 with MatcherStatement

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

the class PSQLTest method testDescribeSelectedTableMetadataTranslates.

@Test
public void testDescribeSelectedTableMetadataTranslates() {
    // PSQL equivalent: \dt <table>
    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','s','')\n" + "      AND n.nspname !~ '^pg_toast'\n" + "  AND c.relname OPERATOR(pg_catalog.~) '^(users)$'\n" + "  AND pg_catalog.pg_table_is_visible(c.oid)\n" + "ORDER BY 1,2;";
    String expected = "SELECT * FROM information_schema.tables WHERE LOWER(table_name) = LOWER('users')";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}
Also used : MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 10 with MatcherStatement

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

the class PSQLTest method testDescribeSchemaMetadataAutocomplete.

@Test
public void testDescribeSchemaMetadataAutocomplete() {
    // PSQL equivalent: \\dn <schema>
    String sql = "SELECT pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace  WHERE" + " substring(pg_catalog.quote_ident(nspname),1,6)='schema'\n" + "LIMIT 1000";
    String expected = "SELECT schema_name AS quote_ident FROM INFORMATION_SCHEMA.SCHEMATA WHERE" + " STARTS_WITH(LOWER(schema_name), LOWER('schema')) LIMIT 1000";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expected, matcherStatement.getSql());
}
Also used : MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Aggregations

MatcherStatement (com.google.cloud.spanner.pgadapter.statements.MatcherStatement)29 Test (org.junit.Test)29 JSONParser (org.json.simple.parser.JSONParser)2