☰

Expressão regular - manipulação SQL

tag-icon • tag-icon linux text-processing regular-expression sql
Expressão regular - manipulação 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

Estou tentando converter um arquivo de banco de dados de amostra do MS SQL Server para PostgreSQL.

Então, estou tendo dois pequenos problemas que não consigo resolver.

shipname       NVARCHAR(40) NOT NULL,

Isso é

  • (sempre) dois espaços

  • identificador (ou seja, nome do campo) - sempre [az] - letras minúsculas em ordem alfabética

  • seguido por um número desconhecido de espaços

  • seguido por NVARCHAR(xy) NOT NULLoupode ser seguido por NVARCHAR(xy) NULL

e eu quero transformar isso em

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

ou

shipname       TEXT NULL,

O que tenho até agora:

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

Então,

  • ^é o início da string

  • seguido por dois espaços

  • seguido pelo nome do meu campo [az]+

  • seguido por um não arbitrário. de espaços [ ]+

  • NVARCHAR([0-9]+)

e substitua em

TEXTseguido por NOT NULL então CHECK(LENGTH(xy) - referência anterior 1 - <= referência anterior 2...

Tentei várias permutações e combinações dos itens acima, mas nada parece funcionar para mim.

[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

Obter referência inválida...

Idealmente, e eu enfatizoidealmente, se a string após NVARCHAR(xy) for NULLenão NOT NULL, não quero nenhuma verificação de comprimento - porque não faz sentido usar o LENGTH de um NULL... este é um comportamento condicional - não tenho certeza se é possível em regexps....

ps achou que isso seria trivial.

Tenha dados como este:

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

Quero mudar o N'para um apóstrofo simples '( N'é uma coisa do SQL Server), mas não quero mudar NULLpara uma string vazia, ou pior ULL- então tentei:

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

mas pegue

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

Sei que já usei sedbastante, mas estaria aberto a qualquer awkcomando que pudesse realizar as tarefas necessárias.

Responder1

Desde que você usa fedoravocê tem GNU sede isso deve funcionar:

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,/'

Isso emula um if falso.

if:

a NOT( /NOT/) é encontrado dentro da estrutura db, então o primeiro comando sed é executado e então, quit ( q0) sem executar a segunda instrução.

else:

nenhuma NOTpalavra-chave é encontrada e a segunda instância é executada.

Para os segundos requisitos:

sed "s/N'/'/g"

Pesquise globalmente N'e substitua-o apenas por '. Achei útil trocar 'pelo delimitador "de sedlinha de comando e torná-lo mais limpo sem muito escape.

Coloque o primeiro seddentro de um arquivo:

#!/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,/

O {comando é usado para agrupar mais sedcomandos.

O qé o quitcomando, é usado para sedsair. Aqui eu usei-o para forçar seda saída antes de encontrar a última linha se o primeiro teste for bem-sucedido.

Responder2

Você já obteve respostas, mas quero acrescentar o que deu errado em sua abordagem, para que você possa aprender com isso em vez de apenas copiar alguma solução:

  • Você usa expressões regulares estendidas, mas esqueceu de dar a -Eopção sed.
  • Você deseja reutilizar o identificador, mas não o incluiu em()
  • Você parece misturar ()grupos ERE com literais. Você provavelmente quer dizersed -E 's/^ ([a-z]+)[ ]+NVARCHAR\(([0-9]+)\) NOT NULL/TEXT NOT NULL CHECK \(LENGTH\((\1) <= (\2)\)/g'
  • A primeira parte até os espaços não aparece na substituição. Você também precisa agrupá-lo e usá-lo como referência na substituição:sed -E 's/^( ([a-z]+)[ ]+)NVARCHAR\(([0-9]+)\) NOT NULL/\1TEXT NOT NULL CHECK \(LENGTH\((\2) <= (\3)\)/g'
  • [ ]+é o mesmo que +. Não é um erro, mas torna a leitura mais confusa.
  • A gopção é supérflua. Com uma âncora como ^ou $no padrão, múltiplas substituições não são possíveis.
  • Você pode evitar múltiplas expressões tornando o NOTopcional: `sed -E 's/^( ([az]+) +)NVARCHAR(([0-9]+)) (NOT )?NULL/\1TEXT \4NULL CHECK ( COMPRIMENTO((\2) <= (\3))/'
  • Por outro lado, se quiser omitir o cheque, você pode fazer isso com uma substituição separada:s/^( [a-z]+ +)NVARCHAR\(([0-9]+)\) NULL/\1TEXT NULL/
  • Você s/N\'\'/gperde o separador entre o padrão de pesquisa e a substituição:s/N\'/\'/g

Então você acaba com

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'

Responder3

sedé ótimo para algumas tarefas, mas algumas outras tarefas exigiam uma linguagem completa, como awkor perl, com condicionais e printf e muito mais. E de preferência uma linguagem que não pareça um híbrido horrível de regex e calculadora 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";
  };
}

  • este é um método de força bruta para transformar sua entrada (aproximadamente) na saída desejada. algumas transformações regex e algum código para alinhar bem os "campos". e algumas instruções de impressão extras para adicionar linhas em branco e test_field nos locais apropriados. Como tal, não é genericamente útil, mas pode ser adaptado para se adequar a outras transformações SQL, conforme necessário.

  • o script implementa a descrição na sua pergunta, não o que é exibido na "saída desejada" (por exemplo, ambos regione postalcodenão possuem verificações de comprimento porque são campos NULL).

Saída:

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)

);

Aqui está uma comparação entre a saída do script e a saída desejada (após a limpeza para remover comentários e alguns caracteres de espaço estranhos):

-  region          TEXT     NULL CHECK (LENGTH(region) <= 15),
-  postalcode      TEXT     NULL CHECK (LENGTH(postalcode) <= 10),
+  region          TEXT     NULL,
+  postalcode      TEXT     NULL,
Outros comentários:

  • Você provavelmente PRIMARY KEY GENERATED BY DEFAULT AS IDENTITYquerempid

  • postgresql possui um tipo de dados VARCHAR(n), que provavelmente é mais apropriado que TEXT e muito mais simples de transformar: s/NVARCHAR/VARCHAR/. VARCHARs têm um comprimento fixo, portanto a) não precisam de verificações de restrição de comprimento eb) são mais rápidos para indexar e pesquisar.

  • Permitir que um campo seja NULL é o padrão, portanto não há necessidade real de defini-los explicitamente como tal.

informação relacionada