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