CREATE TABLE mytable (id INTEGER PRIMARY KEY, cnt INTEGER);
Adding indexes to tables of Apache James 3
TweetPosted on Sunday Nov 15, 2015 at 05:06PM in Technology
I’m using Apache James (3.0-beta5-SNAPSHOT) as the mail server for my domain. It’s running with JPA backend on PostgreSQL.
Today I’ve found that some slow queries are logged as the number of emails has been grown through the days. The slowest two are following:
-
SELECT t0.property_id, t0.property_line_number, t0.property_local_name, t0.property_name_space, t0.property_value FROM public.JAMES_MAIL_PROPERTY t0 WHERE t0.mailbox_id = $1 AND t0.mail_uid = $2 ORDER BY t0.property_line_number ASC -
SELECT t0.userflag_id, t0.userflag_name FROM public.JAMES_MAIL_USERFLAG t0 WHERE t0.mailbox_id = $1 AND t0.mail_uid = $2 ORDER BY t0.userflag_id ASC
These queries are used sequential scan. It’s terrible for thousands of rows so I’ve created a couple of indexes to avoid sequential scan as follows:
-
create index on james_mail_property (mailbox_id, mail_uid); -
create index on james_mail_userflag (mailbox_id, mail_uid);
They seems work expectedly as I’ve confirmed that now these queries are using index scan instead.
Tags: james postgresql
Notes about using UPSERT on RDBMS
TweetPosted on Saturday Nov 07, 2015 at 12:15PM in Technology
Recently I’ve investigated some ways to implement UPSERT which gets the following job done without problems of race conditions:
-
INSERT a row if there’s no duplicate one with same ID
-
UPDATE a row otherwise
Also another slightly differ requirement:
-
INSERT a row if there’s no duplicate one with same ID
-
Do nothing otherwise
-
Application needs to know whether the query has inserted a row because one is not exist
The above two requirements are needed to implement an application that works with an Amazon SQS which is configured as the destination of Amazon SES notification.
Table to use for experimentation
Solution for MySQL (5.6.x)
There’s an easy solution that uses a MySQL specific clause INSERT INTO … ON DUPLICATE KEY UPDATE …. For detail check http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
With the mytable, I’ve done some experimentation (on MySQL 5.6.27) as follows:
-
Launch two instances of
mysql -
Execute
begin;on both so let a transaction begin for each instances -
Execute
INSERT INTO mytable (id, cnt) VALUES (1, 1) ON DUPLICATE KEY UPDATE cnt=cnt+1;on both. Note that the following execution will be blocked due to the preceding transaction is about to insert a row which has same ID -
Execute
commit;on the instance which executes the statement first -
Execute
commit;on the another instance which has been blocked
Then execute select * from mytable; you will see the desired result:
+----+------+ | id | cnt | +----+------+ | 1 | 2 | +----+------+ 1 row in set (0.00 sec)
If you don’t want to update any values if duplicated one already exists, Use following SQL instead:
INSERT INTO mytable (id, cnt) VALUES (1, 1) ON DUPLICATE KEY UPDATE id=id;
Also note that if you’re using JDBC to communicate with MySQL, You need to add useAffectedRows=true parameter to the JDBC URL so that executeUpdate() method will return the number of affected rows instead of found rows. For detail check https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
Another solution
I found an interesting attempt that seems to work with generic SQL:
http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/ http://stackoverflow.com/a/17067131/3591946
And I’ve confirmed it works as I expected. I’ve done the following experiment on MySQL 5.6.x:
-
Launch two instances of
mysql -
Execute
begin;on both so let a transaction begin for each instances -
Execute
INSERT INTO mytable (id, cnt) SELECT 1, 0 FROM (select 0 as i) mutex LEFT JOIN mytable ON id = 1 WHERE i = 0 AND id IS NULL;. Note that the following execution will be blocked as well -
Execute
UPDATE mytable SET cnt = cnt + 1 WHERE id = 1;on the instance which executes the statement first, if incrementation is needed -
Execute
commit;on the instance which executes the statement first -
Execute
UPDATE mytable SET cnt = cnt + 1 WHERE id = 1;on the instance which executes the statement second as well -
Execute
commit;on the another instance
Note that I’ve tried the experimentation for PostgreSQL 9.3.4 as well but doesn’t work. It blocks the following query but produces ERROR: duplicate key value violates unique constraint "mytable_pkey" after issuing commit of the preceding transaction.
I have no idea why it doesn’t work for PostgreSQL (To be honest, I don’t exactly know why it does work for MySQL). If you know why, Let me know via posting a comment to this entry that would be greatly appreciated.
UPSERT functionality will be in the PostgreSQL 9.5 release (citation from https://wiki.postgresql.org/wiki/SQL_MERGE).
Tags: mysql postgresql ses sql sqs
Excluding particular JUnit test cases that marked as slow in the time of execution
TweetPosted on Sunday Nov 01, 2015 at 03:43PM in Technology
Sometimes we need to create some slow test cases that involve some external resources such as databases, or external API servers. They are necessary to ensure that your application can integrate with such external resources while vast majority of test cases should stick with fast-running plain unit testing.
In such case, We usually wants to exclude such slow test cases from the ones that are frequently executed in local development environment so that we can get timely response from the tests. In this posting, I introduce you a solution that avoids maintenance of any hand-made listing of test cases.
Creating a suite that scans and runs all of test cases exist in classpath
First, assume we have a simple production class named Hello.
public class Hello {
public String greetings(String name) {
return name != null ? "hello, " + name : "hi, what's your name?";
}
}
We also have a couple of test cases against the preceding class:
public class HelloTest1 {
@Test
public void test() {
System.out.println("Running " + getClass().getSimpleName());
Assert.assertEquals("hello, kyle", new Hello().greetings("kyle"));
}
}
public class HelloTest2 {
@Test
public void test() {
System.out.println("Running " + getClass().getSimpleName());
Assert.assertEquals("hi, what's your name?", new Hello().greetings(null));
}
}
Next, We’d like to introduce a test suite that automatically includes the preceding two test cases. Put a following dependency to your pom.xml:
<dependency>
<groupId>io.takari.junit</groupId>
<artifactId>takari-cpsuite</artifactId>
<version>1.2.7</version>
<scope>test</scope>
</dependency>
And create a test suite that named AllTests as follows. You can run this suite from your IDE or executing mvn -Dtest=AllTests test.
@RunWith(ClasspathSuite.class)
public class AllTests {
}
Involving a slow test case and exclude it
First, Create a marker interface which indicates that this test is slow:
public interface SlowTest {
}
Next create a slow test case which annotated with @Category(SlowTest.class) that we would like to avoid execute it frequently:
@Category(SlowTest.class)
public class HelloSlowTest {
@Test
public void test() throws Exception {
System.out.println("Running " + getClass().getSimpleName());
Thread.sleep(3000);
}
}
Finally create a test suite that automatically excludes the test cases annotated as slow but executes rest of the test cases:
@RunWith(Categories.class)
@ExcludeCategory(SlowTest.class)
@SuiteClasses(AllTests.class)
public class AllExceptSlowTests {
}
You can run it on a daily basis instead of selecting root of your entire project and execute tests from your IDE or Maven without any hand maintenance of the listings of tests. For example, mvn -Dtest=AllExceptSlowTests test produces following output in very short-term execution time:
... ------------------------------------------------------- T E S T S ------------------------------------------------------- Picked up _JAVA_OPTIONS: -Dfile.encoding=UTF-8 Running category.suite.AllExceptSlowTests Running HelloTest1 Running HelloTest2 Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.084 sec ...
The complete resources can be obtained from https://github.com/lbtc-xxx/junit-category
SwingIrc: A simple IRC client
TweetPosted on Sunday Nov 01, 2015 at 01:42PM in Technology
About 6 years ago I’ve made a simple IRC client that uses Swing. I’ve digged source code of the app from my old PC by chance so I’ve Mavenized it and put it to GitHub. To be hornest, it’s a toy app but may someone like a student can refer it as an example of Java based GUI application.
How To Launch
Refer README.md in the repository.
How To Use
-
Put
name,hostnameandportto the dialog and hitconnect -
Click
Filethen selectJoin -
Enter name of a channel to the dialog and hit
OK -
Chat with other users
Future plans
-
Rewrite entire the app with JavaFX
-
Implement more features
-
Involve automated GUI testing
