Abstracting functionality with centralised content

Abstracting functionality with Centralised ContentMichael Peacock

About meSenior Web DeveloperM.D. of design agency Peacock CarterTechnical director for an online retailerAuthorwww.michaelpeacock.co.ukme@michaelpeacock.co.uk@michaelpeacock

What's in store?Setting the scene – a look at the problem centralised content solvesCentralised content – what is it and how can it solve this problemImplementation – How we implemented centralised content with PHP and MySQL

A sample bespoke CMS / basic e-commerce websitePagesBlog entriesBlog categoriesNews articlesProductsProduct CategoriesEventsUsers can buy productsUsers can rate productsUsers can comment on / review productsUsers can comment on blog entries

CommentingNeeded for both blog entries and productsCreate a simple library or function to create a comment for usComments table in our databaseTable to link them to blog entriesTable to link them to products

What about searchingDo we have search boxes for different aspects of the site?Do we use a complex searching system?Should we just let Google do it for us?“Can’t someone else do it?” Homer J Simpson

What if in the future, once development is complete…Users need to be able to rate blog entriesUsers need to be able to purchase (book onto) events… and comment on them… … and rate them…Sounds like a pain!

Let’s take a step back…And centralise our content!

Centralised ContentUseful architecture; especially for CMS projectsMVC brings out the best in itDrupal“node”MVC would be nice, DrupalUsed extensively in our own CMS and frameworks for the past year and a half;

ContentTypical content found on a CMS powered site:PagesBlog entriesNews articlesJob vacanciesProductsEventsPhotographs / Image gallery

PagesNameHeadingPage contentMeta data / titleURL

Blog EntriesNameHeadingBlog entryMeta data / titleLeading imageLeading paragraphAuthorURL

News articlesNameHeadingNews articleMeta data / titleLeading imageLeading paragraphAuthorURL

Job VacanciesNameHeadingJob descriptionLocationApplication deadlineSalaryMeta data / titleAuthorURL

ProductsNameHeadingProduct descriptionPriceWeightImageMeta data / titleAuthorURL

EventsNameHeadingEvent descriptionLocationDateStart / End timeMeta data / titleAuthorURL

Gallery ImagesNameHeadingImage caption / descriptionCamera dataLocationImage locationMeta data / titleAuthorURL

Its all the same! (well, almost…)NameHeadingTitleURL / Path / Search Engine friendly namePrimary content / description / detailsMeta dataCreator / Author Active / EnabledComments enabled / disabled

…with extra bits depending on the typeProductsPrice; Stock level; SKU; WeightEventsVenue; Spaces; Price; Date; Start time; End timeGallery imageImage file location; Camera details; LocationJob vacancySalary; Location; Start date; Type; Application date

Content versionsWith content being centralised we can implement versioning more easilyRecord all versions of contentStatic content IDs which relate to the active version

So; let’s centralise it!Core fields will make up our main table of content (content_versions)Content types will have their own table, containing type specific fields content_versions_*A content table (content) will store static data, such as author, creation date, ID, and reference the current active version certain toggle-able fields (active, commentable) should go hereRegardless of the version in play, the content ID can be used to access the element, and won’t change.

Within MVCContent modelModels for each content type, extending the content modelFor administrative tasks (CMS) content controller to perform shared operations: toggle active, toggle comments, deleteContent type controllers extend

Content modelDeals exclusively with content and content versions tablesGetters and setters for core content fieldsCreating content:Create new content versionCreate new content record, pointing to the versionEditing contentCreate new content versionLog the old version ID in a versions logUpdate the content record to point to the new version

