Database

Database access

Database is accessible through KDB registry and KDB_Driver objects.

Every database is configured in KWebSetting as KWebSetting::$inst->db_NAME, where NAME is database's instance name. Default instance name is 'kweb'.

To access (and instantiate for the first time) an instance simply call:

 $db = KDB::instance('NAME');
 // or the main instance
 $db = KDB::instance();

For the list of all possible functions see KDB_Driver and KDB_PDO

The most versatile function is queryRows(). It allows to automatically cache all results

 $db = KDB::instance();
 $rows = $db->queryRows('SELECT 1', array(), array(
  KDB::OPT_CACHE => 'some id', // unique id of this select
  KDB::OPT_CACHE_ITEMS => SPECIALID_CONTENT, // will invalidate this cache upon any change in the CONTENT tree
  ));

The main KDB driver right now is PDO. Every call which returns a result object, will really return PDO statement objects. KDB also stores the last returned object, so you don't have to pass them to KDB_Driver methods, unless you make another nested database call.

 // simple example
 $db = KDB::instance();
 $r = $db->query('SELECT 1');
 $db->query('SELECT 2');
 $db->getError(); // will return an error for last select 2.
 $db->getError($r); // will return an error for select 1.

If you really need to access PDO directly use

 KDB::instance()->pdo

Database reading

There are several ways to read data and objects from the database, apart from direct access.

SqlQueryBuilder

