from __future__ import unicode_literals, division, print_function, absolute_import

import re
import sys
from io import StringIO

import pytest
import sqlalchemy
from sqlalchemy.dialects import mysql
from sqlalchemy.dialects import postgresql
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import (
 MetaData, Table, Column, CheckConstraint, UniqueConstraint, Index, ForeignKey,
 ForeignKeyConstraint)
from sqlalchemy.sql.expression import text
from sqlalchemy.types import INTEGER, SMALLINT, VARCHAR, NUMERIC

from codegen.modelcodegen_bak.codegen import CodeGenerator

# SQLAlchemy 1.3.11+
try:
 from sqlalchemy import Computed
except ImportError:
 Computed = None


if sys.version_info < (3,):
 unicode_re = re.compile(r"u(['\"])(.*?)(?<!\\)1円")

 def remove_unicode_prefixes(text):
 return unicode_re.sub(r"1円2円1円", text)
else:
 def remove_unicode_prefixes(text):
 return text


@pytest.fixture
def metadata(request):
 dialect = getattr(request, 'param', None)
 if dialect == 'postgresql':
 engine = create_engine('postgresql:///testdb')
 elif dialect == 'mysql':
 engine = create_engine('mysql+mysqlconnector://testdb')
 else:
 engine = create_engine('sqlite:///:memory:')

 return MetaData(engine)


def generate_code(metadata, **kwargs):
 codegen = CodeGenerator(metadata, **kwargs)
 sio = StringIO()
 codegen.render(sio)
 return remove_unicode_prefixes(sio.getvalue())


@pytest.mark.parametrize('metadata', ['postgresql'], indirect=['metadata'])
def test_fancy_coltypes(metadata):
 Table(
 'simple_items', metadata,
 Column('enum', postgresql.ENUM('A', 'B', name='blah')),
 Column('bool', postgresql.BOOLEAN),
 Column('number', NUMERIC(10, asdecimal=False)),
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, Enum, MetaData, Numeric, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('enum', Enum('A', 'B', name='blah')),
 Column('bool', Boolean),
 Column('number', Numeric(10, asdecimal=False))
)
"""


def test_boolean_detection(metadata):
 Table(
 'simple_items', metadata,
 Column('bool1', INTEGER),
 Column('bool2', SMALLINT),
 Column('bool3', mysql.TINYINT),
 CheckConstraint('simple_items.bool1 IN (0, 1)'),
 CheckConstraint('simple_items.bool2 IN (0, 1)'),
 CheckConstraint('simple_items.bool3 IN (0, 1)')
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('bool1', Boolean),
 Column('bool2', Boolean),
 Column('bool3', Boolean)
)
"""


@pytest.mark.parametrize('metadata', ['postgresql'], indirect=['metadata'])
def test_arrays(metadata):
 Table(
 'simple_items', metadata,
 Column('dp_array', postgresql.ARRAY(postgresql.DOUBLE_PRECISION(precision=53))),
 Column('int_array', postgresql.ARRAY(INTEGER))
 )

 if sqlalchemy.__version__ < '1.1':
 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Float, Integer, MetaData, Table
from sqlalchemy.dialects.postgresql import ARRAY

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('dp_array', ARRAY(Float(precision=53))),
 Column('int_array', ARRAY(Integer()))
)
"""
 else:
 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import ARRAY, Column, Float, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('dp_array', ARRAY(Float(precision=53))),
 Column('int_array', ARRAY(Integer()))
)
"""


def test_enum_detection(metadata):
 Table(
 'simple_items', metadata,
 Column('enum', VARCHAR(255)),
 CheckConstraint(r"simple_items.enum IN ('A', '\'B', 'C')")
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Enum, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('enum', Enum('A', "\\\\'B", 'C'))
)
"""


@pytest.mark.parametrize('metadata', ['postgresql'], indirect=['metadata'])
def test_column_adaptation(metadata):
 Table(
 'simple_items', metadata,
 Column('id', postgresql.BIGINT),
 Column('length', postgresql.DOUBLE_PRECISION)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import BigInteger, Column, Float, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('id', BigInteger),
 Column('length', Float)
)
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_column_types(metadata):
 Table(
 'simple_items', metadata,
 Column('id', mysql.INTEGER),
 Column('name', mysql.VARCHAR(255)),
 Column('set', mysql.SET('one', 'two'))
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, String, Table
from sqlalchemy.dialects.mysql import SET

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('id', Integer),
 Column('name', String(255)),
 Column('set', SET('one', 'two'))
)
"""


