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 static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import io.netty.util.CharsetUtil;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.tesora.dve.common.PECharsetUtils;
import com.tesora.dve.db.ValueConverter;
import com.tesora.dve.resultset.ResultColumn;
import com.tesora.dve.resultset.ResultRow;
import com.tesora.dve.sql.util.MirrorTest;
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.ProxyConnectionResource;
import com.tesora.dve.sql.util.ResourceResponse;
import com.tesora.dve.sql.util.StorageGroupDDL;
public class BlobTest extends SchemaMirrorTest {
private static final ProjectDDL checkDDL =
new PEDDL("checkdb",
new StorageGroupDDL("check",3,1,"checkg"),
"schema");
private static final NativeDDL nativeDDL =
new NativeDDL("checkdb");
static protected ProxyConnectionResource conn;
@BeforeClass
public static void setup() throws Throwable {
setup(null,checkDDL,nativeDDL,getSchema());
conn = new ProxyConnectionResource();
checkDDL.create(conn);
}
@AfterClass
public static void cleanup() throws Throwable {
if(conn != null)
conn.disconnect();
conn = null;
}
private static final String[] types = new String[] { "LONG VARBINARY", "MEDIUMBLOB", "LONGBLOB", "BLOB" };
private static final String[] tnames = new String[] { "LVARB", "MB", "LB", "BL" };
private static final String[] binTypes = new String[] { "BINARY(2)", "VARBINARY(2)" };
private static final String[] binTypesTNames = new String[] { "BN", "VBN" };
private static List<MirrorTest> getSchema() {
ArrayList<MirrorTest> out = new ArrayList<MirrorTest>();
for(int i = 0; i < types.length; i++) {
String tab = "create table " + tnames[i] + " (`id` integer not null, `bt` " + types[i] + ") ";
out.add(new StatementMirrorProc(tab));
}
return out;
}
@Override
protected ProjectDDL getSingleDDL() {
// return checkDDL;
return null;
}
@Override
protected ProjectDDL getNativeDDL() {
return nativeDDL;
}
private byte[] allBytes(boolean escaped) throws Throwable {
ArrayList<Byte> buf = new ArrayList<Byte>();
for(int i = 0; i < 255; i++) {
byte b = (byte)i;
if (escaped && (b == '\\' || b == '\'')) {
byte p = '\\';
buf.add(new Byte(p));
}
buf.add(new Byte(b));
}
byte[] bytes = new byte[buf.size()];
for(int i = 0; i < bytes.length; i++) {
bytes[i] = buf.get(i).byteValue();
}
return bytes;
}
private List<Byte> toObject(byte[] in) {
return ValueConverter.toObject(in);
}
private byte[] toPrimitive(List<Byte> in) {
return ValueConverter.toPrimitive(in);
}
@Test
public void testBlob() throws Throwable {
ArrayList<MirrorTest> out = new ArrayList<MirrorTest>();
out.add(new StatementMirrorProc("set names utf8"));
Charset charset = CharsetUtil.ISO_8859_1;
byte[] esc = allBytes(true);
for(int i = 0; i < types.length; i++) {
String prefix = "insert into " + tnames[i] + " values (1, '";
String postfix = "')";
List<Byte> prebytes = toObject(prefix.getBytes(charset));
List<Byte> postbytes = toObject(postfix.getBytes(charset));
ArrayList<Byte> buf = new ArrayList<Byte>();
buf.addAll(prebytes);
buf.addAll(toObject(esc));
buf.addAll(postbytes);
String command = PECharsetUtils.getString(toPrimitive(buf), charset, true);
if (command == null)
fail("Not a valid string");
out.add(new StatementMirrorProc(command));
String query = "select bt from " + tnames[i] + " where id = 1";
out.add(new StatementMirrorFun(true,query));
}
// Test for returning a null blob
String nullTest = "insert into LB (id) values (2)";
out.add(new StatementMirrorProc(nullTest));
String nullQuery = "select bt from LB where id = 2";
out.add(new StatementMirrorFun(nullQuery));
runTest(out,getTestConfig(),false);
}
@Test
public void testAllBinaryFieldValues() throws Throwable {
for(int i = 0; i < binTypes.length; ++i) {
String tab = "create table " + binTypesTNames[i] + " (`id` integer not null, `bt` " + binTypes[i] + ") ";
conn.execute(tab);
for(byte j = 0; j < 64; ++j) {
// for(byte k = 63; k < 127; ++k) {
byte k = (byte) (j+64);
String value = createPaddedBinaryString(j, k);
int key = j << 8 | k;
String query = "select bt from " + binTypesTNames[i] + " where id = " + key;
// do an insert into the var/binary column
conn.execute("insert into " + binTypesTNames[i] +
" values (" + key + ", " + value + ")");
conn.assertResults(query, br(nr,new byte[] {j,k}));
// do an update
value = createPaddedBinaryString(k, j);
conn.execute("update " + binTypesTNames[i] +
" set bt=" + value + " where id = " + key);
conn.assertResults(query, br(nr,new byte[] {k,j}));
// }
}
}
}
private String createPaddedBinaryString(byte b1, byte b2) {
return "B'" + String.format("%8s", Integer.toBinaryString(b1)).replace(" ", "0") +
String.format("%8s", Integer.toBinaryString(b2)).replace(" ", "0") + "'";
}
@Test
public void test_PE156() throws Throwable {
String sg = checkDDL.getPersistentGroup().getName();
String range = "field_range_PE156";
// make table with range distribution on the binary column
conn.execute("create range " + range + " (binary(36) using 'com.tesora.dve.comparator.UUIDComparator') persistent group " + sg);
conn.execute("create table tablePE156 (a int, b int, c binary(36)) range distribute on (`c`) using " + range);
conn.execute("create table tablePE156_redist1 (a int, b int, c binary(36)) random distribute");
conn.execute("create table tablePE156_redist2 (a int, b int, c binary(36)) range distribute on (`c`) using " + range);
// test UUID function
conn.execute("insert into tablePE156 values ( 1, 1, UUID())");
conn.execute("insert into tablePE156 values ( 2, 2, UUID())");
conn.execute("insert into tablePE156 values ( 3, 3, UUID())");
conn.execute("insert into tablePE156 values ( 4, 4, UUID())");
conn.execute("insert into tablePE156 values ( 5, 5, UUID())");
// add a generation
conn.execute(checkDDL.getPersistentGroup().getAddGenerations());
conn.execute("insert into tablePE156 values ( 6, 6, UUID())");
conn.execute("insert into tablePE156 values ( 7, 7, UUID())");
conn.execute("insert into tablePE156 values ( 8, 8, UUID())");
conn.execute("insert into tablePE156 values ( 9, 9, UUID())");
conn.execute("insert into tablePE156 values ( 10, 10, UUID())");
// after inserting new row into the new generation that has site2 all inserts should be at site2
nativeResource.getConnection().execute("use check2_checkdb");
nativeResource.getConnection().assertResults("select count(*) from tablePE156", br(nr,Long.valueOf(5)));
// test the redist
conn.execute("insert into tablePE156_redist1 values ( 1, 1, '11111111-1111-1111-1111-111111111111')");
conn.execute("insert into tablePE156_redist1 values ( 2, 2, '22222222-2222-2222-2222-222222222222')");
conn.execute("insert into tablePE156_redist1 values ( 3, 3, '33333333-3333-3333-3333-333333333333')");
conn.execute("insert into tablePE156_redist1 values ( 4, 4, '44444444-4444-4444-4444-444444444444')");
conn.execute("insert into tablePE156_redist1 values ( 5, 5, '55555555-5555-5555-5555-555555555555')");
conn.execute("insert into tablePE156_redist2 values ( 5, 5, '11111111-1111-1111-1111-111111111111')");
conn.execute("insert into tablePE156_redist2 values ( 4, 4, '22222222-2222-2222-2222-222222222222')");
conn.execute("insert into tablePE156_redist2 values ( 3, 3, '33333333-3333-3333-3333-333333333333')");
conn.execute("insert into tablePE156_redist2 values ( 2, 2, '44444444-4444-4444-4444-444444444444')");
conn.execute("insert into tablePE156_redist2 values ( 1, 1, '55555555-5555-5555-5555-555555555555')");
conn.assertResults("select t1.a, t1.b, t2.a, t2.b, t1.c from tablePE156_redist1 t1, tablePE156_redist2 t2 where t1.c=t2.c order by t1.a, t1.b",
br(nr, Integer.valueOf(1), Integer.valueOf(1), Integer.valueOf(5), Integer.valueOf(5), "11111111-1111-1111-1111-111111111111".getBytes(),
nr, Integer.valueOf(2), Integer.valueOf(2), Integer.valueOf(4), Integer.valueOf(4), "22222222-2222-2222-2222-222222222222".getBytes(),
nr, Integer.valueOf(3), Integer.valueOf(3), Integer.valueOf(3), Integer.valueOf(3), "33333333-3333-3333-3333-333333333333".getBytes(),
nr, Integer.valueOf(4), Integer.valueOf(4), Integer.valueOf(2), Integer.valueOf(2), "44444444-4444-4444-4444-444444444444".getBytes(),
nr, Integer.valueOf(5), Integer.valueOf(5), Integer.valueOf(1), Integer.valueOf(1), "55555555-5555-5555-5555-555555555555".getBytes()
));
}
@Test
public void test_PE157() throws Throwable {
conn.execute("create range rangePE157 (binary(36)) persistent group " + checkDDL.getPersistentGroup().getName());
conn.execute("create table tablePE157 (a int, b int, c binary(36)) range distribute on (c) using rangePE157");
conn.execute("insert into tablePE157 values ( 1, 1, '00000000-1000-0000-0101-000000000001')");
conn.assertResults("select c from tablePE157 where a=1", br(nr,"00000000-1000-0000-0101-000000000001".getBytes()));
}
@Test
public void test_PE203() throws Throwable {
conn.execute("create range rangePE203 (binary(36)) persistent group " + checkDDL.getPersistentGroup().getName());
conn.execute("create table tablePE203_ingest (a binary(36)) broadcast distribute");
conn.execute("create table tablePE203 (a binary(36)) range distribute on (a) using rangePE203");
conn.execute("insert into tablePE203_ingest values ('0ae3afab-9d9d-11df-aaf6-1231390955c1'),('0ae3afab-9d9d-11df-aaf6-1231390955c1'),('0ae3afab-9d9d-11df-aaf6-1231390955c1'),('0ae3afab-9d9d-11df-aaf6-1231390955c1')");
conn.execute("insert into tablePE203 select * from tablePE203_ingest;");
long rowCount = 0;
List<String> sites = checkDDL.getPersistentGroup().getPhysicalSiteNames("checkdb");
for(String site : sites) {
rowCount = 0;
ResourceResponse resp = nativeResource.getConnection().execute("select count(*) from " + site + ".tablePE203");
List<ResultRow> rows = resp.getResults();
for(ResultRow row : rows) {
ResultColumn col = row.getResultColumn(1);
rowCount = (Long)col.getColumnValue();
}
if (rowCount == 4) {
break;
}
}
assertTrue("Range distributed table with same key values should be all on same persistent site", rowCount==4);
}
@Test
public void test_PE218() throws Throwable {
conn.execute("create table tablePE218 (a int, b int, c varchar(36)) random distribute");
conn.execute("insert into tablePE218 values ( 1, 1, BINARY '00000000-1000-0000-0101-000000000001')");
conn.assertResults("select c from tablePE218 where a=1", br(nr,"00000000-1000-0000-0101-000000000001"));
conn.assertResults("select a from tablePE218 where c=BINARY '00000000-1000-0000-0101-000000000001'",
br(nr,Integer.valueOf(1)));
conn.assertResults("select BINARY 'something'",
br(nr,"something".getBytes()));
}
}