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