Insert into join tables through a view

Solution to insert in two linked tables through one request

I found a solution to insert data in two joined tables in simple FK situation through a transaction with one request on php-crud-api but this required me to do some changes to the GenericDB and RecordController classes.

Limitations

Here are the limitations I know of:

1. This relies on the creation of a simple view where the two tables are joined in a simple syntax.
2. The two tables can't have columns with the same name except for their ids.
3. This probably only works for MySQL/MariaDB.
4. It requires the two tables to be in the same database.

Drawbacks of separate requests to insert in linked tables

I needed such a solution because creating records in two linked tables through two requests leads to the following drawbacks IMHO:

1. I believe this is slower and consumes more resources.
2. It gives more risks for orphan records.
3. It gives more opportunities for hackers to create a mess in the two separate tables.

Here are the changes I made to php-crud-api for the solution to work

In RecordController.create(), I had to replace:

            if ($this->service->getType($table) != 'table') {
                return $this->responder->error(ErrorCode::OPERATION_NOT_SUPPORTED, __FUNCTION__);
            }

By this:

            if ($this->service->getType($table) != 'table'&&$this->service->getType($table) != 'view') {
                return $this->responder->error(ErrorCode::OPERATION_NOT_SUPPORTED, __FUNCTION__);
            }

I added those two functions to the GenericDB class:

        public function insertInView(ReflectedTable $table, array $columnValues)
        {
            $sql="select `information_schema`.`VIEWS`.`VIEW_DEFINITION` from `INFORMATION_SCHEMA`.`VIEWS` where table_name = '".$table->getName()."';";
            $stmt = $this->query($sql, []);
            $viewSql=$stmt->fetchColumn(0);
            $matches = array();
            $tablesAndCols=array();
            if(preg_match('/(?<=select )(.*)(?= from )/i', $viewSql, $matches)) {
                $selstr=$matches[0];
                $matches=array();
                if (preg_match_all('/(?<=^|,)([^,]*)(?= as)/i', $selstr, $matches)) {
                    foreach($matches[0] as $m){
                        $tempMatches=array();
                        if(preg_match('/(?<=^|.)([^.]*).([^.]*)(?=$)/i',$m,$tempMatches)){
                            $tab=str_replace("`","",$tempMatches[1]);
                            $col=str_replace("`","",$tempMatches[2]);
                            $tablesAndCols[$tab][]=$col;
                        }
                    }
                }
            }
            if(count($tablesAndCols)!=2){
                return(false);
            }
            $sql="select table_name,column_name,referenced_table_name,referenced_column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where (lower(referenced_table_name) = lower('".array_keys($tablesAndCols)[0]."') and lower(table_name) = lower('".array_keys($tablesAndCols)[1]."')) or (lower(referenced_table_name) = lower('".array_keys($tablesAndCols)[1]."') and lower(table_name) = lower('".array_keys($tablesAndCols)[0]."'))";
            $stmt = $this->query($sql, []);
            $foreignKeyData=$stmt->fetch();
            $primaryTable=$foreignKeyData["referenced_table_name"];
            $foreignTable=$foreignKeyData["table_name"];
            $foreignColumn=$foreignKeyData["column_name"];
            $insertPrimarySql=$this->getInsertSql($primaryTable,$tablesAndCols,$columnValues,$table);
            $insertForeignSql=$this->getInsertSql($foreignTable,$tablesAndCols,$columnValues,$table,$foreignColumn);
            try{
                if($this->pdo->beginTransaction()){
                    $stmt = $this->query($insertPrimarySql, []);
                    $stmt->fetch();
                    $stmt = $this->query($insertForeignSql, []);
                    $stmt->fetch();
                    $this->pdo->commit();
                }
                else{
                    return(false);
                }
            }catch (Exception $e) {
                $result=$this->pdo->rollBack();
                return(false);
            }
            return(true);
        }
        
        public function getInsertSql($tableName,$tablesAndCols,$columnValues,$table,$foreignTableColumn="")
        {
            $stringTypes=["varchar","char","binary","varbinary","blob","text","enum","set"];
            $insertSql="insert into ".$tableName." (";
            $insertSqlTail=") values (";
            if(strlen($foreignTableColumn)>0){
                $insertSql.=$foreignTableColumn;
                $insertSqlTail.="LAST_INSERT_ID()";
                if(count($tablesAndCols[$tableName])>0){
                    $insertSql.=",";
                    $insertSqlTail.=",";
                }
            }
            $i=0;
            foreach($tablesAndCols[$tableName] as $colName){
                $i++;
                $insertSql.=" ".$colName;
                $insertSqlTail.=" ";
                if(array_key_exists($colName,$columnValues)){
                    $col = $table->getColumn($colName);
                    $colType=$col->getType();
                    $quote=false;
                    if(array_search(strtolower($colType),$stringTypes)!==false){
                        $quote=true;
                    }
                    $insertSqlTail.=($quote?"'":"").$columnValues[$colName].($quote?"'":"");
                }
                else{
                    $insertSqlTail.="null";
                }
                if($i<count($tablesAndCols[$tableName])){
                    $insertSql.=",";
                    $insertSqlTail.=",";
                }
            }
            $insertSql.=$insertSqlTail.")";
            return($insertSql);
        }

