[Geoserver-devel] Switching GeoServer to ECQL?

GeoServer GeoServer Developer
1

Hi,
ECQL has been around for a while and we failed to
use it in 2.0.x so far.

I've checked, it seems all the changes required to
switch from CQL to ECQL in GeoServer boil down to
changing CQL -> ECQL in two files (see attached patch).
The assumption is, ECQL is a clean extension so there
is no need for the user to know that we actually
switched parsers, we can keep on having users hit
CQL_FILTER=.... (we just have to document the extra
capabilities starting from version x.y.z).

The build after that change works fine. I would
certainly go for the switch on trunk, what about
2.0.x as well?

Wondering if ECQL has been tested enough for a stable
series. Is uDig using it already?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

2

It would be very nice to switch to ECQL … also wondering if it’s stable enough or not at this time.

Eng. Alessio Fabiani
Founder / CTO GeoSolutions S.A.S.
Via Carignoni 51
55041 Camaiore (LU)
Italy

phone: +39 0584 983027
fax: +39 0584 983027
mob: +39 349 8227000

http://www.geo-solutions.it
http://geo-solutions.blogspot.com

On Wed, May 26, 2010 at 4:21 PM, Andrea Aime <aaime@anonymised.com1…> wrote:

Hi,
ECQL has been around for a while and we failed to
use it in 2.0.x so far.

I’ve checked, it seems all the changes required to
switch from CQL to ECQL in GeoServer boil down to
changing CQL → ECQL in two files (see attached patch).
The assumption is, ECQL is a clean extension so there
is no need for the user to know that we actually
switched parsers, we can keep on having users hit
CQL_FILTER=… (we just have to document the extra
capabilities starting from version x.y.z).

The build after that change works fine. I would
certainly go for the switch on trunk, what about
2.0.x as well?

Wondering if ECQL has been tested enough for a stable
series. Is uDig using it already?

Cheers
Andrea

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

3

Makes sense. As I understand it CQL is a subset of ECQL correct? So nothing "protocol" wise has to change?

On 10-05-26 8:21 AM, Andrea Aime wrote:

Hi,
ECQL has been around for a while and we failed to
use it in 2.0.x so far.

I've checked, it seems all the changes required to
switch from CQL to ECQL in GeoServer boil down to
changing CQL -> ECQL in two files (see attached patch).
The assumption is, ECQL is a clean extension so there
is no need for the user to know that we actually
switched parsers, we can keep on having users hit
CQL_FILTER=.... (we just have to document the extra
capabilities starting from version x.y.z).

The build after that change works fine. I would
certainly go for the switch on trunk, what about
2.0.x as well?

Wondering if ECQL has been tested enough for a stable
series. Is uDig using it already?

Cheers
Andrea

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

4

Justin Deoliveira ha scritto:

Makes sense. As I understand it CQL is a subset of ECQL correct? So nothing "protocol" wise has to change?

That is my understanding as well. Here:
http://docs.codehaus.org/display/GEOTOOLS/ECQL+Parser+Design

Btw, as far as stability goes it seems ECQL has the same level
of unit testing as CQL. Just not sure about real world testing.

Mauricio, has ECQL been used in real world applications for a while?
I sure don't see many changes in the EQL code in the last year,
which might be a either sign of very good stability, or that not many
people are using it :wink:

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

5

Andrea Aime ha scritto:

Mauricio, has ECQL been used in real world applications for a while?
I sure don't see many changes in the EQL code in the last year,
which might be a either sign of very good stability, or that not many
people are using it :wink:

Btw, for what is worth I've just tried a few ECQL queries with IN (...),
expressions on both side of comparisons, FID queries and stuff
that we cannot even express in ogc filter like:

ID IN ('states.1','states.2') and STATE_NAME like 'I%'

and they all worked fine. Only concern is that ID might conflict with
an existing field.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

6

On Wednesday 26 May 2010 05:21:27 pm Andrea Aime wrote:

