org.n52.sos.ds.datasource.AbstractSqlServerDatasource Maven / Gradle / Ivy
/*
* Copyright (C) 2012-2022 52°North Spatial Information Research GmbH
*
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License version 2 as published
* by the Free Software Foundation.
*
* If the program is linked with libraries which are licensed under one of
* the following licenses, the combination of the program with the linked
* library is not considered a "derivative work" of the program:
*
* - Apache License, version 2.0
* - Apache Software License, version 1.0
* - GNU Lesser General Public License, version 3
* - Mozilla Public License, versions 1.0, 1.1 and 2.0
* - Common Development and Distribution License (CDDL), version 1.0
*
* Therefore the distribution of the program linked with libraries licensed
* under the aforementioned licenses, is permitted by the copyright holders
* if the distribution is compliant with both the GNU General Public
* License version 2 and the aforementioned licenses.
*
* This program is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
* Public License for more details.
*/
package org.n52.sos.ds.datasource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.hibernate.boot.Metadata;
import org.hibernate.dialect.Dialect;
import org.hibernate.mapping.Table;
import org.n52.faroe.ConfigurationError;
import org.n52.faroe.SettingDefinition;
import org.n52.faroe.settings.StringSettingDefinition;
import org.n52.hibernate.spatial.dialect.sqlserver.TimestampWithTimeZoneSqlServer2012SpatialDialect;
import org.n52.shetland.util.CollectionHelper;
import org.n52.sos.ds.hibernate.util.HibernateConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.collect.Sets;
/**
* Abstract class for MS SQL Server datasources
* @author Carsten Hollmann
* @since 4.2.0
*
*/
public abstract class AbstractSqlServerDatasource extends AbstractHibernateFullDBDatasource {
protected static final String URL_INSTANCE = "instance=";
protected static final String URL_DATABASE_NAME = "databaseName=";
protected static final String INSTANCE_KEY = "jdbc.instance";
protected static final String INSTANCE_TITLE = "SQL Server instance";
protected static final String INSTANCE_DESCRIPTION = "Your SQL Server instance.";
protected static final String SQL_SERVER_DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// TODO adjust make instance optional in regex
protected static final Pattern JDBC_URL_PATTERN = Pattern.compile("^jdbc:sqlserver://([^:]+):([0-9]+)(?:;"
+ URL_INSTANCE + "([^:]+))?;" + URL_DATABASE_NAME + "([^:]+)");
protected static final String USERNAME_DESCRIPTION =
"Your database server user name. The default value for SQL Server is \"sqlserver\".";
protected static final String USERNAME_DEFAULT_VALUE = "sqlserver";
protected static final String PASSWORD_DESCRIPTION =
"Your database server password. The default value is \"sqlserver\".";
protected static final String PASSWORD_DEFAULT_VALUE = USERNAME_DEFAULT_VALUE;
protected static final String HOST_DESCRIPTION =
"Set this to the IP/net location of SQL Server database server. "
+ "The default value for SQL Server is \"localhost\".";
protected static final String PORT_DESCRIPTION =
"Set this to the port number of your SQL Server server. The default value for SQL Server is \"1433\".";
protected static final int PORT_DEFAULT_VALUE = 1433;
protected static final String SCHEMA_DEFAULT_VALUE = "dbo";
private static final boolean PROVIDED_JDBC_DEFAULT_VALUE = true;
private static final Logger LOGGER = LoggerFactory.getLogger(AbstractSqlServerDatasource.class);
private static final int INSTANCE = 3;
private static final int DATABASE = 4;
private static final int PORT = 2;
private static final int HOST = 1;
private static final String ALTER_TABLE = "ALTER TABLE \"";
public AbstractSqlServerDatasource() {
super();
setUsernameDefault(USERNAME_DEFAULT_VALUE);
setUsernameDescription(USERNAME_DESCRIPTION);
setPasswordDefault(PASSWORD_DEFAULT_VALUE);
setPasswordDescription(PASSWORD_DESCRIPTION);
setDatabaseDefault(DATABASE_DEFAULT_VALUE);
setDatabaseDescription(DATABASE_DESCRIPTION);
setHostDefault(HOST_DEFAULT_VALUE);
setHostDescription(HOST_DESCRIPTION);
setPortDefault(PORT_DEFAULT_VALUE);
setPortDescription(PORT_DESCRIPTION);
setSchemaDefault(SCHEMA_DEFAULT_VALUE);
setSchemaDescription(SCHEMA_DESCRIPTION);
setProvidedJdbcDefault(PROVIDED_JDBC_DEFAULT_VALUE);
}
@Override
public Set> getSettingDefinitions() {
Set> settingDefinitions = super.getSettingDefinitions();
return CollectionHelper.union(Sets.> newHashSet(createInstanceDefinition(null)),
settingDefinitions);
}
@Override
public Set> getChangableSettingDefinitions(final Properties current) {
final Map settings = parseDatasourceProperties(current);
return CollectionHelper.union(Sets
.> newHashSet(createInstanceDefinition((String) settings.get(INSTANCE_KEY))),
super.getChangableSettingDefinitions(current));
}
protected StringSettingDefinition createInstanceDefinition(String instanceValue) {
StringSettingDefinition def = new StringSettingDefinition();
def.setGroup(BASE_GROUP);
def.setOrder(2);
def.setKey(INSTANCE_KEY);
def.setTitle(INSTANCE_TITLE);
def.setDescription(INSTANCE_DESCRIPTION);
def.setDefaultValue(instanceValue == null ? "" : instanceValue);
def.setOptional(true);
return def;
}
@Override
protected Dialect createDialect() {
return new TimestampWithTimeZoneSqlServer2012SpatialDialect();
}
@Override
protected String getDriverClass() {
return SQL_SERVER_DRIVER_CLASS;
}
@Override
public boolean checkSchemaCreation(Map settings) {
Connection conn = null;
Statement stmt = null;
try {
conn = openConnection(settings);
stmt = conn.createStatement();
final String schema = (String) settings.get(createSchemaDefinition().getKey());
final String schemaPrefix = schema == null ? "" : "\"" + schema + "\".";
final String testTable = schemaPrefix + "sos_installer_test_table";
final String command =
String.format("BEGIN; " + "IF (OBJECT_ID('%1$s') >0 ) DROP TABLE %1$s; "
+ "CREATE TABLE %1$s (id integer NOT NULL); " + "DROP TABLE %1$s; " + "END;", testTable);
stmt.execute(command);
return true;
} catch (SQLException e) {
return false;
} finally {
close(stmt);
close(conn);
}
}
@Override
protected void validatePrerequisites(Connection con, Metadata metadata, Map settings) {
checkClasspath();
}
private void checkClasspath() throws ConfigurationError {
try {
Class.forName(SQL_SERVER_DRIVER_CLASS);
} catch (ClassNotFoundException e) {
throw new ConfigurationError("SQL Server jar file (sqljdbc.jar) must be "
+ "included in the server classpath. ", e);
}
}
/*
* SQL-Server JDBC String specification:
* https://msdn.microsoft.com/en-us/library/ms378428%28v=sql.110%29.aspx
*
* String url =
* String.format("jdbc:sqlserver://%s:%d;instance=%s;databaseName=%s",
* settings.get(HOST_KEY), settings.get(PORT_KEY),
* settings.get(INSTANCE_KEY), settings.get(DATABASE_KEY));
*/
@Override
protected String toURL(Map settings) {
StringBuilder builder = new StringBuilder("jdbc:sqlserver://");
builder.append(settings.get(HOST_KEY)).append(':');
builder.append(settings.get(PORT_KEY)).append(';');
if (settings.containsKey(INSTANCE_KEY) &&
settings.get(INSTANCE_KEY) != null &&
settings.get(INSTANCE_KEY) instanceof String &&
!((String) settings.get(INSTANCE_KEY)).isEmpty()) {
builder.append(URL_INSTANCE).append(settings.get(INSTANCE_KEY)).append(';');
}
builder.append(URL_DATABASE_NAME).append(settings.get(DATABASE_KEY));
return builder.toString();
}
@Override
protected String[] parseURL(String url) {
Matcher matcher = JDBC_URL_PATTERN.matcher(url);
matcher.find();
if (matcher.group(INSTANCE) == null) {
return new String[] { matcher.group(HOST), matcher.group(PORT), matcher.group(DATABASE) };
}
return new String[] { matcher.group(HOST), matcher.group(PORT), matcher.group(DATABASE),
matcher.group(INSTANCE) };
}
@Override
public Map parseDatasourceProperties(final Properties current) {
super.parseDatasourceProperties(current);
final Map settings = super.parseDatasourceProperties(current);
// parse optional instance
final String[] parsed = parseURL(current.getProperty(HibernateConstants.CONNECTION_URL));
// TODO what happens here
if (parsed.length == 4) {
settings.put(INSTANCE_KEY, parsed[3]);
}
return settings;
}
@Override
public boolean supportsClear() {
return true;
}
@Override
public void clear(Properties properties) {
Map settings = parseDatasourceProperties(properties);
Connection conn = null;
Statement stmt = null;
try {
conn = openConnection(settings);
Iterator tables = getMetadata(conn, settings).collectTableMappings().iterator();
List names = new LinkedList<>();
while (tables.hasNext()) {
Table table = tables.next();
if (table.isPhysicalTable()) {
names.add(table.getName());
}
}
if (!names.isEmpty()) {
stmt = conn.createStatement();
StringBuffer statement = new StringBuffer();
// alter table MyOtherTable nocheck constraint all
for (String table : names) {
statement = statement.append(ALTER_TABLE)
.append(table)
.append("\" NOCHECK CONSTRAINT ALL; ");
}
// delete from MyTable
for (String table : names) {
statement = statement.append("DELETE from \"")
.append(table)
.append("\"; DBCC CHECKIDENT(\"")
.append(table)
.append("\", RESEED, 0); ");
}
// alter table MyOtherTable check constraint all
for (String table : names) {
statement = statement.append(ALTER_TABLE)
.append(table)
.append("\" CHECK CONSTRAINT ALL; ");
}
statement =
statement.append("DBCC SHRINKDATABASE (")
.append(settings.get(DATABASE_KEY).toString())
.append(");");
LOGGER.debug("Executed clear datasource SQL statement: {}", statement);
stmt.execute(statement.toString());
}
} catch (SQLException ex) {
throw new ConfigurationError(ex);
} finally {
close(stmt);
close(conn);
}
}
@Override
protected Connection openConnection(Map settings) throws SQLException {
try {
String jdbc = toURL(settings);
Class.forName(getDriverClass());
String pass = (String) settings.get(HibernateConstants.CONNECTION_PASSWORD);
String user = (String) settings.get(HibernateConstants.CONNECTION_USERNAME);
precheckDriver(jdbc, user, pass);
return DriverManager.getConnection(jdbc, user, pass);
} catch (ClassNotFoundException ex) {
throw new SQLException(ex);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy