bdb.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

-- source include/have_bdb.inc

#
# Small basic test with ignore
#

--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
--enable_warnings
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=bdb;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
select id, code, name from t1 order by id;
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
select id, code, name from t1 order by id;

drop table t1;

#
# A bit bigger test
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
) engine=bdb;
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
-- error 1062
update t1 set id=1024 where id=1009; 
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102 order by parent_id,id;
# Here and below the differences in result are caused by difference in 
# floating point calculations performed in BDB handler. 
--replace_result 5 X 6 X
explain select level from t1 where level=1;
--replace_result 5 X 6 X
explain select level,id from t1 where level=1;
--replace_result 5 X 6 X
explain select level,id,parent_id from t1 where level=1;
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
optimize table t1;
show keys from t1;
drop table t1;

#
# Test replace
#

CREATE TABLE t1 (
  gesuchnr int(11) DEFAULT '0' NOT NULL,
  benutzer_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (gesuchnr,benutzer_id)
) engine=BDB;

replace into t1 (gesuchnr,benutzer_id) values (2,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
select * from t1;
drop table t1;

# test for bug in replace with secondary key
create table t1 (id int not null primary key, x int not null, key (x)) engine=bdb;
insert into t1 (id, x) values (1, 1);
replace into t1 (id, x) values (1, 2);
select * from t1;
drop table t1;

#
# test delete using hidden_primary_key
#

create table t1 (a int) engine=bdb;
insert into t1 values (1), (2);
optimize table t1;
delete from t1 where a = 1;
select * from t1;
check table t1;
drop table t1;

create table t1 (a int,b varchar(20)) engine=bdb;
insert into t1 values (1,""), (2,"testing");
delete from t1 where a = 1;
select * from t1;
create index skr on t1 (a);
insert into t1 values (3,""), (4,"testing");
analyze table t1;
show keys from t1;
drop table t1;

# Test of reading on secondary key with may be null

create table t1 (a int,b varchar(20),key(a)) engine=bdb;
insert into t1 values (1,""), (2,"testing");
select * from t1 where a = 1;
drop table t1;

#
# Test auto_increment on sub key
#

create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) engine=BDB;
insert into t1 values ("a",1),("b",2),("a",2),("c",1);
insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
insert into t1 (a) values ("a"),("b"),("c"),("d");
insert into t1 (a) values ('k'),('d');
insert into t1 (a) values ("a");
insert into t1 values ("d",last_insert_id());
select * from t1;
flush tables;
select count(*) from t1;
drop table t1;

#
# Test rollback
#

create table t1 (n int not null primary key) engine=bdb;
set autocommit=0;
insert into t1 values (4);
rollback;
select n, "after rollback" from t1;
insert into t1 values (4);
commit;
select n, "after commit" from t1;
commit;
insert into t1 values (5);
-- error 1062
insert into t1 values (4);
commit;
select n, "after commit" from t1;
set autocommit=1;
insert into t1 values (6);
-- error 1062
insert into t1 values (4);
select n from t1;
# nop
rollback;
drop table t1;

#
# Testing transactions
#

create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB;
begin;
insert into t1 values(1,'hamdouni');
select id as afterbegin_id,nom as afterbegin_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=0;
insert into t1 values(2,'mysql');
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
rollback;
select id as afterrollback_id,nom as afterrollback_nom from t1;
set autocommit=1;
drop table t1;

#
# Simple not autocommit test
# 

CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb;
insert into t1 values ('pippo', 12);
-- error 1062
insert into t1 values ('pippo', 12); # Gives error
delete from t1;
delete from t1 where id = 'pippo';
select * from t1;

insert into t1 values ('pippo', 12);
set autocommit=0;
delete from t1;
rollback;
select * from t1;
delete from t1;
commit;
select * from t1;
drop table t1;
set autocommit=1;

#
# The following simple tests failed at some point
#

CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=BDB;
INSERT INTO t1 VALUES (1, 'Jochen');
select * from t1;
drop table t1;

CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=BDB;
set autocommit=0;
INSERT INTO t1  SET _userid='[e-mail]';
COMMIT;
SELECT * FROM t1;
SELECT _userid FROM t1 WHERE _userid='[e-mail]';
drop table t1;
set autocommit=1;

#
# Test when reading on part of unique key
#
CREATE TABLE t1 (
  user_id int(10) DEFAULT '0' NOT NULL,
  name varchar(100),
  phone varchar(100),
  ref_email varchar(100) DEFAULT '' NOT NULL,
  detail varchar(200),
  PRIMARY KEY (user_id,ref_email)
)engine=bdb;

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','[e-mail]','xxx'),(10292,'shirish','2333604','[e-mail]','ddsds'),(10292,'sonali','323232','[e-mail]','filmstar');
select * from t1 where user_id=10292;
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','[e-mail]','xxx'),(10293,'shirish','2333604','[e-mail]','ddsds');
select * from t1 where user_id=10292;
select * from t1 where user_id>=10292;
select * from t1 where user_id>10292;
select * from t1 where user_id<10292;
drop table t1;

#
# Test that keys are created in right order
#

CREATE TABLE t1 (a int not null, b int not null,c int not null,
key(a),primary key(a,b), unique(c),key(a),unique(b));
show index from t1;
drop table t1;

#
# Test of ALTER TABLE and BDB tables
#

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
alter table t1 engine=BDB;
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
select * from t1;
update t1 set col2='7' where col1='4';
select * from t1;
alter table t1 add co3 int not null;
select * from t1;
update t1 set col2='9' where col1='2';
select * from t1;
drop table t1;

#
# INSERT INTO BDB tables
#

create table t1 (a int not null , b int, primary key (a)) engine = BDB;
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
insert into t1 VALUES (1,3) , (2,3), (3,3);
select * from t1;
insert into t2 select * from t1;
select * from t2;
delete from t1 where b = 3;
select * from t1;
insert into t1 select * from t2;
select * from t1;
select * from t2;
drop table t1,t2;

#
# Search on unique key
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  ggid varchar(32) binary DEFAULT '' NOT NULL,
  email varchar(64) DEFAULT '' NOT NULL,
  passwd varchar(32) binary DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  UNIQUE ggid (ggid)
) ENGINE=BDB;

insert into t1 (ggid,passwd) values ('test1','xxx');
insert into t1 (ggid,passwd) values ('test2','yyy');
-- error 1062
insert into t1 (ggid,passwd) values ('test2','this will fail');
-- error 1062
insert into t1 (ggid,id) values ('this will fail',1);

select * from t1 where ggid='test1';
select * from t1 where passwd='xxx';
select * from t1 where id=2;

replace into t1 (ggid,id) values ('this will work',1);
replace into t1 (ggid,passwd) values ('test2','this will work');
-- error 1062
update t1 set id=100,ggid='test2' where id=1;
select * from t1;
select * from t1 where id=1;
select * from t1 where id=999;
drop table t1;

#
# ORDER BY on not primary key
#

CREATE TABLE t1 (
  user_name varchar(12),
  password text,
  subscribed char(1),
  user_id int(11) DEFAULT '0' NOT NULL,
  quota bigint(20),
  weight double,
  access_date date,
  access_time time,
  approved datetime,
  dummy_primary_key int(11) NOT NULL auto_increment,
  PRIMARY KEY (dummy_primary_key)
) ENGINE=BDB;
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
drop table t1;

