Merge lp:~mmcm/akiban-server/pg-chartio-queries into lp:~akiban-technologies/akiban-server/trunk

[Mike McMahon]

Add emulation for the simple queries that chartio sends when setting up a data source.

I am not too confident that this will be enough, but putting it into the cloud should allow testing to see whether it is enough for a demo at least.

Because they also use prepared statements, need to run the unparsed matchers then, too. I don't think this will any significant effects.

[Nathan Williams (nwilliams)]

Delightful.

review: Approve

[Akiban Build User (build-akiban)]

There were 2 failures during build/test:

* job server-build failed at build number 3978: http://172.16.20.104:8080/job/server-build/3978/

* view must-pass failed: server-build is yellow

review: Needs Fixing

=== modified file 'src/main/java/com/akiban/sql/pg/PostgresEmulatedMetaDataStatement.java'
--- src/main/java/com/akiban/sql/pg/PostgresEmulatedMetaDataStatement.java	2013-04-04 21:44:13 +0000
+++ src/main/java/com/akiban/sql/pg/PostgresEmulatedMetaDataStatement.java	2013-04-19 22:19:23 +0000
@@ -128,7 +128,10 @@
                                "FROM pg_catalog.pg_trigger t\\s+" +
                                "WHERE t.tgrelid = '(-?\\d+)' AND (?:t.tgconstraint = 0|" + // 2
                                "\\(not tgisconstraint  OR NOT EXISTS  \\(SELECT 1 FROM pg_catalog.pg_depend d    JOIN pg_catalog.pg_constraint c ON \\(d.refclassid = c.tableoid AND d.refobjid = c.oid\\)    WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'\\)\\))(?:\\s+ORDER BY 1)?;?", true),
-        PSQL_DESCRIBE_VIEW("SELECT pg_catalog.pg_get_viewdef\\('(-?\\d+)'::pg_catalog.oid, true\\);?", true);
+        PSQL_DESCRIBE_VIEW("SELECT pg_catalog.pg_get_viewdef\\('(-?\\d+)'::pg_catalog.oid, true\\);?", true),
+        CHARTIO_TABLES("SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )  OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME "),
+        CHARTIO_KEYS("SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   \\(i.keys\\).n AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON \\(ct.oid = a.attrelid\\)   JOIN pg_catalog.pg_namespace n ON \\(ct.relnamespace = n.oid\\)   JOIN \\(SELECT i.indexrelid, i.indrelid, i.indisprimary,              information_schema._pg_expandarray\\(i.indkey\\) AS keys         FROM pg_catalog.pg_index i\\) i     ON \\(a.attnum = \\(i.keys\\).x AND a.attrelid = i.indrelid\\)   JOIN pg_catalog.pg_class ci ON \\(ci.oid = i.indexrelid\\) WHERE true  AND ct.relname = E'(.+)' AND i.indisprimary  ORDER BY table_name, pk_name, key_seq", true),
+        CHARTIO_COLUMNS("SELECT \\* FROM \\(SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR \\(t.typtype = 'd' AND t.typnotnull\\) AS attnotnull,a.atttypmod,a.attlen,row_number\\(\\) OVER \\(PARTITION BY a.attrelid ORDER BY a.attnum\\) AS attnum, pg_catalog.pg_get_expr\\(def.adbin, def.adrelid\\) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON \\(c.relnamespace = n.oid\\)  JOIN pg_catalog.pg_attribute a ON \\(a.attrelid=c.oid\\)  JOIN pg_catalog.pg_type t ON \\(a.atttypid = t.oid\\)  LEFT JOIN pg_catalog.pg_attrdef def ON \\(a.attrelid=def.adrelid AND a.attnum = def.adnum\\)  LEFT JOIN pg_catalog.pg_description dsc ON \\(c.oid=dsc.objoid AND a.attnum = dsc.objsubid\\)  LEFT JOIN pg_catalog.pg_class dc ON \\(dc.oid=dsc.classoid AND dc.relname='pg_class'\\)  LEFT JOIN pg_catalog.pg_namespace dn ON \\(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog'\\)  WHERE a.attnum > 0 AND NOT a.attisdropped  AND c.relname LIKE E'(.+)'\\) c WHERE true  ORDER BY nspname,c.relname,attnum ", true);
 
         private String sql;
         private Pattern pattern;
@@ -185,6 +188,8 @@
         new PostgresType(PostgresType.TypeOid.BOOL_TYPE_OID, (short)1, -1, AkType.BOOL, AkBool.INSTANCE.instance(FIELDS_NULLABLE));
     static final PostgresType INT2_PG_TYPE = 
         new PostgresType(PostgresType.TypeOid.INT2_TYPE_OID, (short)2, -1, AkType.LONG, MNumeric.SMALLINT.instance(FIELDS_NULLABLE));
