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!

example.php

<?php
/*
 * What about asking PHP to figure out the relation path!???? AWESOME!
 */

// url.user_id -> belongs_to -> users.id

$start = 'vote'; // We have a vote
$end = 'user';   // We have a user

// Now figure out a path to connect them (simple since this is just a hasone/belongsto relation)
// @todo figure out the recursive array climb for user -> vote -> revision -> bill -> member -> user
// Or as we say in english, "Does the user have permission to vote on this revision!?"

$closure = function($start, $end) use(&$relations)
{
	if(isset($relations[$start], $relations[$start][$end]))
	{
		return $start . '.' . $relations[$start][$end][0] . ' = '. $end . '.' . $relations[$start][$end][1];
	}
};

$sql = "SELECT * FROM $end LEFT JOIN $start ON ";
print ($sql . $closure($start, $end));

// SELECT * FROM user LEFT JOIN vote ON vote.user_id = user.id

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

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)
{
	static $relations = null;

	if (isset($relations))
	{
		return $relations;
	}

	// 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;

much appreciated!