[sqlalchemy] Re: Unit testing with SA?

[Allen Bierbaum]

I have taken a quick look at this code.  But be warned I have not
actually used it yet so I have limited real experience with it...

Overall it looks very interesting.  I was expecting something much
more along the vein of DbUnit where you do something like this:

setUp():
   dataset = XmlDataset(dataset.xml)
   db.refresh(dataset)

tearDown():
   db.tearDown()

testMethod():
   # Do normal db queries here relying upon the data to be in there
   # some custom comparisons against loaded datasets are supported

You can get the idea of DbUnit by look at it's howto page:
http://dbunit.sourceforge.net/howto.html

So far the fixture code looks a little magic to me but that may be
because I do not use SessionContext or assign_mapper in my code.  I
think it would be helpful to see a full example of a simple test case
class (using unittest) that only uses the standard SA features found
in the SA tutorial.  This may help separate the SA plugin magic from
the fixture magic.

Anyway, see below for more detailed comments

On 2/6/07, Kumar McMillan [EMAIL PROTECTED] wrote:

 On 2/3/07, Allen Bierbaum [EMAIL PROTECTED] wrote:
 
  This works for creating the table structure, and that is important,
  but for me I think the actual data in the database is just was
  important if not more so. What are people using to populate the
  database with test data to use in the tests?

 if you feel adventurous, I've been working to implement SQLAlchemy in
 the fixture module :

 http://code.google.com/p/fixture/

 It's not too difficult to run insert statements at the top of your
 tests, or inject an SQL file.  But when you want to make assertions
 using that data things get tricky, which is why I created the fixture
 module.  To give you a very rough example of how this works (since the
 docs are very lacking at the moment), you have 1) DataSet objects to
 define data and 2) a Fixture object that knows how to load that data.

 In fact, special for you--!--and because I've been meaning to do so, I
 just checked in this example, to illustrate the point (if formatting
 goes funny, it is here:
 http://fixture.googlecode.com/svn/trunk/fixture/examples/db/sqlalchemy_examples.py
 ).  Before I get to it, I want to quickly address the point you made
 about having a gigabyte database but only needing a sample of it for
 testing.  I actually find the simplest way to work with fixtures is to
 use existing data.  So the module also includes a command line that
 can generate fixture code from a data source, given an object that
 knows how to select.  I.E.

 $ fixture path.to.a.table.widgets --dsn=postgres://[EMAIL PROTECTED]/db
 --query=widget_id=2

 will build fixture code (including all foreign key refs) by creating a
 select statement using the widgets table.  This command isn't
 implemented fully and may be rough around the edges but it works for
 something like above.

This command looks interesting and I need to try it on some of my
real data.  One question though, it looks like this generates python
code correct?  Have you thought about supporting the option of
generating some metadata xml file that can be loaded directly.  This
may make maintenance a little easier and the datasets less verbose.
It could also allow for hand editing of the xml datasources when that
makes sense.

 Now ... that example:


 SequencedSet Fixtures with SQLAlchemy and nose
 --

 Create your tables::

  from sqlalchemy import *
  from sqlalchemy.ext.sessioncontext import SessionContext
  from sqlalchemy.ext.assignmapper import assign_mapper

  meta = BoundMetaData(sqlite:///:memory:)
  session_context = SessionContext(
 ... lambda: create_session(bind_to=meta.engine))
 ...
  affiliates = Table('affiliates', meta,
 ... Column('id', INT, primary_key=True),
 ... Column('name', String),)
 ...
  class Affiliate(object): pass
  m = assign_mapper(session_context, Affiliate, affiliates)
  events = Table('events', meta,
 ... Column('id', INT, primary_key=True),
 ... Column('type', String),
 ... Column('affiliate_id', INT,
 ... ForeignKey('affiliates.id')),)
 ...
  class Event(object): pass
  m = assign_mapper(session_context, Event, events, properties = {
 ... 'affiliate': relation(Affiliate), })

 Note that using mappers above is not necessary.  The fixture module also
 supports interacting with mapper classes, however.

 Next you build the DataSet objects that you want to load, in this case they
 inherit from SequencedSet, an optional DataSet enhancement that simulates
 auto-incrementing IDs.  The IDs values can be overridden for any row and the
 column name is configurable, but defaults to 'id'::

Why is this needed?  If the id is already a sequence shouldn't the
standard inserts into the database handle this automatically?  How can
it be disabled?


  from fixture 

[sqlalchemy] Re: Unit testing with SA?

[Allen Bierbaum]

I am going to try to integrate this into my testing framework this
afternoon so I am sure I will have more questions after that.  In the
meantime see below...

On 2/7/07, Kumar McMillan [EMAIL PROTECTED] wrote:

 Thanks for taking a close look Allen.  Here are some answers...

 On 2/7/07, Allen Bierbaum [EMAIL PROTECTED] wrote:
  Overall it looks very interesting.  I was expecting something much
  more along the vein of DbUnit where you do something like this:
 
  setUp():
 dataset = XmlDataset(dataset.xml)
 db.refresh(dataset)
 
  tearDown():
 db.tearDown()
 
  testMethod():
 # Do normal db queries here relying upon the data to be in there
 # some custom comparisons against loaded datasets are supported

 given the first example datasets, the equivalent with fixture is:

 class TestMyCodeWithData(unittest.TestCase):
 def setUp(self):
 self.data = db.data(events_data)
 self.data.setup()

 def tearDown(self):
 self.data.teardown()

 def testSomething(self):
 joe = Affiliate.get(self.data.affiliates_data.joe.id)
 click = Event.get(self.data.events_data.joes_click.id)
 assert click.affiliate is joe
 assert click.type == self.data.events_data.joes_click.type


 And you;re right, I should add an example to this since people coming
 from DbUnit or rails, etc, will be more familiar with this approach.
 I started with the decorator example since I believe most of the time
 it is easier and faster to write small test functions over classes.
 But classes scale better and often it's cumbersome to convert test
 functions into classes when it comes to that -- hence, like the print
 problem [1], there is a good argument to always use test classes

Agreed.  This is probably the simplest example that people may want to
start with.  You could make it even simpler by using only a single
table but that prevents you from showing some of the advanced
features.

 [1] http://www.python.org/dev/peps/pep-3105/

 Having said that, this is a good idea.  I've committed
 fixture.DataTestCase, a mixin for use with unittest.TestCase
 derivatives.

I will give this a try and see how it works.

 I should also point out here that in python 2.5 you can write tests like:

 with db.data(events_data) as data:
  joe = Affiliate.get(data.affiliates_data.joe.id)
  click = Event.get(data.events_data.joes_click.id)
  assert click.affiliate is joe
  assert click.type == data.events_data.joes_click.type

I have no idea how this works. I guess that means I need to learn a
bit of python 2.5 soon. :)

Until then, it is nice to know that it can be even easier once I
understand python 2.5.

 As far as the XML approach -- I dislike this because I think fixtures
 are meant to be hand-coded and editable (in most cases).  XML is hard
 to edit and the rails approach to use YAML is good, but, python code
 is as good as YAML if you ask me.  Somone who feels storngly can
 submit a patch to me for loading fixtures in XML or YAML.

I understand your hesitation.  Maybe I could suggest that you just
think about adding some sort of loader plugin support similar to the
IDataSet interfaces in DbUnit
(http://dbunit.sourceforge.net/components.html).  This could provide a
point of extension for people in the future that may want to load XML,
YAML, or even excel files. :)

  So far the fixture code looks a little magic to me but that may be
  because I do not use SessionContext or assign_mapper in my code.  I
  think it would be helpful to see a full example of a simple test case
  class (using unittest) that only uses the standard SA features found
  in the SA tutorial.  This may help separate the SA plugin magic from
  the fixture magic.

 I admit the discovery of table objects is magical so yes I should
 come up with a better example.  Also, good point about not using
 extensions.  I just committed an update so that you can create a
 fixture purely from a session:

 db = SQLAlchemyFixture(session=sqlalchemy.create_session())

 keep in mind that if you need to init the session elsewhere, you can ...

 db = SQLAlchemyFixture()
 # anytime before db.data().setup() ...
 db.session = my_session

In my current system I have a single global session that is used for
everything.  Is there any reason you can see that I could not just
reuse this session in all the test cases or should I be creating a new
on each time?

  This command looks interesting and I need to try it on some of my
  real data.  One question though, it looks like this generates python
  code correct?  Have you thought about supporting the option of
  generating some metadata xml file that can be loaded directly.  This
  may make maintenance a little easier and the datasets less verbose.
  It could also allow for hand editing of the xml datasources when that
  makes sense.

 I think I addressed this above.  If you are talking about tons and
 tons of data, then yes I can see how python code might not make 

[sqlalchemy] Re: Unit testing with SA?

[Kumar McMillan]

  db = SQLAlchemyFixture()
  # anytime before db.data().setup() ...
  db.session = my_session

 In my current system I have a single global session that is used for
 everything.  Is there any reason you can see that I could not just
 reuse this session in all the test cases or should I be creating a new
 on each time?

Are you using postgres?  The only problem I foresee is if your test
does some work with table instances shared by the fixtures but doesn't
explicitly call rollback when there is an exception.  You will
probably even get a deadlock if that happens.  `ps aux | grep
postgres` will show if a fixture's delete statement is waiting on
another transaction.  I've tried to accomodate for this scenario so
let me know if you run into it again so I can add some tests for it.


 The other thing you lose with an SQL dump is that the output may not
 work across different database backends.  That is why I would really
 like the loading of the table to be routed back through SA so we can
 have some support for moving the testing data to whatever db's you end
 up needed.  (in my particular case this isn't really going to work
 because I need GIS support which is non portable, but it sounds like a
 nice capability to me)


agreed.  I'm not entirely convinced that XML is the way to go, but
this makes sense (and I like the plugin idea).  It would be mighty
fast with lxml.etree.iterparse().  still thinking...

 ...and points out a problem I am going to
 have using it.  In my current code I am not keeping the tables or
 mappers around.  Instead I have a database manager class that sets
 everthing up and simply holds onto the session and engine that should
 be used to query the database.  I rely upon the mapped classes to keep
 track of the table and metadata references internally.

 So... is there any way to associated a dataset with the Class type
 that is associated with the data in the dataset?

 for example maybe something like:

 class anything_I_want(DataSet):
mappedType = MyDataClass
class click:
   name=click

 or something else along these lines.  This seems like it would work
 well to tie the data back to the class type that is actually being
 mapped.  Then the anything_I_want class is really just a list of
 MyDataClass objects that need to be populated into the database.

Yes, I've made an attempt to support mapped classes but there are many
different ways to map classes so I might not have captured them all.
Please try:

class anything_i_want(DataSet):
class Meta:
storable=MyDataClass
class click:
name=click

and let me know if that doesn't work, so I can get an idea for how you
are using mapped classes.


-Kumar

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Unit testing with SA?

[sdobrev]

There is more; 
to have really independent tests u have to destroy all SA caches and 
mappers etc.

metadata.drop_all()
clear_mappers()
db.dispose()
and always let ALL sessions etc fall out of scope. 

Make sure u do not store exception contexts, nor any persistent 
objects between the tests.
otherwise u may get a memory leaking.

see ticket 451 and the attached files.

svil

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Unit testing with SA?

[Christopher Arndt]

Allen schrieb:
 How do people writing db applications with SA do unit testing on their
 code?  Are there any good db unit testing frameworks that work well
 with SA?  If I can't find anything I will probably just roll my own
 but I would like to keep from reinventing the wheel if I can help it.

Basically you have to initialize your database for each test and destroy it
afterwards again (otherwise it wouldn't be a 'unit' test). In the 'unittest'
module, you can use the 'setUp' and tearDown' methods of the TestCase class for
this purpose.

For example:

# model contains the SA table and mapper classes
import unittest
import sqlalchemy
import model

class SATestCase(unittest.TestCase):

def setUp(self):
# do what you have to do to bind the metadata/engine to the model here

# create all model tables
for item in dir(self.model):
item = getattr(self.model, item)
if isinstance(item, sqlalchemy.Table):
item.create(checkfirst=True)

def tearDown(self):
# drop all model tables
for item in dir(self.model):
item = getattr(self.model, item)
if isinstance(item, sqlalchemy.Table):
item.drop(checkfirst=True)
# close database

def testFoo(self):
# here comes your unit test


Chris

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---