Søren's Blog

Random Ramblings

Partial migration of data

I'm currently involved in updating an in-house developed issue/bug tracking system. It's a simple web application with a SQL database used for persistence. We've decided not to do a big bang migration of all data, instead we're migration one project/product at a time. This makes the transition easier to manage, as fewer users need to be informed of the change. Also, if the new system has any serious bugs, not that many users will be affected.

The migration of data from the existing production database to the new database is a little more complex, when we just can't migrate everything at once.

I've create an example to demonstrate how I did the migration. The example below is using MySQL, but the technique can be used with all SQL databases.

To try the code below you'll need a database. Often MySQL is installed with a test database, and you can use that. Or create one like this:

create database catalog default character set utf8;

Then switch to using the database:

use catalog

Now create the first set of tables. They are what's in production right now:

create table countries (
  id int not null auto_increment primary key,
  code varchar(20) not null,
  name varchar(200)
) ENGINE = InnoDB;

create table cars (
  id int not null auto_increment primary key,
  name varchar(50),
  country_id int not null,
  foreign key (country_id) references countries(id) on delete cascade
) ENGINE = InnoDB;

Add some data:

insert into countries (code, name) value ('DK', 'Denmark');
insert into countries (code, name) value ('SE', 'Sweden');
insert into cars (name, country_id) value ('Volvo', (select id from countries where code = 'SE'));
insert into cars (name, country_id) value ('Ellert', (select id from countries where code = 'DK'));

Let's see what's in the database:

mysql> select Car, Country from cars a, countries b where a.country_id =;
| Car    | Country |
| Ellert | Denmark |
| Volvo  | Sweden  |
2 rows in set (0.00 sec)

Now create a set of new set of tables. This is the new system, where data from production will be migrated to:

create table countries_copy (
  id int not null auto_increment primary key,
  code varchar(20) not null,
  name varchar(200)
) ENGINE = InnoDB;

create table cars_copy (
  id int not null auto_increment primary key,
  name varchar(50),
  country_id int not null,
  foreign key (country_id) references countries_copy(id) on delete cascade
) ENGINE = InnoDB;

As the two system will run in parallel, we'll add some data to this as well:

insert into countries_copy (code, name) value ('DE', 'Germany');
insert into cars_copy (name, country_id) value ('Audi', (select id from countries_copy where code = 'DE'));

And let's see that:

mysql> select Car, Country from cars_copy a, countries_copy b where a.country_id =;
| Car  | Country |
| Audi | Germany |
1 row in set (0.00 sec)

Now we'll prepare the new (copy) database for migration. Because both databases are in use, we can't migrate the id's. Instead will create an extra column in the coutries_copy table to hold the original id:

alter table countries_copy add column id_orig int;

Now we can copy all the countries:

insert into countries_copy (code,name,id_orig) select code,name,id from countries;

If we look at the data, we can see that the entries has new ids, but we'll also have the original id stored:

mysql> select * from countries_copy;
| id | code | name    | id_orig |
|  1 | DE   | Germany |    NULL |
|  2 | DK   | Denmark |       1 |
|  3 | SE   | Sweden  |       2 |
3 rows in set (0.00 sec)

We can use the link between the original and new id when we copy data to the cars_copy table. We'll only copy the swedish cars:

insert into cars_copy (name, country_id) select, from cars a, countries_copy b
where a.country_id in (select id from countries where code = 'SE')
and a.country_id = b.id_orig;

The copy now contains data from both systems:

mysql> select Car, Country from cars_copy a, countries_copy b where a.country_id =;
| Car   | Country |
| Audi  | Germany |
| Volvo | Sweden  |
2 rows in set (0.00 sec)

And the relations are still correct.

Now we can continue to add Danish cars to the original table, cars. But Swedish cars should be added to the new table, cars_copy. If we need to add a new country, this should also be added to the new table, countries_copy.

The above code does not remove or somehow deactivate the migrated data in the old system. This should be done when doing this in real life.
Tags: ,

Using winexe to add local users to a Windows server from Linux
If you're like me, and likes working from the command line on linux, but still have to administer Windows servers, you will like winexe. It's a small command line utility that remotely executes commands on a Windows system.

For example, to list alle the user in the Administrators group on the machine called myserver issue the following:

