class ref name: dbtable
category-group: sql
layer: 5
header file: z_dbtable.h

synopsis.
The dbtable_o class manages database tables and is important for several reasons. It sits between 2 important classes - underneath the orthodox object and above dbbi_o. Further below are dbconnection_o and the [layer 3] database subroutines. This class manages many database tables that are not "orthodox" objects. One dbtable object manages one database table. Included in this class is a "geographical" data set - real-world topology data. This data includes:

  • continent - a short list of continents (name and code).
  • country
  • airport
  • state
  • phone_code
  • area_code
  • post_code (in USA: zip codes)
  • school_code
  • school
  • language_code
  • first_name

In September 2012, much of the existing geo-topo data was replaced with the geonames dataset (from geonames.org). Several of the tables are included with the installer. However, some of the larger table data is not, due to its enormous size (close to 1 GB).

The dbtable object can automatically create and load-populate these tables using the static function create_alltables() . There is an option to do so when you install the Z Directory. The tables can be loaded into the database of your choosing. This function takes a long time to complete - possibly hours. It is probably the longest-running function in the Z Directory. Since it emits no messages while it is working, it should be used with extreme caution.

description.
This class exists for Z Directory internal purposes and has very little utility for your applications. It does little more than set up the aforementioned data. You cannot fetch individual rows with the dbtable object - use dbbi_o for that.

member functions (primary)

dbtable_o()
SIGNATURE: dbtable_o ()
SYNOPSIS:
creates a a new database table object instance. Using the default constructor, the dbtable object is not bound to any table.
 

dbtable_o(<args>)
SIGNATURE: dbtable_o (const string_o &s, boolean b = FALSE)
SYNOPSIS:
Create a new dbtable object based on the contents of string object 's'. This constructor expects 's' to be either the name of an existing table, or a table schema in recursive databag format - which is determined by the 2nd input parameter, 'b'.
PARAMETERS

  • s: string containing either a "standard" table name or a table schema.
  • b: if TRUE, parameter 's' must be a simple database table name. The corresponding table must exist in a currently opened database in order for this call to succeed. If FALSE, the contents of 's' must be a valid database schema.
 

name()
SIGNATURE: string_o name (int *pi = NULL) const
SYNOPSIS: returns the name of the dbtable object.
 

zdo_autocreate_dbtables()
SIGNATURE: int zdo_autocreate_dbtables (boolean = TRUE)
SYNOPSIS:
This function allows the application whether to create any of the "standard" tables automatically. Internally, it simply turns on or off a global flag.
TRAITS: this is a static member function. Currently auto-create is not implemnted.
 

zdont_autocreate_dbtables()
SIGNATURE: int zdont_autocreate_dbtables ()
SYNOPSIS: This is a "convenience function", it is exactly equivalent to zdo_autocreate_dbtables(FALSE).
TRAITS: this is a static member function.
 

