КЛАДРируем адреса произвольной формы (ч.1 — импорт)19.08.2021 11:46

#!/bin/sh

. `dirname "$0"`/../app.conf

echo "`date '+%F %T'` ==== Connecting to DB : $pghost:$pgport:$pgbase:$pguser"
# тестирование подключения к БД
psql -t -c 'SELECT 1' -h $pghost -p $pgport -U $pguser -w $pgbase 1>/dev/null 2>/dev/null
rv="$?"
if [ "$rv" != "0" ]; then
  echo "$pghost:$pgport:$pgbase:$pguser:$pgpass" >>~/.pgpass
  chmod 0600 ~/.pgpass
  psql -t -c 'SELECT 1' -h $pghost -p $pgport -U $pguser -w $pgbase 1>/dev/null 2>/dev/null
  rv="$?"
fi

if [ "$rv" != "0" ]; then
  echo "DB not connected : $pghost:$pgport:$pgbase:$pguser"
  exit 1
fi

# инициализация каталога _dbf
#_dbf=`mktemp -d`
mkdir ./dbf && chmod 777 ./dbf
_dbf=`readlink -f ./dbf`
rm -rf ${_dbf} 2>/dev/null
mkdir ${_dbf} 2>/dev/null
touch ${_dbf}/.sql
dir=`dirname "$0"`
dir=`readlink -f $dir`

## импорт базы КЛАДР'а в _dbf/.sql
# защита от автоотключения по таймауту
echo "SET statement_timeout = 0;" >>${_dbf}/.sql
# включаем WIN-кодировку
echo "SET client_encoding = 'WIN1251';" >>${_dbf}/.sql
# включаем application_name для мониторинга активного процесса
echo "SET application_name = 'kladr : import [`hostname`]';" >>${_dbf}/.sql
# включаем "последовательные" транзакции
echo "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;" >>${_dbf}/.sql
# блокируем эксклюзивно upd-таблицу для "замораживания" параллельных операций
echo "LOCK TABLE kladr_upd IN EXCLUSIVE MODE NOWAIT;" >>${_dbf}/.sql
# сбрасываем информацию в таблицу протокола проверок
echo "INSERT INTO kladr_chk(hostname) VALUES('`hostname`');" >>${_dbf}/.sql

# инициализация временного каталога импорта
#tmp=`mktemp -d`
mkdir ./tmp && chmod 777 ./tmp
tmp=`readlink -f ./tmp`
cd $tmp

echo "`date '+%F %T'` ==== Downloading : $source"
# загрузка базы КЛАДР'а с ограничением по скорости или без
wget -S $source --limit-rate=8k 2>.hdr
# wget -S $source 2>.hdr

echo "`date '+%F %T'` ==== Comparing 'Last-Modified'"
rc=`cat .hdr | egrep 'HTTP/[0-9]\.[0-9] [0-9]{3}' | sed -e 's/^[ ]*HTTP\/[0-9]\.[0-9][ ]*\([0-9]*\).*$/\1/i' | egrep -v '301' | head -1`
lm=`cat .hdr | egrep 'Last-Modified' | sed -e 's/^[ ]*Last-Modified:[ ]*//i' | head -1`
echo "  -- HTTP code : $rc"
echo "  -- HTTP 'Last-Modified' : $lm"
pglm=`psql -h $pghost -p $pgport -U $pguser -w -t -c 'SELECT lm FROM kladr_upd ORDER BY id DESC LIMIT 1' $pgbase | sed -e 's/^[ ]*//i'`
echo "  -- PGDB 'Last-Modified' : $pglm"

