History of phpgedview_tables
!phpGedView DATABASE TABLE LAYOUT
PhpGedView uses a very simple database table layout because it operates primarily on the GEDCOM data and only needs the database for search and retrieval. There are only a few tables in the database:
||Table|Description|
pgv_blocks|Description of each user's Portal page|bitweaver modules
pgv_dates|Stores decoded date information from GEDCOM records|
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 (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 )++
!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
||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
||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
||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
||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
||
!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
||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
This is replaced by the bitweaver layout manager phpgedview blocks are repackaged as modules.
!!pgv_favorites
||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
Replaced by bitweaver messaging system.
!!pgv_news
Replaced by bitweaver articles or blog depending on the type of user news display required.
!!pgv_users
This has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage.
((PhpgedviewPackage))
PhpGedView uses a very simple database table layout because it operates primarily on the GEDCOM data and only needs the database for search and retrieval. There are only a few tables in the database:
||Table|Description|
pgv_blocks|Description of each user's Portal page|bitweaver modules
pgv_dates|Stores decoded date information from GEDCOM records|
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 (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 )++
!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
||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
||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
||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
||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
||
!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
||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
This is replaced by the bitweaver layout manager phpgedview blocks are repackaged as modules.
!!pgv_favorites
||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
Replaced by bitweaver messaging system.
!!pgv_news
Replaced by bitweaver articles or blog depending on the type of user news display required.
!!pgv_users
This has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage.
((PhpgedviewPackage))