Storage Conversions

Author: Michiel Meeuwissen
Date: 2007-04-10

This software is OSI Certified Open Source Software. OSI Certified is a certification mark of the Open Source Initiative.

The license (Mozilla version 1.0) can be read at the MMBase site. See http://www.mmbase.org/license

Abstract

Storage conversion

Table of Contents

1. Introduction

2. Converting blobs to files

3. Convert files to blobs

4. Introducing 'DATETIME'

4.1. PostgreSQL
4.2. MySQL
4.3. HSQL

1. Introduction

HOWTO on database-conversions

2. Converting blobs to files

In the MMBase admin pages is a tool to start the conversion. You will need to configure your database first. Change the option 'database-supports-blob' to true:

  <!-- Use Blobs  -->
  <option name="database-supports-blob" value="true" />

Followed by these two lines of which the last line states the absolute path to which the blobs will be written.

  <option name="database-stores-binary-as-file" value="true" />
  <attribute name="database-binary-file-path">/home/http/default/blob</attribute>

3. Convert files to blobs

Can this be done?

4. Introducing 'DATETIME'

In MMBase 1.7 datetime were stored as 'LONG' or 'INTEGER' fields with 'guitype' 'eventtimes'. In 1.8 also 'DATETIME' is supported for database type. So if you have an existing installation, you may want to convert these fields.

It is simple to change the builder XML's, but the database will need a manual conversion.

4.1. PostgreSQL

To transform a column named 'begin' in the builder 'content' to DATETIME you can do the following. This is tested on Postgresql 7.2. It should also work on higher version, though it could be simplified there. For example you may want to drop the old column then (not supported in psql 7.2).

          alter table mm_content rename begin to begin_;
          alter table mm_content add column begin timestamp;
          update mm_content set begin = 'epoch';
          update mm_content set begin  = begin + CAST(begin_||'s' AS interval);
          # alter table drop column begin_; (not supported in 7.2)

Because the column cannot be dropped MMBase will issue a warning on it's startup:

          13:36:55,810 8095kB WAR storage.implementation.database.DatabaseStorageManager - VERIFY: Column 'begin_' for builder 'categories' in Storage but not defined!

This can safely be ignored, until the column can be dropped in some way. IIRC even newer version of postgresql cannot really drop the column, only hide it. In that case you can probably actually remove it by reimporting a database dump.

This works too (converting a column 'broadcasttime'):

          create table mm_programs2 (broadcasttime timestamp) inherits  mm_texts;
          insert into mm_programs2 (number,otype,owner,begin,m_end,visible,title,body,broadcasttime,lastmodifiedby,lastmodified) select number,otype,owner,begin,m_end,visible,title,body,  '1970-01-01'::date + CAST(broadcasttime ||'s' AS interval),lastmodifiedby,lastmodified from mm_programs;
          alter table mm_programs rename to mm_programs_orig; alter table mm_programs2 rename to mm_programs;

And then check if everthing is ok, and you can drop mm_programs_orig;

4.2. MySQL

This will convert your former MMBase date in MySQL in a proper DATETIME field. This was used and tested on MySQL 4.

  mysql> ALTER TABLE intr_employees ADD birthdate DATETIME;  
  mysql> UPDATE intr_employees SET birthdate = from_unixtime(dateofbirth);
  mysql> ALTER TABLE intr_employees DROP COLUMN dateofbirth;  
  mysql> ALTER TABLE intr_employees CHANGE birthdate dateofbirth DATETIME;

4.3. HSQL

Probably simply start from scratch...