Category Archives: Websites

Extreme Attributed Metadata with Autofac

Introduction

If you are anything like me, you love reflection in any programming language. For the last two years or so I have been writing code for work almost exclusively in C# and have found its reflection system to be a pleasure to use. Its simple, can be fast, and can do so much.

I recently started using Autofac at work to help achieve Inversion of Control within our projects. It has honestly been the most life changing C# library (sorry Autofac, jQuery and Knockout still take the cake for “life-changing in all languages”) I have ever used and has changed the way I decompose problems when writing programs.

This article will cover some very interesting features of the Autofac Attributed Metadata module. It is a little lengthy, so I have here what will be covered:

What is autofac?
Attributed Metadata: The Basics
The IMetadataProvider interface
IMetadataProvider: Making a set of objects
IMetadataProvider: Hierarchical Metadata

What is Autofac?

This post assumes that the reader is at least passingly familiar with Autofac. However, I will make a short introduction: Autofac allows you to “compose” your program structure by “registering” components and then “resolving” them at runtime. The idea is that you define an interface for some object that does “something” and create one or more classes that implement that interface, each accomplishing the “something” in their own way. Your parent class, which needs to have one of those objects for doing that “something” will ask the Autofac container to “resolve” the interface. Autofac will give back either one of your implementations or an IEnumerable of all of your implementations (depending on how you ask it to resolve). The “killer feature” of Autofac, IMO, is being able to use constructor arguments to recursively resolve the “dependencies” of an object. If you want an implementation of an interface passed into your object when it is resolved, just put the interface in the constructor arguments and when your object is resolved by Autofac, Autofac will resolve that interface for you and pass it in to your constructor. Now, this article isn’t meant to introduce Autofac, so I would definitely recommend reading up on the subject.

Attributed Metadata: The Basics

One of my most favorite features has been Attributed Metadata. Autofac allows Metadata to be included with objects when they are resolved. Metadata allows one to specify some static parameters that are associated with a particular implementation of something registered with the container. This Metadata is normally created during registration of the particular class and, without this module, must be done “manually”. The Attributed Metadata module allows one to use custom attributes to specify the Metadata for the class rather than needing to specify it when the class is registered. This is an absurdly powerful feature which allows for doing some pretty interesting things.

For my example I will use a “extendible” letter formatting program that adds some text to the content of a “letter”. I define the following interface:

interface ILetterFormatter
{
    string FormatLetter(string content);
}

interface ILetterFormatter

{

string FormatLetter(string content);

}

This interface is for something that can “format” a letter in some way. For starters, I will define two implementations:

class ImpersonalLetterFormatter : ILetterFormatter
{
    public string MakeLetter(string content)
    {
        return "To Whom It May Concern:nn" + content;
    }
}

class PersonalLetterFormatter : ILetterFormatter
{
    public string MakeLetter(string content)
    {
        return "Dear Individual,nn" + content;
    }
}

class ImpersonalLetterFormatter : ILetterFormatter

