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 type for all string and character types, such as `Unicode`, `TEXT`, `CLOB`, etc.  By default it generates a VARCHAR in DDL.  It includes an argument `length`, which indicates the length in characters of the type, as well as `convert_unicode` and `assert_unicode`, which are booleans.  `length` will be used as the length argument when generating DDL.  If `length` is omitted, the `String` type resolves into the `TEXT` type.

`convert_unicode=True` indicates that incoming strings, if they are Python `unicode` strings, will be encoded into a raw bytestring using the `encoding` attribute of the dialect (defaults to `utf-8`).  Similarly, raw bytestrings coming back from the database will be decoded into `unicode` objects on the way back.

`assert_unicode` is set to `None` by default. When `True`, it indicates that incoming bind parameters will be checked that they are in fact  `unicode` objects, else an error is raised.  Setting it to `None` indicates that the dialect-level `convert_unicode` setting should take place, whereas setting it to `False` disables it unconditionally  (this flag is new as of version 0.4.2).

Both `convert_unicode` and `assert_unicode` may be set at the engine level as flags to `create_engine()`.

#### Unicode

The `Unicode` type is shorthand for `String` with `convert_unicode=True` and `assert_unicode=True`.  When writing a unicode-aware appication, it is strongly recommended that this type is used, and that only unicode strings are used in the application.  By "unicode string" we mean a string with a u, i.e. `u'hello'`.  Otherwise, particularly when using the ORM, data will be converted to unicode when it returns from the database, but local data which was generated locally will not be in unicode format, which can create confusion.

#### Numeric

TODO

#### Float

TODO

#### Datetime/Date/Time

TODO

#### Interval

TODO

#### Binary

TODO

#### Boolean

TODO

#### PickleType

TODO

#### 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)

### 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 Postgres 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 postgres types:

    {python}
    from sqlalchemy.databases.postgres import PGInet, PGArray
    
    table = Table('foo', meta,
        Column('ipaddress', PGInet),
        Column('elements', PGArray(str))   # PGArray is available in 0.4, and takes a type argument
        )


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

User-defined types can be created, to support either database-specific types, or customized pre-processing of query parameters as well as post-processing of result set data.  You can make your own classes to perform these operations.  To augment the behavior of a `TypeEngine` type, such as `String`, the `TypeDecorator` class is used:

    {python}
    import sqlalchemy.types as types

    class MyType(types.TypeDecorator):
        """basic type that decorates String, prefixes values with "PREFIX:" on 
        the way in and strips it off on the way out."""
        impl = types.String
        def convert_bind_param(self, value, engine):
            return "PREFIX:" + value
        def convert_result_value(self, value, engine):
            return value[7:]
            
The `PickleType` class is an instance of `TypeDecorator` already and can be subclassed directly.

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 convert_bind_param(self, value, engine):
            return value
        def convert_result_value(self, value, engine):
            return value

Once you make your type, its immediately useable:

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