~drizzle-trunk/drizzle/development

drop table if exists t1, t2;
create table t1 (a int, b double, c float) engine = blitzdb;
create table t2 (a int, b text, c blob) engine = blitzdb;
create table t1 (a int, b double, c float) engine = blitzdb;
ERROR 42S01: Table 'test.t1' already exists
create table t2 (a int, b text, c blob) engine = blitzdb;
ERROR 42S01: Table 'test.t2' already exists
create table t1 (a int) engine = blitzdb;
ERROR 42S01: Table 'test.t1' already exists
create table t2 (a int) engine = blitzdb;
ERROR 42S01: Table 'test.t2' already exists
show tables;
Tables_in_test
t1
t2
drop table t1, t2;
create table t1 (a int, b int, c varchar(255)) engine = blitzdb;
insert into t1 values (1, 8, "one");
select * from t1;
a	b	c
1	8	one
insert into t1 values (2, 7, "two");
insert into t1 values (3, 6, "three");
insert into t1 values (4, 5, "four");
insert into t1 values (5, 4, "five");
insert into t1 values (6, 3, "six");
insert into t1 values (7, 2, "seven");
insert into t1 values (8, 1, "eight");
select count(*) from t1;
count(*)
8
select c from t1 where a = 4;
c
four
select c from t1 where a > 5;
c
six
seven
eight
select * from t1;
a	b	c
1	8	one
2	7	two
3	6	three
4	5	four
5	4	five
6	3	six
7	2	seven
8	1	eight
select * from t1 where a = 1 or b = 1;
a	b	c
1	8	one
8	1	eight
select substring(c, 1, 3) from t1;
substring(c, 1, 3)
one
two
thr
fou
fiv
six
sev
eig
select substring(c, 1, 2) from t1;
substring(c, 1, 2)
on
tw
th
fo
fi
si
se
ei
select substring(c, 1, 1) from t1;
substring(c, 1, 1)
o
t
t
f
f
s
s
e
update t1 set c = "first half" where a <= 4;
update t1 set c = "second half" where a > 4;
select * from t1;
a	b	c
1	8	first half
2	7	first half
3	6	first half
4	5	first half
5	4	second half
6	3	second half
7	2	second half
8	1	second half
create table t2 (a int, b int, c varchar(255))
engine = blitzdb (select * from t1);
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` INT DEFAULT NULL,
  `b` INT DEFAULT NULL,
  `c` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=BLITZDB COLLATE = utf8_general_ci
select count(*) from t2;
count(*)
8
delete from t1 where c = "first half";
delete from t1 where c = "second half";
select count(*) from t1;
count(*)
0
drop table t1;
rename table t2 to t1;
select * from t1;
a	b	c
1	8	first half
2	7	first half
3	6	first half
4	5	first half
5	4	second half
6	3	second half
7	2	second half
8	1	second half
drop table t1;
create table t1 (id int) engine = blitzdb;
insert into t1 values (100), (100);
update t1 set id = id+1 ORDER BY id LIMIT 2;
update t1 set id = id+1 ORDER BY id LIMIT 2;
update t1 set id = id+1 ORDER BY id LIMIT 2;
update t1 set id = id+1 ORDER BY id LIMIT 2;
select * from t1;
id
104
104
drop table t1;
create table t1 (a int) engine = blitzdb;
insert into t1 values (1), (2), (3), (4);
alter table t1 add b int;
select * from t1;
a	b
1	NULL
2	NULL
3	NULL
4	NULL
update t1 set b = 1 where a = 1;
update t1 set b = 2 where a = 2;
update t1 set b = 3 where a = 3;
update t1 set b = 4 where a = 4;
select * from t1;
a	b
1	1
2	2
3	3
4	4
alter table t1 add c text;
update t1 set c = "added column" where a = 1;
update t1 set c = "added column" where a = 2;
update t1 set c = "added column" where a = 3;
update t1 set c = "added column" where a = 4;
select * from t1;
a	b	c
1	1	added column
2	2	added column
3	3	added column
4	4	added column
drop table t1;
create table t1 (name varchar(32), point int) engine = blitzdb;
insert into t1 values ('aaa', 10);
insert into t1 values ('bbb', 20);
insert into t1 values ('ccc', 30);
insert into t1 values ('aaa', 10);
insert into t1 values ('bbb', 20);
insert into t1 values ('ccc', 30);
insert into t1 values ('aaa', 10);
insert into t1 values ('bbb', 20);
insert into t1 values ('ccc', 30);
select * from t1;
name	point
aaa	10
bbb	20
ccc	30
aaa	10
bbb	20
ccc	30
aaa	10
bbb	20
ccc	30
select name, sum(point) from t1 group by name;
name	sum(point)
aaa	30
bbb	60
ccc	90
drop table t1;
create table t1 (string varchar(255)) engine = blitzdb;
insert into t1 values('accompany'), ('balcony'), ('bunny'), ('company');
insert into t1 values('accompanied'), ('amazed'), ('busted'), ('decreased');
insert into t1 values('achiever'), ('blender'), ('ether'), ('launcher');
insert into t1 values('ampersand'), ('compound'), ('comprehend'), ('wand');
select * from t1 where string like '%ny';
string
accompany
balcony
bunny
company
select * from t1 where string like '%ed';
string
accompanied
amazed
busted
decreased
select * from t1 where string like '%er';
string
achiever
blender
ether
launcher
select * from t1 where string like '%nd';
string
ampersand
compound
comprehend
wand
select * from t1 where string like 'a%';
string
accompany
accompanied
amazed
achiever
ampersand
select * from t1 where string like 'ac%';
string
accompany
accompanied
achiever
select * from t1 where string like 'acc%';
string
accompany
accompanied
select * from t1 where string like '____';
string
wand
select * from t1 where string like '_____';
string
bunny
ether
select * from t1 where string like '___________';
string
accompanied
drop table t1;
create table t1 (a int not null, b int) engine = blitzdb;
insert into t1 values (1, NULL), (2, NULL), (3, NULL), (4, NULL);
insert into t1 values (5, NULL), (6, NULL), (7, NULL), (8, NULL);
insert into t1 values (NULL, 1);
ERROR 23000: Column 'a' cannot be null
select * from t1;
a	b
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL
6	NULL
7	NULL
8	NULL
select * from t1 where a is null;
a	b
select * from t1 where b is null;
a	b
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL
6	NULL
7	NULL
8	NULL
drop table t1;
create table t1 (name varchar(64), dob date) engine = blitzdb;
insert into t1 values ('Bernstein', '1971-10-29');
insert into t1 values ('Codd', '1923-08-23');
insert into t1 values ('Lovelace', '1815-12-10');
insert into t1 values ('Tower', '1949-06-17');
insert into t1 values ('Turing', '1912-06-23');
insert into t1 values ('Thompson', '1943-02-04');
select * from t1;
name	dob
Bernstein	1971-10-29
Codd	1923-08-23
Lovelace	1815-12-10
Tower	1949-06-17
Turing	1912-06-23
Thompson	1943-02-04
select name from t1 order by dob;
name
Lovelace
Turing
Codd
Thompson
Tower
Bernstein
select name from t1 order by dob desc;
name
Bernstein
Tower
Thompson
Codd
Turing
Lovelace
select name from t1 where dob < '1900-01-01';
name
Lovelace
select name from t1 where dob > '1950-01-01';
name
Bernstein
select name from t1 where dob = '1943-02-04';
name
Thompson
drop table t1;
create table t1 (a int) engine = blitzdb;
insert into t1 values(2147483649);
ERROR 22003: Out of range value for column 'a' at row 1
insert into t1 values(-2147483649);
ERROR 22003: Out of range value for column 'a' at row 1
insert into t1 values(2147483647);
select * from t1;
a
2147483647
drop table t1;
create table t1 (a varchar(32)) engine = blitzdb;
insert into t1 select repeat('x', 4);
insert into t1 select repeat('x', 6);
insert into t1 select repeat('x', 8);
insert into t1 select repeat('x', 16);
insert into t1 select repeat('x', 32);
insert into t1 select repeat('x', 33);
ERROR 22001: Data too long for column 'a' at row 1
select * from t1;
a
xxxx
xxxxxx
xxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
create table t2 (a varchar(16383)) engine = blitzdb;
insert into t2 select repeat('x', 16383);
insert into t2 select repeat('x', 16384);
ERROR 22001: Data too long for column 'a' at row 1
select count(*) from t2;
count(*)
1
drop table t1, t2;
create table t1 (a varchar(32)) engine = blitzdb;
insert into t1 select repeat('あ', 8);
insert into t1 select repeat('あ', 32);
insert into t1 select repeat('あ', 33);
ERROR 22001: Data too long for column 'a' at row 1
select * from t1;
a
ああああああああ
ああああああああああああああああああああああああああああああああ
create table t2 (a varchar(16383)) engine = blitzdb;
insert into t2 select repeat('値', 16383);
insert into t2 select repeat('値', 16384);
ERROR 22001: Data too long for column 'a' at row 1
select count(*) from t2;
count(*)
1
drop table t1, t2;
create table t1 (a int, b varchar(255)) engine = blitzdb;
insert into t1 values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
insert into t1 values (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight');
create table t2 (a int, b varchar(255), c double) engine = blitzdb;
insert into t2 (a, b) select a, b from t1 where a > 4;
insert into t2 (a) select a from t1 where a = 1;
select * from t2;
a	b	c
5	five	NULL
6	six	NULL
7	seven	NULL
8	eight	NULL
1	NULL	NULL
delete from t2;
insert into t2 (a, b) select * from t1;
select * from t2;
a	b	c
1	one	NULL
2	two	NULL
3	three	NULL
4	four	NULL
5	five	NULL
6	six	NULL
7	seven	NULL
8	eight	NULL
drop table t1;
create table t1 (a int, b varchar(2048)) engine = blitzdb;
insert into t1 values (1, 'abcdefghijklmn');
insert into t1 values (1, 'abcdefghijklmn');
insert into t1 values (1, 'abcdefghijklmn');
insert into t1 values (1, 'abcdefghijklmn');
select * from t1;
a	b
1	abcdefghijklmn
1	abcdefghijklmn
1	abcdefghijklmn
1	abcdefghijklmn
drop table t1;