Loading object properties from an external file

Recently I came up with the idea that I will hold the running properties in the configuration file. The idea was to use the Python syntax but keeps it simple as possible.
The second thing is that the config file can be anywhere and I do not want force people to make any Python modules (with those strange __init__.py files). They will simply forget to create.
On the other hand I want to keep those attributes in the class object because then I can use that object in other part of the program. First idea was to simply use execfile()
Here is how I overcome the problem, I create a BaseSettings class with the constructor that takes an path to the settings file:

class BaseSettings(object):
  def __init__(self, settings_file):
    """Constructor of the settings class.

      Args:
        settings_file: The full path to the settings file.
    """

    _required_sections = set([
        'ANGLES', 
        'DIHEDRALS', 
        'PAIRS', 
        'ATOM_PAIRS', 
        'BOND_CONFIG',
        'TOTAL_NUMBER_OF_CROSSLINKS'
        ])
    settings_variables = {}
    copy_globals = globals().copy()
    try:
      execfile(settings_file, copy_globals, settings_variables)
    except SyntaxError as ex:
      print 'There is an error in your config file %s in line %s' % (
          ex.filename, ex.lineno)
      print '%d: %s' % (ex.lineno, ex.text)
      print ex.msg
      sys.exit(2)

    # Checks if some attributes are missing in the settings file.
    missing_sections = _required_sections - set(settings_variables)

    if missing_sections:
      Exception(
          'Invalid settings file, those sections are missing: %s' % (
              ','.join(missing_sections))
    # Loads the variables as it they would be and attributes of the class.
    self.__dict__.update(settings_variables)

and then I use execfile() with the copied globals() and the empty dict settings_variables that works as the container for the locals(). I had to copy globals dict because otherwise I had those attributes in the globals.
Afterwards the variables from the settings file can be found in the settings_variables dictionary. So the only things was to update the __dict__ of the class with those variables.

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