| 22 |
import weakref |
import weakref |
| 23 |
from sqlite import connect |
from sqlite import connect |
| 24 |
|
|
| 25 |
|
from base import TitledObject |
| 26 |
|
|
| 27 |
import table |
import table |
| 28 |
|
|
| 29 |
sql_type_map = { |
sql_type_map = { |
| 163 |
return self.column_map.has_key(col) |
return self.column_map.has_key(col) |
| 164 |
|
|
| 165 |
def ReadRowAsDict(self, index): |
def ReadRowAsDict(self, index): |
| 166 |
|
# Implementation Strategy: Executing a completely new select |
| 167 |
|
# statement every time this method is called is too slow. The |
| 168 |
|
# most important usage is to read the records more or less |
| 169 |
|
# sequentially. This happens e.g. when drawing a layer with a |
| 170 |
|
# classification where the shapes are drawn in order of the |
| 171 |
|
# shape ids. Another pattern is that the same row is requested |
| 172 |
|
# several times in a row. This happens in the table view, for |
| 173 |
|
# instance. |
| 174 |
|
|
| 175 |
|
# We can exploit this to make access faster by having one cursor |
| 176 |
|
# open all the time and keeping the last row read around in case |
| 177 |
|
# the same row is accessed again the next time and if the row |
| 178 |
|
# index is larger than the row we have read last we simply fetch |
| 179 |
|
# rows from the cursor until we've reached the requested row. If |
| 180 |
|
# the requested row index is smaller then we start a new cursor. |
| 181 |
|
|
| 182 |
|
# FIXME: So far this scheme seems to work well enough. Obvious |
| 183 |
|
# improvements would be to start the cursor at exactly the |
| 184 |
|
# requested row (should be efficient and easy to do now that the |
| 185 |
|
# id is the primary key) and to perhaps to also start a new |
| 186 |
|
# cursor if the requested index is much larger than the last row |
| 187 |
|
# so that we don't read and discard lots of the rows. |
| 188 |
|
|
| 189 |
|
# Check whether we have to start a new cursor |
| 190 |
if self.read_record_cursor is None or index <self.read_record_last_row: |
if self.read_record_cursor is None or index <self.read_record_last_row: |
| 191 |
stmt = ("SELECT %s FROM %s;" |
stmt = ("SELECT %s FROM %s;" |
| 192 |
% (", ".join([c.internal_name for c in self.columns]), |
% (", ".join([c.internal_name for c in self.columns]), |
| 210 |
self.read_record_last_row = index |
self.read_record_last_row = index |
| 211 |
return dict(zip(self.orig_names, result)) |
return dict(zip(self.orig_names, result)) |
| 212 |
|
|
| 213 |
|
def ReadValue(self, row, col): |
| 214 |
|
"""Return the value of the specified row and column |
| 215 |
|
|
| 216 |
|
The col parameter may be the index of the column or its name. |
| 217 |
|
""" |
| 218 |
|
# Depending on the actual access patterns of the table data, it |
| 219 |
|
# might be a bit faster in some circumstances to not implement |
| 220 |
|
# this via ReadRowAsDict, but this simple implementation should |
| 221 |
|
# be fast enough for most purposes. |
| 222 |
|
return self.ReadRowAsDict(row)[self.column_map[col].name] |
| 223 |
|
|
| 224 |
def ValueRange(self, col): |
def ValueRange(self, col): |
| 225 |
col = self.column_map[col] |
col = self.column_map[col] |
| 226 |
iname = col.internal_name |
iname = col.internal_name |
| 242 |
result.append(row[0]) |
result.append(row[0]) |
| 243 |
return result |
return result |
| 244 |
|
|
| 245 |
|
def Width(self, col): |
| 246 |
|
"""Return the maximum width of values in the column |
| 247 |
|
|
| 248 |
|
The return value is the the maximum length of string representation |
| 249 |
|
of the values in the column (represented by index or name).""" |
| 250 |
|
max = 0 |
| 251 |
|
|
| 252 |
|
type = self.column_map[col].type |
| 253 |
|
iname = self.column_map[col].internal_name |
| 254 |
|
cursor = self.db.cursor() |
| 255 |
|
cursor.execute("SELECT %s FROM %s;" % (iname, self.tablename)) |
| 256 |
|
values = [ i[0] for i in cursor.fetchall()] |
| 257 |
|
if not values: |
| 258 |
|
return None |
| 259 |
|
|
| 260 |
|
if type == sql_type_map[table.FIELDTYPE_DOUBLE]: |
| 261 |
|
format = "%.12f" |
| 262 |
|
elif type == sql_type_map[table.FIELDTYPE_INT]: |
| 263 |
|
format = "%d" |
| 264 |
|
else: |
| 265 |
|
format = "%s" |
| 266 |
|
for value in values: |
| 267 |
|
if value is None: continue |
| 268 |
|
l = len(format % value) |
| 269 |
|
if l > max: |
| 270 |
|
max = l |
| 271 |
|
|
| 272 |
|
return max |
| 273 |
|
|
| 274 |
def SimpleQuery(self, left, comparison, right): |
def SimpleQuery(self, left, comparison, right): |
| 275 |
"""Return the indices of all rows that matching a condition. |
"""Return the indices of all rows that matching a condition. |
| 276 |
|
|
| 311 |
result.append(row[0]) |
result.append(row[0]) |
| 312 |
return result |
return result |
| 313 |
|
|
| 314 |
|
def Dependencies(self): |
| 315 |
|
"""Placeholder for a method in a derived class. |
| 316 |
|
|
| 317 |
|
Return a sequence with the tables and other data objects that |
| 318 |
|
self depends on. |
| 319 |
|
""" |
| 320 |
|
raise NotImplementedError |
| 321 |
|
|
| 322 |
|
|
| 323 |
class TransientTable(TransientTableBase): |
class TransientTable(TitledObject, TransientTableBase): |
| 324 |
|
|
| 325 |
"""A Table in a transient DB that starts as the copy of a Thuban Table.""" |
"""A Table in a transient DB that starts as the copy of a Thuban Table.""" |
| 326 |
|
|
| 331 |
interface. |
interface. |
| 332 |
""" |
""" |
| 333 |
TransientTableBase.__init__(self, transient_db) |
TransientTableBase.__init__(self, transient_db) |
| 334 |
|
TitledObject.__init__(self, table.Title()) |
| 335 |
self.create(table) |
self.create(table) |
| 336 |
|
|
| 337 |
def create(self, table): |
def create(self, table): |
| 365 |
|
|
| 366 |
|
|
| 367 |
|
|
| 368 |
class TransientJoinedTable(TransientTableBase): |
class TransientJoinedTable(TitledObject, TransientTableBase): |
| 369 |
|
|
| 370 |
"""A Table in the transient DB that contains a join of two tables""" |
"""A Table in the transient DB that contains a join of two tables""" |
| 371 |
|
|
| 372 |
def __init__(self, transient_db, left_table, left_field, |
def __init__(self, transient_db, left_table, left_field, |
| 373 |
right_table, right_field = None): |
right_table, right_field = None, outer_join = False): |
| 374 |
"""Create a new table in the transient DB as a join of two tables. |
"""Create a new table in the transient DB as a join of two tables. |
| 375 |
|
|
| 376 |
Both input tables, left_table and right_table must have a |
Both input tables, left_table and right_table must have a |
| 377 |
transient_table method that returns a table object for a table |
transient_table method that returns a table object for a table |
| 378 |
in the trnsient database. The join is performed on the condition |
in the transient database. The join is performed on the condition |
| 379 |
that the value of the left_field column the the left table is |
that the value of the left_field column the the left table is |
| 380 |
equal to the value of the right_field in the right_table. |
equal to the value of the right_field in the right_table. |
| 381 |
|
|
| 387 |
names of the input tables without having to create prefixes. |
names of the input tables without having to create prefixes. |
| 388 |
""" |
""" |
| 389 |
TransientTableBase.__init__(self, transient_db) |
TransientTableBase.__init__(self, transient_db) |
| 390 |
|
self.dependencies = (left_table, right_table) |
| 391 |
self.left_table = left_table.transient_table() |
self.left_table = left_table.transient_table() |
| 392 |
self.left_field = left_field |
self.left_field = left_field |
| 393 |
self.right_table = right_table.transient_table() |
self.right_table = right_table.transient_table() |
| 395 |
self.right_field = right_field |
self.right_field = right_field |
| 396 |
else: |
else: |
| 397 |
self.right_field = self.left_field |
self.right_field = self.left_field |
| 398 |
|
self.outer_join = outer_join |
| 399 |
|
|
| 400 |
|
title = "Join of %(left)s and %(right)s" \ |
| 401 |
|
% {"left": self.left_table.Title(), |
| 402 |
|
"right": self.right_table.Title()} |
| 403 |
|
TitledObject.__init__(self, title) |
| 404 |
|
|
| 405 |
self.create() |
self.create() |
| 406 |
|
|
| 407 |
def create(self): |
def create(self): |
| 410 |
|
|
| 411 |
self.right_table.ensure_index(self.right_field) |
self.right_table.ensure_index(self.right_field) |
| 412 |
|
|
| 413 |
|
# determine the internal column names to join on before |
| 414 |
|
# coalescing the column information because if the external |
| 415 |
|
# column names are the same they will be mapped to the same |
| 416 |
|
# internal name afterwards. |
| 417 |
|
internal_left_col = self.left_table.orig_to_internal[self.left_field] |
| 418 |
|
internal_right_col = self.right_table.orig_to_internal[self.right_field] |
| 419 |
|
|
| 420 |
# Coalesce the column information |
# Coalesce the column information |
| 421 |
visited = {} |
visited = {} |
| 422 |
columns = [] |
columns = [] |
| 423 |
for col in self.left_table.columns + self.right_table.columns: |
newcolumns = [] |
| 424 |
|
for table, col in ( |
| 425 |
|
[ (self.left_table.tablename, c) for c in self.left_table.columns ] |
| 426 |
|
+ [ (self.right_table.tablename, c) for c in self.right_table.columns]): |
| 427 |
if col.name in visited: |
if col.name in visited: |
| 428 |
# We can't allow multiple columns with the same original |
if col.name == self.left_field: |
| 429 |
# name, so omit this one. FIXME: There should be a |
continue |
| 430 |
# better solution. |
else: |
| 431 |
continue |
# We can't allow multiple columns with the same original |
| 432 |
columns.append(col) |
# name, so append '_' to this one until it is unique. |
| 433 |
TransientTableBase.create(self, columns) |
# FIXME: There should be a better solution. |
| 434 |
|
while col.name in visited: |
| 435 |
|
col.name = col.name + '_' |
| 436 |
|
columns.append((table, col)) |
| 437 |
|
newcol = ColumnReference(col.name, col.type, |
| 438 |
|
"Col%03d" % (len(newcolumns)+1)) |
| 439 |
|
newcolumns.append(newcol) |
| 440 |
|
visited[col.name] = 1 |
| 441 |
|
TransientTableBase.create(self, newcolumns) |
| 442 |
|
|
| 443 |
# Copy the joined data to the table. |
# Copy the joined data to the table. |
| 444 |
internal_names = [col.internal_name for col in self.columns] |
newinternal_names = [col.internal_name for col in self.columns] |
| 445 |
|
internal_references = ["%s.%s" % (table, col.internal_name) |
| 446 |
|
for table, col in columns] |
| 447 |
|
if self.outer_join: |
| 448 |
|
join_operator = 'LEFT OUTER JOIN' |
| 449 |
|
else: |
| 450 |
|
join_operator = 'JOIN' |
| 451 |
stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s" |
stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s" |
| 452 |
" JOIN %s ON %s = %s;" |
" %s %s ON %s.%s = %s.%s;" |
| 453 |
% (self.tablename, |
% (self.tablename, |
| 454 |
", ".join(internal_names), |
", ".join(newinternal_names), |
| 455 |
self.left_table.tablename, |
self.left_table.tablename, |
| 456 |
", ".join(internal_names), |
", ".join(internal_references), |
| 457 |
self.left_table.tablename, |
self.left_table.tablename, |
| 458 |
|
join_operator, |
| 459 |
self.right_table.tablename, |
self.right_table.tablename, |
| 460 |
self.orig_to_internal[self.left_field], |
self.left_table.tablename, |
| 461 |
self.orig_to_internal[self.right_field])) |
internal_left_col, |
| 462 |
|
self.right_table.tablename, |
| 463 |
|
internal_right_col)) |
| 464 |
self.db.execute(stmt) |
self.db.execute(stmt) |
| 465 |
|
|
| 466 |
|
def Dependencies(self): |
| 467 |
|
"""Return a tuple with the two tables the join depends on.""" |
| 468 |
|
return self.dependencies |
| 469 |
|
|
| 470 |
|
|
| 471 |
class AutoTransientTable(table.OldTableInterfaceMixin): |
class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin): |
| 472 |
|
|
| 473 |
"""Table that copies data to a transient table on demand. |
"""Table that copies data to a transient table on demand. |
| 474 |
|
|
| 477 |
""" |
""" |
| 478 |
|
|
| 479 |
def __init__(self, transient_db, table): |
def __init__(self, transient_db, table): |
| 480 |
|
TitledObject.__init__(self, table.Title()) |
| 481 |
self.transient_db = transient_db |
self.transient_db = transient_db |
| 482 |
self.table = table |
self.table = table |
| 483 |
self.t_table = None |
self.t_table = None |
| 507 |
else: |
else: |
| 508 |
return self.table.ReadRowAsDict(record) |
return self.table.ReadRowAsDict(record) |
| 509 |
|
|
| 510 |
|
def ReadValue(self, row, col): |
| 511 |
|
"""Return the value of the specified row and column |
| 512 |
|
|
| 513 |
|
The col parameter may be the index of the column or its name. |
| 514 |
|
""" |
| 515 |
|
if self.t_table is not None: |
| 516 |
|
return self.t_table.ReadValue(row, col) |
| 517 |
|
else: |
| 518 |
|
return self.table.ReadValue(row, col) |
| 519 |
|
|
| 520 |
def copy_to_transient(self): |
def copy_to_transient(self): |
| 521 |
"""Internal: Create a transient table and copy the data into it""" |
"""Internal: Create a transient table and copy the data into it""" |
| 522 |
self.t_table = TransientTable(self.transient_db, self) |
self.t_table = TransientTable(self.transient_db, self) |
| 538 |
if self.t_table is None: |
if self.t_table is None: |
| 539 |
self.copy_to_transient() |
self.copy_to_transient() |
| 540 |
return self.t_table.UniqueValues(col) |
return self.t_table.UniqueValues(col) |
| 541 |
|
|
| 542 |
|
def SimpleQuery(self, left, comparison, right): |
| 543 |
|
if self.t_table is None: |
| 544 |
|
self.copy_to_transient() |
| 545 |
|
# Make sure to use the column object of the transient table. The |
| 546 |
|
# left argument is always a column object so we can just ask the |
| 547 |
|
# t_table for the right object. |
| 548 |
|
if hasattr(right, "name"): |
| 549 |
|
return self.t_table.SimpleQuery(self.t_table.Column(left.name), |
| 550 |
|
comparison, |
| 551 |
|
self.t_table.Column(right.name)) |
| 552 |
|
else: |
| 553 |
|
return self.t_table.SimpleQuery(self.t_table.Column(left.name), |
| 554 |
|
comparison, right) |
| 555 |
|
|
| 556 |
|
def Dependencies(self): |
| 557 |
|
"""Return a tuple containing the original table""" |
| 558 |
|
return (self.table,) |
| 559 |
|
|
| 560 |
|
def Width(self, col): |
| 561 |
|
return self.table.Width(col) |
Parent Directory
| 
Revision Log
| 
Patch