set_column_delimeter()
SIGNATURE: int set_column_delimeter (const char ch)
SYNOPSIS:
During a load(), column values in the [text] load file are separated by a delimiter character. By default, this is a pipe-symbol ('|'). This may be changed via this function. Any calls to load() after calling this function will employ the new delimiter character. The only restriction is that the character must be a printable character.
PARAMETERS

  • ch: the character to use for delimiting columns (eg separating fields in a row). Any printable character is permitted.
  • RETURNS:
    0: character set-changed
    -1: error; 'ch' is not a "printable" character
    TRAITS: this is a static member function.
     

    create()
    SIGNATURE: int create (int *pi = NULL)
    SYNOPSIS:
    creates a table in the current database for the current object instance. The dbtable object must have been initialized to a specific database table. This is done by the dbtable_o(string_o&) constructor variant, that is, using a constructor with a table name (or table schema) argument.
     

    create()
    SIGNATURE: int create (const string_o &s, int *pi = NULL)
    SYNOPSIS: creates a table in the database whose table name is that of parameter 's'.
     

    load()
    SIGNATURE: int load (boolean force, int *pi = NULL)
    SYNOPSIS:
    Loads the table given by the current object's name. If the current object is not bound to a specific table (which occurs if the default constructor was used), this routine will fail and will return -1.
    PARAMETERS

    • force: if TRUE, any existing rows will be deleted. The default is FALSE. if FALSE, if there is any data in the table (at least 1 row), then the load will be aborted and the existing data will remain intact.
    • pi: [output variable] error indicator flag. values:
      0: success, load done
      zErr_Item_NotFound: can't open [.unl topology] data file
      2: wrong data file format
      3: can't add data to table
      zErr_NotFound: table does not exist
      zErr_DonePrior: table has existing data
    RETURNS:
    0: table successfully loaded
    1: table already has data (not an error; "warning only")
    -1: error occured; see return value, in 'pi'.
     

    load()
    SIGNATURE: int load (const string_o &table_name, boolean force, int *pi = NULL)
    SYNOPSIS: This member function loads data into the database table specified by 'table_name'.
    PARAMETERS

    • table_name: the name of the table to load
    • force: if TRUE, any existing rows will be deleted. The default is FALSE. if FALSE, if there is any data in the table (at least 1 row), then the load will be aborted and the existing data will remain intact.
    • pi: [output variable] error indicator flag. values:
    DESCRIPTION:
    This routine assumes that there are ".unl", or "unload" files (more aptly called "load files") in a directory set by the static member function 'set_datadir()'. The .unl files are in a syntax originally used by the informix database. The basic structure is a text file containing 1 line per database row. The fields are separated by pipe ("|") symbols. The file name extnsion is .unl and the base file name must match the name of the database table. Thus, if the table is 'xxx', then the .unl file must be named 'xxx.unl'.
    TRAITS:
    This is a static function. There is no "current object" that applies to this function. call it like so:
    boolean stomp = TRUE;
    string_o tabname("MYTABLE");
    int ie = dbtable_o::load(tabname, stomp);
    

     

    set_datadir()
    SIGNATURE: int set_datadir (const string_o &s)
    SYNOPSIS: Set the directory path for the load load files.
    RETURNS: 0 (always)
    TRAITS: this is a static member function.
     

    is_std_dbtable()
    SIGNATURE: boolean is_std_dbtable (const string_o &nam, int *pi = NULL)
    SYNOPSIS: This function tells if 'nam' is a valid, standard DB table.
    PARAMETERS

    • nam: [input] the name of a DB table to check
    • pi: [output] error indicator variable. values:
      0: table name found
      1: found; also, the htable is a code table
      zErr_NotFound: not found; 'nam' is not a DB table
    RETURNS:
    TRUE: 'nam' is a valid DB table
    FALSE: 'nam' is not a valid DB table
     

    get_std_dbtable_schema()
    SIGNATURE: string_o get_std_dbtable_schema (const string_o &s, int *pi)
    SYNOPSIS:
    This function goes thru the list of strings containing schema descriptions of standard Z Directory "geo" tables. The dbtable_o entries are in a static, master global list. If found, the corresponding schema is returned, in recursive databag format, put into a string object. if not found, output parameter 'pi' is set to non-zero, and the value of the returned string object is undefined (actually it "points to" the string object "bad value" instance).
    PARAMETERS

    • s: string containing either a "standard" table name
    • pi: [output] error indicator variable. values:
      0: table name found
      zErr_NotFound: table name not found
    TRAITS: this is a static member function.
     

    set_dbtable_with_schema()
    SIGNATURE: int set_dbtable_with_schema (const string_o &s, dbbi_o &deb, int *pi)
    SYNOPSIS:
    fetches a table schema from the list of known table schemas, and sets it into the given dbbi_o parameter 'deb' (a "debbie" object).
    TRAITS: this is a static member function.
     

    create_tables()
    SIGNATURE: int create_tables (const string_o &tabfile, boolean do_load, boolean do_stomp, boolean quit_onerr, int *pi)
    SYNOPSIS:
    This function create tables from the entries in a file specified by 'tabfile'. This file must contain databags that describe tables.
    PARAMETERS

    • tabfile: string containing the file name to open for processing. This string may include the path compoenent.
    • do_load: if TRUE, the table is populated. The data source must be available in order for this to work.
    • do_stomp: if TRUE, and if the table exists in the database, the existing table will be over-written. That is, the table will be dropped, and a new, empty table by the same name created, based on the schema description found in the file.
    • pi: [output] error indicator variable. values:
      0: all ok; every table successfully created
      [n>0]: # tables that failed to get created
    DESCRIPTION:
    this function provides a convenient way to load multiple tables into a database. The database where the tables are to be created must have been opened prior. Whether to over-write any existing tables can be controlled by the second parameter, 'do_stomp'.
    If an error occurs, processing does not stop. For example, if the file contains entries for tables A, B, and C, and the schema description for B is mangled, tables A and C will be created.
    TRAITS: this is a static member function.
     

    create_table()
    SIGNATURE: int create_table (const string_o &name, boolean do_load, boolean do_stomp, int *pi)
    SYNOPSIS: This function create a single Z Directory table. The table name is specified by the first function parameter, 'name'.
    PARAMETERS

    • name: string providing the database table name to create.
    • do_load: if TRUE, the table is populated. The data source must be available in order for this to work.
    • do_stomp: if TRUE, and if the table exists in the database, the existing table will be over-written. That is, the table will be dropped, and a new, empty table by the same name created, based on the schema description found in the file.
    • pi: [output] error indicator variable.
     

    create_alltables()
    SIGNATURE: int create_alltables (boolean = FALSE, boolean = TRUE, int *pi = NULL)
    TRAITS: this is a static member function.
     

    bad_reference()
    SIGNATURE: const dbtable_o &bad_reference ()
    SYNOPSIS:
    returns a global, officially designated "bad" instance of this class. It is used for cases where an object reference return value is required, but there is corresponding valid instance.
    TRAITS: this is a static member function.
     

    examples.
    [example forthcoming]

        [this line is intentionally blank]
    

    history.

    ??? 08/17/1996: resumed development of SQL tables (in Woodstock)
    Sun 08/18/1996: added "type", to differentiate states, territories, ..
    ??? 04/25/1997: changed all "char(1)" -> "char(2)"
    ??? 12/03/1997: added "owner' & 'owner_tabid' to sub-tables
    ??? 03/21/1998: code tables clustered, standardized biz table re-worked
    ??? 04/06/1998: expanding size of all char fields by 1
    ??? 06/09/1998: overhauling exam test questions: multi-language support
    ??? 07/03/1998: broke off area/geography stuff from 'hhu_create' file
    ??? 07/04/1998: broke off of file 'hhu_create'
    Mon 11/23/1998: 02:46am: bug fix, "z_guess_sex_byfirstname()"
    ??? 06/17/2002: added airport codes
    Tue 06/25/2002: created dbtables class [--AG]
    Tue 08/20/2002: changed dbtable_o::create_tables(), from "db_init()"
    ??? 09/06/2002: renamed, from 'geography.sql'; clean-up of SQL files
    Sat 08/23/2003: cast clean-up {--GeG}: ppos = -1; -> ppos = (size_t) -1
    Sat 04/28/2012: major overhaul begun
    Tue 06/25/2002: dbtable .cpp file started [-AG]
    Tue 10/08/2002: add 'is_equipment', 'is_code' -> dbtable databag [--AG]
    Sat 04/28/2012: starting repair project to auto-create tables [--GeG]
    ??? 08/14/2002: changed function name, "table_create()" -> "create()"
    Tue 08/21/2012: add "create()"; loads of bug fixes [10 years later]
    Sun 09/16/2012: continent fields merged, for "geonames.org"
    Tue 09/18/2012: changed 'country.continent' "INT" -> "CHAR(3)", etc.