mysqlstorage.java

一个简单的java邮件系统

/*
 * @(#)MySQLStorage.java
 *
 * Copyright (C) 2006 Sergey Bredikhin
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 2 of
 * the License, or (at your option) any later version.
 * 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.
 * You should have received a copy of the GNU General Public
 * License along with this program; if not, write to the Free
 * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139,
 * USA.
 */

package olivax.webmail;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mysql.jdbc.Driver;

public class MySQLStorage extends Storage {

	public MySQLStorage() {
		super();
		mConn = null;
		mConnString = "jdbc:mysql://localhost/mail";
		mSchema = "";
	}

	public synchronized void logon(String user, String pass) throws Exception {
		if (!driverRegistered) {
			DriverManager.registerDriver(new Driver());
			driverRegistered = true;
		}
		mConn = DriverManager.getConnection(mConnString, user, pass);
		mConn.setAutoCommit(false);
	}

	public synchronized void close() throws Exception {
		if (mConn == null)
			return;
		if (!mConn.isClosed()) {
			mConn.rollback();
			mConn.close();
		}
		mConn = null;
	}

	protected String getLastInsertedId() throws Exception {
		String id = "";
		PreparedStatement ps = mConn
				.prepareStatement("SELECT LAST_INSERT_ID()");
		ResultSet resultset = ps.executeQuery();
		if (resultset.next()) {
			id = resultset.getString(1);
		}
		ps.close();
		resultset.close();
		return id;
	}

	protected boolean isIdAutoincremented() {
		return true;
	}

	protected String getId() throws Exception {
		return null;
	}

	public synchronized String populateSchema() throws Exception {
		String sqls150[] = {
				"CREATE TABLE USERS (ID bigint(10) NOT NULL auto_increment, NAME varchar(255) NOT NULL default '', COMMENT varchar(255) NOT NULL default '', PRIMARY KEY  (ID))",
				"CREATE TABLE DOMAINS (ID bigint(10) NOT NULL auto_increment, NAME varchar(255) NOT NULL default '', COMMENT varchar(255) NOT NULL default '', PRIMARY KEY  (ID))",
				"CREATE TABLE ACCOUNTS (ID bigint(10) NOT NULL auto_increment, USER_ID bigint(10) NOT NULL default '0', DOMAIN_ID bigint(10) NOT NULL default '0', ACCOUNT varchar(255) NOT NULL default '', COMMENT varchar(255) NOT NULL default '', PRIMARY KEY  (ID), KEY USER_ID (USER_ID,DOMAIN_ID))",
				"CREATE TABLE ADDRESSES (ID bigint(10) NOT NULL auto_increment, USER_ID bigint(10) NOT NULL default '0', NAME varchar(255) NOT NULL default '', EMAIL varchar(255) NOT NULL default '', COMMENT varchar(255) NOT NULL default '', PRIMARY KEY  (ID), KEY USER_ID (USER_ID))",
		};
		String sqls151[] = {
				"drop index user_id on ACCOUNTS",
				"drop index user_id on ADDRESSES",
				"RENAME TABLE USERS TO users",
				"RENAME TABLE DOMAINS TO domains",				
				"RENAME TABLE ACCOUNTS TO accounts",
				"RENAME TABLE ADDRESSES TO addresses",
				"ALTER TABLE users CHANGE COMMENT COMMENTS VARCHAR(255)",
				"ALTER TABLE domains CHANGE COMMENT COMMENTS VARCHAR(255)",
				"ALTER TABLE accounts CHANGE COMMENT COMMENTS VARCHAR(255)",
				"ALTER TABLE addresses CHANGE COMMENT COMMENTS VARCHAR(255)",												
				"ALTER TABLE accounts ADD CONSTRAINT acnt_user_fk FOREIGN KEY (USER_ID) REFERENCES users (ID)",
				"CREATE INDEX acnt_user_fk_i on accounts (USER_ID)",
				"ALTER TABLE accounts ADD CONSTRAINT acnt_dmn_fk FOREIGN KEY (DOMAIN_ID) REFERENCES domains (ID)",
				"CREATE INDEX acnt_dmn_fk_i on accounts (DOMAIN_ID)",
				"ALTER TABLE addresses ADD CONSTRAINT addr_user_fk FOREIGN KEY (USER_ID) REFERENCES users (ID)",
				"CREATE INDEX addr_user_fk_i on addresses (USER_ID)",
				"CREATE TABLE settings (ID BIGINT(10) NOT NULL AUTO_INCREMENT, NAME VARCHAR(40) NOT NULL DEFAULT '', VALUE VARCHAR(40), PRIMARY KEY (ID))"				
		};		
		StringBuffer sb = new StringBuffer();
		int schemaVer = getSchemaVersion();
		if(schemaVer == getReleaseVersion())
			return sb.toString();
		if (schemaVer == SCHEMA_EMPTY) {		
			sb.append(execSqlArray(sqls150));
			schemaVer = SCHEMA_1_5_0;
		}
		if (schemaVer == SCHEMA_1_5_0) {
			sb.append(execSqlArray(sqls151));
			try {
				sb.append("Setting schema version to 1.5.1:\n");
				setSettingsValue("schema_version", "1.5.1");
				sb.append("OK!");
			} catch (Exception exc) {
				sb.append(exc.getMessage()).append("\n");
			}
		}
		return sb.toString();
	}
	
}