+    static final PostgresType INT4_PG_TYPE = 
+        new PostgresType(PostgresType.TypeOid.INT4_TYPE_OID, (short)4, -1, AkType.LONG, MNumeric.INT.instance(FIELDS_NULLABLE));
     static final PostgresType OID_PG_TYPE = 
         new PostgresType(PostgresType.TypeOid.OID_TYPE_OID, (short)4, -1, AkType.LONG, MNumeric.INT.instance(FIELDS_NULLABLE));
     static final PostgresType TYPNAME_PG_TYPE = 
@@ -325,6 +330,21 @@
             names = new String[] { "pg_get_viewdef" };
             types = new PostgresType[] { VIEWDEF_PG_TYPE };
             break;
+        case CHARTIO_TABLES:
+            ncols = 5;
+            names = new String[] { "table_cat", "table_schem", "table_name", "table_type", "remarks" };
+            types = new PostgresType[] { IDENT_PG_TYPE, IDENT_PG_TYPE, IDENT_PG_TYPE, LIST_TYPE_PG_TYPE, CHAR0_PG_TYPE };
+            break;
+        case CHARTIO_KEYS:
+            ncols = 6;
+            names = new String[] { "table_cat", "table_schem", "table_name", "column_name", "key_seq", "pk_name" };
+            types = new PostgresType[] { IDENT_PG_TYPE, IDENT_PG_TYPE, IDENT_PG_TYPE, IDENT_PG_TYPE, INT2_PG_TYPE, IDENT_PG_TYPE };
+            break;
+        case CHARTIO_COLUMNS:
+            ncols = 12;
+            names = new String[] { "nspname", "relname", "attname", "atttypid", "attnotnull", "atttypmod", "attlen", "attnum", "adsrc", "description", "typbasetype", "typtype" };
+            types = new PostgresType[] { IDENT_PG_TYPE, IDENT_PG_TYPE, IDENT_PG_TYPE, OID_PG_TYPE, CHAR1_PG_TYPE, INT4_PG_TYPE, INT2_PG_TYPE, INT2_PG_TYPE, CHAR0_PG_TYPE, CHAR0_PG_TYPE, OID_PG_TYPE, CHAR1_PG_TYPE };
+            break;
         default:
             return;
         }
@@ -415,6 +435,15 @@
         case PSQL_DESCRIBE_VIEW:
             nrows = psqlDescribeViewQuery(server, messenger, encoder, maxrows, usePVals);
             break;
+        case CHARTIO_TABLES:
+            nrows = chartioTablesQuery(server, messenger, encoder, maxrows, usePVals);
+            break;
+        case CHARTIO_KEYS:
+            nrows = chartioKeysQuery(server, messenger, encoder, maxrows, usePVals);
+            break;
+        case CHARTIO_COLUMNS:
+            nrows = chartioColumnsQuery(server, messenger, encoder, maxrows, usePVals);
+            break;
         }
         {        
           messenger.beginMessage(PostgresMessages.COMMAND_COMPLETE_TYPE.code());
@@ -1106,4 +1135,141 @@
         return 1;
     }
 
