1""" 2This module provides the functionality to create the temporal 3SQL database and to establish a connection to the database. 4 5Usage: 6 7.. code-block:: python 8 9 >>> import grass.temporal as tgis 10 >>> # Create the temporal database 11 >>> tgis.init() 12 >>> # Establish a database connection 13 >>> dbif, connected = tgis.init_dbif(None) 14 >>> dbif.connect() 15 >>> # Execute a SQL statement 16 >>> dbif.execute_transaction("SELECT datetime(0, 'unixepoch', 'localtime');") 17 >>> # Mogrify an SQL statement 18 >>> dbif.mogrify_sql_statement(["SELECT name from raster_base where name = ?", 19 ... ("precipitation",)]) 20 "SELECT name from raster_base where name = 'precipitation'" 21 >>> dbif.close() 22 23 24(C) 2011-2014 by the GRASS Development Team 25This program is free software under the GNU General Public 26License (>=v2). Read the file COPYING that comes with GRASS 27for details. 28 29:author: Soeren Gebbert 30""" 31#import traceback 32import os 33import sys 34import grass.script as gscript 35 36if sys.version_info.major == 3: 37 long = int 38 39from .c_libraries_interface import * 40from grass.pygrass import messages 41from grass.script.utils import decode, encode 42# Import all supported database backends 43# Ignore import errors since they are checked later 44try: 45 import sqlite3 46except ImportError: 47 pass 48# Postgresql is optional, existence is checked when needed 49try: 50 import psycopg2 51 import psycopg2.extras 52except: 53 pass 54 55import atexit 56from datetime import datetime 57 58############################################################################### 59 60 61def profile_function(func): 62 """Profiling function provided by the temporal framework""" 63 do_profiling = os.getenv("GRASS_TGIS_PROFILE") 64 65 if do_profiling == "True" or do_profiling == "1": 66 import cProfile, pstats 67 try: 68 import StringIO as io 69 except ImportError: 70 import io 71 pr = cProfile.Profile() 72 pr.enable() 73 func() 74 pr.disable() 75 s = io.StringIO() 76 sortby = 'cumulative' 77 ps = pstats.Stats(pr, stream=s).sort_stats(sortby) 78 ps.print_stats() 79 print(s.getvalue()) 80 else: 81 func() 82 83# Global variable that defines the backend 84# of the temporal GIS 85# It can either be "sqlite" or "pg" 86tgis_backend = None 87 88 89def get_tgis_backend(): 90 """Return the temporal GIS backend as string 91 92 :returns: either "sqlite" or "pg" 93 """ 94 global tgis_backend 95 return tgis_backend 96 97# Global variable that defines the database string 98# of the temporal GIS 99tgis_database = None 100 101 102def get_tgis_database(): 103 """Return the temporal database string specified with t.connect 104 """ 105 global tgis_database 106 return tgis_database 107 108# The version of the temporal framework 109# this value must be an integer larger than 0 110# Increase this value in case of backward incompatible changes in the TGIS API 111tgis_version = 2 112# The version of the temporal database since framework and database version 113# can differ this value must be an integer larger than 0 114# Increase this value in case of backward incompatible changes 115# temporal database SQL layout 116tgis_db_version = 2 117 118# We need to know the parameter style of the database backend 119tgis_dbmi_paramstyle = None 120 121 122def get_tgis_dbmi_paramstyle(): 123 """Return the temporal database backend parameter style 124 125 :returns: "qmark" or "" 126 """ 127 global tgis_dbmi_paramstyle 128 return tgis_dbmi_paramstyle 129 130# We need to access the current mapset quite often in the framework, so we make 131# a global variable that will be initiated when init() is called 132current_mapset = None 133current_location = None 134current_gisdbase = None 135 136############################################################################### 137 138 139def get_current_mapset(): 140 """Return the current mapset 141 142 This is the fastest way to receive the current mapset. 143 The current mapset is set by init() and stored in a global variable. 144 This function provides access to this global variable. 145 """ 146 global current_mapset 147 return current_mapset 148 149############################################################################### 150 151 152def get_current_location(): 153 """Return the current location 154 155 This is the fastest way to receive the current location. 156 The current location is set by init() and stored in a global variable. 157 This function provides access to this global variable. 158 """ 159 global current_location 160 return current_location 161 162############################################################################### 163 164 165def get_current_gisdbase(): 166 """Return the current gis database (gisdbase) 167 168 This is the fastest way to receive the current gisdbase. 169 The current gisdbase is set by init() and stored in a global variable. 170 This function provides access to this global variable. 171 """ 172 global current_gisdbase 173 return current_gisdbase 174 175############################################################################### 176 177# If this global variable is set True, then maps can only be registered in 178# space time datasets with the same mapset. In addition, only maps in the 179# current mapset can be inserted, updated or deleted from the temporal database. 180# Overwrite this global variable by: g.gisenv set="TGIS_DISABLE_MAPSET_CHECK=True" 181# ATTENTION: Be aware to face corrupted temporal database in case this global 182# variable is set to False. This feature is highly 183# experimental and violates the grass permission guidance. 184enable_mapset_check = True 185# If this global variable is set True, the timestamps of maps will be written 186# as textfiles for each map that will be inserted or updated in the temporal 187# database using the C-library timestamp interface. 188# Overwrite this global variable by: g.gisenv set="TGIS_DISABLE_TIMESTAMP_WRITE=True" 189# ATTENTION: Be aware to face corrupted temporal database in case this global 190# variable is set to False. This feature is highly 191# experimental and violates the grass permission guidance. 192enable_timestamp_write = True 193 194 195def get_enable_mapset_check(): 196 """Return True if the mapsets should be checked while insert, update, 197 delete requests and space time dataset registration. 198 199 If this global variable is set True, then maps can only be registered 200 in space time datasets with the same mapset. In addition, only maps in 201 the current mapset can be inserted, updated or deleted from the temporal 202 database. 203 Overwrite this global variable by: g.gisenv set="TGIS_DISABLE_MAPSET_CHECK=True" 204 205 ..warning:: 206 207 Be aware to face corrupted temporal database in case this 208 global variable is set to False. This feature is highly 209 experimental and violates the grass permission guidance. 210 211 """ 212 global enable_mapset_check 213 return enable_mapset_check 214 215 216def get_enable_timestamp_write(): 217 """Return True if the map timestamps should be written to the spatial 218 database metadata as well. 219 220 If this global variable is set True, the timestamps of maps will be 221 written as textfiles for each map that will be inserted or updated in 222 the temporal database using the C-library timestamp interface. 223 Overwrite this global variable by: g.gisenv set="TGIS_DISABLE_TIMESTAMP_WRITE=True" 224 225 ..warning:: 226 227 Be aware that C-libraries can not access timestamp information if 228 they are not written as spatial database metadata, hence modules 229 that make use of timestamps using the C-library interface will not 230 work with maps that were created without writing the timestamps. 231 """ 232 global enable_timestamp_write 233 return enable_timestamp_write 234 235############################################################################### 236 237# The global variable that stores the PyGRASS Messenger object that 238# provides a fast and exit safe interface to the C-library message functions 239message_interface = None 240 241 242def _init_tgis_message_interface(raise_on_error=False): 243 """Initiate the global message interface 244 245 :param raise_on_error: If True raise a FatalError exception in case of 246 a fatal error, call sys.exit(1) otherwise 247 """ 248 global message_interface 249 if message_interface is None: 250 message_interface = messages.get_msgr(raise_on_error=raise_on_error) 251 252 253def get_tgis_message_interface(): 254 """Return the temporal GIS message interface which is of type 255 grass.pygrass.message.Messenger() 256 257 Use this message interface to print messages to stdout using the 258 GRASS C-library messaging system. 259 """ 260 global message_interface 261 return message_interface 262 263############################################################################### 264 265# The global variable that stores the C-library interface object that 266# provides a fast and exit safe interface to the C-library libgis, 267# libraster, libraster3d and libvector functions 268c_library_interface = None 269 270 271def _init_tgis_c_library_interface(): 272 """Set the global C-library interface variable that 273 provides a fast and exit safe interface to the C-library libgis, 274 libraster, libraster3d and libvector functions 275 """ 276 global c_library_interface 277 if c_library_interface is None: 278 c_library_interface = CLibrariesInterface() 279 280 281def get_tgis_c_library_interface(): 282 """Return the C-library interface that 283 provides a fast and exit safe interface to the C-library libgis, 284 libraster, libraster3d and libvector functions 285 """ 286 global c_library_interface 287 return c_library_interface 288 289############################################################################### 290 291# Set this variable True to raise a FatalError exception 292# in case a fatal error occurs using the messenger interface 293raise_on_error = False 294 295 296def set_raise_on_error(raise_exp=True): 297 """Define behavior on fatal error, invoked using the tgis messenger 298 interface (msgr.fatal()) 299 300 The messenger interface will be restarted using the new error policy 301 302 :param raise_exp: True to raise a FatalError exception instead of calling 303 sys.exit(1) when using the tgis messenger interface 304 305 .. code-block:: python 306 307 >>> import grass.temporal as tgis 308 >>> tgis.init() 309 >>> ignore = tgis.set_raise_on_error(False) 310 >>> msgr = tgis.get_tgis_message_interface() 311 >>> tgis.get_raise_on_error() 312 False 313 >>> msgr.fatal("Ohh no no no!") 314 Traceback (most recent call last): 315 File "__init__.py", line 239, in fatal 316 sys.exit(1) 317 SystemExit: 1 318 319 >>> tgis.set_raise_on_error(True) 320 False 321 >>> msgr.fatal("Ohh no no no!") 322 Traceback (most recent call last): 323 File "__init__.py", line 241, in fatal 324 raise FatalError(message) 325 FatalError: Ohh no no no! 326 327 :returns: current status 328 """ 329 global raise_on_error 330 tmp_raise = raise_on_error 331 raise_on_error = raise_exp 332 333 global message_interface 334 if message_interface: 335 message_interface.set_raise_on_error(raise_on_error) 336 else: 337 _init_tgis_message_interface(raise_on_error) 338 339 return tmp_raise 340 341 342def get_raise_on_error(): 343 """Return True if a FatalError exception is raised instead of calling 344 sys.exit(1) in case a fatal error was invoked with msgr.fatal() 345 """ 346 global raise_on_error 347 return raise_on_error 348 349 350############################################################################### 351 352 353def get_tgis_version(): 354 """Get the version number of the temporal framework 355 :returns: The version number of the temporal framework as string 356 """ 357 global tgis_version 358 return tgis_version 359 360############################################################################### 361 362 363def get_tgis_db_version(): 364 """Get the version number of the temporal framework 365 :returns: The version number of the temporal framework as string 366 """ 367 global tgis_db_version 368 return tgis_db_version 369 370############################################################################### 371 372 373def get_tgis_metadata(dbif=None): 374 """Return the tgis metadata table as a list of rows (dicts) or None if not 375 present 376 377 :param dbif: The database interface to be used 378 :returns: The selected rows with key/value columns or None 379 """ 380 381 dbif, connected = init_dbif(dbif) 382 383 # Select metadata if the table is present 384 try: 385 statement = "SELECT * FROM tgis_metadata;\n" 386 dbif.execute(statement) 387 rows = dbif.fetchall() 388 except: 389 rows = None 390 391 if connected: 392 dbif.close() 393 394 return rows 395 396############################################################################### 397 398# The temporal database string set with t.connect 399# with substituted GRASS variables gisdbase, location and mapset 400tgis_database_string = None 401 402 403def get_tgis_database_string(): 404 """Return the preprocessed temporal database string 405 406 This string is the temporal database string set with t.connect 407 that was processed to substitue location, gisdbase and mapset 408 variables. 409 """ 410 global tgis_database_string 411 return tgis_database_string 412 413############################################################################### 414 415 416def get_sql_template_path(): 417 base = os.getenv("GISBASE") 418 base_etc = os.path.join(base, "etc") 419 return os.path.join(base_etc, "sql") 420 421############################################################################### 422 423 424def stop_subprocesses(): 425 """Stop the messenger and C-interface subprocesses 426 that are started by tgis.init() 427 """ 428 global message_interface 429 global c_library_interface 430 if message_interface: 431 message_interface.stop() 432 if c_library_interface: 433 c_library_interface.stop() 434 435# We register this function to be called at exit 436atexit.register(stop_subprocesses) 437 438 439def get_available_temporal_mapsets(): 440 """Return a list of of mapset names with temporal database driver and names 441 that are accessible from the current mapset. 442 443 :returns: A dictionary, mapset names are keys, the tuple (driver, 444 database) are the values 445 """ 446 global c_library_interface 447 global message_interface 448 449 mapsets = c_library_interface.available_mapsets() 450 451 tgis_mapsets = {} 452 453 for mapset in mapsets: 454 mapset = mapset 455 driver = c_library_interface.get_driver_name(mapset) 456 database = c_library_interface.get_database_name(mapset) 457 458 message_interface.debug(1, "get_available_temporal_mapsets: "\ 459 "\n mapset %s\n driver %s\n database %s"%(mapset, 460 driver, database)) 461 if driver and database: 462 # Check if the temporal sqlite database exists 463 # We need to set non-existing databases in case the mapset is the current mapset 464 # to create it 465 if (driver == "sqlite" and os.path.exists(database)) or mapset == get_current_mapset() : 466 tgis_mapsets[mapset] = (driver, database) 467 468 # We need to warn if the connection is defined but the database does not 469 # exists 470 if driver == "sqlite" and not os.path.exists(database): 471 message_interface.warning("Temporal database connection defined as:\n" + \ 472 database + "\nBut database file does not exist.") 473 return tgis_mapsets 474 475############################################################################### 476 477 478def init(raise_fatal_error=False, skip_db_version_check=False): 479 """This function set the correct database backend from GRASS environmental 480 variables and creates the grass temporal database structure for raster, 481 vector and raster3d maps as well as for the space-time datasets strds, 482 str3ds and stvds in case it does not exist. 483 484 Several global variables are initiated and the messenger and C-library 485 interface subprocesses are spawned. 486 487 Re-run this function in case the following GRASS variables change while 488 the process runs: 489 490 - MAPSET 491 - LOCATION_NAME 492 - GISDBASE 493 - TGIS_DISABLE_MAPSET_CHECK 494 - TGIS_DISABLE_TIMESTAMP_WRITE 495 496 Re-run this function if the following t.connect variables change while 497 the process runs: 498 499 - temporal GIS driver (set by t.connect driver=) 500 - temporal GIS database (set by t.connect database=) 501 502 The following environmental variables are checked: 503 504 - GRASS_TGIS_PROFILE (True, False, 1, 0) 505 - GRASS_TGIS_RAISE_ON_ERROR (True, False, 1, 0) 506 507 ..warning:: 508 509 This functions must be called before any spatio-temporal processing 510 can be started 511 512 :param raise_fatal_error: Set this True to assure that the init() 513 function does not kill a persistent process 514 like the GUI. If set True a 515 grass.pygrass.messages.FatalError 516 exception will be raised in case a fatal 517 error occurs in the init process, otherwise 518 sys.exit(1) will be called. 519 :param skip_db_version_check: Set this True to skip mismatch temporal 520 database version check. 521 Recommended to be used only for 522 downgrade_temporal_database(). 523 """ 524 # We need to set the correct database backend and several global variables 525 # from the GRASS mapset specific environment variables of g.gisenv and t.connect 526 global tgis_backend 527 global tgis_database 528 global tgis_database_string 529 global tgis_dbmi_paramstyle 530 global raise_on_error 531 global enable_mapset_check 532 global enable_timestamp_write 533 global current_mapset 534 global current_location 535 global current_gisdbase 536 537 raise_on_error = raise_fatal_error 538 539 # We must run t.connect at first to create the temporal database and to 540 # get the environmental variables 541 gscript.run_command("t.connect", flags="c") 542 grassenv = gscript.gisenv() 543 544 # Set the global variable for faster access 545 current_mapset = grassenv["MAPSET"] 546 current_location = grassenv["LOCATION_NAME"] 547 current_gisdbase = grassenv["GISDBASE"] 548 549 # Check environment variable GRASS_TGIS_RAISE_ON_ERROR 550 if os.getenv("GRASS_TGIS_RAISE_ON_ERROR") == "True" or \ 551 os.getenv("GRASS_TGIS_RAISE_ON_ERROR") == "1": 552 raise_on_error = True 553 554 # Check if the script library raises on error, 555 # if so we do the same 556 if gscript.get_raise_on_error() is True: 557 raise_on_error = True 558 559 # Start the GRASS message interface server 560 _init_tgis_message_interface(raise_on_error) 561 # Start the C-library interface server 562 _init_tgis_c_library_interface() 563 msgr = get_tgis_message_interface() 564 msgr.debug(1, "Initiate the temporal database") 565 #"\n traceback:%s"%(str(" \n".join(traceback.format_stack())))) 566 567 msgr.debug(1, ("Raise on error id: %s"%str(raise_on_error))) 568 569 ciface = get_tgis_c_library_interface() 570 driver_string = ciface.get_driver_name() 571 database_string = ciface.get_database_name() 572 573 # Set the mapset check and the timestamp write 574 if "TGIS_DISABLE_MAPSET_CHECK" in grassenv: 575 if gscript.encode(grassenv["TGIS_DISABLE_MAPSET_CHECK"]) == "True" or \ 576 gscript.encode(grassenv["TGIS_DISABLE_MAPSET_CHECK"]) == "1": 577 enable_mapset_check = False 578 msgr.warning("TGIS_DISABLE_MAPSET_CHECK is True") 579 580 if "TGIS_DISABLE_TIMESTAMP_WRITE" in grassenv: 581 if gscript.encode(grassenv["TGIS_DISABLE_TIMESTAMP_WRITE"]) == "True" or \ 582 gscript.encode(grassenv["TGIS_DISABLE_TIMESTAMP_WRITE"]) == "1": 583 enable_timestamp_write = False 584 msgr.warning("TGIS_DISABLE_TIMESTAMP_WRITE is True") 585 586 if driver_string is not None and driver_string != "": 587 driver_string = decode(driver_string) 588 if driver_string == "sqlite": 589 tgis_backend = driver_string 590 try: 591 import sqlite3 592 except ImportError: 593 msgr.error("Unable to locate the sqlite SQL Python interface" 594 " module sqlite3.") 595 raise 596 dbmi = sqlite3 597 elif driver_string == "pg": 598 tgis_backend = driver_string 599 try: 600 import psycopg2 601 except ImportError: 602 msgr.error("Unable to locate the Postgresql SQL Python " 603 "interface module psycopg2.") 604 raise 605 dbmi = psycopg2 606 else: 607 msgr.fatal(_("Unable to initialize the temporal DBMI interface. " 608 "Please use t.connect to specify the driver and the" 609 " database string")) 610 else: 611 # Set the default sqlite3 connection in case nothing was defined 612 gscript.run_command("t.connect", flags="d") 613 driver_string = ciface.get_driver_name() 614 database_string = ciface.get_database_name() 615 tgis_backend = driver_string 616 try: 617 import sqlite3 618 except ImportError: 619 msgr.error("Unable to locate the sqlite SQL Python interface" 620 " module sqlite3.") 621 raise 622 dbmi = sqlite3 623 624 tgis_database_string = database_string 625 # Set the parameter style 626 tgis_dbmi_paramstyle = dbmi.paramstyle 627 628 # We do not know if the database already exists 629 db_exists = False 630 dbif = SQLDatabaseInterfaceConnection() 631 632 # Check if the database already exists 633 if tgis_backend == "sqlite": 634 # Check path of the sqlite database 635 if os.path.exists(tgis_database_string): 636 dbif.connect() 637 # Check for raster_base table 638 dbif.execute("SELECT name FROM sqlite_master WHERE type='table' " 639 "AND name='raster_base';") 640 name = dbif.fetchone() 641 if name and name[0] == "raster_base": 642 db_exists = True 643 dbif.close() 644 elif tgis_backend == "pg": 645 # Connect to database 646 dbif.connect() 647 # Check for raster_base table 648 dbif.execute("SELECT EXISTS(SELECT * FROM information_schema.tables " 649 "WHERE table_name=%s)", ('raster_base',)) 650 if dbif.fetchone()[0]: 651 db_exists = True 652 653 backup_howto = "The format of your actual temporal database is not " \ 654 "supported any more.\nSolution: You need to export it by " \ 655 "restoring the GRASS GIS version used for creating this DB"\ 656 ". From there, create a backup of your temporal database "\ 657 "to avoid the loss of your temporal data.\nNotes: Use " \ 658 "t.rast.export and t.vect.export to make a backup of your" \ 659 " existing space time datasets.To safe the timestamps of" \ 660 " your existing maps and space time datasets, use " \ 661 "t.rast.list, t.vect.list and t.rast3d.list. "\ 662 "You can register the existing time stamped maps easily if"\ 663 " you export columns=id,start_time,end_time into text "\ 664 "files and use t.register to register them again in new" \ 665 " created space time datasets (t.create). After the backup"\ 666 " remove the existing temporal database, a new one will be"\ 667 " created automatically.\n" 668 669 if db_exists is True: 670 dbif.close() 671 if skip_db_version_check is True: 672 return 673 674 # Check the version of the temporal database 675 dbif.connect() 676 metadata = get_tgis_metadata(dbif) 677 dbif.close() 678 if metadata is None: 679 msgr.fatal(_("Unable to receive temporal database metadata.\n" 680 "Current temporal database info:%(info)s") % ( 681 {"info": get_database_info_string()})) 682 for entry in metadata: 683 if "tgis_version" in entry and entry[1] != str(get_tgis_version()): 684 msgr.fatal(_("Unsupported temporal database: version mismatch." 685 "\n %(backup)s Supported temporal API version is:" 686 " %(api)i.\nPlease update your GRASS GIS " 687 "installation.\nCurrent temporal database info:" 688 "%(info)s") % ({"backup": backup_howto, 689 "api": get_tgis_version(), 690 "info": get_database_info_string()})) 691 if "tgis_db_version" in entry and entry[1] != str(get_tgis_db_version()): 692 msgr.fatal(_("Unsupported temporal database: version mismatch." 693 "\n %(backup)sSupported temporal database version" 694 " is: %(tdb)i\nCurrent temporal database info:" 695 "%(info)s") % ({"backup": backup_howto, 696 "tdb": get_tgis_version(), 697 "info": get_database_info_string()})) 698 return 699 700 create_temporal_database(dbif) 701 702############################################################################### 703 704 705def get_database_info_string(): 706 dbif = SQLDatabaseInterfaceConnection() 707 708 info = "\nDBMI interface:..... " + str(dbif.get_dbmi().__name__) 709 info += "\nTemporal database:.. " + str(get_tgis_database_string()) 710 return info 711 712############################################################################### 713 714 715def _create_temporal_database_views(dbif): 716 """Create all views in the temporal database (internal use only) 717 718 Used by downgrade_temporal_database(). 719 720 :param dbif: The database interface to be used 721 """ 722 template_path = get_sql_template_path() 723 724 for sql_filename in ( 725 "raster_views", 726 "raster3d_views", 727 "vector_views", 728 "strds_views", 729 "str3ds_views", 730 "stvds_views", 731 ): 732 sql_filepath = open( 733 os.path.join(template_path, sql_filename + ".sql"), "r" 734 ).read() 735 dbif.execute_transaction(sql_filepath) 736 737 738def create_temporal_database(dbif): 739 """This function will create the temporal database 740 741 It will create all tables and triggers that are needed to run 742 the temporal GIS 743 744 :param dbif: The database interface to be used 745 """ 746 global tgis_backend 747 global tgis_version 748 global tgis_db_version 749 global tgis_database_string 750 751 template_path = get_sql_template_path() 752 msgr = get_tgis_message_interface() 753 754 # Read all SQL scripts and templates 755 map_tables_template_sql = open(os.path.join( 756 template_path, "map_tables_template.sql"), 'r').read() 757 raster_metadata_sql = open(os.path.join( 758 get_sql_template_path(), "raster_metadata_table.sql"), 'r').read() 759 raster3d_metadata_sql = open(os.path.join(template_path, 760 "raster3d_metadata_table.sql"), 761 'r').read() 762 vector_metadata_sql = open(os.path.join(template_path, 763 "vector_metadata_table.sql"), 764 'r').read() 765 raster_views_sql = open(os.path.join(template_path, "raster_views.sql"), 766 'r').read() 767 raster3d_views_sql = open(os.path.join(template_path, 768 "raster3d_views.sql"), 'r').read() 769 vector_views_sql = open(os.path.join(template_path, "vector_views.sql"), 770 'r').read() 771 772 stds_tables_template_sql = open(os.path.join(template_path, 773 "stds_tables_template.sql"), 774 'r').read() 775 strds_metadata_sql = open(os.path.join(template_path, 776 "strds_metadata_table.sql"), 777 'r').read() 778 str3ds_metadata_sql = open(os.path.join(template_path, 779 "str3ds_metadata_table.sql"), 780 'r').read() 781 stvds_metadata_sql = open(os.path.join(template_path, 782 "stvds_metadata_table.sql"), 783 'r').read() 784 strds_views_sql = open(os.path.join(template_path, "strds_views.sql"), 785 'r').read() 786 str3ds_views_sql = open(os.path.join(template_path, "str3ds_views.sql"), 787 'r').read() 788 stvds_views_sql = open(os.path.join(template_path, "stvds_views.sql"), 789 'r').read() 790 791 # Create the raster, raster3d and vector tables SQL statements 792 raster_tables_sql = map_tables_template_sql.replace("GRASS_MAP", "raster") 793 vector_tables_sql = map_tables_template_sql.replace("GRASS_MAP", "vector") 794 raster3d_tables_sql = map_tables_template_sql.replace( 795 "GRASS_MAP", "raster3d") 796 797 # Create the space-time raster, raster3d and vector dataset tables 798 # SQL statements 799 strds_tables_sql = stds_tables_template_sql.replace("STDS", "strds") 800 stvds_tables_sql = stds_tables_template_sql.replace("STDS", "stvds") 801 str3ds_tables_sql = stds_tables_template_sql.replace("STDS", "str3ds") 802 803 msgr.message(_("Creating temporal database: %s" % (str(tgis_database_string)))) 804 805 if tgis_backend == "sqlite": 806 # We need to create the sqlite3 database path if it does not exist 807 tgis_dir = os.path.dirname(tgis_database_string) 808 if not os.path.exists(tgis_dir): 809 try: 810 os.makedirs(tgis_dir) 811 except Exception as e: 812 msgr.fatal(_("Unable to create SQLite temporal database\n" 813 "Exception: %s\nPlease use t.connect to set a " 814 "read- and writable temporal database path" % (e))) 815 816 # Set up the trigger that takes care of 817 # the correct deletion of entries across the different tables 818 delete_trigger_sql = open(os.path.join(template_path, 819 "sqlite3_delete_trigger.sql"), 820 'r').read() 821 indexes_sql = open(os.path.join(template_path, "sqlite3_indexes.sql"), 822 'r').read() 823 else: 824 # Set up the trigger that takes care of 825 # the correct deletion of entries across the different tables 826 delete_trigger_sql = open(os.path.join(template_path, 827 "postgresql_delete_trigger.sql"), 828 'r').read() 829 indexes_sql = open(os.path.join(template_path, 830 "postgresql_indexes.sql"), 'r').read() 831 832 # Connect now to the database 833 if dbif.connected is not True: 834 dbif.connect() 835 836 # Execute the SQL statements for sqlite 837 # Create the global tables for the native grass datatypes 838 dbif.execute_transaction(raster_tables_sql) 839 dbif.execute_transaction(raster_metadata_sql) 840 dbif.execute_transaction(raster_views_sql) 841 dbif.execute_transaction(vector_tables_sql) 842 dbif.execute_transaction(vector_metadata_sql) 843 dbif.execute_transaction(vector_views_sql) 844 dbif.execute_transaction(raster3d_tables_sql) 845 dbif.execute_transaction(raster3d_metadata_sql) 846 dbif.execute_transaction(raster3d_views_sql) 847 # Create the tables for the new space-time datatypes 848 dbif.execute_transaction(strds_tables_sql) 849 dbif.execute_transaction(strds_metadata_sql) 850 dbif.execute_transaction(strds_views_sql) 851 dbif.execute_transaction(stvds_tables_sql) 852 dbif.execute_transaction(stvds_metadata_sql) 853 dbif.execute_transaction(stvds_views_sql) 854 dbif.execute_transaction(str3ds_tables_sql) 855 dbif.execute_transaction(str3ds_metadata_sql) 856 dbif.execute_transaction(str3ds_views_sql) 857 858 # The delete trigger 859 dbif.execute_transaction(delete_trigger_sql) 860 # The indexes 861 dbif.execute_transaction(indexes_sql) 862 863 # Create the tgis metadata table to store the database 864 # initial configuration 865 # The metadata table content 866 metadata = {} 867 metadata["tgis_version"] = tgis_version 868 metadata["tgis_db_version"] = tgis_db_version 869 metadata["creation_time"] = datetime.today() 870 _create_tgis_metadata_table(metadata, dbif) 871 872 dbif.close() 873 874############################################################################### 875 876 877def downgrade_temporal_database(dbif): 878 """This function will downgrade the temporal database if needed. 879 880 It will downdate all tables and triggers that are requested by 881 currently supported TGIS DB version. 882 883 :param dbif: The database interface to be used 884 """ 885 global tgis_database_string 886 global tgis_db_version 887 888 metadata = get_tgis_metadata(dbif) 889 890 msgr = get_tgis_message_interface() 891 if metadata is None: 892 msgr.fatal( 893 _( 894 "Unable to receive temporal database metadata.\n" 895 "Current temporal database info:%(info)s" 896 ) 897 % ({"info": get_database_info_string()}) 898 ) 899 downgrade_db_from = None 900 for entry in metadata: 901 if "tgis_db_version" in entry and entry[1] != str(tgis_db_version): 902 downgrade_db_from = entry[1] 903 break 904 905 if downgrade_db_from is None: 906 msgr.message(_("Temporal database is up-to-date. Operation canceled")) 907 dbif.close() 908 return 909 910 template_path = get_sql_template_path() 911 try: 912 downgrade_db_sql = open( 913 os.path.join( 914 template_path, 915 "downgrade_db_%s_to_%s.sql" % (downgrade_db_from, tgis_db_version), 916 ), 917 "r", 918 ).read() 919 except FileNotFoundError: 920 msgr.fatal( 921 _("Unsupported TGIS DB downgrade scenario: from version %s to %s") 922 % (downgrade_db_from, tgis_db_version) 923 ) 924 925 drop_views_sql = open(os.path.join(template_path, "drop_views.sql"), "r").read() 926 927 msgr.message( 928 _("Downgrading temporal database <%s> from version %s to %s...") 929 % (tgis_database_string, downgrade_db_from, tgis_db_version) 930 ) 931 # Drop views 932 dbif.execute_transaction(drop_views_sql) 933 # Perform upgrade 934 dbif.execute_transaction(downgrade_db_sql) 935 # Recreate views 936 _create_temporal_database_views(dbif) 937 938 dbif.close() 939 940 941############################################################################### 942 943 944def _create_tgis_metadata_table(content, dbif=None): 945 """!Create the temporal gis metadata table which stores all metadata 946 information about the temporal database. 947 948 :param content: The dictionary that stores the key:value metadata 949 that should be stored in the metadata table 950 :param dbif: The database interface to be used 951 """ 952 dbif, connected = init_dbif(dbif) 953 statement = "CREATE TABLE tgis_metadata (key VARCHAR NOT NULL, value VARCHAR);\n"; 954 dbif.execute_transaction(statement) 955 956 for key in content.keys(): 957 statement = "INSERT INTO tgis_metadata (key, value) VALUES " + \ 958 "(\'%s\' , \'%s\');\n" % (str(key), str(content[key])) 959 dbif.execute_transaction(statement) 960 961 if connected: 962 dbif.close() 963 964############################################################################### 965 966 967class SQLDatabaseInterfaceConnection(object): 968 def __init__(self): 969 self.tgis_mapsets = get_available_temporal_mapsets() 970 self.current_mapset = get_current_mapset() 971 self.connections = {} 972 self.connected = False 973 974 self.unique_connections = {} 975 976 for mapset in self.tgis_mapsets.keys(): 977 driver, dbstring = self.tgis_mapsets[mapset] 978 979 if dbstring not in self.unique_connections.keys(): 980 self.unique_connections[dbstring] = DBConnection(backend=driver, 981 dbstring=dbstring) 982 983 self.connections[mapset] = self.unique_connections[dbstring] 984 985 self.msgr = get_tgis_message_interface() 986 987 def get_dbmi(self, mapset=None): 988 if mapset is None: 989 mapset = self.current_mapset 990 991 mapset = decode(mapset) 992 return self.connections[mapset].dbmi 993 994 def rollback(self, mapset=None): 995 """ 996 Roll back the last transaction. This must be called 997 in case a new query should be performed after a db error. 998 999 This is only relevant for postgresql database. 1000 """ 1001 if mapset is None: 1002 mapset = self.current_mapset 1003 1004 def connect(self): 1005 """Connect to the DBMI to execute SQL statements 1006 1007 Supported backends are sqlite3 and postgresql 1008 """ 1009 for mapset in self.tgis_mapsets.keys(): 1010 driver, dbstring = self.tgis_mapsets[mapset] 1011 conn = self.connections[mapset] 1012 if conn.is_connected() is False: 1013 conn.connect(dbstring) 1014 1015 self.connected = True 1016 1017 def is_connected(self): 1018 return self.connected 1019 1020 def close(self): 1021 """Close the DBMI connection 1022 1023 There may be several temporal databases in a location, hence 1024 close all temporal databases that have been opened. 1025 """ 1026 for key in self.unique_connections.keys(): 1027 self.unique_connections[key].close() 1028 1029 self.connected = False 1030 1031 def mogrify_sql_statement(self, content, mapset=None): 1032 """Return the SQL statement and arguments as executable SQL string 1033 1034 :param content: The content as tuple with two entries, the first 1035 entry is the SQL statement with DBMI specific 1036 place holder (?), the second entry is the argument 1037 list that should substitute the place holder. 1038 :param mapset: The mapset of the abstract dataset or temporal 1039 database location, if None the current mapset 1040 will be used 1041 """ 1042 if mapset is None: 1043 mapset = self.current_mapset 1044 1045 mapset = decode(mapset) 1046 if mapset not in self.tgis_mapsets.keys(): 1047 self.msgr.fatal(_("Unable to mogrify sql statement. " + 1048 self._create_mapset_error_message(mapset))) 1049 1050 return self.connections[mapset].mogrify_sql_statement(content) 1051 1052 def check_table(self, table_name, mapset=None): 1053 """Check if a table exists in the temporal database 1054 1055 :param table_name: The name of the table to be checked for existence 1056 :param mapset: The mapset of the abstract dataset or temporal 1057 database location, if None the current mapset 1058 will be used 1059 :returns: True if the table exists, False otherwise 1060 1061 TODO: 1062 There may be several temporal databases in a location, hence 1063 the mapset is used to query the correct temporal database. 1064 """ 1065 if mapset is None: 1066 mapset = self.current_mapset 1067 1068 mapset = decode(mapset) 1069 if mapset not in self.tgis_mapsets.keys(): 1070 self.msgr.fatal(_("Unable to check table. " + 1071 self._create_mapset_error_message(mapset))) 1072 1073 return self.connections[mapset].check_table(table_name) 1074 1075 def execute(self, statement, args=None, mapset=None): 1076 """ 1077 1078 :param mapset: The mapset of the abstract dataset or temporal 1079 database location, if None the current mapset 1080 will be used 1081 """ 1082 if mapset is None: 1083 mapset = self.current_mapset 1084 1085 mapset = decode(mapset) 1086 if mapset not in self.tgis_mapsets.keys(): 1087 self.msgr.fatal(_("Unable to execute sql statement. " + 1088 self._create_mapset_error_message(mapset))) 1089 1090 return self.connections[mapset].execute(statement, args) 1091 1092 def fetchone(self, mapset=None): 1093 if mapset is None: 1094 mapset = self.current_mapset 1095 1096 mapset = decode(mapset) 1097 if mapset not in self.tgis_mapsets.keys(): 1098 self.msgr.fatal(_("Unable to fetch one. " + 1099 self._create_mapset_error_message(mapset))) 1100 1101 return self.connections[mapset].fetchone() 1102 1103 def fetchall(self, mapset=None): 1104 if mapset is None: 1105 mapset = self.current_mapset 1106 1107 mapset = decode(mapset) 1108 if mapset not in self.tgis_mapsets.keys(): 1109 self.msgr.fatal(_("Unable to fetch all. " + 1110 self._create_mapset_error_message(mapset))) 1111 1112 return self.connections[mapset].fetchall() 1113 1114 def execute_transaction(self, statement, mapset=None): 1115 """Execute a transactional SQL statement 1116 1117 The BEGIN and END TRANSACTION statements will be added automatically 1118 to the sql statement 1119 1120 :param statement: The executable SQL statement or SQL script 1121 """ 1122 if mapset is None: 1123 mapset = self.current_mapset 1124 1125 mapset = decode(mapset) 1126 if mapset not in self.tgis_mapsets.keys(): 1127 self.msgr.fatal(_("Unable to execute transaction. " + 1128 self._create_mapset_error_message(mapset))) 1129 1130 return self.connections[mapset].execute_transaction(statement) 1131 1132 def _create_mapset_error_message(self, mapset): 1133 1134 return("You have no permission to " 1135 "access mapset <%(mapset)s>, or " 1136 "mapset <%(mapset)s> has no temporal database. " 1137 "Accessible mapsets are: <%(mapsets)s>" % \ 1138 {"mapset": decode(mapset), 1139 "mapsets":','.join(self.tgis_mapsets.keys())}) 1140 1141############################################################################### 1142 1143 1144class DBConnection(object): 1145 """This class represents the database interface connection 1146 and provides access to the chosen backend modules. 1147 1148 The following DBMS are supported: 1149 1150 - sqlite via the sqlite3 standard library 1151 - postgresql via psycopg2 1152 """ 1153 1154 def __init__(self, backend=None, dbstring=None): 1155 """ Constructor of a database connection 1156 1157 param backend:The database backend sqlite or pg 1158 param dbstring: The database connection string 1159 """ 1160 self.connected = False 1161 if backend is None: 1162 global tgis_backend 1163 if decode(tgis_backend) == "sqlite": 1164 self.dbmi = sqlite3 1165 else: 1166 self.dbmi = psycopg2 1167 else: 1168 if decode(backend) == "sqlite": 1169 self.dbmi = sqlite3 1170 else: 1171 self.dbmi = psycopg2 1172 1173 if dbstring is None: 1174 global tgis_database_string 1175 self.dbstring = tgis_database_string 1176 1177 self.dbstring = dbstring 1178 1179 self.msgr = get_tgis_message_interface() 1180 self.msgr.debug(1, "DBConnection constructor:"\ 1181 "\n backend: %s"\ 1182 "\n dbstring: %s"%(backend, self.dbstring)) 1183 #"\n traceback:%s"%(backend, self.dbstring, 1184 #str(" \n".join(traceback.format_stack())))) 1185 1186 def __del__(self): 1187 if self.connected is True: 1188 self.close() 1189 1190 def is_connected(self): 1191 return self.connected 1192 1193 def rollback(self): 1194 """ 1195 Roll back the last transaction. This must be called 1196 in case a new query should be performed after a db error. 1197 1198 This is only relevant for postgresql database. 1199 """ 1200 if self.dbmi.__name__ == "psycopg2": 1201 if self.connected: 1202 self.connection.rollback() 1203 1204 def connect(self, dbstring=None): 1205 """Connect to the DBMI to execute SQL statements 1206 1207 Supported backends are sqlite3 and postgresql 1208 1209 param dbstring: The database connection string 1210 """ 1211 # Connection in the current mapset 1212 if dbstring is None: 1213 dbstring = self.dbstring 1214 1215 dbstring = decode(dbstring) 1216 1217 try: 1218 if self.dbmi.__name__ == "sqlite3": 1219 self.connection = self.dbmi.connect(dbstring, 1220 detect_types=self.dbmi.PARSE_DECLTYPES | self.dbmi.PARSE_COLNAMES) 1221 self.connection.row_factory = self.dbmi.Row 1222 self.connection.isolation_level = None 1223 self.connection.text_factory = str 1224 self.cursor = self.connection.cursor() 1225 self.cursor.execute("PRAGMA synchronous = OFF") 1226 self.cursor.execute("PRAGMA journal_mode = MEMORY") 1227 elif self.dbmi.__name__ == "psycopg2": 1228 self.connection = self.dbmi.connect(dbstring) 1229 #self.connection.set_isolation_level(dbmi.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 1230 self.cursor = self.connection.cursor( 1231 cursor_factory=self.dbmi.extras.DictCursor) 1232 self.connected = True 1233 except Exception as e: 1234 self.msgr.fatal(_("Unable to connect to %(db)s database: " 1235 "%(string)s\nException: \"%(ex)s\"\nPlease use" 1236 " t.connect to set a read- and writable " 1237 "temporal database backend") % ( 1238 {"db": self.dbmi.__name__, 1239 "string": tgis_database_string, "ex": e, })) 1240 1241 def close(self): 1242 """Close the DBMI connection 1243 TODO: 1244 There may be several temporal databases in a location, hence 1245 close all temporal databases that have been opened. Use a dictionary 1246 to manage different connections. 1247 """ 1248 self.connection.commit() 1249 self.cursor.close() 1250 self.connected = False 1251 1252 def mogrify_sql_statement(self, content): 1253 """Return the SQL statement and arguments as executable SQL string 1254 1255 TODO: 1256 Use the mapset argument to identify the correct database driver 1257 1258 :param content: The content as tuple with two entries, the first 1259 entry is the SQL statement with DBMI specific 1260 place holder (?), the second entry is the argument 1261 list that should substitute the place holder. 1262 :param mapset: The mapset of the abstract dataset or temporal 1263 database location, if None the current mapset 1264 will be used 1265 1266 Usage: 1267 1268 .. code-block:: python 1269 1270 >>> init() 1271 >>> dbif = SQLDatabaseInterfaceConnection() 1272 >>> dbif.mogrify_sql_statement(["SELECT ctime FROM raster_base WHERE id = ?", 1273 ... ["soil@PERMANENT",]]) 1274 "SELECT ctime FROM raster_base WHERE id = 'soil@PERMANENT'" 1275 1276 """ 1277 sql = content[0] 1278 args = content[1] 1279 1280 if self.dbmi.__name__ == "psycopg2": 1281 if len(args) == 0: 1282 return sql 1283 else: 1284 if self.connected: 1285 try: 1286 return self.cursor.mogrify(sql, args) 1287 except Exception as exc: 1288 print(sql, args) 1289 raise exc 1290 else: 1291 self.connect() 1292 statement = self.cursor.mogrify(sql, args) 1293 self.close() 1294 return statement 1295 1296 elif self.dbmi.__name__ == "sqlite3": 1297 if len(args) == 0: 1298 return sql 1299 else: 1300 # Unfortunately as sqlite does not support 1301 # the transformation of sql strings and qmarked or 1302 # named arguments we must make our hands dirty 1303 # and do it by ourself. :( 1304 # Doors are open for SQL injection because of the 1305 # limited python sqlite3 implementation!!! 1306 pos = 0 1307 count = 0 1308 maxcount = 100 1309 statement = sql 1310 1311 while count < maxcount: 1312 pos = statement.find("?", pos + 1) 1313 if pos == -1: 1314 break 1315 1316 if args[count] is None: 1317 statement = "%sNULL%s" % (statement[0:pos], 1318 statement[pos + 1:]) 1319 elif isinstance(args[count], (int, long)): 1320 statement = "%s%d%s" % (statement[0:pos], args[count], 1321 statement[pos + 1:]) 1322 elif isinstance(args[count], float): 1323 statement = "%s%f%s" % (statement[0:pos], args[count], 1324 statement[pos + 1:]) 1325 elif isinstance(args[count], datetime): 1326 statement = "%s\'%s\'%s" % (statement[0:pos], str(args[count]), 1327 statement[pos + 1:]) 1328 else: 1329 # Default is a string, this works for datetime 1330 # objects too 1331 statement = "%s\'%s\'%s" % (statement[0:pos], 1332 str(args[count]), 1333 statement[pos + 1:]) 1334 count += 1 1335 1336 return statement 1337 1338 def check_table(self, table_name): 1339 """Check if a table exists in the temporal database 1340 1341 :param table_name: The name of the table to be checked for existence 1342 :param mapset: The mapset of the abstract dataset or temporal 1343 database location, if None the current mapset 1344 will be used 1345 :returns: True if the table exists, False otherwise 1346 1347 TODO: 1348 There may be several temporal databases in a location, hence 1349 the mapset is used to query the correct temporal database. 1350 """ 1351 table_exists = False 1352 connected = False 1353 if not self.connected: 1354 self.connect() 1355 connected = True 1356 1357 # Check if the database already exists 1358 if self.dbmi.__name__ == "sqlite3": 1359 1360 self.cursor.execute("SELECT name FROM sqlite_master WHERE " 1361 "type='table' AND name='%s';" % table_name) 1362 name = self.cursor.fetchone() 1363 if name and name[0] == table_name: 1364 table_exists = True 1365 else: 1366 # Check for raster_base table 1367 self.cursor.execute("SELECT EXISTS(SELECT * FROM information_schema.tables " 1368 "WHERE table_name=%s)", ('%s' % table_name,)) 1369 if self.cursor.fetchone()[0]: 1370 table_exists = True 1371 1372 if connected: 1373 self.close() 1374 1375 return table_exists 1376 1377 def execute(self, statement, args=None): 1378 """Execute a SQL statement 1379 1380 :param statement: The executable SQL statement or SQL script 1381 """ 1382 connected = False 1383 if not self.connected: 1384 self.connect() 1385 connected = True 1386 try: 1387 if args: 1388 self.cursor.execute(statement, args) 1389 else: 1390 self.cursor.execute(statement) 1391 except: 1392 if connected: 1393 self.close() 1394 self.msgr.error(_("Unable to execute :\n %(sql)s" % 1395 {"sql": statement})) 1396 raise 1397 1398 if connected: 1399 self.close() 1400 1401 def fetchone(self): 1402 if self.connected: 1403 return self.cursor.fetchone() 1404 return None 1405 1406 def fetchall(self): 1407 if self.connected: 1408 return self.cursor.fetchall() 1409 return None 1410 1411 def execute_transaction(self, statement, mapset=None): 1412 """Execute a transactional SQL statement 1413 1414 The BEGIN and END TRANSACTION statements will be added automatically 1415 to the sql statement 1416 1417 :param statement: The executable SQL statement or SQL script 1418 """ 1419 connected = False 1420 if not self.connected: 1421 self.connect() 1422 connected = True 1423 1424 sql_script = "" 1425 sql_script += "BEGIN TRANSACTION;\n" 1426 sql_script += statement 1427 sql_script += "END TRANSACTION;" 1428 1429 try: 1430 if self.dbmi.__name__ == "sqlite3": 1431 self.cursor.executescript(statement) 1432 else: 1433 self.cursor.execute(statement) 1434 self.connection.commit() 1435 except: 1436 if connected: 1437 self.close() 1438 self.msgr.error(_("Unable to execute transaction:\n %(sql)s" % 1439 {"sql": statement})) 1440 raise 1441 1442 if connected: 1443 self.close() 1444 1445############################################################################### 1446 1447 1448def init_dbif(dbif): 1449 """This method checks if the database interface connection exists, 1450 if not a new one will be created, connected and True will be returned. 1451 If the database interface exists but is connected, the connection will 1452 be established. 1453 1454 :returns: the tuple (dbif, True|False) 1455 1456 Usage code sample: 1457 1458 .. code-block:: python 1459 1460 dbif, connect = tgis.init_dbif(None) 1461 1462 sql = dbif.mogrify_sql_statement(["SELECT * FROM raster_base WHERE ? = ?"], 1463 ["id", "soil@PERMANENT"]) 1464 dbif.execute_transaction(sql) 1465 1466 if connect: 1467 dbif.close() 1468 1469 """ 1470 if dbif is None: 1471 dbif = SQLDatabaseInterfaceConnection() 1472 dbif.connect() 1473 return dbif, True 1474 elif dbif.is_connected() is False: 1475 dbif.connect() 1476 return dbif, True 1477 1478 return dbif, False 1479 1480############################################################################### 1481 1482if __name__ == "__main__": 1483 import doctest 1484 doctest.testmod() 1485