Tuesday, August 4, 2009

layman data I

Helpful links: **Google MySQL Primer MySQL Forge OpenSourceCMS compare insert data from html html data entry formats useful php code Blog w/simple folksonomy schemas

introduction

Folders and a file manager aren't sufficient for speedy file retrieval once a few thousand documents are accumulated. Further, they don't allow for proper metadata storage. I'm sure many home users are in this situation.Our needs are great, but we are basically forced to rely on folders and a file manager or to contact, say, Oracle and pay business rates. And the only in-between option seems to be to go to all the trouble of learning how to build and implement a CMS such as a LAMP or to install a boggy pre-designed LAMP like Joomla or Drupal.

I had a few considerations:

  • PostgreSQL data warehouse
  • browser initiated query ability (JavaScript, PHP, blah blah blah)
  • methods to vacuum, backup, and restore the DB
  • a schema representing the above in some reasonably intuitive way
  • relationships

    The problem was how to establish relationships between a file and several tags. Three commonly used schemas are MySQLicious, Scuttle, and Toxi. There are others, more complex, and faster, but my provider is simple and only has MySQL. Toxi appeared passable for my arrangement. The key though is the PHP to enter the relationships, and in the proper order. Anyway, first, the schemas. This site shows the three options utilizing ER modeling but crows feet is probably the clearest representation. Crow's feet versions can be seen in some of the representations in the blog link above the introduction.

    mysql

    The provider on which my site is parked only provides MySQL for manipulation. This was OK for a trial run. Below are the three tables, taken more or less verbatim from MySQL Forge's excellent page: CREATE TABLE Items ( item_id INT UNSIGNED NOT NULL AUTO_INCREMENT , item_name VARCHAR(255) NOT NULL /* Many more attributes of the item... */ , PRIMARY KEY (item_id) ) ENGINE=InnoDB; CREATE TABLE Tags ( tag_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT , tag_text TEXT NOT NULL , PRIMARY KEY (tag_id) , UNIQUE INDEX (tag_text) ) ENGINE=InnoDB; CREATE TABLE Item2Tag ( item_id INT UNSIGNED NOT NULL , tag_id SMALLINT UNSIGNED NOT NULL , PRIMARY KEY (item_id, tag_id) , INDEX (tag_id) , FOREIGN KEY fk_Item (item_id) REFERENCES Items (item_id) , FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id) ) ENGINE=InnoDB; With these I was nearly able to be up and running, but I received an error when attempting to create "Tags", namely that: Error: tag_text used in key specification without a key length I changed UNIQUE INDEX (tag_text) to index on the first 12 characters: UNIQUE INDEX (tag_text(12)) The table was created properly but my provider does not allow for the InnoDB and so the command was subverted to the ungainly MyISAM. Nothing I could do there. Subsequently, however, I added a column to the table to provide more complete descriptions of files: ALTER TABLE `mydb`.`mytable` ADD COLUMN `item_desc` TEXT NOT NULL AFTER `item_name` I wanted a column to list the page numbers or slide numbers of whatever file I was looking at: ALTER TABLE `mydb`.`mytable` ADD COLUMN `item_pages` SMALLINT NOT NULL DEFAULT '1' AFTER `item_name` BACKUP AND RESTORE Tutorials describe back-ups using phpMyAdmin , or directly from a PHP browser page.

    html

    Challenges include setting up forms inside a table, the order of html and php, and resetting the form after the submission of data. An example of a form inside a table, with a reset function after the data is submitted: <form action="insert1.php" method="post" onsubmit="this.submit(); this.reset(); return false"> <table bordercolorlight="#CFCFCF" bordercolordark="#FFFFFF" border="1" bordercolor="#cfcfcf" cellpadding="2" cellspacing="0" width="100%"> <tbody> <tr> <td align="left" bgcolor="#009dd0" valign="top"><b><font color="#ffffff">description</font></b></td> <td bgcolor="#009dd0"><b><font color="#ffffff">tags</font></b></td> <td bgcolor="#009dd0"><b><font color="#ffffff">slides/pages</font></b></td> <td bgcolor="#009dd0"><b><font color="#ffffff">filename</font></b></td> </tr> <tr> <td><TEXTAREA class="expands" name="item_desc" rows="8" cols="35"></TEXTAREA></td> <td <input name="#" type="text" size="35" /> <td <input name="Position[]" type="text" id="Position[]" size="5" /> <td> <input name="item_name" type="text"></input></td> </tr> </tbody></table></form>

    php

    Appeared at first that an html file containing forms had to be made to enter data and another to retrieve data. Each of these would presumably call an appropriate php script to do the database work. However, it now appears best to include the php right into the html files.

    INSERT (MySQL)

    The core portion of the insert, which pulled values from a previous page's SUBMIT. <? php $hostname="foo"; $username="foo"; $password="foo"; //connection to the tablespace $dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL"); printf ("<p>Status: Connected to tablespace </p>"); //connection to a database $selected = mysql_select_db("foo",$dbhandle) or die("Could not select database"); printf (" <p>Status: Connected to database </p>"); $descrip=$_POST['item_desc']; $filename=$_POST['item_name']; mysql_query("INSERT INTO Items (item_name, item_desc) VALUES ('$filename','$descrip')") or die(mysql_error()); //DB CLOSING mysql_close($dbhandle); ?>