Content: savepublic function save(){ // are we creating a new content item? if( $this->id == 0 ) {/** create the content versions record */ $this->registry->getObject('db')->insertRecords( 'content_versions', $insert );// record the ID $this->revisionID = $this->registry->getObject('db')->lastInsertID();/** insert the content record */ $this->registry->getObject('db')->insertRecords( 'content', $insert );// record the ID $this->id = $this->registry->getObject('db')->lastInsertID(); } else {// have we changed the revision, or just something from the content table? if( $this->revisionChanged == true ) {// make a note of the old revision ID for the history $this->oldRevisionID = $this->revisionID;/** insert the new content_versions record */ $this->registry->getObject('db')->insertRecords( 'content_versions', $insert );// update the revisionID $this->revisionID = $this->registry->getObject('db')->lastInsertID();/** record the history */ $this->registry->getObject('db')->insertRecords( 'content_versions_history', $insert); }/* update the content table */ $this->registry->getObject('db')->updateRecords('content', $update, 'ID=' . $this->id ); }}

Product (i.e. a content type) modelExtends content modelGetters and setters for extended data for the content typeCreating product

Product: savepublic function save(){// Creating a new product if( $this->getID() == 0 ) {parent::setType( $this->typeID ); parent::save(); $this->saveProduct(); } else { // tells the parent, that the revision has changed, // i.e. that we didn't just toggle active / change something from the _content_table!$this->setRevisionChanged( true ); parent::save(); $this->saveProduct(); }}

Product: Saving product dataprivate function saveProduct(){/** insert product specific data */ // product version ID should be the same as the content version ID for easy maping $insert['version_id'] = $this->getRevisionID(); $this->registry->getObject('db')->insertRecords( 'content_versions_store_products', $insert ); // get the content ID $pid = $this->getID(); // categories // delete all associations with this product ID // insert new ones based off user input; $pid to reference product // shipping costs // delete all associations with this ID // insert new ones based off user input; $pid to reference product}

A load of CRUD!CreatingNew features / content types we only need to code for the extended fieldsReading Custom constructor in the child modelCall setters for content type specific fieldsCall parent setters for core contentChild method to iterate through / process fields to go to the template engine

A load of CRUD!UpdatingParent deals with all the core fields (no new work!)Insert (versions) extended fieldsUse the ID the parent gives to the content version, to make table mapping easierDeletingAssuming “deleting” just hides content from front and back-endParent object updates content record to deleted – no extra work!

CommentingRequires just one database tableCan be used, without additional work, for all content typesEach comment relates to a record in the content table

... commentingprivate function postComment( $id ){require_once( FRAMEWORK_PATH . 'models/comment/comment.php'); $comment = new Commentmodel( $this->registry, 0 ); // tell the comment which content element it relates to $comment->setContent( $id ); $comment->setName( isset( $_POST['comment_name'] ) ? $_POST['comment_name']: '' ); $comment->setEmail( isset( $_POST['comment_email'] ) ? $_POST['comment_email']: '' ); $comment->setURL( isset( $_POST['comment_url'] ) ? $_POST['comment_url']: '' ); $comment->setComment( isset( $_POST['comment_comment'] ) ? $_POST['comment_comment']: '' ); $comment->setIPAddress( $_SERVER['REMOTE_ADDR'] ); $comment->setCommentsAutoPublished( $this->registry->getSetting('blog.comments_approved') ); $comment->setPageURL( $this->registry->getURLBits() ); if( $comment->checkForErrors() ) { /** error processing */ } else { // save the post $comment->save();/** Redirect to the controller the user was on before, based on the URL */ }}

RatingAgain, just one tableDirectly relates to the appropriate content elementCreate it once; works for all content types – no future work for new content types

Geo-taggingEither extend the content / versions tableORCreate a co-ordinates table and map it to the content table

What else?Keyword taggingKeywords tableContent keywords associations tableCentral functionality to add keywords and delete orphaned keywordsCategoriesA content type (up for debate) – why?New table to map content to contentCentral functionality to manage associations

PurchasingProvided content types have consistent cost / shipping fields, they can slot into the order pipelineWon’t work for all content typesMakes conversion easyMake event purchasable?Make gallery image purchasable?Just add price fields, and indicate the content type can be purchased

PurchasingBy giving an image a price field, pre-existing e-commerce functionality can process itPiece of cake

Hierarchies and orderingOrdering pages within the sites menuMoving pages within another pageOrdering product categoriesOrdering blog entriesOrdering news articles

SearchingSearch the content & content versions tableLEFT JOIN content_versions_* tables where appropriateDesignate searchable fieldsExecute the queryEnjoy integrated search results!

Searching: Define our extended search fieldsprivate $extendedTablesAndFields = array( 'content_versions_news' => array( 'joinfield' => 'version_id', 'fields' => array( 'lead_paragraph' ) ) );

Searching: Build our left joins$joins = "";$selects = "";$wheres = " ";$priority = 4;$orders = "";foreach( $this->extendedTablesAndFields as $table => $data ){ $field = $data['joinfield']; $joins .= " LEFT JOIN {$table} ON content.current_revision={$table}.{$field} "; foreach( $data['fields'] as $field ) { $wheres .= " IF( {$table}.{$field} LIKE '%{$phrase}%', 0, 1 ) <> 1 OR "; $selects .= ", IF( {$table}.{$field} LIKE '%{$phrase}%', 0, 1 ) as priority{$priority} "; $orders .= " priority{$priority} ASC, "; $priority++; }}

Searching: Query!$sql = "SELECT *, IF(content_types.reference='page',content.path,CONCAT(content_types.view_path,'/',content.path ) ) as access_path, REPLACE(substr(content_versions.content,1,100),'<p>','') as snippet, content_types.name as ct, IF( content_versions.name LIKE '%{$phrase}%', 0, 1 ) as priority0, IF( content_versions.title LIKE '%{$phrase}%', 0, 1 ) as priority1, IF( content_versions.heading LIKE '%{$phrase}%', 0, 1 ) as priority2, IF( content_versions.content LIKE '%{$phrase}%', 0, 1 ) as priority3 {$selects} FROM content LEFT JOIN content_types ON content.type=content_types.ID{$joins} LEFT JOIN content_versions ON content.current_revision=content_versions.ID WHERE content.active=1 AND content.deleted=0 AND ( IF( content_versions.name LIKE '%{$phrase}%', 0, 1 ) <> 1 OR IF( content_versions.title LIKE '%{$phrase}%', 0, 1 ) <> 1 OR IF( content_versions.heading LIKE '%{$phrase}%', 0, 1 ) <> 1 OR IF( content_versions.content LIKE '%{$phrase}%', 0, 1 ) <> 1 OR{$wheres} ) ORDER BY priority0 ASC, priority1 ASC, priority2 ASC, priority3 ASC, {$orders} 1";

Simple access permissionspublic function isAuthorised( $user ){ if( $this->requireAuthorisation == false ) { return true; }elseif( $user->loggedIn == false ) { return false; }elseif( count( array_intersect( $this->authorisedGroups, $user->groups ) ) > 0 ) { return true; } else { return false; }}

(Simple) Access permissionsSingle table mapping content to groupsAt content level, for content elements which require – cross reference the users groups with allowed groups

Downloads / Files / ResourcesCreate them as a content typeSearchable based off name / descriptionStore the file outside of the web rootMake use of access permissions already implementedMake them purchasable

Not just “content”!Other entities within an application which are similarSocial NetworksCRM’s

Social Networks: StatusesCore table: statusCreatorProfile status posted on (use it for both statuses and “wall posts”The statusCreation date

Social Networks: StatusesExtended tables:Videos: YouTube Video URLImages: URL, DimensionsLinks: URL, Title

... Build a streamStatus streamsRecent activityViewing a profile’s “wall posts”

Query the streamPart of a stream modelSELECT t.type_reference, t.type_name, s.*, p.name as poster_name, r.name as profile_nameFROM statuses s, status_types t, profile p, profile r WHERE t.ID=s.type AND p.user_id=s.poster AND r.user_id=s.profileAND ( p.user_id={$user} OR r.user_id={$user} OR ( p.user_id IN ({$network}) AND r.user_id IN ({$network}) ) )ORDER BY s.ID DESC LIMIT {$offset}, 20

Generate the streamFor each stream recordInclude a template related to:the stream item type, e.g. videoThe context, e.g. Posting on your own profileInsert stream record details into that instance of the template bit

foreach( $streamdata as $data ){ if( $userUpdatingOwnStatus ) { // updates to users "wall" by themselves $template->addBit( 'stream/types/' . $data['type_reference'] . '-me-2-me.tpl.php', $data ); }elseif( $statusesToMe ) { // updates to users "wall" by someone $template->addBit( 'stream/types/' . $data['type_reference'] . '-2me.tpl.php', $datatags ); }elseif( $statusesFromMe ) { // statuses by user on someone elses wall $template->addBit( 'stream/types/' . $data['type_reference'] . '-fromme.tpl.php', $datatags ); } else { // friends posting on another friends wall $template->addBit( 'stream/types/' . $data['type_reference'] . '-f2f.tpl.php', $datatags ); }}

CRM’sPeople and organisations are very similarCentralise them!EntityEntity_OrganisationEntity_PersonRecord Person <-> organisation relationships in a table mapping entity table onto itself

Extending: Adding new content typesDrop in a modelGetters and setters for extended fieldsSave method to insert extended dataDrop in an administrator controllerRely on parent for standard operationsPass CRUD requests / data to the modelDrop in a front end controllerViewing: over-ride parent method & extend the select queryAnything else: Add specific functionality here

Why centralise your content / common entities?Eases content versioningWrite functionality (commenting, rating, etc) once, and it works for all current and future content types – without the need for additional workFixing bugs with abstracted features, fixes it for all aspects which use that featureConversion is relatively easy – e.g. Turn a page into a blog entry. More so if they don’t extend the core table

Some of the problemsCan be a risk of content and content versions tables having too many fieldsOptimization bottle-neck – poorly performing and optimizing tables will cripple the entire siteVersioning: Stores lots of dataSometimes you want functionality to differ across features (e.g. Product reviews <> comments)A bug in an abstract feature will be present in all aspects which use it

phpMyAdmin is harderManaging content via phpMyAdmin is more difficultMySQL Views can help with listing and viewing content

Thanks for listeningwww.michaelpeacock.co.ukme@michaelpeacock.co.uk@michaelpeacock Please leave feedback!http://joind.in/2065

Abstracting functionality with centralised content

More Related Content

What's hot

Viewers also liked

Similar to Abstracting functionality with centralised content

More from Michael Peacock

Abstracting functionality with centralised content