if [ "$rc" = "200" ] && [ "$lm" != "" ] && [ "$lm" != "$pglm" ]; then
  # распаковка базы
  echo "`date '+%F %T'` ==== Unpacking 7z"
  p7zip -d Base.7z 1>/dev/null 2>/dev/null
  cp $tmp/* ${_dbf}
  cd $dir

  echo "`date '+%F %T'` ==== Processing DBF"
  # обработка всех .DBF
  for dbf in `find ${_dbf} -maxdepth 1 -iname '*.DBF'`; do
    dbfn=`basename $dbf | tr '[:lower:]' '[:upper:]'`
    # преобразование заголовков
    echo "  -- DBF : $dbfn"
    echo "    -- header"
    # получаем структуру полей DBF | DOS2WIN | берем только описания полей (skip 2 строки) | оставляем только их имена
    fld=`dbview -b -t -e -o -r $dbf | recode CP866..CP1251 | tail -n+2 | xargs -l | egrep -io "^[a-z0-9_]+"`
    echo "CREATE TEMPORARY TABLE \"$dbfn\"(" >>${_dbf}/.sql
    fl="0"
    for i in ${fld}; do
      [ "$fl" = "1" ] && echo ',' >>${_dbf}/.sql
      echo -n "  \"$i\"\n    varchar" >>${_dbf}/.sql
      fl="1"
    done
    echo ");" >>${_dbf}/.sql
    # преобразование данных
    echo "    -- data"
    echo "COPY \"$dbfn\"(" >>${_dbf}/.sql
    fl="0"
    for i in ${fld}; do
      [ "$fl" = "1" ] && echo ',' >>${_dbf}/.sql
      echo -n "  \"$i\"" >>${_dbf}/.sql
      fl="1"
    done
    echo ") FROM stdin;" >>${_dbf}/.sql
    # получаем данные DBF, разделенные '~' | склеиваем "висящие" строки ([\t\r\n] в теле поля данных) | DOS2WIN | убираем все '\t' | убираем концевые ';' | заменяем ';'->'\t'
    dbview -d~ -b -t $dbf | sed -e :a -e '/[\r\t]$/N; s/[\r\t]\n//g; ta' | recode CP866..CP1251 | sed -e 's/\t//g; s/~\r//g; s/~,/,/g; s/~/\t/g' >>${_dbf}/.sql
    echo "\\." >>${_dbf}/.sql
  done

  # интеграция процедуры обновления базы - последовательное подключение всех sql-файлов импорта
  ls ${dir}/import/*.sql | xargs -l readlink -f | xargs -l -I{} cat {} >>${_dbf}/.sql
  # вставка метки обновления
  echo "INSERT INTO kladr_upd(lm, hostname) VALUES('$lm', '`hostname`');" >>${_dbf}/.sql
fi
echo "COMMIT;" >>${_dbf}/.sql

cd $dir
rm -rf $tmp

echo "`date '+%F %T'` ==== Processing SQL"
psql -h $pghost -p $pgport -U $pguser -w -f ${_dbf}/.sql $pgbase
rv="$?"

if [ "$rv" = "0" ]; then
  rm -rf ${_dbf}/ 2>/dev/null
fi
echo "`date '+%F %T'` ==== Exit : $rv"

exit "$rv"

--// КЛАДР : сокращения

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_abbr(
  LIKE kladr_abbr INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_abbr(code, lvl, name)
  SELECT
    "SCNAME",
    "KOD_T_ST"::smallint,
    "SOCRNAME"
  FROM
    "SOCRBASE.DBF";

-- удаляем отсутствующие
DELETE FROM
  kladr_abbr T
USING
  kladr_abbr X LEFT JOIN
  _kladr_abbr Y
    USING(code, lvl)
WHERE
  (T.code, T.lvl) = (X.code, X.lvl) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr_abbr kl
SET
  name = kli.name
FROM
  _kladr_abbr kli
WHERE
  (kl.code, kl.lvl) = (kli.code, kli.lvl) AND
  (
    kl.name
  ) IS DISTINCT FROM
  (
    kli.name
  );

-- очищаем совпадающие
DELETE FROM
  _kladr_abbr kli
USING
  kladr_abbr kl
WHERE
  (kli.code, kli.lvl) = (kl.code, kl.lvl);

-- вставляем оставшиеся
INSERT INTO kladr_abbr
  SELECT
    *
  FROM
    _kladr_abbr;

--// КЛАДР : от регионов до улиц

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr(
  LIKE kladr INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr(code, status, name, abbr, idx, ifns, ocato, lvl)
  SELECT DISTINCT ON(code, status)
    *
  FROM
    (
      SELECT
        regexp_replace(rpad(substr("CODE", 1, length("CODE") - 2), 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') code,
        substr("CODE", length("CODE") - 1, 2) status,
        "NAME",
        "SOCR",
        nullif("INDEX", ''),
        nullif("GNINMB", ''),
        nullif("OCATD", ''),
        "STATUS"
      FROM
        (
          SELECT
            "CODE",
            "NAME",
            "SOCR",
            "INDEX",
            "GNINMB",
            "OCATD",
            "STATUS"::smallint
          FROM
            "KLADR.DBF"
        UNION ALL
          SELECT
            "CODE",
            "NAME",
            "SOCR",
            "INDEX",
            "GNINMB",
            "OCATD",
            NULL::smallint "STATUS"
          FROM
            "STREET.DBF"
        ) T
    ) T;

-- удаляем отсутствующие
DELETE FROM
  kladr T
USING
  kladr X LEFT JOIN
  _kladr Y
    USING(code, status)
WHERE
  (T.code, T.status) = (X.code, X.status) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr kl
SET
  (
    name,
    abbr,
    idx,
    ifns,
    ocato,
    lvl
  ) =
  (
    kli.name,
    kli.abbr,
    kli.idx,
    kli.ifns,
    kli.ocato,
    kli.lvl
  )
FROM
  _kladr kli
WHERE
  (kl.code, kl.status) = (kli.code, kli.status) AND
  (
    kl.name,
    kl.abbr,
    kl.idx,
    kl.ifns,
    kl.ocato,
    kl.lvl
  ) IS DISTINCT FROM
  (
    kli.name,
    kli.abbr,
    kli.idx,
    kli.ifns,
    kli.ocato,
    kli.lvl
  );

-- очищаем совпадающие
DELETE FROM
  _kladr kli
USING
  kladr kl
WHERE
  (kli.code, kli.status) = (kl.code, kl.status);

-- вставляем оставшиеся
INSERT INTO kladr
  SELECT
    *
  FROM
    _kladr;

-- обновляем поисковый кэш
DELETE FROM
  kladr_kw
WHERE
  (code, status) IN (
    SELECT
      (ro).code,
      (ro).status
    FROM
      kladr$log
    WHERE
      ro IS DISTINCT FROM NULL
  );

INSERT INTO
  kladr_kw(code, status, keyword)
SELECT DISTINCT
  code,
  status,
  kw
FROM
  (
    SELECT
      (rn).code,
      (rn).status,
      regexp_split_to_table(lower((rn).name), E'[^\\-a-zа-яё0-9]+', 'i') kw
    FROM
      kladr$log
    WHERE
      rn IS DISTINCT FROM NULL
  ) T
WHERE
  kw <> '';

DELETE FROM kladr$log;

--// КЛАДР : дома

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_house(
  LIKE kladr_house INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_house(code, codeExt, name, idx, ifns, ocato)
  SELECT
    regexp_replace(substr("CODE", 1, 15), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig'),
    substr("CODE", 16, 4),
    "NAME",
    nullif("INDEX", ''),
    nullif("GNINMB", ''),
    nullif("OCATD", '')
  FROM
    "DOMA.DBF";

-- удаляем отсутствующие
DELETE FROM
  kladr_house T
USING
  kladr_house X LEFT JOIN
  _kladr_house Y
    USING(code, codeExt)
WHERE
  (T.code, T.codeExt) = (X.code, X.codeExt) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr_house kl
SET
  (
    name,
    idx,
    ifns,
    ocato
  ) =
  (
    kli.name,
    kli.idx,
    kli.ifns,
    kli.ocato
  )
FROM
  _kladr_house kli
WHERE
  (kl.code, kl.codeExt) = (kli.code, kli.codeExt) AND
  (
    kl.name,
    kl.idx,
    kl.ifns,
    kl.ocato
  ) IS DISTINCT FROM
  (
    kli.name,
    kli.idx,
    kli.ifns,
    kli.ocato
  );

-- очищаем совпадающие
DELETE FROM
  _kladr_house kli
USING
  kladr_house kl
WHERE
  (kli.code, kli.codeExt) = (kl.code, kl.codeExt);

-- вставляем оставшиеся
INSERT INTO kladr_house
  SELECT
    *
  FROM
    _kladr_house;

-- обновляем поисковый кэш
DELETE FROM
  kladr_hs
WHERE
  (code) IN (
      SELECT
        (ro).code
      FROM
        kladr_house$log
      WHERE
        ro IS DISTINCT FROM NULL
    UNION ALL
      SELECT
        (rn).code
      FROM
        kladr_house$log
      WHERE
        rn IS DISTINCT FROM NULL
  );

-- заполняем преобразованными данными
CREATE TEMPORARY TABLE _kladr_hs0 AS
  SELECT DISTINCT ON(code, house)
    code,
    idx,
    ifns,
    ocato,
    unnest(houses) house
  FROM
    (
      SELECT
        *,
        CASE
          WHEN _range IS NULL AND name ~ E'_' THEN ARRAY[regexp_replace(name, '_', '-')]
          WHEN _range IS NULL THEN ARRAY[name]
          WHEN _range IS NOT NULL THEN ARRAY(
            SELECT
              i::text
            FROM
              generate_series(_range[1]::integer + CASE WHEN _range[4] IS NOT NULL THEN (_range[1]::integer + _range[4]::integer) % 2 ELSE 0 END, _range[2]::integer, _range[3]::integer) i
          )
          ELSE NULL
        END houses
      FROM
        (
          SELECT
            code,
            idx,
            ifns,
            ocato,
            name,
            CASE
              WHEN name ~ E'^Н\\(\\d+-\\d+\\)$' THEN regexp_split_to_array(substr(name, 3, length(name) - 3), '-') || '2'::text || '1'::text
              WHEN name ~ E'^Ч\\(\\d+-\\d+\\)$' THEN regexp_split_to_array(substr(name, 3, length(name) - 3), '-') || '2'::text || '0'::text
              WHEN name = 'Н' THEN '{1,999,2}'::text[]
              WHEN name = 'Ч' THEN '{2,998,2}'::text[]
              WHEN name ~ E'^\\d+-\\d+$' THEN regexp_split_to_array(name, '-') || '1'::text
              ELSE NULL
            END _range
          FROM
            (
              SELECT
                code,
                idx,
                ifns,
                ocato,
                unnest(regexp_split_to_array(upper(name), ',')) "name"
              FROM
                kladr_house
              WHERE
                (code) IN (
                    SELECT
                      (ro).code
                    FROM
                      kladr_house$log
                    WHERE
                      ro IS DISTINCT FROM NULL
                  UNION ALL
                    SELECT
                      (rn).code
                    FROM
                      kladr_house$log
                    WHERE
                      rn IS DISTINCT FROM NULL
                )
            ) T
        ) T
    ) T
ORDER BY
  code, house, (_range IS NULL) DESC;

CREATE INDEX ON _kladr_hs0(code, house, idx DESC NULLS LAST);

CREATE TEMPORARY TABLE _kladr_hs1 AS
  SELECT DISTINCT ON (code, house)
    code,
    idx,
    ifns,
    ocato,
    house
  FROM
    _kladr_hs0
  ORDER BY
    code, house, idx DESC NULLS LAST;

CREATE INDEX ON _kladr_hs1(code, house);

CREATE TEMPORARY TABLE _kladr_hs2 AS
  SELECT
    code,
    coalesce(
      idx, 
      coalesce(
        (
          SELECT
            idx
          FROM
            _kladr_hs1
          WHERE
            (code, house) = (T.code, regexp_replace(T.house, E'^(\\d+)(\\D)?.*$', E'\\1', 'ig'))
          LIMIT 1
        ), 
        coalesce(
          (
            SELECT
              idx
            FROM
              kladr
            WHERE
              code IN (
                substr(T.code, 1, 15),
                substr(T.code, 1, 11),
                substr(T.code, 1,  8),
                substr(T.code, 1,  5),
                substr(T.code, 1,  2)
              ) AND
--              status = '00' AND
              idx IS NOT NULL
            ORDER BY
              length(code) DESC
            LIMIT 1
          ),
          ''
        )
      )
    ) idx,
    ifns,
    ocato,
    house
  FROM
    _kladr_hs1 T;

CREATE INDEX ON _kladr_hs2(code, idx, ifns, ocato, house);

INSERT INTO kladr_hs(code, idx, ifns, ocato, houses)
  SELECT
    code,
    idx,
    ifns,
    ocato,
    array_agg(house ORDER BY house) houses
  FROM
    _kladr_hs2
  GROUP BY
    1, 2, 3, 4;

DELETE FROM kladr_house$log;

--// КЛАДР : замены

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_repl(
  LIKE kladr_repl INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_repl(oldCode, newCode)
  SELECT DISTINCT
    co,
    cn
  FROM
    (
      SELECT
        regexp_replace(rpad(co, 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') co,
        so,
        regexp_replace(rpad(cn, 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') cn,
        sn
      FROM
        (
          SELECT
            *,
            substr("OLDCODE", 1, length("OLDCODE") - 2) co,
            substr("OLDCODE", length("OLDCODE") - 1, 2) so,
            substr("NEWCODE", 1, length("NEWCODE") - 2) cn,
            substr("NEWCODE", length("NEWCODE") - 1, 2) sn
          FROM
            "ALTNAMES.DBF"
        ) T
    ) T;

-- удаляем отсутствующие
DELETE FROM
  kladr_repl T
USING
  kladr_repl X LEFT JOIN
  _kladr_repl Y
    USING(oldCode, newCode)
WHERE
  (T.oldCode, T.newCode) = (X.oldCode, X.newCode) AND
  Y IS NULL;

-- очищаем совпадающие
DELETE FROM
  _kladr_repl kli
USING
  kladr_repl kl
WHERE
  (kli.oldCode, kli.newCode) = (kl.oldCode, kl.newCode);

-- вставляем оставшиеся
INSERT INTO kladr_repl
  SELECT
    *
  FROM
    _kladr_repl;