#! /usr/bin/env python
# -*- coding: iso-8859-1 -*-
#-----------------------------------------------------------------------------
# Modeling Framework: an Object-Relational Bridge for python
#
# Copyright (c) 2001-2004 Sbastien Bigaret <sbigaret@users.sourceforge.net>
# All rights reserved.
#
# This file is part of the Modeling Framework.
#
# This code is distributed under a "3-clause BSD"-style license;
# see the LICENSE file for details.
#-----------------------------------------------------------------------------
"""
Tests for SQLExpression
CVS information
$Id: test_SQLExpression.py 948 2004-11-24 11:26:36Z sbigaret $
"""
__version__='$Revision: 948 $'[11:-2]
import unittest, string
from os import getcwd,path
if __name__ == "__main__":
import utils, sys
utils.fixpath()
from Modeling.SQLExpression import SQLExpression,InvalidSQLTypeError,\
BinaryType, CharacterType, DateType, NumericType
from Modeling import FetchSpecification,Qualifier
from Modeling.Qualifier import qualifierWithQualifierFormat
# load the models
from Modeling.Model import ModelError
from Modeling import ModelSet
# Old (deprecated) model AuthorBook
modelSet=ModelSet.ModelSet()
xmlmodelPath=path.join(getcwd(),'xmlmodels/model_AuthorBook.xml')
modelSet.addModelFromXML({'file': xmlmodelPath})
AuthorBookModel=modelSet.modelNamed('AuthorBook')
# New model AuthorBooks
from testPackages.AuthorBooks import Writer
AuthorBooksModel=ModelSet.defaultModelSet().modelNamed('AuthorBooks')
class TestSQLExpression_1(unittest.TestCase):
"Tests for SQLExpressions"
def setUp(self):
"setUp"
#self.model=ModelSet.defaultModelSet().modelNamed('AuthorBook')
self.model=AuthorBookModel
def test_01_sqlStringForAttributeNamed(self):
"[SQLExpression] sqlStringForAttributeNamed"
# Tests sqlStringForAttribute and sqlStringForAttributePath as well
book=self.model.entityNamed('Book')
writer=self.model.entityNamed('Writer')
writer_bookTitle=writer.attributeNamed('bookTitle')
book_title=book.attributeNamed('title')
# should trigger sqlStringForAttribute
sqlExpr=SQLExpression(writer)
self.assertEqual(sqlExpr.sqlStringForAttributeNamed('firstName'),
"FIRST_NAME")
sqlExpr.setUseAliases(1)
self.assertEqual(sqlExpr.sqlStringForAttributeNamed('firstName'),
"t0.FIRST_NAME")
# should trigger sqlStringForAttribute
# NB: de-activated, derived attributes are not handled for the moment
#sqlStr=sqlExpr.sqlStringForAttributeNamed('bookTitle')
#alias=sqlExpr.aliasesByRelationshipPath().get('book')
#self.failUnless(alias)
#self.assertEqual(sqlStr, alias+'.'+book_title.columnName())
def test_02_prepareSelectExpressionWithAttributes(self):
"[SQLExpression] prepareSelectExpressionWithAttributes"
book=self.model.entityNamed('Book')
sqlExpr=SQLExpression(book)
sqlExpr.prepareSelectExpressionWithAttributes(book.attributesToFetch(),
lock=0,
fetchSpec=None)
# NB: This also tests addSelectListAttribute
self.failUnless(string.find(sqlExpr.listString(), 't0.ID')!=-1)
self.failUnless(string.find(sqlExpr.listString(), 't0.TITLE')!=-1)
#print sqlExpr.statement()
self.assertEqual(sqlExpr.statement(),
"SELECT t0.ID, t0.TITLE FROM BOOK t0")
def test_03_valueTypeForExternalType(self):
"[SQLExpression] valueTypeForExternalType"
sqlExpr=SQLExpression()
self.assertEqual(sqlExpr.valueTypeForExternalType('VARCHAR'),
CharacterType)
def test_04_sqlStringForValueType(self):
"[SQLExpression] sqlStringForValueType"
writer=self.model.entityNamed('Writer')
sqlExpr=SQLExpression(writer)
self.assertEqual(sqlExpr.sqlStringForString("Qui que v'la?"),
"'Qui que v\\'la?'")
self.assertEqual(sqlExpr.sqlStringForValue("Qui que v'la?",
'book.title'),
"'Qui que v\\'la?'")
def test_05_formatValueForAttribute(self):
"[SQLExpression] formatValueForAttribute raises InvalidSQLTypeError"
writer=self.model.entityNamed('Writer')
sqlExpr=SQLExpression(writer)
try:
attr=writer.attributeNamed('firstName')
ext_type_ori=attr.externalType()
attr.setExternalType("UNKNOWN_SQL_TYPE")
self.assertRaises(InvalidSQLTypeError, sqlExpr.formatValueForAttribute,
'dummy value', attr)
finally:
attr.setExternalType(ext_type_ori)
class TestSQLExpression_2(unittest.TestCase):
"Tests for SQLExpressions"
def setUp(self):
"setUp"
#self.model=ModelSet.defaultModelSet().modelNamed('AuthorBook')
self.model=AuthorBooksModel
def test_05_sqlStringForAttributePath(self):
"[SQLExpression] sqlStringForAttributePath"
writer=self.model.entityNamed('Writer')
book=self.model.entityNamed('Book')
book_author=book.relationshipNamed('author')
author_pygmalion=writer.relationshipNamed('pygmalion')
author_lastName=writer.attributeNamed('lastName')
sqlExpr=SQLExpression(book)
sqlExpr.setUseAliases('Yes')
self.assertEqual(sqlExpr.sqlStringForAttributePath((book_author,
author_pygmalion,
author_lastName)),
't2.LAST_NAME')
# check internals
#print sqlExpr._internals._Internals__entityExternalNamesByAliases
#print sqlExpr._internals._Internals__aliasesByRelPaths
#print sqlExpr._internals._Internals__relPathsByAliases
#print sqlExpr._internals._Internals__comesFromAlias
def test_06_prepareSelectExpressionWithAttributes_01(self):
"[SQLExpression] prepareSelectExpressionWithAttributes/KeyValueQualifier"
book=self.model.entityNamed('Book')
sqlExpr=SQLExpression(book)
q=qualifierWithQualifierFormat('author.pygmalion.lastName == "Rabelais"')
fs=FetchSpecification.FetchSpecification('Book', qualifier=q)
sqlExpr.prepareSelectExpressionWithAttributes(book.attributesToFetch(),
lock=0, fetchSpec=fs)
# Note: the result is directly dependant on the order in which attributes
# are returned by Entity.attributesToFetch() (which in turn depends on
# other things, such as attributes that are used for locking, etc.)
attrs=', '.join(['t0.%s'%a.columnName() for a in book.attributesToFetch()])
self.assertEqual(sqlExpr.statement(),
"SELECT DISTINCT %s FROM BOOK t0 INNER JOIN ( WRITER t1 INNER JOIN WRITER t2 ON t1.FK_WRITER_ID=t2.ID ) ON t0.FK_WRITER_ID=t1.ID WHERE t2.LAST_NAME = 'Rabelais'"%attrs)
# None/NULL value
sqlExpr=SQLExpression(book)
q=qualifierWithQualifierFormat('author.pygmalion.books.price == None')
fs=FetchSpecification.FetchSpecification('Book',
qualifier=q)
sqlExpr.prepareSelectExpressionWithAttributes(book.attributesToFetch(),
lock=0, fetchSpec=fs)
self.assertEqual(sqlExpr.statement(),
"SELECT DISTINCT %s FROM BOOK t0 INNER JOIN ( WRITER t1 INNER JOIN ( WRITER t2 INNER JOIN BOOK t3 ON t2.ID=t3.FK_WRITER_ID ) ON t1.FK_WRITER_ID=t2.ID ) ON t0.FK_WRITER_ID=t1.ID WHERE t3.PRICE IS NULL"%attrs)
def test_07_prepareSelectExpressionWithAttributes_02(self):
"[SQLExpression] prepareSelectExpressionWithAttributes/KeyComparisonQualifier"
book=self.model.entityNamed('Book')
sqlExpr=SQLExpression(book)
qualifier=qualifierWithQualifierFormat('author.pygmalion.lastName == author.lastName')
fetchSpec=FetchSpecification.FetchSpecification('Book',
qualifier=qualifier)
sqlExpr.prepareSelectExpressionWithAttributes(book.attributesToFetch(),
lock=0,
fetchSpec=fetchSpec)
# See note in test_06_prepareSelectExpressionWithAttributes_01()
attrs=', '.join(['t0.%s'%a.columnName() for a in book.attributesToFetch()])
self.assertEqual(sqlExpr.statement(),
"SELECT DISTINCT %s FROM BOOK t0 INNER JOIN ( WRITER t1 INNER JOIN WRITER t2 ON t1.FK_WRITER_ID=t2.ID ) ON t0.FK_WRITER_ID=t1.ID WHERE t2.LAST_NAME = t1.LAST_NAME"%attrs)
def test_suite():
suite = unittest.TestSuite()
suite.addTest(unittest.makeSuite(TestSQLExpression_1, "test_"))
suite.addTest(unittest.makeSuite(TestSQLExpression_2, "test_"))
return suite
if __name__ == "__main__":
errs = utils.run_suite(test_suite())
sys.exit(errs and 1 or 0)
|