Storage Conversions

Abstract

Storage conversion


Table of Contents

Introduction
Converting blobs to files
Convert files to blobs
Introducing 'DATETIME'
PostgreSQL
MySql
HSQL

HOWTO on database-conversions

Use the tool in mmbase/admin/tools. TODO: more info, details, how-to..

Can this be done?

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.

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;


This is part of the MMBase documentation.

For questions and remarks about this documentation mail to: documentation@mmbase.org