I changed the GenericDB.createSingle() function into this:

        public function createSingle(ReflectedTable $table, array $columnValues) /*: ?String*/
        {
            $created=false;
            if($table->getType() == 'view'){
                $created=$this->insertInView($table,$columnValues);
            }
            if(!$created){
                $this->converter->convertColumnValues($table, $columnValues);
                $insertColumns = $this->columns->getInsert($table, $columnValues);
                $tableName = $table->getName();
                if(!is_null($table->getPk()))
                    $pkName = $table->getPk()->getName();
                $parameters = array_values($columnValues);
                $sql = 'INSERT INTO "' . $tableName . '" ' . $insertColumns;
                $stmt = $this->query($sql, $parameters);
                // return primary key value if specified in the input
                if (!is_null($table->getPk())&&isset($columnValues[$pkName])) {
                    return $columnValues[$pkName];
                }
            }
            // work around missing "returning" or "output" in mysql
            switch ($this->driver) {
                case 'mysql':
                    $stmt = $this->query('SELECT LAST_INSERT_ID()', []);
                    break;
            }
            $pkValue = $stmt->fetchColumn(0);
            if ($this->driver == 'sqlsrv' && $table->getPk()->getType() == 'bigint') {
                return (int) $pkValue;
            }
            return $pkValue;
        }

Here is an example of how this works

I have tables A and B with view VW_CRUD_A_B as:

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `a_name` varchar(20) NOT NULL
);

CREATE TABLE `b` (
  `id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `b_name` varchar(20) NOT NULL
);

CREATE VIEW vw_crud_a_b AS select `a`.`a_name`,`b`.`b_name` from (`a` join `b` on(`b`.`a_id` = `a`.`id`)) ;

ALTER TABLE `a`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `b`
  ADD PRIMARY KEY (`id`),
  ADD KEY `b_a_id` (`a_id`);

ALTER TABLE `a`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `b`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `b`
  ADD CONSTRAINT `b_a_id` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

When I POST on /records/vw_crud_a_b with the following, I get the records shown below in the database:

{
  "a_name":"name for a",
  "b_name":"name for b"
}

TABLE A:
ID: 1
A_NAME: name for a

TABLE B:
ID: 1
A_ID: 1
B_NAME: name for b

While I realize that there are many limitations and there are probably better ways to do the same thing, this does the job for me so far. The main risk for me using this solution is that php-crud-api will evolve and the changes I made may not work anymore.

Do you think there could be a way for me to improve this solution enough to have something similar implemented into php-crud-api ?

What would you recommend?

Thank you so much for php-crud-api , this is really useful to me!