Provide a restore method

[sergiokessler]

Provide a restore method, please?

[liamka]

What this method can do?

[sergiokessler]

something like this: (minus the bugs people will surely spot given enough eyeballs)

$db = new PDO($db_dsn, $db_user, $db_pass, $db_options);

// Temporary variable, used to store current query
$templine = '';
$handle = fopen($dump_file , 'r');
if ($handle) {
    while (!feof($handle)) { // Loop through each line
        $line = trim(fgets($handle));
        // Skip it if it's a comment
        if (substr($line, 0, 2) == '--' || $line == '') {
            continue;
        }

        // Skip it if it's a DEFINER
        // if (strpos($line, 'DEFINER') !== false) {
        //     $line = '';
        // }

        // Add this line to the current segment
        $templine .= $line;

        // If it has a semicolon at the end, it's the end of the query
        if (substr(trim($line), -1, 1) == ';') {
            // Perform the query
            $db->query($templine);
            // Reset temp variable to empty
            $templine = '';
        }
    }
    fclose($handle);
}

[phpony]

https://github.com/ifsnop/mysqldump-php/wiki/Importing-dump-from-php

[sergiokessler]

yup, add that code to the class as a restore method

[ifsnop]

The example uses mysqli, if you transform it to use PDO I could certainly include it.

yup, add that to the class as a restore method

[sergiokessler]

The example uses mysqli, if you transform it to use PDO I could certainly

my posted code (see 4 comments above) uses PDO...

(and I'm using that code in production, but I think it belongs to this class)

[phpony]

To be honest, I'm not a fan of all the examples provided, because:

1. There's no memory control. Whole query is being loaded into memory, even if it's a huge comma separated 300mb table values dump.
2. There's no error control. What happens if 1234th query returns execution error (for example, table already exists, or have duplicate keys)?
3. It's slow. For tiny databases this can be OK, but on large scale this approach can't compete with simple streaming dump file to mysql.

If we talk about proper importing function built into mysqldump-php, we should not go with a simple slow solution, it sould be a solid mysqlimport competitor. Otherwise it's not worth it.

[sergiokessler]

Hi Volkov,

1. No. The file is opened and read line by line.
2. Feel free to post an enhanced version, but today people are using this code (this is one of the advantages of having this code in the library, there are more people reviewing it and able to post patches)
3. No one is forcing you to use it, you can choose any other method, don't worry.

I think it should start simple, and growing from there...

[ifsnop]

I merged the proposed solution, since not everyone has mysql client to be able to import in a shared hosting.

[ve3]

https://github.com/ifsnop/mysqldump-php/wiki/Importing-dump-from-php

The code in this link will be error and not woking IF...
There is dump statement like this...

-- other normal tables dump here
-- Dumped table `rdb_posts` with 14 row(s)
--

--
-- Dumping routines for database 'dev_rdb_premium'
--

/*!50003 DROP FUNCTION IF EXISTS `udf_FirstNumberPos` */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!40101 SET character_set_client = utf8mb4 */;
/*!40101 SET character_set_results = utf8mb4 */;
/*!50003 SET collation_connection  = utf8mb4_unicode_520_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `udf_FirstNumberPos`(`instring` varchar(4000)) RETURNS int(11)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE position int;
    DECLARE tmp_position int;
    SET position = 5000;
    SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF; 
    SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;

    IF (position = 5000) THEN RETURN 0; END IF;
    RETURN position;
END ;;
DELIMITER ;

The other normal tables dump will work but routines does not work.

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER' at line 1

[ve3]

My improved import sql that can be use with DELIMITER.
Original source code: https://github.com/ifsnop/mysqldump-php/wiki/Importing-dump-from-php

$defaultDelimiter = ';';
$delimiter = $defaultDelimiter;

// Temporary variable, used to store current query
$templine = '';
$executedSql = 0;

while (!feof($handle)) {
    $line = fgets($handle);// DO NOT trim here because it will make line end with LF mixed with next line and work incorrectly. This is due to exporter mixed CRLF with LF on Windows.
    $line = str_replace(["\r\n", "\r", "\n", PHP_EOL], "\n", $line);

    // skip for comments or empty line.
    if (mb_substr($line, 0, 2) == '--' || trim($line) == '') {
        continue;
    }

    if (stripos($line, 'DELIMITER ') !== 0) {
        // if not start with set the delimiter. We will not execute `DELIMITER` command because it will be throwing the errors even it is write correctly.
        // Add this line to the current segment
        $templine .= $line;
    }// endif; delimiter.

    if (preg_match('/^DELIMITER\s+(?<delimiter>.+)$/mi', $line, $matches)) {
        // if found `DELIMITER` statement.
        // change delimiter to this.
        $delimiter = $matches['delimiter'];
    }

    // If it has a specific delimiter at the end, it's the end of the query
    if (mb_substr(trim($line), -1, 1) == $delimiter) {
        // Perform the query. I hope you can change to one you use.
        $STh = $PDO->prepare($templine);
        $executed = $STh->execute();
        // end perform the query.
        if ($executed === true) {
            $executedSql++;
        }
        unset($executed, $Sth);

        // Reset temp variable to empty
        $templine = '';
    }
}// endwhile;