Overview

In previous versions of ccHost querying the database tables was done in an object oriented way, building up a SELECT statement through function calls. That code still exists in the codebase and is occasionally used in older (pre-v5) code, however it was deemed too heavy on performance and a relatively needless abstraction (on top of PHP and SQL which are already high-level abstractions on top of the raw data).

The preferred way to do querying now is either a simple straight SELECT statement (see raw query) for one time, simple queries or dataview for complex queries that are used in multiple places in the code.

Updating and inserting new records is still done through the object oriented interface.

Raw Queries

Doing a simply query relies on the CCDatabase object's static methods.

For returning a PHP mySQL resource object:

 $qr = CCDatabase::Query('SELECT upload_name, upload_id FROM cc_tbl_uploads LIMIT 10');
 while( $row = mysql_fetch_array($qr) )
 {
    ....
 }

For returning multiple rows in an array:

 $rows = CCDatabase::QueryRows('SELECT upload_name, upload_id FROM cc_tbl_uploads LIMIT 10');
 foreach( $rows as $row )
 {
 }

For returning a single row:

 $user_row = CCDatabase::QueryRow('SELECT * FROM cc_tbl_user WHERE user_name = ' . $user_name );

For returning a single item:

 $user_real_name = CCDatabase::QueryItem('SELECT user_real_name FROM cc_tbl_user WHERE user_name = ' . $user_name );

The single item version is ideal for 'COUNT(*)' statements.

To return an array of a single column:

 $upload_ids = CCDatabase::QueryItems('SELECT upload_id FROM cc_tbl_user WHERE user_name = '. $user_name );

The call above will return an array of upload_id values for $user_name;

Dataviews

A 'dataview' is an encapsulation of a SQL query. It specifies what columns from what tables are to be queried. When you use a dataview you have to supply with query arguments that limit the number of rows. The relationship between Query API, dataviews and templates are shown in the Ultimate Template example.

Using Dataviews in Code

The simplest way to use dataviews are to use existing one in the ccdataviews directory. For exmaple, lets you want the expanded file list for a given upload:

   require_once('cchost_lib/cc-dataview.php');
   $args['where'] = 'upload_id = ' . $upload_id;
   $dv = new CCDataView();
   $ret = $dv->PerformFile('files',$args,CCDV_RET_RECORD);

Besides where, the $args elements will accept:

 'order'
 'limit'
 'group_by'
 'columns' 
 'joins'

The third argument tells the dataview what type is expected on return:

 CCDV_RET_RECORDS  - returns an array of records (rows)
 CCDV_RET_ITEMS    - returns an array of items
 CCDV_RET_RESOURCE - returns the PHP resource for the mySQL query
 CCDV_RET_ITEM     - returns a single item
 CCDV_RET_RECORD   - returns a single record (row)

Since we only care about one result we use the RECORD version.

Using Dataviews with the Query API

Every Query API request requires a dataview. If you don't specify one then ccdataviews/default.php is used.

This query will generate a comma separated list of the latest 10 uploads using the colums from the links dataview:

 api/query?dataview=links&f=csv&limit=10

Using Dataviews in Templates

All templates that are used with the Query API specify a dataview directly in the template file using the [meta] section:

 %%
   [meta]
      dataview = upload_list_wide
      ...
   [/meta]
 %%

('%%' is the comment block figure for TPL files. A PHP style /* */ would work the same.)

When you specify the template, the Query API will look at this meta section for the dataview.

If there is no meta section, then the Query API looks back at the request for a dataview parameter:

 api/query?dataview=my_dataview&t=some_template

If there is no dataview parameter in the request and no meta section in the template, then the Query API uses ccdataview/default.php.

If the template [i]does[/i] specify a dataview [i]and[/i] there is different one in the Query API request, the meta section must allow overrides using the dataview_param property with a value of 'ok':

 %%
   [meta]
      dataview       = upload_list_wide
      dataview_param = ok
      ...
   [/meta]
 %%

The above property list tells the Query API to use upload_list_wide as the dataview unless there is another dataview passed to the Query API. If the dataview_param was not there or didn't say 'ok' then the Query API would only use upload_list_wide.

The default lookup for a dataview name in a property is done via the admin control paths (admin/paths). However a template can use the embedded property to signal that the dataview is actually embedded directly into the template:

 %%
   [meta]
       dataview = my_foo
       embedded = 1
       ...
   [/meta]
   [dataview]
   function my_foo_dataview()
   {
         ....
   }
   [/dataview]

The relationship between Query API, dataviews and templates are shown in the Ultimate Template example.

Custom Dataviews

Anatomy of a Dataview

The implementation of a dataview is a function with three elements: a parametrized SQL statement, a SQL count statement and array of filter events.

The SQL Statement

The SQL statement is seen as responsible for the columns and tables. It has placeholders for the query parameters laced throughout it. The placeholders are keywords surrounded by the percent '%' sign:

 SELECT upload_name, upload_id
        %columns%
        FROM cc_tbl_uploads
        %joins%
        %where%
        %order%
        %limit%

