/**
* This function returns a new token.
*
* @access public
* @return string a new token
*/
public function create_token()
{
do {
$token = sha1(uniqid(Text::random('alnum', 32), TRUE));
} while (DB_SQL::select($this->data_source(DB_DataSource::SLAVE_INSTANCE))->from($this->table())->where('token', DB_SQL_Operator::_EQUAL_TO_, $token)->query()->is_loaded());
return $token;
}
/**
* This function loads the corresponding model.
*
* @access protected
* @override
* @return DB_ORM_Model the corresponding model
*/
protected function load()
{
$parent_key = $this->metadata['parent_key'];
$child_model = $this->metadata['child_model'];
$child_table = $child_model::table();
$child_key = $this->metadata['child_key'];
$child_source = $child_model::data_source(DB_DataSource::SLAVE_INSTANCE);
$builder = DB_SQL::select($child_source)->all("{$child_table}.*")->from($child_table);
$field_count = count($child_key);
for ($i = 0; $i < $field_count; $i++) {
$builder->where("{$child_table}.{$child_key[$i]}", DB_SQL_Operator::_EQUAL_TO_, $this->model->{$parent_key[$i]});
}
$result = $builder->limit(1)->query($child_model);
if ($result->is_loaded()) {
return $result->fetch(0);
}
$record = new $child_model();
for ($i = 0; $i < $field_count; $i++) {
$record->{$child_key[$i]} = $this->model->{$parent_key[$i]};
}
return $record;
}
/**
* This function returns a result set of database views.
*
* +---------------+---------------+------------------------------------------------------------+
* | field | data type | description |
* +---------------+---------------+------------------------------------------------------------+
* | schema | string | The name of the schema that contains the table. |
* | table | string | The name of the table. |
* | type | string | The type of table. |
* +---------------+---------------+------------------------------------------------------------+
*
* @access public
* @override
* @param string $like a like constraint on the query
* @return DB_ResultSet a result set of database views
*
* @see http://www.devx.com/dbzone/Article/29585/0/page/4
* @see http://lpetr.org/blog/archives/find-a-list-of-views-marked-inoperative
* @see http://www.ibm.com/developerworks/data/library/techarticle/dm-0411melnyk/
*/
public function views($like = '')
{
$builder = DB_SQL::select($this->data_source)->column('VIEWSCHEMA', 'schema')->column('VIEWNAME', 'table')->column(DB_SQL::expr("'VIEW'"), 'type')->from('SYSCAT.VIEWS')->where('VIEWSCHEMA', DB_SQL_Operator::_NOT_LIKE_, 'SYS%')->where('VALID', DB_SQL_Operator::_NOT_EQUIVALENT_, 'Y')->order_by(DB_SQL::expr('UPPER("VIEWSCHEMA")'))->order_by(DB_SQL::expr('UPPER("VIEWNAME")'));
if (!empty($like)) {
$builder->where('VIEWNAME', DB_SQL_Operator::_LIKE_, $like);
}
return $builder->query();
}
/**
* This function returns a result set of database views.
*
* +---------------+---------------+------------------------------------------------------------+
* | field | data type | description |
* +---------------+---------------+------------------------------------------------------------+
* | schema | string | The name of the schema that contains the table. |
* | table | string | The name of the table. |
* | type | string | The type of table. |
* +---------------+---------------+------------------------------------------------------------+
*
* @access public
* @override
* @param string $like a like constraint on the query
* @return DB_ResultSet a result set of database views
*
* @see http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html
* @see http://www.razorsql.com/articles/oracle_system_queries.html
*/
public function views($like = '')
{
$builder = DB_SQL::select($this->data_source)->column('OWNER', 'schema')->column('VIEW_NAME', 'table')->column(DB_SQL::expr("'VIEW'"), 'type')->from('SYS.ALL_VIEWS')->order_by(DB_SQL::expr('UPPER("OWNER")'))->order_by(DB_SQL::expr('UPPER("VIEW_NAME")'));
if (!empty($like)) {
$builder->where('VIEW_NAME', DB_SQL_Operator::_LIKE_, $like);
}
return $builder->query();
}
/**
* This function returns a result set of database views.
*
* +---------------+---------------+------------------------------------------------------------+
* | field | data type | description |
* +---------------+---------------+------------------------------------------------------------+
* | schema | string | The name of the schema that contains the table. |
* | table | string | The name of the table. |
* | type | string | The type of table. |
* +---------------+---------------+------------------------------------------------------------+
*
* @access public
* @override
* @param string $like a like constraint on the query
* @return DB_ResultSet a result set of database views
*
* @see http://www.alberton.info/sql_server_meta_info.html
*/
public function views($like = '')
{
$builder = DB_SQL::select($this->data_source)->column('[TABLE_SCHEMA]', 'schema')->column('[TABLE_NAME]', 'table')->column(DB_SQL::expr("'VIEW'"), 'type')->from('[INFORMATION_SCHEMA].[TABLES]')->where('[TABLE_TYPE]', DB_SQL_Operator::_EQUAL_TO_, 'VIEW')->where(DB_SQL::expr("OBJECTPROPERTY(OBJECT_ID([TABLE_NAME]), 'IsMsShipped')"), DB_SQL_Operator::_EQUAL_TO_, 0)->order_by(DB_SQL::expr('UPPER([TABLE_SCHEMA])'))->order_by(DB_SQL::expr('UPPER([TABLE_NAME])'));
if (!empty($like)) {
$builder->where('[TABLE_NAME]', DB_SQL_Operator::_LIKE_, $like);
}
return $builder->query();
}
/**
* This function loads the corresponding model(s).
*
* @access protected
* @override
* @return DB_ResultSet the corresponding model(s)
*/
protected function load()
{
$parent_key = $this->metadata['parent_key'];
$child_model = $this->metadata['child_model'];
$child_table = $child_model::table();
$child_key = $this->metadata['child_key'];
$child_source = $child_model::data_source(DB_DataSource::SLAVE_INSTANCE);
if (isset($this->metadata['through_model']) and isset($this->metadata['through_keys'])) {
$through_model = $this->metadata['through_model'];
$through_table = $through_model::table();
$through_keys = $this->metadata['through_keys'];
$through_source = $through_model::data_source(DB_DataSource::SLAVE_INSTANCE);
if ($through_source != $child_source) {
$builder = DB_SQL::select($through_source)->from($through_table);
$field_count = count($through_keys[1]);
for ($i = 0; $i < $field_count; $i++) {
$builder->column("{$through_table}.{$through_keys[1][$i]}");
}
$field_count = count($through_keys[0]);
for ($i = 0; $i < $field_count; $i++) {
$builder->where("{$through_table}.{$through_keys[0][$i]}", DB_SQL_Operator::_EQUAL_TO_, $this->model->{$parent_key[$i]});
}
$records = $builder->query('array');
$builder = DB_SQL::select($child_source)->all("{$child_table}.*")->from($child_table);
$field_count = count($child_key);
foreach ($records as $record) {
$builder->where_block(DB_SQL_Builder::_OPENING_PARENTHESIS_, DB_SQL_Connector::_OR_);
for ($i = 0; $i < $field_count; $i++) {
$builder->where("{$child_table}.{$child_key[$i]}", DB_SQL_Operator::_EQUAL_TO_, $this->model->{$record[$through_keys[1][$i]]});
}
$builder->where_block(DB_SQL_Builder::_CLOSING_PARENTHESIS_);
}
foreach ($this->metadata['options'] as $option) {
call_user_func_array(array($builder, $option[0]), $option[1]);
}
$result = $builder->query($child_model);
} else {
$builder = DB_SQL::select($child_source)->all("{$child_table}.*")->from($through_table)->join(DB_SQL_JoinType::_INNER_, $child_table);
$field_count = count($child_key);
for ($i = 0; $i < $field_count; $i++) {
$builder->on("{$child_table}.{$child_key[$i]}", DB_SQL_Operator::_EQUAL_TO_, "{$through_table}.{$through_keys[1][$i]}");
}
$field_count = count($through_keys[0]);
for ($i = 0; $i < $field_count; $i++) {
$builder->where("{$through_table}.{$through_keys[0][$i]}", DB_SQL_Operator::_EQUAL_TO_, $this->model->{$parent_key[$i]});
}
foreach ($this->metadata['options'] as $option) {
call_user_func_array(array($builder, $option[0]), $option[1]);
}
$result = $builder->query($child_model);
}
} else {
$builder = DB_SQL::select($child_source)->all("{$child_table}.*")->from($child_table);
$field_count = count($child_key);
for ($i = 0; $i < $field_count; $i++) {
$builder->where("{$child_table}.{$child_key[$i]}", DB_SQL_Operator::_EQUAL_TO_, $this->model->{$parent_key[$i]});
}
foreach ($this->metadata['options'] as $option) {
call_user_func_array(array($builder, $option[0]), $option[1]);
}
$result = $builder->query($child_model);
}
return $result;
}
/**
* This function returns a result set of database views.
*
* +---------------+---------------+------------------------------------------------------------+
* | field | data type | description |
* +---------------+---------------+------------------------------------------------------------+
* | schema | string | The name of the schema that contains the table. |
* | table | string | The name of the table. |
* | type | string | The type of table. |
* +---------------+---------------+------------------------------------------------------------+
*
* @access public
* @override
* @param string $like a like constraint on the query
* @return DB_ResultSet a result set of database views
*
* @see http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/mysql_query_to_find_all_views_in_a_database.php
*/
public function views($like = '')
{
$builder = DB_SQL::select($this->data_source)->column('TABLE_SCHEMA', 'schema')->column('TABLE_NAME', 'table')->column(DB_SQL::expr("'VIEW'"), 'type')->from('INFORMATION_SCHEMA.TABLES')->where(DB_SQL::expr('UPPER(`TABLE_TYPE`)'), DB_SQL_Operator::_EQUAL_TO_, 'VIEW')->order_by(DB_SQL::expr('UPPER(`TABLE_SCHEMA`)'))->order_by(DB_SQL::expr('UPPER(`TABLE_NAME`)'));
if (!empty($like)) {
$builder->where('TABLE_NAME', DB_SQL_Operator::_LIKE_, $like);
}
return $builder->query();
}
/**
* This function returns the level (i.e. depth) at which the current node resides.
*
* @access public
* @return integer the level at which the current
* node resides
*
* @see http://stackoverflow.com/questions/7661913/modified-preorder-tree-traversal-selecting-nodes-1-level-deep
*/
public function level()
{
$record = DB_SQL::select(static::data_source(DB_DataSource::SLAVE_INSTANCE))->column(DB_SQL::expr('COUNT(parent_id) - 1'), 'level')->from(static::table())->where('scope', DB_SQL_Operator::_EQUAL_TO_, $this->fields['scope']->value)->where('parent_id', DB_SQL_Operator::_EQUAL_TO_, $this->fields['parent_id']->value)->group_by('parent_id')->query()->fetch();
return $record ? $record['level'] : 0;
}
/**
* This function saves the record matching using the primary key.
*
* @access public
* @param boolean $reload whether the model should be reloaded
* after the save is done
* @param boolean $mode TRUE=save, FALSE=update, NULL=automatic
* @throws Throwable_Marshalling_Exception indicates that model could not be saved
*/
public function save($reload = FALSE, $mode = NULL)
{
if (!static::is_savable()) {
throw new Throwable_Marshalling_Exception('Message: Failed to save record to database. Reason: Model is not savable.', array(':class' => get_called_class()));
}
$primary_key = static::primary_key();
if (empty($primary_key) or !is_array($primary_key)) {
throw new Throwable_Marshalling_Exception('Message: Failed to save record to database. Reason: No primary key has been declared.');
}
$data_source = static::data_source(DB_DataSource::MASTER_INSTANCE);
$table = static::table();
$columns = array_keys($this->fields);
$hash_code = $this->hash_code();
// Set saving mode
$do_insert = $mode === NULL ? $hash_code === NULL : (bool) $mode;
if (!$do_insert) {
// Check if we have to detect saving mode automatically
if ($mode === NULL) {
// Check if the model has been already saved
$do_insert = ($this->metadata['saved'] === NULL or $hash_code != $this->metadata['saved']);
// Check if the record exists in database
if ($do_insert) {
$builder = DB_SQL::select($data_source)->column(DB_SQL::expr(1), 'IsFound')->from($table);
foreach ($primary_key as $column) {
$builder->where($column, DB_SQL_Operator::_EQUAL_TO_, $this->fields[$column]->value);
}
$do_insert = !$builder->limit(1)->query()->is_loaded();
}
}
if (!$do_insert) {
if (!empty($columns)) {
$builder = DB_SQL::update($data_source)->table($table);
// Is there any data to save and it's worth to execute the query?
$is_worth = FALSE;
foreach ($columns as $column) {
if ($this->fields[$column]->savable and $this->fields[$column]->modified) {
// Add column values to the query builder
$builder->set($column, $this->fields[$column]->value);
if (in_array($column, $primary_key) or $this->fields[$column]->value instanceof DB_SQL_Expression) {
// Reloading required because primary key has been changed or an SQL expression has been used
$reload = TRUE;
}
// It's worth do execute the query.
$is_worth = TRUE;
}
// Mark field as not modified
$this->fields[$column]->modified = FALSE;
}
// Execute the query only if there is data to save
if ($is_worth) {
foreach ($primary_key as $column) {
$builder->where($column, DB_SQL_Operator::_EQUAL_TO_, $this->fields[$column]->value);
}
$builder->execute();
}
$this->metadata['saved'] = $hash_code;
}
}
}
if ($do_insert) {
if (!empty($columns)) {
$builder = DB_SQL::insert($data_source)->into($table);
// Is any data to save and it's worth to execute the query?
$is_worth = FALSE;
foreach ($columns as $column) {
if ($this->fields[$column]->savable and $this->fields[$column]->modified) {
// It's worth executing the query.
$is_worth = TRUE;
// Add column values to the query builder
$builder->column($column, $this->fields[$column]->value);
if ($this->fields[$column]->value instanceof DB_SQL_Expression) {
// Reloading required, if using SQL expressions
$reload = TRUE;
}
}
// Mark field as not modified
$this->fields[$column]->modified = FALSE;
}
// Execute the query only if there is data to save
if ($is_worth) {
if (static::is_auto_incremented() and $hash_code === NULL) {
// Execute the query and assign the result to the primary key field
$this->fields[$primary_key[0]]->value = $builder->execute(TRUE);
// Mark the primary key field as not modified
$this->fields[$primary_key[0]]->modified = FALSE;
} else {
$builder->execute();
}
}
$this->metadata['saved'] = $this->hash_code();
}
}
if ($reload) {
$primary_key = static::primary_key();
//set the primary keys in a temp variable
$temp = new stdClass();
foreach ($primary_key as $column) {
$temp->{$column} = $this->{$column};
}
//Force reset and then you can reload the model with relations
$this->reset();
foreach ($primary_key as $column) {
$this->{$column} = $temp->{$column};
}
// Reload the record, if it's required
$this->load();
}
}
/**
* This function returns a result set of database views.
*
* +---------------+---------------+------------------------------------------------------------+
* | field | data type | description |
* +---------------+---------------+------------------------------------------------------------+
* | schema | string | The name of the schema that contains the table. |
* | table | string | The name of the table. |
* | type | string | The type of table. |
* +---------------+---------------+------------------------------------------------------------+
*
* @access public
* @override
* @param string $like a like constraint on the query
* @return DB_ResultSet a result set of database views
*
* @see http://www.firebirdfaq.org/faq174/
* @see http://www.alberton.info/firebird_sql_meta_info.html
*/
public function views($like = '')
{
$path_info = pathinfo($this->data_source->database);
$schema = $path_info['filename'];
$builder = DB_SQL::select($this->data_source)->column(DB_SQL::expr("'{$schema}'"), 'schema')->column(DB_SQL::expr('TRIM("RDB$RELATION_NAME")'), 'table')->column(DB_SQL::expr("'VIEW'"), 'type')->from('RDB$RELATIONS')->where(DB_SQL::expr('COALESCE("RDB$SYSTEM_FLAG", 0)'), DB_SQL_Operator::_EQUAL_TO_, 0)->where('RDB$VIEW_BLR', DB_SQL_Operator::_IS_NOT_, NULL)->order_by(DB_SQL::expr('UPPER("RDB$RELATION_NAME")'));
if (!empty($like)) {
$builder->where(DB_SQL::expr('TRIM("RDB$RELATION_NAME")'), DB_SQL_Operator::_LIKE_, $like);
}
return $builder->query();
}
