PHP application with Mysql database to a Django app with Postgres Database

Recently I had a chance to work on a project, where a PHP app had to be converted to a Django one.

The work seemed trivial, but it brought a few difficult technicalities. It had a Mysql DB, with few tables. Overall, I had to  -

• Convert them into Django models
• Convert the app into Django
• Setup the app on Heroku using Postgres

1. Converting the models to Django is easy. Django provides an inspectdb command. It looks at the current model structures, inspects it and provides django model accordingly.
2. This is a nice feature. It gave me a push forward to begin the app development. However, this feature lacked some points that I would like to point out - 
1. In Mysql, we have a set type . This is basically similar to choices in Django models. I had to manually do that. 
2. The foreign key constraint is set to id by default. The command could check this and set the field in model declaration using to_field parameter.
3. The DB views had to created manually. 
3. For changing Mysql Data to Heroku, https://devcenter.heroku.com/articles/heroku-mysql is a good resource.

As always, heroku has to be provided with a S3 access to uploading of static and media files. But thats a different matter.

Ejabberd Dissection

Working on Ejabbered 2.1.13, in order to understand ejabbered db calls and processes, I followed this presentation  - http://www.slideshare.net/ShaojieYang/ejabberd-installation-configuration-15508995
.
 It showed an easy process to connect Ejabberd to an external database.
Here I would be going a step ahead and saving the DB calls Ejabberd makes, assuming this will be same, when it using its internal Mnesia DB.

When adding a new user(registration)  from web admin - test3@192.168.1.250
----------------------------------------------------------------------------------------------------------
  113 Query select password from users where username='admin'
  112 Query select password from users where username='admin'
  112 Query select password from users where username='test3'

  104 Query insert into users(username, password) values ('test3', 'test3')

Below for count for Offline Messages and  Last Activity shown in admin
-----------------------------------------------------------------------
108 Query select username from users
104 Query select count(*) from spool  where username='admin'
104 Query select seconds, state from last where username='admin'
107 Query select count(*) from spool  where username='admin3'
108 Query select seconds, state from last where username='admin3'

Connecting a user - test2@192.168.1.250
-------------------------------------------------------
109 Query select password from users where username='test2'
113 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
104 Query select name from privacy_default_list where username='test2'
105 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
112 Query select jid, grp from rostergroups where username='test2'
109 Query select password from users where username='test2'
113 Query select name from privacy_default_list where username='test2'
105 Query select name from privacy_list where username='test2'
105 Query select id from privacy_list where username='test2' and name='invisible'
110 Query begin
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query insert into privacy_list(username, name) values ('test2', 'invisible')
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query delete from privacy_list_data where id='1'
110 Query insert into privacy_list_data(id, t, value, action, ord, match_all, match_iq, match_message, match_presence_in, match_presence_out ) values ('1', 'n', '', 'd', '1', '0', '0', '0', '0', '1')
110 Query commit
104 Query begin
104 Query select username, xml from spool where username='test2'  order by seq
104 Query delete from spool where username='test2'
104 Query commit
108 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
110 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
113 Query select jid, grp from rostergroups where username='test2'
105 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
104 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'

111 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'

Adding contact - test1@192.168.1.250 by test4
--------------------------------------------------------------

122 Query begin
122 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
122 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='N', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
122 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test4', 'test1@192.168.1.250', '', 'N', 'N', '', 'N', '', 'item')
122 Query delete from rostergroups       where username='test4'         and jid='test1@192.168.1.250'
122 Query commit
131 Query begin
131 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
131 Query select grp from rostergroups where username='test4' and jid='test1@192.168.1.250'
131 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='O', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
131 Query commit
129 Query select name from privacy_default_list where username='test1'
128 Query select password from users where username='test1'
123 Query begin
123 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test1' and jid='test4@192.168.1.250'
123 Query update rosterusers set username='test1', jid='test4@192.168.1.250', nick='', subscription='N', ask='I', askmessage='', server='N', subscribe='', type='item' where username='test1' and jid='test4@192.168.1.250'
123 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test1', 'test4@192.168.1.250', '', 'N', 'I', '', 'N', '', 'item')
123 Query commit

Now asks for permission 

in rosterusers table - subscription field is N (related to pending as others which are already accepted contacts have B) and  ask field is O for test4 and I for test1 (test4 added test1)

Ater accepting

-------------------

122 Query begin

122 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'

122 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='N', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'

122 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test4', 'test1@192.168.1.250', '', 'N', 'N', '', 'N', '', 'item')

122 Query delete from rostergroups       where username='test4'         and jid='test1@192.168.1.250'

122 Query commit

131 Query begin

131 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'

131 Query select grp from rostergroups where username='test4' and jid='test1@192.168.1.250'

131 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='O', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'

131 Query commit

129 Query select name from privacy_default_list where username='test1'

128 Query select password from users where username='test1'

123 Query begin

123 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test1' and jid='test4@192.168.1.250'

123 Query update rosterusers set username='test1', jid='test4@192.168.1.250', nick='', subscription='N', ask='I', askmessage='', server='N', subscribe='', type='item' where username='test1' and jid='test4@192.168.1.250'

123 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test1', 'test4@192.168.1.250', '', 'N', 'I', '', 'N', '', 'item')

123 Query commit

Memory footprint 
-----------------------

connecting 100 clients to ejabberd - no issue,
there are 9 instances running with around 70mb each.

Used this script to hit to server (Uses XMPPY )
-----------------------------------------

It assumes that we have test users already registered with username test1, test2.. and pass test1 and test2.  (ejabberdctl register command can be used to make such users)

from xmpp import *

clients = []
for i in range(100):
    clients.append(Client('192.168.1.250'))
for cl in clients:
    cl.connect(server=('192.168.1.250',5222))
for i in range(1,101):
    clients[i-1].auth('test%d'%i,'test%d'%i)

while True:
    for cl in clients:
        cl.sendPresence()

        cl.Process(2)

Sum good post
http://metajack.im/2008/08/27/migrating-to-ejabberd-the-gory-details/‎