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    }