/sqlautocode/tests/test_declarative.py

  1import os
  2import pprint as pp
  3from nose.tools import eq_
  4from sqlautocode.declarative import ModelFactory
  5from sqlalchemy.orm import class_mapper
  6testdb = 'sqlite:///'+os.path.abspath(os.path.dirname(__file__))+'/data/devdata.db'
  7#testdb = 'postgres://postgres@localhost/TestSamples'
  8
  9from base import make_test_db, make_test_db_multi
 10
 11class DummyOptions:
 12    
 13    tables = None
 14
 15class DummyConfig:
 16
 17
 18    def __init__(self, engine=testdb):
 19        self.engine  = engine
 20
 21    example = True
 22    schema = None
 23    interactive = None
 24    
 25    options=DummyOptions()
 26#    schema = ['pdil_samples', 'pdil_tools']
 27
 28
 29class _TestModelFactory:
 30
 31    def setup(self):
 32        self.config = DummyConfig()
 33        self.factory = ModelFactory(self.config)
 34
 35    def test_tables(self):
 36        tables = sorted([t.name for t in self.factory.tables])
 37        eq_(tables,  [u'tg_group', u'tg_group_permission', u'tg_permission', u'tg_town', u'tg_user', u'tg_user_group'])
 38
 39    def _setup_all_models(self):
 40        return self.factory.models
 41
 42    def test_create_model(self):
 43        self.factory.used_model_names = []
 44        self.factory.used_table_names = []
 45
 46        Group      = self.factory.create_model(self.factory._metadata.tables['tg_group'])
 47        Permission = self.factory.create_model(self.factory._metadata.tables['tg_permission'])
 48        Town       = self.factory.create_model(self.factory._metadata.tables['tg_town'])
 49        User       = self.factory.create_model(self.factory._metadata.tables['tg_user'])
 50
 51        class_mapper(Town)
 52        class_mapper(User)
 53
 54        t = Town(town_name="Arvada")
 55        u = User(tg_town=t)
 56        assert u.tg_town.town_name == 'Arvada'
 57
 58    def test_get_many_to_many_tables(self):
 59        tables = sorted([table.name for table in self.factory.get_many_to_many_tables()])
 60        eq_(tables, [u'tg_group_permission', u'tg_user_group'])
 61
 62    def test_get_related_many_to_many_tables(self):
 63        tables = [table.name for table in self.factory.get_related_many_to_many_tables('tg_user')]
 64        eq_(tables, [u'tg_user_group'])
 65
 66    def test_get_foreign_keys(self):
 67        columns = [c[0].column.name for c in self.factory.get_foreign_keys(self.factory._metadata.tables['tg_user']).values()]
 68        eq_(columns, ['town_id'])
 69
 70    def test_model___repr__(self):
 71        models = sorted(self._setup_all_models())
 72        for model in models:
 73            if model.__name__=='TgUser':
 74                User = model
 75        r = User.__repr__()
 76        print r
 77        expected = """\
 78class TgUser(DeclarativeBase):
 79    __tablename__ = 'tg_user'
 80
 81    #column definitions
 82    created = Column(u'created', TIMESTAMP(timezone=False))
 83    display_name = Column(u'display_name', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
 84    email_address = Column(u'email_address', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
 85    password = Column(u'password', VARCHAR(length=80, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
 86    town_id = Column(u'town_id', INTEGER(), ForeignKey('tg_town.town_id'))
 87    user_id = Column(u'user_id', INTEGER(), primary_key=True, nullable=False)
 88    user_name = Column(u'user_name', VARCHAR(length=16, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
 89
 90    #relation definitions
 91    tg_town = relation('TgTown', primaryjoin='TgUser.town_id==TgTown.town_id')
 92    tg_groups = relation('TgGroup', primaryjoin='TgUser.user_id==tg_user_group.c.user_id', secondary=tg_user_group, secondaryjoin='tg_user_group.c.group_id==TgGroup.group_id')
 93"""
 94        eq_(r.strip(), expected.strip())
 95
 96    def test__repr__(self):
 97
 98        r = repr(self.factory)
 99        print r