SQL Count

When you want to do paging ('previous', 'next', etc.) it helps to know when you've reached the end. We need a SQL way to count the total number of records that meet the criteria of the query. In this statement we do not want to include the %limit% keyword because that would defeat the purpose of getting a total count. We also do not want to include the %order% keyword or any of the columns because that would be wasted calculation. We just want the total and it's the %where% that decides that.

 SELECT COUNT(*)
        FROM cc_tbl_uploads
        %joins%
        %where%

Dataview Filtering

A 'filter' is a piece of code that is called with the result of the SQL query. What happens to the data in a filter is up to the filter. In technical terms a filter is a record munger.

A 'filter event' is an event that is triggered when a specific kind of munging is being requesting.

Here's a quick psuedo walk through of what happens:

1. The dataview file defines a parametrized SQL query and some filter events.
2. Some calls the dataview engine with a pointer to the dataview file and parameters
3. The dataview engine merges the paramters with the SQL statement and performs it.
4. The dataview engine calls all the filter events passing around the result set from the SQL query.

The dataview file specifies which events it wants triggered in the 'e' element of the return array. All filter events have defines associated with them that begin with CC_EVENT_FILTER_ and most are defined in cchost_lib/cc-defines-filters.php.

Filters are not so agnostic that any filter will 'just work' on any data. For example the CC_EVENT_FILTER_EXTRA assumes that the cc_tbl_uploads.upload_extra column is in the result set. There are several cases where a filter will depend on another filter in an order dependent way. (examples to follow, sorry don't have those ready...)

There are many (!) filters in the system but here are several of the more useful one when customizing the site:

  CC_EVENT_FILTER_DOWNLOAD_URL   - populates the 'download_url' field 
  CC_EVENT_FILTER_EXTRA          - unserializes 'upload_extra' field
  CC_EVENT_FILTER_FILES          - populates a 'files' array under 
                                   every upload record
  CC_EVENT_FILTER_FORMAT         - translates topic codes (e.g. [url]) 
                                   into plain text or HTML
  CC_EVENT_FILTER_MACROS         - populates the 'files_macros' array 
                                   with template names to execute for 
                                   this record
  CC_EVENT_FILTER_NUM_FILES      - populates a 'num_files' field with 
                                   the number files for this upload record
  CC_EVENT_FILTER_PLAY_URL       - populates a 'fplay_url' field with a 
                                   playable (streamable) URL
  CC_EVENT_FILTER_RATINGS_STARS  - populates the 'ratings' and 
                                   'ratings_score' fields
  CC_EVENT_FILTER_REMIXES_FULL   - populates 'remix_parents' and 
                                   'remix_sources' with all relevant links
  CC_EVENT_FILTER_REMIXES_SHORT  - populates 'remix_parents' and 
                                   'remix_sources' with a teaser list (used 
                                   with 'more...')
  CC_EVENT_FILTER_SEARCH_RESULTS - populates 'qsearch' field with 
                                   highlighted search results
  CC_EVENT_FILTER_UPLOAD_MENU    - populates the 'local_menu' field with 
                                   links to commands relevant to this
                                   upload
  CC_EVENT_FILTER_UPLOAD_PAGE    - triggered before an upload page
                                   currently only used for 'flag_url'
  CC_EVENT_FILTER_UPLOAD_TAGS    - populates 'upload_taglinks'
  CC_EVENT_FILTER_UPLOAD_USER_TAGS populates 'usertag_links'
  CC_EVENT_FILTER_USER_PROFILE     populates 'user_fields'

As you can see almost all of the filters will add columns to the resulting set.

Putting it all together

There are two requirements for a dataview function:

• The name of the function must be a concatenation of the dataview name and _dataview
• It must return an array with the following elements:
  • 'sql' - The SQL statement to execute embedded with keywords for the query parameters
  • 'sql_count' - A SQL statement that represents the count of all records that meet the query without limits
  • 'e' - A list of filter events to execute after the query has been performed

Technically the 'sql_count' is only required by Query API's page format but it doesn't hurt to be there.

If a dataview is in a stand-alone file (not embedded in a template) it must have a meta header identifying it as a dataview. Here's what a typical dataview looks like:

   1.  <? /*
   2.   [meta]
   3.     type     = dataview
   4.     name     = my_whiz_bang
   6.   [/meta]      
   7.    */
   8.     function my_whiz_bang_dataview()
   9.     {
  10.        $sql =<<<EOF
  11.           SELECT upload_name, user_name, upload_id, upload_contest
  12.              %columns% 
  13.           FROM cc_tbl_uploads
  14.           JOIN cc_tbl_user ON upload_user=user_id
  15.           %joins%
  16.           %where%
  17.           %order%
  18.          %limit%
  19.  EOF;
  20.
  21.        $sql_count =<<<EOF
  22.           SELECT COUNT(*) 
  23.           FROM cc_tbl_uploads
  24.           JOIN cc_tbl_user ON upload_user = user_id
  25.           %joins%
  26.           %where%
  27.  EOF;
  28.
  29.           return array( 'sql'       => $sql,
  30.                         'sql_count' => $sql_count,
  31.                         'e'         => array( CC_EVENT_FILTER_DOWNLOAD_URL )
  32.             );
  33.       }
  34. 
  35.  ?>

Now we will go through this new template line by line:

2. This opens the meta section of the file. The dataview engine will read these properties.

3. The type property is must be dataview

8. A dataview is basically a PHP function with the name of the dataview prepended to _dataview. The Query engine will execute this function and then perform the SQL query.

11. This is the SQL query that represents the dataview query. We only want two columns but the filter we use (DOWNLOAD_URL) requires two more fields so we place them here.

12. The Query engine will search and replace certain keywords in the SQL statement. The %columns% keyword will expand to any extra columns required by parameters passed to the query engine.

13-14. These are standard SQL that specifies the tables to be used.

15-17. Like %columns%, these keywords will be replaced by the query engine based on the query parameters.

21. When you want to do paging ('previous', 'next', etc.) it helps to know when you've reached the end. We need a SQL way to count the total number of records that meet the criteria of the query.

22. Our queries will be pretty generic so 'COUNT(*)' is fine.

23-24. We want to make sure we have the same table structure as the normal query.

25-26. We do not want to include the %limit% keyword because that would defeat the purpose of getting a total count. We do not want to include the %order% keyword because that would be wasted calculation. We just want the total and it's the %where% that decides that.

29-31. Here we return the SQL statements in an array.

If you put this in <local_files>/dataviews/my_whiz_bang.php then you should be able to peek into it:

 api/query?dataview=my_whiz_bang&f=xml&limit=10

Alternate tables: datasource

Every query through the Query API requires a dataview and every dataview requires a datasource.

In versions previous to ccHost 5 the Query API only operated on cc_tbl_uploads. However in version 5 the Query API was extended to be used with (almost) any other table in the system. The dataview parameter became necessary in ccHost 5 in order help map query parameters like date and sort to the proper columns in the SQL statement. (Yes, this is a somewhat arcane way to do things.)

If you never specify a dataview then uploads is used. However, if your dataview is operating on a table other than uploads you need to specify that in your meta section so that query parameters can be properly mapped.

 /*
 [meta]
   desc = _('Links to page topics')
   datasource = topics
 [/meta]
 */

Other than uploads the other known datasources are:

• user
• topics
• pool
• pool_items
• collabs
• collab_users
• cart
• ratings

Note that the same parameters in one query will have different meanings in another if you change the datasource. For example if you have a query that includes the parameter:

 user=teru

and your datasource is uploads then you will see a list of upload records by a user named 'teru'.

However if the datasource is user then you will see one user record (teru's) and no uploads.

Another example:

  sort=date

for uploads means sort by the date the content was uploaded. For user it means the date the user registered with the site.

Editing Tables

The CCTable Object

Use the CCTable object (or one its derivatives) to update a row or insert a new one.

You can create a new table object:

 $table = new CCTable('cc_tbl_uploads','upload_id');

Or you can get a specific type by getting a reference through the GetTable method:

 $uploads =& CCUploads::GetTable();

The latter syntax saves you from having to know what the index key is for the table and may have some magic embedded in it or more likely, specialized methods. (Both are relatively equal as far as performance goes.)

For example, never write or read from cc_tbl_config using any other method than the encapsulated object:

 $configs =& CCConfigs::GetTable(); // this is the right way to do it

Inserting New Rows

If you need to know the ID of the record you are inserting then use the NextID method, otherwise just let it fly:

               $reviews =& CCReviews::GetTable();
               $values['topic_id'] = $reviews->NextID();
               $values['topic_upload'] = $upload_id;
               $values['topic_date'] = date('Y-m-d H:i:s',time());
               $values['topic_user'] = CCUser::CurrentUser();
               $values['topic_type'] = 'review';
               $values['topic_name'] = sprintf(_("Review of '%s'), upload_name);
               $reviews->Insert($values);

If you have a lot of rows you use InsertBatch to specify the column names in the first parameter and the data in the second:

       $tag_table =& CCTags::GetTable();
       $values = array();
       foreach( $new_tags as $tag )
       {
           $values[] = array( strtolower($tag), 1,  CCTT_USER );
       }
       $fields = array( 'tags_tag', 'tags_count', 'tags_type' );
       $this->InsertBatch($fields,$values);

Updating Rows

Updating a row in a table relies on you knowing the ID (aka 'key') of the row. For every table in ccHost there is a field with the root name followed by _id.

For example the id field of cc_tbl_user is user_id, cc_tbl_topics is topic_id, etc.

To update a row you fill in an array with the fields and the key:

 $users =& CCUser::GetTable();
 $args['user_id']    = $some_user_id_you_got_from_somewhere;
 $args['user_email'] = $the_new_user_email_address;
 $users->Update($args);