public function testAddingForeignKey()
{
R::nuke();
$database = R::getCell('SELECT current_database()');
$sql = 'CREATE TABLE book (
id SERIAL PRIMARY KEY
)';
R::exec($sql);
$sql = 'CREATE TABLE page (
id SERIAL PRIMARY KEY,
book_id INTEGER
)';
R::exec($sql);
$writer = R::getWriter();
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 0);
$writer->addFK('page', 'page', 'book_id', 'id', TRUE);
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 1);
//dont add twice
$writer->addFK('page', 'page', 'book_id', 'id', TRUE);
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 1);
//even if it is different
$writer->addFK('page', 'page', 'book_id', 'id', FALSE);
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 1);
R::nuke();
$sql = 'CREATE TABLE book (
id SERIAL PRIMARY KEY
)';
R::exec($sql);
$sql = 'CREATE TABLE page (
id SERIAL PRIMARY KEY,
book_id INTEGER
)';
R::exec($sql);
$writer = R::getWriter();
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 0);
$writer->addFK('page', 'page', 'book_id', 'id', FALSE);
$sql = "\n\t\t\tSELECT\n\t\t\t\tCOUNT(*)\n\t\t\tFROM information_schema.key_column_usage AS k\n\t\t\tLEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name\n\t\t\tWHERE k.table_catalog = '{$database}'\n\t\t\t\tAND k.table_schema = 'public'\n\t\t\t\tAND k.table_name = 'page'\n\t\t\t\tAND c.constraint_type = 'FOREIGN KEY'";
$numFKS = R::getCell($sql);
asrt((int) $numFKS, 1);
}