Mapping Geometry in Grails and MySQL

针对地理数据的ORM，有一个Hibernate的扩展HibernateSpatial项目可以将JTS对象映射到MySQL/PostGIS/Oracle中。这个扩展同样可以用在Grails里，这里有一篇简单的介绍，关于在Grails和MySQL中管理地理数据：
http://www.grails.org/MySQL+GIS-Geometry+with+Grails

不过按照这个文章里介绍的方法用，很可能会遭遇这样的报错：

org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003

这个问题最终在这里得到了解答：
http://n2.nabble.com/No-Dialect-mapping-for-JDBC-type-2003-td1141106.html
按照邮件列表里的反映，上面的配置在Postgis里是可以work的，但是如果用Mysql还需要指定JPA的columnDefinition，对应的Hibernate属性是sql-type。虽然作者承诺会在今后的版本里修改这个问题，不过眼下的M2版本还没有修正这个问题。为此，Grails的用户特地提出在Grails中加入sql-type的支持：
http://jira.codehaus.org/browse/GRAILS-3201
现在按照下面文档的说明，可以在mapping里指定sqlType了：
http://grails.org/doc/latest/ref/Database%20Mapping/column.html

实例代码里的domain定义应该改成：

import com.vividsolutions.jts.geom.Polygon
import org.hibernatespatial.GeometryUserType

public class MyPoly {
    String name
    Polygon poly

    static mapping = {
        poly type: GeometryUserType， sqlType:"GEOMETRY"
    }

}

于是，再也没有莫名其妙的No Dialect报错了。

Using MySQL on ArchLinux

1. su -
2. pacman -S mysql mysql-gui-tools
   
3. run mysql_install_db –user=mysql in /var/lib/mysql
4. vim /etc/my.cnf, disable skip_networking to allow tcp/ip connection
   
5. vim /etc/hosts.allow, add mysql:all:allow
6. /etc/rc.d/mysql start
7. mysqladmin -u root password #NEW_PASSWORD# to set root password
   

2. su -

DB dump with chroot

I caught chroot first time when attempting to install gentoo linux. Of course, its a tool of great useful. Two months ago I had my laptop crashed, and all static files were recovery easily by copy while raw db data file seems to be difficult to handle.

The old file system is in a mobile disk, mounted at /media/disk-1/. Now I try to use chroot to rollback to that environment, so that all standard dump tools will be available.

Before you chroot to the system, don’t forget to bind /dev to new location. Or you might get error prompt such as “/dev/null Permission Denied” (it’s a common error). Just execute command below:
$ sudo mount –bind /dev /media/disk-1/dev

Then change root to my old system:
$ sudo chroot /media/disk-1

Now you become root user automatically.

MySQL Dump

In a standard ubuntu mysql installation, we should launch mysql db from init.d by:
# /etc/init.d/mysql start

But it’s no longer available in such environment. Fortunately, there is a direct way:
# mysqld_safe &

OK, go on to dump database with mysqldump:
# mysqldump –all-databases > mysql_dump_file

Postgresql Dump

We cannot use init.d to start pgsql either. Therefore, try to run it by:
# su postgres -c “/usr/lib/postgresql/8.3/bin/pg_ctl start -D /etc/postgresql/8.3/main”

/etc/postgresql/8.3/main is the default data directory in standard installation(with apt-get). This directory is supposed to contain a file named with “postgesql.conf”

Now pgsql db is also running. As root user we have no privilege to run pgsql utilities. so take following steps:
# touch pgsql_dump_file
# chmod a+w pgsql_dump_file
# su postgres -c “pg_dumpall -f pgsql_dump_file”

Conclusion

Personally, I found it’s too complex and low efficiency to backup and restore data with mysql and postgresql. If there is no critical requirement(just like personal data management), file based db(Surely I mean sqlite) is no doubt better choice. Easy management(lots of gui tool now provided), smooth copy and move, standard db interface for programming, and enough functionality.