The Types System {@name=types}
================

The package `sqlalchemy.types` defines the datatype identifiers which may be used when defining [metadata](rel:table metadata).  This package includes a set of generic types, a set of SQL-specific subclasses of those types, and a small extension system used by specific database connectors to adapt these generic types into database-specific type objects.

### Built-in Types {@name=standard}

SQLAlchemy comes with a set of standard generic datatypes, which are defined as classes.  Types are usually used when defining tables, and can be left as a class or instantiated, for example:

    {python}
    mytable = Table('mytable', metadata,
        Column('myid', Integer, primary_key=True),
        Column('data', String(30)),
        Column('info', Unicode(100)),
        Column('value', Number(7,4)) 
        )

Following is a rundown of the standard types.

#### String

This type is the base class for all string and character types.  `String` includes a `length` parameter, which will be used as the "length" when generating DDL for types such as `CHAR` and `VARCHAR`.  The base `String` type will usually generate the DDL of `VARCHAR`.  `length` has no other usage and can be omitted if DDL is not being generated.

#### Unicode

The `Unicode` type is a `String` which converts Python unicode objects (i.e., strings that are defined as `u'somevalue'`) into encoded bytestrings when passing the value to the database, and similarly decodes values from the database back into Python unicode objects.  The encoding used is configured on the dialect using the `encoding` parameter, which defaults to utf-8.

When using the `Unicode` type, it is only appropriate to pass Python unicode objects, and not plain strings.   If a bytestring is passed, a warning is issued.  If you notice your application raising these warnings but you're not sure where, the Python `warnings` filter can be used to turn these warnings into exceptions which will illustrate a stack trace:

    {python}
    import warnings
    warnings.simplefilter('error')

Any `String` type or subtype can be turned into a `Unicode` type by passing the flags `convert_unicode=True, assert_unicode='warn'` to the constructor.  The `Unicode` type itself is shorthand for this notation.   The `create_engine()` call also accepts these flags which when passed will establish their settings as the default setting for all `String` types.

#### Text / UnicodeText

The `Text` and `UnicodeText` types are the same as `String` and `Unicode` except they do not take a length parameter.  They differ only in that they generate DDL of `TEXT` or `CLOB` instead of `VARCHAR`.

#### Numeric

Numeric types return `decimal.Decimal` objects by default.  The flag `asdecimal=False` may be specified which causes data to be passed straight from the DBAPI's preferred return type, which may be either `Decimal` or `float`.   Numeric also takes "precision" and "scale" arguments which are used when generating DDL.

#### Float

Float types return Python floats.  Float also takes a "precision" argument which is used when generating DDL.

#### DateTime/Date/Time

Date and time types return objects from the Python `datetime` module.  Most DBAPIs have built in support for the datetime module, with the noted exception of SQLite.  In the case of SQLite, date and time types are stored as strings which are then converted back to datetime objects when rows are returned.

#### Interval

The Interval type deals with `datetime.timedelta` objects.  In PostgreSQL, the native INTERVAL type is used; for others, the value is stored as a date which is relative to the "epoch" (Jan. 1, 1970).

#### Binary

The Binary type generates BLOB or BYTEA when tables are created, and also converts incoming values using the `Binary` callable provided by each DBAPI.  

#### Boolean

Boolean typically uses BOOLEAN or SMALLINT on the DDL side, and on the Python side deals in `True` or `False`.

#### PickleType

PickleType builds upon the Binary type to apply Python's `pickle.dumps()` to incoming objects, and `pickle.loads()` on the way out, allowing any pickleable Python object to be stored as a serialized binary field.

#### SQL-Specific Types {@name=sqlspecific}

These are subclasses of the generic types and include:

    {python}
    class FLOAT(Numeric)
    class TEXT(String)
    class DECIMAL(Numeric)
    class INT(Integer)
    INTEGER = INT
    class TIMESTAMP(DateTime)
    class DATETIME(DateTime)
    class CLOB(String)
    class VARCHAR(String)
    class CHAR(String)
    class BLOB(Binary)
    class BOOLEAN(Boolean)

The idea behind the SQL-specific types is that DDL (i.e. during a CREATE TABLE statement) would generate the exact type specified in all cases.   This also implies that some of these types may not be supported by all dialects.

### Dialect Specific Types {@name=dialect}

Each dialect has its own set of types, many of which are available only within that dialect.  For example, MySQL has a `BigInteger` type and PostgreSQL has an `Inet` type.  To use these, import them from the module explicitly:

    {python}
    from sqlalchemy.databases.mysql import MSEnum, MSBigInteger
    
    table = Table('foo', meta,
        Column('enumerates', MSEnum('a', 'b', 'c')),
        Column('id', MSBigInteger)
    )
        
Or some PostgreSQL types:

    {python}
    from sqlalchemy.databases.postgres import PGInet, PGArray
    
    table = Table('foo', meta,
        Column('ipaddress', PGInet),
        Column('elements', PGArray(str))
        )


### Creating your Own Types {@name=custom}

User-defined types can be created which can augment the bind parameter and result processing capabilities of the built in types.  This is usually achieved using the `TypeDecorator` class, which "decorates" the behavior of any existing type.  

    {python}
    import sqlalchemy.types as types

    class MyType(types.TypeDecorator):
        """a type that decorates Unicode, prefixes values with "PREFIX:" on 
        the way in and strips it off on the way out."""
        
        impl = types.Unicode
        
        def process_bind_param(self, value, dialect):
            return "PREFIX:" + value
            
        def process_result_value(self, value, dialect):
            return value[7:]
        
        def copy(self):
            return MyType(self.impl.length)

The reason that type behavior is modified using class decoration instead of subclassing is due to the way dialect specific types are used.  Such as with the example above, when using the mysql dialect, the actual type in use will be a `sqlalchemy.databases.mysql.MSString` instance.  `TypeDecorator` handles the mechanics of passing the values between user-defined `process_` methods and the current dialect-specific type in use.

To build a type object from scratch, which will not have a corresponding database-specific implementation, subclass `TypeEngine`:

    {python}
    import sqlalchemy.types as types

    class MyType(types.TypeEngine):
        def __init__(self, precision = 8):
            self.precision = precision

        def get_col_spec(self):
            return "MYTYPE(%s)" % self.precision

        def bind_processor(self, dialect):
            def process(value):
                return value
            return process

        def result_processor(self, dialect):
            def process(value):
                return value
            return process

The `bind_processor` and `result_processor` methods return a callable which will be used to process data at the bind parameter and result row level.  If processing is not necessary, the method should return `None`.

Once you make your type, it's immediately useable:

    {python}
    table = Table('foo', meta,
        Column('id', Integer, primary_key=True),
        Column('data', MyType(16))
        )
        
        