$ winexe -U DOMAIN/username //myserver "net localgroup Administrators"

You will be prompted for your password.

You can add a user using:

$ winexe -U DOMAIN/username //myserver "net localgroup Administrators anotheruser /add"
Tags: ,

How to check the amount of RAM in a Linux machine
On Linux information about memory are available ind the virtual file /proc/meminfo, you can open this file in an editor or simple display it in a console like this:

$ less /proc/meminfo

The output will be something like this:

MemTotal:      1034436 kB
MemFree:        296028 kB
Buffers:        108808 kB
Cached:         348964 kB
SwapCached:          0 kB
Active:         420868 kB
Inactive:       279276 kB
HighTotal:      129476 kB
HighFree:          252 kB
LowTotal:       904960 kB
LowFree:        295776 kB
SwapTotal:     1558264 kB
SwapFree:      1558264 kB
Dirty:             320 kB
Writeback:           0 kB
AnonPages:      242372 kB

Often you just want the amount of installed RAM, that's MemTotal. And you might also want it displayed as Mega og Giga Bytes. The following little awk-oneliner does that:

$ awk '/^MemTotal:/{d="kmg"; for (i=1; $2>999; i++) { $2/=1024 } print "RAM: " int($2+.5) substr(d,i,1) " B"}' /proc/meminfo
RAM: 1gB

If you're using bash, add the following to ~/.bashrc:

