From 68806a05900972c7591b2d6020a96315ea33bcf9 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Thu, 4 Jan 2018 02:41:06 +0000 Subject: [PATCH] lib/php/db/table.php --- lib/php/db.php | 5 +++++ lib/php/db/field.php | 3 +-- lib/php/db/table.php | 29 +++++++++++++----------- share/sql/country | 18 +++++++++++++++ share/sql/country.sql | 51 +++++++++++++++++++++++++++++++++++++++++++ share/sql/zipcode | 3 +-- www/dbq/dbq.php | 5 +++-- 7 files changed, 95 insertions(+), 19 deletions(-) create mode 100755 share/sql/country create mode 100644 share/sql/country.sql diff --git a/lib/php/db.php b/lib/php/db.php index 67846b75..db307cf3 100644 --- a/lib/php/db.php +++ b/lib/php/db.php @@ -1328,6 +1328,11 @@ class Db extends nb { return $password; } + public function cast_text($str) { + if ($fct = $this->conf_type('cast_text')) $str = $fct($str); + return $str; + } + } # < Class ?> diff --git a/lib/php/db/field.php b/lib/php/db/field.php index bbf835e6..0ea5d10c 100644 --- a/lib/php/db/field.php +++ b/lib/php/db/field.php @@ -147,8 +147,7 @@ class field extends nb { public function sql_name_cast_text() { $name = $this->db()->sql_name($this->name); - if ($fct = $this->db()->conf_type('cast_text')) $name = $fct($name); - return $name; + return $this->db()->cast_text($name); } public function sql_name() { diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 15af5d22..1f7a4f9e 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -1856,13 +1856,18 @@ Class Table extends nb { public function out($v,$head=[],$conf=[]) { return $this->db()->out($v,$head,$conf); } public function url_referer($default='') { + if (self::p('referer')) { return urldecode($this->p('referer')); + } elseif(!empty($default)) { return $default; + } else { return '?table=' . urlencode($this->name) . (self::p('db') ? '&db='.self::p('db') : ''); + } + } public function fields_rows() { @@ -1870,27 +1875,25 @@ Class Table extends nb { list($sql,$where,$limit,$select_count) = $this->rows_sql(); foreach ([ - 'maxlen' => 'MAX(LENGTH())', + 'maxlen' => 'MAX(LENGTH('.$this->db()->cast_text('').'))', 'max' => 'MAX()', ] as $name => $select) { - if ($this->p($name)) { - $sql = ''; + if (!$this->p($name)) continue; + $sql = ''; - foreach ($this->fields() as $f) { - $sql .= ($sql == '' ? 'SELECT ' : ', '); - $sql .= str_replace('',$f->sql_name(),$select); - } + foreach ($this->fields() as $f) { + $sql .= ($sql == '' ? 'SELECT ' : ', '); + $sql .= str_replace('',$f->sql_name(),$select); + } - $sql .= ' FROM ' . $this->sql_name() . $where . ($limit ? " LIMIT ".$limit : ''); - $len = $this->db()->query($sql)->fetch(PDO::FETCH_NUM); + $sql .= ' FROM ' . $this->sql_name() . $where . ($limit ? " LIMIT ".$limit : ''); + $len = $this->db()->query($sql)->fetch(PDO::FETCH_NUM); - $i = 0; - foreach ($rows as $k => $v) { $rows[$k][$name] = $len[$i]; $i++; } + $i = 0; + foreach ($rows as $k => $v) { $rows[$k][$name] = $len[$i]; $i++; } - } } - #return $this->out(array_values($this->object2array($this->fields()))); return $rows; } diff --git a/share/sql/country b/share/sql/country new file mode 100755 index 00000000..57b0dca3 --- /dev/null +++ b/share/sql/country @@ -0,0 +1,18 @@ +#!/usr/bin/env perl +use strict; +use warnings; +@ARGV = ('curl -s http://download.geonames.org/export/dump/countryInfo.txt |'); +#exec($ARGV[0]); exit; + +while (<>) { + #s/^#ISO/ISO/; + next if /^#/; + chomp($_); + s,\\,\\\\,g; + my @r = split("\t",$_); + unshift @r,$r[0]; + while (@r<20) { + push @r,''; + } + print join("\t",@r)."\n"; +} diff --git a/share/sql/country.sql b/share/sql/country.sql new file mode 100644 index 00000000..08e61bf4 --- /dev/null +++ b/share/sql/country.sql @@ -0,0 +1,51 @@ +-- ISO +-- ISO3 +-- ISO-Numeric +-- fips +-- Country +-- Capital +-- Area(in sq km) +-- Population +-- Continent +-- tld +-- CurrencyCode +-- CurrencyName +-- Phone +-- Postal Code Format +-- Postal Code Regex +-- Languages +-- geonameid +-- neighbours +-- EquivalentFipsCode +DROP TABLE IF EXISTS country; +CREATE TABLE IF NOT EXISTS country ( + id varchar(2), + iso varchar(2), + iso3 varchar(3), + isonum varchar(3), + fips varchar(2), + name varchar(200), + capital varchar(200), + area_km varchar(200), + population bigint, + continent varchar(200), + tld varchar(200), + currency_code varchar(200), + currency_name varchar(200), + phone varchar(200), + zip_formaat varchar(200), + zip_regexp varchar(200), + languages varchar(200), + geonameid varchar(200), + neighbours varchar(200), + eqfips varchar(200) + -- PRIMARY KEY(id,country) +); +GRANT ALL ON ALL TABLES IN SCHEMA public TO www; +GRANT ALL ON ALL TABLES IN SCHEMA public TO nico; +GRANT ALL ON ALL TABLES IN SCHEMA public TO root; +BEGIN TRANSACTION; +DELETE FROM country; +copy country from stdin with (format 'text'); +COMMIT; + diff --git a/share/sql/zipcode b/share/sql/zipcode index c91bcc47..b6ae7206 100755 --- a/share/sql/zipcode +++ b/share/sql/zipcode @@ -1,9 +1,8 @@ #!/usr/bin/env perl use strict; use warnings; -#exec('curl -s http://download.geonames.org/export/zip/allCountries.zip | zcat'); -#exit; @ARGV = ('curl -s http://download.geonames.org/export/zip/allCountries.zip | zcat |'); +#exec($ARGV[0]); exit; while (<>) { chomp($_); diff --git a/www/dbq/dbq.php b/www/dbq/dbq.php index 87555d77..b32b76db 100644 --- a/www/dbq/dbq.php +++ b/www/dbq/dbq.php @@ -401,11 +401,12 @@ class DbQ extends nb { } if (empty($values)) $this->error('Missing values'); - #debug([$keys,$values,$this->params['args']]); $values = $add ? array_fill(0,count($keys),'') : $values; + + #debug([count($keys),$keys,count($values),$values]); $values = array_combine($keys,$values); - # NB 23.11.17: Handle format for /vi + # Handle format for /vi if ($this->params['format'] != $this->format_html) { $row = $this->db->query2h($this->table->sql_edit($values)); -- 2.47.3