Table constraints and module dependencies

The place to go for any sort of help with using or developing with Bonfire.
Post Reply
User avatar
juanmatias
Posts: 6
Joined: 06 Nov 2017, 17:53
Location: Córdoba, Argentina
Contact:

07 Nov 2017, 18:10

Hello, bonfirepeople.

I have two little questions.

1. What's the proper way to insert "foreign keys" in migrations.

I was reading but it seems to be that the only way it with plain sql. I know that BF insert a preffix on tablenames, so I'd like to know if there is a proper way to go.
My main concern is avoid other module uninstall from deleting a referenced table.

2. What's the proper way to check and handle modules dependencies.

I know tha idea behind modules is to keep them away from each other. But I'm thinking on a module that uses other module functionality or extends it. So I must be sure the "parent" module exists.
I thought on use module_controller_exists() in migration of my new module.


A case to do this could be:
I have a courses module. It adds simple functionality to allow users to take courses and learn something. This course module has only text material.

Then I build a new module, this one just adds a functionality to have graphic material on my courses. So I can have a course_id in my graphic_material table that is a foreign key to courses.id.

So I must:
  • Check courses module exists when installing graphic material module
  • Avoid deletion of courses table while graphic material exists
  • Avoid courses removal while graphic material module exists

I'm working with my own code, but I'd like t know if there is a BF way.

Thanks!
juanmatias

User avatar
juanmatias
Posts: 6
Joined: 06 Nov 2017, 17:53
Location: Córdoba, Argentina
Contact:

08 Nov 2017, 11:55

Ok, I'm here again to post an idea on my problem.

For item 1, I think I can use sql and get the db prefix from config/database.php

Code: Select all

'dbprefix'     => 'bf_',
For example, in migrations file:

Code: Select all

$this->dbforge->add_field('CONSTRAINT FOREIGN KEY (parent_id) REFERENCES '.$db_prefix.'parent(id)');
Now I must figure out how to get this value in the BF way.

For item 2, I was thinking on use config($module_name[, $return_full = false]) instead of module_controller_exists() .
This way I can check module's version too.

I'll try it and will come back to share my thoughts.
juanmatias

User avatar
juanmatias
Posts: 6
Joined: 06 Nov 2017, 17:53
Location: Córdoba, Argentina
Contact:

09 Nov 2017, 12:17

Ok, me again.

I want to share my solution for the issue with DB constraints.

I will drop here an example to you to see.
I stripped out code to make clear what I've done.

I'm creating a parent table and then a child one.
The second one has two contraint, one to parent and one to users.

In the $tables array I've added a key called foreignKeys. The constraint is defined with the string dbprefix_ prefixing the table name.
Inside up function I've added a check, if foreignKeys exists then replace dbprefix_ with the actual prefix and add the field.

Code: Select all

<?php defined('BASEPATH') || exit('No direct script access allowed');

class Migration_Initial_tables extends Migration
{
    private $permissionValues = array(
        array('name' => 'Aula.Content.View', 'description' => 'View the Aula menu.', 'status' => 'active'),
        array('name' => 'Aula.Content.Delete', 'description' => 'Delete Aula entries', 'status' => 'active'),
    );

    private $permittedRoles = array(
        'Administrator',
	);
     /**
     * The definition(s) for the table(s) used by this migration.
     * @type array
     */
	private $tables = array(
		'courses' => array(
			'primaryKey' => 'id',
			'fields' => array(
				'id' => array(
					'type'           => 'bigint',
					'constraint'     => 20,
					'unsigned'       => true,
					'auto_increment' => true,
				),
				'title' => array(
					'type'       => 'varchar',
					'constraint' => 255,
					'null'       => false,
				),
				'body' => array(
					'type'       => 'varchar',
					'constraint' => 5000,
					'null'       => false,
				),
				'created_on' => array(
					'type' => 'datetime',
					'null' => false,
				),
				'modified_on' => array(
					'type'    => 'datetime',
					'null'    => true,
					'default' => '0000-00-00 00:00:00',
				),
			),
		),
		'enrollments' => array(
			'primaryKey' => 'id',
			'foreignKeys' => array(
				'CONSTRAINT FOREIGN KEY (course_id) REFERENCES dbprefix_courses(id)',
				'CONSTRAINT FOREIGN KEY (user_id) REFERENCES dbprefix_users(id)',
			),
			'fields' => array(
				'id' => array(
					'type'           => 'bigint',
					'constraint'     => 20,
					'unsigned'       => true,
					'auto_increment' => true,
				),
				'course_id' =>array(
					'type'           => 'bigint',
					'constraint'     => 20,
					'unsigned'       => true,
				),
				'user_id' =>array(
					'type'           => 'bigint',
					'constraint'     => 20,
					'unsigned'       => true,
				),
				'created_on' => array(
					'type' => 'datetime',
					'null' => false,
				),
				'deleted' =>array(
					'type'           => 'tinyint',
					'constraint'     => 1,
					'unsigned'       => true,
					'default'		 => 0,
				),
			),
		),
	);

    /**
     * Install the blog tables
     *
     * @return void
     */
    public function up()
    {
        $this->load->dbforge();

        // Install the table(s) in the database.
        foreach ($this->tables as $tableName => $tableDef) {
            $this->dbforge->add_field($tableDef['fields']);
            $this->dbforge->add_key($tableDef['primaryKey'], true);

			if(array_key_exists('foreignKeys',$tableDef))
			{
				foreach($tableDef['foreignKeys'] as $fk)
				{
					$this->dbforge->add_field(preg_replace('/dbprefix_/',$this->db->dbprefix,$fk));
				}
			}

            $this->dbforge->create_table($tableName);
        }

        // Create the Permissions.
		//code here

        // Assign them to the permitted roles.
		//code here

    }

    /**
     * Remove the blog tables
     *
     * @return void
     */
    public function down()
    {
		//code here
    }
}
juanmatias

Post Reply