Adding new content with sql statements

[Glenn]

I often have a lot of pages to add at one time and was wondering if anyone had a sql script for doing it right in the db (instead of using the admin front end adding one page at a time).

Here's a script I came up with, and it seems to work, except that in the admin front end the newly added page doesn't show up so I must be missing something. Any help is appreciated.

INSERT INTO `cms_content` VALUES(576, 'City Test 3', 'content', 1, 444, 23, 3, '00002.00003.00003', 0, 'City Test 3', 'City-Test-3', 1, 'html', 1, 1, 1, '2008-09-24 12:57:10', '2008-09-24 12:57:10', NULL, '10.444.574', 'content_en', '', '', '', '', 'Catalog/BYOB/City-Test-3');

INSERT INTO `cms_content_props` VALUES(576, 'string', 'content_en', '', '', '', 'This is where the content for the page goes.', NULL, '2008-09-24 12:57:10');

-- Increment the index value
INSERT INTO `cms_module_search_index` VALUES(665, 'city', 3);
INSERT INTO `cms_module_search_index` VALUES(665, 'test', 3);

INSERT INTO `cms_module_search_items` VALUES(665, 'Search', 576, 'content', NULL);

-- update cms_content_seq with the highest content id from cms_content
UPDATE `cms_content_seq` SET id=576;

-- update cms_module_search_items_seq with the highest index value from search index
UPDATE `cms_module_search_items_seq` SET id=665;

[NaN]

You forgot the table where the sequences of contents are stored. (last given content_id)
When inserting content via sql right in the db you also need to update the table content_seq and content_props_seq.
Just insert the last content id of your newly added pages.
Otherwise you will have trouble when adding a new page in the backend. (results in duplicate entry for the key "content_id" because the cms generates new content_ids from that sequences)

[calguy1000]

also, the hierarchy has to be generated, and checked to be valid.

[Pierre M.]

The installer has such a samplecontent.sql or so file, hasn't it ?

Pierre M.

[tobych]

I'm also interested in adding content to CMSMS without using the admin front-end. I've hacked something up just to test the concept (below). Rather than using SQL directly, though, I'm calling methods on global objects, and my own functions, which are based on functions in the admin/*.php files, tweaked to cope with not being called from forms.

In case this of interest to anyone...

There are still some unanswered questions that I might soon ask in a separate thread. But in case it's any use, the code is below. It's horrible but it works. For me, anyway. As I say, it's just a proof of concept, has no error handling, and has all sorts of other stuff missing. What it does is list the current pages, add 3 pages, list the pages again, delete the 3 pages, list the ages again. Note that I've actually had to make a small (but significant) one-line change to the CMSMS to get this working.

What I'm actually looking at doing is writing a script that will help me move a Dreamweaver-built site into CMSMS:

1. Upload the Dreamweaver site, Templates/ directory included, into the CMSMS installation
2. Go the whatever script I write. It lists all the pages, titles and all, on the Dreamweaver site.
3. I click on a page. The script grabs bits of the page (editable regions, title).
4. It shows a preview of these, perhaps
5. If things look like they're going okay, I hit something and the script adds a page to CMSMS with the appropriate content blocks populated with the content from the page from the static site.

I was originally thinking of doing all this by building an XML-RPC or SOAP interface to CMSMS as a module (that acts as a SOAP client) so I could run the script on my development machine and target a staging or production server running CMSMS with my SOAP module. But I don't really need this.

Toby

Code: Select all

<pre>
<?php
error_reporting(ERROR_ALL);
require_once("include.php");

// Targeted at CMSMS 1.4.1

// Maybe we should be using just one ContentOperations object throughout
// Next:
// - try uploading a page with several content blocks
// - try uploading, and deleting etc, a page that has a non-Null parent

// This is based on code in admin/listcontent.php
function list_pages() {
  global $gCms;
  print "--- Pages --------\n";
  $node =& $gCms->GetHierarchyManager()->getRootNode();
  if ($node->hasChildren()) {
    foreach ($node->getChildren() as $child) {
      $root =& $child;
      $one =& $root->getContent();
      if (isset($one) && $one != NULL)
	if( $one->Type() != 'content')
	  printf("%d %s\n", $one->Id(), $one->Type());
	else
	  printf("%d %s %s\n",  $one->Id(), $one->Type(), $one->mName);
    }
  } else {
    print "No children.\n";
  }
  print "------------------\n";
}

