Thursday, February 28, 2013
Find Antenna officially released
My very first mobile app was officially released yesterday. I have spent many evenings on ridiculous details and I am happy to say that the app is ready at last. The app can be used to determine the 5 closest DVB-T transmitters and in which direction they can be found. It is very useful if you want to figure out which way your TV antenna should point. It uses the location of the mobile phone to determine which transmitters are closest and the compass to determine the direction.
The app is currently only available for Android and can be found at Google Play right here.
Labels: android, java, mobile, tv
posted by Peter Haldbæk @ 21:11,
, links to this post
Thursday, October 25, 2012
Setting character set to UTF8 in MySQL
The default installation of MySQL (5.5) does not use UTF-8 so every time I install an instance of MySQL I spend some time tinkering with the character setup so I decided to write a quick summary of the steps needed in order for MySQL to support UTF8. When you have a vanilla installation of MySQL you can check your character settings by executing these commands.
mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+As we can see latin1 is used in several places. This should be changed to UTF8. You can do this by either editing your existing my.cnf configuration file or creating a new configuration file in the conf.d folder of your MySQL configuration (recommended). I create a new configuration file at conf.d/utf8.cnf which looks like this.
[mysqld] character-set-server = utf8 character-set-client = utf8Restart the server.
> sudo service mysql restart
Log in to MySQL and check that everything is ok.
mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+Everything is now using UTF8.
Update 21.11.2012
If you want to use utf8_unicode_ci instead of utf8_general_ci (you probably want that, see this) then add the following to the my.cnf file in the mysqld section and restart the server.
collation-server = utf8_unicode_ciUpdate 21.11.2012 (a little later)
The collation settings should be something like this.
mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+I previously had the impression that it should be utf8_unicode_ci for all collation variables (obtained by setting the skip-character-set-client-handshake option in my.cnf) but this generated weird behavior when comparing dates in the database.
posted by Peter Haldbæk @ 15:31,
, links to this post
Tuesday, October 23, 2012
Creating insert statements from table data
Creating a SQL script which inserts data from an already existing table is not as straightforward as one would think. I found a pretty clean and easy solution which I will explain in detail here. I use MySQL but I believe the principles can be applied to most other databases if needed. Consider table A with the following content.
| id | name |
|---|---|
| 1 | Peter |
| 2 | Jake |
| 3 | Paul |
CREATE TABLE b (SELECT FROM a WHERE name LIKE 'P%');Now the data can be exported using mysqldump.
> mysqldump -p -u <username> <database> b > insert-script.sqlThe created script contains statements which will drop and create the table (among other things). Normally I am only interested in the part where the insert statements are so open the created script file and remove everything except the INSERT INTO statement. Also remember to rename the name of the table where the data is to be inserted to the correct table name (table A in my case). The final script file should look something like this.
INSERT INTO a VALUES (1, 'Peter'),('3', 'Paul');
Finally you should drop the temporary table you created.
posted by Peter Haldbæk @ 13:04,
, links to this post
Tuesday, September 11, 2012
Accessing memory database in Play!
The other day I was running some unit tests in an app using the Play! framework and I wanted to see how the data in the database looked like. Tests are run using a H2 memory database so accessing this was not as straightforward as I am used to since most configuration in Play! is done by convention (or magic, not sure which). So I did not know the connection string to the database nor the credentials used by the framework. After some googling I came across the information needed.
First start your test and when you are at a breakpoint open the database browser supplied by Play! at localhost:9000/@db (this was new to me as well). Select the Generic Server option and use these parameters.
| JDBC URL | jdbc:h2:mem:play |
| User Name | sa |
| Password | <none> |
You should now be able to access the database used by your tests.
Labels: database, h2, java, play
posted by Peter Haldbæk @ 10:28,
, links to this post
Saturday, August 11, 2012
Changing unnamed constraints in Play!
I have been working on a project based on the Play! framework lately (the old version, 1.2.4) and I came across a real headscratcher. My problem was I needed to delete a foreign constraint in a table but this constraint had no name (it was not given a name when it was created). Deleting a constraint with standard SQL requires a name so I had to do come up with something else for my evolution script. I was using MySQL in production and H2 in test so it was not an option to use some MySQL specific code unless it was compatible with H2.
Let's make a little example first to illustrate my problem. Consider these two tables:
CREATE TABLE a (
id INTEGER NOT NULL AUTO_INCREMENT,
CONSTRAINT pk_a PRIMARY KEY(id)
);
CREATE TABLE b (
id INTEGER NOT NULL AUTO_INCREMENT,
a_id INTEGER NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(a_id) REFERENCES a(id)
);
Table B has a foreign constraint for the column a_id which points to the id column in the A table. If I wanted to delete the foreign constraint I should execute this statement:ALTER TABLE b DROP CONSTRAINT "constraint name"But since the foreign constraint was not named initially I cannot use this statement. My next thought was to find a way to list all the foreign constraints of the table and then use the result as input to an alter table statement. I did not find any way to do this with standard SQL but using some MySQL-specific SQL I actually managed to do this. The select query looks like this:
SELECT
constraint_name,
CONCAT(table_name, '.', column_name) AS 'foreign key',
CONCAT(referenced_table_name, '.', referenced_column_name) AS 'references'
FROM
information_schema.key_column_usage
WHERE
referenced_table_name IS NOT NULL;
This lists all foreign key constraints in the database. I tried executing it in H2 and to no surprise it did not work.I decided not to change the existing table but instead create a new table (without constraints) and copy the contents of the old table to the new table. If you need to keep the name of the table as it was you could always rename the table but since the naming of the table was a bit off in my case I actually ended up with a better named table anyway. And I am not sure renaming a table is supported by standard SQL.
So lets first create a new table without the constraints:
CREATE TABLE b_new (
id INTEGER NOT NULL AUTO_INCREMENT,
a_id INTEGER NOT NULL,
CONSTRAINT pk_b PRIMARY KEY(id)
);
Then copy the data from the old to the new table:INSERT INTO b_new SELECT * FROM b ORDER BY id;The last thing to do is to delete the old table (and maybe renaming the new one):
DROP TABLE b;That's it. Now the table has no foreign constraints.
So if you need a reason for why you always should name your constraints, now you know why.
Labels: java, mysql, play, sql
posted by Peter Haldbæk @ 22:17,
, links to this post
Monday, February 13, 2012
Removing all .svn folders
Every now and then I need to copy a folder which is under source control. By copying the folder all source control information is copied along which needs to be removed afterwards. If the amount of folders is small this is done pretty easy manually but when we are talking larger amounts of folders doing it manually easily ends up pretty time consuming.
I figured there would be a pretty easy solution for this with some nifty Linux command line magic and it didn't take long to find something that worked:
> find . -iname ".svn" | xargs rm -r $1
This removes all .svn folders recursively.
Labels: linux, subversion
posted by Peter Haldbæk @ 12:21,
, links to this post
Wednesday, October 26, 2011
More mobile conferences
I just discovered some extra (Android) conferences which seem interesting.
- DroidCon was recently held in London but is also held in Berlin, Amsterdam and Bukarest
- AnDevCon Nov. 6-9 in San Francisco
Labels: conference, mobile
posted by Peter Haldbæk @ 23:12,
, links to this post
Thursday, September 22, 2011
Hibernate, Oracle, HSQLDB and sequences
Recently I was writing some unit tests where I needed to access a database sequence for generation of unique numbers. The production environment uses Oracle while my unit tests use HQSLDB. The numbers from the sequence are extracted by ordinary SQL through Hibernate meaning that I could not rely on specifying the database dialect since the SQL is hardcoded. The code used for extracting the numbers (in Oracle) looked something like this
long number =
((BigDecimal) sessionFactory.getCurrentSession().createSQLQuery
("select my_seq.nextval from dual")
.uniqueResult()
).longValue();Running this in a unit test results in a ClassCastException since HSQLDB either generates the primitive int or a BigInteger instance depending on how the sequence was created. A simple solution for this is to add a scalar to the query determining the type of the returned number.long number =
((BigDecimal) sessionFactory.getCurrentSession().createSQLQuery
("select my_seq.nextval as id from dual")
.addScalar("id", new BigDecimalType())
.uniqueResult()
).longValue();Voila! The number is now converted to a BigDecimal by Hibernate and the ClassCastExceptions are long gone.Labels: hibernate, hsqldb, java, oracle, test
posted by Peter Haldbæk @ 20:01,
, links to this post
Monday, August 29, 2011
Mobile conferences
Just stumbled upon some more interesting conferences. I really wanted to go to Google IO and/or WWDC but these conferences sell out in like 2 seconds so my odds of ever going to one of these events seem slim. But there are other interesting conferences out there and here is a quick list of what I have found so far:
- Android Open Oct. 9-11 in San Francisco
- App Conference and Hackaton Oct. 26-27 in Santa Clara (not really a conference but looks fun)
- Mobile Web Africa Nov. 22-25 in Johannesburg, South Africa
Labels: conference, mobile
posted by Peter Haldbæk @ 21:23,
, links to this post
Saturday, March 26, 2011
Mockito, static imports and Eclipse
I recently started using Mockito as a mocking framework. Mockito makes heavy use of static imports which by default in Eclipse are not accessible through code completion. It is possible to use the Mockito methods by accessing them through their class by writing Mockito.when but this would quickly clutter your code with a lot of Mockito references. Code completion has been around for ages so not having it feels like coding in Notepad or vi. Fortunately I have found out that it is actually possible and quite easy to configure Eclipse to add the static imports which is a great relief.
Simply open the Preferences in Eclipse and go to the menu item Java -> Editor -> Content Assist -> Favorites and press the button New Type.... Enter the text org.mockito.Mockito and press OK. Now you have all static methods on the Mockito class available to you through code completion.
Other useful Mockito classes to add are org.mockito.Matchers and org.mockito.BDDMockito (if you are into BDD).
Labels: eclipse, java, mockito, test
posted by Peter Haldbæk @ 14:22,
, links to this post