/** * Returns the indexes for type $type. * * @param string $type * * @return array $indexInfo index information */ protected function getIndexes($type) { $table = $this->esc($type, TRUE); $indexes = $this->adapter->get("PRAGMA index_list('{$table}')"); $indexInfoList = array(); foreach ($indexes as $i) { $indexInfoList[$i['name']] = $this->adapter->getRow("PRAGMA index_info('{$i['name']}') "); $indexInfoList[$i['name']]['unique'] = $i['unique']; } return $indexInfoList; }
/** * @see RedBean_QueryWriter::addIndex */ public function addIndex($type, $name, $column) { $table = $type; $table = $this->getTableName($table); $name = preg_replace('/\\W/', '', $name); $column = $this->esc($column); $index = $this->adapter->getRow("SELECT 1 as `exists` FROM db_index WHERE index_name = ? ", array($name)); if ($index && $index['exists']) { return; // positive number will return, 0 will continue. } try { $this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) "); } catch (Exception $e) { } }
/** * @see RedBean_QueryWriter::queryRecordLink */ public function queryRecordLink($sourceType, $destType, $sourceID, $destID) { list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType); $key = $this->getCacheKey(array($sourceType, $destType, $sourceID, $destID)); if ($this->flagUseCache && ($cached = $this->getCached($linkTable, $key))) { return $cached; } if ($sourceTable === $destTable) { $sql = "SELECT {$linkTable}.* FROM {$linkTable}\n\t\t\t\tWHERE ( {$sourceCol} = ? AND {$destCol} = ? ) OR\n\t\t\t\t ( {$destCol} = ? AND {$sourceCol} = ? ) -- keep-cache"; $row = $this->adapter->getRow($sql, array($sourceID, $destID, $sourceID, $destID)); } else { $sql = "SELECT {$linkTable}.* FROM {$linkTable}\n\t\t\t\tWHERE {$sourceCol} = ? AND {$destCol} = ? -- keep-cache"; $row = $this->adapter->getRow($sql, array($sourceID, $destID)); } $this->putResultInCache($linkTable, $key, $row); return $row; }
/** * @see RedBean_QueryWriter::addFK */ public function addFK($type, $targetType, $field, $targetField, $isDep = FALSE) { try { $table = $this->esc($type); $column = $this->esc($field); $tableNoQ = $this->esc($type, TRUE); $columnNoQ = $this->esc($field, TRUE); $targetTable = $this->esc($targetType); $targetTableNoQ = $this->esc($targetType, TRUE); $targetColumn = $this->esc($targetField); $targetColumnNoQ = $this->esc($targetField, TRUE); $sql = "SELECT\n\t\t\t\ttc.constraint_name, tc.table_name,\n\t\t\t\tkcu.column_name, ccu.table_name AS foreign_table_name,\n\t\t\t\tccu.column_name AS foreign_column_name,rc.delete_rule\n\t\t\t\tFROM information_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name\n\t\t\t\tJOIN information_schema.referential_constraints AS rc ON ccu.constraint_name = rc.constraint_name\n\t\t\t\tWHERE constraint_type = 'FOREIGN KEY' AND tc.table_catalog=current_database()\n\t\t\t\t\tAND tc.table_name = '{$tableNoQ}'\n\t\t\t\t\tAND ccu.table_name = '{$targetTableNoQ}'\n\t\t\t\t\tAND kcu.column_name = '{$columnNoQ}'\n\t\t\t\t\tAND ccu.column_name = '{$targetColumnNoQ}'\n\t\t\t"; $row = $this->adapter->getRow($sql); $flagAddKey = FALSE; if (!$row) { $flagAddKey = TRUE; } if ($row) { if ($row['delete_rule'] == 'SET NULL' && $isDep || $row['delete_rule'] != 'SET NULL' && !$isDep) { // Delete old key and order a new one $flagAddKey = TRUE; $cName = $row['constraint_name']; $sql = "ALTER TABLE {$table} DROP CONSTRAINT {$cName} "; $this->adapter->exec($sql); } } if ($flagAddKey) { $delRule = $isDep ? 'CASCADE' : 'SET NULL'; $this->adapter->exec("ALTER TABLE {$table}\n\t\t\t\t\tADD FOREIGN KEY ( {$column} ) REFERENCES {$targetTable} (\n\t\t\t\t\t{$targetColumn}) ON DELETE {$delRule} ON UPDATE SET NULL DEFERRABLE ;"); return TRUE; } return FALSE; } catch (Exception $e) { return FALSE; } }