Hello!

I am looking at the database created by couch_cms and unless I am mistaken, there do not seem to be any foreign keys amongst the tables. Is this for a particular reason? I am hoping to add foreign keys to get some of my own queries to work properly.

For example - the couch_pages table has a template_id column. In the couch/install.php i do not see the creation of a foreign key:

Code: Select all

    $k_stmts[] = "CREATE TABLE ".K_TBL_PAGES." (
      id                 int AUTO_INCREMENT NOT NULL,
      template_id        int NOT NULL,
      parent_id          int DEFAULT '0',
      page_title         varchar(255),
      page_name          varchar(255),
      creation_date      datetime DEFAULT '0000-00-00 00:00:00',
      modification_date  datetime DEFAULT '0000-00-00 00:00:00',
      publish_date       datetime DEFAULT '0000-00-00 00:00:00',
      status             int,
      is_master          int(1) DEFAULT '0',
      page_folder_id     int DEFAULT '-1',
      access_level       int DEFAULT '0',
      comments_count     int DEFAULT '0',
      comments_open      int(1) DEFAULT '1',
      nested_parent_id   int DEFAULT '-1',
      weight             int DEFAULT '0',
      show_in_menu       int(1) DEFAULT '1',
      menu_text          varchar(255),
      is_pointer         int(1) DEFAULT '0',
      pointer_link       text,
      pointer_link_detail text,
      open_external int(1) DEFAULT '0',
      masquerades          int(1) DEFAULT '0',
      strict_matching      int(1) DEFAULT '0',
      file_name            varchar(260),
      file_ext             varchar(20),
      file_size            int DEFAULT '0',
      file_meta            text,
      creation_IP          varchar(45),
      k_order            int DEFAULT '0',

      PRIMARY KEY (id)
    ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;";

Has anyone else tried manipulating the couch_cms tables? Has anything bad happened to you guys? I'm not an expert in mysql by any means so I can't imagine why a foreign key would affect any of the functionality.

[KK]

Hi,

Defining explicit foreign keys, as I am sure you know, makes the database engine enforce referential integrity e.g.
disallow a query to run where one tries to delete a template while there are pages referring to it (or, if you have set cascade, delete all pages records referring to the template being deleted as well).

Helpful things for sure but that is not to say that you cannot do without using foreign keys (in fact, the MyISAM engine of MySQL has no provision for defining foreign keys and, therefore, the default installation of Wordpress, the most used CMS, using it has no foreign keys defined).

That then, of course, presupposes that your code takes over this responsibility on itself and prevents violation of referential integrity.

Couch does its utmost to do that e.g. it won't allow to delete a template that has any existing cloned pages.

So, to answer your query, as long as you are manipulating data from within Couch, you should be safe.
Once you try to do that from outside, for example, delete a template record using phpMyAdmin directly or from your custom addon, you are responsible for knowing what you are doing (and that would include defining explicit foreign keys yourself that cascade delete all related records)..

Hope this answers your query.

Thank you for the reply KK,

based on your response, it seems like Couch was designed without foreign keys because not only is it an industry standard set by Wordpress, but MyISAM does not support foreign keys, and you would want this CMS to be "environment agnostic"?

I have never dug deep enough into mysql to read up on MyISAM or InnoDB which seem to be the two options, looks like I have more reading to do.

Thanks for your help!