123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217 |
- <?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();
- }
- }
|