All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • base.sync.Sync Maven / Gradle / Ivy

    Go to download

    Simple tool for copying mysql data updates from one small mysql database to its mirror.

    The newest version!
    /**
 This is free and unencumbered software released into the public domain.

 Anyone is free to copy, modify, publish, use, compile, sell, or
 distribute this software, either in source code form or as a compiled
 binary, for any purpose, commercial or non-commercial, and by any
 means.

 In jurisdictions that recognize copyright laws, the author or authors
 of this software dedicate any and all copyright interest in the
 software to the public domain. We make this dedication for the benefit
 of the public at large and to the detriment of our heirs and
 successors. We intend this dedication to be an overt act of
 relinquishment in perpetuity of all present and future rights to this
 software under copyright law.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
 OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
 ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 OTHER DEALINGS IN THE SOFTWARE.
 */
package base.sync;

import base.json.Json;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonParser;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * Synchronise the tables in two MySQL database schemas
 */
public class Sync {

	private final String sourceHostname;
	private final String sourceSchema;
	private final String sourceUser;
	private final String sourcePassword;

	private final String targetHostname;
	private final String targetSchema;
	private final String targetUser;
	private final String targetPassword;

	private final String[] skip;
	private final Map tableFilter;
	private final Map> primaryKeyHints;

	public Sync(String sourceHostname, String sourceSchema, String sourceUser, String sourcePassword, String targetHostname, String targetSchema, String targetUser, String targetPassword, String[] skip, Map> primaryKeyHints, Map tableFilter) {
		this.sourceHostname = sourceHostname;
		this.sourceSchema = sourceSchema;
		this.sourceUser = sourceUser;
		this.sourcePassword = sourcePassword;

		this.targetHostname = targetHostname;
		this.targetSchema = targetSchema;
		this.targetUser = targetUser;
		this.targetPassword = targetPassword;

		this.skip = skip;
		this.tableFilter = tableFilter;
		this.primaryKeyHints = primaryKeyHints;
	}

	public void sync() throws ClassNotFoundException, SQLException {
		Connection source = null;
		Connection target = null;

		Class.forName("com.mysql.jdbc.Driver");

		// Connect to source
		MysqlDataSource ds = new MysqlDataSource();
		ds.setUrl("jdbc:mysql://" + sourceHostname + "/" + sourceSchema + "?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull");
		ds.setUser(sourceUser);
		ds.setPassword(sourcePassword);
		source = ds.getConnection();

		// Connect to destination
		ds = new MysqlDataSource();
		ds.setUrl("jdbc:mysql://" + targetHostname + "/" + targetSchema + "?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull");
		ds.setUser(targetUser);
		ds.setPassword(targetPassword);
		target = ds.getConnection();
		target.prepareStatement("SET foreign_key_checks = 0").execute();

		// Lookup tables meta data in source
		DatabaseMetaData m = source.getMetaData();
		ResultSet tables = m.getTables(null, null, "%", null);
		while(tables.next()) {

			// Ignore skipped tables
			boolean shouldSkip = false;
			for(String s : skip) {
				if(tables.getString(3).equalsIgnoreCase(s)) {
					shouldSkip = true;
					break;
				}
			}
			if(shouldSkip) {
				continue;
			}

			// Run the MySQL synchronisation
			try {
				syncTable(source, target, tables.getString(3));
			} catch(Exception e) {
				e.printStackTrace(System.out);
			}

			System.gc();
			System.gc();
		}

		source.close();
		target.close();
	}

