Okay, so I've got some additional information for you which are
hopefully useful for you. I've tried to switch to the dev version but
I don't think that I'm on the right way right now.
So, here the SQL-code which generates the database view:
SELECT tr.*, tp.TestPlanName,
(SELECT IF(COUNT(ProductVersionId) > 0, 1, 0) FROM Masters WHERE
TestCaseId=tr.TestCaseId GROUP BY TestCaseId) AS UsesVM,
civ.SvnRevision CanSvnRevision,
civ.BuildDate CanBuildDate,
concat(cpv.BuildVersion, '-', civ.MinorVersion) CanVersion,
civ.ProductVersionId CanProductVersionId,
civ.State CanState,
civ.EnvironmentId CanEnvironmentId,
miv.SvnRevision MasSvnRevision,
miv.BuildDate MasBuildDate,
concat(mpv.BuildVersion, '-', miv.MinorVersion) MasVersion,
miv.ProductVersionId MasProductVersionId,
COUNT(IF(er.TestRunId = tr.TestRunId, 1, NULL)) numEvalResults,
COUNT(IF(er.TestRunId = tr.TestRunId AND er.DeviationGrade = 'A',1,
NULL)) DeviationA,
COUNT(IF(er.TestRunId = tr.TestRunId AND er.DeviationGrade = 'B',1,
NULL)) DeviationB,
COUNT(IF(er.TestRunId = tr.TestRunId AND er.DeviationGrade = 'C',1,
NULL)) DeviationC,
COUNT(IF(er.TestRunId = tr.TestRunId AND er.DeviationGrade = 'D',1,
NULL)) DeviationD,
COUNT(IF(er.TestRunId = tr.TestRunId AND er.DeviationGrade > 'D',1,
NULL)) DeviationZ,
MAX(er.DeviationGrade) MaxDeviation
FROM testruns tr
LEFT JOIN InstalledVersions civ ON
(tr.InstalledVersionId=civ.InstalledVersionId)
LEFT JOIN ProductVersions cpv ON
(civ.ProductVersionId=cpv.ProductVersionId)
LEFT JOIN InstalledVersions miv ON (tr.UsedAsMaster =
miv.InstalledVersionId)
LEFT JOIN ProductVersions mpv ON (miv.ProductVersionId =
mpv.ProductVersionId)
LEFT JOIN TestPlans tp ON (tr.TestPlanId = tp.TestPlanId)
LEFT JOIN EvaluationResults er ON (tr.TestRunId = er.TestRunId)
GROUP BY TestRunId
Tried to optimize it a bit to run faster. It also is pretty fast - if
not used in a view;)
So.. I started with this model:
#==============================================================================
class vTestRunOverview(models.Model):
#==============================================================================
"""
This model should just be used for read only-access to the
vTestRunOverview-View in the MySQL-DB.
"""
objects = TestRunOverviewManager()
# From table TestRuns
testRunId = models.ForeignKey(TestRun, blank=True,
primary_key=True, db_column='TestRunId')
testCaseId = models.ForeignKey(TestCase, blank=True,
db_column='TestCaseId')
installedVersionId = models.ForeignKey(InstalledVersion,
blank=True, db_column='InstalledVersionId')
state = models.CharField(max_length=20, choices = TESTRUN_STATE,
db_column='State')
updateTime = models.DateTimeField(db_column='UpdateTime',
editable=False)
usedAsMaster = models.ForeignKey(InstalledVersion, null=True,
db_column='UsedAsMaster', related_name='UsedAsMaster')
startDuration = models.DecimalField(max_digits=10,
decimal_places=2, db_column='StartDuration')
loadDuration = models.DecimalField(max_digits=10,
decimal_places=2, db_column='LoadDuration')
runDuration = models.DecimalField(max_digits=10, decimal_places=2,
db_column='RunDuration')
exitDuration = models.DecimalField(max_digits=10,
decimal_places=2, db_column='ExitDuration')
testDuration = models.DecimalField(max_digits=10,
decimal_places=2, db_column='TestDuration')
# From table TestPlans
testPlanName = models.CharField(max_length=20,
db_column='TestPlanName')
# From Table Masters
usesVM = models.IntegerField(db_column='UsesVM')
# From table InstalledVersions
cSvnRevision = models.CharField(max_length=10,
db_column='CanSvnRevision')
cBuildDate = models.DateField(null=True, blank=True,
db_column='CanBuildDate')
cProductVersionId = models.IntegerField(null=True, blank=True,
db_column='CanProductVersionId')
cState = models.CharField(max_length=10, db_column='CanState')
cEnvironmentId = models.IntegerField(db_column='CanEnvironmentId')
mSvnRevision = models.CharField(max_length=10,
db_column='MasSvnRevision')
mBuildDate = models.DateField(null=True, blank=True,
db_column='MasBuildDate')
mProductVersionId = models.DateField(null=True, blank=True,
db_column='MasProductVersionId')
# Derived fields
cVersion = models.CharField(max_length=41, db_column='CanVersion')
mVersion = models.CharField(max_length=41, db_column='MasVersion')
numEvalResults = models.IntegerField(db_column='NumEvalResults')
deviationACnt = models.IntegerField(db_column='DeviationA')
deviationBCnt = models.IntegerField(db_column='DeviationB')
deviationCCnt = models.IntegerField(db_column='DeviationC')
deviationDCnt = models.IntegerField(db_column='DeviationD')
deviationZCnt = models.IntegerField(db_column='DeviationZ')
maxDeviation = models.CharField(max_length=10,
db_column='MaxDeviation')
#--------------------------------------------------------------------------
class Meta:
#--------------------------------------------------------------------------
db_table = 'TestRuns'
As far as I understand it, this is also required if I use a custom
Manager because django uses it to determine related fields (I need
select_related()).
Now I tried to create a custom manager which should just consist of a
get_query_set() with all fields mentioned above added to the
extra(select=[...]). To get the joins, I trued the extra(tables=[...])
but without the possibility of using aliases for tables that wasn't
such a good idea (okay, as mentioned in the documentation). I got the
error that the tables aren't uniqie ("Not unique table/alias:
'InstalledVersions'").
Here is my Manager:
#==============================================================================
class TestRunOverviewManager(models.Manager):
#==============================================================================
def get_query_set(self):
return super(TestRunOverviewManager,
self).get_query_set().extra(
select={
'UsesVM': '(SELECT IF(COUNT(ProductVersionId) > 0, 1,
0) FROM Masters WHERE TestCaseId=tr.TestCaseId GROUP BY TestCaseId)',
'CanSvnRevision': 'civ.SvnRevision',
'CanBuildDate': 'civ.BuildDate',
'CanVersion': 'concat(cpv.BuildVersion, \'-\',
civ.MinorVersion)',
'CanProductVersionId': 'civ.ProductVersionId',
'CanState': 'civ.State',
'CanEnvironmentId': 'civ.EnvironmentId',
'MasSvnRevision': 'miv.SvnRevision',
'MasBuildDate': 'miv.BuildDate',
'MasVersion': 'concat(mpv.BuildVersion, \'-\',
miv.MinorVersion)',
'MasProductVersionId': 'miv.ProductVersionId',
'numEvalResults': 'COUNT(IF(er.TestRunId =
tr.TestRunId, 1, NULL))',
'DeviationA': 'COUNT(IF(er.TestRunId = tr.TestRunId
AND er.DeviationGrade = \'A\',1, NULL))',
'DeviationB': 'COUNT(IF(er.TestRunId = tr.TestRunId
AND er.DeviationGrade = \'B\',1, NULL))',
'DeviationC': 'COUNT(IF(er.TestRunId = tr.TestRunId
AND er.DeviationGrade = \'C\',1, NULL))',
'DeviationD': 'COUNT(IF(er.TestRunId = tr.TestRunId
AND er.DeviationGrade = \'D\',1, NULL))',
'DeviationZ': 'COUNT(IF(er.TestRunId = tr.TestRunId
AND er.DeviationGrade > \'D\',1, NULL))',
'MaxDeviation': 'MAX(er.DeviationGrade)'
},
tables=['InstalledVersions', 'ProductVersions',
'InstalledVersions', 'TestPlans', 'EvaluationResults']
).select_related()
I hope you can help my with this;)
Best Regards
- Adrian
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
