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