| pgv_families|All the families in the GEDCOM|
|
| pgv_favorites|Stores users favorites|bitweaver user prefs
|
| pgv_individuals|All the individuals in the GEDCOM|
|
| +pgv_link|Links between gedcom records| |
| +pgv_media|List of media references from gedcom data|These will be stored using LibertyMime |
| +pgv_media_mapping|Links to gedcom records using media items| |
| pgv_messages|Messages to and from users|bitweaver messaging
|
| +pgv_mutex|Database managed lock on file updates| |
| pgv_names|Stores decoded name information from GEDCOM records|
|
| pgv_news|Stores news items for the Index and Portal pages|various other packages
|
| +pgv_nextid|Table of id numbers fore adding records to gedcom data|This is not multi-user safe! |
| pgv_other|All other level 0 GEDCOM records (i.e., repositories, media objects, notes, etc.)|
|
| pgv_placelinks|Cross-reference between places and individuals and families|
|
| pgv_places|Place hierarchy|
|
| +pgv_remotelinks|Table of urls used within gedcom data| |
| +pgv_site_settings|System settings data|These are stored in kernel_config |
| pgv_sources|All the sources in the GEDCOM|
|
| -pgv_users|Table for user data (only exists if using default mysql authentication module)|BitUser||
|
| +pgv_users|Table for user data (replaced by users_users)|BitUser|| |
|
|
| +More details can also be found in the [http://wiki.phpgedview.net/|phpgedview wiki] ++red:( links will be created when their wiki is working again )++ |
|
|
| -The tables are all very similar. They each have a field for the GEDCOM ID, a field to tell which GEDCOM file the record was imported from, a few fields for things like quick retrieval of name information, and a field for the raw GEDCOM record data.
|
| +!Core data tables |
| +The data tables are all very similar. They each have a field for the GEDCOM ID, a field to tell which GEDCOM file the record was imported from, a few fields for things like quick retrieval of name information, and a field for the raw GEDCOM record data. The entire original gedcom data is stored across these six tables. |
|
|
| !!pgv_individuals
|
| - i_id VARCHAR(255) # GEDCOM individual ID
|
| - i_file INT # ID number of the GEDCOM file the record is from
|
| - i_rin VARCHAR(30) # Individual's RIN number
|
| - i_name VARCHAR(255) # Person's primary name taken from the first
|
| - # 1 NAME line stored in GEDCOM name format
|
| - i_isdead int(1) # Alive/dead status of individual
|
| - # -1 = not calculated yet 0 = alive 1 = dead
|
| - i_GEDCOM TEXT # Raw GEDCOM record for this individual
|
| - i_letter VARCHAR(5) # First letter of the individual's surname
|
| - i_surname VARCHAR(100) # Person's surname
|
| +||Field|Size|Notes |
| +i_id|VARCHAR(20)|GEDCOM individual ID |
| +i_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +i_rin|VARCHAR(20)|Individual's RIN number |
| +i_isdead|INT|Alive/dead status of individual -1 = not calculated yet 0 = alive 1 = dead |
| +i_sex|CHAR(1)|Alive/dead status of individual |
| +i_gedcom|TEXT|Raw GEDCOM record for this individual |
| +|| |
|
|
| !!pgv_families
|
| - f_id VARCHAR(255) # GEDCOM family ID
|
| - f_file INT # ID number of the GEDCOM file the record is from
|
| - f_husb VARCHAR(255) # ID of the husband
|
| - f_wife VARCHAR(255) # ID of the wife
|
| - f_chil TEXT # List of children IDs, semi-colon (;) delimited
|
| - f_GEDCOM TEXT # Raw GEDCOM record for this family
|
| - f_numchil INT # Number of children in this family
|
| +||Field|Size|Notes |
| +f_id|VARCHAR(20)|GEDCOM family ID |
| +f_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +f_husb|VARCHAR(20)|ID of the husband |
| +f_wife|VARCHAR(20)|ID of the wife |
| +f_chil|TEXT|List of children IDs, semi-colon (;) delimited |
| +f_gedcom|TEXT|Raw GEDCOM record for this family |
| +f_numchil|INT|Number of children in this family |
| +|| |
| + |
| +!!pgv_media |
| +||Field|Size|Notes |
| +m_id|INT| |
| +m_media|VARCHAR(20)| |
| +m_ext|VARCHAR(6)| |
| +m_titl|VARCHAR(255)| |
| +m_file|VARCHAR(255)| |
| +m_gedfile|SMALLINT| |
| +m_gedrec|BLOB| |
| +|| |
| +This will be complimented with a link the generic bitweaver content and use the bitweaver management tools. |
| + |
| +!!pgv_media_mapping |
| +||Field|Size|Notes |
| +mm_id|INT| |
| +mm_media|VARCHAR(20)| |
| +mm_gid|VARCHAR(20)| |
| +mm_order|INT| |
| +mm_gedfile|INT| |
| +mm_gedrec|BLOB| |
| +|| |
|
|
| !!pgv_sources
|
| - s_id VARCHAR(255) # GEDCOM source ID
|
| - s_file INT # ID number of the GEDCOM file the record is from
|
| - s_name VARCHAR(255) # Abbreviated title of the source
|
| - s_GEDCOM TEXT # Raw GEDCOM record for this source
|
| +||Field|Size|Notes |
| +s_id|VARCHAR(20)|GEDCOM source ID |
| +s_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +s_name|VARCHAR(255)|Abbreviated title of the source |
| +s_gedcom|TEXT|Raw GEDCOM record for this source |
| +s_dbid|CHAR(1)|? |
| +|| |
|
|
| !!pgv_other
|
| - o_id VARCHAR(255) # GEDCOM record ID
|
| - o_file INT # ID number of the GEDCOM file the record is from
|
| - o_type VARCHAR(20) # Type of GEDCOM record
|
| - # (REPO, ADDR, NOTE, OBJE, etc)
|
| - o_GEDCOM TEXT # Raw GEDCOM record for this item
|
| +||Field|Size|Notes |
| +o_id|VARCHAR(20)|GEDCOM record ID |
| +o_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +o_type|VARCHAR(5)|Type of GEDCOM record (REPO, ADDR, NOTE, OBJE, etc) |
| +o_gedcom|TEXT|Raw GEDCOM record for this item |
| +|| |
|
|
| - pgv_names:
|
| - n_gid VARCHAR(255) # Individual ID that this name corresponds to
|
| - n_file INT # ID number of the GEDCOM file the record is from
|
| - n_name VARCHAR(255) # Name in GEDCOM format,
|
| - # with / / around the surname
|
| - n_letter VARCHAR(5) # First letter of the surname
|
| - n_surname VARCHAR(100) # Surname for this name record
|
| - n_type VARCHAR(10) # Type of name,
|
| - # P = primary, A = additional, C=calculated
|
| +!Record detail tables |
| +Date then extracted from the gedcom is processed to provide a more searchable indexing of names and places |
| + |
| +!!pgv_names |
| +||Field|Size|Notes |
| +n_id|VARCHAR(20)|Individual ID that this name corresponds to |
| +n_file SMALLINT|ID number of the GEDCOM file the record is from |
| +n_num|INT|Version number of the name ( allow multiple names ) |
| +n_type|VARCHAR(15)|Type of Name |
| +n_full|VARCHAR(255)|Name in GEDCOM format, with / / around the surname |
| +n_list|VARCHAR(255)|First letter of the surname |
| + | |The following fields are only used in INDI records |
| +n_surname|VARCHAR(255)|Surname for this name record |
| +n_surn|VARCHAR(255)|Surname used for soundex |
| +n_givn|VARCHAR(255)|Given used for soundex |
| +n_soundex_givn_std|VARCHAR(255)|Standard soundex given name |
| +n_soundex_surn_std|VARCHAR(255)|Standard soundex surname name |
| +n_soundex_givn_dm|VARCHAR(255)|DM soundex given name |
| +n_soundex_surn_dm|VARCHAR(255)|DM soundex surname name |
| +|| |
|
|
| !!pgv_dates
|
| - d_day # The day of month for this date
|
| - d_month # The 3 letter abbreviation for month of year
|
| - d_mon # Integer 1-12 for the month of year
|
| - d_year # The year for this date
|
| - d_datestamp # A date stamp of the form YYYYMMDD used for simple
|
| - # comparisons
|
| - d_fact # The fact that this date was associated with
|
| - d_gid # The gedcom XREF ID where this fact and date were found
|
| - d_file # The gedcom file id where this fact was found
|
| - d_type # Used if this date uses an alternate calendar type
|
| +||Field|Size|Notes |
| +d_day|SMALLINT|The day of month for this date |
| +d_month|VARCHAR(5)|The 3 letter abbreviation for month of year |
| +d_mon|SMALLINT|Integer 1-12 for the month of year |
| +d_year|SMALLINT|The year for this date |
| +d_julianday1|INT|Day count in julian format |
| +d_julianday2|INT|Day count in julian format |
| +d_datestamp|TIMESTAMP|Add UTC normalised time information |
| +d_fact|VARCHAR(15)|The fact that this date was associated with |
| +d_gid|VARCHAR(20)|The gedcom XREF ID where this fact and date were found |
| +d_file|SMALLINT|The gedcom file id where this fact was found |
| +d_type|VARCHAR(13)|Used if this date uses an alternate calendar type |
| +|| |
| + |
| +!!pgv_places |
| +||Field|Size|Notes |
| +p_id|INT|Unique identifier |
| +p_place|VARCHAR(150)|Place name |
| +p_level|INT|Level of the place in the hierarchy, 0 is the country or state |
| +p_parent_id|INT|ID of this item's parent place in the hierarchy. A city's parent would be the county it is in, a county's parent would be a state or province, and a state or province would have a country as parent. |
| +p_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +p_std_soundex|VARCHAR(255)|Standard soundex of place name |
| +p_dm_soundex|VARCHAR(255)|DM soundex of place name |
| +|| |
| +At some point a cross reference to openstreetmap will be added along with links to any mapper content. |
| + |
| +!Crosslink tables |
| +Data is then further indexed to provide cross references between the data. |
| + |
| +!!pgv_placelinks |
| +||Field|Size|Notes |
| +pl_p_id|INT|Unique identifier |
| +pl_gid|VARCHAR(20)|Family or individual ID referencing this place |
| +pl_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +|| |
| + |
| +!!pgv_link |
| +||Field|Size|Notes |
| +l_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +l_from|VARCHAR(20)|Source reference ID |
| +l_type|VARCHAR(15)|Link type |
| +l_to|VARCHAR(20)|Destination reference ID |
| +|| |
| + |
| +!!pgv_nextid |
| +||Field|Size|Notes |
| +ni_id|INT|ID number for record type |
| +ni_type|VARCHAR(15)|Record type |
| +ni_file|SMALLINT|The gedcom file id where this id is used |
| +|| |
| +Since MAX() is used to access this data, it is possible in situations where multiple users are adding data that the same ID will be assigned. This should be locked in a similar way to the file updates. |
| +The numbers generated are used in conjunction with the the prefix letters allocated to each record type |
| + |
| +!!pgv_remotelinks |
| +||Field|Size|Notes |
| +r_gid|VARCHAR(20)|Reference ID for link |
| +r_linkid|VARCHAR(255)|Link data |
| +r_file|SMALLINT|ID number of the GEDCOM file the record is from |
| +|| |
| + |
| +!Miscellaneous Tables |
| +The remaining tables provide additional user data and is on the whole replaced by other bitweaver packages. |
|
|
| !!pgv_blocks
|
| - b_id INT(11) # Record ID
|
| - b_username VARCHAR(100) # User name whom block belongs to
|
| - b_location VARCHAR(30) # Location of the block.
|
| - # Main column or right column
|
| - b_order INT(11) # Position of the block within the column
|
| - b_name VARCHAR(255) # Name of the block
|
| - b_config TEXT # Configuration settings for this block
|
| +This is replaced by the bitweaver layout manager phpgedview blocks are repackaged as modules. |
|
|
| !!pgv_favorites
|
| - fv_id INT(11) # Record ID
|
| - fv_username VARCHAR(30) # User name whom the favorite belongs to
|
| - fv_gid VARCHAR(10) # ID of the favorite
|
| - fv_type VARCHAR(10) # Type of favorite (currently only INDI)
|
| - fv_file VARCHAR(100) # File that this favorite belongs to
|
| - fv_url VARCHAR(255) # The URL for this favorite if it is not one of
|
| - # the basic types
|
| - fv_title VARCHAR(255) # A title for URL based favorites
|
| - fv_note TEXT # Optional descriptive information about this favorite
|
| +||Field|Size|Notes |
| +fv_id|INT|Record ID |
| +fv_username|VARCHAR(30)|User name whom the favourite belongs to |
| +fv_gid|VARCHAR(20)|ID of the favourite |
| +fv_type|VARCHAR(15)|Type of favourite (currently only INDI) |
| +fv_file|VARCHAR(100)|File that this favourite belongs to |
| +fv_url|VARCHAR(255)|The URL for this favourite if it is not one of the basic types |
| +fv_title|VARCHAR(255)|A title for URL based favourites |
| +fv_note|TEXT|Optional descriptive information about this favourite |
| +|| |
| +This should be provided by a similar facility in bitweaver, but may need to develop that area first. |
|
|
| !!pgv_messages
|
| - m_id INT(11) # Record ID
|
| - m_from VARCHAR(255) # Name or email address of the sender
|
| - m_to VARCHAR(30) # Destination user name
|
| - m_subject VARCHAR(255) # Subject of the message
|
| - m_body TEXT # Body text of the message
|
| - m_created VARCHAR(255) # Time stamp when the message was created
|
| +Replaced by bitweaver messaging system. |
|
|
| !!pgv_news
|
| - n_id INT(11) # Unique identifier
|
| - n_username VARCHAR(100) # User name or GEDCOM the News item belongs to
|
| - n_date INT(11) # Time stamp of last update
|
| - n_title VARCHAR(255) # Title of the article
|
| - n_text TEXT # Body text of the article
|
| -
|
| -!!pgv_places
|
| - p_id INT(11) # Unique identifier
|
| - p_place VARCHAR(150) # Place name
|
| - p_level INT(11) # Level of the place in the hierarchy,
|
| - # 0 is the country or state
|
| - p_parent_id INT(11) # ID of this item's parent place in the
|
| - # hierarchy. A city's parent would be the
|
| - # county it is in, a county's parent would be
|
| - # a state or province, and a state or province
|
| - # would have a country as parent.
|
| - p_file INT # ID number of the GEDCOM file the record is from
|
| -
|
| -!!pgv_placelinks
|
| - pl_p_id INT(11) # Unique identifier
|
| - pl_gid VARCHAR(30) # Family or individual ID referencing this place
|
| - pl_file INT # ID number of the GEDCOM file the record is from
|
| +Replaced by bitweaver articles or blog depending on the type of user news display required. |
|
|
| !!pgv_users
|
| - u_username VARCHAR(30) # User name
|
| - u_password VARCHAR(255) # Encrypted password
|
| - u_fullname VARCHAR(255) # User's full name
|
| - u_GEDCOMid TEXT # Serialized array representing the GEDCOM IDs
|
| - # for this user
|
| - u_rootid TEXT # Serialized array representing the root IDs
|
| - # for this user
|
| - u_canadmin ENUM('Y','N') # Is the user an admin or not
|
| - u_canedit TEXT # Serialized array indicating the editing
|
| - # privileges a user has for each GEDCOM
|
| - u_email TEXT # Email addres
|
| - u_verified VARCHAR(20) # User self verified
|
| - u_verified_by_admin VARCHAR(20) # User has been verified by the admin
|
| - u_language VARCHAR(50) # User's preferred language
|
| - u_pwrequested VARCHAR(20) # User requested a new password
|
| - u_reg_timestamp VARCHAR(50) # Registration timestamp
|
| - u_reg_hashcode VARCHAR(255) # Self-registration hash key
|
| - u_theme VARCHAR(50) # User's preferred theme
|
| - u_loggedin ENUM('Y','N') # User's login status
|
| - u_sessiontime INT(14) # User's last login time stamp
|
| - u_contactmethod VARCHAR(20) # User's preferred method of contact
|
| - u_visibleonline ENUM('Y','N') # Whether or not the user is visible in
|
| - # the logged on users block
|
| - u_editaccount ENUM('Y', 'N') # Whether or not the user can edit his
|
| - # own account information
|
| - u_defaulttab INT(10) # Default tab on the individual page
|
| - # for this user
|
| - u_comment VARCHAR(255) # Admin's comments on this user
|
| - u_comment_exp VARCHAR(20) # Alert date for the admin, for instance
|
| - # for temporary accounts.
|
| - u_sync_gedcom VARCHAR(2) # If the user has a GEDCOM record ID, then
|
| - # should some of the data for the user (name,
|
| - # email) be synchronized with the GEDCOM data.
|
| - u_relationship_privacy VARCHAR(2) # Should this user use relationship privacy
|
| - u_max_relation_length INT # The maximum path that the user is allowed to see
|
| - u_auto_accept VARCHAR(2) # Are changes made by this user automatically
|
| - # accepted into the database
|
| +This has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage. |
|
|
| -!!media
|
| - m_id INT NOT NULL
|
| - m_media VARCHAR(15)
|
| - m_ext VARCHAR(6)
|
| - m_titl VARCHAR(255)
|
| - m_file VARCHAR(255)
|
| - m_gedfile BIGINT
|
| - m_gedrec BLOB
|
| - PRIMARY KEY (m_id)
|
|
|
| -!!media_mapping
|
| - mm_id INT NOT NULL
|
| - mm_media VARCHAR(15) NOT NULL default ''
|
| - mm_gid VARCHAR(15) NOT NULL default ''
|
| - mm_order INT NOT NULL default '0'
|
| - mm_gedfile INT default NULL
|
| - mm_gedrec BLOB
|
| - PRIMARY KEY (mm_id) |
| +((PhpgedviewPackage)) |