Count user activity in every hour

This post will be about how to do a simple user activity count in a MySQL database with hourly resolution for the last 24 hours.

Suppose we have the following table:

id	activityTimestamp
10	2013-09-21 23:45:08
9	2013-09-21 23:30:44
8	2013-09-21 16:11:51
7	2013-09-21 15:14:20
6	2013-09-21 14:25:11
5	2013-09-21 12:23:22
4	2013-09-21 09:09:32
3	2013-09-21 08:44:49
2	2013-09-21 08:44:38
1	2013-09-21 06:38:09

We want to have a report, where each previous 24 hours, the activity count is shown. For this, we need a pivot table, because MySQL does not support auto-generated sequences on-the-fly:

id	int_value	key
1	0	TIME_S
2	1	TIME_S
3	2	TIME_S
4	3	TIME_S
5	4	TIME_S
6	5	TIME_S
7	6	TIME_S
8	7	TIME_S
9	8	TIME_S
10	9	TIME_S
11	10	TIME_S
12	11	TIME_S
13	12	TIME_S
14	13	TIME_S
15	14	TIME_S
16	15	TIME_S
17	16	TIME_S
18	17	TIME_S
19	18	TIME_S
20	19	TIME_S
21	20	TIME_S
22	21	TIME_S
23	22	TIME_S
24	23	TIME_S

The first task is to select each 24 hours :

The SQL query to do that is the following:

SELECT NOW() - INTERVAL int_value HOUR-INTERVAL MINUTE(NOW()) MINUTE-INTERVAL SECOND(NOW()) SECOND dt

FROM `pivot` WHERE NOW() - INTERVAL int_value HOUR > NOW() - INTERVAL 1 DAY

and `key` = 'TIME_S' order by int_value asc

What it basically does, is the query select every whole hours backwards, and displays the timestamp of every previous whole hour.

2013-09-21 23:00:00

2013-09-21 22:00:00

2013-09-21 21:00:00

2013-09-21 20:00:00

2013-09-21 19:00:00

2013-09-21 18:00:00

2013-09-21 17:00:00

2013-09-21 16:00:00

2013-09-21 15:00:00

2013-09-21 14:00:00

2013-09-21 13:00:00

2013-09-21 12:00:00

2013-09-21 11:00:00

2013-09-21 10:00:00

2013-09-21 09:00:00

2013-09-21 08:00:00

2013-09-21 07:00:00

2013-09-21 06:00:00

2013-09-21 05:00:00

2013-09-21 04:00:00

2013-09-21 03:00:00

2013-09-21 02:00:00

2013-09-21 01:00:00

2013-09-21 00:00:00

2013-09-20 23:00:00

the result is the following:


The next step is to do this selection again, but increase the hour difference with 1, so we will have an interwall based on the two selections:

SELECT NOW() - INTERVAL int_value + 1 HOUR-INTERVAL MINUTE(NOW()) MINUTE-INTERVAL SECOND(NOW()) SECOND dt

FROM `pivot` WHERE NOW() - INTERVAL int_value + 1 HOUR > NOW() - INTERVAL 1 DAY and `key` = 'TIME_S' order by int_value asc

This selection is the same as the previous one, only the values are shifted with 1 hour.

Now, it is time, to do the selection, where the activities are in the range provided by the two subqueries:

select DATE_FORMAT(from_time.dt,'%Y-%m-%d %H') as from_ ,

DATE_FORMAT(to_time.dt,'%Y-%m-%d %H') as to_, sum(coalesce(activities.num, 0)) as acts from

(SELECT NOW() - INTERVAL int_value HOUR-INTERVAL MINUTE(NOW()) MINUTE-INTERVAL SECOND(NOW()) SECOND dt

FROM `pivot` WHERE NOW() - INTERVAL int_value HOUR > NOW() - INTERVAL 1 DAY

and `key` = 'TIME_S' order by int_value asc) as to_time,

(SELECT NOW() - INTERVAL int_value + 1 HOUR-INTERVAL MINUTE(NOW()) MINUTE-INTERVAL SECOND(NOW()) SECOND dt

FROM `pivot` WHERE NOW() - INTERVAL int_value + 1 HOUR > NOW() - INTERVAL 1 DAY

and `key` = 'TIME_S' order by int_value asc) as from_time

left outer join (select count(distinct ua.activityid) as num, ua.timestamp as timestmp

from `useractivity` ua where  ua.timestamp > (NOW() - INTERVAL 1 DAY) group by 2) as