Justin Deoliveira ha scritto:
> Makes sense. As I understand it CQL is a subset of ECQL correct? So
> nothing "protocol" wise has to change?

That is my understanding as well. Here:
http://docs.codehaus.org/display/GEOTOOLS/ECQL+Parser+Design

Btw, as far as stability goes it seems ECQL has the same level
of unit testing as CQL. Just not sure about real world testing.

Mauricio, has ECQL been used in real world applications for a while?

I can not reply seriously this question. Jody was very interested to do this
extension, maybe he know or can mention some real applications.

I sure don't see many changes in the EQL code in the last year,
which might be a either sign of very good stability, or that not many
people are using it :wink:

:D. Well, my personal experience say me that if a product do not work
correctly you will see a storm of mails, telephone calls, etc. The sea looks
fine around CQL.

Analysing the constructive strategy, ECQL is reusing the CQL code, I want to
say that the error probability in ECQL is very low.

Cheers
Andrea

cheers
--
Mauricio Pazos
www.axios.es

7

I use ECQL in uDig and have had no troubles; Gabriel has an open bug about the use of the "IN" keyword to check feature id. I would like to see that resolved as it is the only thing outstanding likely to result in a grammar change.

Jody

On 27/05/2010, at 1:21 AM, Andrea Aime wrote:

Justin Deoliveira ha scritto:

Makes sense. As I understand it CQL is a subset of ECQL correct? So
nothing "protocol" wise has to change?

That is my understanding as well. Here:
http://docs.codehaus.org/display/GEOTOOLS/ECQL+Parser+Design

Btw, as far as stability goes it seems ECQL has the same level
of unit testing as CQL. Just not sure about real world testing.

Mauricio, has ECQL been used in real world applications for a while?
I sure don't see many changes in the EQL code in the last year,
which might be a either sign of very good stability, or that not many
people are using it :wink:

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------

_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

8

On 27/05/2010, at 4:38 AM, Mauricio Pazos wrote:

Mauricio, has ECQL been used in real world applications for a while?

I can not reply seriously this question. Jody was very interested to do this
extension, maybe he know or can mention some real applications.

I was interested in it ... because of Andrea. Andrea would not let me delete the horrible ExpressionBuilder parser until CQL took on the extra functionality of feature id filters :slight_smile:

I have been using ECQL without incident.

D. Well, my personal experience say me that if a product do not work
correctly you will see a storm of mails, telephone calls, etc. The sea looks
fine around CQL.

Analysing the constructive strategy, ECQL is reusing the CQL code, I want to
say that the error probability in ECQL is very low.

I agree nice work,
Jody

9

On Wednesday 26 May 2010 06:04:28 pm Andrea Aime wrote:

ID IN ('states.1','states.2') and STATE_NAME like 'I%'

and they all worked fine. Only concern is that ID might conflict with
an existing field.

Let me open the debate in this list.

In general, you will have problem in ECQL/CQL if you use an language keyword
as field identifier.
By example, if you define a field named LIKE. You will have problem to query by
the LIKE field.

LIKE > 1 <<< is not a valid sentence.

In SQL, the way to save this problem is to define the identifier between double
quote. By example

CREATE TABLE test
(
  "LIKE" integer
)
Then you can ask for
"LIKE" > 1

Actually, there are two different symbols: "LIKE" and LIKE. So, in the SQL
language "LIKE" is an identifier and LIKE is the keyword.

So, how could be solved the problem, if someone wants to define an CQL/ECQL
keyword as property/attribute name in a Layer?

cheers

--
Mauricio Pazos
www.axios.es

10

Mauricio Pazos ha scritto:

On Wednesday 26 May 2010 06:04:28 pm Andrea Aime wrote:

ID IN ('states.1','states.2') and STATE_NAME like 'I%'

and they all worked fine. Only concern is that ID might conflict with
an existing field.

Let me open the debate in this list.

