History of phpgedview_tables
Version 5 | Current version | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
phpGedView DATABASE TABLE LAYOUTThis needs updating to the later structure used by phpGedView version 4, but I hope to strip some of the tables before I get round to updating the contents here. 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:
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_individuals
pgv_families
pgv_sources
pgv_other
pgv_names
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 . # 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 pgv_blocksb_id INT(11) # Record IDb_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_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 . # the basic types fv_title VARCHAR(255) # A title for URL based favorites 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, . # 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_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_usersThis has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage.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) PhpgedviewPackage | 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 |