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 @Override 104 public Attr getAttr(DatasourceConnection dc, String prefix, int fullPathHash,String path, String name) throws SQLException { 105 // ROOT 106 if(StringUtil.isEmpty(path)) 107 return ATTR_ROOT; 108 109 110 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=?"; 111 PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql); 112 stat.setInt(1, fullPathHash); 113 stat.setString(2, path); 114 stat.setString(3, name); 115 log(sql,fullPathHash+"",path,name); 116 117 ResultSet rs = stat.executeQuery(); 118 try{ 119 if(!rs.next())return null; 120 121 return new Attr( 122 rs.getInt(1), 123 name,path,true, 124 rs.getInt(2), 125 rs.getInt(3), 126 rs.getTimestamp(4,getCalendar()).getTime(), 127 rs.getShort(5), 128 rs.getShort(6), 129 rs.getInt(7)); 130 } 131 finally { 132 DBUtil.closeEL(rs); 133 //DBUtil.closeEL(stat); 134 } 135 } 136 137 138 @Override 139 public List getAttrs(DatasourceConnection dc, String prefix, int pathHash,String path) throws SQLException { 140 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"; 141 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 142 stat.setInt(1, pathHash); 143 stat.setString(2, path); 144 log(sql,pathHash+"",path); 145 146 ResultSet rs = stat.executeQuery(); 147 148 try{ 149 List attrs=new ArrayList(); 150 //hashCode=(path+name).hashCode(); 151 while(rs.next()) { 152 attrs.add( new Attr( 153 rs.getInt(1), 154 rs.getString(2), 155 path, 156 true, 157 rs.getInt(3), 158 rs.getInt(4), 159 rs.getTimestamp(5,getCalendar()).getTime(), 160 rs.getShort(6), 161 rs.getShort(7), 162 rs.getInt(8) 163 )); 164 } 165 return attrs; 166 } 167 finally { 168 DBUtil.closeEL(rs); 169 DBUtil.closeEL(stat); 170 } 171 } 172 173 @Override 174 public void create(DatasourceConnection dc, String prefix, int fullPatHash,int pathHash,String path, String name, int type) throws SQLException { 175 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) " + 176 "values(?,?,?,?,?,?,?,?,?,?)"; 177 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 178 log(sql); 179 stat.setInt(1,type); 180 stat.setString(2, path); 181 stat.setString(3, name); 182 stat.setInt(4, fullPatHash); 183 stat.setInt(5, pathHash); 184 stat.setTimestamp(6, new Timestamp(System.currentTimeMillis()),getCalendar()); 185 stat.setInt(7, DEFAULT_MODE); 186 stat.setInt(8, DEFAULT_ATTRS); 187 stat.setInt(9, 0); 188 stat.setInt(10, 0); 189 try{ 190 stat.executeUpdate(); 191 } 192 finally { 193 DBUtil.closeEL(stat); 194 } 195 } 196 197 @Override 198 public boolean delete(DatasourceConnection dc, String prefix, Attr attr) throws SQLException { 199 boolean rst=false; 200 if(attr!=null){ 201 String sql="delete from "+prefix+"attrs where rdr_id=?"; 202 log(sql,attr.getId()+""); 203 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 204 stat.setInt(1,attr.getId()); 205 206 try{ 207 rst = stat.executeUpdate()>0; 208 } 209 finally { 210 DBUtil.closeEL(stat); 211 } 212 213 if(attr.getData()>0) { 214 sql="delete from "+prefix+"data where rdr_id=?"; 215 log(sql,attr.getData()+""); 216 stat = dc.getConnection().prepareStatement(sql); 217 stat.setInt(1,attr.getData()); 218 try{ 219 stat.executeUpdate(); 220 } 221 finally { 222 DBUtil.closeEL(stat); 223 } 224 } 225 } 226 return rst; 227 } 228 229 @Override 230 public InputStream getInputStream(DatasourceConnection dc, String prefix, Attr attr) throws SQLException, IOException { 231 if(attr==null || attr.getData()==0) return new ByteArrayInputStream(new byte[0]); 232 233 String sql="select rdr_data from "+prefix+"data where rdr_id=?"; 234 log(sql,attr.getData()+""); 235 PreparedStatement stat = dc.getConnection().prepareStatement(sql); 236 stat.setInt(1,attr.getData()); 237 238 ResultSet rs=null; 239 try{ 240 rs = stat.executeQuery(); 241 if(!rs.next()){ 242 throw new IOException("can't read data from "+attr.getParent()+attr.getName()); 243 } 244 return rs.getBlob(1).getBinaryStream(); 245 } 246 finally { 247 DBUtil.closeEL(rs); 248 DBUtil.closeEL(stat); 249 } 250 } 251 252 public void write(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException { 253 if(attr.getData()==0) writeInsert(dc, prefix, attr, is); 254 else writeUpdate(dc, prefix, attr, is, append); 255 } 256 257 258 private void writeUpdate(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException { 259 260 //update rdr_data set rdr_data = concat(rdr_data,'susi') where rdr_id = 1 261 262 String sql=append? 263 "update "+prefix+"data set rdr_data=concat(rdr_data,?) where rdr_id=?": 264 "update "+prefix+"data set rdr_data=? where rdr_id=?"; 265 log(sql); 266 PreparedStatement stat1=null; 267 PreparedStatement stat2=null; 268 PreparedStatement stat3=null; 269 ResultSet rs=null; 270 try{ 271 //Connection conn = dc.getConnection(); 272 stat1 = dc.getConnection().prepareStatement(sql); 273 stat1.setBinaryStream(1, is,-1); 274 stat1.setInt(2, attr.getData()); 275 stat1.executeUpdate(); 276 277 // select 278 sql="select Length(rdr_data) as DataLen from "+prefix+"data where rdr_id=?"; 279 log(sql); 280 stat2=dc.getConnection().prepareStatement(sql); 281 stat2.setInt(1, attr.getData()); 282 rs=stat2.executeQuery(); 283 284 if(rs.next()){ 285 sql="update "+prefix+"attrs set rdr_length=? where rdr_id=?"; 286 log(sql); 287 stat3 = dc.getConnection().prepareStatement(sql); 288 stat3.setInt(1, rs.getInt(1)); 289 stat3.setInt(2, attr.getId()); 290 stat3.executeUpdate(); 291 } 292 } 293 finally { 294 DBUtil.closeEL(stat1); 295 } 296 } 297 298 private void writeInsert(DatasourceConnection dc, String prefix, Attr attr, InputStream is) throws SQLException { 299 300 301 PreparedStatement stat1=null; 302 Statement stat2=null; 303 PreparedStatement stat3=null; 304 ResultSet rs=null; 305 try{ 306 // Insert 307 String sql="insert into "+prefix+"data (rdr_data) values(?)"; 308 log(sql); 309 Connection conn = dc.getConnection(); 310 stat1 = dc.getConnection().prepareStatement(sql); 311 stat1.setBinaryStream(1, is,-1); 312 stat1.execute(); 313 314 // select 315 sql="select rdr_id,Length(rdr_data) as DataLen from "+prefix+"data order by rdr_id desc LIMIT 1"; 316 log(sql); 317 stat2=conn.createStatement(); 318 rs=stat2.executeQuery(sql); 319 320 // update 321 if(rs.next()){ 322 323 sql="update "+prefix+"attrs set rdr_data=?,rdr_length=? where rdr_id=?"; 324 log(sql); 325 stat3 = dc.getConnection().prepareStatement(sql); 326 stat3.setInt(1, rs.getInt(1)); 327 stat3.setInt(2,rs.getInt(2)); 328 stat3.setInt(3, attr.getId()); 329 stat3.executeUpdate(); 330 } 331 332 } 333 finally { 334 DBUtil.closeEL(rs); 335 DBUtil.closeEL(stat1); 336 DBUtil.closeEL(stat2); 337 } 338 } 339 340 341 342 343 @Override 344 public void setLastModified(DatasourceConnection dc, String prefix, Attr attr, long time) throws SQLException { 345 String sql="update "+prefix+"attrs set rdr_last_modified=? where rdr_id=?"; 346 log(sql); 347 PreparedStatement stat=null; 348 try{ 349 stat = dc.getConnection().prepareStatement(sql); 350 stat.setTimestamp(1, new Timestamp(time),getCalendar()); 351 stat.setInt(2, attr.getId()); 352 stat.executeUpdate(); 353 } 354 finally { 355 DBUtil.closeEL(stat); 356 } 357 } 358 359 360 @Override 361 public void setMode(DatasourceConnection dc,String prefix, Attr attr, int mode) throws SQLException { 362 String sql="update "+prefix+"attrs set rdr_mode=? where rdr_id=?"; 363 log(sql); 364 PreparedStatement stat=null; 365 try{ 366 stat = dc.getConnection().prepareStatement(sql); 367 stat.setInt(1, mode); 368 stat.setInt(2, attr.getId()); 369 stat.executeUpdate(); 370 } 371 finally { 372 DBUtil.closeEL(stat); 373 } 374 } 375 376 377 @Override 378 public void setAttributes(DatasourceConnection dc,String prefix, Attr attr, int attributes) throws SQLException { 379 String sql="update "+prefix+"attrs set rdr_attributes=? where rdr_id=?"; 380 log(sql); 381 PreparedStatement stat=null; 382 try{ 383 stat = dc.getConnection().prepareStatement(sql); 384 stat.setInt(1, attributes); 385 stat.setInt(2, attr.getId()); 386 stat.executeUpdate(); 387 } 388 finally { 389 DBUtil.closeEL(stat); 390 } 391 } 392 393 @Override 394 public boolean concatSupported() { 395 return true; 396 } 397 }