// This is based on code from line 260 of admin/addcontent.php
function create_page($userid, $name, $alias, $content) {
  global $gCms;
  $contentops =& $gCms->GetContentOperations();
  $contentobj = $contentops->CreateNewContent('content');
  $contentobj->SetOwner($userid);
  $contentobj->SetCachable(true);
  $contentobj->SetActive(true);
  $contentobj->SetShowInMenu(true);
  $contentobj->SetLastModifiedBy($userid);
  $contentobj->SetMetadata('');

  $contentobj->SetName($name);
  $contentobj->SetAlias($alias);
  $contentobj->SetPropertyValue('content_en', $content);
  $contentobj->SetTemplateId(17);

  $error = $contentobj->ValidateData();
  if ($error === FALSE) {
    $contentobj->Save();
    global $gCms;
    $contentops =& $gCms->GetContentOperations();
    $contentops->SetAllHierarchyPositions();
    return $contentobj;
  } else {
    print_r($error);
    die("dying");
  }
}

// This is based on deletecontent in admin/listcontent.php
function my_deletecontent($contentid)
{
  // koko we won't bother checking this for now
  // $userid = get_userid();
  // $access = check_permission($userid, 'Remove Pages') || check_permission($userid, 'Modify Page Structure');
	
  global $gCms;
  $tree =& $gCms->GetHierarchyManager();

  // I've removed the $access check here (removes one level of {})
  $node = &$tree->getNodeById($contentid);
  if ($node)
    {
      $contentobj =& $node->getContent();
      $childcount = 0;
      $parentid = -1;
      if (isset($node->parentNode)) {
	  $parent =& $node->parentNode;
	  if (isset($parent)) {
	      $parentContent =& $parent->getContent();
	      if (isset($parentContent)) {
		  $parentid = $parentContent->Id();
		  $childcount = $parent->getChildrenCount();
		}
	    }
	}

      if ($contentobj) {
	  $title = $contentobj->Name();
	
#Check for children
	  if ($contentobj->HasChildren()) {
	      die('errorchildcontent');
	      // $_GET['error'] = 'errorchildcontent';
	    }
	
#Check for default
	  if ($contentobj->DefaultContent()) {
	      die('errordefaultpage');
	      // $_GET['error'] = 'errordefaultpage';
	    }
			
	  $title = $contentobj->Name();
	  $contentobj->Delete();

	  $contentops =& $gCms->GetContentOperations();
	  $contentops->SetAllHierarchyPositions();
				
//  #See if this is the last child... if so, remove
//  #the expand for it
//  	  if ($childcount == 1 && $parentid > -1)
//  	    {
//  	      toggleexpand($parentid, true);
//  	    }
				
//  #Do the same with this page as well
//  	  toggleexpand($contentid, true);
				
//  	  audit($contentid, $title, 'Deleted Content');
				
//  	  $contentops->ClearCache();
			
	  // print "contentdeleted";
// $_GET['message'] = 'contentdeleted';
	}
    }
}

$userid = 1;

print "BEFORE\n";
list_pages();

print "ADDING...\n";
$newPages = array();
for ($i=0; $i<3; $i++) {
  foreach ($newPages as $newPage)
    printf("newPage=%d\n", $newPage);
  $page =& create_page($userid, "Page $i", "page$i", "<p>This is Page $i.</p>");
  $newPages[] = $page->Id();
  printf("ADDED PAGE Id=%d\n", $page->Id());
}

// Seems to be a bug in cmsms here, where all the pages created are
// becoming the most recent page created. Haven't looked closely at
// the code. I'm sure I'm missing something.
// So we'll just make a note of their Ids, rather than storing a
// reference to the object in $newPages.  Yep, that does the job.

// Also, a second list_pages() doesn't show the new pages. 
// I think it's because of line #496 in lib/classes/class.contentoperations.inc.php:
// $usecache = true;
// and the subsequent logic, which appears to not allow caching to be disabled.
// Also, the cache doesn't seem to get updated when a Save() is done; maybe I'm
// missing something here. BTW Save() is at line #917 of class.content.inc.php
// Actually... no... this and/or because CmsObject::getHierarchyManager is
// actually just returning the Tree object, not manager. Is this a
// bug, or a feature? Must be missing something. Hence the use of
// variable name 'tree' in list_pages(), above.
// Anyway. This code works  if at #327 of lib/classes/class.global.inc.php becomes:
//	  if (!isset($this->hrinstance) || TRUE)
// YEP, that does the job.
// HOW TO AVOID THAT HACK??? TRY USING SAME CONTENTOPS OBJECT?? REPORT BUG?? (PATCH)

list_pages();

foreach ($newPages as $id) {
printf("DELETING Id=%d...\n", $id);
  my_deletecontent($id);
}
print "DELETED\n";

print "AFTER\n";
list_pages();

?>
</pre>

<?php
// This is just to show how we can pull pages out using the content operations.
global $gCms;
$contentops =& $gCms->GetContentOperations();
$page = $contentops->LoadContentFromId(15, true);
// echo $page->Name();
echo $page->Show();
?>

[Pierre M.]

You may be interested by a thread about wget automation to do automatically admin things through http rather than SQL directly.

Pierre M.

[Glenn]

Thanks to all for the thoughtful replies!