      1 // The Antville Project
      2 // http://code.google.com/p/antville
      3 //
      4 // Copyright 2007-2011 by Tobi Schäfer.
      5 //
      6 // Copyright 2001–2007 Robert Gaggl, Hannes Wallnöfer, Tobi Schäfer,
      7 // Matthias & Michael Platzer, Christoph Lincke.
      8 //
      9 // Licensed under the Apache License, Version 2.0 (the ``License'');
     10 // you may not use this file except in compliance with the License.
     11 // You may obtain a copy of the License at
     12 //
     13 //    http://www.apache.org/licenses/LICENSE-2.0
     14 //
     15 // Unless required by applicable law or agreed to in writing, software
     16 // distributed under the License is distributed on an ``AS IS'' BASIS,
     17 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
     18 // See the License for the specific language governing permissions and
     19 // limitations under the License.
     20 //
     21 // $Revision$
     22 // $LastChangedBy$
     23 // $LastChangedDate$
     24 // $URL$
     25
     26 /**
     27  * @fileOverview Defines the Sql prototype, a utility for relational queries
     28  */
     29
     30 /**
     31  * @constructor
     32  */
     33 var Sql = function(options) {
     34    options || (options = {});
     35    var db = getDBConnection("antville");
     36    var query;
     37
     38    var log = new function() {
     39       var fname = getProperty("sqlLog", "helma." + app.getName() + ".sql");
     40       return Packages.org.apache.commons.logging.LogFactory.getLog(fname);
     41    }
     42
     43    var SqlData = function(result) {
     44       var columns = [];
     45       this.values = {};
     46
     47       for (var i=1; i<=result.getColumnCount(); i+=1) {
     48          columns.push(result.getColumnName(i).toLowerCase());
     49       }
     50
     51       this.next = function() {
     52          for each (var key in columns) {
     53             this.values[key] = result.getColumnItem(key);
     54          }
     55          return;
     56       }
     57
     58       return this;
     59    }
     60
     61    var quote = function(str) {
     62       if (!options.quote || str === null) {
     63          return str;
     64       }
     65       return str.replace(/\\/g, "\\\\").replace(/'/g, "\\'");
     66    }
     67
     68    var value = function(obj) {
     69       if (obj === null) {
     70          return obj;
     71       }
     72       if (obj === undefined) {
     73          obj = String(obj);
     74       }
     75       switch (obj.constructor) {
     76          case Number:
     77          return obj;
     78          case String:
     79          return quote(obj);
     80          case Date:
     81          return "from_unixtime(" + (obj.getTime() / 1000) + ")";
     82          case HopObject:
     83          case Object:
     84          return quote(obj.toSource());
     85       }
     86       return quote(String(obj));
     87    }
     88
     89    var resolve = function(args) {
     90       var sql = args[0];
     91       if (args.length > 1) {
     92          var values = Array.prototype.splice.call(args, 1);
     93          if (typeof values[0] === "object") {
     94             values = values[0];
     95          }
     96          sql = sql.replace(/\$(\w*)/g, function() {
     97             return value(values[arguments[1]]);
     98          });
     99       }
    100       return sql;
    101    }
    102
    103    /**
    104     * Executes an SQL command.
    105     * @param {String} sql The SQL command.
    106     * @returns {Object} The result of the SQL command.
    107     */
    108    this.execute = function(sql) {
    109       sql = resolve(arguments);
    110       log.info(sql);
    111       if (options.test) {
    112          return app.log(sql);
    113       }
    114       var error;
    115       var result = db.executeCommand(sql);
    116       if (error = db.getLastError()) {
    117          app.log(error);
    118       }
    119       return result;
    120    }
    121
    122    /**
    123     * Retrieves an SQL query.
    124     * @example sql.retrieve('select $1 from $2 order by $1', 'date', 'foo')
    125     * ===> 'select date from foo order by date'
    126     * @returns {String}
    127     */
    128    this.retrieve = function() {
    129       return log.info(query = resolve(arguments));
    130    }
    131
    132    /**
    133     * Traverses over the results of an SQL query.
    134     * @param {Function} callback The callback function executed for each record.
    135     */
    136    this.traverse = function(callback) {
    137       var rows = db.executeRetrieval(query);
    138       if (rows && rows.next()) {
    139          do {
    140             var sql = new SqlData(rows);
    141             sql.next();
    142             if (!options.test) {
    143                callback.call(sql.values, rows);
    144             }
    145          } while (record = rows.next());
    146          rows.release();
    147       }
    148       return;
    149    }
    150
    151    /**
    152     * @return {String}
    153     */
    154    this.toString = function() {
    155       return query;
    156    }
    157
    158    return this;
    159 }
    160
    161 /**
    162  * SQL query for retrieving the amount of records in a table.
    163  * @constant
    164  */
    165 Sql.COUNT = "select count(*) as count from $0";
    166
    167 /**
    168  * SQL query for retrieving the referrers of a site or a story.
    169  * @constant
    170  */
    171 Sql.REFERRERS = "select referrer, count(*) as requests from " +
    172       "log where context_type = '$0' and context_id = $1 and action = " +
    173       "'main' and created > now() - interval '2 days' group " +
    174       "by referrer order by requests desc, referrer asc";
    175
    176 /**
    177  * SQL command for deleting all log entries older than 2 days.
    178  * @constant
    179  */
    180 Sql.PURGEREFERRERS = "delete from log where action = 'main' and " +
    181       "created < now() - interval '2 days'";
    182
    183 /**
    184  * SQL query for searching stories and comments.
    185  * @constant
    186  */
    187 Sql.SEARCH = "select content.id from content, site, metadata where site.id = $0 and " +
    188       "site.id = content.site_id and content.status in ('public', 'shared', 'open') and " +
    189       "content.id = metadata.parent_id and metadata.name in ('title', 'text') and " +
    190       "lower(metadata.value) like lower('%$1%') group by content.id, content.created " +
    191       "order by content.created desc limit $2";
    192
    193 /**
    194  * SQL query for searching members.
    195  * @constant
    196  */
    197 Sql.MEMBERSEARCH = "select name from account where name $0 '$1' " +
    198       "order by name asc limit $2";
    199
    200 /**
    201  * SQL query for retrieving all story IDs in a site’s archive.
    202  * @constant
    203  */
    204 Sql.ARCHIVE = "select id from content where site_id = $0 and prototype = 'Story' and " +
    205       "status in ('public', 'shared', 'open') $1 $2 limit $3 offset $4";
    206
    207 /**
    208  * SQL command for retrieving the size of a site’s archive.
    209  * @constant
    210  */
    211 Sql.ARCHIVESIZE = "select count(*) as count from content where site_id = $0 " +
    212       "and prototype = 'Story' and status in ('public', 'shared', 'open') $1";
    213
    214 /**
    215  * SQL part filtering the archive query.
    216  * @see Archive#getFilter
    217  * @constant
    218  */
    219 Sql.ARCHIVEPART = " and extract($0 from created) = $1";
    220
    221 /**
    222  * SQL part for applying an order to the archive query.
    223  * @see Archive#stories_macro
    224  * @constant
    225  */
    226 Sql.ARCHIVEORDER = "order by created desc";
    227