☰

正規表現 - 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 に変換しようとしています。

つまり、解決できない小さな問題が 2 つあるのです。

shipname       NVARCHAR(40) NOT NULL,

それは

  • （いつも）2つのスペース

  • 識別子（フィールド名など） - 常に [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'

それで、

  • ^文字列の始まりです

  • 2つのスペースが続く

  • 私のフィールド名 [az]+

  • 任意の数のスペースが続く [ ]+

  • 可変長文字([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 の長さを取得するのは意味がないため...これは条件付きの動作です。正規表現で可能かどうかはわかりません...

追伸：これは些細なことだと思いました。

次のようなデータがあります:

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

N'を単なるアポストロフィに変更したいのですが'(はSQL Server のものです)、 を空の文字列にN'変更したり、さらに悪いことに変更したりしたくないので、次のことを試しました。NULLULL

[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 をエミュレートします。

if:

db 構造内に ( ) が見つかった場合、最初の sed コマンドが実行され、2 番目のステートメントを実行せずに quit ( )が実行されますNOT。/NOT/q0

else:

キーワードが見つからないNOTため、2 番目のインスタンスが実行されます。

2番目の要件:

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です。ここでは、最初のテストが成功した場合、最後の行に到達する前に強制的に終了するために使用しています。quitsedsed

答え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

sedawkはいくつかのタスクには最適ですが、他のタスクでは、条件文や printf などを備えたorのようなフル機能の言語が必要ですperl。また、正規表現と 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 を適切な場所に追加するための追加の print ステートメントがいくつかあります。そのため、一般的に役立つわけではありませんが、必要に応じて他の SQL 変換に合わせて調整できます。

  • スクリプトは、質問の説明を実装します。「望ましい出力」に表示される内容ではありません (たとえば、 と は両方ともregionNULLpostalcodeフィールドであるため、長さチェックは行われません)。

出力：

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 よりも適切で、変換がはるかに簡単です。VARCHARs/NVARCHAR/VARCHAR/は固定長なので、a) 長さ制約のチェックが不要で、b) インデックス作成と検索が高速です。

  • フィールドを NULL にすることがデフォルトなので、明示的にそのように定義する必要はありません。

関連情報