Require Trac v1.0.2 installation onwards

[ntmlod]

When attempting to write links to ticket or wiki page, I get no suggestions and the following error in the logging:

Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.7/site-packages/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-x86_64/egg/wikiautocomplete/web_ui.py", line 87, in process_request
    """ % db.prefix_match(),
  File "/usr/lib/python2.7/site-packages/trac/db/util.py", line 108, in __getattr__
    return getattr(self.cnx, name)
  File "/usr/lib/python2.7/site-packages/trac/db/util.py", line 108, in __getattr__
    return getattr(self.cnx, name)
  File "/usr/lib/python2.7/site-packages/trac/db/util.py", line 108, in __getattr__
    return getattr(self.cnx, name)
AttributeError: 'psycopg2._psycopg.connection' object has no attribute 'prefix_match'

To generate completions for 'ticket' or 'wikipage' case, the plugin use db.prefix_match & db.prefix_match_value methods which are missing in db directory of our v1.0.1 installation. Actually, these methods have been implemented since 1.0.2 version: ​https://trac.edgewall.org/changeset/13016.

Probably nothing essential for the plugin, I'm going to try to get this works.

[Jun Omae]

In that case, db.prefix_match() is used with CAST(id AS TEXT). It would leads full scan for ticket table and slowness if many tickets.

After the following patch, it would scan tickets with primary key of the ticket table.

wikiautocompleteplugin/trunk/wikiautocomplete/web_ui.py

@@ -6 +6 @@
 
 from trac.core import *
 from trac.resource import Resource
+from trac.ticket.model import Ticket
 from trac.util.text import to_unicode
 from trac.util.translation import dgettext
 from trac.web import IRequestFilter, IRequestHandler
@@ -60 +61 @@
             self._send_json(req, completions)
 
         elif strategy == 'ticket':
-            with self.env.db_query as db:
-                rows = db("""
-                    SELECT id, summary
-                    FROM ticket
-                    WHERE %s %s
+            try:
+                num = int(term)
+            except:
+                num = 0
+            ids = []
+            while num > 0 and Ticket.id_is_valid(num):
+                ids.append(num)
+                num *= 10
+            if ids:
+                rows = self.env.db_query("""
+                    SELECT id, summary FROM ticket
+                    WHERE id IN (%s)
                     ORDER BY changetime DESC
                     LIMIT 10
-                    """ % (db.cast('id', 'text'), db.prefix_match()),
-                    (db.prefix_match_value(term), ))
+                    """ % ','.join(['%s'] * len(ids)),
+                    ids)
+            else:
+                rows = []
             completions = [{
                 'id': row[0],
                 'summary': row[1],

[Jun Omae]

Also, typing ticket: leads no suggestion. It would be good to suggest when typing ticket: as the same as #.

wikiautocompleteplugin/trunk/wikiautocomplete/htdocs/js/wikiautocomplete.js

@@ -15 +15 @@
         },
 
         { // Tickets
-            match: /#(\d*)$/,
+            match: /(?:#|\bticket:)(\d*)$/,
             search: function (term, callback) {
                 $.getJSON(wikiautocomplete_url + '/ticket', { q: term })
                     .done(function (resp) { callback(resp); })

[Peter Suter]

Replying to jun66j5:

After the following patch, it would scan tickets with primary key of the ticket table.

For 77 would that still find 771, 772, 773, ... or only 770, 7700, 77000, ...?

[ntmlod]

Well for once I managed to fix it for my environment but perhaps it is not robust enough.

wikiautocomplete/web_ui.py

@@ -64 +64 @@
                 rows = db("""
                     SELECT id, summary
                     FROM ticket
-                    WHERE %s %s
+                    WHERE %s LIKE '%s%%'
                     ORDER BY changetime DESC
                     LIMIT 10