100        expected = """\
101#autogenerated by sqlautocode
102
103from sqlalchemy import *
104from sqlalchemy.ext.declarative import declarative_base
105from sqlalchemy.orm import relation
106
107engine = create_engine('sqlite:////Users/percious/oss/tgdev-py27/src/sqlautocode/sqlautocode/tests/data/devdata.db')
108DeclarativeBase = declarative_base()
109metadata = DeclarativeBase.metadata
110metadata.bind = engine
111
112tg_group_permission = Table(u'tg_group_permission', metadata,
113    Column(u'group_id', INTEGER(), ForeignKey('tg_group.group_id'), primary_key=True, nullable=False),
114    Column(u'permission_id', INTEGER(), ForeignKey('tg_permission.permission_id'), primary_key=True, nullable=False),
115)
116
117tg_user_group = Table(u'tg_user_group', metadata,
118    Column(u'user_id', INTEGER(), ForeignKey('tg_user.user_id'), primary_key=True, nullable=False),
119    Column(u'group_id', INTEGER(), ForeignKey('tg_group.group_id'), primary_key=True, nullable=False),
120)
121
122class TgGroup(DeclarativeBase):
123    __tablename__ = 'tg_group'
124
125    #column definitions
126    created = Column(u'created', TIMESTAMP(timezone=False))
127    display_name = Column(u'display_name', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
128    group_id = Column(u'group_id', INTEGER(), primary_key=True, nullable=False)
129    group_name = Column(u'group_name', VARCHAR(length=16, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
130
131    #relation definitions
132    tg_permissions = relation('TgPermission', primaryjoin='TgGroup.group_id==tg_group_permission.c.group_id', secondary=tg_group_permission, secondaryjoin='tg_group_permission.c.permission_id==TgPermission.permission_id')
133    tg_users = relation('TgUser', primaryjoin='TgGroup.group_id==tg_user_group.c.group_id', secondary=tg_user_group, secondaryjoin='tg_user_group.c.user_id==TgUser.user_id')
134
135
136class TgPermission(DeclarativeBase):
137    __tablename__ = 'tg_permission'
138
139    #column definitions
140    description = Column(u'description', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
141    permission_id = Column(u'permission_id', INTEGER(), primary_key=True, nullable=False)
142    permission_name = Column(u'permission_name', VARCHAR(length=16, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
143
144    #relation definitions
145    tg_groups = relation('TgGroup', primaryjoin='TgPermission.permission_id==tg_group_permission.c.permission_id', secondary=tg_group_permission, secondaryjoin='tg_group_permission.c.group_id==TgGroup.group_id')
146
147
148class TgTown(DeclarativeBase):
149    __tablename__ = 'tg_town'
150
151    #column definitions
152    town_id = Column(u'town_id', INTEGER(), primary_key=True, nullable=False)
153    town_name = Column(u'town_name', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
154
155    #relation definitions
156
157
158class TgUser(DeclarativeBase):
159    __tablename__ = 'tg_user'
160
161    #column definitions
162    created = Column(u'created', TIMESTAMP(timezone=False))
163    display_name = Column(u'display_name', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
164    email_address = Column(u'email_address', VARCHAR(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
165    password = Column(u'password', VARCHAR(length=80, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
166    town_id = Column(u'town_id', INTEGER(), ForeignKey('tg_town.town_id'))
167    user_id = Column(u'user_id', INTEGER(), primary_key=True, nullable=False)
168    user_name = Column(u'user_name', VARCHAR(length=16, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
169
170    #relation definitions
171    tg_town = relation('TgTown', primaryjoin='TgUser.town_id==TgTown.town_id')
172    tg_groups = relation('TgGroup', primaryjoin='TgUser.user_id==tg_user_group.c.user_id', secondary=tg_user_group, secondaryjoin='tg_user_group.c.group_id==TgGroup.group_id')
173
174
175#example on how to query your Schema
176from sqlalchemy.orm import sessionmaker
177session = sessionmaker(bind=engine)()
178objs = session.query(TgGroup).all()
179print 'All TgGroup objects: %s'%objs"""
180        assert expected in r, r
181
182
183class TestModelFactoryNew:
184
185    def setup(self):
186        self.metadata = make_test_db()
187        engine = self.metadata.bind
188        self.config = DummyConfig(engine)
189        self.factory = ModelFactory(self.config)
190        self.factory.models
191
192    def test_tables(self):
193        tables = sorted([t.name for t in self.factory.tables])
194        eq_(tables,  [u'environment', u'no_pk', u'report', u'ui_report'])
195
196    def test_setup_all_models(self):
197        assert len(self.factory.models) == 3
198
199    def test_repr_environ_model(self):
200        print self.factory.models
201        s = self.factory.models[0].__repr__()
202        assert s == """class Environment(DeclarativeBase):
203    __tablename__ = 'environment'
204
205    __table_args__ = {}
206
207    #column definitions
208    database_host = Column(u'database_host', VARCHAR(length=100, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
209    database_pass = Column(u'database_pass', VARCHAR(length=100, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
210    database_port = Column(u'database_port', VARCHAR(length=5, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
211    database_sid = Column(u'database_sid', VARCHAR(length=32, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
212    database_user = Column(u'database_user', VARCHAR(length=100, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
213    environment_id = Column(u'environment_id', NUMERIC(precision=10, scale=0, asdecimal=True), primary_key=True, nullable=False)
214    environment_name = Column(u'environment_name', VARCHAR(length=100, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
215
216    #relation definitions
217    reports = relation('Report', primaryjoin='Environment.environment_id==UiReport.environment_id', secondary=ui_report, secondaryjoin='UiReport.report_id==Report.report_id')
218""", s
219
220    def test_no_pk_table_in_output(self):
221        s = self.factory.__repr__()
222        assert """no_pk = Table(u'no_pk', metadata,
223    Column(u'data', TEXT(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)),
224)""" in s, s
225        
226        
227    def test_repr_report(self):
228        s = self.factory.models[1].__repr__()
229        assert s == """class Report(DeclarativeBase):
230    __tablename__ = 'report'
231
232    __table_args__ = {}
233
234    #column definitions
235    created_by = Column(u'created_by', NUMERIC(precision=10, scale=0, asdecimal=True), nullable=False)
236    created_date = Column(u'created_date', DATETIME(timezone=False), nullable=False)
237    deleted = Column(u'deleted', NUMERIC(precision=1, scale=0, asdecimal=True), nullable=False)
238    deleted_by = Column(u'deleted_by', NUMERIC(precision=10, scale=0, asdecimal=True))
239    deleted_date = Column(u'deleted_date', DATETIME(timezone=False))
240    environment_id = Column(u'environment_id', NUMERIC(precision=10, scale=0, asdecimal=True), ForeignKey('environment.environment_id'), nullable=False)
241    report_description = Column(u'report_description', VARCHAR(length=4000, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
242    report_id = Column(u'report_id', NUMERIC(precision=10, scale=0, asdecimal=True), primary_key=True, nullable=False)
243    report_name = Column(u'report_name', VARCHAR(length=50, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=False)
244    updated_by = Column(u'updated_by', NUMERIC(precision=10, scale=0, asdecimal=True), nullable=False)
245    updated_date = Column(u'updated_date', DATETIME(timezone=False), nullable=False)
246
247    #relation definitions
248    environment = relation('Environment', primaryjoin='Report.environment_id==Environment.environment_id')
249    environments = relation('Environment', primaryjoin='Report.report_id==UiReport.report_id', secondary=ui_report, secondaryjoin='UiReport.environment_id==Environment.environment_id')
250""", s
251
252        
253class TestModelFactoryMulti:
254
255    def __init__(self, *args, **kw):
256        
257        self.metadata = make_test_db_multi()
258        engine = self.metadata.bind
259        self.config = DummyConfig(engine)
260        self.factory = ModelFactory(self.config)
261#        self.factory.models
262        
263    
264    def test_get_foreign_keys(self):
265        fks = [t.name for t in self.factory.get_foreign_keys(self.metadata.tables['song']).keys()]
266        
267        eq_(fks, ['album'])
268        
269    def test_get_composite_fks(self):
270        fks = sorted([k.column.name for k in self.factory.get_composite_foreign_keys(self.metadata.tables['song'])[0]])
271        eq_(fks, [u'albumartist', u'albumname'] )
272 
273    def test_render_song(self):
274        self.factory.models
275        song = self.factory.models[1]
276        eq_(song.__repr__(), """class Song(DeclarativeBase):
277    __tablename__ = 'song'
278
279    __table_args__ = {}
280
281    #column definitions
282    songalbum = Column(u'songalbum', TEXT(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), ForeignKey('album.albumname'))
283    songartist = Column(u'songartist', TEXT(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), ForeignKey('album.albumartist'))
284    songid = Column(u'songid', INTEGER(), primary_key=True)
285    songname = Column(u'songname', TEXT(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False))
286
287    #relation definitions
288    album = relation('Album', primaryjoin="and_(Song.songartist==Album.albumartist, Song.songalbum==Album.albumname)")
289"""
290    )
291        
292