bdb.test

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

	unique  file_platform(product_file_id,platform_id,branch_id)
) engine=bdb;
create table t8
(
	archive_id	int		auto_increment	primary key,
	branch_id	int		not null,
	target_id	int		not null,
	platform_id	int		not null,
	product_id	int		not null,
	status_id	int		not null	default 1,

	unique  archive(branch_id,target_id,platform_id,product_id),
	index	status_id(status_id)
) engine=bdb;
create table t7
(
	build_id	int		auto_increment	primary key,
	branch_id	int		not null,
	target_id	int		not null,
	build_number	int		not null,
	build_date	date		not null,
	/* build system tag, e.g. 'rmanight-022301-1779' */
	build_tag	varchar(255)	not null,	
	/* path relative to the build archive root, e.g. 'current' */
	build_path	text		not null,	

	unique  build(branch_id,target_id,build_number)
) engine=bdb;

insert into t1 (branch_name)
values ('RealMedia');
insert into t1 (branch_name)
values ('RP8REV');
insert into t1 (branch_name)
values ('SERVER_8_0_GOLD');

insert into t2 (target_name)
values ('rmanight');
insert into t2 (target_name)
values ('playerall');
insert into t2 (target_name)
values ('servproxyall');

insert into t3 (platform_name)
values ('linux-2.0-libc6-i386');
insert into t3 (platform_name)
values ('win32-i386');

insert into t4 (product_name, version_file)
values ('realserver', 'servinst');
insert into t4 (product_name, version_file)
values ('realproxy', 'prxyinst');
insert into t4 (product_name, version_file)
values ('realplayer', 'playinst');
insert into t4 (product_name, version_file)
values ('plusplayer', 'plusinst');

create temporary table tmp1
        select branch_id, target_id, platform_id, product_id
        from t1, t2, t3, t4 ;
create temporary table tmp2 
        select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id 
        from tmp1 left join t8 
        using (branch_id,target_id,platform_id,product_id) 
        where t8.archive_id is null ;
insert into t8 
        (branch_id, target_id, platform_id, product_id, status_id)
        select branch_id, target_id, platform_id, product_id, 1
        from tmp2 ;
drop table tmp1 ;
drop table tmp2 ;

insert into t5 (product_id, file_name, module_name)
values (1, 'servinst', 'server');

insert into t5 (product_id, file_name, module_name)
values (2, 'prxyinst', 'server');

insert into t5 (product_id, file_name, module_name)
values (3, 'playinst', 'rpapp');

insert into t5 (product_id, file_name, module_name)
values (4, 'plusinst', 'rpapp');

insert into t6 
(product_file_id,platform_id,branch_id,build_filename,archive_filename)
values (1, 2, 3, 'servinst.exe', 'win32-servinst.exe');

insert into t6 
(product_file_id,platform_id,branch_id,build_filename,archive_filename)
values (1, 1, 3, 'v80_linux-2.0-libc6-i386_servinst.bin', 'linux2-servinst.exe');

insert into t6 
(product_file_id,platform_id,branch_id,build_filename,archive_filename)
values (3, 2, 2, 'playinst.exe', 'win32-playinst.exe');

insert into t6 
(product_file_id,platform_id,branch_id,build_filename,archive_filename)
values (4, 2, 2, 'playinst.exe', 'win32-playinst.exe');

insert into t7 
(branch_id,target_id,build_number,build_tag,build_date,build_path)
values (2, 2, 1071, 'playerall-022101-1071', '2001-02-21', 'current');

insert into t7 
(branch_id,target_id,build_number,build_tag,build_date,build_path)
values (2, 2, 1072, 'playerall-022201-1072', '2001-02-22', 'current');

insert into t7 
(branch_id,target_id,build_number,build_tag,build_date,build_path)
values (3, 3, 388, 'servproxyall-022201-388', '2001-02-22', 'current');

insert into t7 
(branch_id,target_id,build_number,build_tag,build_date,build_path)
values (3, 3, 389, 'servproxyall-022301-389', '2001-02-23', 'current');

insert into t7 
(branch_id,target_id,build_number,build_tag,build_date,build_path)
values (4, 4, 100, 'foo target-010101-100', '2001-01-01', 'current');

update t8
set status_id=2
where branch_id=2 and target_id=2 and platform_id=2 and product_id=1;

select t7.build_path
from 
    t1, 
    t7, 
    t2, 
    t3, 
    t4,
    t5, 
    t6
where 
    t7.branch_id = t1.branch_id and 
    t7.target_id = t2.target_id and 
    t5.product_id = t4.product_id and
    t6.product_file_id = t5.product_file_id and
    t6.platform_id = t3.platform_id and
    t6.branch_id = t6.branch_id and
    t7.build_id = 1 and
    t4.product_id = 3 and
    t5.file_name = 'playinst' and
    t3.platform_id = 2;

drop table t1, t2, t3, t4, t5, t6, t7, t8;

#
# Test with blob + tinyint key
#

