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

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

);

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',
    cmsBlock.pageID as 'pageID',
    cmsPage.title as 'page'    
  FROM 
    cmsBlock, blockType, cmsPage
  WHERE 
    cmsBlock.blockTypeID = blockType.blockTypeID;

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');
   
-- create pages --
INSERT INTO cmsPage VALUES (
  null,
  'main page'
);

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

-- data for page 1 --
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=1">three</a></li>
        </ul>
  ',
  1
);

INSERT INTO cmsBlock VALUES (
  null,
  3,
  'Book I - Creating the XHTML Foundation',
  '
<ol>
  <li>Sound XHTML Foundations</li>
  <li>It\'s All About Validation</li>
  <li>Choosing your Tools</li>
  <li>Managing Information with Lists and Tables</li>
  <li>Making Connections with Links</li>
  <li>Adding Images</li>
  <li>Creating forms</li>
</ol>
  ',
  1
);

INSERT INTO cmsBlock VALUES (
  null,
  4,
  'Book II - Styling with CSS',
  '
<ol>
  <li>Coloring Your World</li>
  <li>Styling Text</li>
  <li>Selectors, Class, and Style</li>
  <li>Borders and Backgrounds</li>
  <li>Levels of CSS</li>
</ol>  
  ',
  1
);  

INSERT INTO cmsBlock VALUES (
  null,
  5,
  null,
  'see <a href = "http://www.aharrisbooks.net">aharrisbooks.net</a> for more information',
  1
);

-- data for page 2 --
INSERT INTO cmsBlock VALUES (
  null,
  1,
  'page 2',
  null,
  2
);

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=1">three</a></li>
        </ul>
  ',
  2
);

INSERT INTO cmsBlock VALUES (
  null,
  3,
  'Book III - Positionable CSS',
  '
Building complex layouts using various CSS tricks including
floating layouts, absolute positioning, and other great techniques
',
  2
);

INSERT INTO cmsBlock VALUES (
  null,
  4,
  'Book IV - JavaScript',
  '
Introducing client-side programming with JavaScript
  ',
  2
);  

INSERT INTO cmsBlock VALUES (
  null,
  5,
  null,
  'see <a href = "http://www.aharrisbooks.net">aharrisbooks.net</a> for more information',
  2
);


