This is an automated email from the git hooks/post-receive script. New commit to branch feature/SqlFileReader in repository nuiton-utils. See https://gitlab.nuiton.org/nuiton/nuiton-utils.git commit f3f3f1d8411eb9203bf4a2d96e7eb26adeb57f44 Author: Julien Ruchaud <julien.ruchaud@debux.org> Date: Tue Apr 5 11:07:18 2016 +0200 Inital import --- .../java/org/nuiton/util/sql/SqlFileReader.java | 156 +++++++++++++++++++++ .../org/nuiton/util/sql/SqlFileReaderTest.java | 112 +++++++++++++++ 2 files changed, 268 insertions(+) diff --git a/src/main/java/org/nuiton/util/sql/SqlFileReader.java b/src/main/java/org/nuiton/util/sql/SqlFileReader.java new file mode 100644 index 0000000..3ef664c --- /dev/null +++ b/src/main/java/org/nuiton/util/sql/SqlFileReader.java @@ -0,0 +1,156 @@ +package org.nuiton.util.sql; + +import java.io.IOException; +import java.io.Reader; +import java.io.StringReader; +import java.util.Iterator; +import java.util.NoSuchElementException; + +/** + * Create an iterable on a SQL text content. The content is iterated on each SQL + * statement. For information the class handles semi-colon in quote. + * + * File example: + * INSERT INTO client (prenom, age) VALUES ('John', 11); + * INSERT INTO client (prenom, age) VALUES ('Jack', 12); + * + * Then: + * SqlLine sqlLine = new SqlLine(stream); + * for (String sql : sqlLine) { + * // process sql variable + * } + * + * @author jruchaud + */ +public class SqlFileReader implements Iterable<String> { + protected Reader source; + + public SqlFileReader(String source) { + this.source = new StringReader(source); + } + + public SqlFileReader(Reader source) { + this.source = source; + } + + @Override + public Iterator<String> iterator() { + return new SqlFileReaderIterator<>(source); + } + + enum SqlFileParserState {NORMAL, QUOTE, COMMENT}; + + /** + * Use to create an iterator on the iterable. + * @param <E> a String + */ + public static class SqlFileReaderIterator<E extends String> implements Iterator<String> { + + protected Reader source; + + /** The variable is used to keep if the iterator reach the end */ + protected int scanner; + + public SqlFileReaderIterator(Reader source) { + this.source = source; + } + + @Override + public boolean hasNext() { + return this.scanner != - 1; + } + + @Override + public String next() { + if (this.scanner == -1) { + throw new NoSuchElementException(); + } + + String buffer = ""; + SqlFileParserState state = SqlFileParserState.NORMAL; + + try { + + while ((this.scanner = this.source.read()) != -1) { + char character = (char) this.scanner; + + switch (state) { + case NORMAL: + switch (character) { + // Remove useless character + case '\n': + case '\r': + break; + + // Search the end of query + case ';': + return buffer + ";"; + + // Enter comment state if you have -- + case '-': + int length = buffer.length(); + if (length != 0) { + state = SqlFileParserState.COMMENT; + } + buffer += character; + break; + + // Enter quote state + case '\'': + state = SqlFileParserState.QUOTE; + buffer += character; + break; + + // By default append character + default: + buffer += character; + break; + } + break; + + case QUOTE: + // Remove useless character + switch (character) { + case '\n': + case '\r': + break; + + // Search the end of quote + case '\'': + state = SqlFileParserState.NORMAL; + buffer += character; + break; + + // By default append character + default: + buffer += character; + break; + + } + break; + + case COMMENT: + switch (character) { + // Search the end of comment + case '\n': + case '\r': + return buffer; + + // By default append character + default: + buffer += character; + break; + + } + break; + } + } + + } catch (IOException ex) { + throw new RuntimeException(ex); + } + + return buffer; + } + } +} diff --git a/src/test/java/org/nuiton/util/sql/SqlFileReaderTest.java b/src/test/java/org/nuiton/util/sql/SqlFileReaderTest.java new file mode 100644 index 0000000..07a8e76 --- /dev/null +++ b/src/test/java/org/nuiton/util/sql/SqlFileReaderTest.java @@ -0,0 +1,112 @@ +package org.nuiton.util.sql; + +import junit.framework.TestCase; + +/** + * Test the SqlFileReader. + * + * @author jruchaud + */ +public class SqlFileReaderTest extends TestCase { + + public void assertIterator(Iterable<String> iterable, String[] expectedValues) { + int index = 0; + for (String value : iterable) { + String expectedValue = expectedValues[index].replaceAll("\n|\r", ""); + assertEquals(expectedValue, value); + index ++; + } + + assertEquals(expectedValues.length, index); + } + + public SqlFileReader getSqlFileReader(String[] sql) { + String content = ""; + for (String value : sql) { + content += value; + } + SqlFileReader reader = new SqlFileReader(content); + return reader; + } + + public void testBasic() { + String[] sql = {"INSERT INTO client (prenom, age) VALUES ('John', 11)"}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testBasicWithSemiColon() { + String[] sql = {"INSERT INTO client (prenom, age) VALUES ('John', 11);", ""}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testBasicMultiInserts() { + String[] sql = { + "INSERT INTO client (prenom, age) VALUES ('John', 11);\n\n\n\n\n", + "INSERT INTO client (prenom, age) VALUES ('Jack', 12);", + "INSERT INTO client (prenom, age) VALUES ('Boby', 13);", + "" + }; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testQuoteWithSemiColon() { + String[] sql = {"INSERT INTO client (prenom, age) VALUES ('Jo;hn', 11)"}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testQuoteWithQuote() { + String[] sql = {"INSERT INTO client (prenom, age) VALUES ('Jo''hn', 11)"}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testQuoteMultiInserts() { + String[] sql = { + "INSERT INTO client (prenom, age) VALUES ('John\n', 11);", + "INSERT INTO client (prenom, age) VALUES ('Jack;\n', 12);", + "INSERT INTO client (prenom, age) VALUES ('Boby', 13);", + "" + }; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testComment() { + String[] sql = {"-- Comment"}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testCommentWithSemiColon() { + String[] sql = {"-- Comm;ent"}; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + + public void testCommentMultiInserts() { + String[] sql = { + "INSERT INTO client (prenom, age) VALUES ('John', 11);\n", + "-- Comment\n", + "-- Comment\n", + "INSERT INTO client (prenom, age) VALUES ('Jack', 12);", + "INSERT INTO client (prenom, age) VALUES ('Boby', 13);", + "" + }; + + SqlFileReader reader = getSqlFileReader(sql); + assertIterator(reader, sql); + } + +} -- To stop receiving notification emails like this one, please contact nuiton.org SCM administrator <admin+scm@nuiton.org>.