dolphin/imdb-dataset-to-mysql/lib/import.abstract.class.php

<?php
/**
 * dolphin. Collection of useful PHP skeletons.
 * Copyright (C) 2013-2020  Johannes 'Banana' Keßler
 *
 * https://www.bananas-playground.net
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the COMMON DEVELOPMENT AND DISTRIBUTION LICENSE
 *
 * You should have received a copy of the
 * COMMON DEVELOPMENT AND DISTRIBUTION LICENSE (CDDL) Version 1.0
 * along with this program.  If not, see http://www.sun.com/cddl/cddl.html
 */

/**
 * base class with needed methods to make an import
 * Class TSVImport
 */
abstract class TSVImport {

    /**
     * @var $_DB object database
     */
    protected $_DB;

    /**
     * @var $_db_table_name string Tablename
     */
    protected $_db_table_name;

    /**
     * @var $_db_table_crate_str string Creation SQL for this table
     */
    protected $_db_table_crate_str;

    /**
     * @var bool Create fulltext index or not
     */
    protected $_createFulltext = BUILD_INDEX;

    /**
     * @var array Queries to be run after the import
     */
    protected $_db_table_after_import_query = array();

    /**
     * TSVImport constructor.
     *
     * @param $db Mysqli database object
     */
    public function __construct($db) {
        $this->_DB = $db;
        $this->setup();
    }

    /**
     * Set the table name into $_db_table_name
     * Set the CREATE TABLE query into $_db_table_crate_str with the use
     * of $_db_table_name
     *
     * set $_db_table_name $_db_table_crate_str
     * @return void
     */
    abstract public function setup();

    /**
     * Creates the values port of the insert query
     * INSERT INTO ... VALUES (1,2,3),(1,2,3),(1,2,3)
     * Where (1,2,3) is the result of this function
     *
     * @param $data array of the tsv line to build the ()
     *        for the values insert query
     * @return string
     */
    abstract public function queryValuePart($data);

    /**
     * @param $file The TSV file to import
     * @return bool
     */
    public function import($file) {
        $ret = false;

        echo "Starting to import $file with ".get_class($this)." class\n";

        $check = $this->_checkTable();
        if($check == false) {
            echo "Creating needed database table: $this->_db_table_name \n";
            $this->_createTable();
        }
        else {
            echo "Database table already exists: $this->_db_table_name \n";
        }

        if(!empty($file)) {
            if (($handle = fopen($file, "r")) !== FALSE) {
                // skip first line as it should be column names
                // the length of the first line should be not so long as the others.
                fgetcsv($handle, 4000, "\t");

                $linesInFile = $this->_linesInFile($file);

                $queryStrStart = "INSERT IGNORE INTO `".$this->_db_table_name."` VALUES ";
                $queryStr = '';
                $total=0;
                // some files have very long lines... otherwise a length value would be perfect
                while (($data = fgetcsv($handle, 0, "\t")) !== FALSE) {
                    // invalid lines.
                    $_p = $this->queryValuePart($data);
                    if(!empty($_p)) {
                        $queryStr .= $this->queryValuePart($data).",";
                        $total++;

                        if(isset($queryStr[1000000])) {
                            try {
                                $this->_DB->query($queryStrStart . trim($queryStr, ","));
                                $queryStr = '';
                            } catch(Exception $e) {
                                echo "Failure in executing the query. ".$e->getMessage()."\n";
                                var_dump($queryStr);
                                exit();
                                return false;
                            }
                        }

                        echo "Inserting: $total/$linesInFile\r";
                    }
                }
                if(!empty($queryStr)) {
                    $this->_DB->query($queryStrStart.trim($queryStr,","));
                    echo "\n";
                }

                fclose($handle);
                echo "Import complete. Inserted $total rows\n";
                if(!empty($this->_db_table_after_import_query)) {
                    echo "Executing after import stuff\n";
                    foreach ($this->_db_table_after_import_query as $k=>$v) {
                        echo " Running $v\n";
                        $this->_DB->query($v);
                        echo " Done\n";
                    }
                    echo "Done\n";
                }
                $ret = true;
            }
        } else {
            echo "Filename empty\n";
        }

        return $ret;
    }

    /**
     * check if needed DB Table is already there.
     * Otherwise create one.
     *
     * @return bool
     */
    protected function _checkTable() {
        $ret = false;

        $queryStr = "SELECT count(*) AS amount
            FROM information_schema.TABLES
            WHERE (TABLE_SCHEMA = '".DB_NAME."') AND (TABLE_NAME = '".$this->_db_table_name."')";
        try {
            $query = $this->_DB->query($queryStr);
            $result = $query->fetch_assoc();
            if(!empty($result['amount'])) {
                $ret = true;
            }
        }
        catch (Exception $e) {
            echo $e->getMessage();
        }

        return $ret;
    }

    /**
     * Creates the needed table für this import
     * If there are any changes to the table you need to
     * alter the insert queries too
     *
     * @return bool
     */
    protected function _createTable() {
        $ret = false;

        try {
            $query = $this->_DB->query($this->_db_table_crate_str);
            if($query) {
                $ret = true;
            }
        }
        catch (Exception $e) {
            echo $e->getMessage();
        }

        return $ret;
    }

    /**
     * Count the file lines.
     * Used for user info
     *
     * @param $file
     * @return int
     */
    protected function _linesInFile($file) {
        $file = new \SplFileObject($file, 'r');
        $file->seek(PHP_INT_MAX);
        return $file->key();
    }

}