bdb.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

) 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,

	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;