+    private int chartioTablesQuery(PostgresServerSession server, PostgresMessenger messenger, ServerValueEncoder encoder, int maxrows, boolean usePVals) throws IOException {
+        int nrows = 0;
+        List<Columnar> tables = new ArrayList<>();
+        AkibanInformationSchema ais = server.getAIS();
+        tables.addAll(ais.getUserTables().values());
+        tables.addAll(ais.getViews().values());
+        Iterator<Columnar> iter = tables.iterator();
+        while (iter.hasNext()) {
+            TableName name = iter.next().getName();
+            if (name.getSchemaName().equals(TableName.INFORMATION_SCHEMA) ||
+                name.getSchemaName().equals(TableName.SECURITY_SCHEMA) ||
+                !server.isSchemaAccessible(name.getSchemaName()))
+                iter.remove();
+        }
+        Collections.sort(tables, tablesByName);
+        for (Columnar table : tables) {
+            TableName name = table.getName();
+            messenger.beginMessage(PostgresMessages.DATA_ROW_TYPE.code());
+            messenger.writeShort(5); // 5 columns for this query
+            writeColumn(messenger, encoder, usePVals, 
+                        null, IDENT_PG_TYPE);
+            writeColumn(messenger, encoder, usePVals, 
+                        name.getSchemaName(), IDENT_PG_TYPE);
+            writeColumn(messenger, encoder, usePVals, 
+                        name.getTableName(), IDENT_PG_TYPE);
+            String type = table.isView() ? "VIEW" : "TABLE";
+            writeColumn(messenger, encoder, usePVals, 
+                        type, LIST_TYPE_PG_TYPE);
+            writeColumn(messenger, encoder, usePVals, 
+                        null, CHAR0_PG_TYPE);
+            messenger.sendMessage();
+            nrows++;
+            if ((maxrows > 0) && (nrows >= maxrows)) {
+                break;
+            }
+        }
+        return nrows;
+    }
+
+    private int chartioKeysQuery(PostgresServerSession server, PostgresMessenger messenger, ServerValueEncoder encoder, int maxrows, boolean usePVals) throws IOException {
+        int nrows = 0;
+        String name = groups.get(1);
+        AkibanInformationSchema ais = server.getAIS();
+        List<UserTable> tables = new ArrayList<>();
+        for (UserTable table : ais.getUserTables().values()) {
+            TableName tableName = table.getName();
+            if (server.isSchemaAccessible(tableName.getSchemaName()) &&
+                tableName.getTableName().equalsIgnoreCase(name)) {
+                tables.add(table);
+            }
+        }
+        Collections.sort(tables, tablesByName);
+        rows:
+        for (UserTable table : tables) {
+            TableName tableName = table.getName();
+            TableIndex index = table.getIndex(Index.PRIMARY_KEY_CONSTRAINT);
+            if (index != null) {
+                for (IndexColumn column : index.getKeyColumns()) {
+                    messenger.beginMessage(PostgresMessages.DATA_ROW_TYPE.code());
+                    messenger.writeShort(6); // 6 columns for this query
+                    writeColumn(messenger, encoder, usePVals, 
+                                null, IDENT_PG_TYPE);
+                    writeColumn(messenger, encoder, usePVals, 
+                                tableName.getSchemaName(), IDENT_PG_TYPE);
+                    writeColumn(messenger, encoder, usePVals, 
+                                tableName.getTableName(), IDENT_PG_TYPE);
+                    writeColumn(messenger, encoder, usePVals, 
+                                column.getColumn().getName(), IDENT_PG_TYPE);
+                    writeColumn(messenger, encoder, usePVals, 
+                                (short)(column.getPosition() + 1), INT2_PG_TYPE);
+                    writeColumn(messenger, encoder, usePVals, 
+                                index.getIndexName().getName(), IDENT_PG_TYPE);
+                    messenger.sendMessage();
+                    nrows++;
+                    if ((maxrows > 0) && (nrows >= maxrows)) {
+                        break rows;
+                    }
+                }
+            }
+        }
+        return nrows;
+    }
+
+    private int chartioColumnsQuery(PostgresServerSession server, PostgresMessenger messenger, ServerValueEncoder encoder, int maxrows, boolean usePVals) throws IOException {
+        int nrows = 0;
+        String name = groups.get(1);
+        AkibanInformationSchema ais = server.getAIS();
+        List<UserTable> tables = new ArrayList<>();
+        for (UserTable table : ais.getUserTables().values()) {
+            TableName tableName = table.getName();
+            if (server.isSchemaAccessible(tableName.getSchemaName()) &&
+                tableName.getTableName().equalsIgnoreCase(name)) {
+                tables.add(table);
+            }
+        }
+        Collections.sort(tables, tablesByName);
+        rows:
+        for (UserTable table : tables) {
+            TableName tableName = table.getName();
+            for (Column column : table.getColumns()) {
+                PostgresType type = PostgresType.fromAIS(column);
+                messenger.beginMessage(PostgresMessages.DATA_ROW_TYPE.code());
+                messenger.writeShort(12); // 12 columns for this query
+                writeColumn(messenger, encoder, usePVals, 
+                            tableName.getSchemaName(), IDENT_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            tableName.getTableName(), IDENT_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            column.getName(), IDENT_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            type.getOid(), OID_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            column.getNullable() ? "t" : "f", CHAR1_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            type.getModifier(), INT4_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            type.getLength(), INT2_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            (short)(column.getPosition() + 1), INT2_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            null, CHAR0_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            null, CHAR0_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            0, OID_PG_TYPE);
+                writeColumn(messenger, encoder, usePVals, 
+                            "b", CHAR1_PG_TYPE);
+                messenger.sendMessage();
+                nrows++;
+                if ((maxrows > 0) && (nrows >= maxrows)) {
+                    break rows;
+                }
+            }
+        }
+        return nrows;
+    }
+
 }

=== modified file 'src/main/java/com/akiban/sql/pg/PostgresServerConnection.java'
--- src/main/java/com/akiban/sql/pg/PostgresServerConnection.java	2013-04-04 21:44:13 +0000
+++ src/main/java/com/akiban/sql/pg/PostgresServerConnection.java	2013-04-19 22:19:23 +0000
@@ -751,6 +751,15 @@
         if (statementCache != null)
             pstmt = statementCache.get(sql);
         if (pstmt == null) {
+            for (PostgresStatementParser parser : unparsedGenerators) {
+                pstmt = parser.parse(this, sql, null);
+                if (pstmt != null) {
+                    pstmt.setAISGeneration(ais.getGeneration());
+                    break;
+                }
+            }
+        }
+        if (pstmt == null) {
             StatementNode stmt;
             List<ParameterNode> params;
             try {