activities on (DATE_FORMAT(from_time.dt,'%Y-%m-%d %H') = DATE_FORMAT(activities.timestmp,'%Y-%m-%d %H'))

WHERE from_time.dt = (to_time.dt - INTERVAL 1 HOUR)

group by 1, 2 limit 24

The coalesce is needet, to include the hours, where no activity was registered in the database.

The result is the following:

from	to	activitynumber
2013-09-20 23	2013-09-21 00	0
2013-09-21 00	2013-09-21 01	0
2013-09-21 01	2013-09-21 02	0
2013-09-21 02	2013-09-21 03	0
2013-09-21 03	2013-09-21 04	0
2013-09-21 04	2013-09-21 05	0
2013-09-21 05	2013-09-21 06	0
2013-09-21 06	2013-09-21 07	1
2013-09-21 07	2013-09-21 08	0
2013-09-21 08	2013-09-21 09	2
2013-09-21 09	2013-09-21 10	1
2013-09-21 10	2013-09-21 11	0
2013-09-21 11	2013-09-21 12	0
2013-09-21 12	2013-09-21 13	1
2013-09-21 13	2013-09-21 14	0
2013-09-21 14	2013-09-21 15	1
2013-09-21 15	2013-09-21 16	1
2013-09-21 16	2013-09-21 17	1
2013-09-21 17	2013-09-21 18	0
2013-09-21 18	2013-09-21 19	0
2013-09-21 19	2013-09-21 20	0
2013-09-21 20	2013-09-21 21	0
2013-09-21 21	2013-09-21 22	0
2013-09-21 22	2013-09-21 23	0

Insert into postgres from existing table with new sequences

There was a database upgrade lately, and we needed to include some auto-generated data based on an other table. The scenario was that one table listed all the available places for rent in the city, and we had to assign a default lease time for it. The lease_interval table had an id based on a sequence, so autogeneration was not an option, because the sequence was for the entire database.

place_pk	placename
123	place1
125	place2
127	place3
129	place4
131	place5
133	plcae6

The main idea was to do the generation of the new table in a for cycle. First step is to determine what variables are needed for the operation.
we needed the current status of the sequence counter, and we have named it p_count. Then we needed the ids of the place on which we iterated, so we named it placeid. All these two were of numeric types.

DECLARE
  p_count  numeric;
  placeid numeric;

The second step was to create a for loop:

FOR placeid IN SELECT place_pk FROM place
 LOOP
  n_count=n_count+1;
  INSERT INTO lease_interval (lease_interval_id, leasetime, place)
  SELECT p_count, NULL, placeid;
 END LOOP;

Great, now we need a criteria for the last_login for each day. The previous query generates timestamps with 00:00:00.000, so we need an interval with the current day as the beginning, and the next day as the end of the interval we are looking for.
Withe the help of the query

select (i::date + '1 day'::interval) from 
 generate_series('2012-12-30', '2013-01-05', '1 day'::interval) i;

Here, the sequence is incremented in every iteration, so we could keep track of the incrementing of the sequence.
then using the SELECT place_pk FROM place statement, we could select all the values of place_pk, and iterate through them.

Then the insert statement is just the usual, with the exception of using the variables declared in the function declaration section.

After the loop is finished, the sequence is updated with the new sequence count.

The function creation and the transaction handling is as usual, as you can see the whole script:

begin;

CREATE OR REPLACE FUNCTION add_default_lease_time()
  RETURNS void AS $$ 
DECLARE
  p_count  numeric;
  placeid numeric;
BEGIN
  select seq_count from sequence INTO p_count;
  FOR placeid IN SELECT place_pk FROM place
 LOOP
  n_count=n_count+1;
  INSERT INTO lease_interval (lease_interval_id, leasetime, place)
  SELECT p_count, NULL, placeid;
 END LOOP;
 update sequence set seq_count= p_count;

END; 
$$ LANGUAGE plpgsql; 

select * from add_default_lease_time();

commit;

And the results:

lease_interval_id	leasetime	place
221	null	123
222	null	125
223	null	127
224	null	129
225	null	131
226	null	133

AES256 between php and java

There can be several cases, where two completely independent systems communicate over an unsecure channel. The systems themselves are secure, but the communication is visible by anyone. For this scenarios, encryption is the way to go. In the following case, the AES256 encryption is demonstraded between php and java. The ise case is the following:
there is some information which should be transfered from the system written in php to the system written in java
the encryption key is kept secret, and is available for both systems

