Zend script to create tables on demand

When you are working on a large project like a CMS system, you provide different kind of modules and you allow the end user to enable or disable certain modules depending on his needs.

There are different way of achieving this :

  • You create the whole database with all tables in it, giving the possibility to only enable activity on these tables
  • You create a simple database with the bare minimum tables and each time the user enables a new module, you create the tables accordingly (we will talk about dependencies later)

In this tutorial we will cover the later option and we are using ZF3 along with Doctrine 2.

Configuring the Zend 3 module

To make things as clear as possible, we will create a simple module with 1 Entity (one table), one service which takes care of the table creation and one controller and its view.

create table on fly with zend 3

We are using an existing application for this project, so we will detail only the module with its sub directories starting with the configuration files.

Following is our main module structure :

MyApp/
    config
    src
        Controller
            Factory
        Entity
        Fixtures
        Form
        Service
            Factory
    view
        my-app

Now let’s fill the content of our module.config.php configuration file :

<?php namespace MyApp; 

use Doctrine\ORM\Mapping\Driver\AnnotationDriver; 
use Zend\Router\Http\Segment; 

return [ 'controllers' => [
		'factories' => [
						Controller\IndexController::class =>
						Controller\Factory\IndexControllerFactory::class,
				],
		],
		
		'router' => [
				'routes' => [
						'myapp' => [
								'type'    => Segment::class,
								'options' => [
										'route'    => '/myapp[/:action[/:id]]',
										'constraints' => [
												'action' => '[a-zA-Z][a-zA-Z0-9_-]*',
												'id' => '[a-zA-Z0-9_-]*',
										],
										'defaults' => [
												'controller'    => Controller\IndexController::class,
												'action'        => 'index',
										],
								],
						],
						
				],
		],
		// The 'access_filter' key is used by the MyApp module to restrict or permit
		// access to certain controller actions for unauthorized visitors.
		'access_filter' => [
				'controllers' => [
						Controller\IndexController::class => [
								// Give access to actions to authorized users only.
								['actions' => ['index','enable'],
										'allow' => '@']
						],
						
				]
		],
		'view_manager' => [
				'template_path_stack' => [
						'myapp' => __DIR__ . '/../view',
				],
		],
		
		'doctrine' => [
				'driver' => [
						__NAMESPACE__ . '_driver' => [
								'class' => AnnotationDriver::class,
								'cache' => 'array',
								'paths' => [__DIR__ . '/../src/Entity']
						],
						'orm_default' => [
								'drivers' => [
										__NAMESPACE__ . '\Entity' => __NAMESPACE__ . '_driver'
								]
						]
				]
		] ,
		
		'service_manager' => [
				'factories' => [
						Service\EnableManager::class => Service\Factory\EnableManagerFactory::class
						
				],
		],

];

?>

Not a lot comment to be added here, we know what we need to do and which service and action we plan. We only fill in details about the right spots to look for by Zend engine.

As you can see we use the index controller and trigger the Enable action within it to handle our required function.

We also require the user to be logged in in order to achieve the task (look at the @ in the access filters). For more information about this, have a look to our authentication tutorial with zend.

Now the Module.php file :

<?php
namespace MyApp;

class Module {
	
	
	public function getConfig() {
		
		return include __DIR__ . '/../config/module.config.php';
		
	}
	
	
}

Our model for the module

Now that we are ready for coding as everything is prepared, we need to build an example of an entity to deal with table entries.

We plan on integrating the table on the fly within a service manager but we will need to deal with the table. Let’s tell Zend how to deal with that table and use Doctrine annotations to provide the details about it.

Example of a table :