	/**
	 * Copy objects from mysql source to mysql target database
	 *
	 * @param source
	 * @param target
	 * @param table
	 * @return
	 * @throws java.sql.SQLException
	 */
	public void syncTable(Connection source, Connection target, String table) throws SQLException {

		QueryBuilder q = new QueryBuilder(table);

		// Load table meta data into QueryBuilder object
		DatabaseMetaData m = source.getMetaData();
		ResultSet cols = m.getColumns(null, null, table, null);
		while(cols.next()) {
			q.addColumn(cols.getString(4));
		}

		// Work out table primary key columns
		if(primaryKeyHints.containsKey(table)) {
			// Config file is overriding the primary key composition
			System.out.print("  overriding primary key: ");
			for(String k : primaryKeyHints.get(table)) {
				System.out.print(k + " ");
				q.addPrimaryKeyColumn(k);
			}
			System.out.println();
		} else {
			// Using the mysql table defined primary key definition
			ResultSet pk = m.getPrimaryKeys(null, null, table);
			while(pk.next()) {
				q.addPrimaryKeyColumn(pk.getString(4));
			}
		}
		if(tableFilter.containsKey(table)) {
			q.setFilter(tableFilter.get(table));
		}

		// If meta data indicates table has a primary key on which to compare records, we can sync
		if(q.hasPrimaryKey()) {
			compareAndUpdateTable(source, target, q);
		} else {
			System.out.println("Skipping table with no primary key: " + table);
			System.out.println();
		}

	}

	/**
	 * Each item from the source table is compared with each item in the target table, changes in the source table
	 * are applied to the destination table.
	 *
	 * @param source
	 * @param target
	 * @param q
	 * @return
	 * @throws java.sql.SQLException
	 */
	public void compareAndUpdateTable(Connection source, Connection target, QueryBuilder q) throws SQLException {
		System.out.println("Running sync on: " + q.getTableName());

		if(q.getColumns() == null || q.getColumns().length == 0) {
			System.err.println("Skipping table which has no columns: " + q.getTableName());
			return;
		}

		Map> src = loadRowsFromTable(source, q);

		Map> dst = loadRowsFromTable(target, q);

		List srcKeys = new LinkedList<>(src.keySet());
		List dstKeys = new LinkedList<>(dst.keySet());
		Collections.sort(srcKeys);
		Collections.sort(dstKeys);

		Iterator srcIterator = srcKeys.iterator();
		Iterator dstIterator = dstKeys.iterator();

		String line1 = null;
		String line2 = null;

		JsonParser parser = new JsonParser();
		Gson gson = new GsonBuilder().setPrettyPrinting().create();

		while(srcIterator.hasNext() && dstIterator.hasNext()) {
			if(line1 == null) {
				line1 = srcIterator.next();
			}
			if(line2 == null) {
				line2 = dstIterator.next();
			}

			if(line1.equals(line2)) {
				Map entity1 = src.get(line1);
				Map entity2 = dst.get(line2);

				String sql = q.buildUpdateStatement(entity1, entity2);
				if(sql.length() > 0) {
					System.out.println(gson.toJson(parser.parse(Json.toJson(entity1))));
					System.out.println(gson.toJson(parser.parse(Json.toJson(entity2))));
					System.out.println(sql);
					PreparedStatement p = prepare(target, sql);
					int i = 1;
					for(Object o : q.lastBuiltUpdateStatementItems()) {
						p.setObject(i, o);
						System.out.println("    " + o);
						i++;
					}
					p.execute();
					p.clearParameters();
				}
				line1 = null;
				line2 = null;
				continue;
			} else {
				int compare = line1.compareTo(line2);
				if(compare == 0) {
					// Record has been altered
					System.err.println(line1 + " == " + line2 + ". this should not happen here.");
					line1 = null;
					line2 = null;
					continue;
				} else if(compare > 0) {
					// Record has appeared
					System.out.println("remove from target:");
					System.out.println(gson.toJson(parser.parse(Json.toJson(dst.get(line2)))));
					String sql = "delete from " + q.getTableName() + " where " + q.sqlMatch(dst.get(line2));
					System.out.println(sql);
					PreparedStatement p = prepare(target, sql);
					p.executeUpdate();
					p.clearParameters();
					System.out.println();
					line2 = null;
					continue;
				} else if(compare < 0) {
					// Record has disappeared
					System.out.println("add to target:");
					Map entity = src.get(line1);
					System.out.println(gson.toJson(parser.parse(Json.toJson(entity))));
					String sql = q.buildInsertStatement(src.get(line1));
					System.out.println(sql);
					PreparedStatement p = prepare(target, sql);
					q.prepareBuiltInsertStatement(p, entity);
					p.executeUpdate();
					p.clearParameters();
					System.out.println();
					line1 = null;
					continue;
				}
			}
		}

		// Read any records dangling off the end of file 1, ie removed from new file
		while(srcIterator.hasNext()) {
			line1 = srcIterator.next();
			Map entity = src.get(line1);
			System.out.println("add to target: " + line1);
			System.out.println(gson.toJson(parser.parse(Json.toJson(entity))));
			String sql = q.buildInsertStatement(entity);
			System.out.println(sql);
			PreparedStatement p = prepare(target, sql);
			q.prepareBuiltInsertStatement(p, entity);
			p.executeUpdate();
			p.clearParameters();
			System.out.println();
		}

		// Read any records dangling off the end of file 2, ie new records for new file
		while(dstIterator.hasNext()) {
			line2 = dstIterator.next();
			System.out.println("remove from target: " + line2);
			System.out.println(gson.toJson(parser.parse(Json.toJson(dst.get(line2)))));
			String sql = "delete from " + q.getTableName() + " where " + q.sqlMatch(dst.get(line2));
			System.out.println(sql);
			PreparedStatement p = prepare(target, sql);
			p.executeUpdate();
			p.clearParameters();
			System.out.println();
		}

		System.out.println();

		src = null;
		dst = null;

		System.gc();
		System.gc();
	}

