001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * 005 * This library is free software; you can redistribute it and/or 006 * modify it under the terms of the GNU Lesser General Public 007 * License as published by the Free Software Foundation; either 008 * version 2.1 of the License, or (at your option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 013 * Lesser General Public License for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public 016 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 017 * 018 **/ 019package lucee.commons.io.res.type.datasource.core; 020 021import java.io.ByteArrayInputStream; 022import java.io.IOException; 023import java.io.InputStream; 024import java.sql.Connection; 025import java.sql.PreparedStatement; 026import java.sql.ResultSet; 027import java.sql.SQLException; 028import java.sql.Statement; 029import java.sql.Timestamp; 030import java.util.ArrayList; 031import java.util.List; 032 033import lucee.commons.db.DBUtil; 034import lucee.commons.io.res.type.datasource.Attr; 035import lucee.commons.lang.StringUtil; 036import lucee.runtime.db.DatasourceConnection; 037 038public class MySQL extends CoreSupport { 039 040 private static final int DEFAULT_MODE = 0777; 041 private static final int DEFAULT_ATTRS = 0; 042 043 public MySQL(DatasourceConnection dc, String prefix) throws SQLException { 044 Connection conn = dc.getConnection(); 045 Statement stat1=null; 046 ResultSet rs=null; 047 boolean installAttrs=true; 048 boolean installData=true; 049 050 // check attr 051 String sql="show table status like '" + prefix + "attrs'"; 052 try{ 053 stat1=conn.createStatement(); 054 rs=stat1.executeQuery(sql); 055 if(rs.next())installAttrs=false; 056 } 057 finally { 058 DBUtil.closeEL(rs); 059 DBUtil.closeEL(stat1); 060 } 061 062 // check data 063 sql="show table status like '"+prefix+"data'"; 064 try{ 065 stat1=conn.createStatement(); 066 rs=stat1.executeQuery(sql); 067 if(rs.next())installData=false; 068 } 069 finally { 070 DBUtil.closeEL(rs); 071 DBUtil.closeEL(stat1); 072 } 073 074 075 076 077 if(installAttrs){ 078 execute(conn,"CREATE TABLE `" + prefix + "attrs` (" + 079 "`rdr_id` int(11) NOT NULL auto_increment," + 080 "`rdr_name` varchar(255) default NULL," + 081 "`rdr_path_hash` int(11) default NULL," + 082 "`rdr_full_path_hash` int(11) default NULL," + 083 "`rdr_path` varchar(1023) default NULL," + 084 "`rdr_type` int(11) default NULL," + 085 "`rdr_last_modified` datetime default NULL," + 086 "`rdr_mode` int(11) default '0'," + 087 "`rdr_attributes` int(11) default '0'," + 088 "`rdr_data` int(11) default '0'," + 089 "`rdr_length` int(11) default '0'," + 090 "PRIMARY KEY (`rdr_id`)," + 091 "KEY `idx_name` (`rdr_name`)," + 092 "KEY `idx_path_hash` (`rdr_path_hash`)," + 093 "KEY `idx_full_path_hash` (`rdr_full_path_hash`)," + 094 "KEY `idx_data` (`rdr_data`)" + 095 ")"); 096 } 097 098 if(installData){ 099 execute(conn, "CREATE TABLE `" + prefix + "data` (" + 100 "`rdr_id` int(10) unsigned NOT NULL auto_increment," + 101 "`rdr_data` longblob," + 102 "PRIMARY KEY (`rdr_id`)" + 103 ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"); 104 } 105 } 106 107 108 private void execute(Connection conn, String sql) throws SQLException { 109 log(sql); 110 Statement stat=null; 111 try{ 112 stat=conn.createStatement(); 113 stat.executeUpdate(sql); 114 } 115 finally { 116 DBUtil.closeEL(stat); 117 } 118 } 119 120 121 @Override 122 public Attr getAttr(DatasourceConnection dc, String prefix, int fullPathHash,String path, String name) throws SQLException { 123 // ROOT 124 if(StringUtil.isEmpty(path)) 125 return ATTR_ROOT; 126 127 128 String sql="select rdr_id,rdr_type,rdr_length,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data from "+prefix+"attrs where rdr_full_path_hash=? and rdr_path=? and rdr_name=?"; 129 PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql); 130 stat.setInt(1, fullPathHash); 131 stat.setString(2, path); 132 stat.setString(3, name); 133 log(sql,fullPathHash+"",path,name); 134 135 ResultSet rs = stat.executeQuery(); 136 try{ 137 if(!rs.next())return null; 138 139 return new Attr( 140 rs.getInt(1), 141 name,path,true, 142 rs.getInt(2), 143 rs.getInt(3), 144 rs.getTimestamp(4,getCalendar()).getTime(), 145 rs.getShort(5), 146 rs.getShort(6), 147 rs.getInt(7)); 148 } 149 finally { 150 DBUtil.closeEL(rs); 151 //DBUtil.closeEL(stat); 152 } 153 } 154 155 156 @Override 157 public List getAttrs(DatasourceConnection dc, String prefix, int pathHash,String path) throws SQLException { 158 String sql="select rdr_id,rdr_name,rdr_type,rdr_length,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data from "+prefix+"attrs where rdr_path_hash=? and rdr_path=? order by rdr_name"; 159 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 160 stat.setInt(1, pathHash); 161 stat.setString(2, path); 162 log(sql,pathHash+"",path); 163 164 ResultSet rs = stat.executeQuery(); 165 166 try{ 167 List attrs=new ArrayList(); 168 //hashCode=(path+name).hashCode(); 169 while(rs.next()) { 170 attrs.add( new Attr( 171 rs.getInt(1), 172 rs.getString(2), 173 path, 174 true, 175 rs.getInt(3), 176 rs.getInt(4), 177 rs.getTimestamp(5,getCalendar()).getTime(), 178 rs.getShort(6), 179 rs.getShort(7), 180 rs.getInt(8) 181 )); 182 } 183 return attrs; 184 } 185 finally { 186 DBUtil.closeEL(rs); 187 DBUtil.closeEL(stat); 188 } 189 } 190 191 @Override 192 public void create(DatasourceConnection dc, String prefix, int fullPatHash,int pathHash,String path, String name, int type) throws SQLException { 193 String sql="insert into "+prefix+"attrs(rdr_type,rdr_path,rdr_name,rdr_full_path_hash,rdr_path_hash,rdr_last_modified,rdr_mode,rdr_attributes,rdr_data,rdr_length) " + 194 "values(?,?,?,?,?,?,?,?,?,?)"; 195 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 196 log(sql); 197 stat.setInt(1,type); 198 stat.setString(2, path); 199 stat.setString(3, name); 200 stat.setInt(4, fullPatHash); 201 stat.setInt(5, pathHash); 202 stat.setTimestamp(6, new Timestamp(System.currentTimeMillis()),getCalendar()); 203 stat.setInt(7, DEFAULT_MODE); 204 stat.setInt(8, DEFAULT_ATTRS); 205 stat.setInt(9, 0); 206 stat.setInt(10, 0); 207 try{ 208 stat.executeUpdate(); 209 } 210 finally { 211 DBUtil.closeEL(stat); 212 } 213 } 214 215 @Override 216 public boolean delete(DatasourceConnection dc, String prefix, Attr attr) throws SQLException { 217 boolean rst=false; 218 if(attr!=null){ 219 String sql="delete from "+prefix+"attrs where rdr_id=?"; 220 log(sql,attr.getId()+""); 221 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 222 stat.setInt(1,attr.getId()); 223 224 try{ 225 rst = stat.executeUpdate()>0; 226 } 227 finally { 228 DBUtil.closeEL(stat); 229 } 230 231 if(attr.getData()>0) { 232 sql="delete from "+prefix+"data where rdr_id=?"; 233 log(sql,attr.getData()+""); 234 stat = dc.getConnection().prepareStatement(sql); 235 stat.setInt(1,attr.getData()); 236 try{ 237 stat.executeUpdate(); 238 } 239 finally { 240 DBUtil.closeEL(stat); 241 } 242 } 243 } 244 return rst; 245 } 246 247 @Override 248 public InputStream getInputStream(DatasourceConnection dc, String prefix, Attr attr) throws SQLException, IOException { 249 if(attr==null || attr.getData()==0) return new ByteArrayInputStream(new byte[0]); 250 251 String sql="select rdr_data from "+prefix+"data where rdr_id=?"; 252 log(sql,attr.getData()+""); 253 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 254 stat.setInt(1,attr.getData()); 255 256 ResultSet rs=null; 257 try{ 258 rs = stat.executeQuery(); 259 if(!rs.next()){ 260 throw new IOException("can't read data from "+attr.getParent()+attr.getName()); 261 } 262 return rs.getBlob(1).getBinaryStream(); 263 } 264 finally { 265 DBUtil.closeEL(rs); 266 DBUtil.closeEL(stat); 267 } 268 } 269 270 public void write(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException { 271 if(attr.getData()==0) writeInsert(dc, prefix, attr, is); 272 else writeUpdate(dc, prefix, attr, is, append); 273 } 274 275 276 private void writeUpdate(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException { 277 278 //update rdr_data set rdr_data = concat(rdr_data,'susi') where rdr_id = 1 279 280 String sql=append? 281 "update "+prefix+"data set rdr_data=concat(rdr_data,?) where rdr_id=?": 282 "update "+prefix+"data set rdr_data=? where rdr_id=?"; 283 log(sql); 284 PreparedStatement stat1=null; 285 PreparedStatement stat2=null; 286 PreparedStatement stat3=null; 287 ResultSet rs=null; 288 try{ 289 //Connection conn = dc.getConnection(); 290 stat1 = dc.getConnection().prepareStatement(sql); 291 stat1.setBinaryStream(1, is,-1); 292 stat1.setInt(2, attr.getData()); 293 stat1.executeUpdate(); 294 295 // select 296 sql="select Length(rdr_data) as DataLen from "+prefix+"data where rdr_id=?"; 297 log(sql); 298 stat2=dc.getConnection().prepareStatement(sql); 299 stat2.setInt(1, attr.getData()); 300 rs=stat2.executeQuery(); 301 302 if(rs.next()){ 303 sql="update "+prefix+"attrs set rdr_length=? where rdr_id=?"; 304 log(sql); 305 stat3 = dc.getConnection().prepareStatement(sql); 306 stat3.setInt(1, rs.getInt(1)); 307 stat3.setInt(2, attr.getId()); 308 stat3.executeUpdate(); 309 } 310 } 311 finally { 312 DBUtil.closeEL(stat1); 313 } 314 } 315 316 private void writeInsert(DatasourceConnection dc, String prefix, Attr attr, InputStream is) throws SQLException { 317 318 319 PreparedStatement stat1=null; 320 Statement stat2=null; 321 PreparedStatement stat3=null; 322 ResultSet rs=null; 323 try{ 324 // Insert 325 String sql="insert into "+prefix+"data (rdr_data) values(?)"; 326 log(sql); 327 Connection conn = dc.getConnection(); 328 stat1 = dc.getConnection().prepareStatement(sql); 329 stat1.setBinaryStream(1, is,-1); 330 stat1.execute(); 331 332 // select 333 sql="select rdr_id,Length(rdr_data) as DataLen from "+prefix+"data order by rdr_id desc LIMIT 1"; 334 log(sql); 335 stat2=conn.createStatement(); 336 rs=stat2.executeQuery(sql); 337 338 // update 339 if(rs.next()){ 340 341 sql="update "+prefix+"attrs set rdr_data=?,rdr_length=? where rdr_id=?"; 342 log(sql); 343 stat3 = dc.getConnection().prepareStatement(sql); 344 stat3.setInt(1, rs.getInt(1)); 345 stat3.setInt(2,rs.getInt(2)); 346 stat3.setInt(3, attr.getId()); 347 stat3.executeUpdate(); 348 } 349 350 } 351 finally { 352 DBUtil.closeEL(rs); 353 DBUtil.closeEL(stat1); 354 DBUtil.closeEL(stat2); 355 } 356 } 357 358 359 360 361 @Override 362 public void setLastModified(DatasourceConnection dc, String prefix, Attr attr, long time) throws SQLException { 363 String sql="update "+prefix+"attrs set rdr_last_modified=? where rdr_id=?"; 364 log(sql); 365 PreparedStatement stat=null; 366 try{ 367 stat = dc.getConnection().prepareStatement(sql); 368 stat.setTimestamp(1, new Timestamp(time),getCalendar()); 369 stat.setInt(2, attr.getId()); 370 stat.executeUpdate(); 371 } 372 finally { 373 DBUtil.closeEL(stat); 374 } 375 } 376 377 378 @Override 379 public void setMode(DatasourceConnection dc,String prefix, Attr attr, int mode) throws SQLException { 380 String sql="update "+prefix+"attrs set rdr_mode=? where rdr_id=?"; 381 log(sql); 382 PreparedStatement stat=null; 383 try{ 384 stat = dc.getConnection().prepareStatement(sql); 385 stat.setInt(1, mode); 386 stat.setInt(2, attr.getId()); 387 stat.executeUpdate(); 388 } 389 finally { 390 DBUtil.closeEL(stat); 391 } 392 } 393 394 395 @Override 396 public void setAttributes(DatasourceConnection dc,String prefix, Attr attr, int attributes) throws SQLException { 397 String sql="update "+prefix+"attrs set rdr_attributes=? where rdr_id=?"; 398 log(sql); 399 PreparedStatement stat=null; 400 try{ 401 stat = dc.getConnection().prepareStatement(sql); 402 stat.setInt(1, attributes); 403 stat.setInt(2, attr.getId()); 404 stat.executeUpdate(); 405 } 406 finally { 407 DBUtil.closeEL(stat); 408 } 409 } 410 411 @Override 412 public boolean concatSupported() { 413 return true; 414 } 415}