title: saiku+kettle整合(十三)替换h2数据库 tags:
- OLAP categories: saiku date: 2016-08-25 18:18:54
替换h2数据库
saiku默认使用h2数据库作为用户的储存。到了生产上替换成mysql吧。
Index: saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java (revision 783e78ca132d62452435514a5d76662aec9db8af)
+++ saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java (revision 3c1304562ddce613858d89d856e1045aa1cd7975)
@@ -1,36 +1,30 @@
package org.saiku.database;
import org.apache.commons.io.FileUtils;
-
+import org.h2.jdbcx.JdbcDataSource;
import org.saiku.datasources.datasource.SaikuDatasource;
import org.saiku.service.datasource.IDatasourceManager;
import org.saiku.service.importer.LegacyImporter;
import org.saiku.service.importer.LegacyImporterImpl;
-
-import org.h2.jdbcx.JdbcDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
+import javax.servlet.ServletContext;
+import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
-import java.sql.Connection;
-import java.sql.DatabaseMetaData;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.*;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
-import javax.servlet.ServletContext;
-
/**
* Created by bugg on 01/05/14.
*/
@@ -39,12 +33,12 @@
@Autowired
ServletContext servletContext;
- private JdbcDataSource ds;
+ private DataSource ds;
private static final Logger log = LoggerFactory.getLogger(Database.class);
private final BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
private IDatasourceManager dsm;
- public Database() {
-
+ public Database(DataSource ds) {
+ this.ds=ds;
}
public void setDatasourceManager(IDatasourceManager dsm) {
@@ -60,22 +54,12 @@
}
public void init() throws SQLException {
- initDB();
loadUsers();
// loadFoodmart();
// loadEarthquakes();
loadLegacyDatasources();
}
- private void initDB() {
- String url = servletContext.getInitParameter("db.url");
- String user = servletContext.getInitParameter("db.user");
- String pword = servletContext.getInitParameter("db.password");
- ds = new JdbcDataSource();
- ds.setURL(url);
- ds.setUser(user);
- ds.setPassword(pword);
- }
private void loadFoodmart() throws SQLException {
String url = servletContext.getInitParameter("foodmart.url");
@@ -224,7 +208,7 @@
Connection c = ds.getConnection();
Statement statement = c.createStatement();
- statement.execute("CREATE TABLE IF NOT EXISTS LOG(time TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL, log CLOB);");
+ statement.execute("CREATE TABLE IF NOT EXISTS LOG(time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, log TEXT);");
statement.execute("CREATE TABLE IF NOT EXISTS USERS(user_id INT(11) NOT NULL AUTO_INCREMENT, " +
"username VARCHAR(45) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, email VARCHAR(100), " +
@@ -242,16 +226,16 @@
dsm.createUser("admin");
dsm.createUser("smith");
statement.execute("INSERT INTO users(username,password,email, enabled)\n"
- + "VALUES ('admin','admin', 'test@admin.com',TRUE);" +
- "INSERT INTO users(username,password,enabled)\n"
+ + "VALUES ('admin','admin', 'test@admin.com',TRUE);");
+ statement.execute("INSERT INTO users(username,password,enabled)\n"
+ "VALUES ('smith','smith', TRUE);");
statement.execute(
"INSERT INTO user_roles (user_id, username, ROLE)\n"
- + "VALUES (1, 'admin', 'ROLE_USER');" +
- "INSERT INTO user_roles (user_id, username, ROLE)\n"
- + "VALUES (1, 'admin', 'ROLE_ADMIN');" +
- "INSERT INTO user_roles (user_id, username, ROLE)\n"
+ + "VALUES (1, 'admin', 'ROLE_USER');");
+ statement.execute("INSERT INTO user_roles (user_id, username, ROLE)\n"
+ + "VALUES (1, 'admin', 'ROLE_ADMIN');");
+ statement.execute("INSERT INTO user_roles (user_id, username, ROLE)\n"
- + "VALUES (2, 'smith', 'ROLE_USER');");
+ + "VALUES (2, 'smith', 'ROLE_USER');");
statement.execute("INSERT INTO LOG(log) VALUES('insert users');");
}
@@ -278,7 +262,7 @@
Connection c = ds.getConnection();
Statement statement = c.createStatement();
- statement.execute("ALTER TABLE users ALTER COLUMN password VARCHAR(100) DEFAULT NULL");
+ statement.execute("ALTER TABLE users MODIFY COLUMN password VARCHAR(100) DEFAULT NULL");
ResultSet result = statement.executeQuery("select username, password from users");
Index: saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties (revision 783e78ca132d62452435514a5d76662aec9db8af)
+++ saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties (revision 3c1304562ddce613858d89d856e1045aa1cd7975)
@@ -3,7 +3,7 @@
jdbcauth.usernamequery=select u.username,u.password, u.enabled from users u inner join ( \
select MAX(USERS.USER_ID) ID, USERS.USERNAME from USERS group by USERS.USERNAME) tm on u.USER_ID =\
tm.ID where u.username = ? GROUP BY u.USER_ID
-jdbcauth.driver=org.h2.Driver
-jdbcauth.url=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//saiku;MODE=MySQL
-jdbcauth.username=sa
+jdbcauth.driver=com.mysql.jdbc.Driver
+jdbcauth.url=jdbc:mysql://localhost:3306/saiku
+jdbcauth.username=root
jdbcauth.password=
\ No newline at end of file
Index: saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties (revision 783e78ca132d62452435514a5d76662aec9db8af)
+++ saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties (revision 3c1304562ddce613858d89d856e1045aa1cd7975)
@@ -1,9 +1,9 @@
default.role=ROLE_USER
external.properties.file=${catalina.base}/conf/Catalina/localhost/datasources.properties
webdav.password=sa!kuanalyt!cs
-userdao.driverclass=org.h2.Driver
-userdao.url=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/saiku;MODE=MySQL
-userdao.username=sa
+userdao.driverclass=com.mysql.jdbc.Driver
+userdao.url=jdbc:mysql://localhost:3306/saiku
+userdao.username=root
userdao.password=
logdir=../logs
repoconfig=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/repository/configuration.xml
@@ -15,3 +15,4 @@
earthquakeschema=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/Earthquakes.xml
earthquakeurl=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/earthquakes;MODE=MySQL
pluginpath=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/js/saiku/plugins/
+
Index: saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml (revision 783e78ca132d62452435514a5d76662aec9db8af)
+++ saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml (revision 3c1304562ddce613858d89d856e1045aa1cd7975)
@@ -41,8 +41,14 @@
<bean id="repoPasswordProviderBean" class="org.saiku.service.util.security.authentication.SimplePasswordProvider">
<constructor-arg index="0" value="${webdav.password}"/>
</bean>
-
- <bean id="h2database" class="org.saiku.database.Database" init-method="init">
+ <bean id="dataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
+ <property name="password" value="${userdao.password}"/>
+ <property name="user" value="${userdao.username}"/>
+ <property name="url" value="${userdao.url}"/>
+ <property name="encoding" value="UTF-8"/>
+ </bean>
+ <bean id="database" class="org.saiku.database.Database" init-method="init">
+ <constructor-arg ref="dataSource"/>
<property name="datasourceManager" ref="repositoryDsManager"/>
</bean>
@@ -176,7 +182,7 @@
<bean id="licenseUtils" class="org.saiku.LicenseUtils"/>
<bean id="licenseBean" class="org.saiku.web.rest.resources.License">
- <property name="databaseManager" ref="h2database"/>
+ <property name="databaseManager" ref="database"/>
<property name="licenseUtils" ref="licenseUtils"/>
<property name="userService" ref="userServiceBean"/>
</bean>
Index: saiku-webapp/src/main/webapp/WEB-INF/web.xml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- saiku-webapp/src/main/webapp/WEB-INF/web.xml (revision 783e78ca132d62452435514a5d76662aec9db8af)
+++ saiku-webapp/src/main/webapp/WEB-INF/web.xml (revision 3c1304562ddce613858d89d856e1045aa1cd7975)
@@ -11,46 +11,10 @@
<param-value>UTF-8</param-value>
</context-param>
<context-param>
- <param-name>db.url</param-name>
- <param-value>jdbc:h2:jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/saiku;MODE=MySQL</param-value>
- </context-param>
- <context-param>
- <param-name>db.user</param-name>
- <param-value>sa</param-value>
- </context-param>
- <context-param>
- <param-name>db.password</param-name>
- <param-value></param-value>
- </context-param>
- <context-param>
<param-name>db.encryptpassword</param-name>
<param-value>true</param-value>
</context-param>
<context-param>
- <param-name>foodmart.url</param-name>
- <param-value>jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//foodmart;MODE=MySQL</param-value>
- </context-param>
- <context-param>
- <param-name>foodmart.user</param-name>
- <param-value>sa</param-value>
- </context-param>
- <context-param>
- <param-name>foodmart.password</param-name>
- <param-value></param-value>
- </context-param>
- <context-param>
- <param-name>earthquakes.url</param-name>
- <param-value>jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//earthquakes;MODE=MySQL</param-value>
- </context-param>
- <context-param>
- <param-name>earthquakes.user</param-name>
- <param-value>sa</param-value>
- </context-param>
- <context-param>
- <param-name>earthquakes.password</param-name>
- <param-value></param-value>
- </context-param>
- <context-param>
<param-name>db.tcpServer</param-name>
<param-value>-tcpAllowOthers</param-value>
</context-param>
@@ -160,25 +124,6 @@
<url-pattern>/rmi</url-pattern>
</servlet-mapping>-->
- <servlet>
- <servlet-name>H2Console</servlet-name>
- <servlet-class>org.h2.server.web.WebServlet</servlet-class>
- <!--
- <init-param>
- <param-name>webAllowOthers</param-name>
- <param-value></param-value>
- </init-param>
- <init-param>
- <param-name>trace</param-name>
- <param-value></param-value>
- </init-param>
- -->
- <load-on-startup>1</load-on-startup>
- </servlet>
- <servlet-mapping>
- <servlet-name>H2Console</servlet-name>
- <url-pattern>/console/*</url-pattern>
- </servlet-mapping>
<servlet>
<servlet-name>jersey2</servlet-name>
复制代码