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