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