MySQL database relations

readme.md

Auto-Relations (AutoCRUD)

So why do we define the relations manually in ORM objects when the database knows full-well what is going on?

This is a simple prototype of the idea that a PHP + MySQL app can handle fetching and checking relations automatically without any help from the user.

In the example.php you see a sample of two objects passed to the page A) The user and B) the Vote they want to create/edit/delete. Thanks to the relations we got in relations.php we can easily check that a user has permission to access that record because they have an entry in user (via vote.user_id) that matches the user object they are.

A more complex example would be recusing down the relation tree looking in the bill memberships table to see if they have permission to vote on a revision that belongs to the bill that is in the membership the user posses.

In other words, why build backend CRUD? PHP can do it for you!

index.php

<?php

require('relations.php');

$database = 'relations';

header('Content-Type: text/plain; charset="UTF-8"');

$relations = relations($database);

print_r($relations); // See `output.txt`

/*
 * Now that we have a map of all the FK relations we can figure out the path
 * between the entities using this recursive closure.
 */
$closure = function($start, $end) use(&$relations, &$closure)
{
	$queries = array();

	if(isset($relations[$start]))
	{
		if(isset($relations[$start][$end]))
		{
			$queries[] = array($start, $end);
			return $queries;
		}
		else
		{
			die('Now what...?');
		}
	}
	else
	{
		foreach($relations as $relation => $other)
		{
			if(isset($other[$start]))
			{
				$result = $closure($relation, $start);
				$queries = array_merge($queries, $result);

				// Now continue looking for the end
				if($result)
				{
					$result = $closure($relation, $end);
					$queries = array_merge($queries, $result);
				}
			}
		}
	}

	return $queries;
};

/*
 * Lets start with a [user] object (perhaps from the session) along with a request
 * to see how a [bill] object could be related to a [user] object. In the following
 * example we will expect "2" paths to show up based on our schema:
 * 
 * [bill] hasmany [revision] which belong to [user]s
 * [user] hasmany [membership]s which belongto [bill]
 */

$start = 'bill';
$end = 'user';

print_r($closure($start, $end));

/*
Array
(
	[0] => Array
		(
			[0] => revision
			[1] => bill
		)

	[1] => Array
		(
			[0] => revision
			[1] => user
		)

	[2] => Array
		(
			[0] => members
			[1] => bill
		)

	[3] => Array
		(
			[0] => members
			[1] => user
		)
)
*/

/*
 * Well it looks like it worked! Here you can see that a [user] revision belongs to
 * a [bill] & [user]. So the relation must be [bill] <--- [revision] ---> [user].
 *
 * However, we also see a second relation show up. [user] has access to [bill]s 
 * via [membership]. So the relation must be [bill] <--- [membership] ---> [user].
 */

output.txt

Array
(
    [revision] => Array
        (
            [bill] => Array
                (
                    [0] => bill_id
                    [1] => id
                )

            [user] => Array
                (
                    [0] => author_id
                    [1] => id
                )

        )

    [members] => Array
        (
            [bill] => Array
                (
                    [0] => bill_id
                    [1] => id
                )

            [user] => Array
                (
                    [0] => user_id
                    [1] => id
                )

        )

    [vote] => Array
        (
            [revision] => Array
                (
                    [0] => revision_id
                    [1] => id
                )

            [user] => Array
                (
                    [0] => user_id
                    [1] => id
                )

        )

)

relations.php

<?php

require('DByte/DB.php');

// Create a new PDO connection to MySQL
$pdo = new PDO(
	'mysql:host=localhost',
	'root',
	'',
	array(
		PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
	)
);

DB::$c = $pdo;

function relations($database)
{
	// Load all the foreign keys for this database
	$sql = "SELECT " //TABLE_SCHEMA as `database`,
	."TABLE_NAME as `table`,
	COLUMN_NAME as `column`,
	REFERENCED_TABLE_NAME as foreign_table,
	REFERENCED_COLUMN_NAME as foreign_column,
	CONSTRAINT_NAME as index_name
	FROM information_schema.KEY_COLUMN_USAGE
	WHERE referenced_table_name IS NOT NULL
	AND table_schema = ?
	ORDER BY TABLE_SCHEMA, referenced_table_name";

	$rows = DB::fetch($sql, array($database));

	$relations = array();
	foreach($rows as $row)
	{
		$relations[$row->table][$row->foreign_table] = array($row->column, $row->foreign_column);
	}

	return $relations;
}


$relations = relations('databasenamehere');

print_r($relations);

schema.sql

CREATE TABLE IF NOT EXISTS `bill` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `members`
--

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `bill_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `bill_id` (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `revision`
--

CREATE TABLE IF NOT EXISTS `revision` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(10) unsigned NOT NULL,
  `bill_id` int(10) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `bill_id` (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `vote`
--

CREATE TABLE IF NOT EXISTS `vote` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `revision_id` int(10) unsigned NOT NULL,
  `vote` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `revision_id` (`revision_id`),
  KEY `vote` (`vote`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `members`
--
ALTER TABLE `members`
  ADD CONSTRAINT `members_ibfk_2` FOREIGN KEY (`bill_id`) REFERENCES `bill` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `members_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `revision`
--
ALTER TABLE `revision`
  ADD CONSTRAINT `revision_ibfk_2` FOREIGN KEY (`bill_id`) REFERENCES `bill` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `revision_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `vote`
--
ALTER TABLE `vote`
  ADD CONSTRAINT `vote_ibfk_2` FOREIGN KEY (`revision_id`) REFERENCES `revision` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `vote_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Looks clean, but what's the difference from this and, for instance, NoSQL schema discovery? (Sorry, but I haven't had the time to try this out or look at it in detail.)

I'm not sure what NoSQL auto schema discovery is (even after googling it). However, this is the idea that you deal with objects passing back-and-forth between the user agent and the server. Since MySQL has a copy of the foreign keys between tables (object collections), why write PHP controllers or validation handlers at all when you can just pass a couple objects to PHP and have it figure out the access restrictions, object types, relations, and everything else.

So, say I login. I now have my user object id. Each time I make a request I pass it along with the type of object I want to create/update/delete (like a vote) back to the server. Now the server has two objects (my user) and vote - it can trace back through the relations in the schema to find out if there is a matching user object to mine that has permission create/update/delete the vote object I just passed. I now have a PHP script that builds the CRUD services I need without writing controller code for each and ever type of object I want to create (forum post, blog article, user, membership, ticket, etc...)

Perhaps add a config file with validation closures for each column type and your done.

$array('title' => function($title) { if( ! preg_match('~foo~', $title)) return _('error'); });

Sorry, I meant NotORM not NoSQL.

I'm kinda busy ATM, but I'll take a closer look at this ASAP and I'll let you know what I think.

Don't worry about it. Apparently I need to do more work as it just isn't clear enough yet. I'll keep working on it and try to build a better prototype.

I updated the index.php example again, this time it shows how you can ask for a connection between two objects [user] and [bill] and it will find the path(s) between them.