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