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;

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.