Source code

001/*
002 *  Copyright 2017 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.datasourcesexplorer;
017
018import java.sql.Connection;
019import java.sql.DatabaseMetaData;
020import java.sql.PreparedStatement;
021import java.sql.ResultSet;
022import java.sql.ResultSetMetaData;
023import java.sql.SQLException;
024import java.util.ArrayList;
025import java.util.HashMap;
026import java.util.HashSet;
027import java.util.List;
028import java.util.Map;
029import java.util.Set;
030
031import org.apache.avalon.framework.parameters.Parameters;
032import org.apache.avalon.framework.service.ServiceException;
033import org.apache.avalon.framework.service.ServiceManager;
034import org.apache.cocoon.ProcessingException;
035import org.apache.cocoon.acting.ServiceableAction;
036import org.apache.cocoon.environment.ObjectModelHelper;
037import org.apache.cocoon.environment.Redirector;
038import org.apache.cocoon.environment.Request;
039import org.apache.cocoon.environment.SourceResolver;
040import org.apache.commons.lang3.StringUtils;
041
042import org.ametys.core.cocoon.JSonReader;
043import org.ametys.core.datasource.ConnectionHelper;
044import org.ametys.core.datasource.SQLDataSourceManager;
045import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint;
046import org.ametys.core.util.JSONUtils;
047
048/**
049 * Generates the sql data 
050 */
051public class GetSQLData extends ServiceableAction
052{
053    private JSONUtils _jsonUtils;
054    private SQLDatabaseTypeExtensionPoint _sqlDatabaseTypeExtensionPoint;
055    private SQLDataSourceManager _sqlDataSourceManager;
056
057    @Override
058    public void service(ServiceManager smanager) throws ServiceException
059    {
060        super.service(smanager);
061        _jsonUtils = (JSONUtils) smanager.lookup(JSONUtils.ROLE);
062        _sqlDatabaseTypeExtensionPoint = (SQLDatabaseTypeExtensionPoint) smanager.lookup(SQLDatabaseTypeExtensionPoint.ROLE);
063        _sqlDataSourceManager = (SQLDataSourceManager) manager.lookup(SQLDataSourceManager.ROLE);
064    }
065    
066    public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String source, Parameters parameters) throws Exception
067    {
068        Map<String, Object> results = new HashMap<>();
069        
070        Request request = ObjectModelHelper.getRequest(objectModel);
071        request.setAttribute(JSonReader.OBJECT_TO_READ, results);
072        
073        String datasourceId = request.getParameter("datasourceId");
074        String tableName = request.getParameter("sqlTableName");
075        String limit = StringUtils.defaultString(request.getParameter("limit"), "10");
076        String offset = StringUtils.defaultString(request.getParameter("start"), "0");
077        String whereCondition = _getWhereCondition(request);
078        String sortCondition = _getSortCondition(request);
079        
080        Connection connection = null;
081        PreparedStatement stmt = null;
082        ResultSet rs = null;
083        try
084        {
085            connection = ConnectionHelper.getConnection(datasourceId);
086            String dbType = ConnectionHelper.getDatabaseType(connection);
087
088            String escapedTableName = _sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, tableName);
089            
090            // Count
091            stmt = connection.prepareStatement("SELECT count(*) FROM " + escapedTableName + whereCondition);
092            rs = stmt.executeQuery();
093
094            rs.next();
095            results.put("total", rs.getString(1));
096            
097            if (rs.getInt(1) < Integer.parseInt(offset))
098            {
099                offset = "0";
100            }
101            
102            ConnectionHelper.cleanup(rs);
103            ConnectionHelper.cleanup(stmt);
104            
105            stmt = connection.prepareStatement(_sqlDatabaseTypeExtensionPoint.languageLimitQuery(dbType, "SELECT * FROM " + escapedTableName + whereCondition + sortCondition, limit, offset));
106            rs = stmt.executeQuery();
107            
108
109            // Table structure
110            ResultSetMetaData metaData = rs.getMetaData();
111            results.put("meta", _getDataStructure(_getPrimaryKeys(connection.getMetaData(), tableName), metaData, datasourceId));
112
113            int columnCount = metaData.getColumnCount();
114            
115            List<Map<String, Object>> lines = new ArrayList<>();
116            results.put("data", lines);
117            while (rs.next())
118            {
119                Map<String, Object> line = new HashMap<>();
120                for (int i = 1; i <= columnCount; i++)
121                {
122                    String value = rs.getString(i);
123                    if (metaData.getColumnDisplaySize(i) > 255 && value != null && value.length() > 255)
124                    {
125                        line.put(metaData.getColumnName(i), value.substring(0, 255) + "…");
126                    }
127                    else
128                    {
129                        line.put(metaData.getColumnName(i), value);
130                    }
131                }
132                lines.add(line);
133            }
134        }
135        catch (Exception e)
136        {
137            throw new ProcessingException("An error occurred while getting SQL table '" + tableName + "' data for datasource '" + datasourceId + "'", e);
138        }
139        finally
140        {
141            ConnectionHelper.cleanup(rs);
142            ConnectionHelper.cleanup(stmt);
143            ConnectionHelper.cleanup(connection);
144        }
145        
146        results.put("success", "true");
147        
148        return EMPTY_MAP;
149    }
150    
151    private Map<String, Object> _getDataStructure(Set<String> primaryKeys, ResultSetMetaData metaData, String datasourceId) throws SQLException
152    {
153        Map<String, Object> results = new HashMap<>();
154        
155        results.put("datasource", _sqlDataSourceManager.getDataSourceDefinition(datasourceId).getName());
156
157        List<Map<String, String>> columns = new ArrayList<>();
158        results.put("columns", columns);
159
160        int columnCount = metaData.getColumnCount();
161        for (int i = 1; i <= columnCount; i++)
162        {
163            Map<String, String> column = new HashMap<>();
164            columns.add(column);
165            
166            column.put("name", metaData.getColumnName(i));
167            column.put("displaySize", Integer.toString(metaData.getColumnDisplaySize(i)));
168            column.put("label", metaData.getColumnLabel(i) 
169                    + "<br/>" +  metaData.getColumnTypeName(i) + " (" + metaData.getPrecision(i) + ")" 
170                    + (metaData.isNullable(i) == ResultSetMetaData.columnNoNulls ? " NOT NULL" : "")
171                    + (primaryKeys.contains(metaData.getColumnName(i)) ? "<br/>Primary Key" : ""));
172            column.put("type", metaData.getColumnTypeName(i));
173            column.put("isPrimary", Boolean.toString(primaryKeys.contains(metaData.getColumnName(i))));
174            column.put("isNullable", Boolean.toString(metaData.isNullable(i) == ResultSetMetaData.columnNoNulls));
175        }
176
177        results.put("columnsHashcode", columns.hashCode());
178                
179        return results;
180    }
181
182    private Set<String> _getPrimaryKeys(DatabaseMetaData databaseMetaData, String tableName) throws SQLException
183    {
184        Set<String> primaryKey = new HashSet<>();
185        ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);
186        while (primaryKeys.next())
187        {
188            primaryKey.add(primaryKeys.getString("COLUMN_NAME"));
189        }
190        return primaryKey;
191    }
192
193    private String _getWhereCondition(Request request)
194    {
195        String whereCondition = StringUtils.defaultString(request.getParameter("where"));
196        if (StringUtils.isNotBlank(whereCondition))
197        {
198            whereCondition = " WHERE " + whereCondition;
199        }
200        return whereCondition;
201    }
202
203    private String _getSortCondition(Request request)
204    {
205        String sortCondition = StringUtils.defaultString(request.getParameter("sort"));
206        if (StringUtils.isNotBlank(sortCondition))
207        {
208            List<String> sorts = new ArrayList<>();
209            
210            List<Object> sortInfos = _jsonUtils.convertJsonToList(sortCondition);
211            for (Object sortInfo : sortInfos)
212            {
213                @SuppressWarnings("unchecked")
214                Map<String, String> sort = (Map<String, String>) sortInfo;
215                sorts.add(sort.get("property") + " " + sort.get("direction"));
216            }
217            
218            if (sorts.size() > 0)
219            {
220                sortCondition = " ORDER BY " + StringUtils.join(sorts, ", ");
221            }
222            else
223            {
224                sortCondition = "";
225            }
226        }
227        return sortCondition;
228    }
229}