lessql auto convention

books.sql

-- Adminer 4.2.2 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `person_id` (`person_id`),
  CONSTRAINT `author_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `publish_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `book_author`;
CREATE TABLE `book_author` (
  `book_id` int(11) NOT NULL,
  `author_id` tinyint(4) NOT NULL,
  PRIMARY KEY (`book_id`,`author_id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE,
  CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `illustration`;
CREATE TABLE `illustration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `illustrator_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `path` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `illustrator_id` (`illustrator_id`),
  KEY `page_id` (`page_id`),
  CONSTRAINT `illustration_ibfk_1` FOREIGN KEY (`illustrator_id`) REFERENCES `illustrator` (`id`) ON DELETE CASCADE,
  CONSTRAINT `illustration_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `illustrator`;
CREATE TABLE `illustrator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `illustrator_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `page`;
CREATE TABLE `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) NOT NULL,
  `page_number` int(11) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `page_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `birthday` date NOT NULL,
  `gender` enum('male','female') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2016-04-28 19:31:44

convention.php

<?php
$pdo = new PDO('mysql:dbname=hamershof;charset=utf8', 'root', 'mysql', array(
    PDO::ATTR_STRINGIFY_FETCHES   => false,
    PDO::ATTR_DEFAULT_FETCH_MODE  => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE             => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_FETCH_CATALOG_NAMES => true,
    PDO::ATTR_CASE                => PDO::CASE_LOWER,
));
$primary_keys = array();
$references = array();
$back_references = array();
$skip_default_convention = false;
$result = $pdo->query(<<<SQL
SELECT * FROM information_schema.key_column_usage
WHERE table_schema = DATABASE() AND IFNULL(referenced_table_schema, DATABASE()) = DATABASE()
SQL
);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $table_name = $column_name = $referenced_table_name = $referenced_column_name = $constraint_name = null;
    extract($row);
    if ($constraint_name == 'PRIMARY') {
        if (isset($primary_keys[$table_name])) {
            if (!is_array($primary_keys[$table_name])) {
                $primary_keys[$table_name] = (array)$primary_keys[$table_name];
            }
            $primary_keys[$table_name][] = $column_name;
        } else {
            $primary_keys[$table_name] = $column_name;
        }
    } elseif ($referenced_column_name) {
        if (!$skip_default_convention || $column_name != $referenced_table_name . '_id') {
            $references[] = array($table_name, $referenced_table_name, $column_name);
            $back_references[] = array($referenced_table_name, $table_name, $column_name);
        }
    }
}
// strip the primary keys matching the default pattern
// this is done here since a compound key can have an id field..
if ($skip_default_convention) {
    $primary_keys = array_filter($primary_keys, function ($pk) {
        return $pk != 'id';
    });
}
foreach (compact('primary_keys', 'references', 'back_references') as $k => $v) {
    echo '<h2>$', $k, '</h2><pre>', htmlspecialchars(print_r($v, true)), '</pre>';
}

output.txt

$primary_keys

Array
(
    [author] => id
    [book] => id
    [book_author] => Array
        (
            [0] => book_id
            [1] => author_id
        )

    [illustration] => id
    [illustrator] => id
    [page] => id
    [person] => id
)
$references

Array
(
    [0] => Array
        (
            [0] => author
            [1] => person
            [2] => person_id
        )

    [1] => Array
        (
            [0] => book_author
            [1] => book
            [2] => book_id
        )

    [2] => Array
        (
            [0] => book_author
            [1] => author
            [2] => author_id
        )

    [3] => Array
        (
            [0] => illustration
            [1] => illustrator
            [2] => illustrator_id
        )

    [4] => Array
        (
            [0] => illustration
            [1] => page
            [2] => page_id
        )

    [5] => Array
        (
            [0] => illustrator
            [1] => person
            [2] => person_id
        )

    [6] => Array
        (
            [0] => page
            [1] => book
            [2] => book_id
        )

)
$back_references

Array
(
    [0] => Array
        (
            [0] => person
            [1] => author
            [2] => person_id
        )

    [1] => Array
        (
            [0] => book
            [1] => book_author
            [2] => book_id
        )

    [2] => Array
        (
            [0] => author
            [1] => book_author
            [2] => author_id
        )

    [3] => Array
        (
            [0] => illustrator
            [1] => illustration
            [2] => illustrator_id
        )

    [4] => Array
        (
            [0] => page
            [1] => illustration
            [2] => page_id
        )

    [5] => Array
        (
            [0] => person
            [1] => illustrator
            [2] => person_id
        )

    [6] => Array
        (
            [0] => book
            [1] => page
            [2] => book_id
        )

)