001/*
002 * (C) Copyright 2006-2011 Nuxeo SA (http://nuxeo.com/) and others.
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 *
016 * Contributors:
017 *     Florent Guillaume
018 *     Benoit Delbosc
019 */
020
021package org.nuxeo.ecm.core.storage.sql;
022
023import java.sql.Connection;
024import java.sql.DatabaseMetaData;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.sql.Statement;
028import java.util.HashSet;
029import java.util.LinkedList;
030import java.util.List;
031import java.util.Set;
032
033import org.apache.commons.logging.Log;
034import org.apache.commons.logging.LogFactory;
035import org.nuxeo.common.utils.JDBCUtils;
036import org.nuxeo.ecm.core.blob.binary.BinaryManager;
037import org.nuxeo.ecm.core.blob.binary.DefaultBinaryManager;
038import org.nuxeo.runtime.RuntimeServiceEvent;
039import org.nuxeo.runtime.RuntimeServiceListener;
040import org.nuxeo.runtime.api.Framework;
041
042public abstract class DatabaseHelper {
043
044    private static final Log log = LogFactory.getLog(DatabaseHelper.class);
045
046    public static final String DB_PROPERTY = "nuxeo.test.vcs.db";
047
048    public static final String DB_DEFAULT = "H2";
049
050    public static final String DEF_ID_TYPE = "varchar"; // "varchar", "uuid", "sequence"
051
052    public static DatabaseHelper DATABASE;
053
054    public static final String DB_CLASS_NAME_BASE = "org.nuxeo.ecm.core.storage.sql.Database";
055
056    protected static final Class<? extends BinaryManager> defaultBinaryManager = DefaultBinaryManager.class;
057
058    static {
059        setSystemProperty(DB_PROPERTY, DB_DEFAULT);
060        String className = System.getProperty(DB_PROPERTY);
061        if (className.indexOf('.') < 0) {
062            className = DB_CLASS_NAME_BASE + className;
063        }
064        setDatabaseForTests(className);
065    }
066
067    // available for JDBC tests
068    public static final String DRIVER_PROPERTY = "nuxeo.test.vcs.driver";
069
070    // available for JDBC tests
071    public static final String URL_PROPERTY = "nuxeo.test.vcs.url";
072
073    public static final String SERVER_PROPERTY = "nuxeo.test.vcs.server";
074
075    public static final String PORT_PROPERTY = "nuxeo.test.vcs.port";
076
077    public static final String DATABASE_PROPERTY = "nuxeo.test.vcs.database";
078
079    public static final String USER_PROPERTY = "nuxeo.test.vcs.user";
080
081    public static final String PASSWORD_PROPERTY = "nuxeo.test.vcs.password";
082
083    public static final String ID_TYPE_PROPERTY = "nuxeo.test.vcs.idtype";
084
085    protected Error owner;
086
087    public static String setSystemProperty(String name, String def) {
088        String value = System.getProperty(name);
089        if (value == null || value.equals("") || value.equals("${" + name + "}")) {
090            System.setProperty(name, def);
091        }
092        return value;
093    }
094
095    public static String setProperty(String name, String def) {
096        String value = System.getProperty(name);
097        if (value == null || value.equals("") || value.equals("${" + name + "}")) {
098            value = def;
099        }
100        Framework.getProperties().setProperty(name, value);
101        return value;
102    }
103
104    public static final String DEFAULT_DATABASE_NAME = "nuxeojunittests";
105
106    public String databaseName = DEFAULT_DATABASE_NAME;
107
108    public void setDatabaseName(String name) {
109        databaseName = name;
110    }
111
112    /**
113     * Sets the database backend used for VCS unit tests.
114     */
115    public static void setDatabaseForTests(String className) {
116        try {
117            DATABASE = (DatabaseHelper) Class.forName(className).getDeclaredConstructor().newInstance();
118        } catch (ReflectiveOperationException e) {
119            throw new ExceptionInInitializerError("Database class not found: " + className);
120        }
121    }
122
123    /**
124     * Gets a database connection, retrying if the server says it's overloaded.
125     *
126     * @since 5.9.3
127     */
128    public static Connection getConnection(String url, String user, String password) throws SQLException {
129        return JDBCUtils.getConnection(url, user, password);
130    }
131
132    /**
133     * Executes one statement on all the tables in a database.
134     */
135    public static void doOnAllTables(Connection connection, String catalog, String schemaPattern, String statement)
136            throws SQLException {
137        DatabaseMetaData metadata = connection.getMetaData();
138        List<String> tableNames = new LinkedList<>();
139        Set<String> truncateFirst = new HashSet<>();
140        try (ResultSet rs = metadata.getTables(catalog, schemaPattern, "%", new String[] { "TABLE" })) {
141            while (rs.next()) {
142                String tableName = rs.getString("TABLE_NAME");
143                if (tableName.indexOf('$') != -1) {
144                    // skip Oracle 10g flashback/fulltext-index tables
145                    continue;
146                }
147                if (tableName.toLowerCase().startsWith("trace_xe_")) {
148                    // Skip mssql 2012 system table
149                    continue;
150                }
151                if ("sys_config".equals(tableName)) {
152                    // Skip MySQL system table
153                    continue;
154                }
155                if ("ACLR_USER_USERS".equals(tableName)) {
156                    // skip nested table that is dropped by the main table
157                    continue;
158                }
159                if ("ANCESTORS_ANCESTORS".equals(tableName)) {
160                    // skip nested table that is dropped by the main table
161                    continue;
162                }
163                if ("ACLR_MODIFIED".equals(tableName) && DATABASE instanceof DatabaseOracle) {
164                    // global temporary table on Oracle, must TRUNCATE before DROP
165                    truncateFirst.add(tableName);
166                }
167                tableNames.add(tableName);
168            }
169        }
170        // not all databases can cascade on drop
171        // remove hierarchy last because of foreign keys
172        if (tableNames.remove("HIERARCHY")) {
173            tableNames.add("HIERARCHY");
174        }
175        // needed for Azure
176        if (tableNames.remove("NXP_LOGS")) {
177            tableNames.add("NXP_LOGS");
178        }
179        if (tableNames.remove("NXP_LOGS_EXTINFO")) {
180            tableNames.add("NXP_LOGS_EXTINFO");
181        }
182        // PostgreSQL is lowercase
183        if (tableNames.remove("hierarchy")) {
184            tableNames.add("hierarchy");
185        }
186        Statement st = connection.createStatement();
187        for (String tableName : tableNames) {
188            if (truncateFirst.contains(tableName)) {
189                String sql = String.format("TRUNCATE TABLE \"%s\"", tableName);
190                executeSql(st, sql);
191            }
192            String sql = String.format(statement, tableName);
193            executeSql(st, sql);
194        }
195        st.close();
196    }
197
198    protected static void executeSql(Statement st, String sql) throws SQLException {
199        log.trace("SQL: " + sql);
200        st.execute(sql);
201    }
202
203    public void setUp() throws SQLException {
204        setOwner();
205        setDatabaseName(DEFAULT_DATABASE_NAME);
206        setBinaryManager(defaultBinaryManager, "");
207        Framework.addListener(event -> {
208            if (RuntimeServiceEvent.RUNTIME_STOPPED == event.id) {
209                try {
210                    tearDown();
211                } catch (SQLException cause) {
212                    throw new AssertionError("Cannot teardown database", cause);
213                }
214            }
215        });
216    }
217
218    protected void setOwner() {
219        if (owner != null) {
220            Error e = new Error("Second call to setUp() without tearDown()", owner);
221            log.fatal(e.getMessage(), e);
222            throw e;
223        }
224        owner = new Error("Database not released");
225    }
226
227    public void tearDown() throws SQLException {
228        owner = null;
229    }
230
231    public static void setBinaryManager(Class<? extends BinaryManager> binaryManagerClass, String key) {
232        setProperty("nuxeo.test.vcs.binary-manager", binaryManagerClass.getName());
233        setProperty("nuxeo.test.vcs.binary-manager-key", key);
234    }
235
236    public abstract String getDeploymentContrib();
237
238    public abstract RepositoryDescriptor getRepositoryDescriptor();
239
240    /**
241     * For databases that do asynchronous fulltext indexing, sleep a bit.
242     */
243    public void sleepForFulltext() {
244    }
245
246    /**
247     * For databases that fail to cascade deletes beyond a certain depth.
248     */
249    public int getRecursiveRemovalDepthLimit() {
250        return 0;
251    }
252
253    /**
254     * For databases that don't support clustering.
255     */
256    public boolean supportsClustering() {
257        return false;
258    }
259
260    public boolean supportsMultipleFulltextIndexes() {
261        return true;
262    }
263
264    /** @since 11.1 */
265    public boolean supportsFulltextSearch() {
266        return true;
267    }
268
269    public boolean supportsXA() {
270        return true;
271    }
272
273    public boolean supportsSoftDelete() {
274        return false;
275    }
276
277    /**
278     * Whether this database supports "sequence" as an id type.
279     *
280     * @since 5.9.3
281     */
282    public boolean supportsSequenceId() {
283        return false;
284    }
285
286    public boolean supportsArrayColumns() {
287        return false;
288    }
289
290}