	private Map> prepared = new Hashtable<>();

	public PreparedStatement prepare(Connection c, String query) throws SQLException {
		if(prepared.get(c) == null) {
			prepared.put(c, new Hashtable());
		}

		PreparedStatement p = prepared.get(c).get(query);

		if(p == null) {
			p = c.prepareStatement(query);
			prepared.get(c).put(query, p);
		}

		return p;
	}

	/**
	 * Load all objects from the table into a map, using the primary key fields as the key.
	 *
	 * @param c Database connection.
	 * @param q QueryBuilder describing the table.
	 * @return
	 * @throws java.sql.SQLException
	 */
	public Map> loadRowsFromTable(Connection c, QueryBuilder q) throws SQLException {
		Map> items = new Hashtable<>();

		PreparedStatement p = c.prepareStatement(q.toQuery(),
												 ResultSet.TYPE_FORWARD_ONLY,
												 ResultSet.CONCUR_READ_ONLY);
		p.setFetchSize(50);
		ResultSet r = p.executeQuery();
		while(r.next()) {
			Map item = new Hashtable<>();
			String pk = "";
			for(String i : q.getPrimaryKey()) {
				if(pk.length() > 0) {
					pk = pk + "|";
				}
				pk = pk + r.getString(i);
			}
			for(String i : q.getColumns()) {
				Object o = r.getObject(i);
				if(o != null) {
					item.put(i, o);
				}
				if(o == null && i.equals("update_date_time")) {
					System.err.println("    null update date time, pk=" + pk);
				}
			}
			if(items.containsKey(pk)) {
				System.err.println("delete from " + q.getTableName() + " where " + q.sqlMatch(item) + " limit 1;");
			}
			items.put(pk, item);
		}
		r.close();
		p.close();

		return items;
	}

	public class QueryBuilder {

		private String table;
		private String filter;

		private List columns = new LinkedList<>();
		private List primaryKey = new LinkedList<>();

		public QueryBuilder(String table) {
			this.table = table;
		}

		public String getTableName() {
			return table;
		}

		public void addColumn(String column) {
			columns.add(column);
		}

		public void addPrimaryKeyColumn(String key) {
			primaryKey.add(key);
		}

		public void setFilter(String filter) {
			this.filter = filter;
		}

		public String[] getPrimaryKey() {
			return primaryKey.toArray(new String[]{});
		}

		public String[] getColumns() {
			return columns.toArray(new String[]{});
		}

		public boolean hasPrimaryKey() {
			return primaryKey.size() > 0;
		}