In general, you will have problem in ECQL/CQL if you use an language keyword as field identifier. By example, if you define a field named LIKE. You will have problem to query by the LIKE field.

LIKE > 1 <<< is not a valid sentence.

In SQL, the way to save this problem is to define the identifier between double quote. By example

CREATE TABLE test
(
  "LIKE" integer
)
Then you can ask for "LIKE" > 1

Actually, there are two different symbols: "LIKE" and LIKE. So, in the SQL language "LIKE" is an identifier and LIKE is the keyword.

What happens in ECQL in that respect? Is there a way to use a keyword
as an identifier?

So, how could be solved the problem, if someone wants to define an CQL/ECQL keyword as property/attribute name in a Layer?

I agree a language has keywords and there is nothing that can be done about it.

The problem here is that we're superimposing different sets of
keywords and rules on the user making it hard to setup a database table
that can be used in all contexts. The rule sets I have in mind here are:
- what is valid as a database column name
- what is valid a XML element (thinking WFS here)
- what is valid as ECQL

I don't have statistics at hand, but my guess is that ID is a very commonly used column name, especially for primary keys.
Primary keys can be exposed as read only attributes from JDBC data
stores so we're likely to face complaints once ECQL goes in common use.

Possible ways out:
- use an identifier that's very unlikely or downright wrong as an
   attribute name. Something like @id or ::id or id() (just making them
   up, did not check if I'm actually introducing other problems with
   any of them).
- turn that into a pseudo function call, something like
   id_in(id1, id2, ..., idn)
- have escapes to state ID is intended to be used as an attribute.
   "ID" would work I guess

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

11

On Thursday 27 May 2010 10:05:56 am Andrea Aime wrote:

> Actually, there are two different symbols: "LIKE" and LIKE. So, in the
> SQL language "LIKE" is an identifier and LIKE is the keyword.

What happens in ECQL in that respect? Is there a way to use a keyword
as an identifier?

No, it isn't. I think we could use double quote like SQL because CQL/ECQL are
sql like. But, I want to hear the geoserver community.

> So, how could be solved the problem, if someone wants to define an
> CQL/ECQL keyword as property/attribute name in a Layer?

I agree a language has keywords and there is nothing that can be done
about it.

The problem here is that we're superimposing different sets of
keywords and rules on the user making it hard to setup a database table
that can be used in all contexts. The rule sets I have in mind here are:
- what is valid as a database column name
- what is valid a XML element (thinking WFS here)
- what is valid as ECQL

I don't have statistics at hand, but my guess is that ID is a very
commonly used column name, especially for primary keys.
Primary keys can be exposed as read only attributes from JDBC data
stores so we're likely to face complaints once ECQL goes in common use.

Possible ways out:
- use an identifier that's very unlikely or downright wrong as an
   attribute name. Something like @id or ::id or id() (just making them
   up, did not check if I'm actually introducing other problems with
   any of them).
- turn that into a pseudo function call, something like
   id_in(id1, id2, ..., idn)
- have escapes to state ID is intended to be used as an attribute.
   "ID" would work I guess

I like this option, it matches with SQL convention (cql is sql like), and we
could apply the same criteria for all keyword.

ID is Keyword, "ID" is attribute
LIKE is Keyword, "LIKE" is attribute
BEFORE is keyword, "BEFORE" is attribute
...
etc.

Cheers
Andrea

cheers
--
Mauricio Pazos
www.axios.es

12

Mauricio Pazos ha scritto:

- have escapes to state ID is intended to be used as an attribute.
   "ID" would work I guess

I like this option, it matches with SQL convention (cql is sql like), and we could apply the same criteria for all keyword.

ID is Keyword, "ID" is attribute LIKE is Keyword, "LIKE" is attribute
BEFORE is keyword, "BEFORE" is attribute ...
etc.

Works for me :slight_smile:

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

13

On Thursday 27 May 2010 10:48:13 am Mauricio Pazos wrote:

