def test_sql_insert(self):
cursor = MagicMock()
now = datetime.now()
# Use ordereddict to ensure correct order in generated SQL request
doc = OrderedDict()
doc['_id'] = ObjectId.from_datetime(now)
doc['field'] = 'val'
sql.sql_insert(cursor, 'table', doc, '_id')
doc['_creationDate'] = utils.extract_creation_date(doc, '_id')
cursor.execute.assert_called_with(
'INSERT INTO table (_creationDate,_id,field) VALUES (%(_creationDate)s,%(_id)s,%(field)s) ON CONFLICT (_id) DO UPDATE SET (_creationDate,_id,field) = (%(_creationDate)s,%(_id)s,%(field)s) ',
doc
)
doc = {
'field': 'val'
}
sql.sql_insert(cursor, 'table', doc, '_id')
cursor.execute.assert_called_with(
'INSERT INTO table (field) VALUES (%(field)s) ',
doc
)
def sql_insert(cursor, tableName, document, primary_key):
creationDate = extract_creation_date(document, primary_key)
if creationDate is not None:
document['_creationDate'] = creationDate
keys = get_document_keys(document)
valuesPlaceholder = ("%(" + column_name + ")s" for column_name in keys)
if primary_key in document:
sql = u"INSERT INTO {0} {1} VALUES {2} ON CONFLICT ({3}) DO UPDATE SET {1} = {2}".format(
tableName,
to_sql_list(keys),
to_sql_list(valuesPlaceholder),
primary_key
)
else:
sql = u"INSERT INTO {0} {1} VALUES {2}".format(
tableName,
to_sql_list(keys),
to_sql_list(valuesPlaceholder),
primary_key
)
try:
cursor.execute(sql, document)
except Exception as e:
LOG.error(u"Impossible to upsert the following document %s : %s", document, e)
def test_extract_creation_date(self):
now = datetime.now()
doc = {
'_id': ObjectId.from_datetime(now)
}
got = utils.extract_creation_date(doc, '_id')
expected = now
if expected.utcoffset() is not None:
expected -= expected.utcoffset()
expected = timegm(expected.timetuple())
expected = datetime.fromtimestamp(expected, utc)
self.assertEqual(expected, got)
got = utils.extract_creation_date({}, '_id')
self.assertEqual(got, None)
def test_extract_creation_date(self):
now = datetime.now()
doc = {
'_id': ObjectId.from_datetime(now)
}
got = utils.extract_creation_date(doc, '_id')
expected = now
if expected.utcoffset() is not None:
expected -= expected.utcoffset()
expected = timegm(expected.timetuple())
expected = datetime.fromtimestamp(expected, utc)
self.assertEqual(expected, got)
got = utils.extract_creation_date({}, '_id')
self.assertIsNone(got)
def _sql_bulk_insert(query, mappings, namespace, documents):
if not documents:
return
db, collection = db_and_collection(namespace)
primary_key = mappings[db][collection]['pk']
keys = [(k, v['dest']) for k, v in iteritems(mappings[db][collection])
if 'dest' in v
and v['type'] not in [ARRAY_TYPE, ARRAY_OF_SCALARS_TYPE]]
keys.sort(key=lambda x: x[1])
for document in documents:
mapped_document = get_mapped_document(mappings, document, namespace)
values = [
to_sql_value(extract_creation_date(mapped_document, primary_key),
vtype='TIMESTAMP')
]
for key, mapkey in keys:
field_mapping = mappings[db][collection][key]
if mapkey in mapped_document:
values.append(
to_sql_value(mapped_document[mapkey],
vtype=field_mapping['type']))
else:
values.append(to_sql_value(None, vtype=field_mapping['type']))
subquery = {
'collection': collection,
'document': {
'raw': document,
'mapped': mapped_document
},
'keys': ['_creationDate'] + [k[1] for k in keys],
'values': values,
'pk': primary_key,
'queries': []
}
query.append(subquery)
insert_document_arrays(collection, subquery['queries'], db, document,
mapped_document, mappings, primary_key)
insert_scalar_arrays(collection, subquery['queries'], db, document,
mapped_document, mappings, primary_key)
def sql_bulk_insert(cursor, mappings, namespace, documents):
if not documents:
return
db, collection = db_and_collection(namespace)
primary_key = mappings[db][collection]['pk']
keys = [
v['dest'] for k, v in iteritems(mappings[db][collection])
if 'dest' in v and v['type'] != ARRAY_TYPE
and v['type'] != ARRAY_OF_SCALARS_TYPE
]
keys.sort()
values = []
for document in documents:
mapped_document = get_mapped_document(mappings, document, namespace)
document_values = [
to_sql_value(
extract_creation_date(mapped_document,
mappings[db][collection]['pk']))
]
if not mapped_document:
break
for key in keys:
if key in mapped_document:
document_values.append(to_sql_value(mapped_document[key]))
else:
document_values.append(to_sql_value(None))
values.append(u"({0})".format(u','.join(document_values)))
insert_document_arrays(collection, cursor, db, document,
mapped_document, mappings, primary_key)
insert_scalar_arrays(collection, cursor, db, document, mapped_document,
mappings, primary_key)
if values:
sql = u"INSERT INTO {0} ({1}) VALUES {2}".format(
collection, u','.join(['_creationDate'] + keys), u",".join(values))
cursor.execute(sql)
def sql_bulk_insert(cursor, mappings, namespace, documents):
if not documents:
return
db, collection = db_and_collection(namespace)
primary_key = mappings[db][collection]['pk']
keys = unique([
v['dest'] for k, v in iteritems(mappings[db][collection])
if 'dest' in v and v['type'] != ARRAY_TYPE
and v['type'] != ARRAY_OF_SCALARS_TYPE
])
values = []
for document in documents:
mapped_document = get_mapped_document(mappings, document, namespace)
document_values = [to_sql_value(extract_creation_date(mapped_document, mappings[db][collection]['pk']))]
if not mapped_document:
break
for key in keys:
if key in mapped_document:
document_values.append(to_sql_value(mapped_document[key]))
else:
document_values.append(to_sql_value(None))
values.append(u"({0})".format(u','.join(document_values)))
insert_document_arrays(collection, cursor, db, document, mapped_document, mappings, primary_key)
insert_scalar_arrays(collection, cursor, db, document, mapped_document, mappings, primary_key)
if values:
sql = u"INSERT INTO {0} ({1}) VALUES {2}".format(
collection,
u','.join(['_creationDate'] + keys),
u",".join(values)
)
cursor.execute(sql)
def test_sql_insert(self):
cursor = MagicMock()
now = datetime.now()
# Use ordereddict to ensure correct order in generated SQL request
doc = OrderedDict()
doc['_id'] = ObjectId.from_datetime(now)
doc['field'] = 'val'
sql.sql_insert(cursor, 'table', doc, '_id')
doc['_creationDate'] = utils.extract_creation_date(doc, '_id')
cursor.execute.assert_called_with(
'INSERT INTO table (_creationDate,_id,field) VALUES (%(_creationDate)s,%(_id)s,%(field)s) ON CONFLICT (_id) DO UPDATE SET (_creationDate,_id,field) = (%(_creationDate)s,%(_id)s,%(field)s) ',
doc)
doc = {'field': 'val'}
sql.sql_insert(cursor, 'table', doc, '_id')
cursor.execute.assert_called_with(
'INSERT INTO table (field) VALUES (%(field)s) ', doc)