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());
}
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());
}
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());
}
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());
}
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());
}
Aggregations