def test_diff():
expected = '''\
SELECT book.code, book.title, book.score FROM book
LEFT JOIN score
ON book.score=score.value
WHERE score.value IS NULL'''
assert_equal(str(orm.diff(s.book, s.score)), expected)
assert_equal(str(orm.diff(s.book, s.score, {'book.score': 'score.value'})),
expected)
assert_equal(
str(orm.diff(s.book, s.score, {'score.value': 'book.score'})),
expected)
assert_equal(str(orm.diff(s.score, s.book)), '''\
SELECT score.value, score.evaluation FROM score
LEFT JOIN book
ON score.value=book.score
WHERE book.score IS NULL''')
def _build_queries(self, csvfile, tempname=None):
self.tempname = tempname or (
'csvloader_' + str(uuid.uuid1()).replace('-', '_'))
kfields = [c.name for c in self.table._columns if c.is_key]
vfields = [c.name for c in self.table._columns
if c.name not in kfields]
fkeys = dict((c.name, str(c)) for c in self.table._columns if c.is_key)
common_vfields = [f for f in csvfile.fields if f in vfields]
schema = self.table._schema
self.tmp = db.def_table(
schema, self.tempname, kfields, common_vfields, fkeys)
db.def_rel_methods(schema, self.tempname)
# generate the queries needed to modify the target table
if self.mode in ('update', 'save'):
sets = ', '.join('%s=%s' % (c.name, c) for c in self.tmp._columns
if not c.is_key)
update = 'UPDATE {}\nSET {}\nFROM {}\nWHERE {}'.format(
self.table._name, sets, self.tempname,
orm.join_cond(self.table, self.tmp))
yield sql.Query(update, 'execute')
if self.mode in ('create', 'save'):
create = 'INSERT INTO {}\n{}'.format(
self.table._name, orm.diff(self.tmp, self.table))
yield sql.Query(create, 'execute')