December 2009
November 2009
October 2009
September 2009
June 2009
April 2009
March 2009
February 2009
January 2009
December 2008
November 2008
October 2008
July 2008
June 2008
October 2007
September 2007
Want to have fun? Try migrating an existing web application between different database technologies! With Django and SQLAlchemy, it actually isn't that hard! I used the following procedure to migrate both deathcat.org and this blog to Postgres. I'm assuming your know you to use Postgres and you are doing this as a Postgres superuser. All of this assumes ident authentication for Postgres, but should be easily tweaked for other configurations.
Make a directory in your Django application to store these scripts, like scripts/. Make sure this directory resides at the same level as manage.py. Now, get the code for my SQLAlchemy table copier and put it in a new file called puller.py. Comment out the line that reads table.metadata.create_all(dengine).
Now put this in a file called migrate2pg.sh:
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 | #!/bin/bash database=my_django_db mysql_user=django_user mysql_pass=django_passwd mysql_connection_string="mysql://$mysql_user:$mysql_pass@localhost/$database?charset=utf8" postgres_connection_string="postgres:///$database" tables=$(echo 'show tables' | mysql -u $mysql_user -p"$mysql_pass" $database | xargs echo | cut -d ' ' -f 2-) echo $tables echo "Dropping old postgres database, if any" dropdb $database echo "Creating new database" createdb $database echo "Setting up Django schema" ../manage.py syncdb --noinput echo "Removing initial data" echo 'DELETE FROM auth_permission' | psql $database echo 'DELETE FROM django_content_type' | psql $database echo "Importing data from MySQL" python puller.py \ -f $mysql_connection_string \ -t $postgres_connection_string \ $tables echo "Fixing sequences" for table in $tables do echo Fixing "${table}'s sequence" echo "select setval('${table}_id_seq', max(id)) from ${table};" | psql $database done |
Tweak the variables at the top as necessary for your case. Run a bash migrate2pg.sh and read the messages. One error you will see is a during the Fixing sequences phase when the script attempts to fix django_session_id_seq sequence. Ignore this error.
The final part is to give permissions or ownership to the user who will be accessing the data. I'm assuming you can do this, but if you are using Postgres ident authentication and apache, then here's a helpful script for you.
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 | #!/bin/bash database='my_django_db' echo "Granting apache rights to ${database}" echo "GRANT ALL ON DATABASE ${database} TO apache;" | psql $database tables=$(echo '\dt' | psql $database | awk -F '|' '/table/ {print $2}') sequences=$(echo '\ds' | psql $database | awk -F '|' '/sequence/ {print $2}') echo "Tables:" $tables echo echo "Sequences:" $sequences echo tablesql=$(for table in $tables; do echo "ALTER TABLE $table OWNER TO apache;"; done) seqsql=$(for seq in $sequences; do echo "ALTER TABLE $seq OWNER TO apache;"; done) echo "Table Alteration SQL:" $tablesql echo echo "Sequence Alteration SQL:" $seqsql echo echo $tablesql $seqsql | psql $database |
Adding tagging to your Django application is easy, but can have some complications if you have existing data. In this post, I will cover how I accomplished it on this blog.
The first thing to do is install django-tagging. I am using Django 1.0, so I have to get the code from their subversion repository. You will not have to do this once version 0.3 comes out. Once django-tagging is installed, we can add tagging to our INSTALLED_APPS in our settings.py and do a ./manage.py syncdb. That's all you have to do for basic functionality in django-tagging. You will want more than basic functionality, am I right?
The next step you will want to take is registering your models with tagging.register as shown below.
import tagging from django.db import models class Entry(models.Model): # Imagine the rest of your Entry model here pass try: tagging.register(Entry) except tagging.AlreadyRegistered: # You might wonder what is going on here. # This is a bug in the svn release. For some reason, the authors' cannot handle # re-registering gracefully, so we have to. # This should be fixed in later releases, but your code will still work here after it is. pass
After your model is registered, you will have access to several convenience methods and attributes, like yourmodelinstance.tags. It makes handling tags in your views and templates a dream.
What about Django's awesome admin though? It is a pain in the butt with just this to attach tags to a model instance. You have go to Tagged Objects, select the tag to apply to your model instance, select the model to be tagged, and finally the id of the instance. You do not want to do that. There is a smarter and faster way. We first need to add a TagField to our model like so.
import tagging from django.db import models from tagging.fields import TagField class Entry(models.Model): # Imagine your other model fields preceded this tags_string = TagField() # Don't name this tags as it will conflict with the tags attribute gained # through registering the model with tagging try: tagging.register(Entry) except tagging.AlreadyRegistered: pass
If this is a fresh application without real data, you are done. You may not be so lucky, like myself. You will have to alter the database table yourself because Django, as great as it is, does not do this...yet. Here is something like commands you would run to alter your table, assuming your database is called yourdb, your application is called yourapp, your model is called yourmodel, and the last field of your model before tags_string is called last_field:
ALTER TABLE yourdb.yourapp_yourmodel ADD tags_string VARCHAR(255) AFTER last_field; UPDATE yourdb.yourapp_yourmodel SET tags_string = ''; ALTER TABLE yourdb.yourapp_yourmodel MODIFY COLUMN tags_string VARCHAR(255) NOT NULL;
You can inject this into mysql easily by running something like this:
mysql -u root -p < file_the_earlier_sql_in
Now, your table is just like a fresh one, schema-wise. Your data is the same with a blank tags_string field. Tags can be added from the model edit page in Django admin now. I will cover adding tags to your views and templates in the next post.
