tables.sql

C++连接一写常用数据库的接口

# dbConnect MySQL database example(s) and testing tables
# Copyright (C) 2003 Johnathan Ingram, [e-mail]
#
# This library is free software; you can redistribute it and/or
#   modify it under the terms of the GNU Lesser General Public
#   License as published by the Free Software Foundation; either
#   version 2.1 of the License, or (at your option) any later version.
#
#   This library is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
#   Lesser General Public License for more details.
#
#   You should have received a copy of the GNU Lesser General Public
#   License along with this library; if not, write to the Free Software
#   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
#
#   Note: To create execute `cat ./tables.sql | mysql -DdbConnectDB -udbconnect -pletmein`
#
USE dbConnectDB;


# The following table lists the types that the API has been tested with
#  MySQL Types Testing Table.
#  INTEGER        is a synonym for INT
#  REAL           is a synonym for DOUBLE
#  NUMERIC        is a synonym for DECIMAL
#  TINYTEXT       is the same as TINYBLOB
#  TEXT           is the same as BLOB

DROP TABLE IF EXISTS TypeTest;

# -------------------------------------------------------
CREATE TABLE TypeTest
(
   id                      INT          NOT NULL AUTO_INCREMENT,
   description             VARCHAR(100) NOT NULL,
                                   
   t_TINYINT               TINYINT,
   t_SMALLINT              SMALLINT,
   t_MEDIUMINT             MEDIUMINT,
   t_INT                   INT,
   t_BIGINT                BIGINT,

   t_TINYINT_UNSIGNED      TINYINT   UNSIGNED,
   t_SMALLINT_UNSIGNED     SMALLINT  UNSIGNED,
   t_MEDIUMINT_UNSIGNED    MEDIUMINT UNSIGNED,
   t_INT_UNSIGNED          INT       UNSIGNED,
   t_BIGINT_UNSIGNED       BIGINT    UNSIGNED,

   t_FLOAT                 FLOAT,
   t_DOUBLE                DOUBLE,
   t_DOUBLE_PRECISION      DOUBLE PRECISION,
   t_DECIMAL               DECIMAL(10,5),

   t_DATE                  DATE,
   t_DATETIME              DATETIME,
   t_TIMESTAMP             TIMESTAMP,
   t_TIME                  TIME,
   t_YEAR                  YEAR,

   t_CHAR                  CHAR(100),
   t_VARCHAR               VARCHAR(100),

   t_TINYBLOB              TINYBLOB,
   t_BLOB                  BLOB,
   t_LONGBLOB              LONGBLOB,

   t_ENUM                  ENUM("one", "two", "three"),
   t_SET                   SET("a", "b", "c", "d"),

   PRIMARY KEY(id)
) 
TYPE=INNODB;

INSERT INTO TypeTest 
   (id, description, t_TINYINT, t_SMALLINT, t_MEDIUMINT, t_INT, t_BIGINT) 
VALUES
   (NULL, "1: Integer Types", -128, 32767, -8388608, 2147483647, -9223372036854775808);

INSERT INTO TypeTest 
   (id, description, t_TINYINT_UNSIGNED, t_SMALLINT_UNSIGNED, t_MEDIUMINT_UNSIGNED, t_INT_UNSIGNED, t_BIGINT_UNSIGNED) 
VALUES
   (NULL, "2: Unsigned Integer Types", 255, 65535, 16777215, 4294967295, 18446744073709551615);

INSERT INTO TypeTest 
   (id, description, t_FLOAT, t_DOUBLE, t_DOUBLE_PRECISION, t_DECIMAL) 
VALUES
   (NULL, "3: Floating Point Types", '3.402823466E+38', '-1.7976931348623157E+308', '-1.7976931348623157E+308', 9321.93421);

INSERT INTO TypeTest 
   (id, description, t_DATE, t_DATETIME, t_TIMESTAMP, t_TIME, t_YEAR) 
VALUES
   (NULL, "4: Date & Time Types", '2020-12-31', '1999-12-31 23:59:59', '1971-01-01 00:00:00', '18:59:59', 99);

INSERT INTO TypeTest 
   (id, description, t_CHAR, t_VARCHAR) 
VALUES
   (NULL, "5: Character (String) Types", "This is a char", "345 Elm Street");

INSERT INTO TypeTest
   (id, description, t_TINYBLOB, t_BLOB, t_LONGBLOB)
VALUES
   (NULL, "6: LOB Types", "abcdefghijklmnopqrstuv", "abcdefghijklmnopqrstuv", "abcdefghijklmnopqrstuv");

INSERT INTO TypeTest 
   (id, description, t_ENUM, t_SET)
VALUES
   (NULL, "7: Set/Enum Types", 'two', 'b,c');



# The following table is used to test transactions.
# Note: Transactions are only support when using INNODB tables.
#       Since INNODB introduces row locking etc, I would recommend it

DROP TABLE IF EXISTS TransactionTest;

# -------------------------------------------------------
CREATE TABLE TransactionTest
(
   id                      INT          NOT NULL,
   description             VARCHAR(100) NOT NULL,
   
   PRIMARY KEY(id)
) 
TYPE=INNODB;