[JBoss JIRA] (TEIID-3301) Add GEOMETRY_COLUMNS view
by Tom Arnold (JIRA)
[ https://issues.jboss.org/browse/TEIID-3301?page=com.atlassian.jira.plugin... ]
Tom Arnold edited comment on TEIID-3301 at 2/5/15 9:07 PM:
-----------------------------------------------------------
Could we do blob concatenation in all cases to return SRID 32bit int + WKB? I've only tried it on MySQL so far but it seems like it should work on the others as well. For example:
{code}
select shape from cola_markets;
select ST_AsTeiidFormat(shape) from cola_markets;
select CONCAT(RPAD(REVERSE(CHAR(SRID(shape))), 4, CHAR(0)), AsWKB(shape)) from cola_markets;
{code}
{quote}
It does directly support the srid / three dimensional aspects of ewkb, which would be useful for reading values from postgis.
{quote}
I've been intentionally avoiding 3d so far because it seems to be poorly supported. For example, newer versions of WKT support 3d in a way that is incompatible with EWKT. JTS IO classes support 3d but last time I played with them they would drop the Z coordinate when writing WKT.
GML might be an option if we want to try to support 3d on Oracle, but I think WKT/WKB are off the table unless we want to supply custom PL/SQL implementations; Oracle only implemented the original version of the OGC spec which did not cover 3d. :-)
was (Author: tom9729):
Could we do blob concatenation in all cases to return SRID 32bit int + WKB? I've only tried it on MySQL so far but it seems like it should work on the others as well. For example:
{code}
select shape from cola_markets;
select ST_AsTeiidFormat(shape) from cola_markets;
select CONCAT(RPAD(REVERSE(CHAR(SRID(shape))), 4, CHAR(0)), AsWKB(shape)) from cola_markets;
{code}
{quote}
It does directly support the srid / three dimensional aspects of ewkb, which would be useful for reading values from postgis.
{quote}
I've been intentionally avoiding 3d so far because it seems to be poorly supported. For example, newer versions of WKT support 3d in a way that is incompatible with EWKT. JTS IO classes support 3d but last time I played with them they would drop the Z coordinate when writing WKT.
> Add GEOMETRY_COLUMNS view
> -------------------------
>
> Key: TEIID-3301
> URL: https://issues.jboss.org/browse/TEIID-3301
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: spatial
> Fix For: 8.10
>
>
> Another OGC Simple Features implementation detail. Basically this is like a specialized version of SYS.COLUMNS, and would be used to support GIS software (like GeoServer).
> http://www.carocoops.org/resources/doc/postgis/x321.html
> {code}
> CREATE TABLE GEOMETRY_COLUMNS (
> F_TABLE_CATALOG VARCHAR(256) NOT NULL,
> F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
> F_TABLE_NAME VARCHAR(256) NOT NULL,
> F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
> COORD_DIMENSION INTEGER NOT NULL,
> SRID INTEGER NOT NULL,
> TYPE VARCHAR(30) NOT NULL
> )
> {code}
> I think we could implement this by storing SRID & friends as column properties, and then creating a view on SYS.COLUMNS & SYS.PROPERTIES.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3301) Add GEOMETRY_COLUMNS view
by Tom Arnold (JIRA)
[ https://issues.jboss.org/browse/TEIID-3301?page=com.atlassian.jira.plugin... ]
Tom Arnold commented on TEIID-3301:
-----------------------------------
Could we do blob concatenation in all cases to return SRID 32bit int + WKB? I've only tried it on MySQL so far but it seems like it should work on the others as well. For example:
{code}
select shape from cola_markets;
select ST_AsTeiidFormat(shape) from cola_markets;
select CONCAT(RPAD(REVERSE(CHAR(SRID(shape))), 4, CHAR(0)), AsWKB(shape)) from cola_markets;
{code}
{quote}
It does directly support the srid / three dimensional aspects of ewkb, which would be useful for reading values from postgis.
{quote}
I've been intentionally avoiding 3d so far because it seems to be poorly supported. For example, newer versions of WKT support 3d in a way that is incompatible with EWKT. JTS IO classes support 3d but last time I played with them they would drop the Z coordinate when writing WKT.
> Add GEOMETRY_COLUMNS view
> -------------------------
>
> Key: TEIID-3301
> URL: https://issues.jboss.org/browse/TEIID-3301
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: spatial
> Fix For: 8.10
>
>
> Another OGC Simple Features implementation detail. Basically this is like a specialized version of SYS.COLUMNS, and would be used to support GIS software (like GeoServer).
> http://www.carocoops.org/resources/doc/postgis/x321.html
> {code}
> CREATE TABLE GEOMETRY_COLUMNS (
> F_TABLE_CATALOG VARCHAR(256) NOT NULL,
> F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
> F_TABLE_NAME VARCHAR(256) NOT NULL,
> F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
> COORD_DIMENSION INTEGER NOT NULL,
> SRID INTEGER NOT NULL,
> TYPE VARCHAR(30) NOT NULL
> )
> {code}
> I think we could implement this by storing SRID & friends as column properties, and then creating a view on SYS.COLUMNS & SYS.PROPERTIES.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-2875) Metadata failed to load. No column found
by Camilo Ortiz (JIRA)
[ https://issues.jboss.org/browse/TEIID-2875?page=com.atlassian.jira.plugin... ]
Camilo Ortiz commented on TEIID-2875:
-------------------------------------
Hi,
I believe I'm having a similar problem. Deploying a VDB that has only one Oracle DB as a datasource I obtain the following output after the VDB tries to load:
12:28:36,961 WARN [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID50036 VDB testOracle.1 model "BKPTest" metadata failed to load. Reason:TEIID60011 No column found with name NESTED_TABLE_ID: org.teiid.metadata.MetadataException: TEIID60011 No column found with name NESTED_TABLE_ID
at org.teiid.metadata.MetadataFactory.assignColumn(MetadataFactory.java:353) [teiid-api-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.metadata.MetadataFactory.assignColumns(MetadataFactory.java:461) [teiid-api-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.metadata.MetadataFactory.addPrimaryKey(MetadataFactory.java:261) [teiid-api-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.translator.jdbc.JDBCMetdataProcessor.getPrimaryKeys(JDBCMetdataProcessor.java:463)
at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:147)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:309)
at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:57)
at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83) [teiid-engine-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60) [teiid-engine-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.jboss.VDBService$6.run(VDBService.java:396) [teiid-jboss-integration-8.9.0.Final.jar:8.9.0.Final]
at org.teiid.jboss.VDBService$7.run(VDBService.java:444) [teiid-jboss-integration-8.9.0.Final.jar:8.9.0.Final]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_10]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_10]
at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_10]
at org.jboss.threads.JBossThread.run(JBossThread.java:122)
I obtain the same error even trying some suggestions obtained at irc #teiid by setting several combinations of the following properties in the -vbd.xml file:
<property name="importer.tableTypes" value="TABLE"/>
<property name="importer.schemaPattern" value="my-schema"/>
<property name="importer.importApproximateIndexes" value="false"/>
Moreover, setting
<property name="importer.tableNamePattern" value="my-table"/>
with a table that definitely has no hidden columns gives the same error as above. Do you have any suggestions?
Thanks!
> Metadata failed to load. No column found
> ----------------------------------------
>
> Key: TEIID-2875
> URL: https://issues.jboss.org/browse/TEIID-2875
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.6
> Environment: Windows server 2003, Windows 7.
> Reporter: SHI HONG CHIN
> Assignee: Steven Hawkins
> Attachments: JakkerData-vdb.xml
>
>
> Affected database: Oracle
> The following error displayed when Teiid loading a dynamic VDB:
> 10:21:41,458 WARN [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID50036 VDB JakkerData.1 model "IWMS" metadata failed to load. Reason:TEIID60011 No column found with name "RATE"."CHARGE_NAME"
> 10:23:29,994 WARN [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID50036 VDB JakkerData.1 model "IWMS" metadata failed to load. Reason:TEIID60011 No column found with name "RATE"."CHARGE_NAME"
> I am not the one who design the database. But, I cannot find any table with above mentioned column name in Oracle database. So, I am not sure whether the above mentioned column name is a hidden column or not.
> This bug does not exist in version 8.5.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3254) Add supporting capabilities
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3254?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3254:
---------------------------------------
We can probably safely ignore the like restriction for now and address it at a later time.
It is fairly straight-forward to add a restriction for update to use only literal values - but that requires a compensating procedure that will only work if a primary key is defined. However a spreadsheet will typically not have a key, so it may be moot to work this issue.
> Add supporting capabilities
> ---------------------------
>
> Key: TEIID-3254
> URL: https://issues.jboss.org/browse/TEIID-3254
> Project: Teiid
> Issue Type: Sub-task
> Components: Connector API, Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> With spreadsheet queries LIKE is not supported in UPDATE/DELETE and only simple values are supported in the update change set.
> We should add capabilities to account for these restrictions.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3317) MongoDB: group by doesn't work with MongoDB 2.6.3
by Ivan Chan (JIRA)
[ https://issues.jboss.org/browse/TEIID-3317?page=com.atlassian.jira.plugin... ]
Ivan Chan commented on TEIID-3317:
----------------------------------
It will be awesome if you guys can fix this issue in your next release. Thanks a lot.
> MongoDB: group by doesn't work with MongoDB 2.6.3
> --------------------------------------------------
>
> Key: TEIID-3317
> URL: https://issues.jboss.org/browse/TEIID-3317
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.9.1
> Environment: Teiid 8.9.1 with MongoDB version: 2.6.3 using MongoDB Driver mongo-java-driver-2.12.3.jar
> Reporter: Ivan Chan
> Assignee: Ramesh Reddy
> Labels: Teiid
>
> Running group by sql through exception:
> select "FirstName"
> from "MongoDB_Array_263"."TeiidArray"
> group by "FirstName"
> order by "FirstName"
> limit 1000
> Exception:
> Caused by: java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
> at java.util.ArrayList.rangeCheck(ArrayList.java:604)
> at java.util.ArrayList.get(ArrayList.java:382)
> at org.teiid.translator.mongodb.MongoDBQueryExecution.next(MongoDBQueryExecution.java:132)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:386)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:204)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
> at $Proxy182.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
> at java.util.concurrent.FutureTask.run(FutureTask.java:166)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3157) Allow streaming without an intermediate copy
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3157?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3157:
----------------------------------
Fix Version/s: 8.11
This is a fairly significant undertaking because it will require a new set of streaming logic from the client, initiated by the server - and will have to be utilized as a pull exactly when the lob is being sent to another source.
We'll investigate more for 8.11
> Allow streaming without an intermediate copy
> --------------------------------------------
>
> Key: TEIID-3157
> URL: https://issues.jboss.org/browse/TEIID-3157
> Project: Teiid
> Issue Type: Feature Request
> Components: JDBC Driver, Server
> Reporter: Haifen Bi
> Assignee: Steven Hawkins
> Fix For: 8.11
>
>
> With TEIID-3153 there are scenarios that require streaming larger than 4 gigs of data. We should also provide a mechanism for on demand streaming from the client to accommodate even larger streaming scenarios to eliminate the need for an intermediate copy.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3319) Union with different datatypes gives error in mysql
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3319:
---------------------------------------
> you mean that teiid won't remove the cast on the field of type string?
The engine will insert the conversion from int to string so that the types match for the union. It will remove a conversion from string to string as that is seen as unnecessary. What I'm saying is that mysql seems to handle the union of int and a character string just fine without the explicit conversion, so we could just have the translator remove the explicit conversion inserted by the engine. This would be a code change, not something that would be directly in the user query.
> Union with different datatypes gives error in mysql
> ---------------------------------------------------
>
> Key: TEIID-3319
> URL: https://issues.jboss.org/browse/TEIID-3319
> Project: Teiid
> Issue Type: Bug
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
>
> Hello,
> I'm using Teiid 8.9.0 Alpha2 with mysql5.7 and mysql5 translator.
> I have a union query with fields of different types, int and varchar.
> Teiid is adding dynamically the cast as char to the int field but the jdbc query is giving an error on mysql level (Illegal mix of collations for operation 'UNION')
> I edited the generated jdbc query and added cast as char to both fields int and varchar, mysql executes the jdbc query without a problem.
> I added convert to String to both union fields in Teiid sql query, but the jdbc query generated by teiid is not adding the cast to the string field.
> Teiid sql query:
> select "ITRITPTicketsView"."ITRITPTicketsView_jiraissue_" as "ITRITPTicketsView_jiraissue_"
> from ((select convert("ITRITPTicketsView_sub"."jiraissue_ID", string) as "ITRITPTicketsView_jiraissue_" from "implifyBusinessModel"."ITRITPTicketsView" "ITRITPTicketsView_sub")
> union
> (select convert("SDView_sub"."incidentsm1_INCIDENT_ID", string) as "SDView_incidentsm1_INCIDENT_" from "implifyBusinessModel"."SDView" "SDView_sub")
> ) "ITRITPTicketsView"
> LIMIT 0 , 10
> The generated error:
> org.teiid.translator.jdbc.JDBCExecutionException: 1271 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: (SELECT cast(g_1.`jiraissue_ID` AS char) AS c_0 FROM `implify_view`.`ITRITPTicketsView` AS g_1) UNION (SELECT g_0.`incidentsm1_INCIDENT_ID` AS c_0 FROM `implify_view`.`SDView` AS g_0) LIMIT 10]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:151)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:744)
> Caused by: java.sql.SQLException: Illegal mix of collations for operation 'UNION'
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:141)
> ... 12 more
> Feb 03, 2015 8:43:32 AM org.teiid.logging.JavaLogger log
> WARNING: TEIID30020 Processing exception for request AxQ9XTXrAYkm.0 'TEIID30504 implify_viewModelVDB: 1271 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: (SELECT cast(g_1.`jiraissue_ID` AS char) AS c_0 FROM `implify_view`.`ITRITPTicketsView` AS g_1) UNION (SELECT g_0.`incidentsm1_INCIDENT_ID` AS c_0 FROM `implify_view`.`SDView` AS g_0) LIMIT 10]'. Originally TeiidProcessingException 'Illegal mix of collations for operation 'UNION'' SQLError.java:1084. Enable more detailed logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months