Inside info
Contents
Inside Information
Database tables v4.0 and 4.1
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 17 tables in the v4.0 database, 20 in the 4.1 database, and this note assumes use of the “pgv_” prefix for each table name:
| pgv_blocks | Description of each user's Portal page |
| pgv_dates | Stores decoded date information from GEDCOM records |
| pgv_families | All the families in the GEDCOM |
| pgv_favorites | Stores users favorites |
| pgv_individuals | All the individuals in the GEDCOM |
| pgv_media | |
| pgv_media_mapping | |
| pgv_messages | Messages to and from users |
| pgv_names | Stores decoded name information from GEDCOM records |
| pgv_news | Stores news items for the Index and Portal pages |
| pgv_placelinks | Cross-reference between places and individuals and families |
| pgv_places | Place hierarchy |
| pgv_remotelinks | |
| pgv_other | All other level 0 GEDCOM records (i.e., repositories, media objects, notes, etc.) |
| pgv_sources | All the sources in the GEDCOM |
| pgv_users | Table for user data (only exists if using default MySQL authentication module) |
| pgv_next_id |
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.
In v4.1, three additional tables have been added -
| pgv_mutex | (information required here) |
| pgv_other | -- |
| pgv_soundex | -- |
Following is a more detailed description of each table in 4.0:
| 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 |
| d_day INT(11) | The day of month for this date |
| d_month VARCHAR(5) | The 3 letter abbreviation for month of year |
| d_mon INT(11) | Integer 1-12 for the month of year |
| d_year INT(11) | The year for this date |
| d_datestamp INT(11) | A date stamp of the form YYYYMMDD used for simple comparisons |
| d_fact VARCHAR(10) | The fact that this date was associated with |
| d_gid VARCHAR(255) | The GEDCOM XREF ID where this fact and date were found |
| d_file INT(11) | The GEDCOM file id where this fact was found |
| d_type VARCHAR(13) | Used if this date uses an alternate calendar type |
| 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 |
| 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 |
| 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 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 |
| m_id INT(11) | Media ID |
| m_media VARCHAR(15) | Media reference number |
| m_ext VARCHAR(6) | File type eg jpg |
| m_titl VARCHAR(255) | Media title |
| m_file (VARCHAR(255) | File name eg media/picname.jpg |
| m_gedfile INT(11) | |
| m_gedrec TEXT | Raw GEDCOM record for this media |
| mm_id INT(11) | Media ID |
| mm_media VARCHAR(15) | Media reference number |
| mm_gid VARCHAR(15) | Connected to individual Ixxx |
| mm_order INT(11) | Sequence |
| mm_gedfile INT(11) | Belongs to which gedfile |
| mm_gedrec TEXT | Raw GEDCOM record for this media |
| 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 |
| n_gid VARCHAR(255) | Individual ID that this name corresponds to |
| n_file INT(11) | 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 |
| 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 |
| ni_id INT(11) | |
| ni_type VARCHAR(30) | |
| ni_gedfile INT(11) |
| o_id VARCHAR(255) | GEDCOM record ID |
| o_file INT(11) | 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 |
| pl_p_id INT(11) | Unique identifier |
| pl_gid VARCHAR(30) | Family or individual ID referencing this place |
| pl_file INT(11) | ID number of the GEDCOM file the record is from |
| 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(11) | ID number of the GEDCOM file the record is from |
| r_gid VARCHAR(255) | |
| r_linkid VARCHAR(255) | |
| r_file INT(11) |
| s_id VARCHAR(255) | GEDCOM source ID |
| s_file INT(11) | 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 |
| 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 address |
| 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 |
The 3 extra tables in 4.1 are:
| mx_id INT(11) | Unique identifier |
| mx_name VARCHAR(255) | |
| mx_thread VARCHAR(255) | |
| mx_time INT(11) |
| o_id INT(11) | GEDCOM record ID |
| o_file INT(11) | ID number of the GEDCOM file the record is from |
| o_type VARCHAR(20) | Type of GEDCOM record |
| o_gedcom LONGTEXT | Raw GEDCOM record for this item |
| sx_i_id VARCHAR(255) | Unique identifier (Individuals table) |
| sx_n_id VARCHAR(255) | Unique identifier (Names table) |
| sx_file INT(11) | Unique identifier (GEDCOM file) |
| sx_fn_std_code TEXT | Standard first name soundex code. Used for soundex searching |
| sx_fn_dm_code TEXT | Soundex code for international first names |
| sx_ln_std_code TEXT | Standard last name soundex code. Used for soundex searching |
| sx_ln_dm_code TEXT | Soundex code for international last names. This uses the Daitch-Mokotoff soundex method, which is better suited for them. |
This table layout has received criticism from some for its simplicity, size, and because it does not follow a genealogy model like GENTECH. We admit that these tables can be hard to interface to because the code has to understand GEDCOM in order to get information out of them. We also admit that storing the raw GEDCOM data could make the tables very large.
Fortunately the GEDCOM standard is not a very complex or large format; it only requires 6 characters per line, which is very good compared to something like XML. However, there are some very compelling reasons why this table structure was chosen:
- Simpler tables mean fewer and simpler database queries. This takes a large load off the database and makes the program run faster.
- Nothing is lost in the Import. Even though GEDCOM is a standard, each genealogy program interprets the standard a bit differently and adds its own tags. Creating a database model that conforms to all the GEDCOM outputs of different genealogy software programs would be very difficult.
Manual configuration
Advanced users who understand PHP may want to configure manually by editing the configuration file config.php When you have finished editing config.php make sure that the variable $CONFIGURED=true; so that the program does not try to forward you to the configuration.php script when you launch it for the first time.
You can manually add GEDCOMS to the system by adding them to the $GEDCOMS array in the index/GEDCOMs.php file. The GEDCOM array looks like this:
$gedarray = array(); $gedarray["GEDCOM"] = "surname.ged"; $gedarray["config"] = "./index/surname.ged_conf.php"; $gedarray["privacy"] = "./index/surname.ged_priv.php"; $gedarray["title"] = "Surname Genealogy"; $gedarray["path"] = "./surname.ged"; $GEDCOMS["surname.ged"] = $gedarray;
"surname" above could be anything, for example, "johnson" or "private". You must pay attention to the case of what you enter. PhpGedView is case sensitive.
Each GEDCOM will need a configuration file. You can copy the config_gedcom.php file which has all of the default values for each GEDCOM you add manually. Then set the "config" item of the GEDCOMs array to point to the file you copied.
Each GEDCOM also needs a privacy file. Make a copy the privacy.php file for each GEDCOM and set the "privacy" item of the GEDCOMs array to the location of the new privacy.php file.
Non-standard GEDCOM codes
The GEDCOM 5.5 standard has a defined set of codes. (This used read - You can read the specification online at http://www.phpGedView.net/ged551-5.pdf but the link seems to be broken) Part of the standard allows for genealogy software to define their own codes, and requests that they begin with an "_" underscore.
When PhpGedView comes across a tag that is not defined it will display an error message. You can disable these error messages by setting $HIDE_GEDCOM_ERRORS=true in the config_gedcom.php file. PhpGedView can also be customized to work with these codes by adding them to the facts array in a new language file named facts.en.extra.php. If you add it to the English facts file you should also add it to the other facts language files you are using on your site if you want other languages to translate the tag correctly.
The format of the facts file is a PHP associative array. Each tag requires one line in the array. The following line defines the label "Abbreviation" for the ABBR GEDCOM tag.
$factarray["ABBR"] = "Abbreviation";
As an example, if you use a genealogy program that generates the tag "_ZZZZ" you can customize PhpGedView to accept this code by adding the following lines to the facts.en.extra.php file:
<?php $factarray["_ZZZZ"] = "Tag Label goes here"; ?>
You can disable these error messages in the config.php file by setting $HIDE_GEDCOM_ERRORS=true.
Backup
Note: If you want to use the Backup feature of the Upgrade utility in PhpGedView you will need to either set Write permission on the "phpgedview" folder itself or create a folder named "backup" with Write permissions. Write permissions for the "phpgedview" folder can be removed as soon as the backup folder is in place and has the appropriate permissions.
With the Backup function in the administration menu, you can make a simple backup of all important PhpGedView files. With this backup, it's possible to rebuild your PhpGedView site to the situation at backup time.
The backup can contain the following files, as selected on the Backup page:
- config.php with all configuration settings of your installation - all GEDCOM files that were present in your installation - all GEDCOM options and privacy settings for the above files - counters, PhpGedView- and search-logfiles - user definitions and options (block definitions, favorites, messages and news)
The files will be gathered into a ZIP file, which can be downloaded by clicking the link on the page.
Note: The database itself will not be included in the backup, since it can be rebuilt using the files in the backup.
Note: All pending changes (not approved or rejected yet by the administrator) will be present in the GEDCOM files but can no longer be identified as changes. If the database is rebuilt using the GEDCOMs, these changes therefore can no longer be rejected.
Note: If you have installed your index folder outside of the root directory of your web server, the references to some of the files in the zip will include directory paths of the form "../". Some zip utilities (for example Windows XP/Vista's native zip capability in Windows Explorer) can't handle this style of file path. You may need to get a tool that correctly handles these paths. WinRAR is one tool that does.
The Backup function uses the PclZip library, which is written by Vincent Blavet and can be found at http://www.phpconcept.net/pclzip.
PostNuke and PHPNuke integration
PhpGedView can integrate with PostNuke and phpNuke so that your users do not have to login twice.
After you have PhpGedView up and running, you should follow the instructions in the readme.txt file in the "pgvnuke" folder.
The files that make the integration magic happen were donated by Jim Carey.