CREATE TABLE `example_table` (
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `example_title` varchar(100) NOT NULL,
  `example_description` varchar(250) NOT NULL,

   UNIQUE(example_title)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For the sake of this article, we keep things simple so there are only 3 fields including the primary key for this table.

There is no need to create the table, we will create a service for that.

The entity

Following is our example entity :

<?php namespace MyApp\Entity; 

use Doctrine\ORM\Mapping as ORM; 

/** * This class represents a single application 
 * @ORM\Entity 
 * @ORM\Table(name="example_table") 
 **/ 
 
class Example 
{ 
    /** * @ORM\Id 
     * @ORM\GeneratedValue 
     * @ORM\Column(name="id",type="int") 
     * */ 
     
    protected $id; 
     
     /** 
      * @ORM\Column(name="example_title") 
      * */ 

    protected $title; 


    /** 
    * @ORM\Column(name="example_description") 
    */ 
    protected $description; 

    // Returns ID of this example. 

    public function getId() 
    {
        return $this->id;
    }
	
	// Returns title
	public function getTitle()
	{
		return $this->title;
	}
	
	// Sets title of example
	public function setTitle($title)
	{
		$this->title = $title;
	}
	

	// Returns description
	public function getDescription()
	{
		return $this->description;
	}
	
	// Sets description
	public function setDescription($description)
	{
		$this->description = $description;
	}
	
}

You can see the getters and setters for this entity, not a big deal.

The service manager

Now we are talking !

If you remember the tutorial implementing the user demo project from Github, we were using migrations to update the tables in our base. In our script we will just use the same type of code.

See for example the following file :

<?php
namespace MyApp\Service;

/**
 * This service is responsible for checking Account adding a new Table when needed
 */

class ExampleManager
{
	/**
	 * Doctrine entity manager.
	 * @var Doctrine\ORM\EntityManager
	 */
	private $entityManager;
	
	/**
	 * Connection manager.
	 * @var Doctrine\ORM\ConnectionManager
	 */
	private $connectionManager;
	
	/**
	 * Constructs the service.
	 */
	public function __construct($entityManager,$connectionManager)
	{
		$this->entityManager = $entityManager;
		
		$this->connectionManager = $connectionManager;
	}	
	/*
	 * Create the NEW Example Table -- example_table
	 */
	public function createExampleTable()
	{
		$tbname = 'example_table';
		
		$sm = $this->connectionManager->getSchemaManager();

		if ($sm->tablesExist($tbname)) {
			
			return 1;
	
		} else {
		
			// Create 'example_table' table
			$table =  $table = new \Doctrine\DBAL\Schema\Table($tbname);
			$table->addColumn('id', 'integer', ['autoincrement'=>true]);
			$table->addColumn('example_title', "string", array("length" => 120,'notnull' => true));
			$table->addColumn('example_description', "string", array("length" => 255 , 'notnull' => true));
		
			$table->setPrimaryKey(['id']);
			$table->addUniqueIndex(['example_title'],'example_title_index',[]);
			$table->addOption('engine' , 'InnoDB');
	
			$sm->createTable($table);
			
			return 1;
		}
	
	}
}

Its Factory which creates the service.

<?php
namespace MyApp\Service\Factory;

use Interop\Container\ContainerInterface;
use MyApp\Service\ExampleManager;

/**
 * This is the factory class for ExampleManager service. The purpose of the factory
 * is to instantiate the service and pass it dependencies (inject dependencies).
 */
class ExampleManagerFactory
{
	/**
	 * This method creates the AccountManager service and returns its instance.
	 */
	public function __invoke(ContainerInterface $container, $requestedName, array $options = null)
	{
		$entityManager = $container->get('doctrine.entitymanager.orm_default');
		
        // Add connection details to the manager - from config data
		$connectionManager = $container->get('doctrine.connection.orm_example');
		
		
		return new ExampleManager($entityManager,$connectionManager);
	}
}

Note the doctrine.connection.orm_example parameter which is taken from our project local.php configuration file as follow :


    'connection' => [
            // adding other database for applications
            'orm_example' => [
                    'driverClass' => PDOMySqlDriver::class,
                    'params' => [
                            'host'     => '127.0.0.1',
                            'user'     => 'test',
                            'password' => 'mypassword',
                            'dbname'   => 'dbname'
                    ]
            ],
    ]

 

The controller

Your application does not know yet what action needs to be taken, so let’s add the right function under the enable action method with a new controller file as defined on our module.config.php file :

<?php
namespace MyApp\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;

use MyApp\Entity\Account;

class IndexController extends AbstractActionController
{
	/**
	 * Entity manager.
	 * @var Doctrine\ORM\EntityManager
	 */
	private $entityManager;
	
	/**
	 * Connection manager.
	 * @var Doctrine\ORM\ConnectionManager
	 */
	private $connectionManager;
	
	
	/**
	 * Example manager.
	 * @var MyChart\Service\ExampleManager
	 */
	private $exampleManager;
	
	
	/**
	 * Constructor.
	 */
	public function __construct($entityManager,$exampleManager,$connectionManager)
	{
		$this->entityManager = $entityManager;
		$this->exampleManager = $exampleManager;
		$this->connectionManager = $connectionManager;
		
	}
	
	/**
	 * This is the default "index" action of the controller. It displays the
	 * list of table entries
	 */
	public function indexAction()
	{
		/* Make sure you have a table before we list anything */
		$result = $this->exampleManager->createExampleTable();
		
		if (!$result) {
			
			throw new \Exception('Sorry, a problem with the table for this module');
			
		}
		
		/* retrieve accounts listing */
		$examples = $this->entityManager->getRepository(Example::class)
		->findBy([], ['example_title' => 'ASC']);
		
		/* Return to the view the listing of examples */
		return new ViewModel([
				'example' => $examples
		]);
	}
	
	/*
	 * ENABLE the module by creating the table 
	 */
	public function enableAction()
	{
		$result = $this->exampleManager->createExampleTable();
		
		/* Redirect to wherever you need or display something */

		return $this->redirect()->toRoute('myapp',
				['action'=>'index']);
		
	}
	

}

As you notice reading the above code, we define 2 actions :

  1. The default action which lists the table entries
  2. The enable action which create the table so we can add entries into it

The later action returns true when the table is already created. You may add additional error/exception codes to match your own requirements.

It’s time to add the factory of our controller :

<?php
namespace MyApp\Controller\Factory;

use Interop\Container\ContainerInterface;
use Zend\ServiceManager\Factory\FactoryInterface;
use MyApp\Controller\IndexController;
use MyApp\Service\ExampleManager;

/**
 * This is the factory for IndexController. Its purpose is to instantiate the
 * controller.
 */
class IndexControllerFactory implements FactoryInterface
{
	public function __invoke(ContainerInterface $container, $requestedName, array $options = null)
	{
		$entityManager = $container->get('doctrine.entitymanager.orm_default');
		$exampleManager   = $container->get(ExampleManager::class);
		
		
		$connectionManager = $container->get('doctrine.connection.orm_example');		
		
		// Instantiate the controller and inject dependencies
		return new IndexController($entityManager,$exampleManager,$connectionManager);
	}
}

The View of our module

In this tutorial, the view isn’t important as the enable action can redirect to another view. But for instance you may list the table entries using the following code :


<?php $this->headTitle('Table entries');
?>


<h2>Table entries</h2>



<table class="table table-striped">

    <?php foreach ($example as $ex): ?>
    
	    
<tr>
	    	
<td><?= $this->escapeHtml($acc->getTitle()); ?></td>

	        
<td><?= $this->escapeHtml($acc->getDescription()); ?></td>

	       </td>

	    </tr>

        
    <?php endforeach; ?>   
    
</table>


That’s all for this tutorial, I hope you learnt something here and I’ll be glad if you drop a comment to add your suggestions and other things.

We will shortly add an article on switching of database on the fly using Zend when you know the name beforehand…

 

For other interesting resources you may check :

http://zf2.readthedocs.io/en/latest/tutorials/config.advanced.html

https://framework.zend.com/manual/2.4/en/modules/zend.module-manager.module-manager.html#zend-module-manager-module-manager-module-manager-listeners

Leave a Reply

Want more information?

Related links will be displayed here in this section for you to pick up another good spot to get more details about Web marketing and Search Engine Optimization. There will be some sites which we selected to ease the work of any webmaster or/and web marketer on the Internet.

%d bloggers like this: