DROP TABLE IF EXISTS cmsPage;
CREATE TABLE cmsPage (
  cmsPageID INTEGER PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(30)
);

DROP TABLE IF EXISTS cmsBlock;
CREATE TABLE cmsBlock (
  cmsBlockID INTEGER PRIMARY KEY AUTO_INCREMENT,
  blockTypeID INTEGER,
  title VARCHAR(50),
  content TEXT,
  pageID INTEGER

);

DROP TABLE IF EXISTS blockType;
CREATE TABLE blockType (
  blockTypeID INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30)
);

DROP VIEW IF EXISTS pageView;
CREATE VIEW pageView AS
  SELECT 
    blockType.name as 'block',
    cmsBlock.title as 'title',
    cmsBlock.content as 'content',
    cmsPage.cmsPageID as 'pageID',
    cmsPage.title as 'page'    
  FROM 
    cmsBlock, blockType, cmsPage
  WHERE 
    cmsBlock.blockTypeID = blockType.blockTypeID;

INSERT INTO cmsPage VALUES (
  null,
  'main page'
);

INSERT into blockType VALUES (null, 'head');
INSERT into blockType VALUES (null, 'menu');
INSERT into blockType VALUES (null, 'content1');
INSERT into blockType VALUES (null, 'content2');
INSERT into blockType VALUES (null, 'footer');

INSERT INTO cmsBlock VALUES (
  null,
  1,
  'it\'s a binary thing',
  null,
  1
);

INSERT INTO cmsBlock VALUES (
  null,
  2,
  'menu',
  '
          <ul>
          <li><a href = "dbCMS.php?pageID=1">one</a></li>
          <li><a href = "dbCMS.php?pageID=2">two</a></li>
          <li><a href = "dbCMS.php?pageID=3">three</a></li>
        </ul>
  ',
  1
);

INSERT INTO cmsBlock VALUES (
  null,
  3,
  'Factual Error Found on Internet',
  '
LONGMONT, CO - The Information Age was dealt a stunning blow Monday,
when a factual error was discovered on the Internet. The error 
was found on TedsUltimateBradyBunch.com, a Brady Bunch fan site 
that incorrectly listed the show\'s debut year as 1968, not 1969.  
  ',
  1
);

INSERT INTO cmsBlock VALUES (
  null,
  4,
  'Nation\'s Snowmen March Against Global Warming',
  '
WASHINGTON, DC - Braving balmy temperatures and sunny skies, 
millions of scarfless snowmen and snowwomen gathered in 
cities across the world Tuesday to raise public awareness 
about the heavy toll global warming is taking on their 
health and well-being.   
  ',
  1
);  

INSERT INTO cmsBlock VALUES (
  null,
  5,
  null,
  'all \'news\' items from <a href = "http://www.onion.com">The Onion</a>',
  1
);