diff options
3 files changed, 193 insertions, 170 deletions
diff --git a/etherpad/src/plugins/twitterStyleTags/controllers/tagBrowser.js b/etherpad/src/plugins/twitterStyleTags/controllers/tagBrowser.js index 7071306..838fb24 100644 --- a/etherpad/src/plugins/twitterStyleTags/controllers/tagBrowser.js +++ b/etherpad/src/plugins/twitterStyleTags/controllers/tagBrowser.js @@ -15,6 +15,8 @@ * limitations under the License. */ +import("plugins.twitterStyleTags.models.tagQuery"); + import("faststatic"); import("dispatch.{Dispatcher,PrefixMatcher,forward}"); @@ -31,167 +33,6 @@ import("sqlbase.sqlcommon"); import("sqlbase.sqlobj"); import("etherpad.pad.padutils"); -function tagsToQuery(tags, antiTags) { - var prefixed = []; - for (i = 0; i < antiTags.length; i++) - prefixed[i] = '!' + antiTags[i]; - return tags.concat(prefixed).join(','); -} - -function stringFormat(text, obj) { - var name; - for (name in obj) { - //iterate through the params and replace their placeholders from the original text - text = text.replace(new RegExp('%\\(' + name + '\\)s', 'gi' ), obj[name]); - } - return text; -} - -/* All these sql query functions both takes a querySql object as - * parameter and returns one. This object has two members - sql and - * params. Sql is a string of an sql table name or a subqyery in - * parens. The table pr subquery should have an ID column containing a - * PAD_ID. - */ - -/* Filters pads by tags and anti-tags */ -function getQueryToSql(tags, antiTags, querySql) { - var queryTable; - var queryParams; - - if (querySql == null) { - queryTable = 'PAD_META'; - queryParams = []; - } else { - queryTable = querySql.sql; - queryParams = querySql.params; - } - - var exceptArray = []; - var joinArray = []; - var whereArray = []; - var exceptParamArray = []; - var joinParamArray = []; - - var info = new Object(); - info.queryTable = queryTable; - info.n = 0; - var i; - - for (i = 0; i < antiTags.length; i++) { - tag = antiTags[i]; - exceptArray.push( - stringFormat( - 'left join (PAD_TAG as pt%(n)s ' + - ' join TAG AS t%(n)s on ' + - ' t%(n)s.NAME = ? ' + - ' and t%(n)s.ID = pt%(n)s.TAG_ID) on ' + - ' pt%(n)s.PAD_ID = p.ID ', - info)); - whereArray.push(stringFormat('pt%(n)s.TAG_ID is null', info)); - exceptParamArray.push(tag); - info.n += 1; - } - for (i = 0; i < tags.length; i++) { - tag = tags[i]; - joinArray.push( - stringFormat( - 'join PAD_TAG as pt%(n)s on ' + - ' pt%(n)s.PAD_ID = p.ID ' + - 'join TAG as t%(n)s on ' + - ' t%(n)s.ID = pt%(n)s.TAG_ID ' + - ' and t%(n)s.NAME = ? ', - info)); - joinParamArray.push(tag); - info.n += 1; - } - - info["joins"] = joinArray.join(' '); - info["excepts"] = exceptArray.join(' '); - info["wheres"] = whereArray.length > 0 ? ' where ' + whereArray.join(' and ') : ''; - - /* Create a subselect from all the joins */ - return { - sql: stringFormat( - '(select distinct ' + - ' p.ID ' + - ' from ' + - ' %(queryTable)s as p ' + - ' %(joins)s ' + - ' %(excepts)s ' + - ' %(wheres)s ' + - ') ', - info), - params: queryParams.concat(joinParamArray).concat(exceptParamArray)}; -} - -/* Returns the sql to count the number of results from some other - * query. */ -function nrSql(querySql) { - var queryTable; - var queryParams; - - if (querySql == null) { - queryTable = 'PAD_META'; - queryParams = []; - } else { - queryTable = querySql.sql; - queryParams = querySql.params; - } - - var info = []; - info['query_sql'] = queryTable - return { - sql: stringFormat('(select count(*) as total from %(query_sql)s as q)', info), - params: queryParams}; -} - -/* Returns the sql to select the 10 best new tags to tack on to a - * query, that is, the tags that are closest to halving the result-set - * if tacked on. */ -function newTagsSql(querySql) { - var queryTable; - var queryParams; - - if (querySql == null) { - queryTable = 'PAD_META'; - queryParams = []; - } else { - queryTable = querySql.sql; - queryParams = querySql.params; - } - - var info = []; - info["query_post_table"] = queryTable; - var queryNrSql = nrSql(querySql); - info["query_nr_sql"] = queryNrSql.sql; - queryNrParams = queryNrSql.params; - - return { - sql: stringFormat('' + - 'select ' + - ' t.NAME tagname, ' + - ' count(tp.PAD_ID) as matches, ' + - ' tn.total - count(tp.PAD_ID) as antimatches, ' + - ' abs(count(tp.PAD_ID) - (tn.total / 2)) as weight ' + - 'from ' + - ' TAG as t, ' + - ' PAD_TAG as tp, ' + - ' %(query_nr_sql)s as tn ' + - 'where ' + - ' tp.TAG_ID = t.ID ' + - ' and tp.PAD_ID in %(query_post_table)s ' + - ' and tp.PAD_ID NOT LIKE \'%$%\'' + - 'group by t.NAME, tn.total ' + - 'having ' + - ' count(tp.PAD_ID) > 0 and count(tp.PAD_ID) < tn.total ' + - 'order by ' + - ' abs(count(tp.PAD_ID) - (tn.total / 2)) asc ' + - 'limit 10 ' + - '', info), - params: queryNrParams.concat(queryParams)}; -} - function onRequest() { var tags = new Array(); @@ -207,10 +48,10 @@ function onRequest() { } /* Create the pad filter sql */ - var querySql = getQueryToSql(tags.concat(['public']), antiTags); + var querySql = tagQuery.getQueryToSql(tags.concat(['public']), antiTags); /* Use the pad filter sql to figure out which tags to show in the tag browser this time. */ - var queryNewTagsSql = newTagsSql(querySql); + var queryNewTagsSql = tagQuery.newTagsSql(querySql); var newTags = sqlobj.executeRaw(queryNewTagsSql.sql, queryNewTagsSql.params); /* Select the 10 last changed matching pads and some extra information on them. Except the Pro Pads*/ @@ -260,7 +101,7 @@ function onRequest() { var info = { prefs: prefs, config: appjet.config, - tagsToQuery: tagsToQuery, + tagQuery: tagQuery, padIdToReadonly: server_utils.padIdToReadonly, tags: tags, antiTags: antiTags, diff --git a/etherpad/src/plugins/twitterStyleTags/models/tagQuery.js b/etherpad/src/plugins/twitterStyleTags/models/tagQuery.js new file mode 100644 index 0000000..a2e8374 --- /dev/null +++ b/etherpad/src/plugins/twitterStyleTags/models/tagQuery.js @@ -0,0 +1,182 @@ +/** + * Copyright 2010 RedHog, Egil Möller <egil.moller@piratpartiet.se> + * Copyright 2010 Pita, Peter Martischka <petermartischka@googlemail.com> + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS-IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +import("sqlbase.sqlbase"); +import("sqlbase.sqlcommon"); +import("sqlbase.sqlobj"); +import("etherpad.log"); + +function tagsToQuery(tags, antiTags) { + var prefixed = []; + for (i = 0; i < antiTags.length; i++) + prefixed[i] = '!' + antiTags[i]; + return tags.concat(prefixed).join(','); +} + +function stringFormat(text, obj) { + var name; + for (name in obj) { + //iterate through the params and replace their placeholders from the original text + text = text.replace(new RegExp('%\\(' + name + '\\)s', 'gi' ), obj[name]); + } + return text; +} + +/* All these sql query functions both takes a querySql object as + * parameter and returns one. This object has two members - sql and + * params. Sql is a string of an sql table name or a subqyery in + * parens. The table pr subquery should have an ID column containing a + * PAD_ID. + */ + +/* Filters pads by tags and anti-tags */ +function getQueryToSql(tags, antiTags, querySql) { + var queryTable; + var queryParams; + + if (querySql == null) { + queryTable = 'PAD_META'; + queryParams = []; + } else { + queryTable = querySql.sql; + queryParams = querySql.params; + } + + var exceptArray = []; + var joinArray = []; + var whereArray = []; + var exceptParamArray = []; + var joinParamArray = []; + + var info = new Object(); + info.queryTable = queryTable; + info.n = 0; + var i; + + for (i = 0; i < antiTags.length; i++) { + tag = antiTags[i]; + exceptArray.push( + stringFormat( + 'left join (PAD_TAG as pt%(n)s ' + + ' join TAG AS t%(n)s on ' + + ' t%(n)s.NAME = ? ' + + ' and t%(n)s.ID = pt%(n)s.TAG_ID) on ' + + ' pt%(n)s.PAD_ID = p.ID ', + info)); + whereArray.push(stringFormat('pt%(n)s.TAG_ID is null', info)); + exceptParamArray.push(tag); + info.n += 1; + } + for (i = 0; i < tags.length; i++) { + tag = tags[i]; + joinArray.push( + stringFormat( + 'join PAD_TAG as pt%(n)s on ' + + ' pt%(n)s.PAD_ID = p.ID ' + + 'join TAG as t%(n)s on ' + + ' t%(n)s.ID = pt%(n)s.TAG_ID ' + + ' and t%(n)s.NAME = ? ', + info)); + joinParamArray.push(tag); + info.n += 1; + } + + info["joins"] = joinArray.join(' '); + info["excepts"] = exceptArray.join(' '); + info["wheres"] = whereArray.length > 0 ? ' where ' + whereArray.join(' and ') : ''; + + /* Create a subselect from all the joins */ + return { + sql: stringFormat( + '(select distinct ' + + ' p.ID ' + + ' from ' + + ' %(queryTable)s as p ' + + ' %(joins)s ' + + ' %(excepts)s ' + + ' %(wheres)s ' + + ') ', + info), + params: queryParams.concat(joinParamArray).concat(exceptParamArray)}; +} + +/* Returns the sql to count the number of results from some other + * query. */ +function nrSql(querySql) { + var queryTable; + var queryParams; + + if (querySql == null) { + queryTable = 'PAD_META'; + queryParams = []; + } else { + queryTable = querySql.sql; + queryParams = querySql.params; + } + + var info = []; + info['query_sql'] = queryTable + return { + sql: stringFormat('(select count(*) as total from %(query_sql)s as q)', info), + params: queryParams}; +} + +/* Returns the sql to select the 10 best new tags to tack on to a + * query, that is, the tags that are closest to halving the result-set + * if tacked on. */ +function newTagsSql(querySql) { + var queryTable; + var queryParams; + + if (querySql == null) { + queryTable = 'PAD_META'; + queryParams = []; + } else { + queryTable = querySql.sql; + queryParams = querySql.params; + } + + var info = []; + info["query_post_table"] = queryTable; + var queryNrSql = nrSql(querySql); + info["query_nr_sql"] = queryNrSql.sql; + queryNrParams = queryNrSql.params; + + return { + sql: stringFormat('' + + 'select ' + + ' t.NAME tagname, ' + + ' count(tp.PAD_ID) as matches, ' + + ' tn.total - count(tp.PAD_ID) as antimatches, ' + + ' abs(count(tp.PAD_ID) - (tn.total / 2)) as weight ' + + 'from ' + + ' TAG as t, ' + + ' PAD_TAG as tp, ' + + ' %(query_nr_sql)s as tn ' + + 'where ' + + ' tp.TAG_ID = t.ID ' + + ' and tp.PAD_ID in %(query_post_table)s ' + + ' and tp.PAD_ID NOT LIKE \'%$%\'' + + 'group by t.NAME, tn.total ' + + 'having ' + + ' count(tp.PAD_ID) > 0 and count(tp.PAD_ID) < tn.total ' + + 'order by ' + + ' abs(count(tp.PAD_ID) - (tn.total / 2)) asc ' + + 'limit 10 ' + + '', info), + params: queryNrParams.concat(queryParams)}; +} diff --git a/etherpad/src/plugins/twitterStyleTags/templates/tagBrowser.ejs b/etherpad/src/plugins/twitterStyleTags/templates/tagBrowser.ejs index 955d2e6..1dddce7 100644 --- a/etherpad/src/plugins/twitterStyleTags/templates/tagBrowser.ejs +++ b/etherpad/src/plugins/twitterStyleTags/templates/tagBrowser.ejs @@ -18,7 +18,7 @@ limitations under the License. */ %> helpers.setHtmlTitle("EtherPad: Browse tags"); helpers.includeCss("plugins/twitterStyleTags/tagBrowser.css"); helpers.includeCss("plugins/twitterStyleTags/pad.css"); - helpers.addToHead('\n<link rel="alternate" href="/ep/tag/?query=' + tagsToQuery(tags, antiTags) + '&format=rss" type="application/rss+xml" title="Query results as RSS" />\n'); + helpers.addToHead('\n<link rel="alternate" href="/ep/tag/?query=' + tagQuery.tagsToQuery(tags, antiTags) + '&format=rss" type="application/rss+xml" title="Query results as RSS" />\n'); function inArray(item, arr) { for (var i = 0; i < arr.length; i++) @@ -59,10 +59,10 @@ limitations under the License. */ %> Latest changed pads <% } else { %> <% for (i = 0; i < tags.length; i++) { %> - <a href="/ep/tag/?query=<%= tagsToQuery(tags.filter(function (tag) { return tag != tags[i]}), antiTags) %>" class="padtag" title="<%= tags[i] %> matches">#<%= tags[i] %></a> + <a href="/ep/tag/?query=<%= tagQuery.tagsToQuery(tags.filter(function (tag) { return tag != tags[i]}), antiTags) %>" class="padtag" title="<%= tags[i] %> matches">#<%= tags[i] %></a> <% } %> <% for (i = 0; i < antiTags.length; i++) { %> - <a href="/ep/tag/?query=<%= tagsToQuery(tags, antiTags.filter(function (tag) { return tag != antiTags[i]})) %>" class="anti_padtag" title="<%= antiTags[i] %> matches">!#<%= antiTags[i] %></a> + <a href="/ep/tag/?query=<%= tagQuery.tagsToQuery(tags, antiTags.filter(function (tag) { return tag != antiTags[i]})) %>" class="anti_padtag" title="<%= antiTags[i] %> matches">!#<%= antiTags[i] %></a> <% } %> <% } %> </td> @@ -73,12 +73,12 @@ limitations under the License. */ %> <div class="query-refiner"> <h1>Search for pads that have the tag</h1> <% for (i = 0; i < newTags.length; i++) { %> - <a href="/ep/tag/?query=<%= tagsToQuery(tags.concat([newTags[i].tagname]),antiTags) %>" class="padtag" title="<%= newTags[i].matches %> matches">#<%= newTags[i].tagname %></a> + <a href="/ep/tag/?query=<%= tagQuery.tagsToQuery(tags.concat([newTags[i].tagname]),antiTags) %>" class="padtag" title="<%= newTags[i].matches %> matches">#<%= newTags[i].tagname %></a> <% } %> <h1>Search for pads that <em>don't</em> have the tag</h1> <% for (i = 0; i < newTags.length; i++) { %> - <a href="/ep/tag/?query=<%= tagsToQuery(tags,antiTags.concat([newTags[i].tagname])) %>" class="anti_padtag" title="<%= newTags[i].antimatches %> matches">!#<%= newTags[i].tagname %></a> + <a href="/ep/tag/?query=<%= tagQuery.tagsToQuery(tags,antiTags.concat([newTags[i].tagname])) %>" class="anti_padtag" title="<%= newTags[i].antimatches %> matches">!#<%= newTags[i].tagname %></a> <% } %> </div> @@ -95,7 +95,7 @@ limitations under the License. */ %> <dt><a href="/<%= matchingPadUrl %>"><%= matchingPadId %></a><dt> <dd> <% for (j = 0; j < matchingPads[i].TAGS.length; j++) { %> - <a href="/ep/tag/?query=<%= tagsToQuery(tags.concat([matchingPads[i].TAGS[j]]), antiTags) %>" class="padtag" title="<%= matchingPads[i].TAGS[j] %> matches">#<%= matchingPads[i].TAGS[j] %></a> + <a href="/ep/tag/?query=<%= tagQuery.tagsToQuery(tags.concat([matchingPads[i].TAGS[j]]), antiTags) %>" class="padtag" title="<%= matchingPads[i].TAGS[j] %> matches">#<%= matchingPads[i].TAGS[j] %></a> <% } %> </dd> <% } %> |