Removing a line, plus drop the comma of the last line with a matching pattern

Question

DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`),
  CONSTRAINT `FK6661B19126D878D` FOREIGN KEY (`answer_id`) REFERENCES `ans` (`id`),
  CONSTRAINT `FK6661B1975B33071` FOREIGN KEY (`id`) REFERENCES `apobj` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

My goal is to drop all constraints from a database, so I would like to have simple code search for the word "CONSTRAINT" and drop the line

I tried to use sed

sed '/\s*CONSTRAINT/d' ~/Downloads/dump.sql > ~/ouput.sql

but there are all these tailing comma that are left behind because of CONSTRAINTS being the last statement. I don't mind if it awk, sed, or some common tools.

The desired output is

DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Answer 1

sed is an excellent tool for simple substitutions on a single line but for anything else just use awk. Here's GNU awk for multi-char RS and the \s abbreviation for [[:space:]]:

$ awk -v RS='^$' -v ORS= '{gsub(/,\s*CONSTRAINT[^\n,]+/,"")}1' file
DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL,
  `answer_id` bigint(20) DEFAULT NULL,
  `date_deleted` bigint(20) DEFAULT NULL,
  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL,
  `expression_id` bigint(20) DEFAULT NULL,
  `expression_type` varchar(255) DEFAULT NULL,
  `ordering` int(11) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  `expression_for_deselect_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6661B19F393DFCD` (`expression_id`),
  KEY `FK6661B195182DDCD` (`question_id`),
  KEY `FK6661B195742A56B` (`expression_for_deselect_id`),
  KEY `idx_qlnk_nswrd` (`answer_id`),
  KEY `FK6661B19126D878D` (`answer_id`),
  KEY `FK6661B1975B33071` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

For a POSIX-only solution (see comments below from @mklement0):

awk -v RS=$(printf '\3') -v ORS= '{gsub(/,[[:space:]]*CONSTRAINT[^\n,]+/,"")}1'

Answer 2

It's a bit of a hack - use (GNU) awk to shift trailing ,s to the start of subsequent lines first followed by a sed delete.

awk -v RS= '{gsub(/,\n/, "\n,"); print}'  ~/Downloads/dump.sql | 
sed '/\s*CONSTRAINT/d' > ~/ouput.sql

This gives me the following, which should be valid SQL

DROP TABLE IF EXISTS `qalnk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `qalnk` (
  `id` bigint(20) NOT NULL
,  `answer_id` bigint(20) DEFAULT NULL
,  `date_deleted` bigint(20) DEFAULT NULL
,  `deleted_by_user_ap_id` varchar(36) DEFAULT NULL
,  `expression_id` bigint(20) DEFAULT NULL
,  `expression_type` varchar(255) DEFAULT NULL
,  `ordering` int(11) DEFAULT NULL
,  `question_id` bigint(20) DEFAULT NULL
,  `expression_for_deselect_id` bigint(20) DEFAULT NULL
,  PRIMARY KEY (`id`)
,  KEY `FK6661B19F393DFCD` (`expression_id`)
,  KEY `FK6661B195182DDCD` (`question_id`)
,  KEY `FK6661B195742A56B` (`expression_for_deselect_id`)
,  KEY `idx_qlnk_nswrd` (`answer_id`)
,  KEY `FK6661B19126D878D` (`answer_id`)
,  KEY `FK6661B1975B33071` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Answer 3

search for any line that has the expression "CONSTRAINT" and delete line and the , before that line.

sed -e '/.*/{
N
s/,\n.*CONSTRAINT//g}' -e '/.*CONSTRAINT.*/d' my_file

Answer 4

^{Despite the down-votes, I think this answer offers effective solutions that work as advertised, while (hopefully) also being informative. Do tell us if it is not, so I can fix it.}

A single-pass sed solution requires that all input lines be read at once, similar to Ed Morton's helpful awk answer:

A GNU sed solution:

sed -zr 's/,\n\s*CONSTRAINT\s+[^\n,]+//g' file

• -z uses NUL (null bytes) as the input line separator, and since the are no embedded NULs in the input, the entire contents of file is read into the pattern space at once.

• -r enables extended regular expressions (modern syntax, extended features).

• The regex removes all CONSTRAINT lines, including the ,\n from the preceding line, which leaves the syntax of the enclosing CREATE TABLE statement intact.

A BSD (macOS) sed solution is, unfortunately, much more cumbersome:

The BSD sed version is missing many of GNU's (nonstandard) convenience features, which makes the solution more painful. BSD sed offers only a few extensions to the POSIX standard, but a notable one is the ability to use so-called extended regular expressions.

sed -E ':a
$!{N;ba
}
s/,\n([[:blank:]]*CONSTRAINT[[:blank:]]+[[:print:][:blank:]]+\n)+/\
/g' file

• -E - similar to GNU sed's -r - enables extended regular expressions.

• :a\n$!{N;b\na} is a common sed idiom that reads the entire input at once:

  • :a defines a label to jump to.
  • $! matches every line but (!) the last ($)
  • {N;ba}; reads the next line into the pattern space (input buffer to operate on), then branches (b) to label a (:a).
  • In other words: This reads all lines into the pattern space, which is what the subsequent commands then operate on (s, in this case).

• Note the required newlines, both to terminate branch labels and branching commands, and (in escaped form) in the replacement command.

  • It is possible to cram this into a single line by using multiple -e options, but that makes the command less readable.
• Note the use of verbose POSIX character classes such as [[:blank:]], because shortcut classes such as \s aren't supported.
  • In particular, while \n can be matched in the regex in principle, it is inexplicably not recognized inside a character class. Thus, [^\n] must be emulated with [[:print:][:blank:]] -- [:blank:] is added to also match tab chars., which are not considered printable chars; (\t can't be used inside char. classes either).