{

public string MakeLetter(string content)

{

return "To Whom It May Concern:nn" + content;

}

class PersonalLetterFormatter : ILetterFormatter

{

public string MakeLetter(string content)

{

return "Dear Individual,nn" + content;

}

Now, here is a simple program that will use these formatters:

class MainClass
{
    public static void Main (string[] args)
    {
        var builder = new ContainerBuilder();

        //register all ILetterFormatters in this assembly
        builder.RegisterAssemblyTypes(typeof(MainClass).Assembly)
            .Where(c => c.IsAssignableTo<ILetterFormatter>())
            .AsImplementedInterfaces();

        var container = builder.Build();

        using (var scope = container.BeginLifetimeScope())
        {
            //resolve all formatters
            IEnumerable<ILetterFormatter> formatters = scope.Resolve<IEnumerable<ILetterFormatter>>();

            //What do we do now??? So many formatters...which is which?
        }
    }
}

class MainClass

{

public static void Main (string[] args)

{

var builder = new ContainerBuilder();

//register all ILetterFormatters in this assembly

builder.RegisterAssemblyTypes(typeof(MainClass).Assembly)

.Where(c => c.IsAssignableTo<ILetterFormatter>())

.AsImplementedInterfaces();

var container = builder.Build();

using (var scope = container.BeginLifetimeScope())

{

//resolve all formatters

IEnumerable<ILetterFormatter> formatters = scope.Resolve<IEnumerable<ILetterFormatter>>();

//What do we do now??? So many formatters...which is which?

}

Ok, so we have ran into a problem: We have a list of formatters, but we don’t know which is which. There are a couple different solutions:

Use the “is” test or do a “soft cast” using the “as” operator to a specific type. This is bad because it requires that the resolver know about the specific implementations of the interface (which is what we are trying to avoid)
Just choose one based on order. This is bad because the resolution order is just as guaranteed as reflection order in C#…which is not guaranteed at all. We can’t be sure they will be resolved in the same order each time.
Use metadata at registration time and resolve it with metadata. The issue here is that if we used RegisterAssemblyTyps like above, it makes registration difficult. Also, once we get any sizable number of things registered with metadata, it becomes unmanageable IMO.
Use attributed metadata! Example follows…

We define another class:

[MetadataAttribute]
sealed class LetterFormatterAttribute : Attribute
{
    public string Name { get; private set; }

    public LetterFormatterAttribute(string name)
    {
        this.Name = name;
    }
}

[MetadataAttribute]

sealed class LetterFormatterAttribute : Attribute

{

public string Name { get; private set; }

public LetterFormatterAttribute(string name)

{

this.Name = name;

}

Marking it with System.ComponetModel.Composition.MetadataAttributeAttribute (no, that’s not a typo) will make the Attributed Metadata module place the public properties of the Attribute into the metadata dictionary that is associated with the class at registration time.

We mark the classes as follows:

[LetterFormatter("Impersonal")]
class ImpersonalLetterFormatter : ILetterFormatter
...

[LetterFormatter("Personal")]
class PersonalLetterFormatter : ILetterFormatter
...

[LetterFormatter("Impersonal")]

class ImpersonalLetterFormatter : ILetterFormatter

...

[LetterFormatter("Personal")]

class PersonalLetterFormatter : ILetterFormatter

...

And then we change the builder to take into account the metadata by asking it to register the Autofac.Extras.Attributed.AttributedMetadataModule. This will cause the Attributed Metadata extensions to scan all of the registered types (past, present, and future) for MetadataAttribute-marked attributes and use the public properties as metadata:

var builder = new ContainerBuilder();

builder.RegisterModule<AttributedMetadataModule>();

builder.RegisterAssemblyTypes(typeof(MainClass).Assembly)
    .Where(c => c.IsAssignableTo<ILetterFormatter>())
    .AsImplementedInterfaces();

var builder = new ContainerBuilder();

builder.RegisterModule<AttributedMetadataModule>();

builder.RegisterAssemblyTypes(typeof(MainClass).Assembly)

.Where(c => c.IsAssignableTo<ILetterFormatter>())

.AsImplementedInterfaces();

Now, when we resolve the ILetterFormatter classes, we can either use Autofac.Features.Meta<TImplementation> or Autofac.Features.Meta<TImplementation, TMetadata>. I’m a personal fan of the “strong” metadata, or the latter. It causes the metadata dictionary to be “forced” into a class rather than just directly accessing the metadata dictionary. This removes any uncertainty about types and such. So, I will create a class that will hold the metadata when the implementations are resolved:

class LetterMetadata
{
    public string Name { get; set; }
}

class LetterMetadata

{

public string Name { get; set; }

}

It would worthwhile to note that the individual properties must have a value in the metadata dictionary unless the DefaultValue attribute is applied to the property. For example, if I had an integer property called Foo an exception would be thrown when metadata was resolved since I have no corresponding Foo metadata. However, if I put DefaultValue(6) on the Foo property, no exception would be thrown and Foo would be set to 6.

So, we now have the following inside our using statement that controls our scope in the main method:

//resolve all formatters
IEnumerable<Meta<ILetterFormatter, LetterMetadata>> formatters = scope.Resolve<IEnumerable<Meta<ILetterFormatter, LetterMetadata>>>();

//we will ask how the letter should be formatted
Console.WriteLine("Formatters:");
foreach (var formatter in formatters)
{
    Console.Write("- ");
    Console.WriteLine(formatter.Metadata.Name);
}

ILetterFormatter chosen = null;
while (chosen == null)
{
    Console.WriteLine("Choose a formatter:");
    string name = Console.ReadLine();
    chosen = formatters.Where(f => f.Metadata.Name == name).Select(f => f.Value).FirstOrDefault();

    if (chosen == null)
        Console.WriteLine(string.Format("Invalid formatter: {0}", name));
}

//just for kicks, we say the first argument  is our letter, so we format it and output it to the console
Console.WriteLine(chosen.FormatLetter(args[0]));

//resolve all formatters

IEnumerable<Meta<ILetterFormatter, LetterMetadata>> formatters = scope.Resolve<IEnumerable<Meta<ILetterFormatter, LetterMetadata>>>();

//we will ask how the letter should be formatted

Console.WriteLine("Formatters:");

foreach (var formatter in formatters)

{

Console.Write("- ");

Console.WriteLine(formatter.Metadata.Name);

}

ILetterFormatter chosen = null;

while (chosen == null)

{

Console.WriteLine("Choose a formatter:");

string name = Console.ReadLine();

chosen = formatters.Where(f => f.Metadata.Name == name).Select(f => f.Value).FirstOrDefault();

if (chosen == null)

Console.WriteLine(string.Format("Invalid formatter: {0}", name));

}

//just for kicks, we say the first argument is our letter, so we format it and output it to the console

Console.WriteLine(chosen.FormatLetter(args[0]));

The IMetadataProvider Interface

So, in the contrived example above, we were able to identify a class based solely on its metadata rather than doing type checking. What’s more, we were able to define the metadata through attributes. However, this is old hat for Autofac. This feature has been around for a while.

When I was at work the other day, I needed to be able to handle putting sets of things into metadata (such as a list of strings). Autofac makes no prohibition on this in its metadata dictionary. The dictionary is of the type IDictionary<string, object>, so it can hold pretty much anything, including arbitrary objects. The problem is that the Attributed Metadata module had no way to do this easily. Attributes can only take certain types as constructor arguments and that seriously places a limit on what sort of things could be put into metadata via attributes easily.

I decided to remedy this and after submitting an idea for autofac via a pull request, having some discussion, changing the exact way to accomplish this goal, and fixing things up, my pull request was merged into autofac which resulted in a new feature: The IMetadataProvider interface. This interface provides a way for metadata attributes to control how exactly they produce metadata. By default, the attribute would just have its properties scanned. However, if the attribute implemented the IMetadataProvider interface, a method will be called to get the metadata dictionary rather than doing the property scan. When an IMetadataProvider is found, the GetMetadata(Type targetType) method will be called with the first argument set to the type that is being registered. This allows the IMetadataProvider the opportunity to know which class it is actually applied to; something normally not possible without explicitly passing the attribute a Type in a constructor argument.

To get an idea of what this would look like, here is a metadata attribute which implements this interface:

[MetadataAttribute]
class LetterFormatterAttribute : Attribute, IMetadataProvider
{
    public string Name { get; private set; }

    public LetterFormatterAttribute(string name)
    {
        this.Name = name;
    }

    #region IMetadataProvider implementation

    public IDictionary<string, object> GetMetadata(Type targetType)
    {
        return new Dictionary<string, object>()
        {
            { "Name", this.Name }
        };
    }

    #endregion
}

[MetadataAttribute]

class LetterFormatterAttribute : Attribute, IMetadataProvider

{

public string Name { get; private set; }

public LetterFormatterAttribute(string name)

{

this.Name = name;

}

#region IMetadataProvider implementation

public IDictionary<string, object> GetMetadata(Type targetType)

{

return new Dictionary<string, object>()

{

{ "Name", this.Name }

};

}

#endregion

}

This metadata doesn’t do much more than the original. It actually returns exactly what would be created via property scanning. However, this allows much more flexibility in how MetadataAttributes can provide metadata. They can scan the type for other attributes, create arbitrary objects, and many other fun things that I can’t even think of.

IMetadataProvider: Making a set of objects

Perhaps the simplest application of this new IMetadataProvider is having the metadata contain a list of objects. Building on our last example, we saw that the “personal” letter formatter just said “Dear Individual” every time. What if we could change that so that there was some way to pass in some “properties” or “options” provided by the caller of the formatting function? We can do this using an IMetadataProvider. We make the following changes:

class FormatOptionValue
{
    public string Name { get; set; }
    public object Value { get; set; }
}

interface IFormatOption
{
    string Name { get; }
    string Description { get; }
}

interface IFormatOptionProvider
{
    IFormatOption GetOption();
}

interface ILetterFormatter
{
    string FormatLetter(string content, IEnumerable<FormatOptionValue> options);
}

[MetadataAttribute]
sealed class LetterFormatterAttribute : Attribute, IMetadataProvider
{
    public string Name { get; private set; }

    public LetterFormatterAttribute(string name)
    {
        this.Name = name;
    }

    public IDictionary<string, object> GetMetadata(Type targetType)
    {
        var options = targetType.GetCustomAttributes(typeof(IFormatOptionProvider), true)
            .Cast<IFormatOptionProvider>()
            .Select(p => p.GetOption())
            .ToList();

        return new Dictionary<string, object>()
        {
            { "Name", this.Name },
            { "Options", options }
        };
    }
}

//note the lack of the [MetadataAttribute] here. We don't want autofac to scan this for properties
[AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
sealed class StringOptionAttribute : Attribute, IFormatOptionProvider
{
    public string Name { get; private set; }

    public string Description { get; private set; }

    public StringOptionAttribute(string name, string description)
    {
        this.Name = name;
        this.Description = description;
    }

    public IFormatOption GetOption()
    {
        return new StringOption()
        {
            Name = this.Name,
            Description = this.Description
        };
    }
}

public class StringOption : IFormatOption
{
    public string Name { get; set; }

    public string Description { get; set; }

    //note that we could easily define other properties that
    //do not appear in the interface
}

class LetterMetadata
{
    public string Name { get; set; }

    public IEnumerable<IFormatOption> Options { get; set; }
}

class FormatOptionValue

{

public string Name { get; set; }

public object Value { get; set; }

}

interface IFormatOption

{

string Name { get; }

string Description { get; }

}

interface IFormatOptionProvider

{

IFormatOption GetOption();

}

interface ILetterFormatter

{

string FormatLetter(string content, IEnumerable<FormatOptionValue> options);

}

[MetadataAttribute]

sealed class LetterFormatterAttribute : Attribute, IMetadataProvider

{

public string Name { get; private set; }

public LetterFormatterAttribute(string name)

{

this.Name = name;

}

public IDictionary<string, object> GetMetadata(Type targetType)

{

var options = targetType.GetCustomAttributes(typeof(IFormatOptionProvider), true)

.Cast<IFormatOptionProvider>()

.Select(p => p.GetOption())

.ToList();

return new Dictionary<string, object>()

{

{ "Name", this.Name },

{ "Options", options }

};

}

//note the lack of the [MetadataAttribute] here. We don't want autofac to scan this for properties

[AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]

sealed class StringOptionAttribute : Attribute, IFormatOptionProvider

{

public string Name { get; private set; }

public string Description { get; private set; }

public StringOptionAttribute(string name, string description)

{

this.Name = name;

this.Description = description;

}

public IFormatOption GetOption()

{

return new StringOption()

{

Name = this.Name,

Description = this.Description

};

}

public class StringOption : IFormatOption

{

public string Name { get; set; }

public string Description { get; set; }

//note that we could easily define other properties that

//do not appear in the interface

}

class LetterMetadata

{

public string Name { get; set; }

public IEnumerable<IFormatOption> Options { get; set; }

}

Ok, so this is just a little bit more complicated. There are two changes to pay attention to: Firstly, the FormatLetter function now takes a list of FormatOptionValues. The second change is what enables the caller of FormatLetter to know which options to pass in. The LetterFormatterAttribute now scans the type in order to construct its metadata dictionary by looking for attributes that describe what options it needs. I feel like the usage of this is best illustrated by decorating our PersonalLetterFormatter for it to have some metadata describing the options that it requires:

[LetterFormatter("Personal")]
[StringOption(ToOptionName, "Name of the individual to address the letter to")]
class PersonalLetterFormatter : ILetterFormatter
{
    const string ToOptionName = "To";

    public string FormatLetter(string content, IEnumerable<FormatOptionValue> options)
    {
        var toName = options.Where(o => o.Name == ToOptionName).Select(o => o.Value).FirstOrDefault() as string;
        if (toName == null)
            throw new ArgumentException("The " + ToOptionName + " string option is required");

        return "Dear " + toName + ",nn" + content;
    }
}

[LetterFormatter("Personal")]

[StringOption(ToOptionName, "Name of the individual to address the letter to")]

class PersonalLetterFormatter : ILetterFormatter

{

const string ToOptionName = "To";

public string FormatLetter(string content, IEnumerable<FormatOptionValue> options)

{

var toName = options.Where(o => o.Name == ToOptionName).Select(o => o.Value).FirstOrDefault() as string;

if (toName == null)

throw new ArgumentException("The " + ToOptionName + " string option is required");

return "Dear " + toName + ",nn" + content;

}

When the metadata for the PersonalLetterFormatter is resolved, it will contain an IFormatOption which represents the To option. The resolver can attempt to cast the IFormatOption to a StringOption to find out what type it should pass in using the FormatOptionValue.

This can be extended quite easily for other IFormatOptionProviders and IFormatOption pairs, making for a very extensible way to easily declare metadata describing a set of options attached to a class.

IMetadataProvider: Hierarchical Metadata

The last example showed that the IMetadataProvider could be used to scan the class to provide metadata into a structure containing an IEnumerable of objects. It is a short leap to see that this could be used to create hierarchies of arbitrary objects.

For now, I won’t provide a full example of how this could be done, but in the future I plan on having a gist or something showing arbitrary metadata hierarchy creation.

Conclusion

I probably use Metadata more than I should in Autofac. With the addition of the IMetadataProvider I feel like its quite easy to define complex metadata and use it with Autofac’s natural constructor injection system. Overall, the usage of metadata & reflection in my programs has made them quite a bit more flexible and extendable and I feel like Autofac and its metadata system complement the built in reflection system of C# quite well.

Database Abstraction in Python

As I was recently working on trying out the Flask web framework for Python, I ended up wanting to access my MySQL database. Recently at work I have been using entity framework and I have gotten quite used to having a good database abstraction that allows programmatic creation of SQL. While such frameworks exist in Python, I thought it would interesting to try writing one. This is one great example of getting carried away with a seemingly simple task.

I aimed for these things:

Tables should be represented as objects which each instance of the object representing a row
These objects should be able to generate their own insert, select, and update queries
Querying the database should be accomplished by logical predicates, not by strings
Update queries should be optimized to only update those fields which have changed
The database objects should have support for “immutable” fields that are generated by the database

I also wanted to be able to do relations between tables with foreign keys, but I have decided to stop for now on that. I have a structure outlined, but it isn’t necessary enough at this point since all I wanted was a database abstraction for my simple Flask project. I will probably implement it later.

This can be found as a gist here: https://gist.github.com/kcuzner/5246020

Example

Before going into the code, here is an example of what this abstraction can do as it stands. It directly uses the DbObject and DbQuery-inheriting objects which are shown further down in this post.

from db import *
import hashlib

def salt_password(user, unsalted):
    if user is None:
        return unsalted
    m = hashlib.sha512()
    m.update(user.username)
    m.update(unsalted)
    return m.hexdigest()

class User(DbObject):
    dbo_tablename = "users"
    primary_key = IntColumn("id", allow_none=True, mutable=False)
    username = StringColumn("username", "")
    password = PasswordColumn("password", salt_password, "")
    display_name = StringColumn("display_name", "")
    def __init__(self, **kwargs):
        DbObject.__init__(self, **kwargs)
    @classmethod
    def load(self, cur, username):
        selection = self.select('u')
        selection[0].where(selection[1].username == username)
        result = selection[0].execute(cur)
        if len(result) == 0:
            return None
        else:
            return result[0]
    def match_password(self, password):
        salted = salt_password(self, password)
        return salted == self.password

#assume there is a function get_db defined which returns a PEP-249
#database object
def main():
    db = get_db()
    cur = db.cursor()
    user = User.load(cur, "some username")
    user.password = "a new password!"
    user.save(cur)
    db.commit()

    new_user = User(username="someone", display_name="Their name")
    new_user.password = "A password that will be hashed"
    new_user.save(cur)
    db.commmit()

    print new_user.primary_key # this will now have a database assigned id

from db import *

import hashlib

def salt_password(user, unsalted):

if user is None:

return unsalted

m = hashlib.sha512()

m.update(user.username)

m.update(unsalted)

return m.hexdigest()

class User(DbObject):

dbo_tablename = "users"

primary_key = IntColumn("id", allow_none=True, mutable=False)

username = StringColumn("username", "")

password = PasswordColumn("password", salt_password, "")

display_name = StringColumn("display_name", "")

def __init__(self, **kwargs):

DbObject.__init__(self, **kwargs)

@classmethod

def load(self, cur, username):

selection = self.select('u')

selection[0].where(selection[1].username == username)

result = selection[0].execute(cur)

if len(result) == 0:

return None

else:

return result[0]

def match_password(self, password):

salted = salt_password(self, password)

return salted == self.password

#assume there is a function get_db defined which returns a PEP-249

#database object

def main():

db = get_db()

cur = db.cursor()

user = User.load(cur, "some username")

user.password = "a new password!"

user.save(cur)

db.commit()

new_user = User(username="someone", display_name="Their name")

new_user.password = "A password that will be hashed"

new_user.save(cur)

db.commmit()

print new_user.primary_key # this will now have a database assigned id

This example first loads a user using a DbSelectQuery. The user is then modified and the DbObject-level function save() is used to save it. Next, a new user is created and saved using the same function. After saving, the primary key will have been populated and will be printed.

Change Tracking Columns

I started out with columns. I needed columns that track changes and have a mapping to an SQL column name. I came up with the following:

class ColumnSet(object):
    """
    Object which is updated by ColumnInstances to inform changes
    """
    def __init__(self):
        self.__columns = {} # columns are sorted by name
        i_dict = type(self).__dict__
        for attr in i_dict:
            obj = i_dict[attr]
            if isinstance(obj, Column):
                # we get an instance of this column
                self.__columns[obj.name] = ColumnInstance(obj, self)

    @property
    def mutated(self):
        """
        Returns the mutated columns for this tracker.
        """
        output = []
        for name in self.__columns:
            column = self.get_column(name)
            if column.mutated:
                output.append(column)
        return output

    def get_column(self, name):
        return self.__columns[name]

class ColumnInstance(object):
    """
    Per-instance column data. This is used in ColumnSet objects to hold data
    specific to that particular instance
    """
    def __init__(self, column, owner):
        """
        column: Column object this is created for
        initial: Initial value
        """
        self.__column = column
        self.__owner = owner
        self.update(column.default)

    def update(self, value):
        """
        Updates the value for this instance, resetting the mutated flag
        """
        if value is None and not self.__column.allow_none:
            raise ValueError("'None' is invalid for column '" + 
                             self.__column.name + "'")
        if self.__column.validate(value):
            self.__value = value
            self.__origvalue = value
        else:
            raise ValueError("'" + str(value) + "' is not valid for column '" + 
                             self.__column.name + "'")

    @property
    def column(self):
        return self.__column

    @property
    def owner(self):
        return self.__owner

    @property
    def mutated(self):
        return self.__value != self.__origvalue

    @property
    def value(self):
        return self.__value

    @value.setter
    def value(self, value):
        if value is None and not self.__column.allow_none:
            raise ValueError("'None' is invalid for column '" + 
                             self.__column.name + "'")
        if not self.__column.mutable:
            raise AttributeError("Column '" + self.__column.name + "' is not" +
                                 " mutable")
        if self.__column.validate(value):
            self.__value = value
        else:
            raise ValueError("'" + value + "' is not valid for column '" + 
                             self.__column.name + "'")

class Column(object):
    """
    Column descriptor for a column
    """
    def __init__(self, name, default=None, allow_none=False, mutable=True):
        """
        Initializes a column

        name: Name of the column this maps to
        default: Default value
        allow_none: Whether none (db null) values are allowed
        mutable: Whether this can be mutated by a setter
        """
        self.__name = name
        self.__allow_none = allow_none
        self.__mutable = mutable
        self.__default = default

    def validate(self, value):
        """
        In a child class, this will validate values being set
        """
        raise NotImplementedError

    @property
    def name(self):
        return self.__name

    @property
    def allow_none(self):
        return self.__allow_none

    @property
    def mutable(self):
        return self.__mutable

    @property
    def default(self):
        return self.__default

    def __get__(self, owner, ownertype=None):
        """
        Gets the value for this column for the passed owner
        """
        if owner is None:
            return self
        if not isinstance(owner, ColumnSet):
            raise TypeError("Columns are only allowed on ColumnSets")
        return owner.get_column(self.name).value

    def __set__(self, owner, value):
        """
        Sets the value for this column for the passed owner
        """
        if not isinstance(owner, ColumnSet):
            raise TypeError("Columns are only allowed on ColumnSets")
        owner.get_column(self.name).value = value

class StringColumn(Column):
    def validate(self, value):
        if value is None and self.allow_none:
            print "nonevalue"
            return True
        if isinstance(value, basestring):
            print "isstr"
            return True
        print "not string", value, type(value)
        return False

class IntColumn(Column):
    def validate(self, value):
        if value is None and self.allow_none:
            return True
        if isinstance(value, int) or isinstance(value, long):
            return True
        return False

class PasswordColumn(Column):
    def __init__(self, name, salt_function, default=None, allow_none=False, 
                 mutable=True):
        """
        Create a new password column which uses the specified salt function

        salt_function: a function(self, value) which returns the salted string
        """
        Column.__init__(self, name, default, allow_none, mutable)
        self.__salt_function = salt_function
    def validate(self, value):
        return True
    def __set__(self, owner, value):
        salted = self.__salt_function(owner, value)
        super(PasswordColumn, self).__set__(owner, salted)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

class ColumnSet(object):

"""

Object which is updated by ColumnInstances to inform changes

"""

def __init__(self):

self.__columns = {} # columns are sorted by name

i_dict = type(self).__dict__

for attr in i_dict:

obj = i_dict[attr]

if isinstance(obj, Column):

# we get an instance of this column

self.__columns[obj.name] = ColumnInstance(obj, self)

@property

def mutated(self):

"""

Returns the mutated columns for this tracker.

"""

output = []

for name in self.__columns:

column = self.get_column(name)

if column.mutated:

output.append(column)

return output

def get_column(self, name):

return self.__columns[name]

class ColumnInstance(object):

"""

Per-instance column data. This is used in ColumnSet objects to hold data

specific to that particular instance

"""

def __init__(self, column, owner):

"""

column: Column object this is created for

initial: Initial value

"""

self.__column = column

self.__owner = owner

self.update(column.default)

def update(self, value):

"""

Updates the value for this instance, resetting the mutated flag

"""

if value is None and not self.__column.allow_none:

raise ValueError("'None' is invalid for column '" +

self.__column.name + "'")

if self.__column.validate(value):

self.__value = value

self.__origvalue = value

else:

raise ValueError("'" + str(value) + "' is not valid for column '" +

self.__column.name + "'")

@property

def column(self):

return self.__column

@property

def owner(self):

return self.__owner

@property

def mutated(self):

return self.__value != self.__origvalue

@property

def value(self):

return self.__value

@value.setter

def value(self, value):

if value is None and not self.__column.allow_none:

raise ValueError("'None' is invalid for column '" +

self.__column.name + "'")

if not self.__column.mutable:

raise AttributeError("Column '" + self.__column.name + "' is not" +

" mutable")

if self.__column.validate(value):

self.__value = value

else:

raise ValueError("'" + value + "' is not valid for column '" +

self.__column.name + "'")

class Column(object):

"""

Column descriptor for a column

"""

def __init__(self, name, default=None, allow_none=False, mutable=True):

"""

Initializes a column

name: Name of the column this maps to

default: Default value

allow_none: Whether none (db null) values are allowed

mutable: Whether this can be mutated by a setter

"""

self.__name = name

self.__allow_none = allow_none

self.__mutable = mutable

self.__default = default

def validate(self, value):

"""

In a child class, this will validate values being set

"""

raise NotImplementedError

@property

def name(self):

return self.__name

@property

def allow_none(self):

return self.__allow_none

@property

def mutable(self):

return self.__mutable

@property

def default(self):

return self.__default

def __get__(self, owner, ownertype=None):

"""

Gets the value for this column for the passed owner

"""

if owner is None:

return self

if not isinstance(owner, ColumnSet):

raise TypeError("Columns are only allowed on ColumnSets")

return owner.get_column(self.name).value

def __set__(self, owner, value):

"""

Sets the value for this column for the passed owner

"""

if not isinstance(owner, ColumnSet):

raise TypeError("Columns are only allowed on ColumnSets")

owner.get_column(self.name).value = value

class StringColumn(Column):

def validate(self, value):

if value is None and self.allow_none:

print "nonevalue"

return True

if isinstance(value, basestring):

print "isstr"

return True

print "not string", value, type(value)

return False

class IntColumn(Column):

def validate(self, value):

if value is None and self.allow_none:

return True

if isinstance(value, int) or isinstance(value, long):

return True

return False

class PasswordColumn(Column):

def __init__(self, name, salt_function, default=None, allow_none=False,

mutable=True):

"""

Create a new password column which uses the specified salt function

salt_function: a function(self, value) which returns the salted string

"""

Column.__init__(self, name, default, allow_none, mutable)

self.__salt_function = salt_function

def validate(self, value):

return True

def __set__(self, owner, value):

salted = self.__salt_function(owner, value)

super(PasswordColumn, self).__set__(owner, salted)

The Column class describes the column and is implemented as a descriptor. Each ColumnSet instance contains multiple columns and holds ColumnInstance objects which hold the individual column per-object properties, such as the value and whether it has been mutated or not. Each column type has a validation function to help screen invalid data from the columns. When a ColumnSet is initiated, it scans itself for columns and at that moment creates its ColumnInstances.

Generation of SQL using logical predicates

The next thing I had to create was the database querying structure. I decided that rather than actually using the ColumnInstance or Column objects, I would use a go-between object that can be assigned a “prefix”. A common thing to do in SQL queries is to rename the tables in the query so that you can reference the same table multiple times or use different tables with the same column names. So, for example if I had a table called posts and I also had a table called users and they both shared a column called ‘last_update’, I could assign a prefix ‘p’ to the post columns and a prefix ‘u’ to the user columns so that the final column name would be ‘p.last_update’ and ‘u.last_update’ for posts and users respectively.

Another thing I wanted to do was avoid the usage of SQL in constructing my queries. This is similar to the way that LINQ works for C#: A predicate is specified and later translated into an SQL query or a series of operations in memory depending on what is going on. So, in Python one of my queries looks like so:

class Table(ColumnSet):
    some_column = StringColumn("column_1", "")
    another = IntColumn("column_2", 0)
a_variable = 5
columns = Table.get_columns('x') # columns with a prefix 'x'
query = DbQuery() # This base class just makes a where statement
query.where((columns.some_column == "4") &amp; (columns.another &gt; a_variable)
print query.sql

class Table(ColumnSet):

some_column = StringColumn("column_1", "")

another = IntColumn("column_2", 0)

a_variable = 5

columns = Table.get_columns('x') # columns with a prefix 'x'

query = DbQuery() # This base class just makes a where statement

query.where((columns.some_column == "4") & (columns.another > a_variable)

print query.sql

This would print out a tuple (" WHERE x.column_1 = %s AND x.column_2 > %s", ["4", 5]). So, how does this work? I used operator overloading to create DbQueryExpression objects. The code is like so:

class DbQueryExpression(object):
    """
    Query expression created from columns, literals, and operators
    """
    def __and__(self, other):
        return DbQueryConjunction(self, other)
    def __or__(self, other):
        return DbQueryDisjunction(self, other)

    def __str__(self):
        raise NotImplementedError
    @property
    def arguments(self):
        raise NotImplementedError

class DbQueryConjunction(DbQueryExpression):
    """
    Query expression joining together a left and right expression with an
    AND statement
    """
    def __init__(self, l, r):
        DbQueryExpression.__ini__(self)
        self.l = l
        self.r = r
    def __str__(self):
        return str(self.l) + " AND " + str(self.r)
    @property
    def arguments(self):
        return self.l.arguments + self.r.arguments

class DbQueryDisjunction(DbQueryExpression):
    """
    Query expression joining together a left and right expression with an
    OR statement
    """
    def __init__(self, l, r):
        DbQueryExpression.__init__(self)
        self.l = l
        self.r = r
    def __str__(self):
        return str(self.r) + " OR " + str(self.r)
    @property
    def arguments(self):
        return self.l.arguments + self.r.arguments

class DbQueryColumnComparison(DbQueryExpression):
    """
    Query expression comparing a combination of a column and/or a value
    """
    def __init__(self, l, op, r):
        DbQueryExpression.__init__(self)
        self.l = l
        self.op = op
        self.r = r
    def __str__(self):
        output = ""
        if isinstance(self.l, DbQueryColumn):
            prefix = self.l.prefix
            if prefix is not None:
                output += prefix + "."
            output += self.l.name
        elif self.l is None:
            output += "NULL"
        else:
            output += "%s"
        output += self.op
        if isinstance(self.r, DbQueryColumn):
            prefix = self.r.prefix
            if prefix is not None:
                output += prefix + "."
            output += self.r.name
        elif self.r is None:
            output += "NULL"
        else:
            output += "%s"
        return output
    @property
    def arguments(self):
        output = []
        if not isinstance(self.l, DbQueryColumn) and self.l is not None:
            output.append(self.l)
        if not isinstance(self.r, DbQueryColumn) and self.r is not None:
            output.append(self.r)
        return output

class DbQueryColumnSet(object):
    """
    Represents a set of columns attached to a specific DbOject type. This
    object dynamically builds itself based on a passed type. The columns
    attached to this set may be used in DbQueries
    """
    def __init__(self, dbo_type, prefix):
        d = dbo_type.__dict__
        self.__columns = {}
        for attr in d:
            obj = d[attr]
            if isinstance(obj, Column):
                column = DbQueryColumn(dbo_type, prefix, obj.name)
                setattr(self, attr, column)
                self.__columns[obj.name] = column
    def __len__(self):
        return len(self.__columns)
    def __getitem__(self, key):
        return self.__columns[key]
    def __iter__(self):
        return iter(self.__columns)

class DbQueryColumn(object):
    """
    Represents a Column object used in a DbQuery
    """
    def __init__(self, dbo_type, prefix, column_name):
        self.dbo_type = dbo_type
        self.name = column_name
        self.prefix = prefix

    def __lt__(self, other):
        return DbQueryColumnComparison(self, "&lt;", other)
    def __le__(self, other):
        return DbQueryColumnComparison(self, "&lt;=", other)
    def __eq__(self, other):
        op = "="
        if other is None:
           op = " IS "
       return DbQueryColumnComparison(self, op, other)
    def __ne__(self, other):
        op = "!="
        if other is None:
            op = " IS NOT "
        return DbQueryColumnComparison(self, op, other)
    def __gt__(self, other):
        return DbQueryColumnComparison(self, "&gt;", other)
    def __ge__(self, other):
        return DbQueryColumnComparison(self, "&gt;=", other)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

class DbQueryExpression(object):

"""

Query expression created from columns, literals, and operators

"""

def __and__(self, other):

return DbQueryConjunction(self, other)

def __or__(self, other):

return DbQueryDisjunction(self, other)

def __str__(self):

raise NotImplementedError

@property

def arguments(self):

raise NotImplementedError

class DbQueryConjunction(DbQueryExpression):

"""

Query expression joining together a left and right expression with an

AND statement

"""

def __init__(self, l, r):

DbQueryExpression.__ini__(self)

self.l = l

self.r = r

def __str__(self):

return str(self.l) + " AND " + str(self.r)

@property

def arguments(self):

return self.l.arguments + self.r.arguments

class DbQueryDisjunction(DbQueryExpression):

"""

Query expression joining together a left and right expression with an

OR statement

"""

def __init__(self, l, r):

DbQueryExpression.__init__(self)

self.l = l

self.r = r

def __str__(self):

return str(self.r) + " OR " + str(self.r)

@property

def arguments(self):

return self.l.arguments + self.r.arguments

class DbQueryColumnComparison(DbQueryExpression):

"""

Query expression comparing a combination of a column and/or a value

"""

def __init__(self, l, op, r):

DbQueryExpression.__init__(self)

self.l = l

self.op = op

self.r = r

def __str__(self):

output = ""

if isinstance(self.l, DbQueryColumn):

prefix = self.l.prefix

if prefix is not None:

output += prefix + "."

output += self.l.name

elif self.l is None:

output += "NULL"

else:

output += "%s"

output += self.op

if isinstance(self.r, DbQueryColumn):

prefix = self.r.prefix

if prefix is not None:

output += prefix + "."

output += self.r.name

elif self.r is None:

output += "NULL"

else:

output += "%s"

return output

@property

def arguments(self):

output = []

if not isinstance(self.l, DbQueryColumn) and self.l is not None:

output.append(self.l)

if not isinstance(self.r, DbQueryColumn) and self.r is not None:

output.append(self.r)

return output

class DbQueryColumnSet(object):

"""

Represents a set of columns attached to a specific DbOject type. This

object dynamically builds itself based on a passed type. The columns

attached to this set may be used in DbQueries

"""

def __init__(self, dbo_type, prefix):

d = dbo_type.__dict__

self.__columns = {}

for attr in d:

obj = d[attr]

if isinstance(obj, Column):

column = DbQueryColumn(dbo_type, prefix, obj.name)

setattr(self, attr, column)

self.__columns[obj.name] = column

def __len__(self):

return len(self.__columns)

def __getitem__(self, key):

return self.__columns[key]

def __iter__(self):

return iter(self.__columns)

class DbQueryColumn(object):

"""

Represents a Column object used in a DbQuery

"""

def __init__(self, dbo_type, prefix, column_name):

self.dbo_type = dbo_type

self.name = column_name

self.prefix = prefix

def __lt__(self, other):

return DbQueryColumnComparison(self, "<", other)

def __le__(self, other):

return DbQueryColumnComparison(self, "<=", other)

def __eq__(self, other):

op = "="

if other is None:

op = " IS "

return DbQueryColumnComparison(self, op, other)

def __ne__(self, other):

op = "!="

if other is None:

op = " IS NOT "

return DbQueryColumnComparison(self, op, other)

def __gt__(self, other):

return DbQueryColumnComparison(self, ">", other)

def __ge__(self, other):

return DbQueryColumnComparison(self, ">=", other)

The __str__ function and arguments property return recursively generated expressions using the column prefixes (in the case of __str__) and the arguments (in the case of arguments). As can be seen, this supports parameterization of queries. To be honest, this part was the most fun since I was surprised it was so easy to make predicate expressions using a minimum of classes. One thing that I didn’t like, however, was the fact that the boolean and/or operators cannot be overloaded. For that reason I had to use the bitwise operators, so the expressions aren’t entirely correct when being read.

This DbQueryExpression is fed into my DbQuery object which actually does the translation to SQL. In the example above, we saw that I just passed a logical argument into my where function. This actually was a DbQueryExpression since my overloaded operators create DbQueryExpression objects when they are compared. The DbColumnSet object is an dynamically generated object containing the go-between column objects which is created from a DbObject. We will discuss the DbObject a little further down

The DbQuery objects are implemented as follows:

class DbQueryError(Exception):
    """
    Raised when there is an error constructing a query
    """
    def __init__(self, msg):
        self.message = msg
    def __str__(self):
        return self.message

class DbQuery(object):
    """
    Represents a base SQL Query to a database based upon some DbObjects

    All of the methods implemented here are valid on select, update, and
    delete statements.
    """
    def __init__(self, execute_filter=None):
        """
        callback: Function to call when the DbQuery is executed
        """
        self.__where = []
        self.__limit = None
        self.__orderby = []
        self.__execute_filter = execute_filter
    def where(self, expression):
        """Specify an expression to append to the WHERE clause"""
        self.__where.append(expression)
    def limit(self, value=None):
        """Specify the limit to the query"""
        self.__limit = value
    @property
    def sql(self):
        query = ""
        args = []
        if len(self.__where) &gt; 0:
            where = self.__where[0]
            for clause in self.__where[1:]:
                where = where &amp; clause
            args = where.arguments
            query += " WHERE " + str(where)
        if self.__limit is not None:
            query += " LIMIT " + self.__limit
        return query,args
    def execute(self, cur):
        """
        Executes this query on the passed cursor and returns either the result
        of the filter function or the cursor if there is no filter function.
        """
        query = self.sql
        cur.execute(query[0], query[1])
        if self.__execute_filter:
            return self.__execute_filter(self, cur)
        else:
            return cur

class DbSelectQuery(DbQuery):
    """
    Creates a select query to a database based upon DbObjects
    """
    def __init__(self, execute_filter=None):
        DbQuery.__init__(self, execute_filter)
        self.__select = []
        self.__froms = []
        self.__joins = []
        self.__orderby = []
    def select(self, *columns):
        """Specify one or more columns to select"""
        self.__select += columns
    def from_table(self, dbo_type, prefix):
        """Specify a table to select from"""
        self.__froms.append((dbo_type, prefix))
    def join(self, dbo_type, prefix, on):
        """Specify a table to join to"""
        self.__joins.append((dbo_type, prefix, on))
    def orderby(self, *columns):
        """Specify one or more columns to order by"""
        self.__orderby += columns
    @property
    def sql(self):
        query = "SELECT "
        args = []
        if len(self.__select) == 0:
            raise DbQueryError("No selection in DbSelectQuery")
        query += ','.join([col.prefix + "." + 
                 col.name for col in self.__select])
        if len(self.__froms) == 0:
            raise DbQueryError("No FROM clause in DbSelectQuery")
        for table in self.__froms:
            query += " FROM " + table[0].dbo_tablename + " " + table[1]
        if len(self.__joins) &gt; 0:
            for join in self.__joins:
                query += " JOIN " + join[0].dbo_tablename + " " + join[1] + 
                         " ON " + str(join[2])
        query_parent = super(DbSelectQuery, self).sql
        query += query_parent[0]
        args += query_parent[1]
        if len(self.__orderby) &gt; 0:
           query += " ORDER BY " + 
                    ','.join([col.prefix + "." + 
                    col.name for col in self.__orderby])
        return query,args

class DbInsertQuery(DbQuery):
    """
    Creates an insert query to a database based upon DbObjects. This does not
    include any where or limit expressions
    """
    def __init__(self, dbo_type, prefix, execute_filter=None):
        DbQuery.__init__(self, execute_filter)
        self.table = (dbo_type, prefix)
        self.__values = []
    def value(self, column, value):
        self.__values.append((column, value))
    @property
    def sql(self):
        if len(self.__values) == 0:
            raise DbQueryError("No values in insert")
        tablename = self.table[0].dbo_tablename
        query = "INSERT INTO {table} (".format(table=tablename)
        args = [val[1] for val in self.__values 
                if val[0].prefix == self.table[1]]
        query += ",".join([val[0].name for val in self.__values 
                          if val[0].prefix == self.table[1]])
        query += ") VALUES ("
        query += ",".join(["%s" for x in args])
        query += ")"
        return query,args

class DbUpdateQuery(DbQuery):
    """
    Creates an update query to a database based upon DbObjects
    """
    def __init__(self, dbo_type, prefix, execute_filter=None):
        """
        Initialize the update query

        dbo_type: table type to be updating
        prefix: Prefix the columns are known under
        """
        DbQuery.__init__(self, execute_filter)
        self.table = (dbo_type, prefix)
        self.__updates = []
    def update(self, left, right):
        self.__updates.append((left, right))
    @property
    def sql(self):
        if len(self.__updates) == 0:
            raise DbQueryError("No update in DbUpdateQuery")
        query = "UPDATE " + self.table[0].dbo_tablename + " " + self.table[1]
        args = []
        query += " SET "
        for update in self.__updates:
            if isinstance(update[0], DbQueryColumn):
                query += update[0].prefix + "." + update[0].name
            else:
                query += "%s"
                args.append(update[0])
            query += "="
            if isinstance(update[1], DbQueryColumn):
                query += update[1].prefix + "." + update[1].name
            else:
                query += "%s"
                args.append(update[1])
        query_parent = super(DbUpdateQuery, self).sql
        query += query_parent[0]
        args += query_parent[1]
        return query, args

class DbDeleteQuery(DbQuery):
    """
    Creates a delete query for a database based on a DbObject
    """
    def __init__(self, dbo_type, prefix, execute_filter=None):
        DbQuery.__init__(self, execute_filter)
        self.table = (dbo_type, prefix)
    @property
    def sql(self):
        query = "DELETE FROM " + self.table[0].dbo_tablename + " " + 
                self.table[1]
        args = []
        query_parent = super(DbDeleteQuery, self).sql
        query += query_parent[0]
        args += query_parent[1]
        return query, args

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

class DbQueryError(Exception):

"""

Raised when there is an error constructing a query

"""

def __init__(self, msg):

self.message = msg

def __str__(self):

return self.message

class DbQuery(object):

"""

Represents a base SQL Query to a database based upon some DbObjects

All of the methods implemented here are valid on select, update, and

delete statements.

"""

def __init__(self, execute_filter=None):

"""

callback: Function to call when the DbQuery is executed

"""

self.__where = []

self.__limit = None

self.__orderby = []

self.__execute_filter = execute_filter

def where(self, expression):

"""Specify an expression to append to the WHERE clause"""

self.__where.append(expression)

def limit(self, value=None):

"""Specify the limit to the query"""

self.__limit = value

@property

def sql(self):

query = ""

args = []

if len(self.__where) > 0:

where = self.__where[0]

for clause in self.__where[1:]:

where = where & clause

args = where.arguments

query += " WHERE " + str(where)

if self.__limit is not None:

query += " LIMIT " + self.__limit

return query,args

def execute(self, cur):

"""

Executes this query on the passed cursor and returns either the result

of the filter function or the cursor if there is no filter function.

"""

query = self.sql

cur.execute(query[0], query[1])

if self.__execute_filter:

return self.__execute_filter(self, cur)

else:

return cur

class DbSelectQuery(DbQuery):

"""

Creates a select query to a database based upon DbObjects

"""

def __init__(self, execute_filter=None):

DbQuery.__init__(self, execute_filter)

self.__select = []

self.__froms = []

self.__joins = []

self.__orderby = []

def select(self, *columns):

"""Specify one or more columns to select"""

self.__select += columns

def from_table(self, dbo_type, prefix):

"""Specify a table to select from"""

self.__froms.append((dbo_type, prefix))

def join(self, dbo_type, prefix, on):

"""Specify a table to join to"""

self.__joins.append((dbo_type, prefix, on))

def orderby(self, *columns):

"""Specify one or more columns to order by"""

self.__orderby += columns

@property

def sql(self):

query = "SELECT "

args = []

if len(self.__select) == 0:

raise DbQueryError("No selection in DbSelectQuery")

query += ','.join([col.prefix + "." +

col.name for col in self.__select])

if len(self.__froms) == 0:

raise DbQueryError("No FROM clause in DbSelectQuery")

for table in self.__froms:

query += " FROM " + table[0].dbo_tablename + " " + table[1]

if len(self.__joins) > 0:

for join in self.__joins:

query += " JOIN " + join[0].dbo_tablename + " " + join[1] +

" ON " + str(join[2])

query_parent = super(DbSelectQuery, self).sql

query += query_parent[0]

args += query_parent[1]

if len(self.__orderby) > 0:

query += " ORDER BY " +

','.join([col.prefix + "." +

col.name for col in self.__orderby])

return query,args

class DbInsertQuery(DbQuery):

"""

Creates an insert query to a database based upon DbObjects. This does not

include any where or limit expressions

"""

def __init__(self, dbo_type, prefix, execute_filter=None):

DbQuery.__init__(self, execute_filter)

self.table = (dbo_type, prefix)

self.__values = []

def value(self, column, value):

self.__values.append((column, value))

@property

def sql(self):

if len(self.__values) == 0:

raise DbQueryError("No values in insert")

tablename = self.table[0].dbo_tablename

query = "INSERT INTO {table} (".format(table=tablename)

args = [val[1] for val in self.__values

if val[0].prefix == self.table[1]]

query += ",".join([val[0].name for val in self.__values

if val[0].prefix == self.table[1]])

query += ") VALUES ("

query += ",".join(["%s" for x in args])

query += ")"

return query,args

class DbUpdateQuery(DbQuery):

"""

Creates an update query to a database based upon DbObjects

"""

def __init__(self, dbo_type, prefix, execute_filter=None):

"""

Initialize the update query

dbo_type: table type to be updating

prefix: Prefix the columns are known under

"""

DbQuery.__init__(self, execute_filter)

self.table = (dbo_type, prefix)

self.__updates = []

def update(self, left, right):

self.__updates.append((left, right))

@property

def sql(self):

if len(self.__updates) == 0:

raise DbQueryError("No update in DbUpdateQuery")

query = "UPDATE " + self.table[0].dbo_tablename + " " + self.table[1]

args = []

query += " SET "

for update in self.__updates:

if isinstance(update[0], DbQueryColumn):

query += update[0].prefix + "." + update[0].name

else:

query += "%s"

args.append(update[0])

query += "="

if isinstance(update[1], DbQueryColumn):

query += update[1].prefix + "." + update[1].name

else:

query += "%s"

args.append(update[1])

query_parent = super(DbUpdateQuery, self).sql

query += query_parent[0]

args += query_parent[1]

return query, args

class DbDeleteQuery(DbQuery):

"""

Creates a delete query for a database based on a DbObject

"""

def __init__(self, dbo_type, prefix, execute_filter=None):

DbQuery.__init__(self, execute_filter)

self.table = (dbo_type, prefix)

@property

def sql(self):

query = "DELETE FROM " + self.table[0].dbo_tablename + " " +

self.table[1]

args = []

query_parent = super(DbDeleteQuery, self).sql

query += query_parent[0]

args += query_parent[1]

return query, args

Each of the SELECT, INSERT, UPDATE, and DELETE query types inherits from a base DbQuery which does execution and such. I decided to make the DbQuery object take a PEP 249-style cursor object and execute the query itself. My hope is that this will make this a little more portable since, to my knowledge, I didn’t make the queries have any MySQL-specific constructions.

The different query types each implement a variety of statements corresponding to different parts of an SQL query: where(), limit(), orderby(), select(), from_table(), etc. These each take in either a DbQueryColumn (such as is the case with where(), orderby(), select(), etc) or a string to be appended to the query, such as is the case with limit(). I could easily have made limit take in two integers as well, but I was kind of rushing through because I wanted to see if this would even work. The query is built by creating the query object for the basic query type that is desired and then calling its member functions to add things on to the query.

Executing the queries can cause a callback “filter” function to be called which takes in the query and the cursor as arguments. I use this function to create new objects from the data or to update an object. It could probably be used for more clever things as well, but those two cases were my original intent in creating it. If no filter is specified, then the cursor is returned.

Table and row objects

At the highest level of this hierarchy is the DbObject. The DbObject definition actually represents a table in the database with a name and a single primary key column. Each instance represents a row. DbObjects also implement the methods for selecting records of their type and also updating themselves when they are changed. They inherit change tracking from the ColumnSet and use DbQueries to accomplish their querying goals. The code is as follows:

class DbObject(ColumnSet):
    """
    A DbObject is a set of columns linked to a table in the database. This is
    synonomous to a row. The following class attributes must be set:

    dbo_tablename : string table name
    primary_key : Column for the primary key
    """
    def __init__(self, **cols):
        ColumnSet.__init__(self)
        for name in cols:
            c = self.get_column(name)
            c.update(cols[name])

    @classmethod
    def get_query_columns(self, prefix):
        return DbQueryColumnSet(self, prefix)

    @classmethod
    def select(self, prefix):
        """
        Returns a DbSelectQuery set up for this DbObject
        """
        columns = self.get_query_columns(prefix)
        def execute(query, cur):
            output = []
            block = cur.fetchmany()
            while len(block) &gt; 0:
                for row in block:
                    values = {}
                    i = 0
                    for name in columns:
                        values[name] = row[i]
                        i += 1
                    output.append(self(**values))
                block = cur.fetchmany()
            return output
        query = DbSelectQuery(execute)
        query.select(*[columns[name] for name in columns])
        query.from_table(self, prefix)
        return query, columns

    def get_primary_key_name(self):
        return type(self).__dict__['primary_key'].name

    def save(self, cur):
        """
        Saves any changes to this object to the database
        """
        if self.primary_key is None:
            # we need to be saved
            columns = self.get_query_columns('x')
            def execute(query, cur):
                self.get_column(self.get_primary_key_name()
                                ).update(cur.lastrowid)
                selection = []
                for name in columns:
                    if name == self.get_primary_key_name():
                        continue #we have no need to update the primary key
                    column_instance = self.get_column(name)
                    if not column_instance.column.mutable:
                        selection.append(columns[name])
                if len(selection) != 0:
                    # we get to select to get additional computed values
                    def execute2(query, cur):
                        row = cur.fetchone()
                        index = 0
                        for s in selection:
                            self.get_column(s.name).update(row[index])
                            index += 1
                        return True
                    query = DbSelectQuery(execute2)
                    query.select(*selection)
                    query.from_table(type(self), 'x')
                    query.where(columns[self.get_primary_key_name()] == 
                                self.get_column(self.get_primary_key_name()
                                                ).value)
                    return query.execute(cur)
                return True
            query = DbInsertQuery(type(self), 'x', execute)
            for name in columns:
                column_instance = self.get_column(name)
                if not column_instance.column.mutable:
                    continue
                query.value(columns[name], column_instance.value)
            print query.sql
            return query.execute(cur)
        else:
            # we have been modified
            modified = self.mutated
            if len(modified) == 0:
                return True
            columns = self.get_query_columns('x')
            def execute(query, cur):
                for mod in modified:
                    mod.update(mod.value)
                return True
            query = DbUpdateQuery(type(self), 'x', execute)
            for mod in modified:
                query.update(columns[mod.column.name], mod.value)
            query.where(columns[self.get_primary_key_name()] == self.primary_key)
            return query.execute(cur)

100

101

102

class DbObject(ColumnSet):

"""

A DbObject is a set of columns linked to a table in the database. This is

synonomous to a row. The following class attributes must be set:

dbo_tablename : string table name

primary_key : Column for the primary key

"""

def __init__(self, **cols):

ColumnSet.__init__(self)

for name in cols:

c = self.get_column(name)

c.update(cols[name])

@classmethod

def get_query_columns(self, prefix):

return DbQueryColumnSet(self, prefix)

@classmethod

def select(self, prefix):

"""

Returns a DbSelectQuery set up for this DbObject

"""

columns = self.get_query_columns(prefix)

def execute(query, cur):

output = []

block = cur.fetchmany()

while len(block) > 0:

for row in block:

values = {}

i = 0

for name in columns:

values[name] = row[i]

i += 1

output.append(self(**values))

block = cur.fetchmany()

return output

query = DbSelectQuery(execute)

query.select(*[columns[name] for name in columns])

query.from_table(self, prefix)

return query, columns

def get_primary_key_name(self):

return type(self).__dict__['primary_key'].name

def save(self, cur):

"""

Saves any changes to this object to the database

"""

if self.primary_key is None:

# we need to be saved

columns = self.get_query_columns('x')

def execute(query, cur):

self.get_column(self.get_primary_key_name()

).update(cur.lastrowid)

selection = []

for name in columns:

if name == self.get_primary_key_name():

continue #we have no need to update the primary key

column_instance = self.get_column(name)

if not column_instance.column.mutable:

selection.append(columns[name])

if len(selection) != 0:

# we get to select to get additional computed values

def execute2(query, cur):

row = cur.fetchone()

index = 0

for s in selection:

self.get_column(s.name).update(row[index])

index += 1

return True

query = DbSelectQuery(execute2)

query.select(*selection)

query.from_table(type(self), 'x')

query.where(columns[self.get_primary_key_name()] ==

self.get_column(self.get_primary_key_name()

).value)

return query.execute(cur)

return True

query = DbInsertQuery(type(self), 'x', execute)

for name in columns:

column_instance = self.get_column(name)

if not column_instance.column.mutable:

continue

query.value(columns[name], column_instance.value)

print query.sql

return query.execute(cur)

else:

# we have been modified

modified = self.mutated

if len(modified) == 0:

return True

columns = self.get_query_columns('x')

def execute(query, cur):

for mod in modified:

mod.update(mod.value)

return True

query = DbUpdateQuery(type(self), 'x', execute)

for mod in modified:

query.update(columns[mod.column.name], mod.value)

query.where(columns[self.get_primary_key_name()] == self.primary_key)

return query.execute(cur)

DbObjects require that the inheriting classes define two properties: dbo_tablename and primary_key. dbo_tablename is just a string giving the name of the table in the database and primary_key is a Column that will be used as the primary key.

To select records from the database, the select() function can be called from the class. This sets up a DbSelectQuery which will return an array of the DbObject that it is called for when the query is executed.

One fallacy of this structure is that at the moment it assumes that the primary key won’t be None if it has been set. In other words, the way I did it right now does not allow for null primary keys. The reason it does this is because it says that if the primary key hasn’t been set, it needs to generate a DbInsertQuery for the object when save() is called instead of a DbUpdateQuery. Both insert and update queries do not include every field. Immutable fields are always excluded and then later selected or inferred from the cursor object.

256Mb doesn’t do what it used to…

So, this last week I got an email from rackspace saying that my server was thrashing the hard drives and lowering performance of everyone else on that same machine. In consequence, they had rebooted my server for me.

I made a few mistakes in the setup of this first iteration of my server: I didn’t restart it after kernel updates, I ran folding@home on it while running nodejs, and I didn’t have backups turned on. I had it running for well over 200 days without a reboot while there had been a dozen or so kernel updates. When they hard rebooted my server, it wouldn’t respond at all to pings, ssh, or otherwise. In fact, it behaved like the firewalls were shut (hanging on the “waiting” step rather than saying “connection refused”). I ended up having to go into their handy rescue mode and copy out all the files. I only copied my www directory and the mysql binary table files, but as you can see, I was able to restore the server from those.

This gave me an excellent opportunity to actually set up my server correctly. I no longer have to be root to edit my website files (yay!), I have virtual hosts set up in a fashion that makes sense and actually works, and overall performance seems to be improved. From now on, I will be doing the updates less frequently and when I do I will be rebooting the machine. That should fix the problem with breaking everything if a hard reboot happens.

I do pay for the hosting for this, 1.5 cents per hour per 256Mb of RAM with extra for bandwidth. I only have 256Mb and since I don’t make any profit off this server whatsoever at the moment, I plan on keeping it that way for now. Considering that back in the day, 256Mb was a ton of memory, it clearly no longer suffices for running too much on my server (httpd + mysql + nodejs + folding@home = crash and burn).

Multiprocessing with the WebSocketServer

After spending way to much time thinking about exactly how to do it, I have got multiprocessing working with the WebSocketServer.

I have learned some interesting things about multiprocessing with Python:

Basically no complicated objects can be sent over queues.
Since pickle is used to send the objects, the object must also be serializable
Methods (i.e. callbacks) cannot be sent either
Processes can’t have new queues added after the initial creation since they aren’t picklable, so when the process is created, it has to be given its queues and that’s all the queues its ever going to get

So the new model for operation is as follows:

WebSocketServer Diagram

Sadly, because of the lack of ability to share methods between processes, there is a lot of polling going on here. A lot. The service has to poll its queues to see if any clients or packets have come in, the server has to poll all the client socket queues to see if anything came in from them and all the service queues to see if the services want to send anything to the clients, etc. I guess it was a sacrifice that had to be made to be able to use multiprocessing. The advantage gained now is that services have access to their own interpreter and so they aren’t all forced to share the same CPU. I would imagine this would improve performance with more intensive servers, but the bottleneck will still be with the actual sending and receiving to the clients since every client on the server still has to share the same thread.

So now, the plan to proceed is as follows:

Figure out a way to do a pre-forked server so that client polling can be done by more than one process
Extend the built in classes a bit to simplify service creation and to make it a bit more intuitive.

As always, the source is available at https://github.com/kcuzner/python-websocket-server

Making a WebSocket server

For the past few weeks I have been experimenting a bit with HTML5 WebSockets. I don’t normally focus only on software when building something, but this has been an interesting project and has allowed me to learn a lot more about the nitty gritty of sockets and such. I have created a github repository for it (it’s my first time using git and I’m loving it) which is here: https://github.com/kcuzner/python-websocket-server

The server I have runs on a port which is considered a dedicated port for WebSocket-based services. The server is written in python and defines a few base classes for implementing a service. The basic structure is as follows:

Super-basic flowchart

Each service has its own thread and inherits from a base class which is a thread plus a queue for accepting new clients. The clients are a socket object returned by socket.accept which are wrapped in a class that allows for communication to the socket via queues. The actual communication to sockets is managed by a separate thread that handles all the encoding and decoding to websocket frames. Since adding a client doesn’t produce much overhead, this structure potentially could be expanded very easily to handle many many clients.

A few things I plan on adding to the server eventually are:

Using processes instead of threads for the services. Due to the global interpreter lock, if this is run using CPython (which is what most people use as far as I know and also what comes installed by default on many systems) all the threads will be locked to use the same CPU since the python interpreter can’t be used by more than one thread at once (however, it can for processes). The difficult part of all this is that it is hard to pass actual objects between processes and I have to do some serious re-structuring for the code to work without needing to pass objects (such as sockets) to services.
Creating a better structure for web services (currently only two base classes are really available) including a generalized database binding that is thread safe so that a service could potentially split into many threads while not overwhelming the database connection.

Currently, the repository includes a demo chatroom service for which I should have the client side application done soon and uploaded. Currently it supports multiple chatrooms and multiple users, but there is no authentication really and there are a few features I would like to add (such as being able to see who is in the chatroom).

Not so temporary…

Well I have decided to place my actual website on this server. My home server has failed due to router issues and I will probably be redesigning the entire website in the near future anyway since it uses outdated layout techniques. This server is basically the smith-marsden.org website that I work on for my dad’s side of the family. I plan on downloading the contents of my server to here over the weekend so that I can try to clean things up a bit and hopefully get this site back on the map.

I will probably post progress on the new smith-marsden site here since that is currently my main development challenge.

Additional features of the new smith-marsden site:

Ability to invite other users given to everyone
RSS feeds on letter submissions
More information stored so that it is all in one place that is private rather than facebook or somethwere else which could be quite public
New state of the art layout techniques enhance the website
AJAX allows pages to submit forms without reloading, making page load times faster and looks really cool.
The entire thing is made using CakePHP, a robust framework.
More to come as I think of them and make them…

Projects & Libraries

Musings and Projects from Kevin Cuzner

Category Archives: Websites

Extreme Attributed Metadata with Autofac

Introduction

What is Autofac?

Attributed Metadata: The Basics

The IMetadataProvider Interface

IMetadataProvider: Making a set of objects

IMetadataProvider: Hierarchical Metadata

Conclusion

Database Abstraction in Python

Example

Change Tracking Columns

Generation of SQL using logical predicates

Table and row objects

256Mb doesn’t do what it used to…

Multiprocessing with the WebSocketServer

Making a WebSocket server

Not so temporary…