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