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            @Override
104            public Attr getAttr(DatasourceConnection dc, String prefix, int fullPathHash,String path, String name) throws SQLException {
105                    // ROOT
106                    if(StringUtil.isEmpty(path))
107                            return ATTR_ROOT;
108                    
109                    
110                    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=?";
111                    PreparedStatement stat = prepareStatement(dc, sql);//dc.getConnection().prepareStatement(sql);
112                    stat.setInt(1, fullPathHash);
113                    stat.setString(2, path);
114                    stat.setString(3, name);
115                    log(sql,fullPathHash+"",path,name);
116                    
117                            ResultSet rs = stat.executeQuery();
118                    try{
119                            if(!rs.next())return null;
120                            
121                            return new Attr(
122                                                    rs.getInt(1),
123                                                    name,path,true,
124                                                    rs.getInt(2),
125                                                    rs.getInt(3),
126                                                    rs.getTimestamp(4,getCalendar()).getTime(),
127                                                    rs.getShort(5),
128                                                    rs.getShort(6),
129                                                    rs.getInt(7));
130                    }
131                    finally {
132                            DBUtil.closeEL(rs);
133                            //DBUtil.closeEL(stat);
134                    }
135            }
136    
137    
138            @Override
139            public List getAttrs(DatasourceConnection dc, String prefix, int pathHash,String path) throws SQLException {
140                    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";
141                    PreparedStatement stat =  dc.getConnection().prepareStatement(sql);
142                    stat.setInt(1, pathHash);
143                    stat.setString(2, path);
144                    log(sql,pathHash+"",path);
145                    
146                    ResultSet rs = stat.executeQuery();
147                    
148                    try{
149                            List attrs=new ArrayList();
150                            //hashCode=(path+name).hashCode();
151                            while(rs.next()) {
152                                    attrs.add( new Attr(
153                                                    rs.getInt(1),
154                                                    rs.getString(2),
155                                                    path,
156                                                    true,
157                                                    rs.getInt(3),
158                                                    rs.getInt(4),
159                                                    rs.getTimestamp(5,getCalendar()).getTime(),
160                                                    rs.getShort(6),
161                                                    rs.getShort(7),
162                                                    rs.getInt(8)
163                                            ));
164                            }
165                            return attrs;
166                    }
167                    finally {
168                            DBUtil.closeEL(rs);
169                            DBUtil.closeEL(stat);
170                    }
171            }
172    
173            @Override
174            public void create(DatasourceConnection dc, String prefix, int fullPatHash,int pathHash,String path, String name, int type) throws SQLException {
175                    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) " +
176                                    "values(?,?,?,?,?,?,?,?,?,?)";
177                    PreparedStatement stat = dc.getConnection().prepareStatement(sql);
178                    log(sql);
179                    stat.setInt(1,type);
180                    stat.setString(2, path);
181                    stat.setString(3, name);
182                    stat.setInt(4, fullPatHash);
183                    stat.setInt(5, pathHash);
184                    stat.setTimestamp(6, new Timestamp(System.currentTimeMillis()),getCalendar());
185                    stat.setInt(7, DEFAULT_MODE);
186                    stat.setInt(8, DEFAULT_ATTRS);
187                    stat.setInt(9, 0);
188                    stat.setInt(10, 0);
189                    try{
190                            stat.executeUpdate();
191                    }
192                    finally {
193                            DBUtil.closeEL(stat);
194                    }
195            }
196    
197            @Override
198            public boolean delete(DatasourceConnection dc, String prefix, Attr attr) throws SQLException {
199                    boolean rst=false;
200                    if(attr!=null){
201                            String sql="delete from "+prefix+"attrs where rdr_id=?";
202                            log(sql,attr.getId()+"");
203                            PreparedStatement stat = dc.getConnection().prepareStatement(sql);
204                            stat.setInt(1,attr.getId());
205                            
206                            try{
207                                    rst = stat.executeUpdate()>0;
208                            }
209                            finally {
210                                    DBUtil.closeEL(stat);
211                            }
212                            
213                            if(attr.getData()>0) {
214                                    sql="delete from "+prefix+"data where rdr_id=?";
215                                    log(sql,attr.getData()+"");
216                                    stat = dc.getConnection().prepareStatement(sql);
217                                    stat.setInt(1,attr.getData());
218                                    try{
219                                            stat.executeUpdate();
220                                    }
221                                    finally {
222                                            DBUtil.closeEL(stat);
223                                    }
224                            }
225                    }
226                    return rst;
227            }
228            
229            @Override
230            public InputStream getInputStream(DatasourceConnection dc, String prefix, Attr attr) throws SQLException, IOException {
231                    if(attr==null || attr.getData()==0) return new ByteArrayInputStream(new byte[0]);
232                    
233                    String sql="select rdr_data from "+prefix+"data where rdr_id=?";
234                    log(sql,attr.getData()+"");
235                    PreparedStatement stat = dc.getConnection().prepareStatement(sql);
236                    stat.setInt(1,attr.getData());
237    
238                    ResultSet rs=null;
239                    try{
240                            rs = stat.executeQuery();
241                            if(!rs.next()){
242                                    throw new IOException("can't read data from "+attr.getParent()+attr.getName());
243                            }
244                            return rs.getBlob(1).getBinaryStream();
245                    }
246                    finally {
247                            DBUtil.closeEL(rs);
248                            DBUtil.closeEL(stat);
249                    }
250            }
251    
252            public void write(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException {
253                    if(attr.getData()==0) writeInsert(dc, prefix, attr, is);
254                    else writeUpdate(dc, prefix, attr, is, append);
255            }
256            
257    
258            private void writeUpdate(DatasourceConnection dc, String prefix, Attr attr, InputStream is, boolean append) throws SQLException {
259                    
260                    //update rdr_data set rdr_data = concat(rdr_data,'susi') where rdr_id = 1
261                    
262                    String sql=append?
263                                    "update "+prefix+"data set rdr_data=concat(rdr_data,?) where rdr_id=?":
264                                    "update "+prefix+"data set rdr_data=? where rdr_id=?";
265                    log(sql);
266                    PreparedStatement stat1=null;
267                    PreparedStatement stat2=null;
268                    PreparedStatement stat3=null;
269                    ResultSet rs=null;
270                    try{
271                            //Connection conn = dc.getConnection();
272                            stat1 = dc.getConnection().prepareStatement(sql);
273                            stat1.setBinaryStream(1, is,-1);
274                            stat1.setInt(2, attr.getData());
275                            stat1.executeUpdate();
276                            
277                            // select
278                            sql="select Length(rdr_data) as DataLen from "+prefix+"data where rdr_id=?";
279                            log(sql);
280                            stat2=dc.getConnection().prepareStatement(sql);
281                            stat2.setInt(1, attr.getData());
282                            rs=stat2.executeQuery();
283                            
284                            if(rs.next()){  
285                                    sql="update "+prefix+"attrs set rdr_length=? where rdr_id=?";
286                                    log(sql);
287                                    stat3 = dc.getConnection().prepareStatement(sql);
288                                    stat3.setInt(1, rs.getInt(1));
289                                    stat3.setInt(2, attr.getId());
290                                    stat3.executeUpdate();
291                            }
292                    }
293                    finally {
294                            DBUtil.closeEL(stat1);
295                    }
296            }
297    
298            private void writeInsert(DatasourceConnection dc, String prefix, Attr attr, InputStream is) throws SQLException {
299                    
300    
301                    PreparedStatement stat1=null;
302                    Statement stat2=null;
303                    PreparedStatement stat3=null;
304                    ResultSet rs=null;
305                    try{
306                            // Insert
307                            String sql="insert into "+prefix+"data (rdr_data) values(?)";
308                            log(sql);
309                            Connection conn = dc.getConnection();
310                            stat1 = dc.getConnection().prepareStatement(sql);
311                            stat1.setBinaryStream(1, is,-1);
312                            stat1.execute();
313                            
314                            // select
315                            sql="select rdr_id,Length(rdr_data) as DataLen from "+prefix+"data order by rdr_id desc LIMIT 1";
316                            log(sql);
317                            stat2=conn.createStatement();
318                            rs=stat2.executeQuery(sql);
319                            
320                            // update
321                            if(rs.next()){
322                                    
323                                    sql="update "+prefix+"attrs set rdr_data=?,rdr_length=? where rdr_id=?";
324                                    log(sql);
325                                    stat3 = dc.getConnection().prepareStatement(sql);
326                                    stat3.setInt(1, rs.getInt(1));
327                                    stat3.setInt(2,rs.getInt(2));
328                                    stat3.setInt(3, attr.getId());
329                                    stat3.executeUpdate();
330                            }
331                             
332                    }
333                    finally {
334                            DBUtil.closeEL(rs);
335                            DBUtil.closeEL(stat1);
336                            DBUtil.closeEL(stat2);
337                    }
338            }
339            
340    
341    
342    
343            @Override
344            public void setLastModified(DatasourceConnection dc, String prefix, Attr attr, long time) throws SQLException {
345                    String sql="update "+prefix+"attrs set rdr_last_modified=? where rdr_id=?";
346                    log(sql);
347                    PreparedStatement stat=null;
348                    try{
349                            stat = dc.getConnection().prepareStatement(sql);
350                            stat.setTimestamp(1, new Timestamp(time),getCalendar());
351                            stat.setInt(2, attr.getId());
352                            stat.executeUpdate();
353                    }
354                    finally {
355                            DBUtil.closeEL(stat);
356                    }
357            }
358    
359    
360            @Override
361            public void setMode(DatasourceConnection dc,String prefix, Attr attr, int mode) throws SQLException {
362                    String sql="update "+prefix+"attrs set rdr_mode=? where rdr_id=?";
363                    log(sql);
364                    PreparedStatement stat=null;
365                    try{
366                            stat = dc.getConnection().prepareStatement(sql);
367                            stat.setInt(1, mode);
368                            stat.setInt(2, attr.getId());
369                            stat.executeUpdate();
370                    }
371                    finally {
372                            DBUtil.closeEL(stat);
373                    }
374            }
375    
376    
377            @Override
378            public void setAttributes(DatasourceConnection dc,String prefix, Attr attr, int attributes) throws SQLException {
379                    String sql="update "+prefix+"attrs set rdr_attributes=? where rdr_id=?";
380                    log(sql);
381                    PreparedStatement stat=null;
382                    try{
383                            stat = dc.getConnection().prepareStatement(sql);
384                            stat.setInt(1, attributes);
385                            stat.setInt(2, attr.getId());
386                            stat.executeUpdate();
387                    }
388                    finally {
389                            DBUtil.closeEL(stat);
390                    }
391            }
392    
393            @Override
394            public boolean concatSupported() {
395                    return true;
396            }
397    }