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