Introduction #

This article describes how to manage instances as asked several times in the forum and Jira. It gives you the ability to delete unwanted instance data.

Code #

To manage instances, and delete unwanted instance data, use the following code.

Note: Always backup first.

Note: When restoring this data in an existing database that problems could arise in tables like Counter. One could set these counters higher manually to much much higher values. It would be nice if these counters where updated with System.currentTimeMillis() so they would be most likely unique or Counter should get an companyId field.

public class LiferayInstanceRemovalMachine {

	public static void main(String[] args) {
		System.out.println("START");
		try {

			String originating_user = "root";
			String originating_password = "root";
			String originating_driverURL = "jdbc:mysql://localhost/lportal?useUnicode=true&" 
				+ "amp;characterEncoding=UTF-8&useFastDateParsing=false";
			String originating_Driver = "com.mysql.jdbc.Driver";
			Class.forName(originating_Driver);
			long companyId = 12345;
			
			Connection connection = java.sql.DriverManager.getConnection(originating_driverURL,
					originating_user,	originating_password);

			// ** LOAD SCHEMAS **//
			DatabaseMetaData metaData = connection.getMetaData();
			// Get driver information
			System.out.println("############ Driver Information ###########");
			System.out.println(metaData.getDriverName());
			System.out.println(metaData.getDriverVersion());

			HashSet<String> all_tables = new LinkedHashSet<String>();

			// Get table information
			System.out.println("########### Tables ############");
			ResultSet tables = metaData.getTables("", "", "", null);
			while (tables.next()) {
				all_tables.add(tables.getString(3));
				System.out.println(tables.getString(3));
			}

			
			for (String table : all_tables) {
				ResultSet rs = metaData.getColumns("", "", table, null);

				for (int cols = 1; cols <= rs.getMetaData().getColumnCount(); cols++) {
					System.out.print(rs.getMetaData().getColumnName(cols)+"\t\t");
				}
				while (rs.next()) {
					for (int cols = 1; cols <= rs.getMetaData().getColumnCount(); cols++) {
						String col = rs.getString(cols);
						System.out.print(col+"\t\t");
						if(col.equalsIgnoreCase("companyid")){
							// delete all from companyId
							//companyId
							String sql = "DELETE FROM "+table+" where companyid="+companyId;
							System.out.println(sql);
							connection.createStatement().execute(sql);
							break;
						}
					}
					System.out.println();
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("END");
	}

}