int db_create_db(sqlite3* pdb, DB_FLAG dbtype)
{
char* pszSql = NULL;
if (dbtype == DB_USERINFO)
{
pszSql = "begin;\n \
CREATE TABLE tb_localuser( \
id varchar(20) not null,\
nickname varchar(20),\
logintime int not null, \
lastlogintime varchar(20) not null,\
primary key(id)\
);\
CREATE TABLE tb_friends (\
id VARCHAR(20) NOT NULL,\
friendid VARCHAR(20) NOT NULL,\
nickname VARCHAR(20),\
datatime VARCHAR(30) NOT NULL,\
CONSTRAINT fk \
foreign key (id) \
references tb_localuser (id),\
CONSTRAINT pk\
primary key (id, friendid)\
);\
CREATE TABLE testtable(\
col_1 int ,\
col_2 int , col_3 int,\
constraint pk primary key (col_1, col_2)\
);\
\
CREATE TABLE tb_localconfig(\
id varchar(20) not null ,\
loginstatus smallint not null default 0,\
savepswd smallint not null default 0,\
autologin smallint not null default 0,\
autostart smallint not null default 0,\
exitconfirm smallint not null default 0,\
autohide smallint not null default 0,\
showintaskbar smallint not null default 0,\
upgrademode smallint not null default 0,\
mergewnd smallint not null default 0,\
autoshowmsg smallint not null default 0,\
leavestatusset smallint not null default 0,\
defaultdirectory varchar(128) default NULL,\
criticalfilesize int not null default 0,\
constraint pk primary key (id)\
); commit;";
}
void SQLiteWrapper::init()
{
myOut() << "SQLite init use db:" << filename;
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(filename);
open();
QStringList tableList = db.tables(QSql::Tables);
if( tableList.size() == 0 )
{
myWarn() << "Warning empty db";
QSqlQuery query;
//mqtt topic, unix timestamp
if( !query.exec("CREATE TABLE IF NOT EXISTS data \
( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
topic VARCHAR(256), \
time INTEGER, \
alarm INTEGER DEFAULT 0 \
)")
)
{
qFatal("Failed to create table!");
}
int mapping_db_create_short_mappings_database(sqlite3** db, const char* db_folder, int memory) {
char db_name[strlen(db_folder) + 14];
sprintf(db_name, "%s/mappings.db", db_folder);
char* error_msg;
int ret_code;
if (memory) {
ret_code = sqlite3_open(":memory:", db);
} else {
ret_code = sqlite3_open(db_name, db);
}
// If database could be open, create its tables
if (ret_code == SQLITE_OK) {
ret_code = sqlite3_exec(*db, "BEGIN TRANSACTION", NULL, NULL, &error_msg);
ret_code += sqlite3_exec(*db,
"CREATE TABLE mappings (\
id_seq VARCHAR(64), \
paired_end INTEGER)",
NULL, 0, &error_msg);
//create_indexes(db); //index creation before insertion
sqlite3_exec(*db, "END TRANSACTION", NULL, NULL, &error_msg);
}
/*
CREATE TABLE IF NOT EXISTS `tb_service_config` (
`service_type` VARCHAR(50) NOT NULL DEFAULT '',
`service_config` LONGTEXT NULL,
PRIMARY KEY (`service_type`)
)
*/
int DBMysql::checkAndCreateTable()
{
boost::mutex::scoped_lock lock(mutex_);
try
{
std::string sql = "select count(*) cnt from tb_service_config";
mysqlpp::StoreQueryResult result ;
mysqlpp::Query query = conn_.query(sql.c_str());
query.parse();
result = query.store();
return 0;
}
catch(const mysqlpp::ConnectionFailed& e)
{
LOG(error)<<"connect failed:"<<e.what()<<ENDL;
return -9;
}
catch(std::exception& e)
{
LOG(error)<<"table not exists ,create!::"<<e.what()<<ENDL;
}
try
{
std::string sql= "CREATE TABLE IF NOT EXISTS `tb_service_config` ( \
`service_type` VARCHAR(50) NOT NULL DEFAULT '', \
`service_config` LONGTEXT NULL, \
PRIMARY KEY (`service_type`) ) ";
mysqlpp::Query query = conn_.query(sql.c_str());
query.parse();
query.execute();
}
catch(const mysqlpp::ConnectionFailed& e)
{
LOG(error)<<"connect failed:"<<e.what()<<ENDL;
return -9;
}
catch(std::exception& e)
{
LOG(error)<<""<<e.what()<<ENDL;
return -1;
}
return 0;
}
int KData::CreateKDataTableIfNotExists(const std::string& dbname, const std::string& tableName){
if (TableIsCreatedMap.find(dbname) != TableIsCreatedMap.end() && TableIsCreatedMap[dbname] == true){
return 0;
}
else
{
TableIsCreatedMap[dbname] = true;
const char* sqltempl = "CREATE TABLE IF NOT EXISTS `%s`.`%s` (\
`id` INT NOT NULL AUTO_INCREMENT, \
`Time` VARCHAR(32) NULL, \
`uuid` BIGINT NULL, \
`InstrumentID` VARCHAR(32) NULL, \
`LastPrice` DOUBLE NULL, \
`PreSettlementPrice` DOUBLE NULL, \
`PreClosePrice` DOUBLE NULL, \
`OpenPrice` DOUBLE NULL, \
`HighestPrice` DOUBLE NULL, \
`LowestPrice` DOUBLE NULL, \
`Volume` DOUBLE NULL, \
`Turnover` DOUBLE NULL, \
`ClosePrice` DOUBLE NULL, \
`SettlementPrice` DOUBLE NULL, \
`UpperLimitPrice` DOUBLE NULL, \
`LowerLimitPrice` DOUBLE NULL, \
`BidPrice1` DOUBLE NULL, \
`BidVolume1` DOUBLE NULL, \
`AskPrice1` DOUBLE NULL, \
`AskVolume1` DOUBLE NULL, \
`AveragePrice` DOUBLE NULL, \
PRIMARY KEY(`id`));";
char sqlbuf[2046];
SPRINTF(sqlbuf, sqltempl, dbname.c_str(), tableName.c_str());
DBWrapper db;
return db.ExecuteNoResult(sqlbuf);
}
}
void Create()
{
// 灵活性与高效性并存
const std::string strSql(
"CREATE TABLE `AM_TAP_XDR` ( \
`ID` BIGINT(10) AUTO_INCREMENT, \
`XDR_FILE` VARCHAR(256) NOT NULL, \
`TOTAL_CDR_COUNT` BIGINT(10) NOT NULL, \
`CREATE_TIME` DATETIME NOT NULL, \
PRIMARY KEY (`ID`) \
)"
);
m_pConn->Execute(strSql);
}
/*
* Creates tables in the database if they don't exist
*/
void Database::initDatabase(){
if(!connected) return;
query.prepare( "CREATE TABLE IF NOT EXISTS config ( \
id INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, \
name VARCHAR(200) UNIQUE DEFAULT NULL, \
value LONGTEXT \
)" );
if( !query.exec() )
qDebug() << "initDatabase() error: " << query.lastError();
else{
qDebug() << "Database initialized!";
}
}
void insert_friend(MES mes)
{
//将成功加到的好友添加进入数据表
char sql[255];
sprintf(sql,"INSERT INTO friend_%s VALUES('%s',0);",mes.to,mes.from);
//puts(sql);
MYSQL *conn;
conn = mysql_init(NULL);
if(conn == NULL) {
//如果返回NULL说明初始化失败
printf("mysql_init failed!\n");
return;// EXIT_FAILURE;
}
conn = mysql_real_connect(conn,HOSTNAME,USER,PASS,DATABASE,0,NULL,0);
if (!conn) {
printf("Connection failed!\n");
}
int res;
res = mysql_query(conn,sql);//正确返回0
if(res) {
perror("my_query");
mysql_close(conn);
exit(0);
}
sprintf(sql,"INSERT INTO friend_%s VALUES('%s',0);",mes.from,mes.to);
res = mysql_query(conn,sql);//正确返回0
if(res) {
perror("my_query");
mysql_close(conn);
exit(0);
}
if(strcmp(mes.from,mes.to) < 0) {
sprintf(sql,"CREATE TABLE message_%s_%s \
( mes VARCHAR(255), send VARCHAR(20),\
recv VARCHAR(20), time VARCHAR(30));",mes.from,mes.to);
} else{
acicd_afdx_message_type::acicd_afdx_message_type(sql_database_manager *database_manager) : acicd_element (database_manager)
{
DB_FIELDS= { { 10, "Name" },
};
DB_table_name="AFDX_MESSAGE_TYPE";
test_field="Name";
insert_query=QString("INSERT INTO %1 VALUES(:Name)").arg(DB_table_name);
test_query=QString("SELECT rowid FROM %1 WHERE (Name=:Name);").arg(DB_table_name);
create_table_query=QString("\
CREATE TABLE IF NOT EXISTS [%1](\
[Name] VARCHAR( 45 ) NULL,\
CONSTRAINT unique_combinaison PRIMARY KEY (Name) ON CONFLICT IGNORE\
);\n\
").arg(DB_table_name);
database_manager->create_table(create_table_query);
}
// ---------------------------------------------------------------------------
// CTrustedSitesServer::CreateDBL()
// ---------------------------------------------------------------------------
//
void CTrustedSitesServer::CreateDBL()
{
RFs fsSession;
User::LeaveIfError( fsSession.Connect() ); // Connect to file server
CleanupClosePushL( fsSession );
RDbNamedDatabase database;
TBuf<KMaxFilenameLength> filename;
FileUtils::MakePrivateFilenameL(fsSession, KTrustedSitesStoreFilename, filename);
FileUtils::EnsurePathL(fsSession, filename);
CleanupClosePushL( database );
User::LeaveIfError( database.Create( fsSession, filename ) );
// Create tables
// Trusted sites table
_LIT( KSQLCreateTable1, "CREATE TABLE Certificates (\
CertID COUNTER NOT NULL,\
CertHash CHAR(20) NOT NULL)");
_LIT( KSQLCreateTable2, "CREATE TABLE TrustedSites (\
CertID INTEGER,\
SiteName VARCHAR(100) NOT NULL,\
Forgiven INTEGER NOT NULL,\
OutOfDate INTEGER NOT NULL,\
ClientUID INTEGER NOT NULL )");
User::LeaveIfError( database.Execute( KSQLCreateTable1 ) );
User::LeaveIfError( database.Execute( KSQLCreateTable2 ) );
CleanupStack::PopAndDestroy( 2 ); // database, fsSession
}
int MACrossTech::CreateTableIfNotExists(const std::string& dbname, const std::string& tableName)
{
if (MACrossTech::IsTableCreated == true){
return 0;
}
else
{
MACrossTech::IsTableCreated = true;
const char* sqltempl = "CREATE TABLE IF NOT EXISTS `%s`.`%s` (\
`id` INT NOT NULL AUTO_INCREMENT, \
`uuid` BIGINT NOT NULL, \
`LongMA` Double(20,5) NULL, \
`ShortMA` Double(20,5) NULL, \
`Ticktype` int NULL, \
`Time` VARCHAR(64) NULL, \
`LastPrice` Double NULL, \
PRIMARY KEY(`id`));";
char sqlbuf[2046];
sprintf_s(sqlbuf, sqltempl, dbname.c_str(), tableName.c_str());
DBWrapper db;
return db.ExecuteNoResult(sqlbuf);
}
}
str l_username_column = str_init("l_username");
str l_domain_column = str_init("l_domain");
str r_username_column = str_init("r_username");
str r_domain_column = str_init("r_domain");
str realm_column = str_init("realm");
str auth_username_column = str_init("auth_username");
str auth_password_column = str_init("auth_password");
str auth_proxy_column = str_init("auth_proxy");
str expires_column = str_init("expires");
#if 0
INSERT INTO version (table_name, table_version) values ('uacreg','1');
CREATE TABLE uacreg (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
l_uuid VARCHAR(64) NOT NULL,
l_username VARCHAR(64) NOT NULL,
l_domain VARCHAR(128) DEFAULT '' NOT NULL,
r_username VARCHAR(64) NOT NULL,
r_domain VARCHAR(128) NOT NULL,
realm VARCHAR(64) NOT NULL,
auth_username VARCHAR(64) NOT NULL,
auth_password VARCHAR(64) NOT NULL,
auth_proxy VARCHAR(128) DEFAULT '' NOT NULL,
expires INT(10) UNSIGNED DEFAULT 0 NOT NULL,
CONSTRAINT l_uuid_idx UNIQUE (l_uuid)
) ENGINE=MyISAM;
#endif
extern struct tm_binds uac_tmb;
/**
* This test HyperSQL client uses the ODBC DSN "tstdsn" to connect up to a
* HyperSQL server. Just configure your own DSN to use the HyperSQL ODBC
* driver, specifying the HyperSQL server host name, database name, user,
* password, etc.
*
* Sample C program accessing HyperSQL.
*
* ODBC C API ref at
* http://msdn.microsoft.com/en-us/library/ms714562(VS.85).aspx .
* Summary of functions at
* http://msdn.microsoft.com/en-us/library/ms712628(VS.85).aspx
*
* To build on UNIX with unixODBC:<PRE><CODE>
* gcc -lodbc -o sample sample.c
* </CODE></PRE>
*
* To build in Windows with MSVC++ (Express variant is free):<PRE><CODE>
* cl /nologo /D _WINDOWS /D ODBCVER=0x0351 /c sample.c
* link odbc32.lib /nologo /machine:x86 sample.obj /out:sample.exe
* </CODE></PRE>
*
* @author Blaine Simpson (blaine dot simpson at admc dot com)
*/
int main(int argc, char** argv) {
SQLRETURN odbcret;
SQLHENV sqlhenv;
SQLHENV conn;
SQLHSTMT stmt;
char *cp;
long in_idval;
const int cstrmax = 100;
char *in_vcval = malloc(cstrmax);
long out_idval;
char *out_vcval = malloc(cstrmax);
char *out_etimeval = malloc(cstrmax);
SQLLEN ntsval = SQL_NTS;
int detect;
// I. CONNECT
odbcret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlhenv);
if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO)
return print_ret("Failed to allocate an ODBC environment handle", 1);
odbcret =
SQLSetEnvAttr(sqlhenv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3, 0);
if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO)
return print_ret("Failed to set ODBC version 3.0", 2);
odbcret = SQLAllocHandle(SQL_HANDLE_DBC, sqlhenv, &conn);
if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO)
return print_ret("Failed to allocate an ODBC connection handle", 3);
odbcret = SQLSetConnectAttr(
conn, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO)
return print_ret("Failed to allocate an ODBC connection handle", 3);
// May also want to set timeout values in the same way
// Can override the DSN-defined user name and/or password here:
detect = detectOdbcFailure(
SQLConnect(conn, (SQLCHAR*) "tstdsn", SQL_NTS, (SQLCHAR*) NULL, 0,
(SQLCHAR*) NULL, 0),
conn, "Connection failure");
if (detect) return detect;
// II. PREPARE OBJECTS FOR USE
detect = detectOdbcFailure(
SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt), conn,
"Failed to allocate an ODBC statement handle");
if (detect) return detect;
// Just using this char pointer because some non-ANSI compilers won't let
// us declare a char array/pointer here.
cp = "DROP TABLE tsttbl IF EXISTS";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"DROP statement failed");
if (detect) return detect;
// Some recent change to the HyperSQL server or to unixODBC
// has made this commit necessary, at least on UNIX. Some other
// transaction control command would probably be more
// appropriate here.
detect = detectOdbcFailure(SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT),
conn, "COMMIT failed");
if (detect) return detect;
cp = "CREATE TABLE tsttbl(\n\
id BIGINT generated BY DEFAULT AS IDENTITY,\n\
vc VARCHAR(20),\n\
entrytime TIMESTAMP DEFAULT current_timestamp NOT NULL\n\
)";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"CREATE TABLE statement failed");
if (detect) return detect;
detect = detectOdbcFailure(SQLCloseCursor(stmt), conn,
"Failed to close Cursor for re-use");
if (detect) return detect;
// III. INSERT DATA
// Non-parameter INSERT
cp = "INSERT INTO tsttbl (id, vc) values (1, 'one')";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"1st Insertion failed");
if (detect) return detect;
#ifdef _WINDOWS
// TODO: PROBLEM with Parameterized INPUT in Windows (works fine on UNIX).
// For some reason, even if we are do a Prepare/Execute (and our
// driver is set to always use server-side Preparation), the client side
// is doing the substitution... and doing a bad Lob of it too.
// Therefore, we do all INSERTs statically for Windows here:
cp = "INSERT INTO tsttbl (id, vc) values (2, 'two')";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"2nd Insertion failed");
if (detect) return detect;
cp = "INSERT INTO tsttbl (id, vc) values (3, 'three')";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"3rd Insertion failed");
if (detect) return detect;
cp = "INSERT INTO tsttbl (id, vc) values (4, 'four')";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"4th Insertion failed");
if (detect) return detect;
cp = "INSERT INTO tsttbl (id, vc) values (5, 'five')";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"5th Insertion failed");
if (detect) return detect;
#else
// Parameterized INSERT
cp = "INSERT INTO tsttbl (id, vc) values (?, ?)";
detect = detectOdbcFailure(SQLPrepare(stmt, (SQLCHAR*) cp, SQL_NTS), conn,
"Preparation of Insertion stmt failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT,
0, 0, &in_idval, 0, NULL), conn,
"Bind of 'id' input failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
20, 0, in_vcval, cstrmax, &ntsval), conn,
"Bind of 'vc' input failed");
if (detect) return detect;
in_idval = 2;
strcpy(in_vcval, "two");
detect = detectOdbcFailure(SQLExecute(stmt), conn,
"Insertion of 2nd row failed");
if (detect) return detect;
in_idval = 3;
strcpy(in_vcval, "three");
detect = detectOdbcFailure(SQLExecute(stmt), conn,
"Insertion of 3rd row failed");
if (detect) return detect;
in_idval = 4;
strcpy(in_vcval, "four");
detect = detectOdbcFailure(SQLExecute(stmt), conn,
"Insertion of 4th row failed");
if (detect) return detect;
in_idval = 5;
strcpy(in_vcval, "five");
detect = detectOdbcFailure(SQLExecute(stmt), conn,
"Insertion of 5th row failed");
if (detect) return detect;
#endif
detect = detectOdbcFailure(SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT),
conn, "COMMIT failed");
if (detect) return detect;
detect = detectOdbcFailure(SQLCloseCursor(stmt), conn,
"Failed to close Cursor for re-use");
if (detect) return detect;
// IV. QUERIES
// Non-Parameter QUERY
cp = "SELECT * FROM tsttbl WHERE id < 3";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"Non-parameter query failed");
// Would return SQL_NO_DATA if no rows inserted.
// Don't need to bind until before fetches are performed.
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn,
"Bind of 'id' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval),
conn, "Bind of 'vc' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval),
conn, "Bind of 'entrytime' output failed");
if (detect) return detect;
while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) {
if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect;
printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval);
}
detect = detectOdbcFailure(SQLCloseCursor(stmt), conn,
"Failed to close Cursor for re-use");
if (detect) return detect;
#if _WINDOWS
// Input parameters not working on Windows. See comment above.
cp = "SELECT * FROM tsttbl WHERE id > 3";
detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn,
"Non-parameter query failed");
// Would return SQL_NO_DATA if no rows inserted.
// Don't need to bind until before fetches are performed.
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn,
"Bind of 'id' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval),
conn, "Bind of 'vc' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval),
conn, "Bind of 'entrytime' output failed");
if (detect) return detect;
while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) {
if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect;
printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval);
}
#else
// Parameterized QUERY
cp = "SELECT * FROM tsttbl WHERE id > ?";
detect = detectOdbcFailure(SQLPrepare(stmt, (SQLCHAR*) cp, SQL_NTS), conn,
"Preparation of Query stmt failed");
if (detect) return detect;
in_idval = 3;
detect = detectOdbcFailure(
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT,
0, 0, &in_idval, 0, NULL), conn,
"Bind of 'id' input failed");
if (detect) return detect;
detect = detectOdbcFailure(SQLExecute(stmt), conn,
"Parameterized query failed");
// Would return SQL_NO_DATA if no rows selected
// Don't need to bind until before fetches are performed.
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn,
"Bind of 'id' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval),
conn, "Bind of 'vc' output failed");
if (detect) return detect;
detect = detectOdbcFailure(
SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval),
conn, "Bind of 'entrytime' output failed");
if (detect) return detect;
#endif
while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) {
if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect;
printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval);
}
detect = detectOdbcFailure(SQLCloseCursor(stmt), conn,
"Failed to close Cursor");
if (detect) return detect;
SQLDisconnect(conn);
SQLFreeHandle(SQL_HANDLE_DBC, conn);
SQLFreeHandle(SQL_HANDLE_ENV, sqlhenv);
//return print_ret("Success", 0);
return 0;
}
void create_db()
{
DataBase * db = new DataBase;
QSqlQuery query = db->dataBase()->exec();
if(query.exec("CREATE TABLE IF NOT EXISTS tbusuario (\
id INTEGER PRIMARY KEY AUTOINCREMENT,\
nombre VARCHAR(50) NOT NULL,\
cargo VARCHAR(50) NOT NULL,\
contrasena VARCHAR(50) NOT NULL,\
email VARCHAR(50) NOT NULL,\
telefono INTEGER NOT NULL)"))
{
qDebug() << "creamos la tabla usuario";
}
else
{
qDebug() << "error al crear la tabla usuario";
delete db;
exit(-1);
}
if(query.exec("CREATE TABLE IF NOT EXISTS tbbodega (\
id INTEGER PRIMARY KEY AUTOINCREMENT,\
nombre VARCHAR(50) NOT NULL,\
ubicacion VARCHAR(50) NOT NULL,\
descripcion VARCHAR(300) NOT NULL,\
telefono INTEGER NOT NULL)"))
{
qDebug() << "creamos la tabla bodega";
}
else
{
qDebug() << "error al crear la tabla bodega";
delete db;
exit(-1);
}
if(query.exec("CREATE TABLE IF NOT EXISTS tbproducto (\
id INTEGER PRIMARY KEY AUTOINCREMENT,\
nombre VARCHAR(50) NOT NULL,\
descripcion VARCHAR(300) NOT NULL,\
bodega VARCHAR(50) NOT NULL,\
preciocompra VARCHAR(50) NOT NULL,\
precioventa VARCHAR(50) NOT NULL)"))
{
qDebug() << "creamos la tabla producto";
}
else
{
qDebug() << "error al crear la tabla producto";
delete db;
exit(-1);
}
query.exec("INSERT INTO tbusuario (nombre, cargo, contrasena, email, telefono) VALUES ('yo', 'admin', 'pass', '*****@*****.**', 1234)");
query.exec("INSERT INTO tbbodega (nombre, ubicacion, telefono, descripcion) VALUES ('la 35', 'cll 35 # 35-35', 555000, 'blablabla')");
query.exec("INSERT INTO tbproducto (nombre, descripcion, bodega, preciocompra, precioventa) VALUES ('pc algo', 'esta vacano', '1', 50, 60)");
delete db;
}
MODULE_VERSION
#define NR_KEYS 3
int mt_fetch_rows = 1000;
/** database connection */
static db1_con_t *db_con = NULL;
static db_func_t mt_dbf;
#if 0
INSERT INTO version (table_name, table_version) values ('mtree','1');
CREATE TABLE mtree (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
tprefix VARCHAR(32) NOT NULL,
tvalue VARCHAR(128) DEFAULT '' NOT NULL,
CONSTRAINT tprefix_idx UNIQUE (tprefix)
) ENGINE=MyISAM;
INSERT INTO version (table_name, table_version) values ('mtrees','1');
CREATE TABLE mtrees (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
tname VARCHAR(128) NOT NULL,
tprefix VARCHAR(32) NOT NULL,
tvalue VARCHAR(128) DEFAULT '' NOT NULL,
CONSTRAINT tname_tprefix_idx UNIQUE (tname, tprefix)
) ENGINE=MyISAM;
#endif
/** parameters */
static str db_url = str_init(DEFAULT_DB_URL);
static ULONG __clientversion;
static ULONG __thread_id;
static const char* __stat;
static const char* __charset;
#define TABLE_NAME _T("cwt_test_table")
#define TABLE0_NAME _T("cwt_test_table0")
#define TABLE_DATE _T("cwt_test_date")
#define TABLE_TIME _T("cwt_test_time")
#define TABLE_INT _T("cwt_test_int")
#define TABLE_TEXT _T("cwt_test_text")
static const CWT_CHAR *__sql_use_db = _T("USE cwt_test_db");
static const CWT_CHAR *__sql_drop_table = _T("DROP TABLE IF EXISTS ") TABLE_NAME;
static const CWT_CHAR *__sql_create_table0 = _T("CREATE TABLE ") TABLE0_NAME _T("(col1 INT,\
col2 VARCHAR(100),\
col3 SMALLINT,\
col4 TIMESTAMP)");
static const CWT_CHAR *__sql_create_table_date =
_T("CREATE TABLE ") TABLE_DATE
_T("(date_val DATE)");
static const CWT_CHAR *__sql_create_table_time =
_T("CREATE TABLE ") TABLE_TIME
_T("(time_val TIME)");
static const CWT_CHAR *__sql_create_table_int =
_T("CREATE TABLE ") TABLE_INT
_T("(bool_val TINYINT\
int test_mysql(int argc, char* argv[])
{
if (argc < 2)
{
printf ("Usage: \n\t./a.out filename [...]\n");
return 1;
}
const char* host = "172.16.0.10"; // Mysql数据库服务器所在主机，使用默认端口3306，亦可指定端口，如"localhost:3000"或"10.10.168.128:3000"
const char* user = "******"; // Mysql用户
std::string pass; // Mysql用户密码
const char* db = "test"; // Mysql数据库名
printf ("输入本地MySQL数据库的root密码: ");
std::getline(std::cin, pass);
MySQL::Connection conn;
if (!conn.connect (host, user, pass.c_str(), db))
{
return 1;
}
// 创建一个具有BLOG字段的数据库表
char drop_table_sql[] = "DROP TABLE IF EXISTS T_TEST";
char create_table_sql[] = "CREATE TABLE T_TEST\
(\
ID SERIAL,\
NAME VARCHAR(100),\
DATA_LEN DECIMAL(10,0),\
DATA MEDIUMBLOB,\
PRIMARY KEY (ID)\
)";
if (!conn.execute (drop_table_sql))
return 1;
if (!conn.execute (create_table_sql))
return 1;
// [1] 写文件到数据库表 T_TEST
printf ("[1] 写文件到数据库表 T_TEST\n");
for (int i=1; i<argc; ++i)
{
// 读取将要写入数据库的文件
char filename[256] = "\0";
strcpy (filename, argv[i]);
FILE* fp = fopen (filename, "rb");
if (fp == NULL)
{
fprintf (stderr, "打开文件失败:%s", strerror (errno));
return 1;
}
struct mysql_iovec v;
fseek(fp, 0, SEEK_SET);
fseek(fp, 0, SEEK_END);
v.iov_len = ftell (fp);
unsigned char* pBuf = new unsigned char [v.iov_len];
memset (pBuf, 0, v.iov_len);
fseek(fp, 0, SEEK_SET);
fread (pBuf, 1, v.iov_len, fp);
fclose (fp);
// 获取文件名(basename)
const char delim[] = "/\\"; // 文件分隔符
char* basename = strrchr (filename, delim[0]);
if (!basename)
{
basename = strrchr (filename, delim[1]);
}
if (basename){
basename++;
}
else {
basename = filename;
}
// 写BLOG
char sql[1024] = "\0";
sprintf (sql, "INSERT INTO T_TEST (NAME,DATA_LEN,DATA) VALUES ('%s',%d,?)",
basename, v.iov_len);
v.iov_base = pBuf;
if (conn.query()->blob_write (sql, &v, 1))
{
printf ("写入文件\"%s\"到数据表T_TEST成功，共%d字节.\n", basename, v.iov_len);
}
else
{
printf ("写入文件失败:%s\n", conn.error().c_str());
}
delete [] pBuf;
}
// [2] 读取T_TEST表，并将BOLG字段中的数据写到文件
printf ("\n[2] 读取T_TEST表，并将BOLG字段中的数据写到文件\n");
char sql[] = "SELECT ID,NAME,DATA_LEN,DATA FROM T_TEST";
if (!conn.query()->execute (sql)){
printf ("执行数据库语句\"%s\"失败:%s.", sql, conn.error().c_str());
return 1;
}
MySQL::Row *r;
while (r = conn.query()->fetch())
{
int i = 0;
int id = (*r)[i++];
char* name = (*r)[i++];
int datalen = (*r)[i++];
char* pBuf = (*r)[i++];
char TmpFile[256] = "\0";
#ifdef WIN32
GetTempPath(MAX_PATH, TmpFile);
#else
strcpy (TmpFile, "/tmp/");
#endif
strcat (TmpFile, name);
FILE* fp = fopen (TmpFile, "wb");
if (fp)
{
if (fwrite (pBuf, 1, datalen, fp) == datalen){
printf ("读出BLOG写入文件\"%s\"（%d字节）.\n", TmpFile, datalen);
}
fclose (fp);
}
}
return 0;
}
int test_mysql1(int argc, char* argv[])
{
const char* host = "172.27.237.60"; // Mysql数据库服务器所在主机，使用默认端口3306，亦可指定端口，如"localhost:3000"或"10.10.168.128:3000"
const char* user = "******"; // Mysql用户
std::string pass = "******"; // Mysql用户密码
const char* db = "repl"; // Mysql数据库名
std::getline(std::cin, pass);
MySQL::Connection conn;
if (!conn.connect (host, user, pass.c_str(), db))
{
return 1;
}
// 创建一个具有BLOG字段的数据库表
char drop_table_sql[] = "DROP TABLE IF EXISTS T_TEST";
char create_table_sql[] = "CREATE TABLE T_TEST\
(\
ID SERIAL,\
NAME VARCHAR(100),\
DATA_LEN DECIMAL(10,0),\
DATA MEDIUMBLOB,\
PRIMARY KEY (ID)\
)";
if (!conn.execute (drop_table_sql))
return 1;
if (!conn.execute (create_table_sql))
return 1;
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('cc', 0)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("set autocommit=0")) { // conn.query()->autocommit(false)
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('12', 0)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.query()->rollback()) {// ROLLBACK
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('34', 0)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.query()->commit()) { // COMMIT
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.query()->autocommit()) { // set autocommit=1
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("begin")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('56', 2)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('78', 2)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.query()->commit()) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("start transaction")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('55', 9)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('66', 9)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.query()->rollback()) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
if (!conn.execute("insert into T_TEST(NAME, DATA_LEN) values('77', 9)")) {
printf ("error:%s\n", conn.error().c_str());
return 1;
}
return 0;
}
int DBUtils::CreateTickTableIfNotExists(const std::string& dbname, const std::string& tableName){
if (DBUtils::m_dict.find(tableName) != m_dict.end()){
return 0;
}
else
{
DBUtils::m_dict[tableName] = true;
const char* sqltempl = "CREATE TABLE IF NOT EXISTS `%s`.`%s` (\
`id` INT NOT NULL AUTO_INCREMENT, \
`Date` DATE NULL, \
`InstrumentID` VARCHAR(32) NULL, \
`ExchangeID` VARCHAR(16) NULL, \
`ExchangeInstID` VARCHAR(32) NULL, \
`LastPrice` DOUBLE NULL, \
`PreSettlementPrice` DOUBLE NULL, \
`PreClosePrice` DOUBLE NULL, \
`PreOpenInterest` DOUBLE NULL, \
`OpenPrice` DOUBLE NULL, \
`HighestPrice` DOUBLE NULL, \
`LowestPrice` DOUBLE NULL, \
`Volume` DOUBLE NULL, \
`Turnover` DOUBLE NULL, \
`OpenInterest` DOUBLE NULL, \
`ClosePrice` DOUBLE NULL, \
`SettlementPrice` DOUBLE NULL, \
`UpperLimitPrice` DOUBLE NULL, \
`LowerLimitPrice` DOUBLE NULL, \
`PreDelta` DOUBLE NULL, \
`CurrDelta` DOUBLE NULL, \
`UpdateTime` TIME NULL, \
`UpdateMillisec` INT NULL, \
`BidPrice1` DOUBLE NULL, \
`BidVolume1` DOUBLE NULL, \
`AskPrice1` DOUBLE NULL, \
`AskVolume1` DOUBLE NULL, \
`BidPrice2` DOUBLE NULL, \
`BidVolume2` DOUBLE NULL, \
`AskPrice2` DOUBLE NULL, \
`AskVolume2` DOUBLE NULL, \
`BidPrice3` DOUBLE NULL, \
`BidVolume3` DOUBLE NULL, \
`AskPrice3` DOUBLE NULL, \
`AskVolume3` DOUBLE NULL, \
`BidPrice4` DOUBLE NULL, \
`BidVolume4` DOUBLE NULL, \
`AskPrice4` DOUBLE NULL, \
`AskVolume4` DOUBLE NULL, \
`BidPrice5` DOUBLE NULL, \
`BidVolume5` DOUBLE NULL, \
`AskPrice5` DOUBLE NULL, \
`AskVolume5` DOUBLE NULL, \
`AveragePrice` DOUBLE NULL, \
`ActionDay` DATE NULL, \
`uuid` BIGINT NULL, \
PRIMARY KEY(`id`));";
char sqlbuf[2046];
SPRINTF(sqlbuf, sqltempl, dbname.c_str(), tableName.c_str());
DBWrapper db;
return db.ExecuteNoResult(sqlbuf);
}
}
/* Returns the MYSQL instance (singleton) */
MYSQL * mysql_get_instance(TSRMLS_D) {
my_bool reconnect = 1;
if (APM_G(mysql_event_db) == NULL) {
APM_DEBUG("[MySQL driver] mysql_event_db is null (mysql handler)... \n");
mysql_library_init(0, NULL, NULL);
APM_G(mysql_event_db) = malloc(sizeof(MYSQL));
mysql_init(APM_G(mysql_event_db));
mysql_options(APM_G(mysql_event_db), MYSQL_OPT_RECONNECT, &reconnect);
APM_DEBUG("[MySQL driver] Connecting to server...");
if (mysql_real_connect(APM_G(mysql_event_db), APM_G(mysql_db_host), APM_G(mysql_db_user), APM_G(mysql_db_pass), APM_G(mysql_db_name), APM_G(mysql_db_port), NULL, 0) == NULL) {
APM_DEBUG("FAILED! Message: %s\n", mysql_error(APM_G(mysql_event_db)));
mysql_destroy(TSRMLS_C);
return NULL;
}
APM_DEBUG("OK\n");
mysql_set_character_set(APM_G(mysql_event_db), "utf8");
mysql_query(
APM_G(mysql_event_db),
"\
CREATE TABLE IF NOT EXISTS request (\
id INTEGER UNSIGNED PRIMARY KEY auto_increment,\
application VARCHAR(255) NOT NULL,\
ts TIMESTAMP NOT NULL,\
script TEXT NOT NULL,\
uri TEXT NOT NULL,\
host TEXT NOT NULL,\
ip INTEGER UNSIGNED NOT NULL,\
cookies TEXT NOT NULL,\
post_vars TEXT NOT NULL,\
referer TEXT NOT NULL,\
method TEXT NOT NULL,\
status TEXT NOT NULL\
)"
);
mysql_query(
APM_G(mysql_event_db),
"\
CREATE TABLE IF NOT EXISTS event (\
id INTEGER UNSIGNED PRIMARY KEY auto_increment,\
request_id INTEGER UNSIGNED,\
ts TIMESTAMP NOT NULL,\
type SMALLINT UNSIGNED NOT NULL,\
file TEXT NOT NULL,\
line MEDIUMINT UNSIGNED NOT NULL,\
message TEXT NOT NULL,\
backtrace BLOB NOT NULL,\
KEY request (request_id)\
)"
);
mysql_query(
APM_G(mysql_event_db),
"\
CREATE TABLE IF NOT EXISTS stats (\
id INTEGER UNSIGNED PRIMARY KEY auto_increment,\
request_id INTEGER UNSIGNED,\
duration FLOAT UNSIGNED NOT NULL,\
user_cpu FLOAT UNSIGNED NOT NULL,\
sys_cpu FLOAT UNSIGNED NOT NULL,\
mem_peak_usage INTEGER UNSIGNED NOT NULL,\
KEY request (request_id)\
)"
);
}
return APM_G(mysql_event_db);
}
int main(int argc, char* argv[])
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
std::string sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("song.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
fprintf(stdout, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "create table cd ( \
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, \
title VARCHAR(70) NOT NULL, \
artist_id INTEGER NOT NULL, \
catalogue VARCHAR(30) NOT NULL \
);";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "Table created successfully\n");
}
/* Create SQL statement */
sql = "create table artist ( \
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, \
name VARCHAR(100) NOT NULL \
);";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "Table created successfully\n");
}
/* Create SQL statement */
sql = "create table track ( \
cd_id INTEGER NOT NULL, \
track_id INTEGER NOT NULL, \
title VARCHAR(70), \
PRIMARY KEY(cd_id, track_id) \
);";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "Table created successfully\n");
}
std::fstream track;
track.open("track.txt");
std::string line;
std::string delimiter = "|";
std::string cd_id_ref;
std::string track_id;
std::string title;
while (std::getline(track,line)) {
cd_id_ref = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
track_id = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
title = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
sql = "insert into track(cd_id, track_id, title) values(";
sql += cd_id_ref + "," + track_id + ",'" + title + "')";
sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
}
track.close();
std::fstream cd;
cd.open("cd.txt");
std::string id;
std::string cd_title;
std::string cd_id;
std::string catalogue;
while (std::getline(cd,line)) {
id = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
cd_title = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
cd_id= line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
catalogue = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
sql = "insert into cd(id,title, artist_id, catalogue) values(";
sql += id +"," + "'" + cd_title + "'" + "," + cd_id + ",'" + catalogue + "')";
sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
}
cd.close();
std::fstream artist;
artist.open("artist.txt");
std::string artist_id;
std::string track_name;
while (std::getline(artist,line)) {
artist_id = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
track_name = line.substr(0,line.find(delimiter));
line.erase(0,line.find(delimiter) + delimiter.length());
sql = "insert into artist(id,name) values(";
sql += artist_id + "," + "'" + track_name + "')";
sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
}
artist.close();
std::ofstream output;
output.open("output.txt");
output << "\ntrack" << std::endl << "_______________\n" << std::endl;
output.close();
sql = "SELECT * FROM track";
sqlite3_exec(db, sql.c_str(), callback, (void*)data, &zErrMsg);
output.open("output.txt", std::ios::app);
output << "\ncd" << std::endl << "_______________\n" << std::endl;
output.close();
sql = "SELECT * FROM cd";
sqlite3_exec(db, sql.c_str(), callback, (void*)data, &zErrMsg);
output.open("output.txt",std::ios::app);
output << "\nartist" << std::endl << "_______________\n" << std::endl;
output.close();
sql = "SELECT * FROM artist";
sqlite3_exec(db, sql.c_str(), callback, (void*)data, &zErrMsg);
sqlite3_close(db);
return 0;
}
void processingAsteriskTest()
{
csvsqldb::FunctionRegistry functions;
csvsqldb::SQLParser parser(functions);
csvsqldb::ASTNodePtr node = parser.parse(
"CREATE TABLE employees(emp_no INTEGER,birth_date DATE NOT NULL,first_name VARCHAR(25) NOT NULL,last_name VARCHAR(50) "
"NOT NULL,gender CHAR,hire_date DATE,PRIMARY KEY(emp_no))");
MPF_TEST_ASSERT(node);
MPF_TEST_ASSERT(std::dynamic_pointer_cast<csvsqldb::ASTCreateTableNode>(node));
csvsqldb::ASTCreateTableNodePtr createNode = std::dynamic_pointer_cast<csvsqldb::ASTCreateTableNode>(node);
csvsqldb::TableData tabledata = csvsqldb::TableData::fromCreateAST(createNode);
csvsqldb::FileMapping mapping;
csvsqldb::Database database("/tmp", mapping);
database.addTable(tabledata);
node =
parser.parse("SELECT * FROM employees emp WHERE emp_no BETWEEN 100 AND 9999 AND emp.birth_date > DATE'1960-01-01'");
csvsqldb::SymbolTablePtr symbolTable = node->symbolTable();
// symbolTable->dump();
// std::cout << std::endl;
symbolTable->typeSymbolTable(database);
// symbolTable->dump();
// std::cout << std::endl;
csvsqldb::ASTQueryNodePtr query = std::dynamic_pointer_cast<csvsqldb::ASTQueryNode>(node);
csvsqldb::ASTExprNodePtr exp =
std::dynamic_pointer_cast<csvsqldb::ASTQuerySpecificationNode>(query->_query)->_tableExpression->_where->_exp;
csvsqldb::Expressions& selectList = std::dynamic_pointer_cast<csvsqldb::ASTQuerySpecificationNode>(query->_query)->_nodes;
csvsqldb::SymbolInfoPtr tableInfo =
std::dynamic_pointer_cast<csvsqldb::ASTTableIdentifierNode>(
std::dynamic_pointer_cast<csvsqldb::ASTQuerySpecificationNode>(query->_query)->_tableExpression->_from->_tableReferences[0])
->_factor->_info;
csvsqldb::BlockManager blockManager;
csvsqldb::StringVector files;
csvsqldb::OperatorContext context(database, functions, blockManager, files);
std::stringstream ss;
csvsqldb::RootOperatorNodePtr output = std::make_shared<csvsqldb::OutputRowOperatorNode>(context, symbolTable, ss);
csvsqldb::RowOperatorNodePtr projection = std::make_shared<csvsqldb::ExtendedProjectionOperatorNode>(context, symbolTable, selectList);
csvsqldb::RowOperatorNodePtr select = std::make_shared<csvsqldb::SelectOperatorNode>(context, symbolTable, exp);
csvsqldb::RowOperatorNodePtr scan = std::make_shared<DummyScanOperatorNode>(context, symbolTable, *tableInfo);
select->connect(scan);
projection->connect(select);
output->connect(projection);
MPF_TEST_ASSERTEQUAL(3, output->process());
std::string expected = R"(#EMP_NO,EMP.BIRTH_DATE,EMP.FIRST_NAME,EMP.LAST_NAME,EMP.GENDER,EMP.HIRE_DATE
4711,1970-09-23,'Lars','Fürstenberg','M',2012-02-01
815,1969-05-17,'Mark','Fürstenberg','M',2003-04-15
9227,1963-03-06,'Angelica','Tello de Fürstenberg','F',2003-06-15
)";
MPF_TEST_ASSERTEQUAL(expected, ss.str());
}
void processingAggregationTest()
{
csvsqldb::FunctionRegistry functions;
csvsqldb::SQLParser parser(functions);
csvsqldb::ASTNodePtr node = parser.parse(
"CREATE TABLE employees(emp_no INTEGER,birth_date DATE NOT NULL,first_name VARCHAR(25) NOT NULL,last_name VARCHAR(50) "
"NOT NULL,gender CHAR,hire_date DATE,PRIMARY KEY(emp_no))");
MPF_TEST_ASSERT(node);
MPF_TEST_ASSERT(std::dynamic_pointer_cast<csvsqldb::ASTCreateTableNode>(node));
csvsqldb::ASTCreateTableNodePtr createNode = std::dynamic_pointer_cast<csvsqldb::ASTCreateTableNode>(node);
csvsqldb::TableData tabledata = csvsqldb::TableData::fromCreateAST(createNode);
csvsqldb::FileMapping mapping;
csvsqldb::Database database("/tmp", mapping);
database.addTable(tabledata);
node = parser.parse("SELECT count(*) FROM employees");
// csvsqldb::ASTNodeDumpVisitor visitor;
// std::cout << std::endl;
// node->accept(visitor);
csvsqldb::SymbolTablePtr symbolTable = node->symbolTable();
symbolTable->typeSymbolTable(database);
// symbolTable->dump();
// std::cout << std::endl;
csvsqldb::ASTQueryNodePtr query = std::dynamic_pointer_cast<csvsqldb::ASTQueryNode>(node);
csvsqldb::Expressions& selectList = std::dynamic_pointer_cast<csvsqldb::ASTQuerySpecificationNode>(query->_query)->_nodes;
csvsqldb::SymbolInfoPtr tableInfo =
std::dynamic_pointer_cast<csvsqldb::ASTTableIdentifierNode>(
std::dynamic_pointer_cast<csvsqldb::ASTQuerySpecificationNode>(query->_query)->_tableExpression->_from->_tableReferences[0])
->_factor->_info;
csvsqldb::BlockManager manager;
csvsqldb::StringVector files;
csvsqldb::OperatorContext context(database, functions, manager, files);
std::stringstream ss;
csvsqldb::RootOperatorNodePtr output = std::make_shared<csvsqldb::OutputRowOperatorNode>(context, symbolTable, ss);
csvsqldb::RowOperatorNodePtr projection = std::make_shared<csvsqldb::AggregationOperatorNode>(context, symbolTable, selectList);
csvsqldb::RowOperatorNodePtr scan = std::make_shared<DummyScanOperatorNode>(context, symbolTable, *tableInfo);
projection->connect(scan);
output->connect(projection);
MPF_TEST_ASSERTEQUAL(1, output->process());
MPF_TEST_ASSERTEQUAL("#$alias_1\n3\n", ss.str());
}
int main( void )
{
sqlite3 *db=NULL;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("zieckey.db", &db); //打开指定的数据库文件,如果不存在将创建一个同名的数据库文件
if( rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
else printf("You have opened a sqlite3 database named zieckey.db successfully!\nCongratulations! Have fun ! ^-^ \n");
//创建一个表,如果该表存在，则不创建，并给出提示信息，存储在 zErrMsg 中
char *sql = " CREATE TABLE SensorData(\
ID INTEGER PRIMARY KEY,\
SensorID INTEGER,\
SiteNum INTEGER,\
Time VARCHAR(12),\
SensorParameter REAL\
);" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
#endif
//插入数据
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 1 , 1 , '200605011206', 18.9 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 23 , 45 , '200605011306', 16.4 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
sql = "INSERT INTO \"SensorData\" VALUES(NULL , 34 , 45 , '200605011306', 15.4 );" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
int nrow = 0, ncolumn = 0;
char **azResult; //二维数组存放结果
//查询数据
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
int i = 0 ;
printf( "row:%d column=%d \n" , nrow , ncolumn );
printf( "\nThe result of querying is : \n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );
//释放掉 azResult 的内存空间
sqlite3_free_table( azResult );
//删除数据
sql = "DELETE FROM SensorData WHERE SensorID = 1 ;" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
#endif
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf( "\n\n\n\nrow:%d column=%d " , nrow , ncolumn );
printf( "\nAfter deleting , the result of querying is : \n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );
//释放掉 azResult 的内存空间
sqlite3_free_table( azResult );
printf( "\nSATRT UPDATE \n" );
//更新数据
sql = "UPDATE SensorData SET SensorParameter = 100.0,SiteNum = 90 WHERE SensorID = 23 ;" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
#endif
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf( "\n\n\n\nrow:%d column=%d " , nrow , ncolumn );
printf( "\nAfter UPDATE , the result of querying is : \n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );
//释放掉 azResult 的内存空间
sqlite3_free_table( azResult );
#ifdef _DEBUG_
printf("zErrMsg = %s \n", zErrMsg);
#endif
sqlite3_close(db); //关闭数据库
return 0;
}