function raminfo {
  awk '/^MemTotal:/ {
                       for (i=1; $2>999; i++) {
                       print "RAM: " int($2+.5) substr(d,i,1) "B"
                    }' /proc/meminfo;

Now you can just use the raminfo function:

$ raminfo
RAM: 1gB
Tags: , ,

A simple way to compare two Oracle schemas

The following SQL can be used to compare two Oracle schemas, for example a staging environment with production.

The first select statement will list all the current users objects (except tables and stuff in the recycle bin). The second select statement will list all the current users columns (and tables), except stuff in the recycle bin.

Send the output to a text file or csv file and compare those.

select owner,object_type,object_name,status
from all_objects 
where owner = (select user from dual)
and object_type != 'TABLE'
and object_name not like 'BIN%'
order by object_type, object_name; 

select c.owner,c.table_name,c.column_name,c.data_type,c.data_length,c.nullable
from all_objects o, all_tab_cols c
where o.owner = (select user from dual)
and o.object_type = 'TABLE'
and o.object_name not like 'BIN%'
and c.owner = o.owner
and c.table_name = o.object_name
order by c.owner,c.table_name,c.column_name;
Tags: ,

Getting Midnight Commander (mc) to list files in zip archives

I recently switched from Ubuntu to Arch Linux as my prefered Linux distribution. I have not regretted that (but I would still recomend Ubuntu, it's a great distribution.)

I've only run into one small problem with Arch Linux. I use Midnight Commander as my file manager. It has the abillity to browse tarballs and zip archives directly (as virtual filesystems). But browsing zip archives didn't work in the Arch Linux version of Midnight Commander.

The solution is simple: edit the file /usr/share/mc/extfs/uzip search for the following line:

my $op_has_zipinfo = 0;
and change it to
my $op_has_zipinfo = 1;
And that's it. You might need to restart Midnight Commander, but browsing zip archives should be working now.

Tags: , , ,

Build and Release with Maven
Recently I gave a short presentation to the local Perl Mongers Group. The topic of the evening was build and deploy systems. I had chosen to present Maven.

Maven is a tool written in Java and only useful when managing Java project (but it implements some common and best practices that a valid in any software project).

The slides are available here:

The main part of the presentation was a practical demonstration of a few concepts: Subversion integration and release management. I've converted my demonstration notes into a simple tutorial, which you're find below.


You'll need
  • A Java JDK - I'm using version 1.6.0 update 17
  • Maven (obviously) - I'm using version 2.2.1
  • The Subversion command line tools - I'm at version 1.6.5

I will not go into details on how to install the tools (it should be pretty easy).

The rest of this tutorial is done in a terminal (or DOS-Prompt if you're on Windows).

Creating a Subversion Repository

To demonstrate the integration with Subversion, you'll need a repository. A repository can be created with following:
mkdir /tmp/svn	
svnadmin create /tmp/svn/maven
svn mkdir file:///tmp/svn/maven/branches file:///tmp/svn/maven/tags file:///tmp/svn/maven/trunk -m "Initial repository layout"
If you're you on Windows you should replace /tmp with /C:/TEMP or similar (and remember to do that for the rest of this tutorial).

Creating a New Project

To start a new project (or archetype) issue the following:
mvn archetype:generate
Don't panic! Maven will download a lot of plugins before presenting you with a menu of project types to chose from. It look something like this:
[INFO] Scanning for projects...
[INFO] Searching repository for plugin with prefix: 'archetype'.
[INFO] ------------------------------------------------------------------------
[INFO] Building Maven Default Project
[INFO]    task-segment: [archetype:generate] (aggregator-style)
[INFO] ------------------------------------------------------------------------
[INFO] Preparing archetype:generate
[INFO] No goals needed for project - skipping
[INFO] Setting property: classpath.resource.loader.class => 'org.codehaus.plexus.velocity.ContextClassLoaderResourceLoader'.
[INFO] Setting property: velocimacro.messages.on => 'false'.
[INFO] Setting property: resource.loader => 'classpath'.
[INFO] Setting property: resource.manager.logwhenfound => 'false'.
[INFO] [archetype:generate {execution: default-cli}]
[INFO] Generating project in Interactive mode
[INFO] No archetype defined. Using maven-archetype-quickstart (org.apache.maven.archetypes:maven-archetype-quickstart:1.0)
Choose archetype:
1: internal -> appfuse-basic-jsf (AppFuse archetype for creating a web application with Hibernate, Spring and JSF)
2: internal -> appfuse-basic-spring (AppFuse archetype for creating a web application with Hibernate, Spring and Spring MVC)
3: internal -> appfuse-basic-struts (AppFuse archetype for creating a web application with Hibernate, Spring and Struts 2)
4: internal -> appfuse-basic-tapestry (AppFuse archetype for creating a web application with Hibernate, Spring and Tapestry 4)
5: internal -> appfuse-core (AppFuse archetype for creating a jar application with Hibernate and Spring and XFire)
6: internal -> appfuse-modular-jsf (AppFuse archetype for creating a modular application with Hibernate, Spring and JSF)
7: internal -> appfuse-modular-spring (AppFuse archetype for creating a modular application with Hibernate, Spring and Spring MVC)
8: internal -> appfuse-modular-struts (AppFuse archetype for creating a modular application with Hibernate, Spring and Struts 2)
9: internal -> appfuse-modular-tapestry (AppFuse archetype for creating a modular application with Hibernate, Spring and Tapestry 4)
10: internal -> maven-archetype-j2ee-simple (A simple J2EE Java application)
11: internal -> maven-archetype-marmalade-mojo (A Maven plugin development project using marmalade)
12: internal -> maven-archetype-mojo (A Maven Java plugin development project)
13: internal -> maven-archetype-portlet (A simple portlet application)
14: internal -> maven-archetype-profiles ()
15: internal -> maven-archetype-quickstart ()
16: internal -> maven-archetype-site-simple (A simple site generation project)
17: internal -> maven-archetype-site (A more complex site project)
18: internal -> maven-archetype-webapp (A simple Java web application)
19: internal -> jini-service-archetype (Archetype for Jini service project creation)
20: internal -> softeu-archetype-seam (JSF+Facelets+Seam Archetype)
21: internal -> softeu-archetype-seam-simple (JSF+Facelets+Seam (no persistence) Archetype)
22: internal -> softeu-archetype-jsf (JSF+Facelets Archetype)
23: internal -> jpa-maven-archetype (JPA application)
24: internal -> spring-osgi-bundle-archetype (Spring-OSGi archetype)
25: internal -> confluence-plugin-archetype (Atlassian Confluence plugin archetype)
26: internal -> jira-plugin-archetype (Atlassian JIRA plugin archetype)
27: internal -> maven-archetype-har (Hibernate Archive)
28: internal -> maven-archetype-sar (JBoss Service Archive)
29: internal -> wicket-archetype-quickstart (A simple Apache Wicket project)
30: internal -> scala-archetype-simple (A simple scala project)
31: internal -> lift-archetype-blank (A blank/empty liftweb project)
32: internal -> lift-archetype-basic (The basic (liftweb) project)
33: internal -> cocoon-22-archetype-block-plain ([])
34: internal -> cocoon-22-archetype-block ([])
35: internal -> cocoon-22-archetype-webapp ([])
36: internal -> myfaces-archetype-helloworld (A simple archetype using MyFaces)
37: internal -> myfaces-archetype-helloworld-facelets (A simple archetype using MyFaces and facelets)
38: internal -> myfaces-archetype-trinidad (A simple archetype using Myfaces and Trinidad)
39: internal -> myfaces-archetype-jsfcomponents (A simple archetype for create custom JSF components using MyFaces)
40: internal -> gmaven-archetype-basic (Groovy basic archetype)
41: internal -> gmaven-archetype-mojo (Groovy mojo archetype)
Choose a number:  (1/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41) 15: : 
We just want the default (quickstart) archetype, so just press enter.

Now you'll be asked to enter a groupId, artifactId, version and package.

The groupId is like a Java package name, usually a domain name in reverse.

For example I could use com.livejournal.slu as a groupId. The artifactId is the name of the project, you should enter maven-demo. The version default 1.0-SNAPSHOT which is ok, the package default is the same as the groupId, also ok.

You will be presented with the selected values and if you're satisfied (pressing enter) a new project will be created for you in the directory maven-demo.

Now you can build you project using
cd maven-demo
mvn compile
Also try mvn package which will create a JAR of your project (after running any unit tests).

Importing to and Integrating with Subversion

To play with the Subversion integration we need to import the project into the repository we created earlier:
cd ..
svn import maven-demo file:///tmp/svn/maven/trunk
Check out you project:
svn co file:///tmp/svn/maven/trunk maven-demo-svn
Now edit the pom.xml file. Add the following lines just after the line that reads <url></url>:

Setting up a Distribution Repository

When doing releases we need a place to store our released artefacts (in essence JAR files). Add the following lines right after the scm-section you added above:
    <name>My TEMP directory</name>
Check in your modified pom.xml:
svn ci pom.xml -m "Added SCM and repository"

Preparing and Performing a Release

Now we're ready to release our project. This is a two step process: first the release is prepared and if all goes well we can perform the actual release.

Start the the prepare:
mvn release:prepare

You will be asked to enter a release version, SCM tag and the next development version. Maven will present you with sensible default values, so just press enter three times.

Now Maven will update the version number in the pom.xml, will verify that the project builds, tags the release in Subversion and finally commits a pom.xml for the next development cycle to Subversion.

If everything went well, you can now perform the release:
mvn release:perform
This will check out a fresh copy of the project for the tag you defined when preparing the release. Next the project is built and released (i.e. copied to the /tmp directory.

The End

That's it. Play with the above commands, read the documentation, and play some more...

Welcome to the world of Maven!

Feeding my twitter from my blog

I'm trying to collect all my public presence (i.e. this blog and twitter) in one place. More of that later...

During that work I stumbled on, that will take a feed and send it twitter, facebook, etc.

I've signed up, and now the subjects of my blog entries should appear on twitter - automatically.</a>

Tags: ,

Øredev 2009 - Accomplishing More By Doing Less
I'm at the Øredev conference. It's the first real day of the conference, that was preceeded by two days of tutorials and courses, which I didn't go to.

First talk today - the keynote - was by Marc Lesser. The title was "Accomplishing More By Doing Less". It was very zen, started with 60 seconds of silence, but didn't really do anything but lists the problems the modern man (or woman) is facing. I think Distraction is my biggest roadblock for getting things done. The solution was to work from "the inside out" and not from "the outside in" - don't know what that means... going to think about it (i.e. google it).

Show me your Firefox extension

The topic for the next meeting of the Copenhagen Perl Mongers is Lightning-talks about Firefox plugins. In preparation for this I installed the Extension List Dumper, a plugin to list the extension/plugins currently installed. The result (on my laptop at work) can be seen below:

Application: Firefox 3.5.3 (20090824101458)
Operating System: WINNT (x86-msvc)

September 30, 2009

Total number of items: 17

Getting ready for YAPC::EU 2009
Well, the conference starts tomorrow... so I'm going to bed now.

Here's the view from my hotel window:



Log in