def test_constraints_table(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER),
 Column('number', INTEGER),
 CheckConstraint('number > 0'),
 UniqueConstraint('id', 'number')
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table, UniqueConstraint

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('id', Integer),
 Column('number', Integer),
 CheckConstraint('number > 0'),
 UniqueConstraint('id', 'number')
)
"""


def test_constraints_class(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('number', INTEGER),
 CheckConstraint('number > 0'),
 UniqueConstraint('id', 'number')
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'
 __table_args__ = (
 CheckConstraint('number > 0'),
 UniqueConstraint('id', 'number')
 )

 id = Column(Integer, primary_key=True)
 number = Column(Integer)
"""


def test_noindexes_table(metadata):
 simple_items = Table(
 'simple_items', metadata,
 Column('number', INTEGER),
 CheckConstraint('number > 2')
 )
 simple_items.indexes.add(Index('idx_number', simple_items.c.number))

 assert generate_code(metadata, noindexes=True) == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('number', Integer),
 CheckConstraint('number > 2')
)
"""


def test_noconstraints_table(metadata):
 simple_items = Table(
 'simple_items', metadata,
 Column('number', INTEGER),
 CheckConstraint('number > 2')
 )
 simple_items.indexes.add(Index('idx_number', simple_items.c.number))

 assert generate_code(metadata, noconstraints=True) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('number', Integer, index=True)
)
"""


def test_indexes_table(metadata):
 simple_items = Table(
 'simple_items', metadata,
 Column('id', INTEGER),
 Column('number', INTEGER),
 Column('text', VARCHAR)
 )
 simple_items.indexes.add(Index('idx_number', simple_items.c.number))
 simple_items.indexes.add(Index('idx_text_number', simple_items.c.text,
 simple_items.c.number, unique=True))
 simple_items.indexes.add(Index('idx_text', simple_items.c.text, unique=True))

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Index, Integer, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('id', Integer),
 Column('number', Integer, index=True),
 Column('text', String, unique=True),
 Index('idx_text_number', 'text', 'number', unique=True)
)
"""


def test_indexes_class(metadata):
 simple_items = Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('number', INTEGER),
 Column('text', VARCHAR)
 )
 simple_items.indexes.add(Index('idx_number', simple_items.c.number))
 simple_items.indexes.add(Index('idx_text_number', simple_items.c.text,
 simple_items.c.number))
 simple_items.indexes.add(Index('idx_text', simple_items.c.text, unique=True))

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Index, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'
 __table_args__ = (
 Index('idx_text_number', 'text', 'number'),
 )

 id = Column(Integer, primary_key=True)
 number = Column(Integer, index=True)
 text = Column(String, unique=True)
"""


