☰

Регулярное выражение - SQL-манипуляция

tag-icon • tag-icon linux text-processing regular-expression sql
Регулярное выражение - SQL-манипуляция 
[pol@fedora data]$ lsb_release -a
LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: Fedora
Description:    Fedora release 34 (Thirty Four)
Release:    34
Codename:   ThirtyFour

Я пытаюсь преобразовать пример файла базы данных из MS SQL Server в PostgreSQL.

Итак, у меня есть две небольшие проблемы, которые я не могу решить.

shipname       NVARCHAR(40) NOT NULL,

Это

  • (всегда) два пробела

  • идентификатор (т.е. имя поля) - всегда [az] - строчные буквы алфавита

  • за которым следует неизвестное количество пробелов

  • за которым следует NVARCHAR(xy) NOT NULLилиза ним может следовать NVARCHAR(xy) NULL

и я хочу превратить это в

shipname       TEXT NOT NULL CHECK (LENGTH(shipname)  <= xy),

или

shipname       TEXT NULL,

Что у меня есть на данный момент:

sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'

Так,

  • ^это начало строки

  • за которым следуют два пробела

  • затем мое имя поля [az]+

  • за которым следует произвольное количество пробелов [ ]+

  • NVARCHAR([0-9]+)

и заменить в

TEXTзатем NOT NULL, затем CHECK(LENGTH(xy) - обратная ссылка 1 - <= обратная ссылка 2...

Я пробовал различные варианты и комбинации вышеперечисленного, но ничего не работает.

[pol@fedora data]$ sed 's/^  [a-z]+[ ]+NVARCHAR([0-9]+) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g' 
sed: -e expression #1, char 87: invalid reference \2 on `s' command's RHS

Получить недействительную обратную ссылку...

Идеально, и я подчеркиваюИдеально, если строка, следующая за NVARCHAR(xy), является NULLинет NOT NULL, мне не нужна проверка длины, потому что нет смысла брать ДЛИНУ NULL... это условное поведение - не уверен, возможно ли это в регулярных выражениях...

ps думал, что это будет тривиально.

Имеются такие данные:

N'Strada Provinciale 1234', N'Reggio Emilia', NULL, N'10289', N'Italy');

