Repository: commons-csv Updated Branches: refs/heads/master 48068091e -> 74afb17d3
[CSV-207] Provide a CSV Format for printing PostgreSQL CSV and Text formats. Project: http://git-wip-us.apache.org/repos/asf/commons-csv/repo Commit: http://git-wip-us.apache.org/repos/asf/commons-csv/commit/74afb17d Tree: http://git-wip-us.apache.org/repos/asf/commons-csv/tree/74afb17d Diff: http://git-wip-us.apache.org/repos/asf/commons-csv/diff/74afb17d Branch: refs/heads/master Commit: 74afb17d34e9f9a880bfe898633609068fc338e1 Parents: 4806809 Author: Gary Gregory <ggreg...@apache.org> Authored: Mon Mar 27 13:06:53 2017 -0700 Committer: Gary Gregory <ggreg...@apache.org> Committed: Mon Mar 27 13:06:53 2017 -0700 ---------------------------------------------------------------------- src/changes/changes.xml | 1 + .../java/org/apache/commons/csv/CSVFormat.java | 86 +++++++ .../commons/csv/CSVFormatPredefinedTest.java | 124 +++++----- .../org/apache/commons/csv/CSVPrinterTest.java | 228 +++++++++++++++++++ 4 files changed, 382 insertions(+), 57 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/commons-csv/blob/74afb17d/src/changes/changes.xml ---------------------------------------------------------------------- diff --git a/src/changes/changes.xml b/src/changes/changes.xml index 557e4df..95ad0b5 100644 --- a/src/changes/changes.xml +++ b/src/changes/changes.xml @@ -50,6 +50,7 @@ <action issue="CSV-191" type="add" dev="ggregory" due-to="Gary Gregory">Add convenience API CSVFormat.print(Path, Charset)</action> <action issue="CSV-192" type="add" dev="ggregory" due-to="Gary Gregory">Add convenience API CSVParser.parse(Path, Charset, CSVFormat)</action> <action issue="CSV-205" type="add" dev="ggregory" due-to="Gary Gregory">Add convenience API CSVFormat#printer() to print to System.out</action> + <action issue="CSV-207" type="add" dev="ggregory" due-to="Gary Gregory">Provide a CSV Format for printing PostgreSQL CSV and Text formats.</action> </release> <release version="1.4" date="2016-05-28" description="Feature and bug fix release"> <action issue="CSV-181" type="update" dev="ggregory" due-to="Gary Gregory">Make CSVPrinter.print(Object) GC-free.</action> http://git-wip-us.apache.org/repos/asf/commons-csv/blob/74afb17d/src/main/java/org/apache/commons/csv/CSVFormat.java ---------------------------------------------------------------------- diff --git a/src/main/java/org/apache/commons/csv/CSVFormat.java b/src/main/java/org/apache/commons/csv/CSVFormat.java index 88b6da3..ea22885 100644 --- a/src/main/java/org/apache/commons/csv/CSVFormat.java +++ b/src/main/java/org/apache/commons/csv/CSVFormat.java @@ -20,6 +20,7 @@ package org.apache.commons.csv; import static org.apache.commons.csv.Constants.BACKSLASH; import static org.apache.commons.csv.Constants.COMMA; import static org.apache.commons.csv.Constants.COMMENT; +import static org.apache.commons.csv.Constants.EMPTY; import static org.apache.commons.csv.Constants.CR; import static org.apache.commons.csv.Constants.CRLF; import static org.apache.commons.csv.Constants.DOUBLE_QUOTE_CHAR; @@ -191,6 +192,17 @@ public final class CSVFormat implements Serializable { MySQL(CSVFormat.MYSQL), /** + * @see CSVFormat#POSTGRESQL_CSV + * @since 1.5 + */ + PostgreSQLCsv(CSVFormat.POSTGRESQL_CSV), + + /** + * @see CSVFormat#POSTGRESQL_CSV + */ + PostgreSQLText(CSVFormat.POSTGRESQL_TEXT), + + /** * @see CSVFormat#RFC4180 */ RFC4180(CSVFormat.RFC4180), @@ -368,6 +380,80 @@ public final class CSVFormat implements Serializable { // @formatter:off /** + * Default PostgreSQL CSV format used by the {@code COPY} operation. + * + * <p> + * This is a comma-delimited format with a LF character as the line separator. Values are double quoted and special + * characters are escaped with {@code '"'}. The default NULL string is {@code ""}. + * </p> + * + * <p> + * Settings are: + * </p> + * <ul> + * <li>withDelimiter(',')</li> + * <li>withQuote('"')</li> + * <li>withRecordSeparator('\n')</li> + * <li>withIgnoreEmptyLines(false)</li> + * <li>withEscape('\\')</li> + * <li>withNullString("")</li> + * <li>withQuoteMode(QuoteMode.ALL_NON_NULL)</li> + * </ul> + * + * @see Predefined#MySQL + * @see <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html";> http://dev.mysql.com/doc/refman/5.1/en/load + * -data.html</a> + * @since 1.5 + */ + // @formatter:off + public static final CSVFormat POSTGRESQL_CSV = DEFAULT + .withDelimiter(COMMA) + .withEscape(DOUBLE_QUOTE_CHAR) + .withIgnoreEmptyLines(false) + .withQuote(DOUBLE_QUOTE_CHAR) + .withRecordSeparator(LF) + .withNullString(EMPTY) + .withQuoteMode(QuoteMode.ALL_NON_NULL); + // @formatter:off + + /** + * Default PostgreSQL text format used by the {@code COPY} operation. + * + * <p> + * This is a tab-delimited format with a LF character as the line separator. Values are double quoted and special + * characters are escaped with {@code '"'}. The default NULL string is {@code "\\N"}. + * </p> + * + * <p> + * Settings are: + * </p> + * <ul> + * <li>withDelimiter('\t')</li> + * <li>withQuote('"')</li> + * <li>withRecordSeparator('\n')</li> + * <li>withIgnoreEmptyLines(false)</li> + * <li>withEscape('\\')</li> + * <li>withNullString("\\N")</li> + * <li>withQuoteMode(QuoteMode.ALL_NON_NULL)</li> + * </ul> + * + * @see Predefined#MySQL + * @see <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html";> http://dev.mysql.com/doc/refman/5.1/en/load + * -data.html</a> + * @since 1.5 + */ + // @formatter:off + public static final CSVFormat POSTGRESQL_TEXT = DEFAULT + .withDelimiter(TAB) + .withEscape(DOUBLE_QUOTE_CHAR) + .withIgnoreEmptyLines(false) + .withQuote(DOUBLE_QUOTE_CHAR) + .withRecordSeparator(LF) + .withNullString("\\N") + .withQuoteMode(QuoteMode.ALL_NON_NULL); + // @formatter:off + + /** * Comma separated format as defined by <a href="http://tools.ietf.org/html/rfc4180";>RFC 4180</a>. * * <p> http://git-wip-us.apache.org/repos/asf/commons-csv/blob/74afb17d/src/test/java/org/apache/commons/csv/CSVFormatPredefinedTest.java ---------------------------------------------------------------------- diff --git a/src/test/java/org/apache/commons/csv/CSVFormatPredefinedTest.java b/src/test/java/org/apache/commons/csv/CSVFormatPredefinedTest.java index 1340534..e4492ff 100644 --- a/src/test/java/org/apache/commons/csv/CSVFormatPredefinedTest.java +++ b/src/test/java/org/apache/commons/csv/CSVFormatPredefinedTest.java @@ -1,57 +1,67 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to You under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.commons.csv; - -import org.junit.Assert; -import org.junit.Test; - -/** - * Tests {@link CSVFormat.Predefined}. - */ -public class CSVFormatPredefinedTest { - - private void test(final CSVFormat format, final String enumName) { - Assert.assertEquals(format, CSVFormat.Predefined.valueOf(enumName).getFormat()); - Assert.assertEquals(format, CSVFormat.valueOf(enumName)); - } - - @Test - public void testDefault() { - test(CSVFormat.DEFAULT, "Default"); - } - - @Test - public void testExcel() { - test(CSVFormat.EXCEL, "Excel"); - } - - @Test - public void testMySQL() { - test(CSVFormat.MYSQL, "MySQL"); - } - - @Test - public void testRFC4180() { - test(CSVFormat.RFC4180, "RFC4180"); - } - - @Test - public void testTDF() { - test(CSVFormat.TDF, "TDF"); - } -} +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.commons.csv; + +import org.junit.Assert; +import org.junit.Test; + +/** + * Tests {@link CSVFormat.Predefined}. + */ +public class CSVFormatPredefinedTest { + + private void test(final CSVFormat format, final String enumName) { + Assert.assertEquals(format, CSVFormat.Predefined.valueOf(enumName).getFormat()); + Assert.assertEquals(format, CSVFormat.valueOf(enumName)); + } + + @Test + public void testDefault() { + test(CSVFormat.DEFAULT, "Default"); + } + + @Test + public void testExcel() { + test(CSVFormat.EXCEL, "Excel"); + } + + @Test + public void testMySQL() { + test(CSVFormat.MYSQL, "MySQL"); + } + + @Test + public void testPostgreSqlCsv() { + test(CSVFormat.POSTGRESQL_CSV, "PostgreSQLCsv"); + } + + @Test + public void testPostgreSqlText() { + test(CSVFormat.POSTGRESQL_TEXT, "PostgreSQLText"); + } + + @Test + public void testRFC4180() { + test(CSVFormat.RFC4180, "RFC4180"); + } + + @Test + public void testTDF() { + test(CSVFormat.TDF, "TDF"); + } +} http://git-wip-us.apache.org/repos/asf/commons-csv/blob/74afb17d/src/test/java/org/apache/commons/csv/CSVPrinterTest.java ---------------------------------------------------------------------- diff --git a/src/test/java/org/apache/commons/csv/CSVPrinterTest.java b/src/test/java/org/apache/commons/csv/CSVPrinterTest.java index 3ee2438..a74ed25 100644 --- a/src/test/java/org/apache/commons/csv/CSVPrinterTest.java +++ b/src/test/java/org/apache/commons/csv/CSVPrinterTest.java @@ -714,10 +714,226 @@ public class CSVPrinterTest { } @Test + @Ignore + public void testPostgreSqlCsvNullOutput() throws IOException { + Object[] s = new String[] { "NULL", null }; + CSVFormat format = CSVFormat.POSTGRESQL_CSV.withQuote(DQUOTE_CHAR).withNullString("NULL").withQuoteMode(QuoteMode.ALL_NON_NULL); + StringWriter writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + String expected = "\"NULL\",NULL\n"; + assertEquals(expected, writer.toString()); + String[] record0 = toFirstRecordValues(expected, format); + assertArrayEquals(new Object[2], record0); + + s = new String[] { "\\N", null }; + format = CSVFormat.POSTGRESQL_CSV.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\t\\N\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\N", "A" }; + format = CSVFormat.POSTGRESQL_CSV.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\tA\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\n", "A" }; + format = CSVFormat.POSTGRESQL_CSV.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\n\tA\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "", null }; + format = CSVFormat.POSTGRESQL_CSV.withNullString("NULL"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\tNULL\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "", null }; + format = CSVFormat.POSTGRESQL_CSV; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\t\\N\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\N", "", "\u000e,\\\r" }; + format = CSVFormat.POSTGRESQL_CSV; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\t\t\u000e,\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "NULL", "\\\r" }; + format = CSVFormat.POSTGRESQL_CSV; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "NULL\t\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\\r" }; + format = CSVFormat.POSTGRESQL_CSV; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + } + + @Test + @Ignore + public void testPostgreSqlCsvTextOutput() throws IOException { + Object[] s = new String[] { "NULL", null }; + CSVFormat format = CSVFormat.POSTGRESQL_TEXT.withQuote(DQUOTE_CHAR).withNullString("NULL").withQuoteMode(QuoteMode.ALL_NON_NULL); + StringWriter writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + String expected = "\"NULL\"\tNULL\n"; + assertEquals(expected, writer.toString()); + String[] record0 = toFirstRecordValues(expected, format); + assertArrayEquals(new Object[2], record0); + + s = new String[] { "\\N", null }; + format = CSVFormat.POSTGRESQL_TEXT.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\t\\N\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\N", "A" }; + format = CSVFormat.POSTGRESQL_TEXT.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\tA\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\n", "A" }; + format = CSVFormat.POSTGRESQL_TEXT.withNullString("\\N"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\n\tA\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "", null }; + format = CSVFormat.POSTGRESQL_TEXT.withNullString("NULL"); + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\tNULL\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "", null }; + format = CSVFormat.POSTGRESQL_TEXT; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\t\\N\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\N", "", "\u000e,\\\r" }; + format = CSVFormat.POSTGRESQL_TEXT; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\N\t\t\u000e,\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "NULL", "\\\r" }; + format = CSVFormat.POSTGRESQL_TEXT; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "NULL\t\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + + s = new String[] { "\\\r" }; + format = CSVFormat.POSTGRESQL_TEXT; + writer = new StringWriter(); + try (final CSVPrinter printer = new CSVPrinter(writer, format)) { + printer.printRecord(s); + } + expected = "\\\\\\r\n"; + assertEquals(expected, writer.toString()); + record0 = toFirstRecordValues(expected, format); + assertArrayEquals(expectNulls(s, format), record0); + } + + @Test public void testMySqlNullStringDefault() { assertEquals("\\N", CSVFormat.MYSQL.getNullString()); } + @Test + public void testPostgreSQLNullStringDefaultCsv() { + assertEquals("", CSVFormat.POSTGRESQL_CSV.getNullString()); + } + + @Test + public void testPostgreSQLNullStringDefaultText() { + assertEquals("\\N", CSVFormat.POSTGRESQL_TEXT.getNullString()); + } + @Test(expected = IllegalArgumentException.class) public void testNewCsvPrinterAppendableNullFormat() throws Exception { try (final CSVPrinter printer = new CSVPrinter(new StringWriter(), null)) { @@ -949,6 +1165,18 @@ public class CSVPrinterTest { } @Test + @Ignore + public void testRandomPostgreSqlCsv() throws Exception { + doRandom(CSVFormat.POSTGRESQL_CSV, ITERATIONS_FOR_RANDOM_TEST); + } + + @Test + @Ignore + public void testRandomPostgreSqlText() throws Exception { + doRandom(CSVFormat.POSTGRESQL_TEXT, ITERATIONS_FOR_RANDOM_TEST); + } + + @Test public void testRandomRfc4180() throws Exception { doRandom(CSVFormat.RFC4180, ITERATIONS_FOR_RANDOM_TEST); }