def test_onetomany(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('container_id', INTEGER),
 ForeignKeyConstraint(['container_id'], ['simple_containers.id']),
 )
 Table(
 'simple_containers', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
 __tablename__ = 'simple_containers'

 id = Column(Integer, primary_key=True)


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 container_id = Column(ForeignKey('simple_containers.id'))

 container = relationship('SimpleContainer')
"""


def test_onetomany_selfref(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('parent_item_id', INTEGER),
 ForeignKeyConstraint(['parent_item_id'], ['simple_items.id'])
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 parent_item_id = Column(ForeignKey('simple_items.id'))

 parent_item = relationship('SimpleItem', remote_side=[id])
"""


def test_onetomany_selfref_multi(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('parent_item_id', INTEGER),
 Column('top_item_id', INTEGER),
 ForeignKeyConstraint(['parent_item_id'], ['simple_items.id']),
 ForeignKeyConstraint(['top_item_id'], ['simple_items.id'])
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 parent_item_id = Column(ForeignKey('simple_items.id'))
 top_item_id = Column(ForeignKey('simple_items.id'))

 parent_item = relationship('SimpleItem', remote_side=[id], \
primaryjoin='SimpleItem.parent_item_id == SimpleItem.id')
 top_item = relationship('SimpleItem', remote_side=[id], \
primaryjoin='SimpleItem.top_item_id == SimpleItem.id')
"""


def test_onetomany_composite(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('container_id1', INTEGER),
 Column('container_id2', INTEGER),
 ForeignKeyConstraint(['container_id1', 'container_id2'],
 ['simple_containers.id1', 'simple_containers.id2'],
 ondelete='CASCADE', onupdate='CASCADE')
 )
 Table(
 'simple_containers', metadata,
 Column('id1', INTEGER, primary_key=True),
 Column('id2', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKeyConstraint, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
 __tablename__ = 'simple_containers'

 id1 = Column(Integer, primary_key=True, nullable=False)
 id2 = Column(Integer, primary_key=True, nullable=False)


class SimpleItem(Base):
 __tablename__ = 'simple_items'
 __table_args__ = (
 ForeignKeyConstraint(['container_id1', 'container_id2'], ['simple_containers.id1', \
'simple_containers.id2'], ondelete='CASCADE', onupdate='CASCADE'),
 )

 id = Column(Integer, primary_key=True)
 container_id1 = Column(Integer)
 container_id2 = Column(Integer)

 simple_container = relationship('SimpleContainer')
"""


def test_onetomany_multiref(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('parent_container_id', INTEGER),
 Column('top_container_id', INTEGER),
 ForeignKeyConstraint(['parent_container_id'], ['simple_containers.id']),
 ForeignKeyConstraint(['top_container_id'], ['simple_containers.id'])
 )
 Table(
 'simple_containers', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
 __tablename__ = 'simple_containers'

 id = Column(Integer, primary_key=True)


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 parent_container_id = Column(ForeignKey('simple_containers.id'))
 top_container_id = Column(ForeignKey('simple_containers.id'))

 parent_container = relationship('SimpleContainer', \
primaryjoin='SimpleItem.parent_container_id == SimpleContainer.id')
 top_container = relationship('SimpleContainer', \
primaryjoin='SimpleItem.top_container_id == SimpleContainer.id')
"""


def test_onetoone(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('other_item_id', INTEGER),
 ForeignKeyConstraint(['other_item_id'], ['other_items.id']),
 UniqueConstraint('other_item_id')
 )
 Table(
 'other_items', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class OtherItem(Base):
 __tablename__ = 'other_items'

 id = Column(Integer, primary_key=True)


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 other_item_id = Column(ForeignKey('other_items.id'), unique=True)

 other_item = relationship('OtherItem', uselist=False)
"""


def test_onetomany_noinflect(metadata):
 Table(
 'oglkrogk', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('fehwiuhfiwID', INTEGER),
 ForeignKeyConstraint(['fehwiuhfiwID'], ['fehwiuhfiw.id']),
 )
 Table(
 'fehwiuhfiw', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Fehwiuhfiw(Base):
 __tablename__ = 'fehwiuhfiw'

 id = Column(Integer, primary_key=True)


class Oglkrogk(Base):
 __tablename__ = 'oglkrogk'

 id = Column(Integer, primary_key=True)
 fehwiuhfiwID = Column(ForeignKey('fehwiuhfiw.id'))

 fehwiuhfiw = relationship('Fehwiuhfiw')
"""


@pytest.mark.skipif(sqlalchemy.__version__ < '1.0', reason='SQLA < 1.0 gives inconsistent results')
def test_manytomany(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True)
 )
 Table(
 'simple_containers', metadata,
 Column('id', INTEGER, primary_key=True)
 )
 Table(
 'container_items', metadata,
 Column('item_id', INTEGER),
 Column('container_id', INTEGER),
 ForeignKeyConstraint(['item_id'], ['simple_items.id']),
 ForeignKeyConstraint(['container_id'], ['simple_containers.id'])
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
 __tablename__ = 'simple_containers'

 id = Column(Integer, primary_key=True)

 items = relationship('SimpleItem', secondary='container_items')


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)


t_container_items = Table(
 'container_items', metadata,
 Column('item_id', ForeignKey('simple_items.id')),
 Column('container_id', ForeignKey('simple_containers.id'))
)
"""


def test_manytomany_selfref(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True)
 )
 Table(
 'child_items', metadata,
 Column('parent_id', INTEGER),
 Column('child_id', INTEGER),
 ForeignKeyConstraint(['parent_id'], ['simple_items.id']),
 ForeignKeyConstraint(['child_id'], ['simple_items.id']),
 schema='otherschema'
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)

 parents = relationship(
 'SimpleItem',
 secondary='otherschema.child_items',
 primaryjoin='SimpleItem.id == child_items.c.child_id',
 secondaryjoin='SimpleItem.id == child_items.c.parent_id'
 )


t_child_items = Table(
 'child_items', metadata,
 Column('parent_id', ForeignKey('simple_items.id')),
 Column('child_id', ForeignKey('simple_items.id')),
 schema='otherschema'
)
"""


@pytest.mark.skipif(sqlalchemy.__version__ < '1.0', reason='SQLA < 1.0 gives inconsistent results')
def test_manytomany_composite(metadata):
 Table(
 'simple_items', metadata,
 Column('id1', INTEGER, primary_key=True),
 Column('id2', INTEGER, primary_key=True)
 )
 Table(
 'simple_containers', metadata,
 Column('id1', INTEGER, primary_key=True),
 Column('id2', INTEGER, primary_key=True)
 )
 Table(
 'container_items', metadata,
 Column('item_id1', INTEGER),
 Column('item_id2', INTEGER),
 Column('container_id1', INTEGER),
 Column('container_id2', INTEGER),
 ForeignKeyConstraint(['item_id1', 'item_id2'],
 ['simple_items.id1', 'simple_items.id2']),
 ForeignKeyConstraint(['container_id1', 'container_id2'],
 ['simple_containers.id1', 'simple_containers.id2'])
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKeyConstraint, Integer, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
 __tablename__ = 'simple_containers'

 id1 = Column(Integer, primary_key=True, nullable=False)
 id2 = Column(Integer, primary_key=True, nullable=False)

 simple_items = relationship('SimpleItem', secondary='container_items')


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id1 = Column(Integer, primary_key=True, nullable=False)
 id2 = Column(Integer, primary_key=True, nullable=False)


t_container_items = Table(
 'container_items', metadata,
 Column('item_id1', Integer),
 Column('item_id2', Integer),
 Column('container_id1', Integer),
 Column('container_id2', Integer),
 ForeignKeyConstraint(['container_id1', 'container_id2'], \
['simple_containers.id1', 'simple_containers.id2']),
 ForeignKeyConstraint(['item_id1', 'item_id2'], ['simple_items.id1', 'simple_items.id2'])
)
"""


def test_joined_inheritance(metadata):
 Table(
 'simple_sub_items', metadata,
 Column('simple_items_id', INTEGER, primary_key=True),
 Column('data3', INTEGER),
 ForeignKeyConstraint(['simple_items_id'], ['simple_items.super_item_id'])
 )
 Table(
 'simple_super_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('data1', INTEGER)
 )
 Table(
 'simple_items', metadata,
 Column('super_item_id', INTEGER, primary_key=True),
 Column('data2', INTEGER),
 ForeignKeyConstraint(['super_item_id'], ['simple_super_items.id'])
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleSuperItem(Base):
 __tablename__ = 'simple_super_items'

 id = Column(Integer, primary_key=True)
 data1 = Column(Integer)


class SimpleItem(SimpleSuperItem):
 __tablename__ = 'simple_items'

 super_item_id = Column(ForeignKey('simple_super_items.id'), primary_key=True)
 data2 = Column(Integer)


class SimpleSubItem(SimpleItem):
 __tablename__ = 'simple_sub_items'

 simple_items_id = Column(ForeignKey('simple_items.super_item_id'), primary_key=True)
 data3 = Column(Integer)
"""


def test_no_inflect(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata, noinflect=True) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItems(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
"""


def test_no_classes(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata, noclasses=True) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('id', Integer, primary_key=True)
)
"""


def test_table_kwargs(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 schema='testschema'
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'
 __table_args__ = {'schema': 'testschema'}

 id = Column(Integer, primary_key=True)
"""


def test_table_args_kwargs(metadata):
 simple_items = Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('name', VARCHAR),
 schema='testschema'
 )
 simple_items.indexes.add(Index('testidx', simple_items.c.id, simple_items.c.name))

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Index, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'
 __table_args__ = (
 Index('testidx', 'id', 'name'),
 {'schema': 'testschema'}
 )

 id = Column(Integer, primary_key=True)
 name = Column(String)
"""


def test_schema_table(metadata):
 Table(
 'simple_items', metadata,
 Column('name', VARCHAR),
 schema='testschema'
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('name', String),
 schema='testschema'
)
"""


def test_schema_boolean(metadata):
 Table(
 'simple_items', metadata,
 Column('bool1', INTEGER),
 CheckConstraint('testschema.simple_items.bool1 IN (0, 1)'),
 schema='testschema'
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('bool1', Boolean),
 schema='testschema'
)
"""


def test_foreign_key_options(metadata):
 Table(
 'simple_items', metadata,
 Column('name', VARCHAR, ForeignKey(
 'simple_items.name', ondelete='CASCADE', onupdate='CASCADE', deferrable=True,
 initially='DEFERRED'))
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
 'simple_items', metadata,
 Column('name', String, ForeignKey('simple_items.name', ondelete='CASCADE', \
onupdate='CASCADE', deferrable=True, initially='DEFERRED'))
)
"""


def test_foreign_key_schema(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('other_item_id', INTEGER),
 ForeignKeyConstraint(['other_item_id'], ['otherschema.other_items.id'])
 )
 Table(
 'other_items', metadata,
 Column('id', INTEGER, primary_key=True),
 schema='otherschema'
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class OtherItem(Base):
 __tablename__ = 'other_items'
 __table_args__ = {'schema': 'otherschema'}

 id = Column(Integer, primary_key=True)


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True)
 other_item_id = Column(ForeignKey('otherschema.other_items.id'))

 other_item = relationship('OtherItem')
"""


def test_pk_default(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True, server_default=text('uuid_generate_v4()'))
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, text
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True, server_default=text("uuid_generate_v4()"))
"""


def test_server_default_multiline(metadata):
 Table(
 'simple_items', metadata,
 Column('id', INTEGER, primary_key=True, server_default=text("""\
/*Comment*/
/*Next line*/
something()"""))
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, text
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple_items'

 id = Column(Integer, primary_key=True, server_default=text(\"""\\
/*Comment*/
/*Next line*/
something()\"""))
"""


def test_server_default_double_quotes(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True, server_default=text("nextval(\"foo\")")),
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, text
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True, server_default=text("nextval(\\"foo\\")"))
"""


def test_invalid_attribute_names(metadata):
 Table(
 'simple-items', metadata,
 Column('id-test', INTEGER, primary_key=True),
 Column('4test', INTEGER),
 Column('_4test', INTEGER),
 Column('def', INTEGER)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
 __tablename__ = 'simple-items'

 id_test = Column('id-test', Integer, primary_key=True)
 _4test = Column('4test', Integer)
 _4test1 = Column('_4test', Integer)
 _def = Column('def', Integer)
"""


def test_pascal(metadata):
 Table(
 'CustomerAPIPreference', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class CustomerAPIPreference(Base):
 __tablename__ = 'CustomerAPIPreference'

 id = Column(Integer, primary_key=True)
"""


def test_underscore(metadata):
 Table(
 'customer_api_preference', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class CustomerApiPreference(Base):
 __tablename__ = 'customer_api_preference'

 id = Column(Integer, primary_key=True)
"""


def test_pascal_underscore(metadata):
 Table(
 'customer_API_Preference', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class CustomerAPIPreference(Base):
 __tablename__ = 'customer_API_Preference'

 id = Column(Integer, primary_key=True)
"""


def test_pascal_multiple_underscore(metadata):
 Table(
 'customer_API__Preference', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class CustomerAPIPreference(Base):
 __tablename__ = 'customer_API__Preference'

 id = Column(Integer, primary_key=True)
"""


def test_metadata_column(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('metadata', VARCHAR)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 metadata_ = Column('metadata', String)
"""


@pytest.mark.skipif(sqlalchemy.__version__ < '1.2', reason='Requires SQLAlchemy 1.2+')
@pytest.mark.parametrize('nocomments', [False, True])
def test_column_comment(metadata, nocomments):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True, comment="this is a 'comment'")
 )

 comment_part = '' if nocomments else ', comment="this is a \'comment\'"'
 assert generate_code(metadata, nocomments=nocomments) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True{comment_part})
""".format(comment_part=comment_part)


@pytest.mark.skipif(sqlalchemy.__version__ < '1.2', reason='Requires SQLAlchemy 1.2+')
def test_table_comment(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 comment="this is a 'comment'"
 )

 codegen = CodeGenerator(metadata, noclasses=True)
 code = codegen.render_table(codegen.models[0])
 assert code == """\
t_simple = Table(
 'simple', metadata,
 Column('id', Integer, primary_key=True),
 comment='this is a \\'comment\\''
)
"""
 code = generate_code(metadata)
 assert code == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'
 __table_args__ = {'comment': "this is a 'comment'"}

 id = Column(Integer, primary_key=True)
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_timestamp(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('timestamp', mysql.TIMESTAMP)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 timestamp = Column(TIMESTAMP)
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_integer_display_width(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('number', mysql.INTEGER(11))
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 number = Column(INTEGER(11))
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_tinytext(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('my_tinytext', mysql.TINYTEXT)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.mysql import TINYTEXT
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 my_tinytext = Column(TINYTEXT)
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_mediumtext(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('my_mediumtext', mysql.MEDIUMTEXT)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 my_mediumtext = Column(MEDIUMTEXT)
"""


@pytest.mark.parametrize('metadata', ['mysql'], indirect=['metadata'])
def test_mysql_longtext(metadata):
 Table(
 'simple', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('my_longtext', mysql.LONGTEXT)
 )

 assert generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Integer
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
 __tablename__ = 'simple'

 id = Column(Integer, primary_key=True)
 my_longtext = Column(LONGTEXT)
"""


def test_table_name_identifiers(metadata):
 Table(
 'simple-items table', metadata,
 Column('id', INTEGER, primary_key=True)
 )

 assert generate_code(metadata, noclasses=True) == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items_table = Table(
 'simple-items table', metadata,
 Column('id', Integer, primary_key=True)
)
"""


@pytest.mark.skipif(Computed is None, reason='requires SQLAlchemy 1.3.11+')
@pytest.mark.parametrize('persisted, extra_args', [
 (None, ''),
 (False, ', persisted=False'),
 (True, ', persisted=True')
])
def test_computed_column(metadata, persisted, extra_args):
 Table(
 'computed', metadata,
 Column('id', INTEGER, primary_key=True),
 Column('computed', INTEGER, Computed('1 + 2', persisted=persisted))
 )

 assert generate_code(metadata, noclasses=True) == """\
# coding: utf-8
from sqlalchemy import Column, Computed, Integer, MetaData, Table

metadata = MetaData()


t_computed = Table(
 'computed', metadata,
 Column('id', Integer, primary_key=True),
 Column('computed', Integer, Computed('1 + 2'{extra_args}))
)
""".format(extra_args=extra_args)