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