CREATE TABLE t1 (
  a tinytext NOT NULL,
  b tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY (a(32),b)
) ENGINE=BDB;
INSERT INTO t1 VALUES ('a',1),('a',2);
SELECT * FROM t1 WHERE a='a' AND b=2;
SELECT * FROM t1 WHERE a='a' AND b in (2);
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
drop table t1;

#
# Test min-max optimization
#

CREATE TABLE t1 (
  a int3 unsigned NOT NULL,
  b int1 unsigned NOT NULL,
  UNIQUE (a, b)
) ENGINE = BDB;
 
INSERT INTO t1 VALUES (1, 1);
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
drop table t1;

#
# Test problem with BDB and lock tables with duplicate write.
#

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb;
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
--error 1062
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
select id from t1;
UNLOCK TABLES;
DROP TABLE t1;

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb;
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
begin;
--error 1062
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
select id from t1;
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
commit;
select id,id3 from t1;
UNLOCK TABLES;
DROP TABLE t1;

#
# Test with empty tables (crashed with lock error)
#

CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY  (SYAIN_NO,KINMU_DATE)) ENGINE=BerkeleyDB;
CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY  (SYAIN_NO,STR_DATE) ) ENGINE=BerkeleyDB;
select T1.KINMU_DATE from t1 T1 ,t2 T2 where  T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO;
select T1.KINMU_DATE from t1 T1 ,t2 T2 where  T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO;
DROP TABLE t1,t2;

#
# Test problem with joining table to itself on a multi-part unique key
#

create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) engine=bdb;
insert into t1 values (1,1), (1,2);
select * from t1 where a = 1;
select t1.*, t2.* from t1, t1 t2 where t1.a = t2.a and t2.a = 1;
select * from t1 where a = 1;
drop table t1;

#
# This caused a deadlock in BDB internal locks
#

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=bdb;
insert into t1 values (0,0,0,'ABCDEFGHIJ');
create table t2 (id int NOT NULL,primary key (id)) engine=bdb;
LOCK TABLES t1 WRITE, t2 WRITE;
insert into t2 values(1);
SELECT t1.* FROM t1 WHERE id IN (1);
SELECT t1.* FROM t2 left outer join t1 on (t1.id=t2.id);
delete from t1 where id3 >= 0 and id3 <= 0;
drop table t1,t2;

#
# Test problems with NULL
#

CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY  (i), UNIQUE(p,s)) ENGINE=BDB;
INSERT INTO t1 VALUES ('00000000-e6c4ddeaa6-003b8-83458387','programs/xxxxxxxx.wmv','00000000-e6c4ddeb32-003bc-83458387');
SELECT * FROM t1 WHERE p='programs/xxxxxxxx.wmv';
drop table t1;

#
# Test problem which gave error 'Can't find record in 't1''
#

CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY  (STR_DATE) ) ENGINE=BerkeleyDB;
select INFO_NOTE from t1 where STR_DATE = '20010610';
select INFO_NOTE from t1 where STR_DATE < '20010610';
select INFO_NOTE from t1 where STR_DATE > '20010610';
drop table t1;

#
# Test problem with multi table delete which quickly shows up with 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 =bdb;
insert into t1 values (2, 3),(1, 7),(10, 7);
insert into t2 values (2, 3),(1, 7),(10, 7);
select * from t1;
select * from t2;
delete t1, t2 from t1, t2 where t1.a = t2.a;
select * from t1;
select * from t2;
select * from t2;
drop table t1,t2;

#
# The bug #971
#

create table t1  (x int not null, index(x)) engine=bdb;
insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
select * from t1 where x <= 10 and x >= 7;
select * from t1 where x <= 10 and x >= 7 order by x;
select * from t1 where x <= 10 and x >= 7 order by x desc;
select * from t1 where x <= 8 and x >= 5 order by x desc;
select * from t1 where x < 8 and x > 5 order by x desc;
drop table t1;

#
# Test of multi-table-updates (bug #1980).
#

create table t1 ( c char(8) not null ) engine=bdb;
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=bdb;
insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
drop table t1,t2;

#
# Test index only read (Bug #2509)
#
create table t1 (a char(10), key(a), b int not null, key(b)) engine=bdb;
insert into t1 values ('a',1),('A',2);
explain select a from t1;
select a from t1;
explain select b from t1;
select b from t1;
alter table t1 modify a char(10) binary;
explain select a from t1;
select a from t1;
drop table t1;

#
# bug#2686 - index_merge select on BerkeleyDB table with varchar PK crashes
#

create table t1(
  pk1 text not null, pk2 text not null, pk3 char(4),
  key1 int, key2 int,
  primary key(pk1(4), pk2(4), pk3), key(key1), key(key2)
) engine=bdb;
insert into t1 values (concat('aaa-', repeat('A', 4000)),
  concat('eee-', repeat('e', 4000)), 'a++a', 1, 1);
insert into t1 values (concat('bbb-', repeat('B', 4000)),
  concat('ggg-', repeat('G', 4000)), 'b++b', 1, 1);
