Enable macro and plugin developers to use REGEXP on all database backends

[carstenklein@…]

Rationale

It would be most helpful to allow plugin developers to query the trac database by also using regular expressions. The reason for that is, that LIKE cannot be used to narrow down search results based on for example character classes and so on.

Background

While mysql and postgresql natively support REGEXPs in different ways, sqlite requires a user function to be created before that any queries using REGEXP to the database can be send.

In the attached archive you find new versions of the database backends that will add regexp(), regexpi(), not_regexp() and a not_regexpi() helpers to the connection wrappers.

In addition, the sqlite_backend's connection wrapper will now register a regexp() user function with the wrapped connection.

[carstenklein@…]

The "enhanced" database backends

[carstenklein@…]

Please note that as of now, only the sqlite backend was tested. The mysql and postgresql backends have not been tested but should work right out of the box, unless that there is some typo in the helpers.

[carstenklein@…]

The "enhanced" database backends

[carstenklein@…]

Please note that the first attachment does not contain any data. Somehow I managed to tar the symbolic links instead of the actual files. The second attachment contains the correct files.

[Remy Blank]

Please submit patches as unified diffs (see TracDev/SubmittingPatches). Also, rather than providing four methods for regexp variants, consider providing a single regexp() method with optional arguments.

I wonder if REGEXP could be the answer to #8519? If all backends support it, it might be worth a try…

[anonymous]

for granted, i will compile a unified patch and merge existing helper methods into two distinct methods, namely not_regexp and regexp, both supporting an optional parameter case_insensitive.

Merging all of the four into one method leaves the developer with the task to add unwanted arguments, IMO.

E.g. Single Method Version

  def regexp( expr, case_insensitive=False, inverse=False )

Usage

  regexp( "^FooBar$", True, True )
  regexp( "^FooBar$", False, True ) 
  regexp( "^FooBar$" ) 

as opposed to

  def regexp( expr, case_insensitive=False )
  def not_regexp( expr, case_insensitive=False )

Usage

  regexp( "^FooBar$", True )
  regexp( "^FooBar$" )
  not_regexp( "^FooBar$", True )
  not_regexp( "^FooBar$" )

But I will consider your further input thoroughly, if you have any objections to the above.

[anonymous]

of course, using python we could always settle on named arguments. would that be ok? AFAIK the current API does not depend on named arguments.

[carstenklein@…]

Considering that is a minor patch, I wonder why this is rescheduled to 1.0?

Would it not be of greater benefit to the plugin developers to have it right now?

[Remy Blank]

Replying to anonymous:

of course, using python we could always settle on named arguments.

Yes, named arguments are often more readable. Consider:

regexp("^FooBar$", case_sensitive=False)

About the not_* variants, I wonder if they are needed at all. We only have like(), and use the following construct to specify the negation:

cursor.execute("SELECT id FROM ticket "
               "WHERE subject NOT %s" % db.like(),
               (db.like_escape(value),))

That is, the negation is placed before the LIKE expansion.

Replying to anonymous:

Considering that is a minor patch, I wonder why this is rescheduled to 1.0?

There are several reasons:

• 0.11.7 will be the last 0.11.x release and is for critical bugfixes only.
• We have already pretty much selected the tickets for 0.12, so that's out, too.
• We currently have no use for REGEXP matching in Trac core (except maybe for #8519).
• We haven't had any requests for REGEXP so far.
• I haven't checked yet, but I suspect the regexp syntax used by the various database backends differ, which could make this a nightmare to provide database-neutral functionality.
• We don't have a complete, working and tested patch yet.

So, to include this functionality earlier, we need a good use case, a few people chiming in that they need it, and an implementation for all database backends that allows backend-neutral usage.

[carstenklein@…]

I have created the patch against the trunk.

I made the following the default behaviour for all backends:

case_sensitive=False and inverse=False

See the newest attachment for more information.

As for the use cases: (the ones that I have found so far)

UC1 - search for all tickets in the database and cross references thereof in the description:

REGEXP = "([[]ticket:%s[]]|#%s\D)"
...
sql += "where ticket.description %s" % backend.regexp( REGEXP % searchTerm )

This is something that otherwise would have to be done in code, namely instantiating every single object from the database and check whether it matches an expression similar to the above expression.

Please note that the above regular expression is not complete. It will fail to match shortcut ticket references at the end of a line or at the end of the input string.

This use case actually came up when I adjusted the behaviour of the TicketBackLinksMacro so that it will find all shortcut ticket references, not just those that were delimited with an extra single whitespace character.

UC2 - user defined regexp based searches (extended search)

[carstenklein@…]

The unified patch against the current trunk

[carstenklein@…]

Regarding the !SQLite backend I must revise my initial statement of making case_sensitive=False the default behaviour. !SQLite as opposed to !PostgreSQL and !MySQL does not provide for a special syntax. I believe that the user must provide a different regular expression that will include an option that it should match/search case insensitively. Perhaps that could be added to the regexp() helper in the sqlite backend?

[Christian Boos]

Milestone 1.0 deleted

[dnedelchev <dbn@…>]

The standard LIKE operator syntax is quite poor. Implementing REGEXP should be very welcome I think…

[Christian Boos]

Milestone triaging deleted

[Carsten Klein <carsten.klein@…>]

duplicate of #3273.

[Remy Blank]

Closing as a duplicate, then.