Я хочу изменить N'на простой апостроф '( N'это особенность SQL Server), но я не хочу менять NULLна пустую строку или еще хуже ULL- поэтому я попробовал:

[pol@fedora data]$ sed 's/N\'\'/g TSQLV5.sql

но получить

sed: -e expression #1, char 7: unterminated `s' command

sedЯ знаю, что я многого добился , но был бы открыт для любых awkкоманд, которые могли бы выполнить требуемые задачи.

решение1

Так как вы используете, fedoraу вас есть GNU sedи это должно работать:

s="  shipname       NVARCHAR(40) NOT NULL,"
echo "$s" | sed -E '/NOT/{s/^  ([[:lower:]]+)\s*NVARCHAR\(([[:digit:]]+)\) NOT NULL,$/\1 TEXT NOT NULL CHECK \(LENGTH\(\1\) <= \2\),/;q0} ; s/^  ([[:lower:]]+)/\1 TEXT NULL,/'

Это имитирует поддельное «если».

if:

Если внутри структуры базы данных найдено (), выполняется первая команда sed, затем выполняется quit ( ) NOTбез выполнения второго оператора./NOT/q0

else:

Ключевое слово не NOTнайдено, и выполняется второй экземпляр.

Для второго требования:

sed "s/N'/'/g"

Глобально найдите N'и замените его только на '. Я обнаружил, что полезно заменить 'его "на sedразделитель командной строки и сделать его более понятным без большого количества экранирований.

Поместите первый sedв файл:

#!/bin/sed -Ef

# If a NOT is found execute this:
# capture the column name and the value of this
/NOT/ {
    s/^  ([[:lower:]]+)\s*NVARCHAR\(([[:digit:]]+)\) NOT NULL,$/\1 TEXT NOT NULL CHECK \(LENGTH\(\1\) <= \2\),/

    # Quit without execute the other statement
    q0
}

# Else: If we are here then the database
# structure does not contains a length for the column;
# so it should be NULL
s/^  ([[:lower:]]+)/\1 TEXT NULL,/

Команда {используется для группировки нескольких sedкоманд.

Это qкоманда quit, она используется для sedвыхода. Здесь я использую ее для принудительного sedвыхода до того, как наткнусь на последнюю строку, если первый тест прошел успешно.

решение2

У вас уже есть ответы, но я хочу добавить, что пошло не так в вашем подходе, чтобы вы могли извлечь из этого урок, а не просто копировать чье-то решение:

  • Вы используете расширенные регулярные выражения, но забыли предоставить -Eвозможность sed.
  • Вы хотите повторно использовать идентификатор, но вы не заключили его в()
  • Вы, кажется, путаете ()группы ERE с буквальными. Вы, вероятно, имеете в видуsed -E 's/^ ([a-z]+)[ ]+NVARCHAR\(([0-9]+)\) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'
  • Первая часть до пробелов не отображается в замене. Вам также нужно сгруппировать ее и использовать как ссылку в замене:sed -E 's/^( ([a-z]+)[ ]+)NVARCHAR\(([0-9]+)\) NOT NULL/\1TEXT NOT NULL CHECK \(LENGTH\((\2) <= (\3)\)/g'
  • [ ]+то же самое, что и +. Это не ошибка, но делает чтение более запутанным.
  • Вариант gизлишний. С анкером типа ^или $в шаблоне многократные замены невозможны.
  • Вы можете избежать множественных выражений, сделав NOTнеобязательным: `sed -E 's/^( ([az]+) +)NVARCHAR(([0-9]+)) (NOT )?NULL/\1TEXT \4NULL CHECK (LENGTH((\2) <= (\3))/'
  • С другой стороны, если вы хотите убрать проверку, вы можете сделать это с помощью отдельной замены:s/^( [a-z]+ +)NVARCHAR\(([0-9]+)\) NULL/\1TEXT NULL/
  • Вы s/N\'\'/gпропустили разделитель между шаблоном поиска и заменой:s/N\'/\'/g

Итак, в итоге вы получаете

sed -E 's/^(  ([a-z]+) +)NVARCHAR\(([0-9]+)\) NOT NULL/\1TEXT NOT NULL CHECK \(LENGTH\((\2) <= (\3)\)/
  s/^(  [a-z]+ +)NVARCHAR\(([0-9]+)\) NULL/\1TEXT NULL/
  s/N\'/\'/g'

решение3

sedотлично подходит для некоторых задач, но для некоторых других задач требуется полнофункциональный язык, например, awkили perl, с условными операторами, printf и т. д. И желательно язык, который не читается как какой-то отвратительный гибрид регулярного выражения и калькулятора RPN :-).

#!/usr/bin/perl
use strict;

while(<>) {
  # print verbatim any lines that don't define an identifier
  unless (m/^\s+\S/) { print; next };
  # print a blank line before certain identifiers
  print "\n" if m/birthdate|address|phone/;

  # various regex transformations for IDENTITY and VARCHAR fields
  s/\s+NOT NULL IDENTITY/ GENERATED BY DEFAULT AS IDENTITY/;
  s/([[:lower:]]+)\s+NVARCHAR\((\d+)\) NOT NULL/$1 TEXT NOT NULL CHECK (LENGTH($1) <= $2)/;
  s/\s+NVARCHAR\((\d+)\)\s+NULL/ TEXT NULL/;

  # remove length checks from NULL definitions
  s/\s+CHECK.*/,/ if /(?<!NOT) NULL/;

  # add a comma at the end of the mgrid line if it's not there
  s/\s*$/,/ if /mgrid/ && ! /,\s*$/;

  # hacky crap to nicely format "TYPE (NOT )?NULL" output.
  my @F = split;
  my $identifier = shift @F;
  my $type = shift @F;
  $type .= " " . shift @F if ($F[0] =~ /NOT/);
  $type = sprintf "%-8s", $type;
  $type .= " " . shift @F if ($F[0] =~ /NULL/);

  printf "  %-15s %-13s%s\n", $identifier, $type, join(" ",'',@F);

  # print the test_field definition after mgrid
  if ($identifier eq 'mgrid') {
    print "  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)\n";
  };
}

  • это довольно грубый метод преобразования вашего ввода в (примерно) желаемый вывод. несколько преобразований регулярных выражений и немного кода для аккуратного выравнивания "полей". и несколько дополнительных операторов печати для добавления пустых строк и test_field в соответствующих местах. Таким образом, он не является универсальным, но может быть адаптирован для других преобразований SQL по мере необходимости.

  • скрипт реализует описание в вашем вопросе, а не то, что отображается в «желаемом выводе» (так, например, regionи postalcodeне имеют проверки длины, поскольку они являются полями NULL).

Выход:

CREATE TABLE employee
(
  empid           INT           GENERATED BY DEFAULT AS IDENTITY,
  lastname        TEXT NOT NULL CHECK (LENGTH(lastname) <= 20),
  firstname       TEXT NOT NULL CHECK (LENGTH(firstname) <= 10),
  title           TEXT     NULL,
  titleofcourtesy TEXT     NULL,

  birthdate       DATE NOT NULL,
  hiredate        DATE NOT NULL,

  address         TEXT NOT NULL CHECK (LENGTH(address) <= 60),
  city            TEXT NOT NULL CHECK (LENGTH(city) <= 15),
  region          TEXT     NULL,
  postalcode      TEXT     NULL,
  country         TEXT NOT NULL CHECK (LENGTH(country) <= 15),

  phone           TEXT NOT NULL CHECK (LENGTH(phone) <= 24),
  mgrid           INT      NULL,
  test_field      TEXT     NULL CHECK (LENGTH(test_field) <= 25)

);

Вот разница между выводом скрипта и желаемым вами выводом (после очистки с целью удаления комментариев и некоторых лишних пробелов):

-  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
-  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
+  region          TEXT     NULL,
+  postalcode      TEXT     NULL,
Другие комментарии:

  • Вы, вероятно, PRIMARY KEY GENERATED BY DEFAULT AS IDENTITYхотитеempid

  • В postgresql есть тип данных VARCHAR(n), который, вероятно, более подходит, чем TEXT, и его гораздо проще преобразовывать: s/NVARCHAR/VARCHAR/VARCHAR имеют фиксированную длину, поэтому а) не нуждаются в проверках ограничений длины и б) быстрее индексируются и ищутся.

  • По умолчанию поле может иметь значение NULL, поэтому нет необходимости явно определять их как таковые.

Связанный контент