> Possible ways out:
> - use an identifier that's very unlikely or downright wrong as an
> attribute name. Something like @id or ::id or id() (just making them
> up, did not check if I'm actually introducing other problems with
> any of them).
> - turn that into a pseudo function call, something like
> id_in(id1, id2, ..., idn)
> - have escapes to state ID is intended to be used as an attribute.
> "ID" would work I guess

I like this option, it matches with SQL convention (cql is sql like), and
we could apply the same criteria for all keyword.

ID is Keyword, "ID" is attribute

Sorry, this case do not work

LIKE is Keyword, "LIKE" is attribute
BEFORE is keyword, "BEFORE" is attribute

--
Mauricio Pazos
www.axios.es

14

On Thursday 27 May 2010 10:58:48 am Mauricio Pazos wrote:

On Thursday 27 May 2010 10:48:13 am Mauricio Pazos wrote:
> > Possible ways out:
> > - use an identifier that's very unlikely or downright wrong as an
> > attribute name. Something like @id or ::id or id() (just making them
> > up, did not check if I'm actually introducing other problems with
> > any of them).
> > - turn that into a pseudo function call, something like
> > id_in(id1, id2, ..., idn)

A sentence like this works and it has not conflicts with other sentence.
I must to check the ECQL grammar details.
Following this idea the options could be
id_in(id1, id2, ..., idn)
or
id(id1, id2, ..., idn)
or
in(id1, id2, ..., idn)
more?

> > - have escapes to state ID is intended to be used as an attribute.
> > "ID" would work I guess
>
> I like this option, it matches with SQL convention (cql is sql like), and
> we could apply the same criteria for all keyword.
>
> LIKE is Keyword, "LIKE" is attribute
> BEFORE is keyword, "BEFORE" is attribute

We could use this strategy to scape keyword.

in this scenario the ID not need to be a keyword in the ECQL grammar.
--
Mauricio Pazos
www.axios.es

15

Mauricio Pazos ha scritto:

On Thursday 27 May 2010 10:58:48 am Mauricio Pazos wrote:

On Thursday 27 May 2010 10:48:13 am Mauricio Pazos wrote:

Possible ways out:
- use an identifier that's very unlikely or downright wrong as an
   attribute name. Something like @id or ::id or id() (just making them
   up, did not check if I'm actually introducing other problems with
   any of them).
- turn that into a pseudo function call, something like
   id_in(id1, id2, ..., idn)

A sentence like this works and it has not conflicts with other sentence.
I must to check the ECQL grammar details.
Following this idea the options could be id_in(id1, id2, ..., idn)
or
id(id1, id2, ..., idn)
or
in(id1, id2, ..., idn)
more?

I'll let other people better than me at naming provide suggestions.
id_in(id1, ...) is fine by me.
The danger with using a function like syntax is to conflict with an
existing filter function, in particular, "id" is already taken,
see the filter capabilities section:

http://demo.opengeo.org/geoserver/ows?service=wfs&version=1.0.0&request=GetCapabilities

Alternatively we could use a syntax that does not look like a function call, something like id{id1, id2, ...} or id[id1, ..., idn]

Pity that "ID" cannot be used, it would have been backwards compatible (I think).

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

16

On Thursday 27 May 2010 11:53:52 am Andrea Aime wrote:

Mauricio Pazos ha scritto:
> On Thursday 27 May 2010 10:58:48 am Mauricio Pazos wrote:
>> On Thursday 27 May 2010 10:48:13 am Mauricio Pazos wrote:
>>>> Possible ways out:
>>>> - use an identifier that's very unlikely or downright wrong as an
>>>> attribute name. Something like @id or ::id or id() (just making
>>>> them up, did not check if I'm actually introducing other problems with
>>>> any of them).
>>>> - turn that into a pseudo function call, something like
>>>> id_in(id1, id2, ..., idn)
>
> A sentence like this works and it has not conflicts with other sentence.
> I must to check the ECQL grammar details.
> Following this idea the options could be
> id_in(id1, id2, ..., idn)
> or
> id(id1, id2, ..., idn)
> or
> in(id1, id2, ..., idn)
> more?