First, lets write the php end of the encryption. In this example, openssl extension is used for the php framework:

private function AES_Encode($plain_text,$key )
{
  $encoded = base64_encode(
     openssl_encrypt($plain_text, "aes-256-cbc", $key, true, 
           str_repeat(chr(0), 16)));
  return rtrim(strtr($encoded, '+/', '-_'), '=');
}

The encoded byte array is base64 encoded to be easily inluded in an url (this is whí the rtrim and strstr string manipulator functions are invoked, to provide an url-safe encoding).

The next step is to decrypt the encoded value message in java. To do this, the following methods are used:

private static void logError(final boolean encode, final String encoded,
  final String password, final Exception e) {
  LOGGER.log(Level.SEVERE, (encode) ? " encode: " : " decode: " + encoded + ":"+ password, e);
}
private static final Logger LOGGER = Logger.getLogger(Crypto.class.getName());

public static byte[] ivBytes = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
    0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };

public static String decode(final String str, final String key) {
  try {
    byte[] textBytes = Base64.decodeBase64(str);
    AlgorithmParameterSpec ivSpec = new IvParameterSpec(ivBytes);
    SecretKeySpec newKey = new SecretKeySpec(key.getBytes("UTF-8"), "AES");
    Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
    cipher.init(Cipher.DECRYPT_MODE, newKey, ivSpec);
    return new String(cipher.doFinal(textBytes), "UTF-8");
  } catch (NoSuchAlgorithmException e) {
    logError(false, str, key, e);
    return null;
  } catch (NoSuchPaddingException e) {
    logError(false, str, key, e);
    return null;
  } catch (InvalidKeyException e) {
    logError(false, str, key, e);
    return null;
  } catch (InvalidAlgorithmParameterException e) {
    logError(false, str, key, e);
    return null;
  } catch (IllegalBlockSizeException e) {
    logError(false, str, key, e);
    return null;
  } catch (BadPaddingException e) {
    logError(false, str, key, e);
    return null;
  } catch (UnsupportedEncodingException e) {
    logError(false, str, key, e);
    return null;
  }
}

There are several pitfalls, which can cause the AES encryption to fail.

First, the key must be exactly the same length as the specification of the used encryption algorithm states, because if shorter key is used, is in undefined what kind of data is used to fill the necessary bytes.

Second, the initial vector must be the same. In java, the ivBytes field provided the necessary value, in php, the str_repeat(chr(0), 16)) function call is responsible for that.

Third, the character encoding must match on both platform.

Last, but not least, on the java side, the UnlimitedJCEPolicy must be used if not prohibited by law at the location the code is used and/or deployed. This is needed for AES256, but not for AES128 and weaker encryptions.

Testing the UI of a web application

Testing is a quite tedious and repetitive work, some may say. This can be true for several applications. But the time put in testing can easily be monetized by the satisfaction of the customer who receives a software that does not contain any KNOWN bug during the tested use-cases.

But what if the software is so huge, that several tester is required to focus only on that specific project full time for several weeks before even a minor release could occur. Then an automatic testing suite must be seriously considered.

At our company, the scenario was as described above. Several testers worked several days before each minor release, and their task was quite repetitive. That is why we decided to move the testing to a concordion test suite with selenium engine.

The main benefit was that the test cases and fixtures can be written by the testers (the concordion does not require advanced programming skills for usage, only if you want to 'play' a bit with the output generation), and the developers created the test blocks similar to the selenium PageObject design pattern. So the complexity of the code for the testers was similar to the following:

LoginPage loginPage = new LoginPage("www.mysite.com/login");
loginPage.fillName("test");
loginPage.fillPassword("test");
loginPage.loginAction();

So the testers did not have to bother with various XPath expressions for finding a specific action button for a user in a html table, this was the task for the developer to do (and it forced the developer to create the user interface so that it can be easily put to automated testing via the selenium webdriver).

In a concordion fixture, several pages can be chained together, so the previous example can be extended as:

UserAccount userAccount = new UserAccount("www.mysite.com/myaccount");
useraccount.deposit(10000);
String userCredit = userAccount.getCurrentAmount();

LogoutPage logoutPage = new Logout("www.mysite.com/logout");
logoutPage.logout();

The concordion fixture then validates whether the userCredit meets the requirements established by the test scenarios:

<p concordion:execute="#result = myTest()">
  <span>Check account increased by 10000:</span>
  <span concordion:assertEquals="#result">10000</span>
