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