def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, False)
monetdbops.dropTable(cursor, self.resultTable, True)
t0 = time.time()
(query, queryArgs) = dbops.getSelect(self.qp, self.flatTable,
self.addContainsCondition,
self.DM_FLAT)
if self.qp.queryMethod != 'stream': # disk or stat
monetdbops.mogrifyExecute(
cursor, "CREATE TABLE " + self.resultTable + " AS " + query +
" WITH DATA", queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.DM_FLAT, True,
self.qp.columns,
self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
monetdbops.createSQLFile(sqlFileName, query, queryArgs)
result = monetdbops.executeSQLFileCount(self.dbName, sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
self.queryIndex = sIndex
self.queryTable = gridTable
cqp = copy.copy(self.qp)
cqp.queryType = 'generic'
if self.qp.queryType == 'rectangle':
cqp.queryType = 'rectangle'
cqp.statistics = None
cqp.minx = "'||to_char(bbox.sdo_ordinates(1))||'"
cqp.maxx = "'||to_char(bbox.sdo_ordinates(3))||'"
cqp.miny = "'||to_char(bbox.sdo_ordinates(2))||'"
cqp.maxy = "'||to_char(bbox.sdo_ordinates(4))||'"
(query, _) = dbops.getSelect(cqp, self.flatTable,
self.addContainsCondition, self.colsDict)
oracleops.mogrifyExecute(
cursor, """
DECLARE
bbox sdo_geometry;
BEGIN
select sdo_geom_mbr (geom) into bbox from """ + gridTable +
""" where id = """ + str(sIndex) + """;
execute immediate 'INSERT INTO """ + self.resultTable + """ """ + query +
"""';
END;""")
connection.close()
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
postgresops.dropTable(cursor, self.resultTable, True)
if self.numProcessesQuery > 1:
if self.qp.queryType in ('rectangle','circle','generic') :
return self.pythonParallelization()
else:
logging.error('Python parallelization only available for queries which are not NN!')
return (eTime, result)
t0 = time.time()
(query, queryArgs) = dbops.getSelect(self.qp, self.flatTable, self.addContainsCondition, self.DM_FLAT)
if self.qp.queryMethod != 'stream': # disk or stat
postgresops.mogrifyExecute(cursor, "CREATE TABLE " + self.resultTable + " AS ( " + query + " )", queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable, self.DM_FLAT, True, self.qp.columns, self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
postgresops.createSQLFile(cursor, sqlFileName, query, queryArgs)
result = postgresops.executeSQLFileCount(self.getConnectionString(False, True), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
(minx,maxx,miny,maxy) = self.getBBoxGeometry(cursor, gridTable, sIndex+1)
qType = 'generic'
if self.qp.queryType == 'rectangle':
qType = 'rectangle'
self.queryIndex = sIndex+1
self.queryTable = gridTable
cqp = QueryParameters('psql',None,'disk',qType, None, self.qp.columns,None,minx,maxx,miny,maxy,None,None,None,self.qp.minz,self.qp.maxz,None,None,None,None)
(query, queryArgs) = dbops.getSelect(cqp, self.flatTable, self.addContainsCondition, self.DM_FLAT)
postgresops.mogrifyExecute(cursor, "INSERT INTO " + self.resultTable + " " + query, queryArgs)
connection.close()
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, False)
monetdbops.dropTable(cursor, self.resultTable, True)
t0 = time.time()
(query, queryArgs) = dbops.getSelect(self.qp, self.flatTable, self.addContainsCondition, self.DM_FLAT)
if self.qp.queryMethod != 'stream': # disk or stat
monetdbops.mogrifyExecute(cursor, "CREATE TABLE " + self.resultTable + " AS " + query + " WITH DATA", queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable, self.DM_FLAT, True, self.qp.columns, self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
monetdbops.createSQLFile(sqlFileName, query, queryArgs)
result = monetdbops.executeSQLFileCount(self.dbName, sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
postgresops.dropTable(cursor, self.resultTable, True)
if self.numProcessesQuery > 1:
if self.qp.queryType in ('rectangle', 'circle', 'generic'):
return self.pythonParallelization()
else:
logging.error(
'Python parallelization only available for queries which are not NN!'
)
return (eTime, result)
t0 = time.time()
(query, queryArgs) = dbops.getSelect(self.qp, self.flatTable,
self.addContainsCondition,
self.DM_FLAT)
if self.qp.queryMethod != 'stream': # disk or stat
postgresops.mogrifyExecute(
cursor,
"CREATE TABLE " + self.resultTable + " AS ( " + query + " )",
queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.DM_FLAT, True,
self.qp.columns,
self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
postgresops.createSQLFile(cursor, sqlFileName, query, queryArgs)
result = postgresops.executeSQLFileCount(
self.getConnectionString(False, True), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
(minx, maxx, miny,
maxy) = self.getBBoxGeometry(cursor, gridTable, sIndex + 1)
qType = 'generic'
if self.qp.queryType == 'rectangle':
qType = 'rectangle'
self.queryIndex = sIndex + 1
self.queryTable = gridTable
cqp = QueryParameters('psql', None, 'disk', qType, None,
self.qp.columns, None, minx, maxx, miny, maxy,
None, None, None, self.qp.minz, self.qp.maxz,
None, None, None, None)
(query, queryArgs) = dbops.getSelect(cqp, self.flatTable,
self.addContainsCondition,
self.DM_FLAT)
postgresops.mogrifyExecute(
cursor, "INSERT INTO " + self.resultTable + " " + query, queryArgs)
connection.close()
def runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
self.queryIndex = sIndex
self.queryTable = gridTable
cqp = copy.copy(self.qp)
cqp.queryType = 'generic'
if self.qp.queryType == 'rectangle':
cqp.queryType = 'rectangle'
cqp.statistics = None
cqp.minx = "'||to_char(bbox.sdo_ordinates(1))||'"
cqp.maxx = "'||to_char(bbox.sdo_ordinates(3))||'"
cqp.miny = "'||to_char(bbox.sdo_ordinates(2))||'"
cqp.maxy = "'||to_char(bbox.sdo_ordinates(4))||'"
(query, _) = dbops.getSelect(cqp, self.flatTable, self.addContainsCondition, self.colsDict)
oracleops.mogrifyExecute(cursor, """
DECLARE
bbox sdo_geometry;
BEGIN
select sdo_geom_mbr (geom) into bbox from """ + gridTable + """ where id = """ + str(sIndex) + """;
execute immediate 'INSERT INTO """ + self.resultTable + """ """ + query + """';
END;""")
connection.close()