/** * Static function to refresh src_org_cache from src_org * * @param Source|src_id|src_uuid $source * @return int number of rows cached */ public static function refresh_cache($source) { $conn = AIR2_DBManager::get_master_connection(); // get the src_id $src_id; if (is_numeric($source)) { $src_id = $source; } elseif (is_string($source)) { $q = 'select src_id from source where src_uuid = ?'; $src_id = $conn->fetchOne($q, array($source), 0); } elseif (is_object($source)) { $src_id = $source->src_id; } // sanity! if (!$src_id) { Carper::carp("Source !exists"); return; } // delete all src_org_cache recs for this source $conn->execute("delete from src_org_cache where soc_src_id = {$src_id}"); // array of org_id => status $org_status = Source::get_authz_status($src_id); // bulk-insert $vals = array(); foreach ($org_status as $org_id => $status) { $vals[] = "({$src_id},{$org_id},'{$status}')"; } if (count($vals)) { $vals = implode(',', $vals); $stmt = "insert into src_org_cache (soc_src_id, soc_org_id, soc_status)"; $conn->execute("{$stmt} values {$vals}"); } return count($vals); }
/** * Destroy stuff in the database */ function __destruct() { $conn = AIR2_DBManager::get_master_connection(); // build the "where" $where = array(); foreach ($this->where_fields as $idx => $fld) { $where[] = "{$fld} = ?"; } $where = implode(' and ', $where); // count before deleting $q = "select count(*) from {$this->tbl_name} where {$where}"; $num = $conn->fetchOne($q, $this->where_values, 0); if ($num > $this->max_delete) { $msg = "UNABLE TO CLEANUP - more than {$this->max_delete} rows"; $msg .= " returned by query --> {$q}"; throw new Exception($msg); } // execute delete $q = "delete from {$this->tbl_name} where {$where}"; $del = $conn->exec($q, $this->where_values); if ($del != $num) { $msg = "PROBLEM CLEANING UP: expected to delete {$num}, got {$del}!"; $msg .= " Query --> {$q}"; throw new Exception($msg); } // debug output if (getenv('AIR_DEBUG')) { diag("TestCleanup deleted {$del} stale {$this->tbl_name}"); } }
/** * Delete from the database on exit */ function __destruct() { if ($this->my_uuid) { $conn = AIR2_DBManager::get_master_connection(); $conn->exec('delete from tag where tag_tm_id in (select tm_id from ' . 'tag_master where tm_name = ?)', array($this->my_uuid)); } trec_destruct($this); }
/** * Prequery hook */ public function preQuery() { if ($this->gettype() == Doctrine_Query::SELECT) { $this->set_connection(AIR2_DBManager::get_slave_connection()); } else { $this->set_connection(AIR2_DBManager::get_master_connection()); } }
/** * Delete from the database on exit */ function __destruct() { if ($this->my_uuid) { // make sure any src_export records are cleaned up $conn = AIR2_DBManager::get_master_connection(); $bid = 'select bin_id from bin where bin_uuid = ?'; $del = "delete from src_export where se_xid = ({$bid}) and se_ref_type = ?"; $conn->exec($del, array($this->my_uuid, SrcExport::$REF_TYPE_BIN)); } trec_destruct($this); }
/** * Merge 2 source records together, including all related data. * * @param Source $prime the record to merge into (will be preserved) * @param Source $merge the record to merge from (will be deleted) * @param array $options (optional) options to use in merging conflicting data * @param boolean $commit (optional) * @return boolean|string|array */ public static function merge($prime, $merge, $options = array(), $commit = false) { self::$MERGE_OPTS = $options; self::$MERGE_RESULT = array('errs' => array(), 'prime' => array(), 'merge' => array(), 'moved' => array(), 'skipped' => array()); // make sure we have fresh objects $prime->clearRelated(); $merge->clearRelated(); // start a transaction $conn = AIR2_DBManager::get_master_connection(); $conn->beginTransaction(); // run the merges try { self::merge_source($prime, $merge); self::merge_facts($prime, $merge); // remaining data produces no errors... so only run on commit if ($commit) { self::merge_easy($prime, $merge); self::combine_orgs($prime, $merge); self::combine_stat($prime, $merge); } // delete the merged source $merge->delete(); } catch (Exception $e) { $conn->rollback(); self::$MERGE_RESULT['fatal_error'] = $e->getMessage(); return self::$MERGE_RESULT['fatal_error']; } // cache the merge result self::$MERGE_RESULT['result'] = $prime->toArray(true); // commit or rollback $errors = self::$MERGE_RESULT['errs']; if (count($errors) == 0 && $commit) { $conn->commit(); } else { $conn->rollback(); $prime->clearRelated(); $prime->refresh(); $merge->clearRelated(); $merge->refresh(); } // return errors or true on success if (count($errors) > 0) { return $errors; } else { return true; } }
/** * Create * * @param array $data * @return UserOrg $rec */ protected function air_create($data) { $this->require_data($data, array('osid_type', 'osid_xuuid')); if (!in_array($data['osid_type'], array('E', 'M'))) { throw new Rframe_Exception(Rframe::BAD_DATA, "invalid osid_type"); } // unique osid_type per organization $conn = AIR2_DBManager::get_master_connection(); $q = 'select count(*) from org_sys_id where osid_org_id = ? and osid_type = ?'; $p = array($this->parent_rec->org_id, $data['osid_type']); $n = $conn->fetchOne($q, $p, 0); if ($n > 0) { throw new Rframe_Exception(Rframe::BAD_DATA, "osid_type already in use"); } $os = new OrgSysId(); $os->osid_org_id = $this->parent_rec->org_id; return $os; }
/** * Add sources to a PINfluence * * @param Outcome $outcome * @param Bin $bin * @param string $type */ public static function add_sources_from_bin($outcome, $bin, $type) { $stats = array('total' => 0, 'insert' => 0, 'duplicate' => 0, 'invalid' => 0); $bin_id = $bin->bin_id; $b_where = "where bsrc_bin_id={$bin_id}"; //hardcode so i don't get mixed up // totals $conn = AIR2_DBManager::get_master_connection(); $stats['total'] = $conn->fetchOne("select count(*) from bin_source {$b_where}", array(), 0); // duplicates $b_s = "select bsrc_src_id from bin_source {$b_where}"; $b_q = "select count(*) from src_outcome where sout_out_id=? and sout_src_id in ({$b_s})"; $stats['duplicate'] += $conn->fetchOne($b_q, array($outcome->out_id), 0); // insert $user_id = $outcome->UpdUser->user_id; $s_where = "where src_id in ({$b_s})"; $s_s = "select ?, src_id, ?, ?, ?, ?, ? from source {$s_where}"; $s_q = "insert ignore into src_outcome (sout_out_id,sout_src_id, sout_type, sout_cre_user, sout_cre_dtim, sout_upd_user, sout_upd_dtim) {$s_s}"; $stats['insert'] += $conn->exec($s_q, array($outcome->out_id, $type, $user_id, air2_date(), $user_id, air2_date())); $stats['invalid'] += $stats['total'] - $stats['insert'] - $stats['duplicate']; return $stats; }
/** * Mark inquiry as stale * * @param InqOrg $rec */ protected function update_parent(InqOrg $rec) { // raw sql update rather than calling parent_rec->save() // because nested objects cascade $upd = 'update inquiry set inq_stale_flag=1 where inq_id = ?'; AIR2_DBManager::get_master_connection()->exec($upd, array($rec->iorg_inq_id)); }
/** * Public utility to log a src_activity without using doctrine. * * @param int $usrid * @param array $srcids * @param string $dtim * @param string $desc * @param string $note */ public static function log_raw($usrid, $srcids, $dtim, $desc, $note) { // collect default values if (!$dtim) { $dtim = air2_date(); } if (!$desc) { $desc = ''; } if (!$note) { $note = ''; } // create generic mapping $mapping = array('src_activity' => array('sact_src_id' => array('map' => 0), 'sact_actm_id' => array('val' => 40), 'sact_dtim' => array('val' => $dtim), 'sact_desc' => array('val' => $desc), 'sact_notes' => array('val' => $note), 'sact_cre_user' => array('val' => $usrid), 'sact_upd_user' => array('val' => $usrid), 'sact_cre_dtim' => array('val' => $dtim), 'sact_upd_dtim' => array('val' => $dtim))); // import data (FAST if there are alot of rows) $rdr = new ArrayReader($srcids); $conn = AIR2_DBManager::get_master_connection(); if (count($srcids) < 10) { $wrt = new SqlWriter($conn, $mapping); $wrt->write_data($rdr); } else { $wrt = new MySqlImporter('/tmp', $mapping, $conn); $wrt->write_data($rdr); $wrt->exec_load_infile(); } // check for errors $errs = $wrt->get_errors(); if (count($errs) > 0) { $str = implode(', ', $errs); throw new Exception("Errors on activity logging: {$str}"); } }
/** * Annotate sources in a bin (must have write-authz on the actual source) * * @param User $u * @param Bin $bin * @param array $note * @return array $counts */ public function annotate_sources($u, $bin, $note) { $stats = self::init_stats($stats = array('total', 'insert', 'duplicate', 'invalid')); // validate note $note = is_string($note) ? trim($note) : $note; if (!$note || !strlen($note)) { throw new Exception("Invalid annotation '{$note}'"); } // calculate total $conn = AIR2_DBManager::get_master_connection(); $q = "select count(*) from bin_source where bsrc_bin_id = ?"; $stats['total'] = $conn->fetchOne($q, array($bin->bin_id), 0); // fast-sql insert $read_org_ids = $u->get_authz_str(ACTION_ORG_SRC_UPDATE, 'soc_org_id'); $cache = "select soc_src_id from src_org_cache where {$read_org_ids}"; $where = "where bsrc_bin_id=? and bsrc_src_id in ({$cache})"; $select = "select bsrc_src_id,?,?,?,?,? from bin_source {$where}"; $flds = 'srcan_src_id,srcan_value,srcan_cre_user,srcan_upd_user,srcan_cre_dtim,srcan_upd_dtim'; $ins = "insert into src_annotation ({$flds}) {$select}"; $params = array($note, $u->user_id, $u->user_id, air2_date(), air2_date(), $bin->bin_id); $stats['insert'] = $conn->exec($ins, $params); // invalid == no write-authz on source $stats['invalid'] = $stats['total'] - $stats['insert'] - $stats['duplicate']; return $stats; }
/** * Create a SrcActivity record for a Source. * * @param Source $src */ public function process_source(Source $src) { if ($this->tact_type != self::$TYPE_SOURCE) { return; } // get data $data = array('sact_actm_id' => $this->tact_actm_id, 'sact_src_id' => $src->src_id, 'sact_prj_id' => $this->tact_prj_id, 'sact_dtim' => $this->tact_dtim, 'sact_desc' => $this->tact_desc, 'sact_notes' => $this->tact_notes, 'sact_cre_user' => $this->Tank->tank_user_id, 'sact_upd_user' => $this->Tank->tank_user_id, 'sact_cre_dtim' => $this->Tank->tank_cre_dtim, 'sact_upd_dtim' => $this->Tank->tank_upd_dtim); if ($this->tact_xid && $this->tact_ref_type) { $data['sact_xid'] = $this->tact_xid; $data['sact_ref_type'] = $this->tact_ref_type; } // run raw-sql for efficiency $conn = AIR2_DBManager::get_master_connection(); $flds = implode(',', array_keys($data)); $vals = air2_sql_param_string($data); $q = "insert into src_activity ({$flds}) values ({$vals})"; $conn->exec($q, array_values($data)); }
/** * Insert or update record in the database. * * @param object $conn (optional) */ public function replace(Doctrine_Connection $conn = null) { // unless explicitly passed, we find the _master connection // for the current env. if ($conn === null) { $conn = AIR2_DBManager::get_master_connection(); } parent::replace($conn); }
/** * Forces a source to be opted-into APMG * * @param int $src_id * @return bool $inserted */ public static function force_apmg($src_id) { $data = array('so_src_id' => $src_id, 'so_org_id' => Organization::$APMPIN_ORG_ID, 'so_uuid' => air2_generate_uuid(), 'so_effective_date' => air2_date(), 'so_home_flag' => 0, 'so_status' => self::$STATUS_OPTED_IN, 'so_cre_user' => 1, 'so_upd_user' => 1, 'so_cre_dtim' => air2_date(), 'so_upd_dtim' => air2_date()); $flds = implode(',', array_keys($data)); $vals = air2_sql_param_string($data); $stmt = "insert ignore into src_org ({$flds}) values ({$vals})"; // execute $conn = AIR2_DBManager::get_master_connection(); $n = $conn->exec($stmt, array_values($data)); return $n; }
/** * Cache a reference to the master db connection. */ public function __construct() { AIR2_DBManager::$FORCE_MASTER_ONLY = true; $this->conn = AIR2_DBManager::get_master_connection(); $this->src_table = Doctrine::getTable('Source'); }
/** * CASCADEs delete to change contact_user_id on ProjectOrg to AIR2SYSTEM, * and delete any image assets * * @param Doctrine_Event $event * @return int number of rows affected */ public function preDelete($event) { // nuke any avatar files if ($this->Avatar && $this->Avatar->exists()) { $this->Avatar->delete(); } // do not orphan any project_org records, nor hit FK constraint. $conn = AIR2_DBManager::get_master_connection(); $sql = "UPDATE project_org SET porg_contact_user_id = 1 WHERE " . "porg_contact_user_id = {$this->user_id}"; return $conn->execute($sql); }
/** * Make sure the user always has one home flag * * @param Doctrine_Event $event */ public function postSave($event) { if ($this->uo_home_flag) { $conn = AIR2_DBManager::get_master_connection(); $conn->execute("UPDATE user_org SET uo_home_flag = FALSE WHERE " . "uo_user_id = ? AND uo_id != ?", array($this->uo_user_id, $this->uo_id)); } parent::postSave($event); }
/** * Tag an object using raw connections. Returns true if the tag was added, * or false if the tag already existed. * * @param int $xid * @param string $type * @param string $tag * @return boolean */ public static function make_tag($xid, $type, $tag) { // sanity! if (!is_numeric($xid) || $xid < 1) { throw new Exception("Bad tag XID({$xid})"); } if (!in_array($type, array('I', 'P', 'S', 'R'))) { throw new Exception("Bad tag TYPE({$type})"); } if (!is_string($tag) || strlen($tag) < 1) { throw new Exception("Bad tag '{$tag}'"); } // fetch/create tag master $tm_id = TagMaster::get_tm_id($tag); // params $usrid = defined('AIR2_REMOTE_USER_ID') ? AIR2_REMOTE_USER_ID : 1; $dtim = air2_date(); $cols = array('tag_tm_id', 'tag_xid', 'tag_ref_type', 'tag_cre_user', 'tag_upd_user', 'tag_cre_dtim', 'tag_upd_dtim'); $vals = array($tm_id, $xid, $type, $usrid, $usrid, $dtim, $dtim); // insert ignore $conn = AIR2_DBManager::get_master_connection(); $colstr = implode(',', $cols); $params = air2_sql_param_string($cols); $n = $conn->exec("insert ignore into tag ({$colstr}) values ({$params})", $vals); // if tag existed, update userstamp $tag_was_new = true; if ($n == 0) { $tag_was_new = false; $set = "tag_upd_user={$usrid}, tag_upd_dtim='{$dtim}'"; $where = "tag_tm_id={$tm_id} and tag_xid={$xid} and tag_ref_type='{$type}'"; $conn->exec("update tag set {$set} where {$where}"); } return $tag_was_new; }
/** * Custom delete procedure to cleanup bin_src_response_sets * * @throws Rframe_Exceptions * @param BinSource $rec */ protected function rec_delete(BinSource $rec) { $sid = $rec->bsrc_src_id; $bid = $rec->bsrc_bin_id; // normal delete stuff $this->check_authz($rec, 'delete'); $rec->delete(); $this->update_parent($rec); // delete any orphaned submissions $conn = AIR2_DBManager::get_master_connection(); $conn->exec("delete from bin_src_response_set where bsrs_bin_id={$bid} and bsrs_src_id={$sid}"); }
/** * Mark inquiry as stale * * @param Question $rec */ protected function update_parent(Question $rec) { $upd = 'update inquiry set inq_stale_flag=1 where inq_id = ?'; AIR2_DBManager::get_master_connection()->exec($upd, array($rec->ques_inq_id)); }
/** * Fix a primary column on a table, making sure that each source only has * one primary set. * * @param AIR2_Record $rec * @param boolean $delete */ function air2_fix_src_primary($rec, $delete = false) { $conn = AIR2_DBManager::get_master_connection(); $tbl = $rec->getTable()->getTableName(); // find column names $idcol = false; $fkcol = false; $flagcol = false; $updcol = false; $cols = $rec->getTable()->getColumnNames(); foreach ($cols as $col) { if (preg_match('/^[a-z]+_id$/', $col)) { $idcol = $col; } elseif (preg_match('/^[a-z]+_src_id$/', $col)) { $fkcol = $col; } elseif (preg_match('/_primary_flag$/', $col) || preg_match('/_home_flag$/', $col)) { $flagcol = $col; } elseif (preg_match('/_upd_dtim$/', $col)) { $updcol = $col; } } // sanity! if (!$idcol || !$fkcol || !$flagcol || !$updcol) { throw new Exception("Missing a column"); } // saved? or deleted? if (!$delete) { if ($rec[$flagcol]) { // unset everyone else $q = "update {$tbl} set {$flagcol}=0 where {$fkcol}=? and {$idcol}!=?"; $conn->exec($q, array($rec[$fkcol], $rec[$idcol])); } else { // check for existing primary $q = "select {$idcol}, {$flagcol} from {$tbl} where {$fkcol} = ?"; $rs = $conn->fetchAll($q, array($rec[$fkcol])); $has_primary = false; foreach ($rs as $row) { if ($row[$flagcol]) { $has_primary = true; } } // should I or someone else be primary? if (!$has_primary && count($rs) == 1) { // it's me! $q = "update {$tbl} set {$flagcol}=1 where {$fkcol}=? and {$idcol}=?"; $conn->exec($q, array($rec[$fkcol], $rec[$idcol])); //TODO: better solution? Can't figure out why refresh fails if ($rec->exists()) { try { $rec->refresh(); } catch (Doctrine_Record_Exception $e) { if ($e->getCode() == 0 && preg_match('/does not exist/i', $e->getMessage())) { // ignore, I guess } else { throw $e; //rethrow } } } } elseif (!$has_primary && count($rs) > 1) { // pick most recent that isn't me $q = "update {$tbl} set {$flagcol}=1 where {$fkcol}=? and {$idcol}!=?"; // find upd_dtim column $data = $rec->toArray(); foreach ($data as $key => $val) { if (preg_match('/upd_dtim$/', $key)) { $q .= " order by {$key} desc"; } } $conn->exec("{$q} limit 1", array($rec[$fkcol], $rec[$idcol])); } } } else { //deleted - pick most recent $q = "update {$tbl} set {$flagcol}=1 where {$fkcol}=? and {$idcol}!=?"; // find upd_dtim column $data = $rec->toArray(); foreach ($data as $key => $val) { if (preg_match('/upd_dtim$/', $key)) { $q .= " order by {$key} desc"; } } $conn->exec("{$q} limit 1", array($rec[$fkcol], $rec[$idcol])); } }
/** * Create or update a SrcOrg record for a Source. * * @param Source $src */ public function process_source(Source $src) { // run raw-sql for efficiency $conn = AIR2_DBManager::get_master_connection(); $src_id = $src->src_id; $org_id = $this->to_org_id; $unset_home_flags = false; // check for existing (and total) $where = "where so_src_id = {$src_id} and so_org_id = {$org_id}"; $status = "(select so_status from src_org {$where}) as status"; $ishome = "(select so_home_flag from src_org {$where}) as ishome"; $select = "select count(*) as total, {$status}, {$ishome}"; $q = "{$select} from src_org where so_src_id = {$src_id}"; $rs = $conn->fetchRow($q); // run operation $op = false; $data = array(); if (is_null($rs['status'])) { // insert $data = array('so_src_id' => $src_id, 'so_org_id' => $org_id, 'so_uuid' => air2_generate_uuid(), 'so_effective_date' => air2_date(), 'so_home_flag' => $this->to_so_home_flag ? 1 : 0, 'so_status' => $this->to_so_status, 'so_cre_user' => $this->Tank->tank_user_id, 'so_upd_user' => $this->Tank->tank_user_id, 'so_cre_dtim' => $this->Tank->tank_cre_dtim, 'so_upd_dtim' => $this->Tank->tank_upd_dtim); // determine home flag if ($rs['total'] == 0) { $data['so_home_flag'] = true; } elseif ($this->to_so_home_flag && $data['total'] > 0) { $unset_home_flags = true; } // insert $flds = implode(',', array_keys($data)); $vals = air2_sql_param_string($data); $q = "insert into src_org ({$flds}) values ({$vals})"; $conn->exec($q, array_values($data)); } else { // update $updates = array(); // change to status if ($rs['status'] != $this->to_so_status) { $updates[] = "so_status='" . $this->to_so_status . "'"; } // change to home flag (only allow setting, not unsetting) if ($this->to_so_home_flag && !$rs['ishome']) { $updates[] = "so_home_flag=1"; //MUST be true if ($rs['total'] > 1) { $unset_home_flags = true; } } // do we need to do anything? if (count($updates)) { $set = implode(', ', $updates); $where = "so_src_id={$src_id} and so_org_id={$org_id}"; $q = "update src_org set {$set} where {$where}"; $conn->exec($q); } } // optionally unset other home flags if ($unset_home_flags) { $set = 'so_home_flag=0'; $where = "so_src_id={$src_id} and so_org_id!={$org_id}"; $conn->exec("update src_org set {$set} where {$where}"); } }
#!/usr/bin/env php <?php require_once realpath(dirname(__FILE__) . '/../app/init.php'); require_once 'AIR2_DBManager.php'; /** * create-tbl.php * * This utility lets you create single tables from doctrine models. * */ AIR2_DBManager::init(); $conn = AIR2_DBManager::get_master_connection(); echo "Enter the name of the Doctrine model > "; $modelname = trim(fgets(STDIN)); if (strlen($modelname) < 1) { echo "Error! No model specified!\n"; exit(1); } try { $tbl = Doctrine::getTable($modelname); } catch (Exception $e) { echo "Error!\n" . $e->getMessage() . "\n"; exit(1); } try { $conn->execute('describe ' . $tbl->getTableName()); echo "Table already exists in database!\nDrop table and recreate? (y/n) > "; $drop = strtolower(trim(fgets(STDIN))); if ($drop == 'y') { try { $conn->execute('drop table ' . $tbl->getTableName());
/** * Make sure a record has unique from/to translation * * @param TranslationMap $rec */ private function check_translation($rec) { $conn = AIR2_DBManager::get_master_connection(); // build query $w = "xm_fact_id= ? and xm_xlate_from = ?"; $q = "select count(*) from translation_map where {$w}"; $params = array($rec->xm_fact_id, $rec->xm_xlate_from); if ($rec->xm_id) { $q .= " and xm_id != ?"; $params[] = $rec->xm_id; } $n = $conn->fetchOne($q, $params, 0); if ($n > 0) { $s = "fact_id(" . $rec->xm_fact_id . ") - text(" . $rec->xm_xlate_from . ")"; throw new Rframe_Exception("Non unique translation - {$s}"); } }
/** * Calls set_src_status() (which just returns the status) * and then executes a SQL update directly on the source table. */ public function set_and_save_src_status() { // this only works AFTER the source is saved if ($this->src_id) { AIR2_DBManager::$FORCE_MASTER_ONLY = true; $stat = $this->set_src_status(); $conn = AIR2_DBManager::get_master_connection(); $conn->execute("update source set src_status='{$stat}' where src_id=" . $this->src_id); } }
/** * Magic path to mark submissions as unfavorite * * @param string @srs_uuid * @param unknown $srs_uuid */ public function unfavorite($srs_uuid) { $conn = AIR2_DBManager::get_master_connection(); $srsid = $conn->fetchOne('select srs_id from src_response_set where srs_uuid = ?', array($srs_uuid), 0); if (!$srsid) { return; } $now = air2_date(); $flds = "usrs_user_id, usrs_srs_id, usrs_favorite_flag, usrs_cre_dtim, usrs_upd_dtim"; $ondup = "on duplicate key update usrs_favorite_flag=0,usrs_upd_dtim='{$now}'"; $ins = "insert into user_srs ({$flds}) values (?,{$srsid},0,'{$now}','{$now}') {$ondup}"; $n = $conn->exec($ins, array($this->user->user_id)); air2_touch_stale_record('src_response_set', $srsid); }