History of phpgedview_tables
Version 2 | Current version | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
phpGedView DATABASE TABLE LAYOUTPhpGedView 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:
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. pgv_individualsi_id VARCHAR(255) # GEDCOM individual IDi_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
i_letter VARCHAR(5) # First letter of the individual's surname i_surname VARCHAR(100) # Person's surname pgv_familiesf_id VARCHAR(255) # GEDCOM family IDf_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_sourcess_id VARCHAR(255) # GEDCOM source IDs_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 pgv_othero_id VARCHAR(255) # GEDCOM record IDo_file INT # ID number of the GEDCOM file the record is from o_type VARCHAR(20) # Type of GEDCOM record
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,
n_surname VARCHAR(100) # Surname for this name record n_type VARCHAR(10) # Type of name,
pgv_datesd_day # The day of month for this dated_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
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 pgv_blocksb_id INT(11) # Record IDb_username VARCHAR(100) # User name whom block belongs to b_location VARCHAR(30) # Location of the block.
b_name VARCHAR(255) # Name of the block b_config TEXT # Configuration settings for this block pgv_favoritesfv_id INT(11) # Record IDfv_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
fv_note TEXT # Optional descriptive information about this favorite pgv_messagesm_id INT(11) # Record IDm_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_newsn_id INT(11) # Unique identifiern_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_placesp_id INT(11) # Unique identifierp_place VARCHAR(150) # Place name p_level INT(11) # Level of the place in the hierarchy,
pgv_placelinkspl_p_id INT(11) # Unique identifierpl_gid VARCHAR(30) # Family or individual ID referencing this place pl_file INT # ID number of the GEDCOM file the record is from pgv_usersu_username VARCHAR(30) # User nameu_password VARCHAR(255) # Encrypted password u_fullname VARCHAR(255) # User's full name u_GEDCOMid TEXT # Serialized array representing the GEDCOM IDs
u_canedit TEXT # Serialized array indicating the editing
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
u_comment_exp VARCHAR(20) # Alert date for the admin, for instance
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
mediam_id INT NOT NULLm_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_mappingmm_id INT NOT NULLmm_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) | phpGedView DATABASE TABLE LAYOUTPhpGedView 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:
More details can also be found in the phpgedview wiki ( links will be created when their wiki is working again ) Core data tablesThe 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
pgv_families
pgv_media
This will be complimented with a link the generic bitweaver content and use the bitweaver management tools. pgv_media_mapping
pgv_sources
pgv_other
Record detail tablesDate then extracted from the gedcom is processed to provide a more searchable indexing of names and placespgv_names
pgv_dates
pgv_places
At some point a cross reference to openstreetmap will be added along with links to any mapper content. Crosslink tablesData is then further indexed to provide cross references between the data.pgv_placelinks
pgv_link
pgv_nextid
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
Miscellaneous TablesThe remaining tables provide additional user data and is on the whole replaced by other bitweaver packages.pgv_blocksThis is replaced by the bitweaver layout manager phpgedview blocks are repackaged as modules.pgv_favorites
This should be provided by a similar facility in bitweaver, but may need to develop that area first. pgv_messagesReplaced by bitweaver messaging system.pgv_newsReplaced by bitweaver articles or blog depending on the type of user news display required.pgv_usersThis has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage.PhpgedviewPackage |