Composite unique constraint

Composite unique constraint are very common in real database entities. In Django models, those constraints are expressed by the unique_together property of the Meta class.
Lets say that we have an Django model class:

class ModelA(models.Model):
  property1 = models.CharField(max_length=255, null=True)
  property2 = models.IntegerField()

  class Meta(object):
    unique_together = ('property1', 'property2')

It looks fine, now lets say that we want to save something in this model:

m3 = ModelA(property1=None, property2=1)
m3.save()
m4 = ModelA(property1=None, property2=1)
m4.save()

What we could expect is that the second save will fail because of the composite unique constraint. What actually will happen is that there will be two rows in db with property1 set to NULL and property2 set to 1. This is because the SQL standard define that NULL is not a value but the state so in that case the NULL value is not treat as unique accros the rows.  
To solve the issue we have to take care about checking the uniqueness on the Django model level. The save method of the models has to be overwrite. Below is the example how to define abstract class which can be used instead of Django models.Model class.

class Model(models.Model):
  class Meta(object):
    abstract = True

  def _check_uniqueness(self):
    nullable_field_names = set([x.name for x in self._meta._fields() if x.null])

    for unique_together in self._meta.unique_together:
      if set(unique_together).intersection(nullable_field_names):
        values = [(x, getattr(self, x)) for x in unique_together]
        queryset = {}
        for field, value in values:
          if value is None:
            queryset['%s__isnull' % field] = True
          else:
            queryset[field] = value
        exists_query = self.__class__.objects.filter(**queryset)
        if self.pk is not None:
          exists_query = exists_query.exclude(id=self.id)
        if exists_query.exists():
          return unique_together
    return None

    def save(self, *args, **kwargs):
      unique_together = self._check_uniqueness()
      if unique_together is not None:
        raise db.IntegrityError('columns %s are not unique' % ', '.join(unique_together))
      super(Model, self).save(*args, **kwargs)

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:

100
300
3
100

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%;