Documentation Mailing Lists FAQ Frequently Asked Questions v2

How to push/pull data into/from database Author: Honza actionapps@ecn.cz

Related Categories: Developer **Unspecified**   Add Comment

External SQL query should look like:

SELECT * FROM item, content
  WHERE item.id = content.item_id                // to join the tables
      AND item.id = 'hey67s[o0-78z._d'           // 16 characters long packed id
                                                 // see util.php3 - q_pack_id()
      AND content.field_id = 'headline........';

This SQL query select all fields from item table (where are stored just the most common item fields - id, short_id, slice_id, status_code, post_date, publish_date, expiry_date, highlight, posted_by, edited_by, last_edit, display_count, flags, disc_count, disc_app, externally_fed) plus the content of 'headline........' field from content table (will be in content.text field). You can change 'headline........' to any field id, of course, just like 'category.......1' or 'source.........1'. Also you can use short_ids as well to identify the item.

Database structure used for items

Data for an item is not stored in one table, as it is obvious and as you probably expect. Data are divided to two tables - item and content. Item table stores the common fields, which is requiered for each item. All other columns are sored in related table content (relation 1:N). The content table is quite easy - item_id, field_id, number, text, flag. Each row holds contents (in 'text' or 'number' filed) of one field (identified by field_id - like 'headline........') for one item (identified by 'item_id' - related to id in 'item' table).

Such, say 'virtual table database structure' or 'object oriented database' allows to store items of any number of fields. It also allows to store multiple values for one field (you just add a row with the same item_id and field_id).

More complicated SQL query, which contain more fields from content table could look like:

SELECT item.*, c1.text, c2.text FROM item, content as c1, content as c2
  WHERE item.id = c1.item_id       // to join the tables
      AND item.id = c2.item_id       // to join the tables
      AND item.short_id = '13432' // we can use short id
      AND c1.field_id = 'headline........';
      AND c2.field_id = 'full_text.......';

To select all extended fields for the item use:

SELECT * FROM content WHERE item_id = 'hey67s[o0-78z._d';

Item data manipulationg functions (data API)

It is important to know how the data are stored in database, but if you want to write some feature to AA, it is strongly recomended not to use direct database access, but rather use three main data API function to access data (especially for storing data into AA database). The functions are independent on database structure behind and it takes care about feeding, clearing the cache and other stuff.

Main AA data API functions are:

function QueryIDs($fields, $slice_id, $conds, $sort="", $group_by="",
                  $type="ACTIVE", $slices="", $neverAllItems=0,
                  $restrict_ids=array() )
  - stored in include/searchlib.php3

function GetItemContent($ids, $use_short_ids=false)
  - stored in include/util.php3

function StoreItem( $id, $slice_id, $content4id, $fields, $insert,
                             $invalidatecache=true, $feed=true )
  - stored in include/itemfunc.php3

By QueryIDs function you will get array of IDs of items based on your conditions, possiblly sorted. Input conditions are stored in array which you already know from design of searchform (conds[0][publish_date....]=...). This function allows you to get IDs based on any number of conditions. Also it is completely database independent, so you need not to know, which field is stored in item table and which is stored in content table.

The content of an item you will get by GetItemContent function. As input parameter use the IDs of the items you want to get (possibly obtained by QueryIDs). The items are then returned in array structure, which is good to know about, because all data manipulation functions in AA uses this structure to access the item data. This structure is also used as parameter to StoreItem function.

Item data structure

Abstract Data Structure for data manipulation in AA (returned by GetItemContent function):

$content[<unpacked_item_id>][<field_id>)][<alternative>][value|flag]

<unpacked_item_id>

is 32 characters long hexadecimal id of item (structure holds data for more than one item, generaly)

<field_id>

is field id as you know it from Admin -> Fields page of AA admin interface ( like 'headline........', 'url............2', ...)

<alternative>

is number used for storing multiple values for the same field. Obviously there is only '0' index, but for field, where we store multiple categories (for example) it will utilize indexes '0', '1' ,... up to number of selected categories for the item

<value

is probably what you care about - it is content of the field like 'An article title' or 1092928827 or <fulltext of item>

flag>

special flags for the content - if the content is HTML formatted, ... The list of possible flags follows (defined in include/constants.php3)

            define( "FLAG_HTML", 1 );      # content is in HTML
            define( "FLAG_FEED", 2 );      # item is fed
            define( "FLAG_FREEZE", 4 );    # content can't be changed
            define( "FLAG_OFFLINE", 8 );   # off-line filled
            define( "FLAG_UPDATE", 16 );   # content should be updated
                                                     # if source is changed
                                                     #   (after feeding)
      

So, to access content of 'headline.........' field for item number 5e72be7289a0be63820a8723837ce21a you can type:

$text = $content['5e72be7289a0be63820a8723837ce21a']['headline........'][0]['value'];

Last Edit: Aug 17 2011

<aafaq id=1685> ~ToDo: 1685 How to push/pull data into/from database </aafaq> 

This FAQ interface was developed by Jason at Commons.ca

APC ActionApps is a free software content management system initiated by the Association for Progressive Communications (APC) APC - Internet and ICTs for social justice and development