예제 #1
0
 function __construct($sObjectID = '', $nID = '')
 {
     // Default constructor.
     // $nID is not really correct here, it's always $sID.
     global $_DB;
     if (!$sObjectID && !$nID) {
         lovd_displayError('ObjectError', 'SharedColumn::__construct() not called with valid Parent or Column ID.');
     }
     $this->sObjectID = $sObjectID;
     // ID of parent gene or disease.
     $this->nID = $nID;
     // ID of the column itself.
     if ($nID) {
         $sCategory = substr($nID, 0, strpos($nID . '/', '/'));
         // Isolate the category from the ID.
     } else {
         $sCategory = ctype_digit($sObjectID) ? 'Phenotype' : 'VariantOnTranscript';
     }
     $this->aTableInfo = lovd_getTableInfoByCategory($sCategory);
     // Gather info on the type of column.
     // SQL code for loading an entry for an edit form.
     $this->sSQLLoadEntry = 'SELECT sc.*, c.form_type ' . 'FROM ' . TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'WHERE sc.colid = ? AND sc.' . $this->aTableInfo['unit'] . 'id = "' . $sObjectID . '"';
     // Variable has been checked elsewhere, before this query is run.
     // SQL code for viewing an entry.
     $this->aSQLViewEntry['SELECT'] = 'sc.*, ' . 'c.hgvs, ' . 'c.form_type, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_';
     $this->aSQLViewEntry['FROM'] = TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS uc ON (sc.created_by = uc.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS ue ON (sc.edited_by = ue.id)';
     $this->aSQLViewEntry['WHERE'] = 'sc.' . $this->aTableInfo['unit'] . 'id = "' . $sObjectID . '"';
     // Variable has been checked elsewhere, before this query is run.
     // SQL code for viewing a list of entries.
     $this->aSQLViewList['SELECT'] = 'sc.*, ' . 'SUBSTRING(sc.colid, LOCATE("/", sc.colid)+1) AS colid, ' . 'c.id, ' . 'c.head_column, ' . 'c.form_type, ' . 'u.name AS created_by_';
     $this->aSQLViewList['FROM'] = TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS u ON (sc.created_by = u.id)';
     // Now restrict viewList to only these related to this gene/disease.
     $this->aSQLViewList['WHERE'] = 'sc.' . $this->aTableInfo['unit'] . 'id = ' . $_DB->quote($sObjectID);
     $this->aSQLViewList['ORDER_BY'] = 'col_order, colid';
     // List of columns and (default?) order for viewing an entry.
     $this->aColumnsViewEntry = array('colid' => 'Column ID', 'width' => 'Displayed width in pixels', 'mandatory_' => 'Mandatory', 'description_form' => 'Description on form', 'description_legend_short' => 'Description on short legend', 'description_legend_full' => 'Description on full legend', 'select_options' => 'Select options', 'public_view_' => 'Show to public', 'public_add_' => 'Show on submission form', 'created_by_' => 'Created by', 'created_date' => 'Date created', 'edited_by_' => 'Last edited by', 'edited_date' => 'Date last edited');
     // List of columns and (default?) order for viewing a list of entries.
     $this->aColumnsViewList = array('id' => array('view' => false, 'db' => array('sc.colid', 'ASC', true)), 'colid_' => array('view' => array('ID', 175), 'db' => array('SUBSTRING(sc.colid, LOCATE("/", sc.colid)+1)', 'ASC', true)), 'head_column' => array('view' => array('Heading', 150), 'db' => array('c.head_column', 'ASC', true)), 'width' => array('view' => array('Width in px', 100), 'db' => array('sc.width', 'ASC', true)), 'mandatory_' => array('view' => array('Mandatory', 60, 'style="text-align : center;"'), 'db' => array('sc.mandatory', 'DESC', true)), 'public_view_' => array('view' => array('Public', 60, 'style="text-align : center;"'), 'db' => array('sc.public_view', 'DESC', true)), 'col_order' => array('view' => array('Order ', 60, 'style="text-align : right;"'), 'db' => array('sc.col_order', 'ASC')), 'form_type_' => array('view' => array('Form type', 200)), 'created_by_' => array('view' => array('Created by', 160), 'db' => array('u.name', 'DESC', true)));
     $this->sSortDefault = 'col_order';
     parent::__construct();
 }
예제 #2
0
파일: columns.php 프로젝트: LOVDnl/LOVD3
if (PATH_COUNT > 2 && ACTION == 'remove') {
    // URL: /columns/VariantOnGenome/DNA?remove
    // URL: /columns/Phenotype/Blood_pressure/Systolic?remove
    // Disable specific custom column.
    $aCol = $_PE;
    unset($aCol[0]);
    // 'columns';
    $sColumnID = implode('/', $aCol);
    $sCategory = $aCol[1];
    define('PAGE_TITLE', 'Remove custom data column ' . $sColumnID);
    define('LOG_EVENT', 'ColRemove');
    // Require form & column functions.
    require ROOT_PATH . 'inc-lib-form.php';
    require_once ROOT_PATH . 'inc-lib-columns.php';
    // Required clearance depending on which type of column is being added.
    $aTableInfo = lovd_getTableInfoByCategory($sCategory);
    if ($aTableInfo['shared']) {
        lovd_isAuthorized('gene', $_AUTH['curates']);
        // Any gene will do.
        lovd_requireAUTH(LEVEL_CURATOR);
    } else {
        lovd_requireAUTH(LEVEL_MANAGER);
    }
    $zData = $_DB->query('SELECT c.*, SUBSTRING(c.id, LOCATE("/", c.id)+1) AS colid FROM ' . TABLE_COLS . ' AS c INNER JOIN ' . TABLE_ACTIVE_COLS . ' AS ac ON (c.id = ac.colid) WHERE c.id = ? AND c.hgvs = 0', array($sColumnID))->fetchAssoc();
    if (!$zData) {
        $_T->printHeader();
        $_T->printTitle();
        lovd_showInfoTable('No such ID!', 'stop');
        $_T->printFooter();
        exit;
    }
예제 #3
0
파일: objects.php 프로젝트: LOVDnl/LOVD3
 function getRowCountForViewList($aSQL, $aArgs = array(), $bDebug = false)
 {
     // Attempt to speed up the "counting" part of the VL queries.
     // ViewList queries are counting the number of total hits using the
     // MySQL extension SQL_CALC_FOUND_ROWS. This works well for queries
     // sorted on non-indexed fields, where the query itself also requires a
     // full scan through the results. However,for queries that are normally
     // fast when LIMITed, this slows down the query a lot.
     // This function here will attempt to reduce the given query to a simple
     // SELECT COUNT(*) statement with as few joins as needed, resulting in
     // an as fast query as possible.
     // The $bDebug argument lets this function just return the SQL that is produced.
     global $_DB, $_INI;
     // If we don't have a HAVING clause, we can simply drop the SELECT information.
     $aColumnsNeeded = array();
     $aTablesNeeded = array();
     if (!$aSQL['GROUP_BY'] && !$aSQL['HAVING'] && !$aSQL['ORDER_BY']) {
         $aSQL['SELECT'] = '';
     } else {
         if ($aSQL['GROUP_BY']) {
             // We do have GROUP BY... We'll need to keep only the columns in the SELECT that are aliases,
             // but non-alias columns that are used for grouping must also be kept in the JOIN!
             // Parse GROUP BY! Can be a mix of real columns and aliases.
             if (preg_match_all('/\\b(?:(\\w+)\\.)?(\\w+)\\b/', $aSQL['GROUP_BY'], $aRegs)) {
                 // This code is the same as for the ORDER BY parsing.
                 for ($i = 0; $i < count($aRegs[0]); $i++) {
                     // 1: table referred to (real columns without alias only);
                     // 2: alias, or column name in given table.
                     if ($aRegs[1][$i]) {
                         // Real table. We don't need this in the SELECT unless it's also in the HAVING, but we definitely need this in the JOIN.
                         $aTablesNeeded[] = $aRegs[1][$i];
                     } elseif ($aRegs[2][$i]) {
                         // Alias only. Keep this column for the SELECT. When parsing the SELECT, we'll find out from which table(s) it is.
                         $aColumnsNeeded[] = $aRegs[2][$i];
                     }
                 }
             }
         }
         if ($aSQL['HAVING']) {
             // We do have HAVING, so now we'll have to see what we need to keep, the rest we toss out.
             // Parse HAVING! These are no fields directly from tables, but all aliases, so this parsing is different from parsing WHERE.
             // We don't care about AND/OR or anything... we just want the aliases.
             if (preg_match_all('/\\b(\\w+)\\s(?:[!><=]+|IS (?:NOT )?NULL|LIKE )/', $aSQL['HAVING'], $aRegs)) {
                 $aColumnsNeeded = array_merge($aColumnsNeeded, $aRegs[1]);
             }
         }
         if ($aSQL['ORDER_BY']) {
             // We do have ORDER BY... We'll need to keep only the columns in the SELECT that are aliases,
             // but non-alias columns that are used for sorting must also be kept in the JOIN!
             // Parse ORDER BY! Can be a mix of real columns and aliases.
             // Adding a comma in the end, so we can use a simpler pattern that always ends with one.
             // FIXME: Wait, why are we parsing the ORDER_BY??? We can just drop it... and drop the cols which it uses... right?
             if (false && preg_match_all('/\\b(?:(\\w+)\\.)?(\\w+)(?:\\s(?:ASC|DESC))?,/', $aSQL['ORDER_BY'] . ',', $aRegs)) {
                 // This code is the same as for the GROUP BY parsing.
                 for ($i = 0; $i < count($aRegs[0]); $i++) {
                     // 1: table referred to (real columns without alias only);
                     // 2: alias, or column name in given table.
                     if ($aRegs[1][$i]) {
                         // Real table. We don't need this in the SELECT unless it's also in the HAVING, but we definitely need this in the JOIN.
                         $aTablesNeeded[] = $aRegs[1][$i];
                     } elseif ($aRegs[2][$i]) {
                         // Alias only. Keep this column for the SELECT. When parsing the SELECT, we'll find out from which table it is.
                         $aColumnsNeeded[] = $aRegs[2][$i];
                     }
                 }
             }
             // We never need an ORDER BY to get the number of results, so...
             $aSQL['ORDER_BY'] = '';
         }
     }
     $aColumnsNeeded = array_unique($aColumnsNeeded);
     if (!$aColumnsNeeded) {
         $aSQL['SELECT'] = '';
     }
     // Now that we know which columns we should keep, we can parse the SELECT clause to see what we can remove.
     $aColumnsUsed = array();
     // Will contain limited information on the columns defined in the SELECT syntax.
     if ($aSQL['SELECT'] && $aColumnsNeeded) {
         // Analyzing the SELECT. This is quite difficult as we can have simple SELECTs but also really complicated ones,
         // such as GROUP_CONCAT() or subselects. These should all be parsed and needed tables should be identified.
         //                    t.* || t.col                    [t.col || "value" || (t.col ... val) || FUNCTION() || CASE ... END] AS alias
         if (preg_match_all('/(([a-z0-9_]+)\\.(?:\\*|[a-z0-9_]+)|(?:(?:([a-z0-9_]+)\\.[a-z0-9_]+|".*"|[A-Z_]*\\(.+\\)|CASE .+ END) AS +([a-z0-9_]+|`[A-Za-z0-9_\\/]+`)))(?:,|$)/U', $aSQL['SELECT'], $aRegs)) {
             for ($i = 0; $i < count($aRegs[0]); $i++) {
                 // First we'll store the column information, later we'll loop though it to see which tables they refer to.
                 // 1: entire SELECT string incl. possible alias;
                 // 2: table referred to (fields without alias only);
                 // 3: table referred to (simple fields with alias only);
                 // 4: alias, if present.
                 // Try to see which table(s) is/are used here.
                 $aTables = array();
                 $sTable = $aRegs[2][$i] ? $aRegs[2][$i] : $aRegs[3][$i];
                 if ($sTable) {
                     $aTables[] = $sTable;
                 } else {
                     // OK, this was no simple SELECT string. This was GROUP_CONCAT, COUNT() or similar.
                     // Especially (GROUP_)CONCAT can contain quite some different columns and even tables.
                     // Analyzing the field definition... We don't care about its structure or anything... we just want tables.
                     // There should *always* be table aliases, so it's going to be easy.
                     // With subqueries however, this will fail a bit. It will find table aliases that may be of tables in the subquery.
                     //  However, in the worst case scenario it will keep tables that are not necessary to be kept.
                     if (preg_match_all('/\\b(\\w+)\\.(?:`|[A-Za-z]|\\*)/', $aRegs[1][$i], $aRegsTables)) {
                         $aTables = array_unique($aRegsTables[1]);
                     }
                 }
                 // Key: alias or, when not available, the SELECT statement (table.col).
                 $aColumnsUsed[$aRegs[4][$i] ? $aRegs[4][$i] : $aRegs[1][$i]] = array('SQL' => $aRegs[1][$i], 'tables' => $aTables);
                 // We don't need more info anyway.
             }
         }
         // Now, loop the parsed columns, check which fields are needed, rebuild the SELECT statement, and store which tables will be needed.
         $aSQL['SELECT'] = '';
         foreach ($aColumnsUsed as $sCol => $aCol) {
             if (in_array($sCol, $aColumnsNeeded)) {
                 $aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . $aCol['SQL'];
                 $aTablesNeeded = array_merge($aTablesNeeded, $aCol['tables']);
             }
         }
     }
     // Analyzing the WHERE... We don't care about AND/OR or anything... we just want tables.
     // WHERE clauses *always* contain the table aliases, so it's going to be easy.
     if (preg_match_all('/\\b(\\w+)\\.(?:`|[A-Za-z])/', $aSQL['WHERE'], $aRegs)) {
         $aTablesNeeded = array_merge($aTablesNeeded, $aRegs[1]);
     }
     // When we're running filters on the custom columns, we never use a table alias,
     // because we don't know where the column comes from.
     // To solve this, we must parse the column and fetch the used alias from the query.
     // We're specifically looking for custom columns *not* prefixed by a table alias.
     if (preg_match_all('/[^.](?:`(\\w+)\\/[A-Za-z0-9_\\/]+`)/', $aSQL['WHERE'], $aRegs)) {
         // To not reproduce code, we'll use lovd_getTableInfoByCategory().
         // Loop columns and find tables.
         foreach ($aRegs[1] as $sCategory) {
             $aTableInfo = lovd_getTableInfoByCategory($sCategory);
             if (isset($aTableInfo['table_sql']) && preg_match_all('/' . $aTableInfo['table_sql'] . ' AS (\\w+)\\b/i', $aSQL['FROM'], $aRegsTables)) {
                 $aTablesNeeded = array_merge($aTablesNeeded, $aRegsTables[1]);
             } else {
                 // OK, this really shouldn't happen. Either the column wasn't a
                 // category we recognized, or the SQL was too complicated?
                 // Let's log this.
                 lovd_writeLog('Error', 'LOVD-Lib', 'LOVD_Object::getRowCountForViewList() - Function identified custom column category ' . $sCategory . ', but couldn\'t find corresponding table alias in query.' . "\n" . 'URL: ' . preg_replace('/^' . preg_quote(rtrim(lovd_getInstallURL(false), '/'), '/') . '/', '', $_SERVER['REQUEST_URI']) . "\n" . 'From: ' . $aSQL['FROM']);
             }
         }
     }
     $aTablesNeeded = array_unique($aTablesNeeded);
     // Now, SELECT should be as small as possible. What's left in the SELECT is needed.
     // See which tables we can't remove from the JOIN because they're in SELECT, or because they're in the WHERE.
     // (INNER JOINs will never be removed).
     // Now shorten the JOIN as much as possible!
     // Tables *always* use aliases so we'll just search for those.
     // While matching, we add a space before the FROM so that we can match the first table as well, but it won't have a JOIN statement captured.
     $aTablesUsed = array();
     if (preg_match_all('/\\s?((?:LEFT(?: OUTER)?|INNER) JOIN)?\\s(' . preg_quote(TABLEPREFIX, '/') . '_[a-z0-9_]+) AS ([a-z0-9]+)\\s/', ' ' . $aSQL['FROM'], $aRegs)) {
         for ($i = 0; $i < count($aRegs[0]); $i++) {
             // 1: JOIN syntax;
             // 2: full table name;
             // 3: table alias.
             $aTablesUsed[$aRegs[3][$i]] = array('name' => $aRegs[2][$i], 'join' => $aRegs[1][$i]);
         }
     }
     // Loop these tables in reverse, and remove JOINs as much as possible!
     foreach (array_reverse(array_keys($aTablesUsed)) as $sTableAlias) {
         if (!$aTablesUsed[$sTableAlias]['join'] || in_array($sTableAlias, $aTablesNeeded)) {
             // We've reached a table that we need, abort now.
             break;
             // FIXME: Actually, it's possible that more tables can be left out, although in most cases we're really done now.
             //   To find out, we'd actually need to analyze which tables we're joining together.
         }
         // OK, this table is not needed. Get rid of it.
         if ($aTablesUsed[$sTableAlias]['join'] != 'INNER JOIN' && ($nPosition = strrpos($aSQL['FROM'], $aTablesUsed[$sTableAlias]['join'])) !== false) {
             $aSQL['FROM'] = rtrim(substr($aSQL['FROM'], 0, $nPosition));
             unset($aTablesUsed[$sTableAlias]);
         }
     }
     // If we have no SELECT left, we can surely do a simple SELECT COUNT(*) FROM ... or
     // a SELECT COUNT(*) FROM (SELECT ...)A. We can't do a simple SELECT COUNT(*) if
     // we have a GROUP_BY, because it will separate the counts.
     // In case we still have a SELECT, and we create a subquery while the
     // SELECT has double columns (happens rarely), we get a query error. In
     // that case we could drop the first column's declaration, or otherwise
     // keep using the SQL_CALC_FOUND_ROWS().
     // For now, we'll just take our chances. If this query will fail, LOVD
     // will fall back on the original SQL_CALC_FOUND_ROWS() method.
     $bInSubQuery = false;
     if (!$aSQL['SELECT']) {
         // If we just have one table left, we might be able to drop the GROUP BY.
         // If so, we can use a simple COUNT(*) query instead of a nested one.
         // In 99%, if not all, of the cases we can just drop the GROUP BY since
         // we "always" put it on the first table's ID, but just to be sure:
         if (count($aTablesUsed) == 1 && $aSQL['GROUP_BY'] == current(array_keys($aTablesUsed)) . '.id') {
             // Using one table, and grouping on its ID.
             $aSQL['GROUP_BY'] = '';
         }
         if (!$aSQL['GROUP_BY']) {
             // Simple SELECT COUNT(*) FROM ...
             $aSQL['SELECT'] = 'COUNT(*)';
         } else {
             // We'll have to create a bigger query around this...
             // We'll build that query in the end.
             $bInSubQuery = true;
             $aSQL['SELECT'] = '1';
         }
     } else {
         // SELECT is left (meaning we had a HAVING), we have to use a subquery!
         $bInSubQuery = true;
     }
     // Delete LIMIT, we don't want that anymore...
     $aSQL['LIMIT'] = '';
     $sSQLOut = $this->buildSQL($aSQL);
     // Now, build the subquery if we need it.
     if ($bInSubQuery) {
         $sSQLOut = 'SELECT COUNT(*) FROM (' . $sSQLOut . ')A';
     }
     if ($bDebug) {
         return $sSQLOut;
     }
     // Run the query, fetch the result and return.
     // We'll return false when we failed.
     $nCount = false;
     $qCount = $_DB->query($sSQLOut, $aArgs, false);
     if ($qCount !== false) {
         $nCount = $qCount->fetchColumn();
     }
     if ($nCount === false) {
         // We failed, log this. Actually, why aren't query errors logged if they're not fatal?
         lovd_queryError('QueryOptimizer', $sSQLOut, 'Error in ' . __FUNCTION__ . '() while executing optimized query.', false);
         // As a fallback, use SQL_CALC_FOUND_ROWS() for MySQL instances, or
         // a count() on a full result set otherwise. The latter is super
         // inefficient, and only meant for small SQLite databases.
         if ($_INI['database']['driver'] == 'mysql') {
             $this->aSQLViewList['SELECT'] = 'SQL_CALC_FOUND_ROWS ' . $this->aSQLViewList['SELECT'];
             $this->aSQLViewList['LIMIT'] = '0';
             $_DB->query($this->buildSQL($this->aSQLViewList), $aArgs);
             $nCount = $_DB->query('SELECT FOUND_ROWS()')->fetchColumn();
         } else {
             // Super inefficient, only for low-volume (sqlite) databases!
             $nCount = count($_DB->query($this->buildSQL($this->aSQLViewList), $aArgs)->fetchAllColumn());
         }
     }
     return $nCount;
 }