Inside info

From PGVWiki
Jump to: navigation, search

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:

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
pgv_dates
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
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
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
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
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
pgv_media
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
pgv_media_mapping
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
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
pgv_names
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
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_next_id
ni_id INT(11)
ni_type VARCHAR(30)
ni_gedfile INT(11)
pgv_other
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
pgv_placelinks
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
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(11) ID number of the GEDCOM file the record is from
pgv_remotelinks
r_gid VARCHAR(255)
r_linkid VARCHAR(255)
r_file INT(11)
pgv_sources
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
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 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:

pgv_mutex
mx_id INT(11) Unique identifier
mx_name VARCHAR(255)
mx_thread VARCHAR(255)
mx_time INT(11)
pgv_other
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
pgv_soundex
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.