RCOM: How to integrate the auto completion piece into your PHP project

Why?

One of the problems that arises from using Stored Procedures, is that the logic in them is hidden from the developer, unlike library functions and objects in his language of choice. In your IDE, all you have to do to see what a function does, is press on that function, and the IDE will take you to the stub of the function or even to the actual function code.
Wouldn’t it be super nice if this could be accomplished for stored procedures in your database?
This is what the Auto Completion Piece Into Php (or ACPIP) feature does.

How?

Generating the auto completion files is very easy.

• cd to the bin folder of Rahl Commander
• run python3.4 ./autocomplete.py --all
• The output is in [assets folder]/autocompletion/php/SP.php
• Set your project’s include path in your IDE to include this file

NOTICE! For now, only PHP is supported out of the box.
Your IDE now recognizes SP as class, where the public methods in this class are the names of the Stored Procedurs in your database.
Obviously, that is some help, but it does not make your life much easier.
Below, I’ll show how to tweak this process to work seamlessly in your project.

What is this code

The output of the autocomplete.py command is the file below. This file is specific for the Dahara demo project.

<?php
/**
 * Autocompletion stub
 * You call (dbname)_(stored_procedure_name)
 */
class SP{
        /**
         * @return \SP
         */
        static function call(){
            return new self;
        }


	/**
         * Database: dhara
         * clean_palace_from_dead_soldiers 
         * File: [ASSETS FOLDER] sp/dhara/palace/soldiers/clean_palace_from_dead_soldiers.sql
         *
         * @param integer $in_member_id  :IN in_member_id INT
         *
         * @return \SP
         */
	public function dhara__clean_palace_from_dead_soldiers ($in_member_id){    		
        /*
	  DELETE FROM first_file_members
	  WHERE member_id = in_member_id;
         */
	}
}

As can be seen the Stored Procedure code is copied as a comment into a method baring the same name. The Stored Procedure in db [dhara] that is called [clean_palace_from_dead_soldiers] turned into

/**
         * Database: dhara
         * clean_palace_from_dead_soldiers 
         * File: [ASSETS FOLDER] sp/dhara/palace/soldiers/clean_palace_from_dead_soldiers.sql
         *
         * @param integer $in_member_id  :IN in_member_id INT
         *
         * @return \SP
         */
	public function dhara__clean_palace_from_dead_soldiers ($in_member_id){    		
        /*
	  DELETE FROM first_file_members
	  WHERE member_id = in_member_id;
         */
	}

In the comments you can see from which DB this SP was copied, What is the original name of the SP, The parameters of SP and The file location of the SP.
The method name is always [db name]__[sp name].
The paramaters of the method are the parameters of the SP itself. The method itself returns nothing and the body is only a comment, containing the original SQL of the SP.

Now, if you will type in your IDE SP::call()->dhara__ it should autocomlete to dhara__clean_palace_from_dead_soldiers ($in_member_id). Of course, use your own SP names.

While all this is nice, you can’t really use this yet. There is some work to be done (one time, I promise).

Some code to make it all work

1. Create a method or function in your Database class (or anyware) that accepts a stored procedure name and an array of arguments and calls them.
   I am using for PHP the PDO extension to call a stored procedure. Vanilla it would look something like the following for a stored prodecure called do_something(IN a INT,IN b INT) with params 1 and 2.
   NOTICE! I do not apply any sanitation to avoid sql injection. That depends on your code and how you generate your SQLs.

   $db_client = new \PDO(....connection details....);
   //connect
   ...
   ...
   //connected
   $sql="call dbname.do_something(1,2)";
   $db_client->query($sql);
   //Fetch the data like in any SELECT and do what u want.
   

   To make this easier, I have made a function (or method) that can take db name, sp name and an array of arguments and do the call above.

   function call_sp(\PDO $db_client,string $db_name,string $sp_name,array $sp_args){
       $sql = "call {$db_name}.{$sp_name}('" . join("','",$sp_args) . "')";
       return $db_client->query($sql);
   }   
   

   WARNING! The code is not production ready, it does not sanitize. It’s only purpose is to display logic.
   Assuming the code above is my library and I use that in my system, or something similar, I now have to make a wrapper that will enable me to use the ACPIP (auto completion). For that, I can use the phpdoc syntax to trick my IDE to believe the SP class the ACPIP generates actually exists in the code base.

   
   /**
    * Wrapper for easy access to stored procedures.
    * This depends adding the auto completion file to the Eclipse language directory for core php
    *
    * @author itaymoav
    *
    */
   class Omega{
       /**
        * @var \PDO
        */
       static public $DB;
       
       /**
        * VERY IMPORTANT THE RETURN TYPE HERE!
        * @return \SP
        */
       static function SP():Omega{
           return new self;
       }
       
       /**
        * @param string $sp
        * @param array $args
        *
        * @return \PDOStatement
        */
       public function __call($sp,$args):\PDOStatement{
           $sp = explode('__',$sp);
           return call_sp(self::$DB,$sp[0],$sp[1],$args);
       }
   }
   
   //in your bootstrap (There are many ways to write this wrapper)
   Omega::$DB = $db_client;//an instantiated \PDO object
   
   //and now anyware else, u can do 
   Omega::SP()->sp_name_from_ACPIP($a,$b);
   //Or, to use the SP name from the example above.
   Omega::SP->dbname__do_something(1,2);
   
   //Or, from the Dhara example:
   Omega::sp()->dhara__clean_palace_from_dead_soldiers(42);
   

   QUESTIONS? FEEDBACK? All are welcome, post as a comment.

   NEXT: Tutorial on the Schema Checker rule syntax and usage.

   FIN

   Advertisement

   Share this:

   • Twitter
   • Facebook

   Like this:

   Like Loading...

   Related