GRASS DataBase Management Interface

by GRASS Development Team

https://grass.osgeo.org

Introduction

The attribute management was completely changed in GRASS 6 to achieve multi-attribute capabilities managed within external databases. The former dig_cats/ files (GRASS 5) are not used any more and all vector attributes are stored in external database. Connection with database is done through DBMI library (DataBase Management Interface) with its integrated drivers. At time of this writing following DBMI drivers for attribute storage are available:

• DBF: xBase files (default)
• ODBC: to interface from https://www.unixodbc.org
• PostgreSQL driver (note that PostgreSQL can also be accessed through ODBC): https://www.postgresql.org
• mySQL: http://mysql.com/
• SQLite: https://www.sqlite.org

These drivers are compiled depending on present DB related libraries and 'configure' settings. Only the DBF driver is always compiled. The default DBMI driver is defined in dbmi.h

#define DB_DEFAULT_DRIVER "sqlite"

In general records in table are linked to vector entities by field number and category number. The field number identifies a database table and the category number identifies the record record. I.e. for unique combination mapset + map + field + category exists one unique combination driver + database + table + row. Each element may have none, one or more categories (cats). More cats are distinguished by field number (field). The flexibility of this approach even supports the extreme case, that one vector entity may be linked to attributes in different tables in different databases connected by different drivers at the same time.

The DBMI library contains drivers such as DBF, SQLite, ODBC, MySQL and PostgreSQL. The functionality of the database support varies with the capabilities of the underlying RDBMS. Main features are multi-attributes support for various data types, also multiple tables may optionally be linked to one or many vector entity/ies. SQL (Structured Query Language) is used for all drivers, but in a limited implementation.

For DBMI architecture details please read Blazek et al. 2002 (see below).

DBMI library functions

The db_*() functions are the programmer's API for GRASS DBMI programming. There are three groups:

• dbmi_base: contains functions for modules, drivers (../../db/drivers/)
• dbmi_client: contains functions for modules
• dbmi_driver: contains functions for drivers (../../db/drivers/)

DBMI BASE functions

Allocation:

• db_store()
• db_malloc()
• db_calloc()
• db_realloc()
• db_free()

String manipulation:

• db_char_to_lowercase()
• db_char_to_uppercase()
• db_Cstring_to_lowercase()
• db_Cstring_to_uppercase()
• db_nocase_compare()

Column properties:

• db_get_column_value()
• db_get_column_default_value()
• db_set_column_sqltype()
• db_set_column_host_type()
• db_get_column_scale()
• db_set_column_scale()
• db_get_column_precision()
• db_set_column_precision()
• db_get_column_sqltype()
• db_get_column_host_type()
• db_set_column_has_defined_default_value()
• db_set_column_has_undefined_default_value()
• db_unset_column_has_default_value()
• db_test_column_has_default_value()
• db_test_column_has_defined_default_value()
• db_test_column_has_undefined_default_value()
• db_set_column_use_default_value()
• db_unset_column_use_default_value()
• db_test_column_use_default_value()
• db_set_column_null_allowed()
• db_unset_column_null_allowed()
• db_test_column_null_allowed()
• db_get_column_length()
• db_set_column_length()
• db_set_column_select_priv_granted()
• db_set_column_select_priv_not_granted()
• db_get_column_select_priv()
• db_set_column_update_priv_granted()
• db_set_column_update_priv_not_granted()
• db_get_column_update_priv()
• db_init_column()
• db_set_column_name()
• db_get_column_name()
• db_set_column_description()
• db_get_column_description()
• db_free_column()
• db_copy_column()
• db_convert_Cstring_to_column_value()
• db_convert_Cstring_to_column_default_value()
• db_convert_column_value_to_string()
• db_convert_column_default_value_to_string()

Connection settings:

• db_set_connection()
• db_get_connection()

Cursor management:

• db_init_cursor()
• db_alloc_cursor_table()
• db_free_cursor()
• db_get_cursor_table()
• db_set_cursor_table()
• db_get_cursor_token()
• db_set_cursor_token()
• db_set_cursor_type_readonly()
• db_set_cursor_type_update()
• db_set_cursor_type_insert()
• db_test_cursor_type_fetch()
• db_test_cursor_type_update()
• db_test_cursor_type_insert()
• db_set_cursor_mode()
• db_set_cursor_mode_scroll()
• db_unset_cursor_mode_scroll()
• db_unset_cursor_mode()
• db_set_cursor_mode_insensitive()
• db_unset_cursor_mode_insensitive()
• db_test_cursor_mode_scroll()
• db_test_cursor_mode_insensitive()
• db_alloc_cursor_column_flags()
• db_free_cursor_column_flags()
• db_set_cursor_column_for_update()
• db_unset_cursor_column_for_update()
• db_test_cursor_column_for_update()
• db_test_cursor_any_column_for_update()
• db_set_cursor_column_flag()
• db_unset_cursor_column_flag()
• db_test_cursor_column_flag()
• db_get_cursor_number_of_columns()
• db_test_cursor_any_column_flag()

DateTime conversion:

• db_convert_value_datetime_into_string()
• db_convert_Cstring_to_value_datetime()

DBmscap management:

• db_dbmscap_filename()
• db_has_dbms()
• db_copy_dbmscap_entry()
• db_read_dbmscap()
• db_free_dbmscap()

Default settings:

• db_get_default_driver_name()
• db_get_default_database_name()
• db_get_default_schema_name()
• db_get_default_group_name()
• db_set_default_connection()

Dirent (directory entities) array:

• db_dirent()
• db_free_dirent_array()
• db_alloc_dirent_array()

Report errors:

• db_on_error()
• db_set_error_who()
• db_get_error_who()
• db_error()
• db_protocol_error()
• db_syserror()
• db_get_error_code()
• db_memory_error()
• db_procedure_not_implemented()
• db_noproc_error()
• db_clear_error()
• db_print_error()
• db_debug_on()
• db_debug_off()
• db_debug()
• db_get_error_msg()
• db_auto_print_errors()
• db_auto_print_protocol_errors()

Handle management:

• db_init_handle()
• db_set_handle()
• db_get_handle_dbname()
• db_get_handle_dbschema()
• db_free_handle()
• db_free_handle_array()
• db_alloc_handle_array()

Index management:

• db_init_index()
• db_free_index()
• db_alloc_index_columns()
• db_alloc_index_array()
• db_free_index_array()
• db_set_index_name()
• db_get_index_name()
• db_set_index_table_name()
• db_get_index_table_name()
• db_get_index_number_of_columns()
• db_set_index_column_name()
• db_get_index_column_name()
• db_set_index_type_unique()
• db_set_index_type_non_unique()
• db_test_index_type_unique()
• db_print_index()

Range functions:

• db_interval_range()

Login functions:

• db_set_login()
• db_get_login()

Return codes (internal use only):

• db__send_success()
• db__send_failure()
• db__recv_return_code()

Data type conversion:

• db_sqltype_to_Ctype()
• db_sqltype_name()

String manipulation:

• db_init_string()
• db_set_string()
• db_set_string_no_copy()
• db_sizeof_string()
• db_zero_string()
• db_enlarge_string()
• db_get_string()
• db_free_string()
• db_free_string_array()
• db_alloc_string_array()
• db_append_string()
• db_copy_string()
• db_double_quote_string()

Table/privileges management:

• db_alloc_table()
• db_init_table()
• db_free_table()
• db_set_table_name()
• db_get_table_name()
• db_set_table_description()
• db_get_table_description()
• db_get_table_number_of_columns()
• db_set_table_select_priv_granted()
• db_set_table_select_priv_not_granted()
• db_get_table_select_priv()
• db_set_table_update_priv_granted()
• db_set_table_update_priv_not_granted()
• db_get_table_update_priv()
• db_set_table_insert_priv_granted()
• db_set_table_insert_priv_not_granted()
• db_get_table_insert_priv()
• db_set_table_delete_priv_granted()
• db_set_table_delete_priv_not_granted()
• db_get_table_delete_priv()
• db_get_table_column()
• db_get_table_column_by_name()
• db_set_table_column()
• db_append_table_column()
• db_clone_table()
• db_table_to_sql()

Token management:

• db_find_token()
• db_drop_token()
• db_new_token()

Value management:

• db_test_value_isnull()
• db_get_value_int()
• db_get_value_double()
• db_get_value_as_double()
• db_get_value_string()
• db_get_value_year()
• db_get_value_month()
• db_get_value_day()
• db_get_value_hour()
• db_get_value_minute()
• db_get_value_seconds()
• db_set_value_null()
• db_set_value_not_null()
• db_set_value_int()
• db_set_value_double()
• db_set_value_string()
• db_set_value_year()
• db_set_value_month()
• db_set_value_day()
• db_set_value_hour()
• db_set_value_minute()
• db_set_value_seconds()
• db_test_value_datetime_current()
• db_set_value_datetime_current()
• db_set_value_datetime_not_current()
• db_copy_value()
• db_CatValArray_init()
• db_CatValArray_free()
• db_CatValArray_alloc()
• db_CatValArray_realloc()
• db_convert_Cstring_to_value()
• db_convert_value_to_string()

Misc:

• db_isdir()
• db_legal_tablename()
• db_whoami

DBMI CLIENT functions

• db_add_column()
• db_begin_transaction()
• db_bind_update()
• db_CatValArray_get_value()
• db_CatValArray_get_value_double()
• db_CatValArray_get_value_int()
• db_CatValArray_sort()
• db_CatValArray_sort_by_value()
• db_close_cursor()
• db_close_database()
• db_close_database_shutdown_driver()
• db_column_Ctype()
• db_column_sqltype()
• db_commit_transaction()
• db_copy_table()
• db_copy_table_where()
• db_copy_table_select()
• db_copy_table_by_ints()
• db_create_database()
• db_create_index()
• db_create_table()
• db_delete()
• db_delete_database()
• db_delete_table()
• db_describe_table()
• db_drop_column()
• db_drop_index()
• db_drop_table()
• db_execute_immediate()
• db_fetch()
• db_find_database()
• db_get_column()
• db_get_num_rows()
• db_get_table_number_of_rows()
• db_grant_on_table()
• db_gversion()
• db_insert()
• db_list_databases()
• db_list_drivers()
• db_list_indexes()
• db_list_tables()
• db_open_database()
• db_open_insert_cursor()
• db_open_select_cursor()
• db_open_update_cursor()
• db_print_column_definition()
• db_print_table_definition()
• db_start_driver_open_database()
• db_select_CatValArray()
• db_select_int()
• db_select_value()
• db_shutdown_driver()
• db_start_driver()
• db_table_exists()
• db_update()

DBMI DRIVER functions

• db_driver()
• db_driver_mkdir()
• db_d_add_column()
• db_d_append_error()
• db_d_begin_transaction()
• db_d_bind_update()
• db_d_close_cursor()
• db_d_close_database()
• db_d_commit_transaction()
• db_d_create_database()
• db_d_create_index()
• db_d_create_table()
• db_d_delete()
• db_d_delete_database()
• db_d_describe_table()
• db_d_drop_column()
• db_d_drop_index()
• db_d_drop_table()
• db_d_execute_immediate()
• db_d_fetch()
• db_d_find_database()
• db_d_get_num_rows()
• db_driver_mkdir()
• db_d_grant_on_table()
• db_d_init_error()
• db_d_insert()
• db_d_list_databases()
• db_d_list_indexes()
• db_d_list_tables()
• db_d_open_database()
• db_d_open_insert_cursor()
• db_d_open_select_cursor()
• db_d_open_update_cursor()
• db_d_report_error()
• db_d_update()

References

Text based on: R. Blazek, M. Neteler, and R. Micarelli. The new GRASS 5.1 vector architecture. In Open source GIS - GRASS users conference 2002, Trento, Italy, 11-13 September 2002. University of Trento, Italy, 2002. http://www.ing.unitn.it/~grass/proceedings/proceedings/pdfs/Blazek_Radim.pdf

See Also

GRASS Vector Architecture: GRASS Vector Library

Authors

• Joel Jones (CERL/UIUC)
• Radim Blazek