my blog

How to get the current list of indexes and columns?

So how to get the list of all columns for all tables with additional information about indexes.
This little query will return the three columns: table_name, column_name and index_name (if there is no index then it will be null).

select 
    ic.table_name, ic.column_name, iss.index_name
from
    information_schema.columns ic
        inner join
    information_schema.tables it ON it.table_name = ic.table_name
        and it.table_type = 'BASE TABLE'
        left join
    information_schema.statistics iss ON iss.column_name = ic.column_name
group by ic.table_name , ic.column_name
order by ic.table_name;

Unique id across multiple tables on MySQL

The idea is to have one global unique id for every entries in MySQL tables. So instead of using auto_increment property for primary key I would like to have some kind of global generator. Here is the first draft of the solution.

1. First step is to create a table in which the ids will be stored:

CREATE TABLE `uuid` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stub` TINYINT(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Next we have to define triggers. Lets assume that we have some tables t2 and t3. We have to create triggers on each of them. The trigger is simple as a rock:

DELIMITER $$
CREATE TRIGGER `t3_uuid` BEFORE INSERT
ON `t3`
FOR EACH ROW BEGIN
REPLACE INTO `uuid` (`stub`) VALUES (1);
SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

We create the trigger t3_uuid on the table t3 which will be run before each insert and will set `id` from the LAST_INSERT_ID() command.

Dictionary with regular expression

import re

class RegexDict(dict):
  def __init__(self, d):
    dict.__init__(self, d)

  def __getitem__(self, key):
    try:
      return super(RegexDict, self).__getitem__(key)
    except KeyError as ex:
      for kkey, value in self.iteritems():
        if re.match(kkey, str(key)):
          return value
      raise ex

usage:

z = RegexDict({
  'a': 100,
  '\d+': 300,
  1: 3
  })
z['re#\d+'] = 100

print z['a']
print z[2]
print z['a']
print z[1]
print z['re#200']

results:

Convert jmol file to png

Simple note, from time to time I am looking for command which convert my xyz file into png. Here is the command:

C="connect delete; rotate y 15; rotate x 20; spacefill 80; background white; zoom 110;"
for f in *.jmol; do echo "${f}"; echo "$C write $f.png; exit" | jmol -nL "${f}" -s - ; done

First line set up config, in this case we disable bonds, rotate in y-plane an about 15 degree and in x-plane 20 degree, set background to white, zoom molecule and set size of atoms to 80%;

Remote PDB and Django

Recently I have had to debug some error in Django system. In fact the problem was somewhere inside views and the debug page did not give any important information (as usually) and randomly show different place in .py files where exception was raised. I could not put standard pdb debugger, cause the problem appeared only on nginx+uwsgi setup. I could not reproduce it on internal django webserver.
How to put debugger in uwsgi environment? Of course we do not have access to terminal in that case but we can use remote debugger. I have found very small and simply debugger rpdb:
http://tamentis.com/projects/rpdb/
Simply install it by pip and then put in the code:

import rpdb; rpdb.set_trace()

By default, rpdb listen on 4444 port so the next step is to connect to it by e.g. nc:

$ nc zuzia 4444

[teodor@zuzia production]$ nc zuzia 4444
--Call--
> /home/teodor/project/test/lib/python2.7/site-packages/rpdb/__init__.py(37)shutdown()
-> def shutdown(self):
(Pdb) w
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/management/commands/runserver.py(107)inner_run()
-> run(self.addr, int(self.port), handler, ipv6=self.use_ipv6)
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/servers/basehttp.py(696)run()
-> httpd.serve_forever()
  /usr/lib64/python2.7/SocketServer.py(227)serve_forever()
-> self._handle_request_noblock()
  /usr/lib64/python2.7/SocketServer.py(284)_handle_request_noblock()
-> self.process_request(request, client_address)
  /usr/lib64/python2.7/SocketServer.py(310)process_request()
-> self.finish_request(request, client_address)
  /usr/lib64/python2.7/SocketServer.py(323)finish_request()
-> self.RequestHandlerClass(request, client_address, self)
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/servers/basehttp.py(570)__init__()
-> BaseHTTPRequestHandler.__init__(self, *args, **kwargs)
  /usr/lib64/python2.7/SocketServer.py(639)__init__()
-> self.handle()
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/servers/basehttp.py(615)handle()
-> handler.run(self.server.get_app())
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/servers/basehttp.py(283)run()
-> self.result = application(self.environ, self.start_response)
  /home/teodor/project/test/lib/python2.7/site-packages/django/contrib/staticfiles/handlers.py(68)__call__()
-> return self.application(environ, start_response)
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/handlers/wsgi.py(273)__call__()
-> response = self.get_response(request)
  /home/teodor/project/test/lib/python2.7/site-packages/django/core/handlers/base.py(111)get_response()
-> response = callback(request, *callback_args, **callback_kwargs)
  /media/Crypted/teodor/projects/test/web/views.py(525)wrapper()
-> return panel_view(request, *args, **kw)
  /media/Crypted/teodor/projects/test/web/views.py(540)test()
-> import rpdb; rpdb.set_trace()
  /home/teodor/project/test/lib/python2.7/site-packages/rpdb/__init__.py(62)set_trace()
-> debugger.shutdown()
> /home/teodor/project/test/lib/python2.7/site-packages/rpdb/__init__.py(37)shutdown()
-> def shutdown(self):
(Pdb)