#
# Testing of tables without primary keys
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
) engine=bdb;
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
INSERT INTO t1 values (179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
update t1 set id=id+1000;
update t1 set id=1024 where id=1009; 
select * from t1;
update ignore t1 set id=id+1; # This will change all rows
select * from t1;
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
--replace_result 5 X 6 X
explain select level from t1 where level=1;
select level,id from t1 where level=1;
select level,id,parent_id from t1 where level=1;
select level,id from t1 where level=1 order by id;
delete from t1 where level=1;
select * from t1;
drop table t1;

#
# Test of index only reads
#
CREATE TABLE t1 (
   sca_code char(6) NOT NULL,
   cat_code char(6) NOT NULL,
   sca_desc varchar(50),
   lan_code char(2) NOT NULL,
   sca_pic varchar(100),
   sca_sdesc varchar(50),
   sca_sch_desc varchar(16),
   PRIMARY KEY (sca_code, cat_code, lan_code),
   INDEX sca_pic (sca_pic)
) engine = bdb ;

INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
select count(*) from t1 where sca_code = 'PD';
select count(*) from t1 where sca_code <= 'PD';
select count(*) from t1 where sca_pic is null;
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where cat_code='E';

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
select count(*) from t1 where sca_pic >= 'n';
select sca_pic from t1 where sca_pic is null;
update t1 set sca_pic="test" where sca_pic is null;
delete from t1 where sca_code='pd';
drop table t1;

#
# Test of opening table twice and timestamps
#
set @a:=now();
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb;
insert into t1 (a) values(1),(2),(3);
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
select a from t1 natural join t1 as t2 where b >= @a order by a;
update t1 set a=5 where a=1;
select a from t1;
drop table t1;

#
# Test flushing of berkeley DB logs
#
flush logs;

#
# Test key on blob with null values
#
create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))) engine=bdb;
insert into t1 values ('this is a blob', 1), (null, -1), (null, null),("",1),("",2),("",3);
select b from t1 where b = 'this is a blob';
select * from t1 where b like 't%';
select b, i from t1 where b is not null;
select * from t1 where b is null and i > 0;
select * from t1 where i is NULL;
update t1 set b='updated' where i=1;
select * from t1;
drop table t1;

#
# Test with variable length primary key
#
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=bdb;
insert into t1 values("hello",1),("world",2);
select * from t1 order by b desc;
optimize table t1;
show keys from t1;
drop table t1;

#
# Test of bug in create index with NULL columns
#
create table t1 (i int, j int )ENGINE=BDB;
insert into t1 values (1,2);
select * from t1 where i=1 and j=2;
create index ax1 on t1 (i,j);
select * from t1 where i=1 and j=2;
drop table t1;

#
# Test of with CONST tables and TEXT columns
# This gave a wrong result because the row information was freed too early
#

create table t1
(
	branch_id	int		auto_increment	primary key,
	branch_name	varchar(255)	not null,
	branch_active	int		not null 	default 1,

	unique  branch_name(branch_name),
	index	branch_active(branch_active)
) engine=bdb;
create table t2
(
	target_id	int		auto_increment	primary key,
	target_name	varchar(255)	not null,
	target_active	int		not null 	default 1,

	unique	target_name(target_name),
	index	target_active(target_active)
) engine=bdb;
create table t3
(
	platform_id	int		auto_increment	primary key,
	platform_name	varchar(255)	not null,
	platform_active	int		not null 	default 1,

	unique	platform_name(platform_name),
	index	platform_active(platform_active)
) engine=bdb;
create table t4
(
	product_id	int		auto_increment	primary key,
	product_name	varchar(255)	not null,
	version_file	varchar(255)	not null,
	product_active	int		not null 	default 1,

	unique	product_name(product_name),
	index	product_active(product_active)
) engine=bdb;
create table t5
(
	product_file_id		int		auto_increment	primary key,
	product_id		int		not null,
	file_name		varchar(255)	not null,
	/* cvs module used to find the file version */
	module_name		varchar(255)	not null,
	/* flag whether the file is still included in the product */
	file_included		int		not null	default 1,

	unique	product_file(product_id,file_name),
	index	file_included(file_included)
) engine=bdb;
create table t6
(
	file_platform_id	int		auto_increment	primary key,
	product_file_id		int		not null,
	platform_id		int		not null,
	branch_id		int		not null,
	/* filename in the build system */
	build_filename		varchar(255)	not null,
	/* default filename in the build archive */
	archive_filename	varchar(255)	not null,