@Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { checkOpen(); if (resultSetType != ResultSet.TYPE_FORWARD_ONLY || resultSetConcurrency != ResultSet.CONCUR_READ_ONLY) { throw new SQLFeatureNotSupportedException(); } return createStatement(); }
@Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { checkOpen(); if (resultSetHoldability != ResultSet.CLOSE_CURSORS_AT_COMMIT) { throw new SQLFeatureNotSupportedException(); } return createStatement(resultSetType, resultSetConcurrency); }
@Override public ResultSet getCatalogs() throws SQLException { StringBuilder buf = new StringBuilder("select \n" + " DISTINCT " + TENANT_ID + " " + TABLE_CAT + " from " + SYSTEM_CATALOG + " " + SYSTEM_CATALOG_ALIAS + " where " + COLUMN_NAME + " is null" + " and " + COLUMN_FAMILY + " is null" + " and " + TENANT_ID + " is not null"); addTenantIdFilter(buf, null); buf.append(" order by " + TENANT_ID); Statement stmt = connection.createStatement(); return stmt.executeQuery(buf.toString()); }
public static void deleteViewIndexSequences(PhoenixConnection connection, PName name, boolean isNamespaceMapped) throws SQLException { String schemaName = getViewIndexSequenceSchemaName(name, isNamespaceMapped); String sequenceName = getViewIndexSequenceName(name, null, isNamespaceMapped); connection.createStatement().executeUpdate("DELETE FROM " + PhoenixDatabaseMetaData.SYSTEM_SEQUENCE + " WHERE " + PhoenixDatabaseMetaData.SEQUENCE_SCHEMA + (schemaName.length() > 0 ? "='" + schemaName + "'" : " IS NULL") + (isNamespaceMapped ? " AND " + PhoenixDatabaseMetaData.SEQUENCE_NAME + " = '" + sequenceName + "'" : "")); }
/** * Go through all the views in the system catalog table and add them to orphanViewSet * @param phoenixConnection * @throws Exception */ private void populateOrphanViewSet(PhoenixConnection phoenixConnection) throws Exception { ResultSet viewRS = phoenixConnection.createStatement().executeQuery(viewQuery); while (viewRS.next()) { String tenantId = viewRS.getString(1); String schemaName = viewRS.getString(2); String tableName = viewRS.getString(3); Key key = new Key(tenantId, schemaName, tableName); View view = new View(key); orphanViewSet.put(key, view); } }
/** * Go through all the tables in the system catalog table and update baseSet * @param phoenixConnection * @throws Exception */ private void populateBaseSet(PhoenixConnection phoenixConnection) throws Exception { ResultSet baseTableRS = phoenixConnection.createStatement().executeQuery(candidateBaseTableQuery); while (baseTableRS.next()) { String tenantId = baseTableRS.getString(1); String schemaName = baseTableRS.getString(2); String tableName = baseTableRS.getString(3); Key key = new Key(tenantId, schemaName, tableName); Base base = new Base(key); baseSet.put(key, base); } }
protected static PhoenixConnection addMetaDataColumn(PhoenixConnection conn, long scn, String columnDef) throws SQLException { PhoenixConnection metaConnection = null; Statement stmt = null; try { metaConnection = new PhoenixConnection(conn, scn); try { stmt = metaConnection.createStatement(); stmt.executeUpdate("ALTER TABLE SYSTEM.\"TABLE\" ADD IF NOT EXISTS " + columnDef); return metaConnection; } finally { if(stmt != null) { stmt.close(); } } } finally { if(metaConnection != null) { metaConnection.close(); } } }
@Override public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException { StringBuilder buf = new StringBuilder("select distinct \n" + TABLE_SCHEM + "," + TENANT_ID + " " + TABLE_CATALOG + " from " + SYSTEM_CATALOG + " " + SYSTEM_CATALOG_ALIAS + " where " + COLUMN_NAME + " is null"); addTenantIdFilter(buf, catalog); if (schemaPattern != null) { buf.append(" and " + TABLE_SCHEM + " like '" + StringUtil.escapeStringConstant(schemaPattern) + "'"); } if (SchemaUtil.isNamespaceMappingEnabled(null, connection.getQueryServices().getProps())) { buf.append(" and " + TABLE_NAME + " = '" + MetaDataClient.EMPTY_TABLE + "'"); } // TODO: we should union this with SYSTEM.SEQUENCE too, but we only have support for // UNION ALL and we really need UNION so that it dedups. Statement stmt = connection.createStatement(); return stmt.executeQuery(buf.toString()); }
@Test public void testViewInvalidation() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); PhoenixConnection conn = DriverManager.getConnection(getUrl(), props).unwrap(PhoenixConnection.class); String ct = "CREATE TABLE s1.t (k1 INTEGER NOT NULL, k2 VARCHAR, v VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2))"; conn.createStatement().execute(ct); conn.createStatement().execute("CREATE VIEW s2.v3 AS SELECT * FROM s1.t WHERE v = 'bar'"); // TODO: should it be an error to remove columns from a VIEW that we're defined there? conn.createStatement().execute("ALTER VIEW s2.v3 DROP COLUMN v"); try { conn.createStatement().executeQuery("SELECT * FROM s2.v3"); fail(); } catch (ColumnNotFoundException e) { } // No error, as v still exists in t conn.createStatement().execute("CREATE VIEW s2.v4 AS SELECT * FROM s1.t WHERE v = 'bas'"); // No error, even though view is invalid conn.createStatement().execute("DROP VIEW s2.v3"); }
@After public void cleanup() throws SQLException { PhoenixConnection pconn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); pconn.createStatement().execute("DROP TABLE T"); }
@Test public void testInvalidUpsertSelect() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); PhoenixConnection conn = DriverManager.getConnection(getUrl(), props).unwrap(PhoenixConnection.class); conn.createStatement().execute("CREATE TABLE t1 (k1 INTEGER NOT NULL, k2 VARCHAR, v VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2))"); conn.createStatement().execute("CREATE TABLE t2 (k3 INTEGER NOT NULL, v VARCHAR, CONSTRAINT pk PRIMARY KEY (k3))"); conn.createStatement().execute("CREATE VIEW v1 AS SELECT * FROM t1 WHERE k1 = 1"); try { conn.createStatement().executeUpdate("UPSERT INTO v1 SELECT k3,'foo',v FROM t2"); fail(); } catch (SQLException e) { assertEquals(SQLExceptionCode.CANNOT_UPDATE_VIEW_COLUMN.getErrorCode(), e.getErrorCode()); } } }
private static void updateIndexesSequenceIfPresent(PhoenixConnection connection, PTable dataTable) throws SQLException { PName tenantId = connection.getTenantId(); PName physicalName = dataTable.getPhysicalName(); PName oldPhysicalName = PNameFactory.newName( physicalName.toString().replace(QueryConstants.NAMESPACE_SEPARATOR, QueryConstants.NAME_SEPARATOR)); String oldSchemaName = MetaDataUtil.getViewIndexSequenceSchemaName(oldPhysicalName, false); String newSchemaName = MetaDataUtil.getViewIndexSequenceSchemaName(physicalName, true); String newSequenceName = MetaDataUtil.getViewIndexSequenceName(physicalName, tenantId, true); // create new entry with new schema format String upsert = "UPSERT INTO " + PhoenixDatabaseMetaData.SYSTEM_SEQUENCE + " SELECT REGEXP_SPLIT(" + PhoenixDatabaseMetaData.SEQUENCE_NAME + ",'_')[3] ,\'" + newSchemaName + "\',\'" + newSequenceName + "\'," + START_WITH + "," + CURRENT_VALUE + "," + INCREMENT_BY + "," + CACHE_SIZE + "," + MIN_VALUE + "," + MAX_VALUE + "," + CYCLE_FLAG + "," + LIMIT_REACHED_FLAG + " FROM " + PhoenixDatabaseMetaData.SYSTEM_SEQUENCE + " WHERE " + PhoenixDatabaseMetaData.TENANT_ID + " IS NULL AND " + PhoenixDatabaseMetaData.SEQUENCE_SCHEMA + " = '" + oldSchemaName + "'"; connection.createStatement().executeUpdate(upsert); // delete old sequence MetaDataUtil.deleteViewIndexSequences(connection, oldPhysicalName, false); }
private void forcefullyDropView(PhoenixConnection phoenixConnection, Key key) throws Exception { String deleteRowsFromCatalog = "DELETE FROM " + SYSTEM_CATALOG_NAME + " WHERE " + TENANT_ID + (key.getTenantId() == null ? " IS NULL" : " = '" + key.getTenantId() + "'") + " AND " + TABLE_SCHEM + (key.getSchemaName() == null ? " IS NULL " : " = '" + key.getSchemaName() + "'") + " AND " + TABLE_NAME + " = '" + key.getTableName() + "'"; String deleteRowsFromChildLink = "DELETE FROM " + SYSTEM_CHILD_LINK_NAME + " WHERE " + COLUMN_NAME + (key.getTenantId() == null ? " IS NULL" : " = '" + key.getTenantId() + "'") + " AND " + COLUMN_FAMILY + " = '" + (key.getSchemaName() == null ? key.getTableName() : key.getSchemaName() + "." + key.getTableName()) + "'"; try { phoenixConnection.createStatement().execute(deleteRowsFromCatalog); phoenixConnection.createStatement().execute(deleteRowsFromChildLink); phoenixConnection.commit(); } catch (SQLException e) { throw new IOException(e); } }
@Test public void testCreateTableWithDuplicateColumns() throws SQLException { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); PhoenixConnection conn = DriverManager.getConnection(getUrl(), props).unwrap(PhoenixConnection.class); String ddl = "CREATE TABLE T (ID INTEGER PRIMARY KEY, DUPE INTEGER, DUPE INTEGER)"; try { conn.createStatement().execute(ddl); fail(); } catch (ColumnAlreadyExistsException e) { assertEquals("DUPE", e.getColumnName()); } } }
private void removeLink(PhoenixConnection phoenixConnection, Key src, Key dst, PTable.LinkType linkType) throws Exception { String deleteQuery = "DELETE FROM " + ((linkType == PTable.LinkType.PHYSICAL_TABLE || linkType == PTable.LinkType.PARENT_TABLE) ? SYSTEM_CATALOG_NAME : SYSTEM_CHILD_LINK_NAME) + " WHERE " + TENANT_ID + (src.getTenantId() == null ? " IS NULL" : " = '" + src.getTenantId() + "'") + " AND " + TABLE_SCHEM + (src.getSchemaName() == null ? " IS NULL " : " = '" + src.getSchemaName() + "'") + " AND " + TABLE_NAME + " = '" + src.getTableName() + "' AND " + COLUMN_NAME + (dst.getTenantId() == null ? " IS NULL" : " = '" + dst.getTenantId() + "'") + " AND " + COLUMN_FAMILY + " = '" + (dst.getSchemaName() == null ? dst.getTableName() : dst.getSchemaName() + "." + dst.getTableName()) + "'"; phoenixConnection.createStatement().execute(deleteQuery); phoenixConnection.commit(); }
public KeyRangeClipTest(String tableDef, List<Object> startValues, List<Object> endValues, int clipTo, KeyRange expectedOutput) throws SQLException { PhoenixConnection pconn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); pconn.createStatement().execute("CREATE TABLE T(" + tableDef+ ")"); PTable table = pconn.getMetaDataCache().getTableRef(new PTableKey(null,"T")).getTable(); this.schema = table.getRowKeySchema(); byte[] lowerRange = getRange(pconn, startValues); byte[] upperRange = getRange(pconn, endValues); this.input = KeyRange.getKeyRange(lowerRange, upperRange); this.expectedOutput = expectedOutput; this.clipTo = clipTo; }
@Test public void testAndPKAndNotPK() throws SQLException { String query = "select * from bugTable where ID = 'i2' and company = 'c3'"; PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); pconn.createStatement().execute("create table bugTable(ID varchar primary key,company varchar)"); PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); PColumn column = plan.getTableRef().getTable().getColumnForColumnName("COMPANY"); assertEquals( singleKVFilter(constantComparison( CompareOp.EQUAL, new KeyValueColumnExpression(column), "c3")), filter); }
@Test public void testSingleFixedFullPkSalted() throws SQLException { PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); pconn.createStatement().execute("CREATE TABLE t (k bigint not null primary key, v varchar) SALT_BUCKETS=20"); String query = "select * from t where k=" + 1; PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertNull(filter); byte[] key = new byte[PLong.INSTANCE.getByteSize() + 1]; PLong.INSTANCE.toBytes(1L, key, 1); key[0] = SaltingUtil.getSaltingByte(key, 1, PLong.INSTANCE.getByteSize(), 20); byte[] expectedStartKey = key; byte[] expectedEndKey = ByteUtil.nextKey(key); byte[] startKey = scan.getStartRow(); byte[] stopKey = scan.getStopRow(); assertArrayEquals(expectedStartKey, startKey); assertArrayEquals(expectedEndKey, stopKey); }
@Test public void testSingleVariableFullPkSalted() throws SQLException { PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class); pconn.createStatement().execute("CREATE TABLE t (k varchar primary key, v varchar) SALT_BUCKETS=20"); String query = "select * from t where k='a'"; PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query); QueryPlan plan = pstmt.optimizeQuery(); Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); assertNull(filter); byte[] key = new byte[2]; PVarchar.INSTANCE.toBytes("a", key, 1); key[0] = SaltingUtil.getSaltingByte(key, 1, 1, 20); byte[] expectedStartKey = key; byte[] expectedEndKey = ByteUtil.nextKey(ByteUtil.concat(key, QueryConstants.SEPARATOR_BYTE_ARRAY)); byte[] startKey = scan.getStartRow(); byte[] stopKey = scan.getStopRow(); assertTrue(Bytes.compareTo(expectedStartKey, startKey) == 0); assertTrue(Bytes.compareTo(expectedEndKey, stopKey) == 0); }
private static JoinTable getJoinTable(String query, PhoenixConnection connection) throws SQLException { SQLParser parser = new SQLParser(query); SelectStatement select = SubselectRewriter.flatten(parser.parseQuery(), connection); ColumnResolver resolver = FromCompiler.getResolverForQuery(select, connection); select = StatementNormalizer.normalize(select, resolver); SelectStatement transformedSelect = SubqueryRewriter.transform(select, resolver, connection); if (transformedSelect != select) { resolver = FromCompiler.getResolverForQuery(transformedSelect, connection); select = StatementNormalizer.normalize(transformedSelect, resolver); } PhoenixStatement stmt = connection.createStatement().unwrap(PhoenixStatement.class); return JoinCompiler.compile(stmt, select, resolver); } }