/** * 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); }