There is SqlQueryBuilder (modified version of Zend's SqlQueryBuilder). It supports SELECT, INSERT, UPDATE and DELETE statements. It also allows to automatically collect all binding values.

See also:

SqlQueryBuilder

Reading:

 // returns item_id for all rows with specified item_status, main_item_id, and language.
   $query = new SqlQueryBuilder();
   $query->from(TABLE_ITEM, 'item_id')
     ->whereEquals('item_status', array(ITEMS_DRAFT,  ITEMS_PENDING,  ITEMS_REMOVER))
     ->whereEquals('main_item_id', $this->getObjectId())
     ->where('main_item_lang = ?', null, null, 'language')
     ->order(array(new SqlQueryExpr('user_id = ' . $db->quote($user) . ' DESC'), 'item_status DESC'))
     ;
   $r = $db->query($query, array('language' => 'pl'));

Insertion:

  $query = new SqlQueryBuilder(null, SqlQueryBuilder::QUERY_INSERT);
  $query->table('dummy')
   ->value('a', 'A')
   ->value('b', 'B', 'something') // B will be automatically bounded as :something
   ->value('time', new SqlQueryExpr('NOW()'))
   ->nextValues() // start next set of values
   ->value('a', 'aa')
   ->value('b', 'dd')
   // add next set of values. Notice, that we add a new column 'c'. All previous sets will now have column c set to NULL
   ->values(array('a' => 'aaa', 'b' => 'eee', 'c' => 'ccc'))
  ;
  // this will execute this query with 'something' set to 'B'
  $db->execute($query); 
  // but we can override it with bindKnown, which will not allow you to bind something, that is not in a statement
  $query->bindKnown(array('something' => 'BB', 'unknown' => 1));
  $db->execute($query);

Collection

In many places, objects are returned in Collection (KWebCollection_Objects specifically). Collections implement Iterator, so they can be used in foreach(), and ArrayAccess so they will work in for() statements.

Additionaly, Collections allow to access fields of their data directly, through fetchField() and fetchFieldRange().

For example, to get a list of all titles from collection of objects you can use

 $title = $collection->fetchFieldRange('item_title')

Or if the collection of objects was a result of a query like "SELECT item_id, count(*) as amount ..." you can access both the KWebObject, and the query result underneath it:

 $result = new Collection_Query($db, null, $query);
 $result = new KWebCollection_Objects($result, GENRE_ITEM, 'item_id');
 foreach($result as $object) {
  $object->printMe('view/list', array('amount' => $result->fetchField('amount', false)));
 }

Another important feature is prefetch. When you have a collection of many objects, it's better to read them in a single batch, instead of one by one. But as you do not know how big is the collection, you can prefetch just some of them at a time

 $collection->prefetch(50); // this will read first 50 in a batch. If there are more items, the rest will be fetched one-by-one
 $i = 0;
 foreach($collection as $object) {
 }

You can also use automatic prefetch and release, ideal if you have to browse through a large amount of items.

 $collection->getCollection('KWebCollection_Objects') // many functions may wrap Objects collection in another collection (like Filtered)
  ->setOption(Collection::OPT_PREFETCH_AUTO, 50) // prefetch 50 objects
  ->setOption(Collection::OPT_RELEASE_AUTO, 100) // release after every 100th object
  ;
 foreach($collection as $object) {
 }

There is a very powerfull Collection_Filtered which allows to filter the inner collection with a set of filters.

 $items = $item->getPublishedChildren(ATTR_NONE, false);
    
    $collection = new Collection_Filtered( $items, array( 
        KWebFilter_Object::create()->with('canRSS') 
    ) );
 
 foreach($collection as $object) {
  // loop through items with ($object->canRSS() == true)
 }

KWebAdapter

KWebAdapter allows to fetch objects from the data source, using a set of rules, instead of building queries.

Different adapters work with different rules. Some adapters may call other adapters for specific rules. Check adapter's applyQueryOptions() function for details...

Almost every option can be passed as either a scalar (only matching one value), or array (matching any value)

 $adapter = KWebAdapter_Item::itemFactory(array(
  KWebAdapter::WITH_PARENTS => SPECIALID_CONTENT, // will include items with SPECIALID_CONTENT in the parents list (deep search)
  //KWebAdapter::WITH_PARENT => SPECIALID_CONTENT, // will include items directly below SPECIALID_CONTENT
  KWebAdapter::WITH_KEYWORD => array('test'), // will include items with a keyword 'test'
  KWebAdapter::IS_PUBLISHED => true, // only published
  KWebAdapter::WITH_TYPE => ITEMT_ART, // of type ART
  KWebAdapter::WITH_SECTION => 'main', // in the section 'main'
 ));
    $collection = $adapter->readObjects();
 foreach($collection as $object) {
  $object->printMe();
 }

KWebAdapter and SqlQueryBuilder

KWebAdapters also have methods which control SqlQueryBuilder objects. This allows to use SqlQueryBuilder without knowing the database structure

This will return a list of user groups, with additional is_user column

 $qb = new KWebQueryBuilder();
 $adapter = KWebAdapter_Item::inst()
  ->queryFromItem($qb, 'item_id') // will automatically join TABLE_ITEM and select item_id
  ->queryFromItemLanguage($qb, 'item_title')  // will automatically join TABLE_ITEM_LANG and select item_title
  ->queryWhereIsType($qb, ITEMT_GROUP) 
 ;
 $qb->columns(new SqlQueryExpr('0 as is_user'));
 $qb->order('item_title');
 $userList = array_merge( $userList, $db->queryRows($qb) );

Or you can use an adapter, to create a query, and then modify it as you please

  $adapter = KWebAdapter_Item::itemFactory(array(
   KWebAdapter::WITH_PARENTS => SPECIALID_GROUPS,
   KWebAdapter::WITH_PARENTS_LINKTYPE => LINK_STANDARD,
   KWebAdapter::WITH_TYPE => ITEMT_GROUP,
   KWebAdapter::IS_PUBLISHED => true,
   KWebAdapter::ORDER => IORDER_TITLE,
  ));
  $query = $adapter->buildReadQuery();
  $query->columns('item_title', $query->findTable(TABLE_ITEM_LANG));
  $groups = KDB::instance()->queryKeyValue($query);

Or you can use the adapter, to get the proper collection of objects

      // get the adapter
  $adapter = KWebAdapter_Item::itemFactory(array(
   KWebAdapter::WITH_KEYWORD => array('test'),
   KWebAdapter::IS_PUBLISHED => true,
  ));
        // build the query
  $query = $adapter->buildReadQuery();
      // add another constraint
  $query->whereEquals('l.item_name', 'hello');
      // use adapter to fetch the objects
  $collection = $adapter->readObjectsFromQuery($query);