-                    """ % (db.cast('id', 'text'), db.prefix_match()),
-                    (db.prefix_match_value(term), ))
+                    """ % (db.cast('id', 'text'), term))
             completions = [{
                 'id': row[0],
                 'summary': row[1],
@@ -81 +80 @@
                 rows = db("""
                     SELECT name
                     FROM wiki
-                    WHERE name %s
+                    WHERE name LIKE '%s%%'
                     GROUP BY name
                     ORDER BY name
                     LIMIT 10
-                    """ % db.prefix_match(),
-                    (db.prefix_match_value(term), ))
+                    """ % term)
             completions = [row[0] for row in rows
                            if 'WIKI_VIEW' in req.perm(Resource('wiki', row[0]))]
             self._send_json(req, completions)

[ntmlod]

Also I modified the js script to split the two cases for 'ticket' links (short one with # and long one with ticket: or [ticket:... ...] because the previous proposition by jun66j5 replace [ticket:... ...] by [#... ...] which is not working.

wikiautocomplete/htdocs/js/wikiautocomplete.js

@@ -31 +31 @@
             cache: true,
         },
 
+        { // Tickets
+            match: /\bticket:(\w*)$/,
+            search: function (term, callback) {
+                $.getJSON(wikiautocomplete_url + '/ticket', { q: term })
+                    .done(function (resp) { callback(resp); })
+                    .fail(function () { callback([]); });
+            },
+            index: 1,
+            template: function (ticket) {
+                return '#' + ticket.id + ' ' + ticket.summary;
+            },
+            replace: function (ticket) {
+                return 'ticket:' + ticket.id;
+            },
+            cache: true,
+        },
+
         { // Wiki pages
             match: /\bwiki:([\w/]*)$/,
             search: function (term, callback) {

Probably not optimized because I guess it is possible to switch the cases after the string match but I'm a real newbie in Js.

I think there is room for improvment in order to add some others Trac useful links like changesets & reports for examples. Perhaps in my easy going time.

[Jun Omae]

Replying to psuter:

Replying to jun66j5:

After the following patch, it would scan tickets with primary key of the ticket table.

For 77 would that still find 771, 772, 773, ... or only 770, 7700, 77000, ...?

Sorry. I was stupid.

Revised patch:

wikiautocompleteplugin/trunk/wikiautocomplete/web_ui.py

@@ -6 +6 @@
 
 from trac.core import *
 from trac.resource import Resource
+from trac.ticket.model import Ticket
 from trac.util.text import to_unicode
 from trac.util.translation import dgettext
 from trac.web import IRequestFilter, IRequestHandler
@@ -60 +61 @@
             self._send_json(req, completions)
 
         elif strategy == 'ticket':
-            with self.env.db_query as db:
-                rows = db("""
-                    SELECT id, summary
-                    FROM ticket
-                    WHERE %s %s
+            try:
+                num = int(term)
+            except:
+                num = 0
+            args = []
+            mul = 1
+            while num > 0 and Ticket.id_is_valid(num):
+                args.append(num)
+                args.append(num + mul)
+                num *= 10
+                mul *= 10
+            if args:
+                expr = ' OR '.join(['id>=%s AND id<%s'] * (len(args) / 2))
+                rows = self.env.db_query("""
+                    SELECT id, summary FROM ticket
+                    WHERE %(expr)s
                     ORDER BY changetime DESC
                     LIMIT 10
-                    """ % (db.cast('id', 'text'), db.prefix_match()),
-                    (db.prefix_match_value(term), ))
+                    """ % {'expr': expr}, args)
+            else:
+                rows = self.env.db_query("""
+                    SELECT id, summary FROM ticket
+                    ORDER BY changetime DESC LIMIT 10""")
             completions = [{
                 'id': row[0],
                 'summary': row[1],

Typing 77, the following query is generated.

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT id, summary FROM ticket
   ...> WHERE id>=77 AND id<78 OR id>=770 AND id<780 OR id>=7700 AND id<7800 OR id>=77000 AND id<78000 OR id>=770000 AND id<780000 OR id>=7700000 AND id<7800000 OR id>=77000000 AND id<78000000 OR id>=770000000 AND id<780000000
   ...> ORDER BY changetime DESC
   ...> LIMIT 10;
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|SEARCH TABLE ticket USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

[Jun Omae]

Replying to ntmlod:

Well for once I managed to fix it for my environment but perhaps it is not robust enough.

-                    WHERE name %s
+                    WHERE name LIKE '%s%%'
                     GROUP BY name
                     ORDER BY name
                     LIMIT 10
-                    """ % db.prefix_match(),
-                    (db.prefix_match_value(term), ))
+                    """ % term)

The patch has SQL injections....

We could retrieve all page names using WikiSystem().pages.

wikiautocompleteplugin/trunk/wikiautocomplete/web_ui.py

@@ -77 +90 @@
             self._send_json(req, completions)
 
         elif strategy == 'wikipage':
-            with self.env.db_query as db:
-                rows = db("""
-                    SELECT name
-                    FROM wiki
-                    WHERE name %s
-                    GROUP BY name
-                    ORDER BY name
-                    LIMIT 10
-                    """ % db.prefix_match(),
-                    (db.prefix_match_value(term), ))
-            completions = [row[0] for row in rows
-                           if 'WIKI_VIEW' in req.perm(Resource('wiki', row[0]))]
+            pages = sorted(page for page in WikiSystem(self.env).pages
+                                if page.startswith(term) and
+                                   'WIKI_VIEW' in req.perm('wiki', page))
+            completions = pages[:10]
             self._send_json(req, completions)
 
         elif strategy == 'macro':

[Jun Omae]

Proposed changes in ​https://github.com/jun66j5/wikiautocompleteplugin/compare/master...t12915.

[Peter Suter]

Replying to jun66j5:

Proposed changes in ​https://github.com/jun66j5/wikiautocompleteplugin/compare/master...t12915.

Looks good to me. Feel free to commit.

[Jun Omae]

In 15939:

WikiAutoCompletePlugin: remove uses of db.prefix_match() (closes #12915)

• use WikiSystem.pages rather than executing query with db.prefix_match()
• use range scan for id column of ticket table rather than db.prefix_match() with CAST (id AS TEXT)
• sort suggestion for macro and send first 10 entries only
• make compatible with Python 2.5

[Jun Omae]

Thanks for the reviewing! Committed in [15939].