« Back to Using Liferay

Managing Instances

Table of Contents [-]

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");
	}

}
0 Attachments
38979 Views
Average (0 Votes)
The average rating is 0.0 stars out of 5.
Comments
Threaded Replies Author Date
This erase data from database, but how remove... Andrey Urvancev June 20, 2009 11:06 AM
Hi. We found the easier way is to go to the... Milan Jaroš July 23, 2009 10:46 AM
I am having trouble getting a Liferay... Tom Thomas October 2, 2009 12:51 PM
HI there, Is that still the recommended... Fabrice Clari October 29, 2010 12:56 AM
Hi, I created a stored procedure, that does the... Andre Schikore June 2, 2013 11:23 AM

This erase data from database, but how remove instance from Document Library repository?
Posted on 6/20/09 11:06 AM.
Hi. We found the easier way is to go to the portal instance and delete all the data via portlets. You can check the data in the database then (i. e. use query by this way: SELECT * FROM igimage WHERE companyid = XXXXX ).
Until there is no advanced managing of instances (remove, duplicate, backup... etc.) we decided to have every instance in separate database and Liferay...
Posted on 7/23/09 10:46 AM in reply to Andrey Urvancev.
I am having trouble getting a Liferay (5.2.2)/JBoss bundle to display a new instance using the same database (SQLServer 2005). I have gone through serveral cycles of using the LiferayInstanceRemovalMachine program (thanks, btw) to remove the new instance and trying to add it again.
I confirmed that the instance is defined in DNS. Any ideas?
Posted on 10/2/09 12:51 PM.
HI there,

Is that still the recommended solution?

Thanks,
Fabrice.
Posted on 10/29/10 12:56 AM.
Hi, I created a stored procedure, that does the same job as *LiferayInstanceRemovalMachine*.
Once created in lportal database, you can use it as follows:
a) use this to check what will be deleted:
call remove_instance(<companyId>,0);
b) use this to perform generated delete statements:
call remove_instance(<companyId>,1);

And here is the code for the procedure:
DROP PROCEDURE IF EXISTS lportal.remove_instance;
CREATE PROCEDURE lportal.`remove_instance`(company_id integer,do_delete Bit(1) )
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE company_tab varchar(100) DEFAULT "";
DECLARE delete_stmt varchar(300) DEFAULT "";

DECLARE
company_tables CURSOR FOR
SELECT DISTINCT c.TABLE_NAME
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = 'lportal' AND c.COLUMN_NAME = 'companyId';

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
-- declare ERROR handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK ;

OPEN company_tables;


CREATE TEMPORARY TABLE TempTable (tabname varchar(300),records integer,sel_stmt varchar(3000),del_stmt varchar(3000));

START TRANSACTION;

get_tables:
LOOP
FETCH company_tables INTO company_tab;

IF finished = 1
THEN
LEAVE get_tables;
END IF;

INSERT INTO TempTable (tabname) VALUES (company_tab);

SET @del = CONCAT("DELETE FROM lportal.",company_tab," WHERE companyId = ",company_id);
SET @sel = CONCAT("SELECT COUNT(*) INTO @i1 FROM lportal.",company_tab," WHERE companyId = ",company_id);
PREPARE stmt FROM @sel ;
EXECUTE stmt;

UPDATE TempTable SET sel_stmt = @sel WHERE tabname = company_tab;
UPDATE TempTable SET records = @i1 WHERE tabname = company_tab;

IF (@i1 > 0)
THEN
UPDATE TempTable SET del_stmt = @del WHERE tabname = company_tab;
IF (do_delete = 1)
THEN
PREPARE stmt FROM @del ;
EXECUTE stmt;
END IF;
END IF;
END LOOP get_tables;

-- PUHH - FINISHED
COMMIT;
CLOSE company_tables;
SELECT * FROM TempTable;
DROP TABLE TempTable;
END;
Posted on 6/2/13 11:23 AM.