[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:
-----------------------------------
{quote}
I'm saying that as long as the source query has the direct projection of a geometry value, then we'll easily be able to associate the srid - but for a query with functions creating geometry values or inline views (although the logic to trace the value would be easy in the engine) would be more difficult to associate the srid.
{quote}
Ahhh, right; I can see now how that could get complicated. I think it would simplify things if we didn't allow such functions to be pushed down. I've only looked at conversion functions and filters so far, and this doesn't seem like it would be a major limitation in those cases at least.
Let's talk about plan b:
{quote}
For mysql we could use their internal format, for pg we could use ewkb, and oracle could use ewkt.
{quote}
I think we should emulate MySQL internal format for all. Another option would be to make something similar to EWKT by simply doing a concat of the SRID and WKT strings. In either case, we would read the SRID off the beginning of the result and then set it on the geometry type.
Couple of notes...
* We might want to be careful using MySQL internal format because I cannot find any documentation specifying it that it is what it appears to be.
* WKB-based format is preferable IMO because it's more compact.
* Postgres is the only database that supports the "extended" formats; Oracle does not support EWKT natively. JTS IO classes do not fully support the "extended" formats.
> 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-2384) Managing Spatial Data Types
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2384?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2384:
---------------------------------------
Another thing that needs addressed is that I think the geometry type should be non-comparable (which matches oracle). For mysql/postgres the ordering rules seem different and based more upon the internal storage format. So we should add an explicit st_equals in line with the spec.
> Managing Spatial Data Types
> ---------------------------
>
> Key: TEIID-2384
> URL: https://issues.jboss.org/browse/TEIID-2384
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: luca gioppo
> Assignee: Steven Hawkins
> Labels: spatial, types
> Fix For: 8.10
>
>
> It would be useful to be able to consume data from spatial database exposing the VDB as a spatial database to other application (imagine geoserver).
> TEIID could be strategic for merging georeferenced data and make it available to those systems.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3323) Add support for Vertica as as data source
by Van Halbert (JIRA)
Van Halbert created TEIID-3323:
----------------------------------
Summary: Add support for Vertica as as data source
Key: TEIID-3323
URL: https://issues.jboss.org/browse/TEIID-3323
Project: Teiid
Issue Type: Feature Request
Components: Misc. Connectors
Affects Versions: 8.11
Reporter: Van Halbert
Assignee: Steven Hawkins
Add support for Vertica as as data source
It supports standard programming interfaces ODBC, JDBC, and ADO.NET.
--
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:
---------------------------------------
> Could you please give more details on which collation to use for the connection?
What is the encoding/collation for incidentsm1_INCIDENT_ID, what client version are you using, and what do you see when checking the various character settings? See the output from "show variables like 'char%';" - http://stackoverflow.com/questions/18058297/jdbc-url-for-mysql-configurat...
> 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
[JBoss JIRA] (TEIID-3290) Auto generated REST WAR truncates the larger payloads
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3290?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3290:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1185024|https://bugzilla.redhat.com/show_bug.cgi?id=1185024] from MODIFIED to ON_QA
> Auto generated REST WAR truncates the larger payloads
> -----------------------------------------------------
>
> Key: TEIID-3290
> URL: https://issues.jboss.org/browse/TEIID-3290
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.4
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Priority: Blocker
> Labels: Beta2
> Fix For: 8.7.1, 8.10
>
>
> Currently only query parameters are supported as the input parameters to the Teiid procedure in POST based call.
> The POST BODY also needs to be supported to handle larger payloads in this situation as query parameters has size limitations. Currently string based query parameters are supported, but since they passed as is as strings on larger payloads the they will be subject 4000 string length restriction.
> It should be possible to read metadata of the procedure and convert the string objects to respective objects before the query gets executed.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 4 months
[JBoss JIRA] (TEIID-3301) Add GEOMETRY_COLUMNS view
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3301?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3301:
---------------------------------------
> What is the concern here?
I'm saying that as long as the source query has the direct projection of a geometry value, then we'll easily be able to associate the srid - but for a query with functions creating geometry values or inline views (although the logic to trace the value would be easy in the engine) would be more difficult to associate the srid.
> 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-3319) Union with different datatypes gives error in mysql
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3319?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3319:
----------------------------------
>A couple of resolutions would be to change the collation in use for your connection
Could you please give more details on which collation to use for the connection?
I tried by adding to mysql datasource url : connectionCollation=utf8_general_ci&sessionVariables=sql_mode=ANSI
But still having the same problem.
For the other resolutions, is it possible to have a small example on how to implement them.
Thanks
> 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