</p>

Concordion with selenium is an incredibly powerful tool for testing not only just the business logic of the application, but due to the screenshot taking ability of the selenium package, the failed concordion tests can include the screenshot of the webpage on which the error occurred. What is more, it is possible to validate the layout (alignment, color, textual representation, etc.) of the pages against the standard. with the help of an image processing library.

Create tables in different databases programatically

Recently I came across a task that required to create tables manually in three different databases, but only if the database did not exist. The databases were accessed via JPA, but these tables contained some database specific optimalizations, which could not be generalized. The project had to be able to run on H2, MySQL and PostgreSQL databases. The main logic behind it was to create a database initializer, which upon the deploy of the application, creates the necessary table if it does not exist. The following SQL statements were created.

H2:

CREATE TABLE IF NOT EXISTS example(
 id_ IDENTITY PRIMARY KEY, 
 description VARCHAR(255), 
 amount BIGINT);

MySQL:

CREATE TABLE IF NOT EXISTS example (
 id_ BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 description VARCHAR(255),
 amount BIGINT
) ENGINE = INNODB;

Well this is not a big deal, they look pretty similar. The only difference is the handling of the auto increment definition, but so far so good, so 2 out of 3 is ready. Here comes PostgreSQL:

CREATE OR REPLACE FUNCTION build_example_table() RETURNS VOID AS $$
CREATE TABLE "example"
(
   "id_" SERIAL PRIMARY KEY, 
   "description" CHARACTER VARYING(255), 
   "amount" bigint
);
$$ LANGUAGE SQL;

SELECT CASE WHEN (SELECT COUNT(*) FROM information_schema.tables WHERE table_name='example')=0 THEN build_example_table() END;

DROP FUNCTION build_example_table();

This is quite different. Unfortunately PostgreSQL does not support "create table if not exist" syntax, but "create or replace function" is supported. So a function can be made for the creation of the table, and the PostgreSQL internal representation can be queried whether the desired table exist.

