Search in sources :

Example 21 with MatcherStatement

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

the class PSQLTest method testDescribeSelectedSchemaMetadataTranslates.

@Test
public void testDescribeSelectedSchemaMetadataTranslates() {
    // PSQL equivalent: \dn <schema>
    String sql = "SELECT n.nspname AS \"Name\",\n" + "  pg_catalog.pg_get_userbyid(n.nspowner) AS \"Owner\"\n" + "FROM pg_catalog.pg_namespace n\n" + "WHERE n.nspname OPERATOR(pg_catalog.~) '^(schema)$'\n" + "ORDER BY 1;";
    String expected = "SELECT * FROM information_schema.schemata WHERE LOWER(schema_name) = LOWER('schema')";
    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 22 with MatcherStatement

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

the class PSQLTest method testMatcherGroupInPlaceReplacements.

@Test
public void testMatcherGroupInPlaceReplacements() throws Exception {
    String inputJSON = "" + "{" + " \"commands\": " + "   [ " + "     {" + "       \"input_pattern\": \"^SELECT (?<expression>.*) FROM (?<table>.*);?$\", " + "       \"output_pattern\": \"TABLE: ${table}, EXPRESSION: ${expression}\", " + "       \"matcher_array\": []" + "     }" + "   ]" + "}";
    JSONParser parser = new JSONParser();
    Mockito.when(server.getOptions()).thenReturn(options);
    Mockito.when(options.getCommandMetadataJSON()).thenReturn((JSONObject) parser.parse(inputJSON));
    String sql = "SELECT * FROM USERS;";
    String expectedResult = "TABLE: USERS, EXPRESSION: *";
    MatcherStatement matcherStatement = new MatcherStatement(options, parse(sql), connectionHandler);
    assertEquals(expectedResult, matcherStatement.getSql());
}
Also used : JSONParser(org.json.simple.parser.JSONParser) MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 23 with MatcherStatement

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

the class PSQLTest method testTableInsertAutocomplete.

@Test
public void testTableInsertAutocomplete() {
    // PSQL equivalent: INSERT INTO <table>
    String sql = "SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN" + " ('r', 'f', 'v', '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', 'f', 'v', '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" + " 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());
}
Also used : MatcherStatement(com.google.cloud.spanner.pgadapter.statements.MatcherStatement) Test(org.junit.Test)

Example 24 with MatcherStatement

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

the class PSQLTest method testDescribeAllSchemaMetadataTranslates.

@Test
public void testDescribeAllSchemaMetadataTranslates() {
    // PSQL equivalent: \dn
    String sql = "SELECT n.nspname AS \"Name\",\n" + "  pg_catalog.pg_get_userbyid(n.nspowner) AS \"Owner\"\n" + "FROM pg_catalog.pg_namespace n\n" + "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n" + "ORDER BY 1;";
    String expected = "SELECT * FROM information_schema.schemata";
    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 25 with MatcherStatement

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

the class PSQLTest method testDescribeSelectedTableMetadataHandlesBobbyTables.

@Test
public void testDescribeSelectedTableMetadataHandlesBobbyTables() {
    // 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.~) '^(bobby'; DROP TABLE USERS; SELECT')$'\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('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)

Aggregations

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