function get_associations()
{
$d = new DBSelector();
$d->add_table('ar', 'allowable_relationship');
$d->add_table('allowable_relationship');
$d->add_table('relationship');
$d->add_table('entity');
$d->add_relation('allowable_relationship.name = "site_to_type"');
$d->add_relation('allowable_relationship.id = relationship.type');
$d->add_relation('relationship.entity_a = ' . $this->admin_page->site_id);
$d->add_relation('relationship.entity_b = ar.relationship_b');
$d->add_relation('entity.id = ar.relationship_b');
$d->add_field('entity', 'id', 'e_id');
$d->add_field('entity', 'name', 'e_name');
$d->add_field('ar', '*');
$d->add_relation('ar.relationship_a = ' . $this->admin_page->type_id);
if (reason_relationship_names_are_unique()) {
$d->add_relation('ar.type = "association"');
} else {
$d->add_relation('ar.name != "owns"');
}
$d->add_relation('(ar.custom_associator IS NULL OR ar.custom_associator = "")');
$r = db_query($d->get_query(), 'Error selecting relationships');
$return_me = array();
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
$return_me[$row['id']] = $row;
}
$this->associations = $return_me;
if (empty($this->admin_page->rel_id)) {
reset($this->associations);
list($key, ) = each($this->associations);
$this->admin_page->rel_id = $key;
}
}
function load_images($association_id, $entity_id)
{
$dbq = new DBSelector();
$dbq->add_table('i', 'image');
$dbq->add_table('m', 'meta');
$dbq->add_table('r', 'relationship');
$dbq->add_field('i', 'id');
$dbq->add_field('i', 'width');
$dbq->add_field('i', 'height');
$dbq->add_field('i', 'size');
$dbq->add_field('i', 'image_type');
$dbq->add_field('m', 'description');
$dbq->add_relation('r.type = ' . $association_id);
$dbq->add_relation('r.entity_a = ' . $entity_id);
$dbq->add_relation('r.entity_b = i.id');
$dbq->add_relation('i.id = m.id');
$res = db_query($dbq->get_query(), 'Error retrieving images');
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
$this->images[$row['id']] = $row;
}
}
function get_entity_relationships_info($entity, $right)
{
$dbq = new DBSelector();
$dbq->add_table('r', 'relationship');
$dbq->add_field('r', 'entity_a');
$dbq->add_field('r', 'entity_b');
$dbq->add_field('r', 'type');
$dbq->add_table('ar', 'allowable_relationship');
$dbq->add_field('ar', 'id');
$dbq->add_field('ar', 'type');
$dbq->add_field('ar', 'relationship_a');
$dbq->add_field('ar', 'relationship_b');
$dbq->add_field('ar', 'description');
if ($right) {
$dbq->add_relation('r.entity_b = ' . $entity->id());
} else {
$dbq->add_relation('r.entity_a = ' . $entity->id());
}
$dbq->add_relation('ar.id = r.type');
if (reason_relationship_names_are_unique()) {
$dbq->add_relation('ar.type = "association"');
} else {
$dbq->add_relation('ar.name != "owns"');
$dbq->add_relation('ar.name != "borrows"');
}
$rels = $dbq->run();
return $rels;
}
while( list( ,$t ) = each( $tables ) )
{
$q->add_table( $t );
if( $t != 'entity' )
$q->add_relation( 'entity.id = '.$t.'.id' );
}
$q->add_relation( 'entity.id != '.$id );
reset( $fields );
while( list( ,$f ) = each( $fields ) )
{
if( $d[$f] )
$q->add_relation( $f.' = "'.addslashes($d[$f]).'"' );
else
$q->add_relation( $f.' IS NULL' );
}
$q->add_field( 'entity','id' );
//echo $q->get_query().'<br /><br />';
$ids_to_delete = $q->run();
//pray( $ids_to_delete );
reset( $ids_to_delete );
while( list( ,$id_to_delete ) = each ( $ids_to_delete ) )
{
$itd = $id_to_delete[ 'id' ];
// delete id from each table
reset( $tables );
while( list( $key,$table ) = each ( $tables ) )
{
$q = "DELETE FROM $table WHERE id = '$itd'";
$output .= $q.";\n";
//$r = mysql_query( $q ) OR die( 'unable to delete from table '.$table.': '.mysql_error() );
}
function get_owner_site_id($entity_id)
{
// do we really need the whole object?
//$d = get_entity_by_id_object( $entity_id );
$d = new DBSelector();
$d->add_table('entity');
$d->add_relation('entity.id = ' . $entity_id);
$d->add_field('r', 'entity_a', 'site_id');
$d->add_table('ar', 'allowable_relationship');
$d->add_table('r', 'relationship');
$d->add_relation('ar.id = r.type');
if (reason_relationship_names_are_unique()) {
$d->add_relation('ar.type = "owns"');
} else {
$d->add_relation('ar.name = "owns"');
}
$d->add_relation('r.entity_b = ' . $entity_id);
$d->set_num(1);
$r = db_query($d->get_query(), 'Error getting owning site ID.');
if ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
return $row['site_id'];
} else {
return false;
}
}
/**
* As well as removing the borrowing relationship, this removes relationships on the site where the
* entity being unborrowed is on the "b" side of the relationship. If the site also owns the entity
* being unborrowed (not a good state) but it can happen, we skip this relationship deletion phase.
*/
function delete_borrowed_relationship( $site_id , $id , $rel_id ) // {{{ //{{{
{
// if we don't also own the entity, delete associations from the site where our entity is on the b side.
if (!site_owns_entity( $site_id, $id ))
{
$dbq = new DBSelector;
//actual relationships that we are selecting
$dbq->add_table( 'r' , 'relationship' );
$dbq->add_table( 'ar' , 'allowable_relationship' );
$dbq->add_field( 'r' , '*' );
$dbq->add_relation( 'r.type = ar.id' );
$dbq->add_relation( 'r.entity_b = ' . $id );
//owns relationship table
$dbq->add_table( 'r2' , 'relationship' );
$dbq->add_table( 'ar2' , 'allowable_relationship' );
if (!reason_relationship_names_are_unique())
{
$dbq->add_relation( 'ar2.name = "owns"' );
}
else
{
$dbq->add_relation( 'ar2.type = "owns"' );
}
$dbq->add_relation( 'r2.type = ar2.id' );
$dbq->add_relation( 'r2.entity_a = ' . $site_id );
$dbq->add_relation( 'r2.entity_b = r.entity_a' );
//current borrowship
$dbq->add_table( 'r3' , 'relationship' );
$dbq->add_table( 'ar3' , 'allowable_relationship' );
$dbq->add_relation( 'ar3.id = ' . $rel_id );
$dbq->add_relation( 'r3.type = ar3.id' );
$dbq->add_relation( 'r3.entity_a = ' . $site_id );
$dbq->add_relation( 'r3.entity_b = ' . $id );
$x = $dbq->run();
if( $x )
{
$first = true;
$in = '';
foreach( $x AS $rel )
{
if (!$first) $in .= ',';
else $first = false;
$in .= $rel[ 'id' ];
}
$q = 'DELETE FROM relationship WHERE id IN(' . $in . ')';
db_query( $q , 'Error removing associations of borrowed item before deleting' );
}
}
$q = 'DELETE FROM relationship WHERE entity_a = ' . $site_id . ' AND entity_b = ' . $id . ' AND type = ' . $rel_id;
db_query( $q , 'Error removing borrowship' );
}
function delete_orphan_page_parent($orphaned_page_id)
{
$d = new DBSelector();
$d->add_table('relationship');
$d->add_field('relationship', 'entity_a');
$d->add_field('relationship', 'entity_b');
$d->add_field('relationship', 'id');
$d->add_relation('relationship.entity_a =' . $orphaned_page_id);
$d->add_relation('relationship.entity_b =' . $orphaned_page_id);
$results = current($d->run());
$id = $results['id'];
delete_relationship($id);
}
function get_relationships($dir = 'left')
{
$q = new DBSelector();
$q->add_table('ar', 'allowable_relationship');
$q->add_table('e', 'entity');
//$q->add_table( 'site_own_alrel', 'allowable_relationship' );
//$q->add_table( 'r', 'relationship' );
$q->add_field('ar', '*');
$q->add_field('e', 'name', 'entity_name');
if ($dir == 'left') {
$q->add_relation('ar.relationship_a = ' . $this->admin_page->type_id);
$q->add_relation('ar.relationship_b = e.id');
if (!reason_relationship_names_are_unique()) {
$q->add_relation('ar.name != "borrows"');
} else {
$q->add_relation('ar.type != "borrows"');
}
} elseif ($dir == 'right') {
$q->add_relation('ar.relationship_a = e.id');
$q->add_relation('ar.relationship_b = ' . $this->admin_page->type_id);
}
if (!reason_relationship_names_are_unique()) {
$q->add_relation('ar.name != "owns"');
$q->add_relation('ar.name NOT LIKE "%archive%"');
} else {
$q->add_relation('ar.type != "owns"');
$q->add_relation('ar.type != "archive"');
}
// make sure this site has access to the related type
// we don't want to be able to associate with types that a site does not have access to
/*
$q->add_relation( 'site_own_alrel.relationship_a = '.id_of( 'site' ) );
$q->add_relation( 'site_own_alrel.relationship_b = '.id_of( 'type' ) );
$q->add_relation( 'site_own_alrel.name = "site_to_type"' );
$q->add_relation( 'r.entity_a = '.$this->admin_page->site_id );
$q->add_relation( 'r.entity_b = e.id' );
$q->add_relation( 'r.type = site_own_alrel.id' );
*/
$r = db_query($q->get_query(), 'Unable to get allowable relationships for this type.');
$x = array();
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
$x[] = $row;
}
return $x;
}
/**
* Grabs a list of fields associated with the current type in case we need them later.
* Used in add_enum_element( $field ).
* @param Viewer $viewer
*/
function get_db_fields($viewer)
{
$n = count($viewer);
$i = 0;
$in = '( ';
foreach ($viewer as $key => $t) {
$i++;
$in .= '"' . $key . '"';
if ($i != $n) {
$in .= ', ';
}
}
$in .= ')';
$d = new DBSelector();
$d->add_table('entity');
$d->add_table('field');
$d->add_table('r1', 'relationship');
$d->add_table('ar1', 'allowable_relationship');
$d->add_table('r2', 'relationship');
$d->add_table('ar2', 'allowable_relationship');
$d->add_field('field', '*');
$d->add_field('entity', '*');
$d->add_relation('ar1.name = "type_to_table"');
$d->add_relation('ar2.name = "field_to_entity_table"');
$d->add_relation('r1.type = ar1.id');
$d->add_relation('r2.type = ar2.id');
$d->add_relation('entity.id = field.id');
$d->add_relation('r1.entity_a = ' . $this->page->type_id);
$d->add_relation('r1.entity_b = r2.entity_b');
$d->add_relation('r2.entity_a = field.id');
if ($n > 0) {
$d->add_relation('entity.name IN ' . $in);
}
$fields = $d->run();
$this->fields = array();
foreach ($fields as $field) {
$this->fields[$field['name']] = $field;
}
}
function set($value)
{
if (!$this->can_run()) {
return;
}
if (empty($value)) {
$this->value = '<' . '?xml version="1.0" ?' . '><form submit="Submit" reset="Clear" />';
} else {
$this->value = $value;
}
// If there's no xml declaration, assume that value contains the tmp_id
if (strpos($this->value, '<' . '?xml') === false) {
$this->tmp_id = $this->value;
connectDB($this->thor_db_conn_name);
$dbs = new DBSelector();
$dbs->add_table('thor');
$dbs->add_field('thor', 'content');
$dbs->add_relation('thor.id = ' . addslashes($this->tmp_id));
$results = $dbs->run();
if (count($results) > 0) {
$this->value = $results[0]['content'];
} else {
$this->value = '';
}
connectDB($this->original_db_conn_name);
} else {
connectDB($this->thor_db_conn_name);
include_once CARL_UTIL_INC . 'db/sqler.php';
$sqler = new SQLER();
$sqler->insert('thor', array('content' => $this->value));
$this->tmp_id = mysql_insert_id();
connectDB($this->original_db_conn_name);
}
}
function get_required_ar_dbq($id = 0)
{
$id = (int) $id;
if (empty($id)) {
$id = $this->id;
}
if (empty($id)) {
return false;
}
$dbq = new DBSelector();
$dbq->add_table('ar', 'allowable_relationship');
$dbq->add_table('r', 'relationship');
$dbq->add_table('entity');
$dbq->add_field('ar', '*');
$dbq->add_field('r', 'entity_a');
$dbq->add_field('r', 'entity_b');
$dbq->add_field('entity', 'id', 'e_id');
$dbq->add_field('entity', 'name', 'e_name');
$dbq->add_relation('ar.connections = "one_to_many"');
$dbq->add_relation('ar.required = "yes"');
$dbq->add_relation('r.entity_b = ' . $id);
$dbq->add_relation('r.type = ar.id');
$dbq->add_relation('entity.id = r.entity_a');
$dbq->add_relation('entity.state = "Live"');
$dbq->add_relation('r.entity_b != r.entity_a');
return $dbq;
}
/**
* Initialize the _right_relationships and _right_relationships_info arrays
* @access private
* @todo cache db structure info so it doesn't need to rediscover it every time
*/
function _init_right_relationships()
{
//first, get relationship types
$dbq = new DBSelector();
$dbq->add_field('allow', '*');
$dbq->add_table('allow', 'allowable_relationship');
$dbq->add_table('entity', 'entity');
$dbq->add_relation('entity.id = ' . $this->_id);
$dbq->add_relation('entity.type = allow.relationship_b');
// we think this should be commented out. it makes an error not appear. you probably want more than that. i don't have it.
//$dbq->add_relation( 'entity.state = "Live"' );
$r1 = db_query($dbq->get_query(), 'Entity Error: Could not get relationships');
$rel_name = array();
while ($row = mysql_fetch_array($r1, MYSQL_ASSOC)) {
$this->_right_relationships[$row['id']] = array();
$this->_right_relationships[$row['name']] = array();
$this->_right_relationships_info[$row['id']] = array();
$this->_right_relationships_info[$row['name']] = array();
$rel_name[$row['id']] = $row['name'];
}
$dbq = new DBSelector();
$dbq->add_table('r', 'relationship');
$dbq->add_field('r', '*');
$dbq->add_table('entity', 'entity');
$dbq->add_relation('entity.state = "Live"');
$dbq->add_relation('entity.id = r.entity_a');
$dbq->add_relation('r.entity_b = ' . $this->id());
if ($this->_env['restrict_site'] and !empty($this->_env['site'])) {
$dbq->add_relation('(r.site=0 OR r.site=' . $this->_env['site'] . ')');
}
$dbq->set_order('rel_sort_order');
$rels = $dbq->run();
foreach ($rels as $r) {
$e = new entity($r['entity_a']);
$this->_right_relationships[$r['type']][] = $e;
$this->_right_relationships_info[$r['type']][] = $r;
if (!empty($rel_name[$r['type']])) {
$this->_right_relationships[$rel_name[$r['type']]][] = $e;
$this->_right_relationships_info[$rel_name[$r['type']]][] = $r;
}
}
}
function clean_expunged()
{
$es = new entity_selector();
$es->add_type(id_of('minisite_page'));
$es->limit_tables();
$es->limit_fields();
$result = $es->run_one('', 'All');
$page_ids = array_keys($result);
$dbs = new DBSelector();
$dbs->add_table('URL_history', 'URL_history');
$dbs->add_field('URL_history', 'id');
$dbs->add_field('URL_history', 'page_id');
$dbs->add_field('URL_history', 'timestamp');
$dbs->add_field('URL_history', 'url');
$dbs->add_relation('URL_history.page_id NOT IN ("' . implode('","', $page_ids) . '")');
$rows = $dbs->run();
foreach ($rows as $row) {
$e = new entity($row['page_id']);
if (!reason_is_entity($e, 'minisite_page')) {
$needs_deletion[] = $row['id'];
}
}
if (isset($needs_deletion)) {
$deleter_sql = 'DELETE FROM URL_history WHERE id IN ("' . implode('","', $needs_deletion) . '")';
if ($this->mode == 'test') {
echo '<p>Would delete ' . count($needs_deletion) . ' rows that reference expunged entities with this query:</p>';
echo $deleter_sql;
}
if ($this->mode == 'run') {
db_query($deleter_sql, 'Could not delete rows from URL_history');
echo '<p>Deleted ' . count($needs_deletion) . ' rows that reference expunged entities with this query:</p>';
echo $deleter_sql;
}
} else {
echo '<p>There are no rows that reference expunged entities in the URL_history table - you may have already run this script</p>';
return true;
}
}
<?php
/**
* Provides a web service for the Thor WYSIWYG editor to get the current value of the temporary XML file in the DB
* @package thor
*/
include_once 'paths.php';
include_once SETTINGS_INC . 'thor_settings.php';
if (!empty($_REQUEST['tmp_id'])) {
include_once CARL_UTIL_INC . 'db/db.php';
include_once CARL_UTIL_INC . 'db/db_selector.php';
connectDB(THOR_FORM_DB_CONN);
$dbs = new DBSelector();
$dbs->add_table('thor');
$dbs->add_field('thor', 'content');
$dbs->add_relation('thor.id = ' . carl_util_sql_string_escape($_REQUEST['tmp_id']));
$results = $dbs->run();
if (count($results) > 0) {
header('Content-type: text/xml; charset=utf-8');
echo $results[0]['content'];
} else {
$results[0]['content'] = '';
}
// if ( empty($results[0]['content']) )
// die('<' . '?xml version="1.0" ?' . '><form submit="Submit" reset="Clear" />');
} else {
die('Please provide a tmp_id.');
}
?>
<form method="post">
<p>Unique names are human-readable keys for entities in Reason. For a given entity, the unique_name field may be empty, but if there is any content in the unique_name field, it must be:</p>
<ol>
<li>unique, and</li>
<li>contain only low-ascii letters, numbers, and underscores.</li>
</ol>
<p>This script finds any unique names that do not fit those criteria, and reports on them. Fixing bad unique names is currently a manual process.</p>
<input type="submit" name="do_it" value="Run the script" />
</form>
<?php
} else {
reason_include_once('classes/entity_selector.php');
$dbs = new DBSelector();
$dbs->add_table('entity');
$dbs->add_field('entity', 'id', 'id');
$dbs->add_field('entity', 'unique_name', 'unique_name');
$dbs->add_field('entity', 'name', 'name');
$dbs->add_relation('`unique_name` != ""');
$dbs->add_relation('unique_name IS NOT NULL');
$dbs->add_relation('`state` IN ("Live","pending")');
$results = $dbs->run('Error getting unique names');
$uniques = array();
$names = array();
foreach ($results as $result) {
if (empty($uniques[$result['unique_name']])) {
$uniques[$result['unique_name']] = array();
}
$uniques[$result['unique_name']][$result['id']] = $result['name'];
}
foreach ($uniques as $uname => $items) {
/**
* We find all backwards rels in Reason.
*/
protected function find_backwards_rels()
{
$dbs = new DBSelector();
$dbs->add_table('r', 'relationship');
$dbs->add_table('ar', 'allowable_relationship');
$dbs->add_table('e1', 'entity');
$dbs->add_table('e2', 'entity');
$dbs->add_field('r', '*');
$dbs->add_relation('r.type = ar.id');
$dbs->add_relation('e1.id = r.entity_a');
$dbs->add_relation('e2.id = r.entity_b');
$dbs->add_relation('e1.type = ar.relationship_b');
$dbs->add_relation('e2.type = ar.relationship_a');
$dbs->add_relation('e1.type != e2.type');
// we cannot know if type to type rels are backwards!
$result = $dbs->run();
if ($result) {
foreach ($result as $row) {
$backwards_rels[$row['id']] = $row;
}
}
return !empty($backwards_rels) ? $backwards_rels : false;
}