/**
* This function adds a lock definition.
*
* @access public
* @override
* @param string $table the table to be locked
* @param array $hints the hints to be applied
* @return DB_SQL_Lock_Builder a reference to the current instance
*/
public function add($table, array $hints = NULL)
{
$table = $this->precompiler->prepare_identifier($table);
$sql = "SELECT * FROM {$table} WITH (";
$modes = array();
if ($hints !== NULL) {
foreach ($hints as $hint) {
if (preg_match('/^FORCESCAN|HOLDLOCK|NOLOCK|NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|READUNCOMMITTED|REPEATABLEREAD|ROWLOCK|SERIALIZABLE|TABLOCK|TABLOCKX|UPDLOCK|XLOCK$/i', $hint)) {
$modes[] = strtoupper($hint);
} else {
if (preg_match('/^(INDEX|FORCESEEK).+$/i', $hint)) {
$modes[] = DB_SQL::expr($hint);
}
}
}
}
if (empty($modes)) {
$modes[] = 'TABLOCKX';
}
$this->data[$table] = $sql . implode(', ', $modes) . ');';
return $this;
}
/**
* 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 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();
}
/**
* This function will a column to be counted.
*
* @access public
* @param string $column the column to be counted
* @param string $alias the alias to be used for the specified column
* @return DB_SQL_Select_Builder a reference to the current instance
*/
public function count($column = '*', $alias = 'count')
{
$column = (!empty($column) and substr_compare($column, '*', -1, 1) === 0) ? $this->precompiler->prepare_wildcard($column) : $this->precompiler->prepare_identifier($column);
return $this->column(DB_SQL::expr("COUNT({$column})"), $alias);
}