		public String sqlMatch(Map entity) {
			StringBuffer b = new StringBuffer();

			for(String i : primaryKey) {
				if(b.length() > 0) {
					b.append(" and ");
				}
				b.append(i);
				if(entity.get(i) == null) {
					b.append(" is null");
				} else {
					b.append("=");
					if(entity.get(i) instanceof String) {
						b.append("\"");
						b.append(entity.get(i));
						b.append("\"");
					} else if(entity.get(i) instanceof Integer || entity.get(i) instanceof Long || entity.get(i) instanceof BigDecimal) {
						b.append(entity.get(i));
					} else {
						b.append("\"");
						b.append(entity.get(i));
						b.append("\"");
					}
				}
			}

			return b.toString();
		}

		public String buildInsertStatement(Map entity) {
			StringBuffer b = new StringBuffer();

			b.append("insert into ");
			b.append(table);
			b.append(" (");
			int count = 0;
			for(String c : columns) {
				if(entity.get(c) != null) {
					if(count > 0) {
						b.append(",");
					}
					b.append(c);
					count++;
				}
			}
			b.append(") values(");
			for(int i = 0; i < count; i++) {
				if(i == 0) {
					b.append("?");
				} else {
					b.append(",?");
				}
			}
			b.append(")");

			return b.toString();
		}

		private List updateItems;

		public List lastBuiltUpdateStatementItems() {
			return updateItems;
		}

		public String buildUpdateStatement(Map src, Map dst) {
			StringBuffer b = new StringBuffer();
			boolean timeUpdated = false;
			boolean usernameUpdated = false;
			updateItems = new LinkedList<>();

			b.append("update ");
			b.append(table);
			b.append(" set ");
			int count = 0;
			for(String c : columns) {

				Object o1 = src.get(c);
				Object o2 = dst.get(c);

				if(o1 == null && o2 == null) {
					continue;
				}

				//if(c.equals("email_address") || c.equals("email") && src.get(c) != null && ((String)src.get(c)).contains("@")) {
				//	src.put(c, ((String)src.get(c)).replaceAll("@", "-") + "@staff.tai.io");
				//	o1 = src.get(c);
				//}

				if((o1 == null && o2 != null) || (o1 != null && o2 == null) || (!o1.equals(o2))) {

					if(c.equals("update_date_time")) {
						timeUpdated = true;
					}
					if(c.equals("update_username")) {
						usernameUpdated = true;
					}
					if(count > 0) {
						b.append(", ");
					}
					b.append(c);
					b.append("=?");
					if((c.equals("archive_date") || c.equals("commence_from") || c.equals("commence_to") || c.equals("attention_date") || c.equals("opening_date") || c.equals("closing_date")) && src.get(c) == null) {
						updateItems.add("0000-00-00 00:00:00");
					} else {
						updateItems.add(src.get(c));
					}
					count++;
					continue;
				}
			}
			b.append(" where ");
			b.append(this.sqlMatch(src));

			if(count == 0) {
				return "";
			}

			if(count == 1 && timeUpdated) {
				return "";
			}

			if(count == 2 && timeUpdated && usernameUpdated) {
				return "";
			}

			return b.toString();
		}

		public void prepareBuiltInsertStatement(PreparedStatement p, Map entity) throws SQLException {
			int count = 1;
			for(String c : columns) {
				if(entity.get(c) != null) {
					p.setObject(count, entity.get(c));
					count++;
				}
			}
		}

		public String toQuery() {
			StringBuffer b = new StringBuffer();

			b.append("select ");
			for(String column : columns) {
				if(b.charAt(b.length()-1) != ' ') {
					b.append(',');
				}
				b.append(column);
			}
			b.append("\nfrom ");
			b.append(table);

			if(filter != null) {
				b.append("\nwhere ");
				b.append(filter);
			}

			if(primaryKey.size() == 0) {
				b.append("  --  HUH!!!");
			} else {
				b.append("\norder by ");
				for(String column : primaryKey) {
					if(b.charAt(b.length()-1) != ' ') {
						b.append(',');
					}
					b.append(column);
				}
			}

			if(filter != null) {
				System.out.println("Filtered query: " + b.toString());
			}

			return b.toString();
		}

	}

}

    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy