Transaction / RawSequel - And the joys of testing

Both of these work in "production" The later only works in testing. Now that I've gotten something to work in both production and testing I'd like to understand why I had to go the whole cursors route rather than the Django route. I believe the problem has to do with transactions but I'm not positive and as I sit here at 8:30pm it's bugging me.

This is related to this question where I thought I had my answer (and an understanding) but alas I didn't. My testing is an A/B where A is injected outside of Django and B compares against the known A. The answer that was provided solved part of my problem but when I added in more tests the problem kept resurfacing.

I dug in and assumed it was the RawQuery was not committing the transaction but no amount transaction.commit seemed to fix it. I also 开发者_JAVA技巧removed the django.testing.TestCase from it and went straight unittest. I think I've tried every combination but I'm not very proficient in SQL or Transactional support and now I'm left wondering why one works and one doesn't...

If anyone has any insight here I would really appreciate it!

Update 2 Revised and cleaned up but still failing..

    # BUG: https://code.djangoproject.com/ticket/12768
    # - Requirement for pmProp.* - This (in-part) forced me to shift to raw.
    sqlraw = """ SELECT
                    pmProp.propid as propid_id,
                    pmProp.owner as owner,
                    pmProp.ownertype as ownertype,
                    pmProp.behavior as behavior,
                    pmProp.value as value_id,
                    pmPropDef.id as propdef_id,
                    pmPropDef.name as name,
                    pmPropDef.datatype as datatype,
                    pmPropDef.choicetype as choicetype,
                    pmPropDef.definition as definition_id,
                    pmPropDef.ptrig as prop_trigger,
                    pmPropDef.units as units,
                    IFNULL(pmPropShort.str, pmPropLong.str) as str_value FROM pmProp
                INNER JOIN pmPropDef ON pmProp.propid=pmPropDef.id AND pmPropDef.name=%s
                LEFT JOIN pmPropShort ON sid=pmProp.value
                LEFT JOIN pmPropLong ON lid=-pmProp.value
                WHERE pmProp.ownertype=%s AND pmProp.owner=%s AND pmPropDef.id=pmProp.propid
                """
    if explicit:
            sqlraw += " AND pmProp.behavior='explicit'"

    # TRY ONE - DOES NOT WORK FOR TESTING..
    # This will NOT work for testing - It simply doesn't get the value
    # when repeatedly inserting from pm and checking the value.
    #
    # Note if you use this you must update the sqlraw to include pmProp.* bug..
    #
    #try:
    #    result = list(Property.objects.raw(sqlraw, [property, owner, self.id]))[0]
    #    result.value = self.coerce_datatype(result.str_val, result.datatype)
    #except IndexError:
    #    result = None
    # END TRY ONE

    # Try TWO:  THIS WORKS for both
    cursor = connections['catalog'].cursor()
    cursor.execute(sqlraw, [property, owner, self.id])
    row = cursor.fetchone()
    transaction.commit_unless_managed(using='catalog')

    if row:
        field_map =  "propid_id owner ownertype behavior value_id propdef_id "
        field_map += "name datatype choicetype definition_id prop_trigger "
        field_map +=  "units str_value"
        field_map = field_map.split()
        class PropVal(object): pass
        result = PropVal()
        result.__dict__=dict(zip(field_map, row))
        result.value = self.coerce_datatype(result.str_value, result.datatype)
        try:
            log.info("%s %s=%s %s" % (property.capitalize(), result.name,
                                               result.value, result.units))
        except UnicodeDecodeError: pass
    else:
        result = None
    # END TRY Two

Update

Here is a sample A/B test.

from django.db import connection, transaction
from unittest import TestCase
#from django.test import TestCase, TransactionTestCase
from apps.pmCatalog.utility.ICMPM.pm import Pm
from apps.pmCatalog.models import Property, Site, Project, Variant, Library, LibraryType, Release
import settings
import datetime

import logging
log = logging.getLogger(__name__)

class PropertyTests(TestCase):

    def test_add_property_value(self):
        """Test the ability to add a property and retrieve a property"""

        prop_types = [("string", "Funny Business"), ("integer", 1234), ("real", 12.34) ]
        pm = Pm(mysql_db='test_bugs')
        tree = pm.add_release_tree()

        for prop_type, pmvalue in prop_types:
            # Add a property definition for a branch (like a project)
            pmproperty = "%s_%s_basic" % (tree[0].name, prop_type)
            pm.add_property_definition(pmproperty, prop_type=prop_type)
            pm.add_propval(pmproperty, value=pmvalue, project=tree[0].name)
            #Project.objects.update()
            project = Project.objects.get(name=pmproject.name)
            property = project.get_property(pmproperty)
            #When using the first one this ALWAYS returned None!
            self.assertEqual(str(pmvalue), property.str_value)
            self.assertEqual(pmvalue, property.value)

Thanks!

---

I see two problems in

sqlraw = """SELECT pmProp.*, pmPropDef.id, pmPropDef.name, pmPropDef.units,
            IFNULL(pmPropShort.str, pmPropLong.str) as value FROM pmProp
            INNER JOIN pmPropDef ON pmProp.propid=pmPropDef.id AND pmPropDef.name=%s
            LEFT JOIN pmPropShort ON sid=pmProp.value
            LEFT JOIN pmPropLong ON lid=-pmProp.value
            WHERE pmProp.ownertype=%s AND pmProp.owner=%s AND pmPropDef.id = pmProp.propid
            """

1. you are getting both pmPropDef.id and pmProp.propid, even if they are equal and the former would't map to a Property field.

2. in general, using a .raw() query, you have to return the correct names (either using SELECT pmPropDef.name AS name and so on for each field, or using the optional translation map to the raw() method, which will map columns to properties. It's easy to return directly the actual names

Try the following (adjust to match your actual column names in the table and field names on the model):

sqlraw = """SELECT 
                pmProp.id as id,
                pmProp.owner as owner,
                pmProp.ownertype as ownertype,
                pmProp.behavior as behavior,
                pmProp.propdef_id as propdef_id,
                pmPropDef.name as name, 
                pmPropDef.units as units,
                IFNULL(pmPropShort.str, pmPropLong.str) as str_value
            FROM pmProp
            INNER JOIN pmPropDef ON pmProp.propid=pmPropDef.id AND pmPropDef.name=%s
            LEFT JOIN pmPropShort ON sid=pmProp.value
            LEFT JOIN pmPropLong ON lid=-pmProp.value
            WHERE pmProp.ownertype=%s AND pmProp.owner=%s AND pmPropDef.id = pmProp.propid
            """

If you really need the coerced value already, then try to coerce it in the same query.