function __construct($sBackend, $sDSN, $sUsername = '', $sPassword = '')
{
// Initiate database connection.
$sDSN = $sBackend . ':' . $sDSN;
$aOptions = array();
if ($sBackend == 'mysql') {
// This method for setting the charset works also before 5.3.6, when "charset" was introduced in the DSN.
// Fix #4; Implement fix for PHP 5.3.0 on Windows, where PDO::MYSQL_ATTR_INIT_COMMAND by accident is not available.
// https://bugs.php.net/bug.php?id=47224 (other constants were also lost, but we don't use them)
// Can't define a class' constant, so I'll have to use this one. This can be removed (and MYSQL_ATTR_INIT_COMMAND
// below restored to PDO::MYSQL_ATTR_INIT_COMMAND) once we're sure they're no other 5.3.0 users left.
if (!defined('MYSQL_ATTR_INIT_COMMAND')) {
// Still needs check though, in case two PDO connections are opened.
define('MYSQL_ATTR_INIT_COMMAND', 1002);
}
$aOptions = array(MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE);
}
try {
parent::__construct($sDSN, $sUsername, $sPassword, $aOptions);
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('LOVD_PDOStatement'));
} catch (PDOException $e) {
// No connection or couldn't select database!
lovd_displayError('Init', 'Error connecting to database: ' . $e->getMessage());
}
}
function __construct($nID, $nGenerations = 5, $sMode = 'pedigree')
{
// Default constructor.
global $_DB;
if (is_int($nID)) {
// We don't handle integers well, change to string.
$nID = sprintf('%08d', $nID);
}
if (!ctype_digit($nID)) {
lovd_displayError('ObjectError', 'Pedigree::__construct() called with non-valid Individual ID.');
}
if (!is_int($nGenerations) && !ctype_digit($nGenerations)) {
lovd_displayError('ObjectError', 'Pedigree::__construct() called with non-valid number of generations.');
}
if (!is_string($sMode) || !in_array($sMode, array('pedigree', 'family_tree'))) {
$this->sMode = 'pedigree';
} else {
$this->sMode = $sMode;
}
// Make sure the individual actually exists.
if (!$_DB->query('SELECT COUNT(*) FROM ' . TABLE_INDIVIDUALS . ' WHERE id = ?', array($nID))->fetchColumn()) {
lovd_displayError('ObjectError', 'Pedigree::__construct() called with non-existing Individual ID ' . $nID . '.');
}
// Build up the tree.
// Current limitations: the system can not build a tree when starting from a patient with multiple trunks.
// In other words, we can follow only one lineage up to a certain pair of (grand)*parents.
// So, I should end up with exactly two IDs when I walk up the line.
$aParentIDs = array($nID);
do {
// I don't think I can do a prepared statement, because the IN () range can change over time.
$sFROM = 'AS parent FROM ' . TABLE_INDIVIDUALS . ' WHERE id IN (?' . str_repeat(', ?', count($aParentIDs) - 1) . ') HAVING parent IS NOT NULL';
$q = $_DB->query('SELECT DISTINCT `fatherid` ' . $sFROM . ' UNION ' . 'SELECT DISTINCT `motherid` ' . $sFROM, array_merge($aParentIDs, $aParentIDs));
$aIDs = $q->fetchAllColumn();
if ($aIDs) {
// Only overwrite parent IDs when we have found the parents of these IDs.
$aParentIDs = $aIDs;
}
} while ($aIDs);
// FIXME; restrict to certain number of generations!
// Now, if we have more than two parent IDs, something is wrong.
// We could have one ID only if the $nID from the constructor has no
// parents (and is therefore the start of the tree).
if (count($aParentIDs) > 1) {
lovd_displayError('ObjectError', 'Pedigree::__construct() failed to construct a pedigree tree: ended up with ' . count($aParentIDs) . ' founding parents for individual ID ' . $nID . '.');
}
// Alright, build the tree.
$this->tree = $this->buildTree($aParentIDs);
return true;
}
function __construct($sType = 'feed', $sFeedTitle = '', $sFeedURL = '', $sFeedID = '', $sFormat = 'atom')
{
global $_CONF, $_DB, $_SETT, $_STAT;
// Feed or entry only options.
if (!in_array($sType, array('feed', 'entry'))) {
$sType = 'feed';
// Silent error - we just assume Feed when we don't understand the requested type.
}
$this->sType = $sType;
// So addEntry() knows what to do.
if (preg_match($this->sAtomEntrySplit, $this->sAtomFeed, $aRegs)) {
$this->sAtomEntry = $aRegs[1];
} else {
// Can't parse own $sAtomFeed, bug in LOVD (or someone has messed with the code).
lovd_displayError('Feed', 'Couldn\'t parse AtomFeed. This is a bug in LOVD or in one of it\'s modules. Please <A href="' . $_SETT['upstream_URL'] . 'bugs/" target="_blank">file a bug</A> and include the below messages to help us solve the problem.' . "\n" . 'Debug: ' . lovd_getProjectFile() . ($_SERVER['QUERY_STRING'] ? '?' . $_SERVER['QUERY_STRING'] : ''));
}
if ($sType == 'feed') {
// Fill in the feed's variables.
$this->sAtomFeed = str_replace('{{ FEED_TITLE }}', $sFeedTitle, $this->sAtomFeed);
$this->sAtomFeed = str_replace('{{ LOVD_URL }}', $_CONF['location_url'] ? $_CONF['location_url'] : lovd_getInstallURL(), $this->sAtomFeed);
$this->sAtomFeed = str_replace('{{ FEED_URL }}', $sFeedURL, $this->sAtomFeed);
$this->sAtomFeed = str_replace('{{ FEED_ID }}', $sFeedID ? $sFeedID : 'tag:' . $_SERVER['HTTP_HOST'] . ',' . $_STAT['install_date'] . ':' . $_STAT['signature'], $this->sAtomFeed);
$this->sAtomFeed = str_replace('{{ LOVD_VERSION }}', $_SETT['system']['version'], $this->sAtomFeed);
// Let the date of last update depend on the type of feed.
if (preg_match('/\\/variants\\/(.+)$/', $sFeedURL, $aRegs)) {
// Variants of a specific gene.
$sDateUpdated = $_DB->query('SELECT MAX(updated_date) FROM ' . TABLE_GENES . ' WHERE id = ?', array($aRegs[1]))->fetchColumn();
} else {
// Find date of last update for all genes.
$sDateUpdated = $_DB->query('SELECT MAX(updated_date) FROM ' . TABLE_GENES)->fetchColumn();
}
$this->sAtomFeed = str_replace('{{ FEED_DATE_UPDATED }}', $this->formatDate($sDateUpdated), $this->sAtomFeed);
// For now, remove any of the entries until they are added using addEntry().
$this->sAtomFeed = preg_replace($this->sAtomEntrySplit, '{{ ENTRY }}', $this->sAtomFeed);
} else {
// Only one entry requested.
// Remove all, except the XML start entity!
$this->sAtomFeed = preg_replace('/^(.+[\\r\\n]{1,2})(.|[\\r\\n]{1,2})+$/', "\$1{{ ENTRY }}", $this->sAtomFeed);
}
}
function lovd_setColleagues($sUserID, $sUserFullname, $sUserInsititute, $sUserEmail, $aColleagues, $bAllowGrantEdit = true)
{
// Removes all existing colleagues for user $sUserID and replaces them with
// all IDs in $aColleagues.
global $_DB;
if (!$bAllowGrantEdit) {
foreach ($aColleagues as $aColleague) {
if ($aColleague['allow_edit']) {
lovd_displayError('ShareAccess', 'Not allowed to grant edit permissions.');
}
}
}
$sOldColleaguesQuery = 'SELECT userid_to FROM ' . TABLE_COLLEAGUES . ' WHERE userid_from = ?';
$aOldColleagueIDs = $_DB->query($sOldColleaguesQuery, array($sUserID))->fetchAllColumn();
$aColleagueIDs = array();
// Array with new colleague IDs, to see who's new and who's removed.
$_DB->beginTransaction();
// Delete all current colleague records with given user in 'from' field.
$_DB->query('DELETE FROM ' . TABLE_COLLEAGUES . ' WHERE userid_from = ?', array($sUserID));
if (count($aColleagues)) {
// Build parts for multi-row insert query.
$sPlaceholders = '(?,?,?)' . str_repeat(',(?,?,?)', count($aColleagues) - 1);
$aData = array();
// Arguments for query.
foreach ($aColleagues as $aColleague) {
$aColleagueIDs[] = $aColleague['id'];
array_push($aData, $sUserID, $aColleague['id'], $aColleague['allow_edit']);
}
$_DB->query('INSERT INTO ' . TABLE_COLLEAGUES . ' (userid_from, userid_to, allow_edit) VALUES ' . $sPlaceholders, $aData);
}
$_DB->commit();
// Notify only the newly added colleagues by email.
// Note: call array_values() on the parameter array to make sure the
// indices are normalized to what PDO expects.
$aNewColleagueIDs = array_values(array_diff($aColleagueIDs, $aOldColleagueIDs));
lovd_mailNewColleagues($sUserID, $sUserFullname, $sUserInsititute, $sUserEmail, $aNewColleagueIDs);
// Write to log.
$aColleagueIDsRemoved = array_values(array_diff($aOldColleagueIDs, $aColleagueIDs));
$sMessage = 'Updated colleagues for user #' . $sUserID . "\nAdded: ";
for ($i = 0; $i < count($aNewColleagueIDs); $i++) {
$aNewColleagueIDs[$i] = 'user #' . $aNewColleagueIDs[$i];
}
$sMessage .= $aNewColleagueIDs ? join(', ', $aNewColleagueIDs) : 'none';
$sMessage .= "\nRemoved: ";
for ($i = 0; $i < count($aColleagueIDsRemoved); $i++) {
$aColleagueIDsRemoved[$i] = 'user #' . $aColleagueIDsRemoved[$i];
}
$sMessage .= $aColleagueIDsRemoved ? join(', ', $aColleagueIDsRemoved) : 'none';
$sMessage .= "\n";
lovd_writeLog('Event', LOG_EVENT, $sMessage);
}
function viewEntry($nID = false)
{
// Views just one entry from the database.
global $_DB, $_T;
if (empty($nID)) {
// We were called, but the class wasn't initiated with an ID. Fail.
lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::viewEntry() - Method didn\'t receive ID');
}
$bAjax = substr(lovd_getProjectFile(), 0, 6) == '/ajax/';
// Check existence of entry.
$n = $this->getCount($nID);
if (!$n) {
lovd_showInfoTable('No such ID!', 'stop');
if (!$bAjax) {
$_T->printFooter();
}
exit;
}
if (!defined('LOG_EVENT')) {
define('LOG_EVENT', $this->sObject . '::viewEntry()');
}
if ($this->sSQLPreViewEntry !== '') {
$_DB->query($this->sSQLPreViewEntry);
}
// Manipulate WHERE to include ID, and build query.
$sTableName = constant($this->sTable);
// Try to get the name of the ID column in MySQL. I'd rather not do it this way, but even worse would be to have yet another variable.
if (!empty($this->aColumnsViewList['id']['db'][0])) {
$sIDColumn = $this->aColumnsViewList['id']['db'][0];
} else {
if (preg_match('/' . constant($this->sTable) . ' AS ([a-z]+)( .+)?$/', $this->aSQLViewEntry['FROM'], $aRegs)) {
// An alias was defined. Use it.
$sIDColumn = $aRegs[1] . '.id';
} else {
// Use the normal table name.
$sIDColumn = constant($this->sTable) . '.id';
}
}
$this->aSQLViewEntry['WHERE'] = $sIDColumn . ' = ?' . (!$this->aSQLViewEntry['WHERE'] ? '' : ' AND ' . $this->aSQLViewEntry['WHERE']);
$sSQL = 'SELECT ' . $this->aSQLViewEntry['SELECT'] . ' FROM ' . $this->aSQLViewEntry['FROM'] . ' WHERE ' . $this->aSQLViewEntry['WHERE'] . (!$this->aSQLViewEntry['GROUP_BY'] ? '' : ' GROUP BY ' . $this->aSQLViewEntry['GROUP_BY']);
// Run the actual query.
$zData = $_DB->query($sSQL, array($nID))->fetchAssoc();
// If the user has no rights based on the statusid column, we don't have a $zData.
if (!$zData) {
// Don't give away information about the ID: just pretend the entry does not exist.
lovd_showInfoTable('No such ID!', 'stop');
$_T->printFooter();
exit;
}
$zData = $this->autoExplode($zData);
$zData = $this->prepareData($zData, 'entry');
// Print the data.
print ' <TABLE border="0" cellpadding="0" cellspacing="1" width="600" class="data">';
foreach ($this->aColumnsViewEntry as $sField => $header) {
$sHeader = is_array($header) ? $header[0] : $header;
if (preg_match("/TableStart/", $sField)) {
print ' <TABLE border="0" cellpadding="0" cellspacing="1" width="600" class="data">';
} elseif (preg_match("/TableHeader/", $sField)) {
print "\n" . ' <TR>' . "\n" . ' <TH colspan="2" class="S15" valign="top">' . $sHeader . '</TH></TR>';
} elseif (preg_match("/TableEnd/", $sField)) {
print '</TABLE>' . "\n\n";
} elseif (preg_match("/HR/", $sField)) {
print "\n" . ' <HR>' . "\n";
} else {
print "\n" . ' <TR>' . "\n" . ' <TH valign="top">' . str_replace(' ', ' ', $sHeader) . '</TH>' . "\n" . ' <TD>' . ($zData[$sField] === '' ? '-' : str_replace(array("\r\n", "\r", "\n"), '<BR>', $zData[$sField])) . '</TD></TR>';
}
}
print '</TABLE>' . "\n\n";
return $zData;
}
function updateEntry($sID, $aData, $aFields = array())
{
// Updates entry $sID with data from $aData in the database, changing only fields defined in $aFields.
// Redefine here since we don't have an id column, and we also need to select on the parent object.
global $_DB;
if (!trim($sID)) {
lovd_displayError('LOVD-Lib', $this->sObject . '::updateEntry() - Method didn\'t receive ID');
} elseif (!is_array($aData) || !count($aData)) {
lovd_displayError('LOVD-Lib', $this->sObject . '::updateEntry() - Method didn\'t receive data array');
} elseif (!is_array($aFields) || !count($aFields)) {
$aFields = array_keys($aData);
}
// Query text.
$sSQL = 'UPDATE ' . constant($this->sTable) . ' SET ';
$aSQL = array();
foreach ($aFields as $key => $sField) {
$sSQL .= (!$key ? '' : ', ') . '`' . $sField . '` = ?';
if ($aData[$sField] === '' && (substr(lovd_getColumnType(constant($this->sTable), $sField), 0, 3) == 'INT' || substr(lovd_getColumnType(constant($this->sTable), $sField), 0, 4) == 'DATE')) {
$aData[$sField] = NULL;
}
$aSQL[] = $aData[$sField];
}
$sSQL .= ' WHERE ' . $this->aTableInfo['unit'] . 'id = ? AND colid = ?';
$aSQL[] = $this->sObjectID;
$aSQL[] = $sID;
if (!defined('LOG_EVENT')) {
define('LOG_EVENT', $this->sObject . '::updateEntry()');
}
$q = $_DB->query($sSQL, $aSQL, true, true);
return $q->rowCount();
}
// Don't allow direct access.
if (!defined('ROOT_PATH')) {
exit;
}
// How are the versions related?
$sCalcVersionFiles = lovd_calculateVersion($_SETT['system']['version']);
$sCalcVersionDB = lovd_calculateVersion($_STAT['version']);
if ($sCalcVersionFiles != $sCalcVersionDB) {
// Version of files are not equal to version of database backend.
// Increased execution time to help perform large upgrades.
if ((int) ini_get('max_execution_time') < 60) {
@set_time_limit(60);
}
// DB version greater than file version... then we have a problem.
if ($sCalcVersionFiles < $sCalcVersionDB) {
lovd_displayError('UpgradeError', 'Database version ' . $_STAT['version'] . ' found newer than file version ' . $_SETT['system']['version']);
}
define('PAGE_TITLE', 'Upgrading LOVD...');
$_T->printHeader();
$_T->printTitle();
print ' Please wait while LOVD is upgrading the database backend from ' . $_STAT['version'] . ' to ' . $_SETT['system']['version'] . '.<BR><BR>' . "\n";
// Array of changes.
$aUpdates = array('3.0-pre-21' => array('UPGRADING TO 3.0-pre-21 IS NOT SUPPORTED. UNINSTALL LOVD 3.0 AND REINSTALL TO GET THE LATEST.'), '3.0-alpha-01' => array('ALTER TABLE ' . TABLE_INDIVIDUALS . ' DROP COLUMN edited_date', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD COLUMN edited_date DATETIME AFTER edited_by', 'ALTER TABLE ' . TABLE_VARIANTS . ' DROP COLUMN edited_date', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD COLUMN edited_date DATETIME AFTER edited_by', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' DROP COLUMN edited_date', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD COLUMN edited_date DATETIME AFTER edited_by', 'ALTER TABLE ' . TABLE_SCREENINGS . ' DROP COLUMN edited_date', 'ALTER TABLE ' . TABLE_SCREENINGS . ' ADD COLUMN edited_date DATETIME AFTER edited_by', 'ALTER TABLE ' . TABLE_GENES . ' MODIFY COLUMN id VARCHAR(20) NOT NULL', 'ALTER TABLE ' . TABLE_CURATES . ' MODIFY COLUMN geneid VARCHAR(20) NOT NULL', 'ALTER TABLE ' . TABLE_TRANSCRIPTS . ' MODIFY COLUMN geneid VARCHAR(20) NOT NULL', 'ALTER TABLE ' . TABLE_GEN2DIS . ' MODIFY COLUMN geneid VARCHAR(20) NOT NULL', 'ALTER TABLE ' . TABLE_SCR2GENE . ' MODIFY COLUMN geneid VARCHAR(20) NOT NULL', 'ALTER TABLE ' . TABLE_SHARED_COLS . ' MODIFY COLUMN geneid VARCHAR(20)', 'ALTER TABLE ' . TABLE_HITS . ' MODIFY COLUMN geneid VARCHAR(20) NOT NULL'), '3.0-alpha-02' => array('UPDATE ' . TABLE_COLS . ' SET select_options = "Unknown\\r\\nBESS = Base Excision Sequence Scanning\\r\\nCMC = Chemical Mismatch Cleavage\\r\\nCSCE = Conformation sensitive capillary electrophoresis\\r\\nDGGE = Denaturing-Gradient Gel-Electrophoresis\\r\\nDHPLC = Denaturing High-Performance Liquid Chromatography\\r\\nDOVAM = Detection Of Virtually All Mutations (SSCA variant)\\r\\nDSCA = Double-Strand DNA Conformation Analysis\\r\\nHD = HeteroDuplex analysis\\r\\nIHC = Immuno-Histo-Chemistry\\r\\nmPCR = multiplex PCR\\r\\nMAPH = Multiplex Amplifiable Probe Hybridisation\\r\\nMLPA = Multiplex Ligation-dependent Probe Amplification\\r\\nNGS = Next Generation Sequencing\\r\\nPAGE = Poly-Acrylamide Gel-Electrophoresis\\r\\nPCR = Polymerase Chain Reaction\\r\\nPTT = Protein Truncation Test\\r\\nRT-PCR = Reverse Transcription and PCR\\r\\nSEQ = SEQuencing\\r\\nSouthern = Southern Blotting\\r\\nSSCA = Single-Strand DNA Conformation Analysis (SSCP)\\r\\nWestern = Western Blotting" WHERE id = "Screening/Technique"', 'UPDATE ' . TABLE_COLS . ' SET mandatory = 1 WHERE id IN ("VariantOnTranscript/RNA", "VariantOnTranscript/Protein")', 'UPDATE ' . TABLE_SHARED_COLS . ' SET mandatory = 1 WHERE colid IN ("VariantOnTranscript/RNA", "VariantOnTranscript/Protein")', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD COLUMN panelid MEDIUMINT(8) UNSIGNED ZEROFILL AFTER id', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD CONSTRAINT ' . TABLE_INDIVIDUALS . '_fk_panelid FOREIGN KEY (panelid) REFERENCES ' . TABLE_INDIVIDUALS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD COLUMN panel_size MEDIUMINT UNSIGNED NOT NULL DEFAULT 1 AFTER panelid', 'DELETE FROM ' . TABLE_COLS . ' WHERE id = "VariantOnTranscript/DBID"', 'UPDATE ' . TABLE_COLS . ' SET description_form = "This ID is used to group multiple instances of the same variant together. The ID starts with the gene symbol of the transcript most influenced by the variant or otherwise the closest gene, followed by an underscore (_) and the ID code, usually six digits.", preg_pattern = "/^[A-Z][A-Z0-9]+_[0-9]{6}\\\\b/" WHERE id = "VariantOnGenome/DBID"', 'ALTER TABLE ' . TABLE_USERS . ' MODIFY COLUMN password CHAR(50) NOT NULL', 'ALTER TABLE ' . TABLE_USERS . ' MODIFY COLUMN password_autogen CHAR(50)', 'ALTER TABLE ' . TABLE_USERS . ' DROP COLUMN current_db'), '3.0-alpha-03' => array('UPDATE ' . TABLE_SOURCES . ' SET url = "http://www.omim.org/entry/{{ ID }}" WHERE id = "omim" AND url = "http://www.ncbi.nlm.nih.gov/omim/{{ ID }}"', 'UPDATE ' . TABLE_LINKS . ' SET replace_text = "<A href=\\"http://www.omim.org/entry/[1]#[2]\\" target=\\"_blank\\">(OMIM [2])</A>" WHERE id = 4 AND replace_text = "<A href=\\"http://www.ncbi.nlm.nih.gov/omim/[1]#[1]Variants[2]\\" target=\\"_blank\\">(OMIM [2])</A>"', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD COLUMN statusid TINYINT(1) UNSIGNED AFTER ownerid', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD INDEX (statusid)', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD CONSTRAINT ' . TABLE_PHENOTYPES . '_fk_statusid FOREIGN KEY (statusid) REFERENCES ' . TABLE_DATA_STATUS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' DROP COLUMN edited_date', 'UPDATE ' . TABLE_COLS . ' SET form_type = "ID||text|15" WHERE id = "VariantOnGenome/DBID" AND form_type = "ID||text|40"'), '3.0-alpha-04' => array('DELETE FROM ' . TABLE_DATA_STATUS . ' WHERE id IN (' . STATUS_IN_PROGRESS . ', ' . STATUS_PENDING . ')', 'INSERT INTO ' . TABLE_DATA_STATUS . ' VALUES (' . STATUS_IN_PROGRESS . ', "In progress")', 'INSERT INTO ' . TABLE_DATA_STATUS . ' VALUES (' . STATUS_PENDING . ', "Pending")', 'UPDATE ' . TABLE_COLS . ' SET description_form = "This ID is used to group multiple instances of the same variant together. The ID starts with the gene symbol of the transcript most influenced by the variant or otherwise the closest gene, followed by an underscore (_) and the ID code, which consists of six digits." WHERE id = "VariantOnGenome/DBID" AND description_form = "This ID is used to group multiple instances of the same variant together. The ID starts with the gene symbol of the transcript most influenced by the variant or otherwise the closest gene, followed by an underscore (_) and the ID code, usually six digits."'), '3.0-alpha-05' => array('ALTER TABLE ' . TABLE_INDIVIDUALS . ' DROP FOREIGN KEY ' . TABLE_INDIVIDUALS . '_fk_ownerid', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' DROP KEY ownerid', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' CHANGE ownerid owned_by SMALLINT(5) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD INDEX (owned_by)', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD CONSTRAINT ' . TABLE_INDIVIDUALS . '_fk_owned_by FOREIGN KEY (owned_by) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_VARIANTS . ' DROP FOREIGN KEY ' . TABLE_VARIANTS . '_fk_ownerid', 'ALTER TABLE ' . TABLE_VARIANTS . ' DROP KEY ownerid', 'ALTER TABLE ' . TABLE_VARIANTS . ' CHANGE ownerid owned_by SMALLINT(5) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD INDEX (owned_by)', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS . '_fk_owned_by FOREIGN KEY (owned_by) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' DROP FOREIGN KEY ' . TABLE_PHENOTYPES . '_fk_ownerid', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' DROP KEY ownerid', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' CHANGE ownerid owned_by SMALLINT(5) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD INDEX (owned_by)', 'ALTER TABLE ' . TABLE_PHENOTYPES . ' ADD CONSTRAINT ' . TABLE_PHENOTYPES . '_fk_owned_by FOREIGN KEY (owned_by) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_SCREENINGS . ' DROP FOREIGN KEY ' . TABLE_SCREENINGS . '_fk_ownerid', 'ALTER TABLE ' . TABLE_SCREENINGS . ' DROP KEY ownerid', 'ALTER TABLE ' . TABLE_SCREENINGS . ' CHANGE ownerid owned_by SMALLINT(5) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_SCREENINGS . ' ADD INDEX (owned_by)', 'ALTER TABLE ' . TABLE_SCREENINGS . ' ADD CONSTRAINT ' . TABLE_SCREENINGS . '_fk_owned_by FOREIGN KEY (owned_by) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE'), '3.0-alpha-07' => array('ALTER TABLE ' . TABLE_TRANSCRIPTS . ' ADD COLUMN id_mutalyzer TINYINT(3) UNSIGNED ZEROFILL AFTER name', 'ALTER TABLE ' . TABLE_SCREENINGS . ' ADD COLUMN variants_found BOOLEAN NOT NULL DEFAULT 1 AFTER individualid', 'ALTER TABLE ' . TABLE_VARIANTS . ' DROP FOREIGN KEY ' . TABLE_VARIANTS . '_fk_pathogenicid', 'ALTER TABLE ' . TABLE_VARIANTS . ' DROP KEY pathogenicid', 'ALTER TABLE ' . TABLE_VARIANTS . ' CHANGE pathogenicid effectid TINYINT(2) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD INDEX (effectid)', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' DROP FOREIGN KEY ' . TABLE_VARIANTS_ON_TRANSCRIPTS . '_fk_pathogenicid', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' DROP KEY pathogenicid', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' CHANGE pathogenicid effectid TINYINT(2) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' ADD INDEX (effectid)', 'RENAME TABLE ' . TABLE_PATHOGENIC . ' TO ' . TABLE_EFFECT, 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS . '_fk_effectid FOREIGN KEY (effectid) REFERENCES ' . TABLE_EFFECT . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS_ON_TRANSCRIPTS . '_fk_effectid FOREIGN KEY (effectid) REFERENCES ' . TABLE_EFFECT . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'UPDATE ' . TABLE_VARIANTS . ' SET effectid = 55 WHERE effectid < 11 OR effectid IS NULL', 'UPDATE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' SET effectid = 55 WHERE effectid < 11 OR effectid IS NULL', 'UPDATE ' . TABLE_LINKS . ' SET replace_text = "<A href=\\"http://www.ncbi.nlm.nih.gov/SNP/snp_ref.cgi?rs=[1]\\" target=\\"_blank\\">dbSNP</A>" WHERE id = 2 AND replace_text = "<A href=\\"http://www.ncbi.nlm.nih.gov/SNP/snp_ref.cgi?type=rs&rs=rs[1]\\" target=\\"_blank\\">dbSNP</A>"'), '3.0-alpha-07b' => array('UPDATE ' . TABLE_COLS . ' SET form_type = "ID|This ID is used to group multiple instances of the same variant together. The ID starts with the gene symbol of the transcript most influenced by the variant or otherwise the closest gene, followed by an underscore (_) and the 6 digit ID code.|text|20" WHERE id = "VariantOnGenome/DBID"', 'UPDATE ' . TABLE_COLS . ' SET description_form = "NOTE: This field will be predicted and filled in by LOVD, if left empty." WHERE id = "VariantOnGenome/DBID"', 'UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf\\\\d+-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf\\\\d+|[A-Z][A-Z0-9]+))_[0-9]{6}\\\\b/" WHERE id = "VariantOnGenome/DBID"', 'UPDATE ' . TABLE_COLS . ' SET description_legend_short = REPLACE(description_legend_short, "Database", "DataBase"), description_legend_full = REPLACE(description_legend_full, "Database", "DataBase") WHERE id = "VariantOnGenome/DBID"', 'INSERT INTO ' . TABLE_USERS . '(name, created_date) VALUES("LOVD", NOW())', 'UPDATE ' . TABLE_USERS . ' SET id = 0, created_by = 0 WHERE username = ""'), '3.0-alpha-07c' => array('ALTER TABLE ' . TABLE_VARIANTS . ' ADD COLUMN mapping_flags TINYINT(3) UNSIGNED NOT NULL AFTER type', 'ALTER TABLE ' . TABLE_USERS . ' AUTO_INCREMENT = 1', 'UPDATE ' . TABLE_COLS . ' SET edited_by = 0 WHERE id = "VariantOnGenome/DBID"', 'UPDATE ' . TABLE_COLS . ' SET width = 80 WHERE id = "VariantOnGenome/DBID"'), '3.0-alpha-07d' => array('ALTER TABLE ' . TABLE_GENES . ' MODIFY COLUMN chromosome VARCHAR(2)', 'ALTER TABLE ' . TABLE_GENES . ' ADD INDEX (chromosome)', 'ALTER TABLE ' . TABLE_VARIANTS . ' MODIFY COLUMN chromosome VARCHAR(2)', 'CREATE TABLE ' . TABLE_CHROMOSOMES . ' (name VARCHAR(2) NOT NULL, sort_id TINYINT(3) UNSIGNED NOT NULL, hg18_id_ncbi VARCHAR(20) NOT NULL, hg19_id_ncbi VARCHAR(20) NOT NULL, PRIMARY KEY (name)) ENGINE=InnoDB, DEFAULT CHARACTER SET utf8', 'chr_values' => 'Reserved for the insert query of the new chromosome table. This will be added later in this script.', 'ALTER TABLE ' . TABLE_GENES . ' ADD CONSTRAINT ' . TABLE_GENES . '_fk_chromosome FOREIGN KEY (chromosome) REFERENCES ' . TABLE_CHROMOSOMES . ' (name) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS . '_fk_chromosome FOREIGN KEY (chromosome) REFERENCES ' . TABLE_CHROMOSOMES . ' (name) ON DELETE SET NULL ON UPDATE CASCADE'), '3.0-beta-02' => array('UPDATE ' . TABLE_COLS . ' SET form_type = "Frequency||text|10" WHERE id = "VariantOnGenome/Frequency" AND form_type = "Frequency||text|15"', 'ALTER TABLE ' . TABLE_TRANSCRIPTS . ' ADD UNIQUE (id_ncbi)'), '3.0-beta-02b' => array('ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN proxy_host VARCHAR(255) NOT NULL AFTER refseq_build', 'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN proxy_port SMALLINT(5) UNSIGNED AFTER proxy_host'), '3.0-beta-02c' => array('ALTER TABLE ' . TABLE_GENES . ' ADD COLUMN imprinting VARCHAR(10) NOT NULL DEFAULT "unknown" AFTER chrom_band'), '3.0-beta-02d' => array('INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnGenome/Conservation_score/GERP", 4, 100, 0, 0, 0, "GERP conservation", "", "Conservation score as calculated by GERP.", "The Conservation score as calculated by GERP.", "DECIMAL(5,3)", "GERP conservation score||text|6", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnGenome/dbSNP", 8, 120, 0, 0, 0, "dbSNP ID", "", "The dbSNP ID.", "The dbSNP ID.", "VARCHAR(15)", "dbSNP ID|If available, please fill in the dbSNP ID, such as rs12345678.|text|10", "", "/^[rs]s\\\\d+$/", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnTranscript/Distance_to_splice_site", 10, 150, 0, 0, 0, "Splice distance", "", "The distance to the nearest splice site.", "The distance to the nearest splice site.", "MEDIUMINT(8) UNSIGNED", "Distance to splice site||text|8", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnTranscript/GVS/Function", 9, 200, 0, 0, 0, "GVS function", "", "Functional annotation of this position by GVS.", "The functional annotation of this position from the Genome Variation Server.", "VARCHAR(30)", "GVS function|Whether the variant is missense, nonsense, in an intron, UTR, etc.|text|30", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnTranscript/PolyPhen", 8, 200, 0, 0, 0, "PolyPhen prediction", "", "The effect predicted by PolyPhen.", "The effect predicted by PolyPhen.", "VARCHAR(20)", "PolyPhen prediction||select|1|true|false|false", "benign = Benign\\r\\npossiblyDamaging = Possably damaging\\r\\nprobablyDamaging = Probably damaging\\r\\nnoPrediction = No prediction", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT INTO ' . TABLE_COLS . ' VALUES ("VariantOnTranscript/Position", 5, 100, 0, 0, 0, "Position", "", "Position in cDNA sequence.", "The position of this variant in the cDNA sequence.", "MEDIUMINT(5)", "cDNA Position||text|5", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)'), '3.0-beta-03b' => array('UPDATE ' . TABLE_LINKS . ' SET description = CONCAT(description, "\\r\\n\\r\\nExamples:\\r\\n{PMID:Fokkema et. al.:15977173}\\r\\n{PMID:Fokkema et. al.:21520333}") WHERE name = "PubMed" AND description = "Links to abstracts in the PubMed database.\\r\\n[1] = The name of the author(s).\\r\\n[2] = The PubMed ID."', 'UPDATE ' . TABLE_LINKS . ' SET description = CONCAT(description, "\\r\\n\\r\\nExamples:\\r\\n{dbSNP:rs193143796}\\r\\n{dbSNP:193143796}") WHERE name = "DbSNP" AND description = "Links to the DbSNP database.\\r\\n[1] = The DbSNP ID."', 'UPDATE ' . TABLE_LINKS . ' SET description = CONCAT(description, "\\r\\n\\r\\nExamples:\\r\\n{GenBank:NG_012232.1}\\r\\n{GenBank:NC_000001.10}") WHERE name = "GenBank" AND description = "Links to GenBank sequences.\\r\\n[1] = The GenBank ID."', 'UPDATE ' . TABLE_LINKS . ' SET description = CONCAT(description, "\\r\\n\\r\\nExamples:\\r\\n{OMIM:300377:0021}\\r\\n{OMIM:188840:0003}") WHERE name = "OMIM" AND description = "Links to an allelic variant on the gene\'s OMIM page.\\r\\n[1] = The OMIM gene ID.\\r\\n[2] = The number of the OMIM allelic variant on that page."', 'UPDATE ' . TABLE_COLS . ' SET id = "VariantOnGenome/Published_as", head_column = "Published as", form_type = REPLACE(form_type, "DNA published", "Published as") WHERE id = "VariantOnGenome/DNA_published"', 'UPDATE ' . TABLE_COLS . ' SET id = "VariantOnTranscript/Published_as", head_column = "Published as", form_type = REPLACE(form_type, "DNA published", "Published as") WHERE id = "VariantOnTranscript/DNA_published"'), '3.0-beta-03c' => array('INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Individual/Consanguinity", 249, 40, 0, 0, 0, "Consanguinity", "Indicates whether the parents are related (consanguineous), not related (non-consanguineous) or whether consanguinity is not known (unknown)", "Indicates whether the parents are related (consanguineous), not related (non-consanguineous) or whether consanguinity is not known (unknown)", "Indicates whether the parents are related (consanguineous), not related (non-consanguineous) or whether consanguinity is not known (unknown)", "VARCHAR(5)", "Consanguinity||select|1|--Not specified--|false|false", "? = Unknown\\r\\nno = Non-consanguineous parents\\r\\nyes = Consanguineous parents", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Date", 255, 80, 0, 0, 0, "Date", "Format: YYYY-MM-DD.", "Date the phenotype was observed.", "Date the phenotype was observed, in YYYY-MM-DD format.", "DATE", "Date||text|10", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Inheritance", 254, 200, 0, 0, 0, "Inheritance", "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)", "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)", "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)", "VARCHAR(25)", "Inheritance||select|1|--Not specified--|false|false", "Unknown\\r\\nFamilial\\r\\nFamilial, autosomal dominant\\r\\nFamilial, autosomal recessive\\r\\nFamilial, X-linked dominant\\r\\nFamilial, X-linked dominant, male sparing\\r\\nFamilial, X-linked recessive\\r\\nPaternal, Y-linked\\r\\nMaternal, mitochondrial\\r\\nIsolated (sporadic)", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("VariantOnGenome/Genetic_origin", 11, 200, 0, 0, 0, "Genetic origin", "Origin of variant; unknown, germline (i.e. inherited), somatic, de novo, from parental disomy (maternal or paternal) or in vitro (cloned)", "Origin of variant; unknown, germline (i.e. inherited), somatic, de novo, from parental disomy (maternal or paternal) or in vitro (cloned)", "Origin of variant; unknown, germline (i.e. inherited), somatic, de novo, from parental disomy (maternal or paternal) or in vitro (cloned)", "VARCHAR(40)", "Genetic origin||select|1|--Not specified--|false|false", "Unknown\\r\\n\\r\\nGermline (inherited)\\r\\nSomatic\\r\\nDe novo\\r\\nUniparental disomy\\r\\nUniparental disomy, maternal allele\\r\\nUniparental disomy, paternal allele", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("VariantOnGenome/Segregation", 12, 40, 0, 0, 0, "Segregation", "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)", "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)", "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)", "VARCHAR(5)", "Segregation||select|1|--Not specified--|false|false", "? = Unknown\\r\\nyes = Segregates with disease\\r\\nno = Does not segregate with disease", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'ALTER TABLE ' . TABLE_GENES . ' ADD INDEX (id_hgnc)'), '3.0-beta-03d' => array('ALTER TABLE ' . TABLE_EFFECT . ' MODIFY COLUMN id TINYINT(2) UNSIGNED', 'ALTER TABLE ' . TABLE_VARIANTS . ' MODIFY COLUMN effectid TINYINT(2) UNSIGNED', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' MODIFY COLUMN effectid TINYINT(2) UNSIGNED', 'CREATE TABLE ' . TABLE_ALLELES . ' (id TINYINT(2) UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, display_order TINYINT(1) UNSIGNED NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB, DEFAULT CHARACTER SET utf8', 'allele_values' => 'Reserved for the insert query of the new allele table. This will be added later in this script.', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS . '_fk_allele FOREIGN KEY (allele) REFERENCES ' . TABLE_ALLELES . ' (id) ON UPDATE CASCADE', 'UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf\\\\d+-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf\\\\d+|[A-Z][A-Z0-9]+))_[0-9]{6}$/" WHERE id = "VariantOnGenome/DBID"'), '3.0-beta-04' => array('ALTER TABLE ' . TABLE_GENES . ' DROP INDEX id_hgnc', 'ALTER TABLE ' . TABLE_GENES . ' ADD UNIQUE (id_hgnc)', 'ALTER TABLE ' . TABLE_EFFECT . ' MODIFY COLUMN id TINYINT(2) UNSIGNED NOT NULL', 'UPDATE ' . TABLE_CONFIG . ' SET proxy_port = NULL WHERE proxy_port = 0'), '3.0-beta-05' => array('UPDATE ' . TABLE_COLS . ' SET mysql_type = "VARCHAR(50)" WHERE id = "Phenotype/Inheritance"', 'DELETE FROM ' . TABLE_COLS2LINKS . ' WHERE colid IN (SELECT id FROM ' . TABLE_COLS . ' WHERE mysql_type NOT REGEXP "^(VARCHAR|TEXT)" OR id = "VariantOnGenome/DBID")', 'UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^[rs]s\\\\d+$/" WHERE id = "VariantOnGenome/dbSNP"', 'UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf\\\\d+-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf\\\\d+|[A-Z][A-Z0-9]+))_\\\\d{6}$/" WHERE id = "VariantOnGenome/DBID"'), '3.0-beta-06' => array('UPDATE ' . TABLE_COLS . ' SET col_order = 240 WHERE id = "Individual/Consanguinity" AND col_order = 249', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Individual/Death/Cause", 249, 150, 0, 0, 0, "Cause of death", "", "The cause of the individual\'s death, if known and applicable.", "The cause of the individual\'s death, if known and applicable.", "VARCHAR(255)", "Cause of death|The cause of the individual\'s death, if known and applicable.|text|30", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Individual/Age_of_death", 248, 100, 0, 0, 0, "Age of death", "Type 35y for 35 years, 04y08m for 4 years and 8 months, 18y? for around 18 years, >54y for still alive at 55, ? for unknown.", "The age at which the individual deceased, if known and applicable. 04y08m = 4 years and 8 months.", "The age at which the individual deceased, if known and applicable.\\r\\n<UL style=\\"margin-top:0px;\\">\\r\\n <LI>35y = 35 years</LI>\\r\\n <LI>04y08m = 4 years and 8 months</LI>\\r\\n <LI>18y? = around 18 years</LI>\\r\\n <LI>>54y = still alive at 55</LI>\\r\\n <LI>? = unknown</LI>\\r\\n</UL>", "VARCHAR(12)", "Age of death|The age at which the individual deceased, if known and applicable. Numbers lower than 10 should be prefixed by a zero and the field should always begin with years, to facilitate sorting on this column.|text|10", "", "/^([<>]?\\\\d{2,3}y(\\\\d{2}m(\\\\d{2}d)?)?)?\\\\??$/", 1, 1, 1, 0, NOW(), NULL, NULL)', 'UPDATE ' . TABLE_COLS . ' SET col_order = 1 WHERE id = "Phenotype/Date" AND col_order = 255', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Age", 2, 100, 0, 0, 0, "Age examined", "Type 35y for 35 years, 04y08m for 4 years and 8 months, 18y? for around 18 years, >54y for older than 54, ? for unknown.", "The age at which the individual was examined, if known. 04y08m = 4 years and 8 months.", "The age at which the individual was examined, if known.\\r\\n<UL style=\\"margin-top:0px;\\">\\r\\n <LI>35y = 35 years</LI>\\r\\n <LI>04y08m = 4 years and 8 months</LI>\\r\\n <LI>18y? = around 18 years</LI>\\r\\n <LI>>54y = older than 54</LI>\\r\\n <LI>? = unknown</LI>\\r\\n</UL>", "VARCHAR(12)", "Age at examination|The age at which the individual was examined, if known. Numbers lower than 10 should be prefixed by a zero and the field should always begin with years, to facilitate sorting on this column.|text|10", "", "/^([<>]?\\\\d{2,3}y(\\\\d{2}m(\\\\d{2}d)?)?)?\\\\??$/", 1, 1, 1, 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Length", 200, 100, 0, 0, 0, "Length", "", "Length of the individual, in cm.", "Length of the individual, in centimeters (cm).", "SMALLINT(3) UNSIGNED", "Length of individual (cm)|Length of individual, in centimeters.|text|3", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'UPDATE ' . TABLE_LINKS . ' SET description = "Links to abstracts in the PubMed database.\\r\\n[1] = The name of the author(s), possibly including year.\\r\\n[2] = The PubMed ID.\\r\\n\\r\\nExample:\\r\\n{PMID:Fokkema et al. (2011):21520333}" WHERE id = 1 AND name = "PubMed"', 'UPDATE ' . TABLE_COLS . ' SET head_column = "DNA change (genomic)" WHERE id = "VariantOnGenome/DNA"', 'UPDATE ' . TABLE_COLS . ' SET head_column = "DNA change (cDNA)" WHERE id = "VariantOnTranscript/DNA"', 'UPDATE ' . TABLE_COLS . ' SET select_options = "Unknown\\r\\nGermline (inherited)\\r\\nSomatic\\r\\nDe novo\\r\\nUniparental disomy\\r\\nUniparental disomy, maternal allele\\r\\nUniparental disomy, paternal allele" WHERE id = "VariantOnGenome/Genetic_origin"', 'UPDATE ' . TABLE_TRANSCRIPTS . ' SET position_c_mrna_start = 0, position_c_mrna_end = 0, position_c_cds_end = 0, position_g_mrna_start = 0, position_g_mrna_end = 0'), '3.0-beta-07' => array('ALTER TABLE ' . TABLE_COLS . ' MODIFY COLUMN form_type TEXT NOT NULL', 'UPDATE ' . TABLE_COLS . ' SET select_options = "? = Unknown\\r\\nF = Female\\r\\nM = Male\\r\\nrF = Raised as female\\r\\nrM = Raised as male" WHERE id = "Individual/Gender" and select_options = "Female\\r\\nMale\\r\\nUnknown"', 'UPDATE ' . TABLE_COLS . ' SET form_type = "Geographic origin|If mixed, please indicate origin of father and mother, if known.|text|30" WHERE id = "Individual/Origin/Geographic" and select_options = "Geographic origin||text|30"', 'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Additional", 250, 200, 0, 1, 0, "Phenotype details", "Additional information on the phenotype of the individual.", "Additional information on the phenotype of the individual.", "Additional information on the phenotype of the individual.", "TEXT", "Additional phenotype details||textarea|40|4", "", "", 1, 1, 1, 0, NOW(), NULL, NULL)', 'UPDATE ' . TABLE_COLS . ' SET hgvs = 1, standard = 1 WHERE id = "Phenotype/Inheritance"', 'UPDATE ' . TABLE_COLS . ' SET select_options = "? = Unknown\\r\\narrayCGH = array for Comparative Genomic Hybridisation\\r\\narraySEQ = array for resequencing\\r\\narraySNP = array for SNP typing\\r\\narrayCNV = array for Copy Number Variation (SNP and CNV probes)\\r\\nBESS = Base Excision Sequence Scanning\\r\\nCMC = Chemical Mismatch Cleavage\\r\\nCSCE = Conformation Sensitive Capillary Electrophoresis\\r\\nDGGE = Denaturing-Gradient Gel-Electrophoresis\\r\\nDHPLC = Denaturing High-Performance Liquid Chromatography\\r\\nDOVAM = Detection Of Virtually All Mutations (SSCA variant)\\r\\nddF = dideoxy Fingerprinting\\r\\nDSCA = Double-Strand DNA Conformation Analysis\\r\\nEMC = Enzymatic Mismatch Cleavage\\r\\nHD = HeteroDuplex analysis\\r\\nMCA = high-resolution Melting Curve Analysis (hrMCA)\\r\\nIHC = Immuno-Histo-Chemistry\\r\\nMAPH = Multiplex Amplifiable Probe Hybridisation\\r\\nMLPA = Multiplex Ligation-dependent Probe Amplification\\r\\nSEQ-NG = Next-Generation Sequencing\\r\\nSEQ-NG-H = Next-Generation Sequencing - Helicos\\r\\nSEQ-NG-I = Next-Generation Sequencing - Illumina/Solexa\\r\\nSEQ-NG-R = Next-Generation Sequencing - Roche/454\\r\\nSEQ-NG-S = Next-Generation Sequencing - SOLiD\\r\\nNorthern = Northern blotting\\r\\nPCR = Polymerase Chain Reaction\\r\\nPCRdig = PCR + restriction enzyme digestion\\r\\nPCRlr = PCR, long-range\\r\\nPCRm = PCR, multiplex\\r\\nPCRq = PCR, quantitative\\r\\nPAGE = Poly-Acrylamide Gel-Electrophoresis\\r\\nPTT = Protein Truncation Test\\r\\nPFGE = Pulsed-Field Gel-Electrophoresis (+Southern)\\r\\nRT-PCR = Reverse Transcription and PCR\\r\\nSEQ = SEQuencing\\r\\nSBE = Single Base Extension\\r\\nSSCA = Single-Strand DNA Conformation polymorphism Analysis (SSCP)\\r\\nSSCAf = SSCA, fluorescent (SSCP)\\r\\nSouthern = Southern blotting\\r\\nTaqMan = TaqMan assay\\r\\nWestern = Western Blotting" WHERE id = "Screening/Technique" and select_options = "Unknown\\r\\nBESS = Base Excision Sequence Scanning\\r\\nCMC = Chemical Mismatch Cleavage\\r\\nCSCE = Conformation sensitive capillary electrophoresis\\r\\nDGGE = Denaturing-Gradient Gel-Electrophoresis\\r\\nDHPLC = Denaturing High-Performance Liquid Chromatography\\r\\nDOVAM = Detection Of Virtually All Mutations (SSCA variant)\\r\\nDSCA = Double-Strand DNA Conformation Analysis\\r\\nHD = HeteroDuplex analysis\\r\\nIHC = Immuno-Histo-Chemistry\\r\\nmPCR = multiplex PCR\\r\\nMAPH = Multiplex Amplifiable Probe Hybridisation\\r\\nMLPA = Multiplex Ligation-dependent Probe Amplification\\r\\nNGS = Next Generation Sequencing\\r\\nPAGE = Poly-Acrylamide Gel-Electrophoresis\\r\\nPCR = Polymerase Chain Reaction\\r\\nPTT = Protein Truncation Test\\r\\nRT-PCR = Reverse Transcription and PCR\\r\\nSEQ = SEQuencing\\r\\nSouthern = Southern Blotting\\r\\nSSCA = Single-Strand DNA Conformation Analysis (SSCP)\\r\\nWestern = Western Blotting"', 'UPDATE ' . TABLE_COLS . ' SET select_options = "DNA\\r\\nRNA = RNA (cDNA)\\r\\nProtein\\r\\n? = unknown" WHERE id = "Screening/Template" and select_options = "DNA\\r\\nRNA\\r\\nProtein"', 'UPDATE ' . TABLE_COLS . ' SET mandatory = 1 WHERE id = "VariantOnGenome/Genetic_origin"', 'UPDATE ' . TABLE_COLS . ' SET form_type = "GVS function||select|1|true|false|false", select_options = "intergenic\\r\\nnear-gene-5\\r\\nutr-5\\r\\ncoding\\r\\ncoding-near-splice\\r\\ncodingComplex\\r\\ncodingComplex-near-splice\\r\\nframeshift\\r\\nframeshift-near-splice\\r\\nsplice-5\\r\\nintron\\r\\nsplice-3\\r\\nutr-3\\r\\nnear-gene-3" WHERE id = "VariantOnTranscript/GVS/Function" and form_type LIKE "GVS function|%|text|%"', 'UPDATE ' . TABLE_COLS . ' SET form_type = "Protein change (HGVS format)|Description of variant at protein level (following HGVS recommendations); e.g. p.(Arg345Pro) = change predicted from DNA (RNA not analysed), p.Arg345Pro = change derived from RNA analysis, p.0 (no protein produced), p.? (unknown effect).|text|30" WHERE id = "VariantOnTranscript/Protein" and form_type LIKE "Protein change (HGVS format)||text|%"', 'UPDATE ' . TABLE_COLS . ' SET form_type = "RNA change (HGVS format)|Description of variant at RNA level (following HGVS recommendations); e.g. r.123c>u, r.? = unknown, r.(?) = RNA not analysed but probably transcribed copy of DNA variant, r.spl? = RNA not analysed but variant probably affects splicing, r.(spl?) = RNA not analysed but variant may affect splicing.|text|30" WHERE id = "VariantOnTranscript/RNA" and form_type LIKE "RNA change (HGVS format)||text|%"', 'INSERT INTO ' . TABLE_DISEASES . ' (symbol, name, created_by, created_date) VALUES ("Healthy/Control", "Healthy individual / control", 0, NOW())', 'UPDATE ' . TABLE_DISEASES . ' SET id = 0 WHERE id_omim IS NULL AND created_by = 0 AND symbol = "Healthy/Control"'), '3.0-beta-08' => array('UPDATE ' . TABLE_COLS . ' SET width = 120 WHERE id = "VariantOnGenome/DBID" AND width < 120', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD COLUMN fatherid MEDIUMINT(8) UNSIGNED ZEROFILL AFTER id', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD COLUMN motherid MEDIUMINT(8) UNSIGNED ZEROFILL AFTER fatherid', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD INDEX (fatherid)', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD INDEX (motherid)', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD CONSTRAINT ' . TABLE_INDIVIDUALS . '_fk_fatherid FOREIGN KEY (fatherid) REFERENCES ' . TABLE_INDIVIDUALS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_INDIVIDUALS . ' ADD CONSTRAINT ' . TABLE_INDIVIDUALS . '_fk_motherid FOREIGN KEY (motherid) REFERENCES ' . TABLE_INDIVIDUALS . ' (id) ON DELETE SET NULL ON UPDATE CASCADE', 'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN omim_apikey VARCHAR(40) NOT NULL AFTER mutalyzer_soap_url', 'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN proxy_username VARCHAR(255) NOT NULL AFTER proxy_port', 'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN proxy_password VARCHAR(255) NOT NULL AFTER proxy_username'), '3.0-beta-09' => array('UPDATE ' . TABLE_CONFIG . ' SET logo_uri = "gfx/LOVD3_logo145x50.jpg" WHERE logo_uri = "gfx/LOVD_logo130x50.jpg"'), '3.0-beta-09b' => array('ALTER TABLE ' . TABLE_USERS . ' ADD COLUMN orcid_id CHAR(19) AFTER id', 'ALTER TABLE ' . TABLE_USERS . ' ADD UNIQUE (orcid_id)'), '3.0-beta-09c' => array('UPDATE ' . TABLE_COLS . ' SET description_form = "Indicates whether the variant segregates with the phenotype (yes), does not segregate with the phenotype (no) or segregation is unknown (?)" WHERE id = "VariantOnGenome/Segregation" AND description_form = "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)"', 'UPDATE ' . TABLE_COLS . ' SET description_legend_short = "Indicates whether the variant segregates with the phenotype (yes), does not segregate with the phenotype (no) or segregation is unknown (?)" WHERE id = "VariantOnGenome/Segregation" AND description_legend_short = "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)"', 'UPDATE ' . TABLE_COLS . ' SET description_legend_full = "Indicates whether the variant segregates with the phenotype (yes), does not segregate with the phenotype (no) or segregation is unknown (?)" WHERE id = "VariantOnGenome/Segregation" AND description_legend_full = "Indicates whether the variant segregates with the disease (yes), does not segregate with the disease (no) or segregation is unknown (?)"', 'ALTER TABLE ' . TABLE_VARIANTS . ' MODIFY COLUMN mapping_flags TINYINT(3) UNSIGNED NOT NULL DEFAULT 0'), '3.0-beta-09d' => array('INSERT IGNORE INTO ' . TABLE_LINKS . ' VALUES (NULL, "DOI", "{DOI:[1]:[2]}", "<A href=\\"http://dx.doi.org/[2]\\" target=\\"_blank\\">[1]</A>", "Links directly to an article using the DOI.\\r\\n[1] = The name of the author(s), possibly followed by the year of publication.\\r\\n[2] = The DOI.\\r\\n\\r\\nExample:\\r\\n{DOI:Fokkema et al. (2011):10.1002/humu.21438}", 0, NOW(), NULL, NULL)', 'INSERT IGNORE INTO ' . TABLE_COLS2LINKS . ' VALUES ("Individual/Reference", LAST_INSERT_ID())', 'UPDATE ' . TABLE_LINKS . ' SET description = "Links to abstracts in the PubMed database.\\r\\n[1] = The name of the author(s), possibly followed by the year of publication.\\r\\n[2] = The PubMed ID.\\r\\n\\r\\nExample:\\r\\n{PMID:Fokkema et al. (2011):21520333}", edited_by = 0, edited_date = NOW() WHERE id = 1 AND (description = "Links to abstracts in the PubMed database.\\r\\n[1] = The name of the author(s).\\r\\n[2] = The PubMed ID.\\r\\n\\r\\nExample:\\r\\n{PMID:Fokkema et al. (2011):21520333}" OR description = "Links to abstracts in the PubMed database.\\r\\n[1] = The name of the author(s), possibly including year.\\r\\n[2] = The PubMed ID.\\r\\n\\r\\nExample:\\r\\n{PMID:Fokkema et al. (2011):21520333}")'), '3.0-beta-10b' => array('ALTER TABLE ' . TABLE_USERS . ' ADD COLUMN orcid_confirmed BOOLEAN NOT NULL DEFAULT 0 AFTER orcid_id', 'ALTER TABLE ' . TABLE_USERS . ' ADD COLUMN email_confirmed BOOLEAN NOT NULL DEFAULT 0 AFTER email', 'UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*|[A-Z][A-Z0-9]+))_\\\\d{6}$/" WHERE preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf\\\\d+-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf\\\\d+|[A-Z][A-Z0-9]+))_\\\\d{6}$/" AND id = "VariantOnGenome/DBID"', 'UPDATE ' . TABLE_COLS . ' SET form_type = "Genomic DNA change (HGVS format)|Description of variant at DNA level, based on the genomic DNA reference sequence (following HGVS recommendations); e.g. g.12345678C>T, g.12345678_12345890del, g.12345678_12345890dup.|text|30" WHERE form_type = "Genomic DNA change (HGVS format)||text|30" AND id = "VariantOnGenome/DNA"'), '3.0-02' => array('INSERT IGNORE INTO ' . TABLE_COLS2LINKS . ' VALUES ("VariantOnGenome/Reference", 001)'), '3.0-04' => array('INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Phenotype/Age/Onset", 1, 100, 0, 0, 0, "Age of onset", "Type 35y for 35 years, 04y08m for 4 years and 8 months, 18y? for around 18 years, >54y for older than 54, ? for unknown.", "The age at which the first symptoms of the disease appeared in the individual, if known. 04y08m = 4 years and 8 months.", "The age at which the first symptoms appeared in the individual, if known.\\r\\n<UL style=\\"margin-top:0px;\\">\\r\\n <LI>35y = 35 years</LI>\\r\\n <LI>04y08m = 4 years and 8 months</LI>\\r\\n <LI>18y? = around 18 years</LI>\\r\\n <LI>>54y = older than 54</LI>\\r\\n <LI>? = unknown</LI>\\r\\n</UL>", "VARCHAR(12)", "Age of onset|The age at which the first symptoms appeared in the individual, if known. Numbers lower than 10 should be prefixed by a zero and the field should always begin with years, to facilitate sorting on this column.|text|10", "", "/^([<>]?\\\\d{2,3}y(\\\\d{2}m(\\\\d{2}d)?)?)?\\\\??$/", 1, 1, 1, 0, NOW(), NULL, NULL)', 'UPDATE ' . TABLE_COLS . ' SET description_form = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial), isolated (sporadic) or complex" WHERE description_form = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)" AND id = "Phenotype/Inheritance"', 'UPDATE ' . TABLE_COLS . ' SET description_legend_short = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial), isolated (sporadic) or complex" WHERE description_legend_short = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)" AND id = "Phenotype/Inheritance"', 'UPDATE ' . TABLE_COLS . ' SET description_legend_full = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial), isolated (sporadic) or complex" WHERE description_legend_full = "Indicates the inheritance of the phenotype in the family; unknown, familial (autosomal/X-linked, dominant/ recessive), paternal (Y-linked), maternal (mitochondrial) or isolated (sporadic)" AND id = "Phenotype/Inheritance"', 'UPDATE ' . TABLE_COLS . ' SET select_options = "Unknown\\r\\nFamilial\\r\\nFamilial, autosomal dominant\\r\\nFamilial, autosomal recessive\\r\\nFamilial, X-linked dominant\\r\\nFamilial, X-linked dominant, male sparing\\r\\nFamilial, X-linked recessive\\r\\nPaternal, Y-linked\\r\\nMaternal, mitochondrial\\r\\nIsolated (sporadic)\\r\\nComplex" WHERE select_options = "Unknown\\r\\nFamilial\\r\\nFamilial, autosomal dominant\\r\\nFamilial, autosomal recessive\\r\\nFamilial, X-linked dominant\\r\\nFamilial, X-linked dominant, male sparing\\r\\nFamilial, X-linked recessive\\r\\nPaternal, Y-linked\\r\\nMaternal, mitochondrial\\r\\nIsolated (sporadic)" AND id = "Phenotype/Inheritance"'), '3.0-05' => array('ALTER TABLE ' . TABLE_GENES . ' MODIFY COLUMN id VARCHAR(25) NOT NULL', 'ALTER TABLE ' . TABLE_CURATES . ' MODIFY COLUMN geneid VARCHAR(25) NOT NULL', 'ALTER TABLE ' . TABLE_TRANSCRIPTS . ' MODIFY COLUMN geneid VARCHAR(25) NOT NULL', 'ALTER TABLE ' . TABLE_GEN2DIS . ' MODIFY COLUMN geneid VARCHAR(25) NOT NULL', 'ALTER TABLE ' . TABLE_SCR2GENE . ' MODIFY COLUMN geneid VARCHAR(25) NOT NULL', 'ALTER TABLE ' . TABLE_SHARED_COLS . ' MODIFY COLUMN geneid VARCHAR(25)', 'DROP TABLE ' . TABLE_HITS), '3.0-07' => array('UPDATE ' . TABLE_COLS . ' SET description_legend_short = REPLACE(description_legend_short, "/76 chomosomes", "/760 chromosomes"), description_legend_full = REPLACE(description_legend_full, "/76 chomosomes", "/760 chromosomes"), form_type = REPLACE(form_type, "/76 chomosomes", "/760 chromosomes") WHERE id = "VariantOnGenome/Frequency"'), '3.0-07b' => array('UPDATE ' . TABLE_COLS . ' SET standard = 0 WHERE id = "VariantOnGenome/Restriction_site"', 'ALTER TABLE ' . TABLE_VARIANTS . ' ADD COLUMN average_frequency FLOAT UNSIGNED AFTER mapping_flags'), '3.0-07c' => array('ALTER TABLE ' . TABLE_VARIANTS . ' ADD INDEX (average_frequency)'), '3.0-10b' => array('UPDATE ' . TABLE_COLS . ' SET preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*|[A-Z][A-Z0-9-]+))_\\\\d{6}$/" WHERE id = "VariantOnGenome/DBID" AND preg_pattern = "/^(chr(\\\\d{1,2}|[XYM])|(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*-|[A-Z][A-Z0-9]+-)?(C(\\\\d{1,2}|[XYM])orf[\\\\d][\\\\dA-Z]*|[A-Z][A-Z0-9]+))_\\\\d{6}$/"'), '3.0-10c' => array('UPDATE ' . TABLE_LOGS . ' SET name = "Event" WHERE name = "Error" AND event = "ColEdit" AND log LIKE "Column % reset to new defaults%"', 'INSERT IGNORE INTO ' . TABLE_EFFECT . ' VALUES ("00", "./."), ("01", "./-"), ("03", "./-?"), ("05", "./?"), ("07", "./+?"), ("09", "./+"), ("10", "-/."), ("30", "-?/."), ("50", "?/."), ("70", "+?/."), ("90", "+/.")', 'ALTER TABLE ' . TABLE_EFFECT . ' MODIFY COLUMN id TINYINT(2) UNSIGNED ZEROFILL NOT NULL', 'ALTER TABLE ' . TABLE_VARIANTS . ' MODIFY COLUMN effectid TINYINT(2) UNSIGNED ZEROFILL', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' MODIFY COLUMN effectid TINYINT(2) UNSIGNED ZEROFILL'), '3.0-10d' => array('ALTER TABLE ' . TABLE_CONFIG . ' MODIFY COLUMN mutalyzer_soap_url VARCHAR(100) NOT NULL DEFAULT "https://mutalyzer.nl/services"', 'UPDATE ' . TABLE_CONFIG . ' SET mutalyzer_soap_url = "https://mutalyzer.nl/services" WHERE mutalyzer_soap_url = "http://www.mutalyzer.nl/2.0/services"'), '3.0-11b' => array('INSERT INTO ' . TABLE_SOURCES . ' VALUES ("pubmed_gene", "http://www.ncbi.nlm.nih.gov/pubmed?LinkName=gene_pubmed&from_uid={{ ID }}")'), '3.0-11c' => array('UPDATE ' . TABLE_COUNTRIES . ' SET name = "Libya" WHERE id = "LY" AND name = "Libyan Arab Jamahiriya"', 'UPDATE ' . TABLE_COUNTRIES . ' SET name = "Saint Helena, Ascension and Tristan da Cunha" WHERE id = "SH" AND name = "Saint Helena"', 'INSERT INTO ' . TABLE_COUNTRIES . ' VALUES ("SS", "South Sudan")'), '3.0-11d' => array('UPDATE ' . TABLE_CONFIG . ' SET mutalyzer_soap_url = "https://mutalyzer.nl/services" WHERE mutalyzer_soap_url = "http://www.mutalyzer.nl/2.0/services"'), '3.0-13' => array('UPDATE ' . TABLE_COLS . ' SET select_options = "intergenic\\r\\nnear-gene-5\\r\\nutr-5\\r\\ncoding\\r\\ncoding-near-splice\\r\\ncoding-synonymous\\r\\ncoding-synonymous-near-splice\\r\\ncodingComplex\\r\\ncodingComplex-near-splice\\r\\nframeshift\\r\\nframeshift-near-splice\\r\\nmissense\\r\\nmissense-near-splice\\r\\nsplice-5\\r\\nintron\\r\\nsplice-3\\r\\nstop-gained\\r\\nstop-gained-near-splice\\r\\nstop-lost\\r\\nstop-lost-near-splice\\r\\nutr-3\\r\\nnear-gene-3" WHERE select_options = "intergenic\\r\\nnear-gene-5\\r\\nutr-5\\r\\ncoding\\r\\ncoding-near-splice\\r\\ncodingComplex\\r\\ncodingComplex-near-splice\\r\\nframeshift\\r\\nframeshift-near-splice\\r\\nsplice-5\\r\\nintron\\r\\nsplice-3\\r\\nutr-3\\r\\nnear-gene-3" AND id = "VariantOnTranscript/GVS/Function"', 'UPDATE ' . TABLE_SHARED_COLS . ' SET select_options = "intergenic\\r\\nnear-gene-5\\r\\nutr-5\\r\\ncoding\\r\\ncoding-near-splice\\r\\ncoding-synonymous\\r\\ncoding-synonymous-near-splice\\r\\ncodingComplex\\r\\ncodingComplex-near-splice\\r\\nframeshift\\r\\nframeshift-near-splice\\r\\nmissense\\r\\nmissense-near-splice\\r\\nsplice-5\\r\\nintron\\r\\nsplice-3\\r\\nstop-gained\\r\\nstop-gained-near-splice\\r\\nstop-lost\\r\\nstop-lost-near-splice\\r\\nutr-3\\r\\nnear-gene-3" WHERE select_options = "intergenic\\r\\nnear-gene-5\\r\\nutr-5\\r\\ncoding\\r\\ncoding-near-splice\\r\\ncodingComplex\\r\\ncodingComplex-near-splice\\r\\nframeshift\\r\\nframeshift-near-splice\\r\\nsplice-5\\r\\nintron\\r\\nsplice-3\\r\\nutr-3\\r\\nnear-gene-3" AND colid = "VariantOnTranscript/GVS/Function"'), '3.0-14' => array('ALTER TABLE ' . TABLE_DISEASES . ' MODIFY COLUMN symbol VARCHAR(25) NOT NULL'), '3.0-14b' => array('ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' DROP FOREIGN KEY ' . TABLE_VARIANTS_ON_TRANSCRIPTS . '_fk_transcriptid', 'ALTER TABLE ' . TABLE_TRANSCRIPTS . ' MODIFY COLUMN id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' MODIFY COLUMN transcriptid MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL', 'ALTER TABLE ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' ADD CONSTRAINT ' . TABLE_VARIANTS_ON_TRANSCRIPTS . '_fk_transcriptid FOREIGN KEY (transcriptid) REFERENCES ' . TABLE_TRANSCRIPTS . ' (id) ON DELETE CASCADE ON UPDATE CASCADE'), '3.0-14c' => array('ALTER TABLE ' . TABLE_TRANSCRIPTS . ' MODIFY COLUMN id MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT'), '3.0-14d' => array('UPDATE ' . TABLE_DISEASES . ' SET symbol = "Healthy/Control" WHERE id_omim IS NULL AND created_by = 0 AND symbol = "Healty/Control"'), '3.0-15a' => array('CREATE TABLE IF NOT EXISTS ' . TABLE_COLLEAGUES . '(
userid_from SMALLINT(5) UNSIGNED ZEROFILL NOT NULL,
userid_to SMALLINT(5) UNSIGNED ZEROFILL NOT NULL,
allow_edit BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (userid_from, userid_to),
INDEX (userid_to),
CONSTRAINT ' . TABLE_COLLEAGUES . '_fk_userid_from FOREIGN KEY (userid_from) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ' . TABLE_COLLEAGUES . '_fk_userid_to FOREIGN KEY (userid_to) REFERENCES ' . TABLE_USERS . ' (id) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB, DEFAULT CHARACTER SET utf8'), '3.0-16a' => array('ALTER TABLE ' . TABLE_TRANSCRIPTS . ' ADD COLUMN remarks TEXT NOT NULL AFTER id_protein_uniprot'), '3.0-16b' => array('ALTER TABLE ' . TABLE_DISEASES . ' ADD COLUMN tissues TEXT NOT NULL AFTER id_omim,
function lovd_soapError($e, $bHalt = true)
{
// Formats SOAP errors for the error log, and optionally halts the system.
if (!is_object($e)) {
return false;
}
// Try to detect if arguments have been passed, and isolate them from the stacktrace.
$sMethod = '';
$sArgs = '';
foreach ($e->getTrace() as $aTrace) {
if (isset($aTrace['function']) && $aTrace['function'] == '__call') {
// This is the low level SOAP call. Isolate used method and arguments from here.
list($sMethod, $aArgs) = $aTrace['args'];
if ($aArgs && is_array($aArgs) && isset($aArgs[0])) {
$aArgs = $aArgs[0];
// Not sure why the call's argument are in a sub array, but oh, well.
foreach ($aArgs as $sArg => $sValue) {
$sArgs .= (!$sArgs ? '' : "\n") . "\t\t" . $sArg . ':' . $sValue;
}
}
break;
}
}
// Format the error message.
$sError = preg_replace('/^' . preg_quote(rtrim(lovd_getInstallURL(false), '/'), '/') . '/', '', $_SERVER['REQUEST_URI']) . ' returned error in module \'' . $sMethod . '\'.' . "\n" . (!$sArgs ? '' : 'Arguments:' . "\n" . $sArgs . "\n") . 'Error message:' . "\n" . str_replace("\n", "\n\t\t", $e->__toString());
// If the system needs to be halted, send it through to lovd_displayError() who will print it on the screen,
// write it to the system log, and halt the system. Otherwise, just log it to the database.
if ($bHalt) {
return lovd_displayError('SOAP', $sError);
} else {
return lovd_writeLog('Error', 'SOAP', $sError);
}
}
$aParsed[$sSection]['updatedIDs'][] = $aData['screeningid'];
}
$sSQL = 'INSERT INTO ' . constant($aSection['table_name']) . ' (';
$aSQL = array();
foreach ($aSection['allowed_columns'] as $key => $sField) {
$sSQL .= (!$key ? '' : ', ') . '`' . $sField . '`';
$aSQL[] = $aData[$sField];
}
$sSQL .= ') VALUES (?' . str_repeat(', ?', count($aFields) - 1) . ')';
$_DB->query($sSQL, $aSQL, true, true);
$nDone++;
break;
default:
// Somehow we don't catch all sections? Big bug...
$bError = true;
lovd_displayError('Import', 'Undefined data processing for section "' . htmlspecialchars($sSection) . '". Please report this bug.');
$_DB->rollBack();
break 3;
// Exit data processing.
}
// Verify and process all edits.
// If we die here for some reason, we must be absolutely sure that we can repeat the same import...
// Curators should also not always be allowed to set the status* field or both pathogenicity fields, it should be based on the individual's data!!!
// Check during import maybe difficult. If it is too difficult, maybe first import and then update for the not-authorized data?
// Curators are allowed to edit diseases if isAuthorized() returns true.
// In the end, verify if we've been using all of the $aParsed columns. If not, remove some.
// Important note: we're not checking the format of phenotype fields that are not included for a certain disease. That means data may be ignored while importing, if it is in fields that are not in use for the given disease.
// The same holds for VOT fields.
// Important note: how will we import phenotype data for diseases that we create in the same file? We won't know which fields will be added, thus we can't check anything!
// Not mandatory yes/no, field lengths, field formats, etc.
/*******************************************************************************
if (count($aTables) < count($_TABLES) - 2) {
// We're not completely installed.
define('NOT_INSTALLED', true);
}
// inc-js-submit-settings.php check is necessary because it gets included in the install directory.
if (dirname(lovd_getProjectFile()) != '/install' && lovd_getProjectFile() != '/inc-js-submit-settings.php') {
// We're not installing, so throwing an error.
if (defined('NOT_INSTALLED')) {
// We're not completely installed.
$_T->printHeader();
print ' <BR>' . "\n" . ' LOVD was not installed yet. Please <A href="' . ROOT_PATH . 'install">install</A> LOVD first.<BR>' . "\n";
$_T->printFooter();
exit;
} elseif (lovd_getProjectFile() != '/uninstall.php') {
// Can't get the configuration for unknown reason. Bail out.
lovd_displayError('Init', 'Error retrieving LOVD configuration or status information');
}
}
// This should leave us alone if we're installing, even if we've got all tables, but are not quite done yet.
} else {
// Store additional version information.
list(, $_STAT['tree'], , $_STAT['build']) = $aRegsVersion;
}
// Prevent some troubles with the menu when the URL contains double slashes.
$_SERVER['SCRIPT_NAME'] = lovd_cleanDirName($_SERVER['SCRIPT_NAME']);
// Force GPC magic quoting OFF.
if (get_magic_quotes_gpc()) {
lovd_magicUnquoteAll();
}
// Use of SSL required?
// FIXME:
function updateAll($nID, $aData, $aGeneFields = array())
{
// Edit all VariantOnTranscript entries.
// FIXME; We need a cleaner solution than globalizing zData.
global $zData, $_AUTH, $_DB;
$nAffected = 0;
foreach ($this->aTranscripts as $nTranscriptID => $aTranscript) {
// Each gene has different fields of course.
foreach ($aGeneFields[$aTranscript[1]] as $sField) {
if (strpos($sField, '/')) {
$aData[$sField] = $aData[$nTranscriptID . '_' . $sField];
}
}
$aData['effectid'] = $aData[$nTranscriptID . '_effect_reported'] . ($_AUTH['level'] >= LEVEL_CURATOR ? $aData[$nTranscriptID . '_effect_concluded'] : $zData[$nTranscriptID . '_effectid'][1]);
$aData['position_c_start'] = $aData[$nTranscriptID . '_position_c_start'];
$aData['position_c_start_intron'] = $aData[$nTranscriptID . '_position_c_start_intron'];
$aData['position_c_end'] = $aData[$nTranscriptID . '_position_c_end'];
$aData['position_c_end_intron'] = $aData[$nTranscriptID . '_position_c_end_intron'];
// Updates entry $nID with data from $aData in the database, changing only fields defined in $aFields.
if (!trim($nID)) {
lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::updateEntry() - Method didn\'t receive ID');
} elseif (!is_array($aData) || !count($aData)) {
lovd_displayError('LOVD-Lib', 'Objects::(' . $this->sObject . ')::updateEntry() - Method didn\'t receive data array');
} elseif (!is_array($aGeneFields[$aTranscript[1]]) || !count($aGeneFields[$aTranscript[1]])) {
$aGeneFields[$aTranscript[1]] = array_keys($aData);
}
// Query text.
$sSQL = 'UPDATE ' . constant($this->sTable) . ' SET ';
$aSQL = array();
foreach ($aGeneFields[$aTranscript[1]] as $key => $sField) {
$sSQL .= (!$key ? '' : ', ') . '`' . $sField . '` = ?';
if (substr(lovd_getColumnType(constant($this->sTable), $sField), 0, 3) == 'INT' && $aData[$sField] === '') {
$aData[$sField] = NULL;
}
$aSQL[] = $aData[$sField];
}
$sSQL .= ' WHERE id = ? AND transcriptid = ?';
$aSQL[] = $nID;
$aSQL[] = $nTranscriptID;
if (!defined('LOG_EVENT')) {
define('LOG_EVENT', $this->sObject . '::updateEntry()');
}
$q = $_DB->query($sSQL, $aSQL, true, true);
$nAffected += $q->rowCount();
}
return $nAffected;
}
function lovd_emailError($sErrorCode, $sSubject, $sTo, $bHalt = false)
{
// Formats email errors for the error log, and optionally halts the system.
// Format the error message.
// FIXME; Kan makkelijker??? // Een str_replace() zou ook wel werken... Deze code staat op minimaal 3 plaatsen.
$sError = preg_replace('/^' . preg_quote(rtrim(lovd_getInstallURL(false), '/'), '/') . '/', '', $_SERVER['REQUEST_URI']) . ' returned error in code block ' . $sErrorCode . '.' . "\n" . 'Error : Couldn\'t send a mail with subject "' . $sSubject . '" to ' . $sTo;
// If the system needs to be halted, send it through to lovd_displayError() who will print it on the screen,
// write it to the system log, and halt the system. Otherwise, just log it to the database.
if ($bHalt) {
lovd_displayError('SendMail', $sError);
} else {
lovd_writeLog('Error', 'SendMail', $sError);
}
}
function __construct($aObjects = array(), $sOtherID = '')
{
// Default constructor.
global $_AUTH, $_CONF, $_DB;
if (!is_array($aObjects)) {
$aObjects = explode(',', $aObjects);
}
$this->sObjectID = implode(',', $aObjects);
// Receive OtherID or Gene.
if (ctype_digit($sOtherID)) {
$sGene = '';
$this->nOtherID = $sOtherID;
} else {
$sGene = $sOtherID;
}
// FIXME: Disable this part when not using any of the custom column data types...
// Collect custom column information, all active columns (possibly restricted per gene).
// FIXME; This join is not always needed (it's done for VOT columns, but sometimes they are excluded, or the join is not necessary because of the user level), exclude when not needed to speed up the query?
// Also, the select of public_view makes no sense of VOTs are restricted by gene.
// Note: objects inheriting LOVD_custom implement selection of
// viewable columns in buildViewList()
$sSQL = 'SELECT c.id, c.width, c.head_column, c.description_legend_short, c.description_legend_full, c.mysql_type, c.form_type, c.select_options, c.col_order, GROUP_CONCAT(sc.geneid, ":", sc.public_view SEPARATOR ";") AS public_view FROM ' . TABLE_ACTIVE_COLS . ' AS ac INNER JOIN ' . TABLE_COLS . ' AS c ON (c.id = ac.colid) LEFT OUTER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = ac.colid) ' . 'WHERE ' . ($_AUTH['level'] >= ($sGene ? LEVEL_COLLABORATOR : LEVEL_MANAGER) ? '' : '((c.id NOT LIKE "VariantOnTranscript/%" AND c.public_view = 1) OR sc.public_view = 1) AND ') . '(c.id LIKE ?' . str_repeat(' OR c.id LIKE ?', count($aObjects) - 1) . ') ' . (!$sGene ? 'GROUP BY c.id ' : 'AND c.id NOT LIKE "VariantOnTranscript/%" GROUP BY c.id ' . 'UNION ' . 'SELECT c.id, sc.width, c.head_column, c.description_legend_short, c.description_legend_full, c.mysql_type, c.form_type, c.select_options, sc.col_order, CONCAT(sc.geneid, ":", sc.public_view) AS public_view FROM ' . TABLE_COLS . ' AS c INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (c.id = sc.colid) WHERE sc.geneid = ? ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'AND sc.public_view = 1 ')) . 'ORDER BY col_order';
$aSQL = array();
foreach ($aObjects as $sObject) {
$aSQL[] = $sObject . '/%';
}
if ($sGene) {
$aSQL[] = $sGene;
}
if ($sOtherID) {
$this->nID = $sOtherID;
// We need the AJAX script to have the same restrictions!!!
}
// Increase the max GROUP_CONCAT() length, so that lists of many many genes still have all genes mentioned here (22.000 genes take 193.940 bytes here).
$_DB->query('SET group_concat_max_len = 200000');
$q = $_DB->query($sSQL, $aSQL);
while ($z = $q->fetchAssoc()) {
$z['custom_links'] = array();
$z['form_type'] = explode('|', $z['form_type']);
$z['select_options'] = explode("\r\n", $z['select_options']);
// What do we use this for?
if (substr($z['id'], 0, 19) == 'VariantOnTranscript') {
$z['public_view'] = explode(';', rtrim(preg_replace('/([A-Za-z0-9-]+:0;|:1)/', '', $z['public_view'] . ';'), ';'));
}
if (is_null($z['public_view'])) {
$z['public_view'] = array();
}
$this->aColumns[$z['id']] = $z;
}
if ($_AUTH) {
$_AUTH['allowed_to_view'] = array_merge($_AUTH['curates'], $_AUTH['collaborates']);
}
// Boolean indicating whether row_id is already in SELECT statement somewhere.
$bSetRowID = false;
$aSQL = $this->aSQLViewList;
// If possible, determine row_id SQL from combination of object types.
$sRowIDSQL = null;
if (in_array('VariantOnGenome', $aObjects) && (in_array('VariantOnTranscript', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects))) {
// Use "vog.id:vot.transcriptid" as row_id, fall back to "vog.id" if there is no VOT entry.
$aSQL['SELECT'] = 'CONCAT(vog.id, IFNULL(CONCAT(":", vot.transcriptid), "")) AS row_id';
$bSetRowID = true;
} elseif (in_array('Transcript', $aObjects)) {
$aSQL['SELECT'] = 't.id AS row_id';
$bSetRowID = true;
}
// Loop requested data types, and keep columns in order indicated by request.
foreach ($aObjects as $nKey => $sObject) {
// Generate custom column listing to be appended to SELECT SQL statement.
// We're not using SELECT * anymore because then we can't use the query as a subquery
// (which needs to have unique columns, and * can select `id` more than once).
// So, we need to select all columns that we need.
$sCustomCols = '';
$aCustomColNames = array_keys($this->getCustomColsForCategory($sObject));
if (count($aCustomColNames) > 0) {
$sCustomCols = ', `' . implode('`, `', $aCustomColNames) . '`';
}
switch ($sObject) {
case 'Gene':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'g.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_GENES . ' AS g';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_GENES)->fetchColumn();
}
break;
case 'Transcript':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 't.id AS tid, ' . 't.geneid, t.name, t.id_ncbi, t.id_protein_ncbi';
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', t.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_TRANSCRIPTS . ' AS t';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_TRANSCRIPTS)->fetchColumn();
} else {
$aSQL['FROM'] .= ' INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (';
$nKeyG = array_search('Gene', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyG !== false && $nKeyG < $nKey) {
// Earlier, Gene was used, join to that.
$aSQL['FROM'] .= 'g.id = t.geneid)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= 'vot.transcriptid = t.id)';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
break;
case 'DistanceToVar':
$nKeyT = array_search('Transcript', $aObjects);
if ($nKeyT !== false && $nKeyT < $nKey && $this->nOtherID) {
// Earlier, Transcript was used, join to that.
// First, retrieve information of variant.
list($nPosStart, $nPosEnd) = $_DB->query('SELECT position_g_start, position_g_end FROM ' . TABLE_VARIANTS . ' WHERE id = ?', array($this->nOtherID))->fetchRow();
// Specific modifications for this overview; distance between variant and transcript in question.
if ($nPosStart && $nPosEnd) {
// 2014-08-11; 3.0-12; Transcripts on the reverse strand did not display the correctly calculated distance.
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'IF(t.position_g_mrna_start < t.position_g_mrna_end, IF(t.position_g_mrna_start > ' . $nPosEnd . ', t.position_g_mrna_start - ' . $nPosEnd . ', IF(t.position_g_mrna_end < ' . $nPosStart . ', ' . $nPosStart . ' - t.position_g_mrna_start, 0)), IF(t.position_g_mrna_end > ' . $nPosEnd . ', t.position_g_mrna_end - ' . $nPosEnd . ', IF(t.position_g_mrna_start < ' . $nPosStart . ', ' . $nPosStart . ' - t.position_g_mrna_end, 0))) AS distance_to_var';
} else {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . '"?" AS distance_to_var';
}
}
break;
case 'VariantOnGenome':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vog.id AS vogid, vog.chromosome, a.name AS allele_' . (!in_array('VariantOnTranscript', $aObjects) ? ', eg.name AS vog_effect' : '') . (in_array('Individual', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects) ? '' : ', uo.name AS owned_by_, CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner') . (in_array('VariantOnTranscriptUnique', $aObjects) ? '' : ', dsg.id AS var_statusid, dsg.name AS var_status') . $sCustomCols;
$nKeyVOTUnique = array_search('VariantOnTranscriptUnique', $aObjects);
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vog.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_VARIANTS . ' AS vog';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_VARIANTS)->fetchColumn();
$aSQL['GROUP_BY'] = 'vog.id';
// Necessary for GROUP_CONCAT(), such as in Screening.
$aSQL['ORDER_BY'] = 'vog.chromosome ASC, vog.position_g_start';
} elseif ($nKeyVOTUnique !== false && $nKeyVOTUnique < $nKey) {
// For the unique variant view a GROUP_CONCAT must be done for the variantOnGenome fields.
foreach ($this->getCustomColsForCategory('VariantOnGenome') as $sCol => $aCol) {
// Here all VariantOnGenome columns are grouped with GROUP_CONCAT. In prepareData(),
// these fields are exploded and the elements are counted, limiting the grouped values
// to a certain length. To recognize the separate items, ;; is used as a separator.
// The NULLIF() is used to not show empty values. GROUP_CONCAT handles NULL values well (ignores them), but not empty values (includes them).
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
}
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS . ' AS vog ON (vot.id = vog.id)';
} else {
// 2016-07-20; 3.0-17; Added FORCE INDEX because MySQL optimized the Full data view
// differently, resulting in immense temporary tables filling up the disk.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS . ' AS vog FORCE INDEX FOR JOIN (PRIMARY) ON (';
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= 'vot.id = vog.id)';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_ALLELES . ' AS a ON (vog.allele = a.id)';
if (!in_array('VariantOnTranscript', $aObjects)) {
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS eg ON (vog.effectid = eg.id)';
}
if (!in_array('Individual', $aObjects)) {
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (vog.owned_by = uo.id)';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsg ON (vog.statusid = dsg.id)';
// If no collaborator, hide lines with hidden variants!
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Construct list of user IDs for current user and users who share access with him.
$aOwnerIDs = array_merge(array($_AUTH['id']), lovd_getColleagues(COLLEAGUE_ALL));
$sOwnerIDsSQL = join(', ', $aOwnerIDs);
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . '(vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR vog.owned_by IN (' . $sOwnerIDsSQL . ')') . ')';
}
break;
case 'VariantOnTranscript':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vot.id AS votid, vot.transcriptid, vot.position_c_start, vot.position_c_start_intron, vot.position_c_end, vot.position_c_end_intron, et.name as vot_effect' . $sCustomCols;
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vot.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
$aSQL['GROUP_BY'] = 'vot.id';
// Necessary for GROUP_CONCAT(), such as in Screening.
} elseif ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Previously, VOG was used. We will join VOT with VOG, using GROUP_CONCAT.
// SELECT will be different: we will GROUP_CONCAT the whole lot, per column.
// Sort GROUP_CONCAT() based on transcript name. We'll have to join Transcripts for that.
// That will break if somebody wants to join transcripts themselves, but why would somebody want that?
$sGCOrderBy = 't.geneid, t.id_ncbi';
foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT ' . ($sCol != 'VariantOnTranscript/DNA' ? '`' . $sCol . '`' : 'CONCAT(t.id_ncbi, ":", `' . $sCol . '`)') . ' ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS `' . $sCol . '`';
}
// If we're joining to Scr2Var, we're showing the Individual- and Screening-specific views, and we want to show a gene as well.
// We can't use _geneid below, because LOVD will explode that into an array.
if (array_search('Scr2Var', $aObjects) !== false) {
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS genes';
}
// Security checks in this file's prepareData() need geneid to see if the column in question is set to non-public for one of the genes.
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid SEPARATOR ";") AS _geneid';
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (';
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= 'vog.id = vot.id)';
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (vot.transcriptid = t.id)';
} else {
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (';
$nKeyT = array_search('Transcript', $aObjects);
if ($nKeyT !== false && $nKeyT < $nKey) {
// Earlier, T was used, join to that.
$aSQL['FROM'] .= 't.id = vot.transcriptid)';
// Nice, but if we're showing transcripts and variants on transcripts in one viewList, we'd only want to see the transcripts that HAVE variants.
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . 'vot.id IS NOT NULL';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
break;
case 'VariantOnTranscriptUnique':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vot.id AS votid, vot.transcriptid, ' . 'vot.position_c_start, vot.position_c_start_intron, ' . 'vot.position_c_end, vot.position_c_end_intron';
// To group variants together that belong together (regardless of minor textual differences, we replace parentheses, remove the "c.", and trim for question marks.
// This notation will be used to group on, and search on when navigating from the unique variant view to the full variant view.
$aSQL['SELECT'] .= ', TRIM(BOTH "?" FROM TRIM(LEADING "c." FROM REPLACE(REPLACE(`VariantOnTranscript/DNA`, ")", ""), "(", ""))) AS vot_clean_dna_change' . ', GROUP_CONCAT(DISTINCT et.name SEPARATOR ", ") AS vot_effect' . ', GROUP_CONCAT(DISTINCT NULLIF(uo.name, "") SEPARATOR ", ") AS owned_by_' . ', GROUP_CONCAT(DISTINCT CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) SEPARATOR ";;") AS __owner';
// dsg.id GROUP_CONCAT is ascendingly ordered. This is done for the color marking.
// In prepareData() the lowest var_statusid is used to determine the coloring.
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(dsg.id, "") ORDER BY dsg.id ASC SEPARATOR ", ") AS var_statusid, GROUP_CONCAT(DISTINCT NULLIF(dsg.name, "") SEPARATOR ", ") AS var_status' . ', COUNT(`VariantOnTranscript/DNA`) AS vot_reported';
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vot.id AS row_id';
$bSetRowID = true;
}
$aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';
// FIXME: On large databases, we might want to skip this, since a COUNT(*) on InnoDB tables isn't fast at all, and nCount doesn't need to be specific at all.
$this->nCount = $_DB->query('SELECT COUNT(DISTINCT `VariantOnTranscript/DNA`) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
$aSQL['GROUP_BY'] = '`position_c_start`, `position_c_start_intron`, `position_c_end`, `position_c_end_intron`, vot_clean_dna_change';
// Necessary for GROUP_CONCAT(), such as in Screening.
foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
// Here all VariantOnTranscript columns are grouped with GROUP_CONCAT. In prepareData(),
// these fields are exploded and the elements are counted, limiting the grouped values
// to a certain length. To recognize the separate items, ;; is used as a separator.
// The NULLIF() is used to not show empty values. GROUP_CONCAT handles NULL values well (ignores them), but not empty values (includes them).
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
break;
case 'Screening':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's.id as sid' . $sCustomCols;
$aSQL['FROM'] = TABLE_SCREENINGS . ' AS s';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_SCREENINGS)->fetchColumn();
$aSQL['ORDER_BY'] = 's.id';
} else {
// SELECT will be different: we will GROUP_CONCAT the whole lot, per column.
$sGCOrderBy = isset($this->aColumns['Screening/Date']) ? '`Screening/Date`' : 'id';
foreach ($this->getCustomColsForCategory('Screening') as $sCol => $aCol) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'GROUP_CONCAT(DISTINCT `' . $sCol . '` ORDER BY s.' . $sGCOrderBy . ' SEPARATOR ";") AS `' . $sCol . '`';
}
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
$nKeyI = array_search('Individual', $aObjects);
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyI !== false && $nKeyI < $nKey) {
// Earlier, I was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (i.id = s.individualid)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
break;
case 'Scr2Var':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's2v.id AS row_id';
$bSetRowID = true;
}
if ($aSQL['FROM']) {
// Not allowed to be the first data table in query, because entries are usually grouped by the first table.
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid)';
// This combination only happens when we're joining VOG to Scr2Var to VOT, to show variants in a screening or individual.
// Then grouping on the s2v's variant ID is faster, because we're searching on the s2v.screeningid and like this we keep
// the group by and the where in the same table, greatly increasing the speed of the query.
$aSQL['GROUP_BY'] = 's2v.variantid';
// Necessary for GROUP_CONCAT().
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
break;
case 'Individual':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'i.id AS iid, i.panel_size, i.owned_by, GROUP_CONCAT(DISTINCT IF(CASE d.symbol WHEN "-" THEN "" ELSE d.symbol END = "", d.name, d.symbol) ORDER BY (d.symbol != "" AND d.symbol != "-") DESC, d.symbol, d.name SEPARATOR ", ") AS diseases_, uo.name AS owned_by_, CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner, dsi.id AS ind_statusid, dsi.name AS ind_status' . $sCustomCols;
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', i.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_INDIVIDUALS . ' AS i';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_INDIVIDUALS)->fetchColumn();
$aSQL['ORDER_BY'] = 'i.id';
// If no manager, hide lines with hidden individuals (not specific to a gene)!
if ($_AUTH['level'] < LEVEL_MANAGER) {
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . 'i.statusid >= ' . STATUS_MARKED;
}
} else {
$nKeyS = array_search('Screening', $aObjects);
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyS === false || $nKeyS > $nKey) {
// S was not used yet, join to something else first!
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (s.individualid = i.id';
// If no collaborator, hide hidden individuals (from the join, don't hide the line)!
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Construct list of user IDs for current user and users who share access with him.
$aOwnerIDs = array_merge(array($_AUTH['id']), lovd_getColleagues(COLLEAGUE_ALL));
$sOwnerIDsSQL = join(', ', $aOwnerIDs);
$aSQL['FROM'] .= ' AND (i.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR i.created_by = "' . $_AUTH['id'] . '" OR i.owned_by IN (' . $sOwnerIDsSQL . ')') . ')';
}
$aSQL['FROM'] .= ')';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (i2d.diseaseid = d.id)';
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id)';
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsi ON (i.statusid = dsi.id)';
break;
}
}
if (!$aSQL['SELECT'] || !$aSQL['FROM']) {
// Apparently, not implemented or no objects given.
lovd_displayError('ObjectError', 'CustomViewLists::__construct() requested with non-existing or missing object(s) \'' . htmlspecialchars(implode(',', $aObjects)) . '\'.');
}
$this->aSQLViewList = $aSQL;
if ($this->sObjectID == 'Transcript,VariantOnTranscript,VariantOnGenome') {
// The joining of the tables needed for this view are in this order, but I want a different order on display.
$aObjects = array('Transcript', 'VariantOnGenome', 'VariantOnTranscript');
}
// Now build $this->aColumnsViewList, from the order given by $aObjects and TABLE_COLS.col_order.
foreach ($aObjects as $nKey => $sObject) {
switch ($sObject) {
case 'Gene':
$sPrefix = 'g.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('chromosome' => array('view' => false, 'db' => array('g.chromosome', 'ASC', true))));
break;
case 'Transcript':
$sPrefix = 't.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('tid' => array('view' => false, 'db' => array('t.id', 'ASC', true)), 'geneid' => array('view' => array('Gene', 100), 'db' => array('t.geneid', 'ASC', true)), 'name' => array('view' => array('Name', 300), 'db' => array('t.name', 'ASC', true)), 'id_ncbi' => array('view' => array('NCBI ID', 120), 'db' => array('t.id_ncbi', 'ASC', true)), 'id_protein_ncbi' => array('view' => array('NCBI Protein ID', 120), 'db' => array('t.id_protein_ncbi', 'ASC', true))));
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'geneid';
}
// The custom ViewList with transcripts and variants also names the id_ncbi field differently.
if ($nKey == 0 && in_array('VariantOnTranscript', $aObjects)) {
// Object [0] is Transcripts, [1] is VOT; this is the in_gene view.
$this->aColumnsViewList['id_ncbi']['view'][0] = 'Transcript';
}
break;
case 'DistanceToVar':
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('distance_to_var' => array('view' => array('Distance (bp)', 90, 'style="text-align : right;"'), 'db' => array('distance_to_var', 'ASC', false))));
// Always force default sorting...
$this->sSortDefault = 'distance_to_var';
break;
case 'VariantOnGenome':
$sPrefix = 'vog.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('chromosome' => array('view' => array('Chr', 50), 'db' => array('vog.chromosome', 'ASC', true)), 'allele_' => array('view' => array('Allele', 120), 'db' => array('a.name', 'ASC', true), 'legend' => array('On which allele is the variant located? Does not necessarily imply inheritance!', 'On which allele is the variant located? Does not necessarily imply inheritance! \'Paternal\' (confirmed or inferred), \'Maternal\' (confirmed or inferred), \'Parent #1\' or #2 for compound heterozygosity without having screened the parents, \'Unknown\' for heterozygosity without having screened the parents, \'Both\' for homozygozity.')), 'vog_effect' => array('view' => array('Effect', 70), 'db' => array('eg.name', 'ASC', true), 'legend' => array('The variant\'s effect on a protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on a protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.'))));
if (in_array('VariantOnTranscript', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects)) {
unset($this->aColumnsViewList['vog_effect']);
}
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnGenome/DNA';
}
$this->sRowLink = 'variants/{{zData_vogid}}#{{zData_transcriptid}}';
break;
case 'VariantOnTranscript':
$sPrefix = 'vot.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('transcriptid' => array('view' => false, 'db' => array('vot.transcriptid', 'ASC', true)), 'position_c_start' => array('view' => false, 'db' => array('vot.position_c_start', 'ASC', true)), 'position_c_start_intron' => array('view' => false, 'db' => array('vot.position_c_start_intron', 'ASC', true)), 'position_c_end' => array('view' => false, 'db' => array('vot.position_c_end', 'ASC', true)), 'position_c_end_intron' => array('view' => false, 'db' => array('vot.position_c_end_intron', 'ASC', true)), 'vot_clean_dna_change' => array('view' => false, 'db' => array('TRIM(BOTH "?" FROM TRIM(LEADING "c." FROM REPLACE(REPLACE(`VariantOnTranscript/DNA`, ")", ""), "(", "")))', 'ASC', 'TEXT')), 'genes' => array('view' => array('Gene', 100), 'db' => array('t.geneid', 'ASC', true)), 'vot_effect' => array('view' => array('Effect', 70), 'db' => array('et.name', 'ASC', true), 'legend' => array('The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.'))));
// Only show the gene symbol when we have Scr2Var included, because these are the Individual- and Screening-specific views.
// FIXME: Perhaps it would be better to always show this column with VOT, but then hide it in all views that don't need it.
if (array_search('Scr2Var', $aObjects) === false) {
unset($this->aColumnsViewList['genes']);
}
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnTranscript/DNA';
}
break;
case 'VariantOnTranscriptUnique':
$sPrefix = 'vot.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('transcriptid' => array('view' => false, 'db' => array('vot.transcriptid', 'ASC', true)), 'vot_effect' => array('view' => array('Effect', 70), 'db' => array('et.name', 'ASC', true), 'legend' => array('The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.')), 'vot_reported' => array('view' => array('Reported', 70, 'style="text-align : right;"'), 'db' => array('vot_reported', 'ASC', 'INT_UNSIGNED'), 'legend' => array('The number of times this variant has been reported.', 'The number of times this variant has been reported in the database.'))));
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnTranscript/DNA';
}
break;
case 'Screening':
$sPrefix = 's.';
// No fixed columns.
if (!$this->sSortDefault) {
// First data table in view.
// The fixed columns, only when first table.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('sid' => array('view' => array('Screening ID', 110, 'style="text-align : right;"'), 'db' => array('s.id', 'ASC', true))));
$this->sSortDefault = 'id';
}
break;
case 'Scr2Var':
$sPrefix = 's2v.';
// No fixed columns, is only used to filter variants based on screening ID.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('screeningid' => array('view' => false, 'db' => array('s2v.screeningid', false, true))));
break;
case 'Individual':
$sPrefix = 'i.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('diseases_' => array('view' => array('Disease', 175), 'db' => array('diseases_', 'ASC', true))));
if (!$this->sSortDefault) {
$this->sSortDefault = 'id';
}
break;
}
// The custom columns.
foreach ($this->aColumns as $sColID => $aCol) {
if (strpos($sColID, str_replace('Unique', '', $sObject) . '/') === 0) {
$bAlignRight = preg_match('/^(DEC|FLOAT|(TINY|SMALL|MEDIUM|BIG)?INT)/', $aCol['mysql_type']);
$this->aColumnsViewList[$sColID] = array('view' => array($aCol['head_column'], $aCol['width'], $bAlignRight ? ' align="right"' : ''), 'db' => array($sPrefix . '`' . $aCol['id'] . '`', 'ASC', lovd_getColumnType('', $aCol['mysql_type'])), 'legend' => array($aCol['description_legend_short'], $aCol['description_legend_full']), 'allowfnr' => true);
}
}
// Some fixed columns are supposed to be shown AFTER this objects's custom columns, so we'll need to go through the objects again.
switch ($sObject) {
case 'VariantOnGenome':
// More fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'owner_countryid' => array('view' => false, 'db' => array('uo.countryid', 'ASC', true)), 'var_status' => array('view' => array('Var. status', 70), 'db' => array('dsg.name', false, true))));
if (in_array('Individual', $aObjects)) {
unset($this->aColumnsViewList['owned_by_']);
}
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Unset status column for non-collaborators. We're assuming here, that lovd_isAuthorized() only gets called for gene-specific overviews.
unset($this->aColumnsViewList['var_status']);
}
// 2015-10-09; 3.0-14; Add genome build name to the VOG/DNA field.
$this->aColumnsViewList['VariantOnGenome/DNA']['view'][0] .= ' (' . $_CONF['refseq_build'] . ')';
break;
case 'Individual':
// More fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('panel_size' => array('view' => array('Panel size', 70, 'style="text-align : right;"'), 'db' => array('i.panel_size', 'DESC', true)), 'owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'ind_status' => array('view' => array('Ind. status', 70), 'db' => array('dsi.name', false, true))));
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Unset status column for non-collaborators. We're assuming here, that lovd_isAuthorized() only gets called for gene-specific overviews.
unset($this->aColumnsViewList['ind_status']);
}
break;
}
}
// Gather the custom link information. It's just easier to load all custom links, instead of writing code that checks for the appropriate objects.
$aLinks = $_DB->query('SELECT l.*, GROUP_CONCAT(c2l.colid SEPARATOR ";") AS colids FROM ' . TABLE_LINKS . ' AS l INNER JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (l.id = c2l.linkid) GROUP BY l.id')->fetchAllAssoc();
foreach ($aLinks as $aLink) {
$aLink['regexp_pattern'] = '/' . str_replace(array('{', '}'), array('\\{', '\\}'), preg_replace('/\\[\\d\\]/', '(.*)', $aLink['pattern_text'])) . '/';
$aLink['replace_text'] = preg_replace('/\\[(\\d)\\]/', '\\$$1', $aLink['replace_text']);
$aCols = explode(';', $aLink['colids']);
foreach ($aCols as $sColID) {
if (isset($this->aColumns[$sColID])) {
$this->aColumns[$sColID]['custom_links'][] = $aLink['id'];
}
}
$this->aCustomLinks[$aLink['id']] = $aLink;
}
// Not including parent constructor, because these table settings will make it freak out.
//parent::__construct();
// Therefore, row links need to be created by us (which is done above).
}
