* Copyright (c) 2007 Platon Group, http://platon.sk/ * Licensed under terms of GNU General Public License. * All rights reserved. * * Changelog: * 2007-03-09 - created * 2017-09-12 - implemented new SK-NIC-EPP/CentralNic export * 2017-10-07 - added import of expired domains (droplist) * 2023-12-23 - use constructor debug option on multiple places */ /* $Platon: scripts/php/sk-nic/SK-NIC.php,v 1.25 2023/12/23 22:04:13 nepto Exp $ */ require_once 'DB.php'; class SK_NIC { var $input_dt_format_sql = '%d.%m.%Y'; var $_debug = null; var $_dsn = null; var $_db = null; public function __construct($dsn, $debug = false) // {{{ { $this->_dsn = $dsn; $this->_debug = intval($debug); $pear_db_opts = array('debug' => ($debug ? 10 : 0)); $this->_db = DB::connect($this->_dsn, $pear_db_opts); if (DB::isError($this->_db)) { $this->error($this->_db); } $this->_db->setFetchMode(DB_FETCHMODE_ASSOC); mysqli_options($this->_db->connection, MYSQLI_OPT_LOCAL_INFILE, 1); $res = $this->_db->query('SET NAMES UTF8'); if (DB::isError($res)) { $this->error($res); } $res = $this->_db->query('SET COLLATION_CONNECTION=UTF8_GENERAL_CI'); if (DB::isError($res)) { $this->error($res); } } // }}} function SK_NIC($dsn, $debug = false) // {{{ { self::__construct(); } // }}} function _SK_NIC() // {{{ DESTRUCTOR { $this->_db->disconnect(); } // }}} function setContext($context) /* {{{ */ { switch ($context) { case 'domain': $this->context = 'domain'; $this->primary_key = 'domain'; $this->table_current = 'sknic_domains'; $this->table_import = 'sknic_domains_import'; $this->table_history = 'sknic_domains_history'; $this->pk_header_del = 'domena'; break; case 'droplist': $this->context = 'droplist'; $this->primary_key = 'domain'; $this->table_current = 'sknic_droplist'; $this->table_import = 'sknic_droplist_import'; $this->table_history = 'sknic_droplist_history'; $this->pk_header_del = 'Domain_Name'; break; case 'registrar': $this->context = 'registrar'; $this->primary_key = 'id_reg'; $this->table_current = 'sknic_registrars'; $this->table_import = 'sknic_registrars_import'; $this->table_history = 'sknic_registrars_history'; $this->pk_header_del = 'Reg ID'; break; default: $this->error(sprintf("%s() ERROR: unknown context: %s", __FUNCTION__, $context)); } } /* }}} */ function setContextFromFilename($filename) /* {{{ */ { $basefile = basename($filename); if (preg_match('/^domain/', $basefile)) { return $this->setContext('domain'); } elseif (preg_match('/^droplist/', $basefile)) { return $this->setContext('droplist'); } elseif (preg_match('/^registrar/', $basefile)) { return $this->setContext('registrar'); } $this->error(sprintf("%s() ERROR: unable to detect context from file: %s", __FUNCTION__, $basefile)); } /* }}} */ function _tableColumns($table_name) /* {{{ */ { $table_info = $this->_db->tableInfo($table_name); $ar = array(); foreach ($table_info as $val) { $ar[] = $val['name']; } return $ar; } /* }}} */ function error($err_obj) // {{{ { if (is_object($err_obj)) { $error_string = $err_obj->getMessage(); if (! empty($this->_debug)) { $error_string .= "\n"; $error_string .= $err_obj->getDebugInfo(); } } else { $error_string = $err_obj; } fprintf(STDERR, "\n%s FATAL ERROR: %s\n", basename(__FILE__), $error_string); exit(1); } // }}} function _getChangedDateFromFilename($filename) /* {{{ */ { $changed_date = date('Y-m-d'); if (file_exists($filename) && is_readable($filename)) { $changed_date = preg_replace('/^.*(\d\d\d\d-\d\d-\d\d).*/', '\1', $filename); if ($changed_date == $filename) { $changed_date = date('Y-m-d', filemtime($filename)); } } return $changed_date; } /* }}} */ function _getMaxChangedDate() /* {{{ */ { $query = 'SELECT MAX(dt_changed) FROM '.$this->table_current; $date = $this->_db->getOne($query); if (DB::isError($date)) { $this->error($date); } return $date; } /* }}} */ function doImport($filename, $changed_date = null) /* {{{ */ { $max_changed_date = $this->_getMaxChangedDate(); if ($changed_date == null) { $changed_date = $this->_getChangedDateFromFilename($filename); if ($this->_debug) { echo "INFO: max changed date in DB: $max_changed_date\n"; echo " changed date from filename: $changed_date\n"; } } else { if ($this->_debug) { echo "INFO: max changed date in DB: $max_changed_date\n"; echo " changed date as argument: $changed_date\n"; } } if (strncmp($changed_date, $max_changed_date, 10) <= 0) { return false; } $this->_cleanImport(); $this->_writeImportWithLoadData($filename, $changed_date); return $changed_date; } /* }}} */ function checkImport() /* {{{ */ { $query = 'SELECT COUNT(*) FROM '.$this->table_import; $count = $this->_db->getOne($query); if (DB::isError($count)) { $this->error($count); } // here droplist shares limits with registrars (1000/9900) $min_count = ($this->context == 'domain') ? 100000 : 1000; $max_count = ($this->context == 'domain') ? 900000 : 9900; if ($count < $min_count) { if ($this->_debug) { echo sprintf("%s() ERROR: too less imported %s records: %d (must be min %d)\n", __FUNCTION__, $this->context, $count, $min_count); } return false; } if ($count > $max_count) { if ($this->_debug) { echo sprintf("%s() ERROR: too much imported %s records: %d (must be max 600000)\n", __FUNCTION__, $this->context, $count, $max_count); } return false; } return true; } /* }}} */ function _cleanImport() /* {{{ */ { $result = $this->_db->query('TRUNCATE TABLE '.$this->table_import); if (DB::isError($result)) { $this->error($result); } } /* }}} */ function _importedSetStatusOK() /* {{{ */ { $query = 'UPDATE sknic_domains_import SET status="OK" WHERE status IS NULL'; $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } /* }}} */ function _importedNameserversReorder() /* {{{ */ { $result = $this->_db->query('SELECT domain,ns,ns1,ns2,ns3,ns4 FROM sknic_domains_import'); while ($row = $result->fetchRow()) { $domain = $row['domain']; $all_ns = $row['ns']; unset($row['domain']); unset($row['ns']); $ns_array = explode(',', $all_ns); usort($ns_array, array($this, '_sortingFunction')); usort($row, array($this, '_sortingFunction')); $ns_array = array_filter($ns_array, function($value) { return $value !== ''; }); $row = array_filter($row, function($value) { return $value !== ''; }); $query = sprintf('UPDATE sknic_domains_import' .' SET ns=%s, ns1=%s, ns2=%s, ns3=%s, ns4=%s' .' WHERE domain=%s', $this->_db->quote(implode(',', $ns_array)), $this->_db->quote($row[0]), $this->_db->quote($row[1]), $this->_db->quote($row[2]), $this->_db->quote($row[3]), $this->_db->quote($domain)); $result2 = $this->_db->query($query); if (DB::isError($result2)) { $this->error($result2); } } } /* }}} */ function _sortingFunction($a, $b) /* {{{ */ { /* Sorts by: * 1. length - shorter hostname first * 2. alphabeticaly * Properly sorted example: * - ns.platon.sk * - dns.platon.sk * - ns.platon.org * - dns.platon.org */ $a_len = strlen($a); $b_len = strlen($b); if ($a_len == $b_len) { return strcasecmp($a, $b); } if ($a_len == 0 || $b_len == 0) { return $b_len - $a_len; // flip order if we have some empty values } return $a_len - $b_len; } /* }}} */ function _writeImportWithInserts($filename, $changed_date) /* {{{ */ { // DELETEME 1Q/2018 if (($f = fopen($filename, 'r')) == false) { $this->error('unable to open: '.$filename); } while (($line = fgets($f)) != false) { $line = trim($line); if (strlen($line) < 2 || ! strncmp('--', $line, 2)) { continue; /* line skipped */ } @list($domain, $id_reg, $id_owner, $status, $ns1, $ns2, $ns3, $ns4, $dt_valid) = split(';', $line); if (! preg_match('|^.+\....?.?$|', $domain)) { echo 'skipping: ',$domain, "\n"; continue; } $dt_valid = strtotime($dt_valid); $dt_valid = date('Y-m-d', $dt_valid); $query = sprintf('INSERT INTO sknic_domains_import' .' (domain,status,id_reg,id_owner,ns1,ns2,ns3,ns4,dt_valid,dt_changed)' .' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', $this->_db->quote($domain), $this->_db->quote($status), $this->_db->quote($id_reg), $this->_db->quote($id_owner), $this->_db->quote($ns1), $this->_db->quote($ns2), $this->_db->quote($ns3), $this->_db->quote($ns4), $this->_db->quote($dt_valid), $this->_db->quote($changed_date)); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } fclose($f); } /* }}} */ function _writeImportWithLoadData($filename, $changed_date) /* {{{ */ { if ($this->context == 'domain') { /* SK-NIC changed format of domains.txt at Autumn/2023 $query = sprintf('LOAD DATA LOCAL INFILE %s' .' INTO TABLE %s' .' CHARACTER SET UTF8' .' FIELDS TERMINATED BY ";"' .' (domain,id_reg,id_owner,status,ns1,ns2,ns3,ns4,@dt_valid)' .' SET dt_valid = STR_TO_DATE(@dt_valid, %s), dt_changed = %s', $this->_db->quote($filename), $this->table_import, $this->_db->quote($this->input_dt_format_sql), $this->_db->quote($changed_date)); */ $query = sprintf('LOAD DATA LOCAL INFILE %s' .' INTO TABLE %s' .' CHARACTER SET UTF8' .' FIELDS TERMINATED BY ";"' .' IGNORE 8 LINES' .' (domain,id_reg,id_owner,@ns,@dt_valid)' .' SET dt_valid = STR_TO_DATE(@dt_valid, %s), dt_changed = %s,' .' ns = @ns,' .' ns1 = SUBSTRING_INDEX(SUBSTRING_INDEX(@ns, ",", 1), ",", -1),' .' ns2 = SUBSTRING_INDEX(SUBSTRING_INDEX(@ns, ",", 2), ",", -1),' .' ns3 = SUBSTRING_INDEX(SUBSTRING_INDEX(@ns, ",", 3), ",", -1),' .' ns4 = SUBSTRING_INDEX(SUBSTRING_INDEX(@ns, ",", 4), ",", -1)', $this->_db->quote($filename), $this->table_import, $this->_db->quote($this->input_dt_format_sql), $this->_db->quote($changed_date)); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } // fixing duplicate of NS $query = sprintf('UPDATE %s SET ns4 = NULL WHERE ns4 IN (ns1, ns2, ns3)', $this->table_import); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } $query = sprintf('UPDATE %s SET ns3 = NULL WHERE ns3 IN (ns1, ns2)', $this->table_import); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } $query = sprintf('UPDATE %s SET ns2 = NULL WHERE ns2 = ns1', $this->table_import); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } $query = sprintf('UPDATE %s SET ns1 = NULL WHERE LENGTH(ns1) <= 0', $this->table_import); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } elseif ($this->context == 'droplist') { $query = sprintf('LOAD DATA LOCAL INFILE %s' .' INTO TABLE %s' .' CHARACTER SET UTF8' .' FIELDS TERMINATED BY ","' // note different separator .' (domain,date_entered,date_leaves,date_purged)' .' SET dt_changed = %s', $this->_db->quote($filename), $this->table_import, $this->_db->quote($changed_date)); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } elseif ($this->context == 'registrar') { $this->_db->query('SET NAMES UTF8'); $query = sprintf('LOAD DATA LOCAL INFILE %s' .' INTO TABLE %s' .' CHARACTER SET UTF8' .' FIELDS TERMINATED BY ";"' .' (id_reg,name,street,city,phone,email)' .' SET dt_changed = %s', $this->_db->quote($filename), $this->table_import, $this->_db->quote($changed_date)); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } else { $this->error(sprintf("%s() ERROR: unknown context: %s", __FUNCTION__, $context)); } // Remove all lines started with "--" $query = sprintf('DELETE FROM %s WHERE %s LIKE "-- %%"', $this->table_import, $this->primary_key); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } // Remove table headers (we want just data) $query = sprintf('DELETE FROM %s WHERE %s = %s LIMIT 1', $this->table_import, $this->primary_key, $this->_db->quote($this->pk_header_del)); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } /* }}} */ function _writeHistory($rec, $deleted = 0) /* {{{ */ { static $dt_deleted = null; if ($dt_deleted == null) { $dt_deleted = $this->_db->getOne("SELECT DISTINCT dt_changed from $this->table_import"); if (strlen($dt_deleted) <= 0) { $dt_deleted = date('Y-m-d'); } } $history_cols = $this->_tableColumns($this->table_history); $cols = ''; $vals = ''; foreach ($history_cols as $col) { if (isset($rec["c_$col"])) { $cols .= "$col,"; $vals .= $this->_db->quote($rec["c_$col"]); $vals .= ','; } } $query = sprintf('INSERT INTO %s (%s dt_deleted) VALUES (%s %s)', $this->table_history, $cols, $vals, $deleted ? $this->_db->quote($dt_deleted) : 'NULL'); $this->_db->expectError(DB_ERROR_ALREADY_EXISTS); $result = $this->_db->query($query); $this->_db->popExpect(); if (DB::isError($result)) { if ($result->getCode() == DB_ERROR_ALREADY_EXISTS) { printf("%s() WARNING: %s entry already exists [%s:%s]\n", __FUNCTION__, $this->table_history, $this->primary_key, $rec['c_'.$this->primary_key]); } else { $this->error($result); } } } /* }}} */ function _updateRecord($rec, $changed_cols) /* {{{ */ { $update_str = ''; foreach ($changed_cols AS $col) { $update_str .= "$col = ".$this->_db->quote($rec["i_$col"]).','; } $update_str = substr($update_str, 0, strlen($update_str) - 1); $query = sprintf('UPDATE %s SET %s WHERE %s = %s', $this->table_current, $update_str, $this->primary_key, $this->_db->quote($rec["c_$this->primary_key"])); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } /* }}} */ function _insertRecord($rec) /* {{{ */ { $columns = $this->_tableColumns($this->table_current); $cols = ''; $vals = ''; foreach ($columns as $col) { if (isset($rec["i_$col"])) { $cols .= "$col,"; $vals .= $this->_db->quote($rec["i_$col"]); $vals .= ','; } } $cols = substr($cols, 0, strlen($cols) - 1); $vals = substr($vals, 0, strlen($vals) - 1); $query = sprintf('INSERT INTO %s (%s) VALUES (%s)', $this->table_current, $cols, $vals); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } /* }}} */ function doComparison() /* {{{ */ { $new = 0; $deleted = 0; $changed = 0; $import_cols = $this->_tableColumns($this->table_import); $current_cols = $this->_tableColumns($this->table_current); $query = 'SELECT 1'; foreach ($import_cols as $col) { $query .= ",i.$col AS i_$col"; // 'i' means "import" } foreach ($current_cols as $col) { $query .= ",c.$col AS c_$col"; // 'c' means "current" } $query .= " FROM $this->table_import AS i %s $this->table_current AS c"; $query .= " ON i.$this->primary_key = c.$this->primary_key"; foreach (array('LEFT JOIN', 'RIGHT JOIN') as $subquery) { $subqryres = $this->_db->query(sprintf($query, $subquery)); while ($subqryres->fetchInto($rec)) { if (@strlen($rec["c_$this->primary_key"]) <= 0) { // new domain if ($this->_debug >= 2) { echo 'NEW: ',$rec["i_domain"],"\n"; } $new++; $this->_insertRecord($rec); continue; } if (@strlen($rec["i_$this->primary_key"]) <= 0) { // deleted domain if ($this->_debug >= 2) { echo 'DELETED: ',$rec["c_domain"],"\n"; } $deleted++; $this->_writeHistory($rec, 1); $query = sprintf('DELETE FROM %s WHERE %s = %s', $this->table_current, $this->primary_key, $this->_db->quote($rec["c_$this->primary_key"])); $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } continue; } $changed_cols = array(); foreach ($import_cols as $col) { if ($rec["i_$col"] != $rec["c_$col"]) { $changed_cols[] = $col; } } if (count($changed_cols) == 1 && $changed_cols[0] = 'dt_changed') { // do nothing } elseif (count($changed_cols) > 0) { if ($this->_debug >= 2) { echo 'CHANGED: ',$rec['i_domain']; echo ' [', join('][',$changed_cols),"]\n"; } $changed++; $this->_writeHistory($rec); $this->_updateRecord($rec, $changed_cols); } } $subqryres->free(); } if ($this->_debug) { echo sprintf("INFO: %s() stats: %d new, %d changed, %d deleted\n", __FUNCTION__, $new, $changed, $deleted); } return ($new + $changed + $deleted); } /* }}} */ function writeStats() /* {{{ */ { static $queries = array(); if (count($queries) <= 0) { $queries[] = 'INSERT INTO sknic_domains_stats' .' SELECT dt_changed, "", "", COUNT(*)' .' FROM sknic_domains_import GROUP BY dt_changed'; $queries[] = 'INSERT INTO sknic_domains_stats' .' SELECT dt_changed, "status", IFNULL(status,"OK"), COUNT(*)' .' FROM sknic_domains_import GROUP BY dt_changed, status' .' ORDER BY dt_changed, COUNT(*)'; $queries[] = 'INSERT INTO sknic_domains_stats' .' SELECT dt_changed, "id_reg", id_reg, COUNT(*)' .' FROM sknic_domains_import GROUP BY dt_changed, id_reg' .' HAVING count(*) >= 100' .' ORDER BY dt_changed, COUNT(*)'; $queries[] = 'INSERT INTO sknic_domains_stats' .' SELECT dt_changed, "id_owner", id_owner, COUNT(*)' .' FROM sknic_domains_import GROUP BY dt_changed, id_owner' .' HAVING count(*) >= 100' .' ORDER BY dt_changed, COUNT(*)'; /* * Revenue queries * * NOTE: Clause "GROUP BY 1, 2, 3" below is not absolutelly * neccessary, since without that clause it works well on MySQL * 5.0.84-1-log, but on 5.0.51a-24+lenny4-log it creates this * error: ERROR 1140 (42000): Mixing of GROUP columns * (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if * there is no GROUP BY clause */ // Registrars with 0-100 domains $queries[] = 'INSERT INTO sknic_domains_stats SELECT dt_changed, "revenue" AS name, "DOM-0", ROUND(SUM(revenue),0) AS revenue FROM ( SELECT dt_changed, COUNT(*) AS cnt, COUNT(*) * 19.92 AS revenue FROM sknic_domains_import WHERE status IN ("DOM_OK","DOM_WARN","DOM_LNOT","DOM_TRAN") GROUP BY dt_changed, id_reg HAVING (COUNT(*) <= 100) ) AS X GROUP BY 1, 2, 3'; // Registrars with 101-1000 domains $queries[] = 'INSERT INTO sknic_domains_stats SELECT dt_changed, "revenue" AS name, "DOM-100", ROUND(SUM(revenue),0) AS revenue FROM ( SELECT dt_changed, COUNT(*) AS cnt, COUNT(*) * 16.6 AS revenue FROM sknic_domains_import WHERE status IN ("DOM_OK","DOM_WARN","DOM_LNOT","DOM_TRAN") GROUP BY dt_changed, id_reg HAVING (COUNT(*) > 100 AND COUNT(*) <= 1000) ) AS X GROUP BY 1, 2, 3'; // Registrars with 1001-10000 domains $queries[] = 'INSERT INTO sknic_domains_stats SELECT dt_changed, "revenue" AS name, "DOM-1000", ROUND(SUM(revenue)) AS revenue FROM ( SELECT dt_changed, COUNT(*) AS cnt, COUNT(*) * 13.28 AS revenue FROM sknic_domains_import WHERE status IN ("DOM_OK","DOM_WARN","DOM_LNOT","DOM_TRAN") GROUP BY dt_changed, id_reg HAVING (COUNT(*) > 1000) ) AS X GROUP BY 1, 2, 3'; // Fee for changing registrar $queries[] = 'INSERT INTO sknic_domains_stats SELECT dt_changed, "revenue" AS name, "CHANGEREG", ROUND(SUM(revenue)) AS revenue FROM ( SELECT dt_changed, COUNT(*) AS cnt, COUNT(*) * 6.64 AS revenue FROM sknic_domains_import WHERE status IN ("DOM_TRAN") GROUP BY dt_changed, id_reg ) AS X GROUP BY 1, 2, 3'; } foreach ($queries as $query) { $result = $this->_db->query($query); if (DB::isError($result)) { $this->error($result); } } } /* }}} */ } /* Modeline for ViM {{{ * vim: set ts=4: * vim600: fdm=marker fdl=0 fdc=0: * }}} */ ?>