select substring(pk1, 1, 4), substring(pk1, 4001),
       substring(pk2, 1, 4), substring(pk2, 4001), pk3, key1, key2
       from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
drop table t1;

#
# bug#2688 - Wrong index_merge query results for BDB table with
# variable length primary key
#

create table t1 (
  pk1 varchar(8) not null default '',
  pk2 varchar(4) not null default '',
  key1 int(11) default null,
  key2 int(11) default null,
  primary key  (pk1,pk2),
  key key1 (key1),
  key key2 (key2)) engine=bdb;
insert into t1 values ('','empt',2,2),  ('a','a--a',2,2),
  ('bb','b--b',2,2),  ('ccc','c--c',2,2),  ('dddd','d--d',2,2);
select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3;
drop table t1;


#
# Bug #4000: problem with active cursor.
#

set autocommit=0;
create table t1(b varchar(30)) engine=bdb;
insert into t1 values ('one');
commit;
select b FROM t1 outer_table where
exists (select 'two' from t1 where 'two' = outer_table.b);
drop table t1;
set autocommit=1;

#
# Bug #4089: subselect and open cursor.
#

create table t1(a int primary key, b varchar(30)) engine=bdb;
insert into t1 values (1,'one'), (2,'two'), (3,'three'), (4,'four');
create table t2 like t1;
insert t2 select * from t1;
select a from t1 where a in (select a from t2);
delete from t2;
insert into t2 (a, b)
  select a, b from t1 where (a, b) in (select a, b from t1);
select * from t2;
drop table t1, t2;

#
# Bug #4304: TRUNCATE <table of type BDB> , wrong result
#

create table t1 (a int, b varchar(30), primary key(a)) engine = bdb;
insert into t1 values (1,'one');
commit;
truncate t1;
select * from t1;
drop table t1;

#
# Check that BDB works fine with a string which is
# longer than 255 bytes for multibyte characters.
#
SET NAMES utf8;
create table t1 (a varchar(255) character set utf8) engine=bdb;
set @a:= convert(repeat(_latin1 0xFF, 255) using utf8);
insert into t1 values (@a);
select a, length(a), char_length(a) from t1;
drop table t1;
SET NAMES latin1;

#
# Bug #5832 SELECT doesn't return records in some cases
#
CREATE TABLE t1 (
    id       int unsigned NOT NULL auto_increment,
    list_id  smallint unsigned NOT NULL,
    term     TEXT NOT NULL,
    PRIMARY KEY(id),
    INDEX(list_id, term(4))
) ENGINE=BDB CHARSET=utf8;
INSERT INTO t1 SET list_id = 1, term = "letterc";
INSERT INTO t1 SET list_id = 1, term = "letterb";
INSERT INTO t1 SET list_id = 1, term = "lettera";
INSERT INTO t1 SET list_id = 1, term = "letterd";
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
DROP TABLE t1;

#
# Bug #15536: Crash when DELETE with subquery using BDB tables
#
create table t1 (a int, key(a)) engine=bdb;
create table t2 (b int, key(b)) engine=bdb;
insert into t1 values (1),(1),(2),(3),(4);
insert into t2 values (1),(5),(6),(7);
delete from t1 where (a in (select b from t2));
select count(*) from t1;
# INSERT also blows up
--error 1242
insert into t1 set a=(select b from t2);
select count(*) from t1;
# UPDATE also blows up
update t1 set a = a + 1 where (a in (select b from t2));
select count(*) from t1;
drop table t1, t2;

--echo End of 4.1 tests

#
# alter temp table
#
create temporary table t1 (a int, primary key(a)) engine=bdb;
select * from t1;
alter table t1 add b int;
select * from t1;
drop table t1;


#
# Test varchar
#

let $default=`select @@storage_engine`;
set storage_engine=bdb;
source include/varchar.inc;

#
# Some errors/warnings on create
#

--replace_result 1024 MAX_KEY_LENGTH 3072 MAX_KEY_LENGTH
create table t1 (v varchar(65530), key(v));
drop table if exists t1;
create table t1 (v varchar(65536));
show create table t1;
drop table t1;
create table t1 (v varchar(65530) character set utf8);
show create table t1;
drop table t1;

# End varchar test
eval set storage_engine=$default;

#
# Test that we can create a large key
#
create table t1 (a varchar(255) character set utf8,
                 b varchar(255) character set utf8,
                 c varchar(255) character set utf8,
                 d varchar(255) character set utf8,
                 key (a,b,c,d)) engine=bdb;
drop table t1;
--error ER_TOO_LONG_KEY
create table t1 (a varchar(255) character set utf8,
                 b varchar(255) character set utf8,
                 c varchar(255) character set utf8,
                 d varchar(255) character set utf8,
                 e varchar(255) character set utf8,
                 key (a,b,c,d,e)) engine=bdb;

#
# Bug #14212: Server crash after COMMIT + ALTER TABLE
#
set autocommit=0;
create table t1 (a int) engine=bdb;
commit;
alter table t1 add primary key(a);
drop table t1;

--echo End of 5.0 tests