I'll let other people better than me at naming provide suggestions.
id_in(id1, ...) is fine by me.
The danger with using a function like syntax is to conflict with an
existing filter function, in particular, "id" is already taken,
see the filter capabilities section:

http://demo.opengeo.org/geoserver/ows?service=wfs&version=1.0.0&request=Get
Capabilities

understood

Alternatively we could use a syntax that does not look like a function
call, something like id{id1, id2, ...} or id[id1, ..., idn]

I agree, I will analyse this too

Pity that "ID" cannot be used, it would have been backwards compatible
(I think).

Yes, it could be a problem, I am thinking in this cases to query by fid
"ID" like "city.1"
"ID" = 1
ID in (...)
I think it could be confuse

Cheers
Andrea

--
Mauricio Pazos
www.axios.es

17

On 27/05/2010, at 6:55 PM, Andrea Aime wrote:

Mauricio Pazos ha scritto:

  • have escapes to state ID is intended to be used as an attribute.

“ID” would work I guess

I like this option, it matches with SQL convention (cql is sql like), and we could apply the same criteria for all keyword.

ID is Keyword, “ID” is attribute LIKE is Keyword, “LIKE” is attribute

BEFORE is keyword, “BEFORE” is attribute …

etc.

Works for me :slight_smile:

Hopefully we can still make that optional? So NAME is attribute and “NAME” is attribute.
Jody

18

On Friday 28 May 2010 04:08:10 am Jody Garnett wrote:

On 27/05/2010, at 6:55 PM, Andrea Aime wrote:
> Mauricio Pazos ha scritto:
>>> - have escapes to state ID is intended to be used as an attribute.
>>> "ID" would work I guess
>>
>> I like this option, it matches with SQL convention (cql is sql like),
>> and we could apply the same criteria for all keyword. ID is Keyword,
>> "ID" is attribute LIKE is Keyword, "LIKE" is attribute BEFORE is
>> keyword, "BEFORE" is attribute ...
>> etc.
>
> Works for me :slight_smile:

Hopefully we can still make that optional? So NAME is attribute and "NAME"
is attribute. Jody

I agree

--
Mauricio Pazos
www.axios.es

19

Mauricio Pazos ha scritto:

Pity that "ID" cannot be used, it would have been backwards compatible
(I think).

Yes, it could be a problem, I am thinking in this cases to query by fid
"ID" like "city.1" "ID" = 1
ID in (...)
I think it could be confuse

Yeah, I agree it could be a bit confusing... at the same time it would
not break uDig usage of ECQL. Jody? What is your preference, using " to
be able and use keywords as field names or moving to a custom sytanx
that cannot be mistaken for something else like:
id{id1, id2, ...}

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

20

Interesting problem:
- My technical direction was to be strict superset of CQL, so although I think it looks funny, the solution of id{'fid1','fid2','fid3'} is a strict superset.
(sigh).

However: even CQL has the problem of a conflict between keywords and property name references; ie cannot have an property name called 'not' for example. Do they provide any guidance on this one?

If we need to patch both CQL and ECQL then I would like to use the same quote solution as SQL to minimise the any learning curve.

Jody

On 28/05/2010, at 4:40 PM, Andrea Aime wrote:

Mauricio Pazos ha scritto:

Pity that "ID" cannot be used, it would have been backwards compatible
(I think).

Yes, it could be a problem, I am thinking in this cases to query by fid
"ID" like "city.1"
"ID" = 1
ID in (...)
I think it could be confuse

Yeah, I agree it could be a bit confusing... at the same time it would
not break uDig usage of ECQL. Jody? What is your preference, using " to
be able and use keywords as field names or moving to a custom sytanx
that cannot be mistaken for something else like:
id{id1, id2, ...}

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------

_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel