Drizzled Public API Documentation

replace.inc

00001 # 
00002 # Simple test of the transaction log for testing REPLACE command 
00003 # 
00004 # We create a table then fill it with a few records and then
00005 # issue a few REPLACE statements on it.
00006 #
00007 
00008 --disable_warnings
00009 DROP TABLE IF EXISTS t1, t2;
00010 --enable_warnings
00011 
00012 CREATE TABLE t1 (
00013   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
00014 , padding VARCHAR(200) NOT NULL
00015 ) ENGINE=InnoDB;
00016 
00017 INSERT INTO t1 VALUES (1, "I love testing.");
00018 INSERT INTO t1 VALUES (2, "I hate testing.");
00019 
00020 # This will actually execute an UPDATE for InnoDB, 
00021 # as this is an optimized scenario that can have the
00022 # REPLACE INTO converted into an INSERT ... ON DUPLICATE
00023 # KEY UPDATE.
00024 
00025 REPLACE INTO t1 VALUE (2, "I love testing.");
00026 
00027 DROP TABLE t1;
00028 
00029 CREATE TABLE t1 (
00030   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
00031 , padding VARCHAR(200) NOT NULL
00032 ) ENGINE=InnoDB;
00033 CREATE TABLE t2 (
00034   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
00035 , fk_id INT NOT NULL
00036 , CONSTRAINT fk_t1 FOREIGN KEY (fk_id) REFERENCES t1 (id) ON DELETE CASCADE
00037 ) ENGINE=InnoDB;
00038 
00039 INSERT INTO t1 VALUES (1, "I love testing.");
00040 INSERT INTO t1 VALUES (2, "I hate testing.");
00041 
00042 # Should delete original and insert a new one
00043 # with a different "padding" column value...
00044 
00045 REPLACE INTO t1 VALUE (2, "I love testing.");
00046 
00047 DROP TABLE t2, t1;
00048 
00049 #
00050 # Test when using replace on table with multiple unique keys
00051 # and a primary key of various data types. This tests code
00052 # that, during a REPLACE, deletes multiple conflicting rows.
00053 # (FYI, In the transaction log, there should be 2 DELETEs
00054 # and 1 UPDATE for each of these REPLACE statements.)
00055 #
00056 
00057 create table t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00058 insert into t1 values (1,"a",1,1),(2,"b",2,2),(3,"c",3,3);
00059 replace into t1 values (1,"b",3,4);
00060 select * from t1 order by a;
00061 drop table t1;
00062 
00063 create table t1 (a CHAR(5) NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00064 insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3);
00065 replace into t1 values ("a","b",3,4);
00066 select * from t1 order by a;
00067 drop table t1;
00068 
00069 create table t1 (a DATE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00070 insert into t1 values ("2010-01-01","a",1,1),("2010-02-02","b",2,2),("2010-03-03","c",3,3);
00071 replace into t1 values ("2010-01-01","b",3,4);
00072 select * from t1 order by a;
00073 drop table t1;
00074 
00075 create table t1 (a DOUBLE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00076 insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3);
00077 replace into t1 values (1.1,"b",3,4);
00078 select * from t1 order by a;
00079 drop table t1;
00080 
00081 create table t1 (a FLOAT NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00082 insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3);
00083 replace into t1 values (1.1,"b",3,4);
00084 select * from t1 order by a;
00085 drop table t1;
00086 
00087 create table t1 (a ENUM("a","bb","ccc") NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
00088 insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3);
00089 replace into t1 values ("a","b",3,4);
00090 select * from t1 order by a;
00091 drop table t1;