Java tutorial
package com.tesora.dve.sql; /* * #%L * Tesora Inc. * Database Virtualization Engine * %% * Copyright (C) 2011 - 2014 Tesora Inc. * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, version 3, * as published by the Free Software Foundation. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * #L% */ import io.netty.util.CharsetUtil; import java.nio.charset.Charset; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.Iterator; import java.util.List; import javax.sql.rowset.serial.SerialBlob; import javax.sql.rowset.serial.SerialException; import org.junit.BeforeClass; import org.junit.Test; import com.google.common.primitives.Bytes; import com.tesora.dve.sql.util.MirrorProc; import com.tesora.dve.sql.util.MirrorTest; import com.tesora.dve.sql.util.MysqlConnectionResource; import com.tesora.dve.sql.util.NativeDDL; import com.tesora.dve.sql.util.PEDDL; import com.tesora.dve.sql.util.ProjectDDL; import com.tesora.dve.sql.util.ResourceResponse; import com.tesora.dve.sql.util.StorageGroupDDL; import com.tesora.dve.sql.util.TestResource; public class InsertMysqlConnTest extends MysqlConnSchemaMirrorTest { private static final int SITES = 3; private static final ProjectDDL sysDDL = new PEDDL("sysdb", new StorageGroupDDL("sys", SITES, "sysg"), "schema"); static final NativeDDL nativeDDL = new NativeDDL("cdb"); // This test is inserting string literals into varbinary fields. This doesn't work so well via JDBC (which // we are using for native (the mirror). All string literals are encoded from Java UCS-2 into some other // character set which doesn't work so well for binary fields. We are going to turn off UTF8 which will // set the JDBC driver to use latin1, which for the most part will do the least harm to these literals. public InsertMysqlConnTest() { setUseUTF8(false); } @Override protected ProjectDDL getMultiDDL() { return sysDDL; } @Override protected ProjectDDL getNativeDDL() { return nativeDDL; } @BeforeClass public static void setup() throws Throwable { setup(sysDDL, null, nativeDDL, getSchema()); } private static List<MirrorTest> getSchema() { ArrayList<MirrorTest> out = new ArrayList<MirrorTest>(); return out; } @Test public void testPE969() throws Throwable { final byte[] failingInput = { 75, -108, -84, 9 }; final List<MirrorTest> tests = new ArrayList<MirrorTest>(); tests.add(new StatementMirrorProc( "CREATE TABLE `bug_repro_969` (`message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,`ip` varbinary(16) DEFAULT NULL,PRIMARY KEY (`message_id`)) ENGINE=InnoDB AUTO_INCREMENT=1262 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")); binaryTestHelper("INSERT INTO `bug_repro_969` (`ip`) VALUES (?)", Collections.singletonList(new SerialBlob(failingInput)), CharsetUtil.ISO_8859_1, tests); tests.add(new StatementMirrorFun("SELECT binary ip FROM `bug_repro_969` order by message_id")); runTest(tests); } @Test public void testPE1149() throws Throwable { final byte[] failingInput = { (byte) 255 }; // any value above 127 will fail final List<MirrorTest> tests = new ArrayList<MirrorTest>(); tests.add(new StatementMirrorProc( "CREATE TABLE `bug_repro_1149` (`user_session_id` INT NOT NULL AUTO_INCREMENT,`detail` blob, PRIMARY KEY (`user_session_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8")); binaryTestHelper("INSERT INTO `bug_repro_1149` (`detail`) VALUES (?),('hi')", Collections.singletonList(new SerialBlob(failingInput)), CharsetUtil.ISO_8859_1, tests); tests.add(new StatementMirrorFun("SELECT * from `bug_repro_1149` ORDER BY `user_session_id`")); runTest(tests); } @Test public void testPE1327() throws Throwable { final byte[] failingInput = { (byte) 0xBE, (byte) 0x0E, (byte) 0x30, (byte) 0x5c, (byte) 0x5c }; final List<MirrorTest> tests = new ArrayList<MirrorTest>(); tests.add(new StatementMirrorProc("CREATE TABLE `ecb` (`bip` int(10) unsigned NOT NULL AUTO_INCREMENT, " + "`start` varbinary(16) NOT NULL, " + "`stop` varbinary(16) NOT NULL, " + "PRIMARY KEY (`bip`) " + ") ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")); binaryTestHelper("INSERT INTO `ecb` (`start`, `stop`) VALUES (?, ?)", Arrays.asList(new SerialBlob(failingInput), new SerialBlob(failingInput)), CharsetUtil.ISO_8859_1, tests); tests.add(new StatementMirrorFun("SELECT start FROM `ecb`")); runTest(tests); } @Test public void testPE1482() throws Throwable { final byte[] failingInput = { (byte) 0x80, 0x5c, 0x72, 0x5c, 0x6e, 0x5c, 0x74, 0x5c, 0x62, 0x5c, 0x5a, 0x5c, 0x27, 0x5c, 0x22 }; // out of range char, \r, \n, \t, \b, \Z, \', \" final List<MirrorTest> tests = new ArrayList<MirrorTest>(); tests.add(new StatementMirrorProc( "CREATE TABLE `dgid` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `p12Key` mediumblob NOT NULL, `email` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")); binaryTestHelper("INSERT INTO `dgid` (`p12Key`, `email`) VALUES (?, '')", Arrays.asList(new SerialBlob(failingInput)), CharsetUtil.ISO_8859_1, tests); tests.add(new StatementMirrorFun("SELECT p12Key FROM `dgid`")); runTest(tests); } private void binaryTestHelper(final String genericSql, final List<SerialBlob> params, final Charset encoding, final List<MirrorTest> tests) throws SerialException { final List<Byte> binarySql = new ArrayList<Byte>(); final StringBuilder textSql = new StringBuilder(); final Iterator<SerialBlob> param = params.iterator(); int lastParamPos = -1; while (param.hasNext()) { final SerialBlob paramBytes = param.next(); final int nextParamPos = genericSql.indexOf("?", lastParamPos + 1); final String textSqlFragment = getSqlFragment(genericSql, lastParamPos, nextParamPos); emitSqlFragment(textSqlFragment, textSql, binarySql, encoding); emitSqlFragment("'", textSql, binarySql, encoding); final byte[] rawParamBytes = paramBytes.getBytes(1, (int) paramBytes.length()); emitSqlFragment(rawParamBytes, textSql, binarySql, encoding); emitSqlFragment("'", textSql, binarySql, encoding); lastParamPos = nextParamPos; } emitSqlFragment(getSqlFragment(genericSql, lastParamPos, -1), textSql, binarySql, encoding); testWithDecodedStmt(textSql.toString(), tests); testWithEncodedStmt(textSql.toString(), Bytes.toArray(binarySql), encoding, tests); } private static String getSqlFragment(final String genericSql, final int startIndexInclusive, final int endIndexExclusive) { return genericSql.substring(startIndexInclusive + 1, (endIndexExclusive > 0) ? endIndexExclusive : genericSql.length()); } private static void emitSqlFragment(final String fragment, final StringBuilder textContainer, final List<Byte> binaryContainer, final Charset encoding) { textContainer.append(fragment); binaryContainer.addAll(Bytes.asList(fragment.getBytes(encoding))); } private static void emitSqlFragment(final byte[] fragment, final StringBuilder textContainer, final List<Byte> binaryContainer, final Charset encoding) { textContainer.append(new String(fragment, encoding)); binaryContainer.addAll(Bytes.asList(fragment)); } private void testWithEncodedStmt(final String textSql, final byte[] binarySql, final Charset encoding, final List<MirrorTest> tests) { tests.add(new MirrorProc() { @Override public ResourceResponse execute(TestResource mr) throws Throwable { if (mr.getDDL().isNative()) { return mr.getConnection().execute(textSql); } final MysqlConnectionResource pcr = (MysqlConnectionResource) mr.getConnection(); return pcr.execute(null, encoding, binarySql); } }); } private void testWithDecodedStmt(final String stmt, final List<MirrorTest> tests) { tests.add(new StatementMirrorProc(stmt)); } }