SQL condition 1=1

[ll4strw]

Hello, I have a few scripts which generate query strings dynamically based on some conditions. To keep the output queries syntactically correct and without many if/else I use the sql condition WHERE 1=1 as a starting condition in my query and then I concatenate any other conditions using and as needed. However I am afraid this is not possible with genquery2 while I read that the and operator should be supported. Am I committing any mistakes here or this is not supported at the moment? Thanks in advance.

irods@leidenuniv:~$ iquery  "select COLL_NAME where DATA_ACCESS_USER_NAME = 'leonardo'"  
[["/überZone/home/leonardo"]]

irods@leidenuniv:~$ iquery  "select COLL_NAME where 1=1 and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where '1=1' and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where 1='1' and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where 1 like '1' and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where 1 like '%1%' and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where 1==1 and DATA_ACCESS_USER_NAME = 'leonardo'"  
error: -167000
irods@leidenuniv:~$ iquery  "select COLL_NAME where  DATA_ACCESS_USER_NAME = 'leonardo' and 1=1"  
error: -167000

[trel]

Interesting use case...

Generally, the left side has to be a token name (database column)...

Will think through this a bit on our side - thanks!

[trel]

Are you using this technique (where 1=1) today with GenQuery1? So this issue is a regression?

[ll4strw]

Sorry for not specifying that, but this is something new that I am testing, so I do not know if it works with genquery1. On the other hand, I am noticing that iRODS v4.3.1 does not start if the zone name has a special char like in the example above /überZone/home/leonardo and produces

irods.exceptions.IrodsError: ERROR: Validation Failed for [/etc/irods/server_config.json]:
    against [file:///var/lib/irods/configuration_schemas/v4/server_config.json]
    ValidationError: 'überZone' does not match '^[A-Za-z0-9_\\.]+$'

Failed validating 'pattern' in schema['properties']['zone_name']:
    {'maxLength': 63, 'pattern': '^[A-Za-z0-9_\\.]+$', 'type': 'string'}

On instance['zone_name']:
    'überZone'

However, if a server is running and one decides to change the zone name via iadmin to a name with a special char, no warnings or errors are produced. In fact, things seem to still run okay. But perhaps this is an iRODS issue not related to this plugin.

[trel]

Okay - so this is not a regression - this is a potential feature request.

And the schema being angry about the zone name is in the main server, definitely not related to this plugin - that is correct.

Changing/loosening that schema is up for debate, but it is working as designed at the moment.

[ll4strw]

Changing/loosening that schema is up for debate, but it is working as designed at the moment.

But should not then iadmin prevent an admin from changing a zone name to an illegal value for consistency? This could prevent a lot of headaches :)

[trel]

A fair point. We can look into that - please create a new issue in irods/irods so we don't forget we talked about it.

[korydraughn]

Like @trel said, the parser isn't designed for that kind of thing.

If the goal is to be able to toggle different things via a condition, that can be achieved by replacing the use of 1=1 with a different condition using the GenQuery columns. For example ...

ZONE_NAME = '[__off__]' # Set to a valid zone name to enable.

The value you use to disable the query simply needs to be a value that you can be confident no one will ever use. You can use any GenQuery column and value to accomplish this.

Now, depending on the columns used in the query, you may need to use different columns for controlling which queries are enabled/disabled (due to table joins).