OK, so the create statements are ready, time to do the database initialization. This can be achieved via a spring or blueprint bean with an init method. The bean must have reference to the database connection (or it can receive the connection through the JNDI API.

The example function is as follows:

public void createDb() {
  Connection connection = null;
  try {
    connection = dataSource.getConnection();
    String databaseProductName = connection.getMetaData().getDatabaseProductName();
    Statement createStatement = connection.createStatement();
    if ("PostgreSQL".equals(databaseProductName)) {
      createStatement.execute(POSTGRESQL_CREATE);
    } else if ("H2".equals(databaseProductName)) {
      createStatement.execute(H2_CREATE);
    } else if ("MySQL".equals(databaseProductName)) {
      createStatement.execute(MYSQL_CREATE);
    } else {
      createStatement.close();
      throw new RuntimeException("Database type (" + databaseProductName + ") not supported!");
    }
    createStatement.close();
  } catch (SQLException e) {
    throw new RuntimeException("Cannot create tables!", e);
  } finally {
    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Liferay shared email address

The aim for this post is to show a possible solution for allowing multiple users with the same email address.
The solution is for Liferay 6.0.5, so compatibility with different versions might not apply.

The first task is to enable the registration with an email address that already exists.
In order to do this two things should be done: first, the constraint in the database must be deleted, then the check during the registration process must be eliminated.
The database constraint is a unique index on table _user consisting of two columns, the companyid and the emailaddress. In our test case, it has the name ix_615e9f7a, but this may vary. Its creation on Postgres database is the following:

CREATE UNIQUE INDEX ix_615e9f7a

  ON user_

  USING btree

  (companyid , emailaddress );

This index must be dropped, otherwise an SQL exception will be thrown during the addition or modification of a user if the email address already exists.

The next task is to eliminate the checks during the user save process that throw DuplicateUserEmailAddressException. This can be done by modifying the UserLocalServiceImpl class which is located in liferay-portal.war/WEB-INF/lib/portal-impl.jar/com/liferay/portal/service/impl.
This class has two protected void validate() functions with different arguments. In these function can be found the parts that should be eliminated:

protected void validate(long companyId, long userId,
 boolean autoPassword, String password1, String password2,
 boolean autoScreenName, String screenName, String emailAddress, 
 String firstName, String middleName, String lastName,
 long[] organizationIds) throws PortalException, SystemException {
    ....
    if (Validator.isNotNull(emailAddress)) {
      User user = this.userPersistence.fetchByC_EA(companyId, emailAddress);
      if (user != null) {
        throw new DuplicateUserEmailAddressException();
      }
    }
    .....
}

protected void validate(long userId, String screenName, 
 String emailAddress, String firstName, String middleName, 
 String lastName, String smsSn) throws PortalException, SystemException {
    .....
      if ((Validator.isNotNull(emailAddress)) && 
        (!user.getEmailAddress().equalsIgnoreCase(emailAddress)))
      {
        if (this.userPersistence.fetchByC_EA(
          user.getCompanyId(), emailAddress) != null)
        {
          throw new DuplicateUserEmailAddressException();
        }
      }
     .....
}

Now duplicated email addresses can be registered. But how can we guarantee that although the users share the same email address, they must not share their registration. In other words, authentication must be done without the email address.

The liferay-portal.war/WEB-INF/struts-config.xml should be edited. The following part is responsible for the login:

<action path="/login/login" type="com.liferay.portlet.login.action.LoginAction">
    <forward name="portlet.login.login" path="portlet.login.login" />
</action> 

The simplest methood is to replace wit a custom Login action:

<action path="/login/login" type="com.liferay.portlet.login.action.MyLoginAction">
    <forward name="portlet.login.login" path="portlet.login.login" />
</action> 

Now the only thing to do, is to create a com.liferay.portlet.action.MyLoginAction. Here is a sample implementation. In this class, the original LoginAction is extended, and the some parts of the original methods are kept, but the login authentication is changed from email to screenname:

package com.liferay.portlet.login.action;





import javax.portlet.ActionRequest;

import javax.portlet.ActionResponse;

import javax.portlet.PortletPreferences;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;



import com.liferay.portal.kernel.util.ParamUtil;

import com.liferay.portal.kernel.util.Validator;

import com.liferay.portal.model.Company;

import com.liferay.portal.model.CompanyConstants;

import com.liferay.portal.theme.ThemeDisplay;

import com.liferay.portal.util.PortalUtil;

import com.liferay.portal.util.PropsValues;

import com.liferay.portlet.login.util.LoginUtil;



public class MyLoginAction extends LoginAction {




    @Override

    protected void login(

            final ThemeDisplay themeDisplay, final ActionRequest actionRequest,

            final ActionResponse actionResponse, final PortletPreferences preferences)

            throws Exception {

        HttpServletRequest request = PortalUtil.getHttpServletRequest(actionRequest);

        HttpServletResponse response = PortalUtil.getHttpServletResponse(actionResponse);

        String login = ParamUtil.getString(actionRequest, "login");

        String password = ParamUtil.getString(actionRequest, "password");

        boolean rememberMe = ParamUtil.getBoolean(actionRequest, "rememberMe");

        String authType = CompanyConstants.AUTH_TYPE_SN;

        LoginUtil.login(request, response, login, password, rememberMe, authType);

        if (PropsValues.PORTAL_JAAS_ENABLE) {

            actionResponse.sendRedirect(themeDisplay.getPathMain() + "/portal/protected");

        }  else {

            String redirect = ParamUtil.getString(actionRequest, "redirect");

            if (Validator.isNotNull(redirect)) {

                redirect = PortalUtil.escapeRedirect(redirect);

                actionResponse.sendRedirect(redirect);

            }  else {

                actionResponse.sendRedirect(themeDisplay.getPathMain());

            }

        }

    }

} 

Migrating Liferay installation to different location

Imagine a scenario when you have a Liferay installation, but for various technical reasons, you have to move it to an other computer or just to a different folder. The other components of the system are not affected, the db connection, and all the connecting legacy system remain on the same location.

If the system setup is complicated enough, and the time frame is narrow, one of the simplest solutions is to just simply migrate the files to the different location.

Of course, some things should be done before this migration takes place. In this example, the Liferay version is 6.0.5 and is bundled with Glassfish 3.0.1.

• The first thing to do is to delete content of the /domains/[domain-name]/generated-sources directory. This directory can be huge (several gigabytes), and it is a generated content used for caching, so there is no point in copying the data, it will be generated again, thus small performance decrease might occur during the first opening of a page.
• The second important task is to delete the content of the /domains/[domain-name]/osgi-cache directory. If this directory is not emptied, then the Glassfish domain cannot be started, because the osgi-cache holds references for the absolute paths of some of the osgi jars.

After this two deletions are made, the Liferay installation can be moved to the new direction and if no other configuration is required (depends on the environment), it can be started safely.