A little while ago I was tasked with migrating about hundred Trac instances from SQLite to MySQL. Unfortunately SQLites schema definitions is not compatible with MySQL. So I had to write some scripts to handle it. After a little bit of experimenting everything worked perfectly.

The Strategy I employed was like this:

1. Make a dump of a default MySQL Trac db schema.
2. Make a dump of the Trac SQLite database
3. Remove db schema definitions from the SQLite dump
4. Concatenate the MySQL and SQLite dumps
5. Load data into MySQL
6. Edit Trac's database settings to use MySQL
7. Do a trac-admin upgrade

Here is the shell script I made:

MYSQL_USER=username
MYSQL_PSWD=password

SQLITECMD=sqlite3
TRACBASE=/path/to/tracreps


# loop through all trac instances in tracreps
for d in $( ls $TRACBASE )
do
if [ -d "$TRACBASE/$d" ]; then
echo $d
TRACNAME=$d

# create database in mysql
echo "creating database for $TRACNAME..."
mysqladmin --user $MYSQL_USER -p$MYSQL_PSWD create $TRACNAME

# dump sqlite db structure + data
echo "dumping data from sqlite..."
$SQLITECMD $TRACBASE/$TRACNAME/db/trac.db .dump > trac.sqlite.sql

# remove database definitions from dump using a custom python script
echo "cleaning database definition from dump..."
`./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly`

# concatenate mysql database definitions and sqlite data
cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql

echo "loading data into mysql..."
mysql --user $MYSQL_USER -p$MYSQL_PSWD --default_character_set utf8 $TRACNAME < trac.sql

# edit database connection string in trac.ini
sed -i "s?sqlite:db/trac.db?mysql://$MYSQL_USER:$MYSQL_PSWD@localhost:3306/$TRACNAME?" $TRACBASE/$TRACNAME/conf/trac.ini

echo "upgrading mysql database..."
trac-admin $TRACBASE/$TRACNAME upgrade --no-backup

To remove schema definitions from the sqlite dump and fix some incompatibilities I made this python script referenced above as cleansql.py:

#!/usr/bin/env python

import sys
import re

file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
# fix sql for reports
file = re.sub(r'CAST\((.+) AS int\)', lambda m: 'CAST(%s AS signed)' % m.groups(1), file)

sys.stdout.write(file)

And that's it. I hope this will benefit someone tasked with the same job.

Recently I had to implement a web form where the user was required to enter the date and time for a certain event. Many data types can conveniently be represented using several form fields to enhance usability. For instance if you want a form field representing the time, instead of requiring a specific time format to be entered, you can use a field for hours and a field for minutes. This is a very common thing to do. Using Django you can achieve this by implementing a custom MultiValueField and MultiWidget. The benefit of using a MultiValueField and MultiWidget to do this, is that you can refer to the fields as one field in django, and get a compressed data type back, like in this case a datetime.time type.

Since this is not yet documented, I am going to show how I implemented a time field with a select field for hours and a select field for minutes.

It looks like this:

MultiValueField

The responsibility of a MultiValueField is to take a list of field input data and compress them into a suitable return data type. Furthermore it validates each of the field input data.

Here is the custom MultiValueField I implemented for my time field:

class TimeSelectField(MultiValueField):
    """
    Time multi field. Returns datetime.time object. Must be used together with TimeWidget
    """
    def __init__(self, *args, **kwargs):
        fields = (
            forms.ChoiceField(choices=HOUR_CHOICES),
            forms.ChoiceField(choices=MINUTE_CHOICES)
        )
        super(TimeSelectField, self).__init__(fields, *args, **kwargs)
    def compress(self, data_list):
        if data_list:
            return time(hour=int(data_list[0]), minute=int(data_list[1]))
        return None

The compress method just returns a time object from the data that the widget (see below) has returned. The HOUR_CHOICES and MINUTE_CHOICES are lists of valid hour and minute values. We need to specify the fields used, so the MultiValueField can do basic validation of the input data. The TimeSelectField does not render two input fields, it only compresses and validates data. So we need to implement a TimeSelectWidget to use with it.

MultiWidget

Here is the code for the TimeSelectWidget:

class TimeSelectWidget(MultiWidget):
    """
    Time Widget, see TimeSelectField for info.
    """
    def __init__(self, *args, **kwargs):
        widgets = (
            forms.Select(choices=HOUR_CHOICES),
            forms.Select(choices=MINUTE_CHOICES)
        )
        super(TimeSelectWidget, self).__init__(widgets, *args, **kwargs)
    def decompress(self, value):
        if value:
            return [str(value.hour), str(value.minute)]
        return [None, None]
    def format_output(self, rendered_widgets):
        return u'\n'.join(rendered_widgets)

The TimeSelectWidget overrides two methods:

• decompress does the opposite of compress. It takes a compressed data type (in this case a datetime.time) object and returns a data list. This is needed so we can provide an initial value to the widget.

• format_output is used for rendering the widget. Since we have the individual widgets rendered, I just inserted a newline between the two.

Conclusion

We can now use the field as any other regular form field in Django, and do the following in the python shell:

>>> from datetime import time
>>> from django import newforms as forms
>>> from mymodule.forms import TimeSelectField, TimeSelectWidget
>>>
>>> t = time(hour=9, minute=0)
>>> class MyForm(forms.Form)
>>>     time = TimeSelectField(widget=TimeSelectWidget, initial=t)
>>>
>>> f = MyForm()
>>> print f
<tr><th><label for="id_time_0">Time:</label></th><td><select name="time_0" id="id_time_0">
<option value="0">00</option>
<option value="1">01</option>
<option value="2">02</option>
<option value="3">03</option>
<option value="4">04</option>
<option value="5">05</option>
<option value="6">06</option>
<option value="7">07</option>
<option value="8">08</option>
<option value="9" selected="selected">09</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
</select>
<select name="time_1" id="id_time_1">
<option value="00">00</option>
<option value="15">15</option>
<option value="30">30</option>
<option value="45">45</option>
</select></td></tr>