Edgewall Software

Opened 12 years ago

Closed 11 years ago

#8914 closed enhancement (duplicate)

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

Reported by: carstenklein@… Owned by:
Priority: normal Milestone:
Component: general Version: 0.11.1
Severity: normal Keywords:
Cc: mmitar@… Branch:
Release Notes:
API Changes:
Internal Changes:



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.


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.

Attachments (3)

TracDbBackendPatch.tar.gz (186 bytes ) - added by carstenklein@… 12 years ago.
The "enhanced" database backends
TracDbBackendPatch.tar.2.gz (5.3 KB ) - added by carstenklein@… 12 years ago.
The "enhanced" database backends
TracDBBackendRegexp.patch (3.0 KB ) - added by carstenklein@… 12 years ago.
The unified patch against the current trunk

Download all attachments as: .zip

Change History (18)

by carstenklein@…, 12 years ago

Attachment: TracDbBackendPatch.tar.gz added

The "enhanced" database backends

comment:1 by carstenklein@…, 12 years ago

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.

by carstenklein@…, 12 years ago

Attachment: TracDbBackendPatch.tar.2.gz added

The "enhanced" database backends

comment:2 by carstenklein@…, 12 years ago

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.

comment:3 by Remy Blank, 12 years ago


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…

comment:4 by anonymous, 12 years ago

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 )


  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 )


  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.

comment:5 by anonymous, 12 years ago

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.

comment:6 by carstenklein@…, 12 years ago

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?

in reply to:  5 comment:7 by Remy Blank, 12 years ago

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(),

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.

comment:8 by carstenklein@…, 12 years ago

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)

by carstenklein@…, 12 years ago

Attachment: TracDBBackendRegexp.patch added

The unified patch against the current trunk

comment:9 by carstenklein@…, 12 years ago

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?

comment:10 by Christian Boos, 12 years ago

Milestone: 1.0unscheduled

Milestone 1.0 deleted

comment:11 by dnedelchev <dbn@…>, 12 years ago

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

comment:12 by Christian Boos, 12 years ago

Milestone: triagingunscheduled

Milestone triaging deleted

comment:13 by Mitar, 12 years ago

Cc: mmitar@… added

comment:14 by Carsten Klein <carsten.klein@…>, 11 years ago

duplicate of #3273.

comment:15 by Remy Blank, 11 years ago

Milestone: unscheduled
Resolution: duplicate
Status: newclosed

Closing as a duplicate, then.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.