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    }