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}