Each database typically contains multiple tables, and each table contains multiple pieces of data. To obtain data in the database, you need the QUERY statement SELECT provided by THE SQL language. This chapter covers SELECT statements, including basic SELECT forms, expressions, joins, grouping, sorting, and de-duplicating, fetching subsets, subqueries, and union queries.
3.1 Basic Form of SELECT
This section explains the basic forms of SELECT statements, including the basic complete form, SELECT clause, FROM clause, and WHERE clause
3.1.1 Basic form
Here is the basic complete form of the SELECT syntax:
SELECT [DISTINCT] select_heading
FROM source_tables
WHERE filter_expression
GROUP BY grouping_expressions
HAVING filter_expression
ORDER BY ordering_expressions
LIMIT count
OFFSET count
The parameters are described as follows:
select_heading: used to define the format and content of the final result table, mainly used to define the header of the table.
Note: In SQL, data structures are tables, which are used for storing data and processing. A table consists of a head and a body. The header defines the name and type (in SQLite) of each column. Column names must be unique in the table. The header defines the order of the columns that are fixed as part of the table definition. The body contains all rows. Each row consists of one data element per column. All rows of each column in the table must have the same number of data elements, and each element can hold a data value (or a NULL).
- FROM source_tables: Specify one or more source tables and combine them into one large worksheet.
- WHERE filter_expression: Filters specific rows in the worksheet.
- GROUP BY grouping_expressions: Specifies groups.
- HAVING filter_expression: Filters specific rows in a grouping table.
- ORDER BY ordering_expressions: Sort the rows of the result set.
- LIMIT count: Limits the output of the result set to a specific number of rows.
- OFFSET count: skips the beginning line of the result set.
Note: DISTINCT is optional to eliminate duplicate rows. In addition to DISTINCT, additional clauses (FROM, WHERE, GROUP BY, and so on) are optional in SELECT statements.
Clauses in SELECT statements are not executed in the order in which they are written. The execution process is shown in Figure 3.1.
Figure 3.1 Execution flow
Note: The number after R in Figure 3.1 can be considered the order of execution.
(1) The query statement requires one or two source tables.
(2) Select R1 FROM table;
(3) The WHERE clause filters rows in table R1 and generates a new table R2.
(4) THE R2 table is grouped through the GROUP BY clause, and the R2 table is grouped into the corresponding GROUP. At this time, the R3 table will be born.
(5) The R4 table is filtered by HAVING clauses.
(6) R4 table through the SELECT clause to execute the format and content of the final result table to display, then the R5 table will be generated.
(7) The R5 table is then de-duplicated using the DISTINCT keyword, and the R6 table will be generated.
(8) R6 will be sorted BY the ORDER BY clause, and R7 table will be generated.
(9) Table R7 creates a new table R8 by skipping the first row of the table through the OFFSET clause.
Table R8 is restricted to a specified number of rows by the LIMIT clause.
Sqlite collation
- BINARY – compares string data using memcmp(), regardless of text encoding.
- NOCASE – The same as BINARY, except that the 26 uppercase characters of the ASCII code are converted to their equivalent lowercase characters before the comparison is performed. Note that only ASCII characters are case – cased. Due to the size of the tables required, SQLite does not attempt to fold the case of the full UTF.
- RTRIM – Same as BINARY, except trailing whitespace is ignored.
Assign collation sequences from SQL
Each column of each table has an associated collation function. If no recalibration function is explicitly defined, the recalibration rule defaults to BINARY. The COLLATE clause of a column definition is used instead to define collation functions for a column.
For a binary comparison operator (=, <, >, <=, >=,! =, IS, and IS NOT) decide which collation function to use, in the order shown below:
- If both operands are assigned an explicit collation function using the COLLATE suffix operator, the explicit collation function is used in the comparison, and the collation function of the left-hand operand has higher precedence.
- If both operands are one column, the collation function for the left column has higher precedence. The purpose of the previous sentence is that a column name with one or more unary “+” operators is still considered a column name.
- Otherwise, the comparison will use BINARY proofreading functions.
The expression “x BETWEEN y AND z” is logically equivalent to two comparisons “x >= y AND x <= z”, AND works in conjunction with the collation function as if there were two separate comparisons. X IN (SELECT y…) “Is handled in the same way as” x = y “in order to determine the collation sequence. Used IN “x IN (y z…)” The collation sequence on the format expression is the collation sequence of x.
The ORDER BY clause is part of a SELECT statement, and the rule is that a collation sequence can be assigned to the SELECT statement using the COLLATE operator, in which case the specific collation function is used for sorting. Otherwise, if the expression sorted BY the ORDER BY clause is a column, the collation sequence of the columns will be used to determine the sort ORDER. If the expression is not a column and there is no COLLATE clause, the BINARY COLLATE sequence will be used.
Proof sequence example
The following example is used to identify collation sequences, which can be used to determine the result of text comparisons executed by various SQL statements. Note that in the case of NUMERIC, BLOB, or NULL values, a text comparison is not required, and collation sequences are not used.
CREATE TABLE T1 (x INTEGER PRIMARY KEY a /* COLLATE BINARY */ b COLLATE BINARY */ c COLLATE RTRIM /* COLLATE sequence RTRIM */ d COLLATE NOCASE */); /* x a b c d */ INSERT INTO t1 VALUES(1 ‘abc’ ‘abc’ ‘abc ‘ ‘abc’); INSERT INTO t1 VALUES(2 ‘abc’ ‘abc’ ‘abc’ ‘ABC’); INSERT INTO t1 VALUES(3 ‘abc’ ‘abc’ ‘abc ‘ ‘Abc’); INSERT INTO t1 VALUES(4 ‘abc’ ‘abc ‘ ‘ABC’ ‘abc’); /* Execute text comparison a = b using BINARY collation sequence. */ SELECT x FROM t1 WHERE a = b ORDER BY x; Result 1 2 3 /* Perform text comparison a = b using RTRIM trim sequence. */ SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x; Result 1, 2, 3, 4 /* Perform text comparison d = a using NOCASE collation sequence. */ SELECT x FROM t1 WHERE d = a ORDER BY x; 1, 2, 3, 4 /* A = d */ SELECT x FROM t1 WHERE a = d ORDER BY x; Result 1 4 /* Perform text comparison ‘ABC’ = c using RTRIM trim sequence. */ SELECT x FROM t1 WHERE ‘abc’ = c ORDER BY x; — result 1 2 3 /* Perform text comparison c = ‘ABC’ using RTRIM trim sequence. */ SELECT x FROM t1 WHERE c = ‘abc’ ORDER BY x; Results 1, 2, 3 /* are grouped using the NOCASE collation sequence (values “ABC”, “ABC”, and “ABC” are placed in the same grouping). */ SELECT count(*) FROM t1 GROUP BY d ORDER BY 1; Result 4 /* Perform grouping using BINARY collation sequences. “ABC”, “ABC” and “ABC” come from different groupings. */ SELECT count(*) FROM t1 GROUP BY (d || ”) ORDER BY 1; Result 1 1 2 /* Collate the sequence or column C with RTRIM. */ SELECT x FROM t1 ORDER BY c x; – 4 1 2 3 / * use BINARY check sequence or (c | | ‘) perform sorting. */ SELECT x FROM t1 ORDER BY (c||”) x; Result 4 2 3 1 /* Use NOCASE to collate the sequence or column C. */ SELECT x FROM t1 ORDER BY c COLLATE NOCASE x; So 2, 4, 3, 1
SQLite custom functions, aggregations, collations
1. The basic way to use custom functions, aggregations, and collations is to use callback functions. The life cycle of these registered functions exists only in the application and is not stored in a database file, so they need to be registered at each connection establishment before they can be used in SQL.
When SQLite sorts the fields in a result set, SQLite uses comparison operators such as < or >= to compare values within the fields. The first thing SQLite does is sort the field values by storage class. It then sorts each storage class according to the method specified by that class. The storage classes are sorted from front to back as follows: NULL values < INTEGER and REAL values < TEXT values < BLOB values (1) A NULL value has the lowest class value, and a value of a NULL store class is smaller than all other values (including all other values of a NULL store class). There is no specific sort order between NULL values. (2) INTEGER or REAL stores class values higher than NULL, and their class values are equal. The value of the TEXT storage class is higher than the value of INTEGER and REAL. The value is always lower than the value of the string. When two TEXT values are compared, the value size is determined by the “collation” defined in that value. (4) The BLOB storage class has the highest class value. Values with BLOB classes are greater than values for all other classes. CREATE TABLE Foo(x TEXT COLLATE NOCASE); CREATE TABLE Foo(x TEXT COLLATE NOCASE); SELECT * FROM Foo ORDER BY x COLLATE NOCASE;
Int sqlite3_create_function_v2(sqlite3* db, const char* zFunctionName, int nArg, int eTextRep, void* pApp, void (*xFunc)(sqlite3_context* ctx, int argc, sqlite3_value** argv), void (*xStep)(sqlite3_context* ctx, int argc, sqlite3_value** argv), void (*xFinal)(sqlite3_context* ctx), void (*xDestroy)(void* pApp) ); Db: Specified connections for database connection handles, functions, and aggregates. To use a function, you must register zFunctionName on the connection: function used in the SQL statement, aggregate name, length limit 255 bytes nArg: Number of function arguments, if -1, variable length arguments. SQLite forces the number of parameters to be checked to ensure that the format of the parameters passed to the custom function is correct eTextRep: first, the text encoding format is SQLITE_UTF8, SQLITE_UTF16 pApp: The application data passed to the callback function is available for use in the callbacks specified by xFunc, xStep, and xFinal, but special API functions must be used to retrieve the data. SQLite calls xStep for each row in the aggregate result set. This function processes the aggregate logic internally and stores it in the result value xFinal: SQLite will call the Finalize aggregation function to summarize the whole aggregation after processing all the rows. The result of the aggregation function is set to xFunc in the custom function, and xStep and xFinal are set to nullPtr. Custom aggregate functions are the opposite. XStep and xFinal must be set, and xFunc must be set to nullPtr. As long as nArg specifies a different number of arguments, or eTextRep specifies a different encoding, multiple versions of the function specified by the same zFunctionName can be registered. SQLite automatically selects the best version xDestroy: (2) int sqlite3_create_collation_v2(sqlite3* db, const char* zFunctionName, int eTextRep, void* pApp, int (*xCompare)(void* pApp, int lLen, const void* lData, int rLen, const void* rData), void (*xDestroy)(void* pApp) ); The meanings of each parameter are the same as sqlite3_create_function_v2. Void xFunc(sqlite3_context* CTX, int argc, sqlite3_value** argv); CTX: context of function/aggregation. It holds the instance state of a particular function call from which to obtain the application data parameter pApp for SQlite3_create_function_v2. To retrieve this data, use void* sqlite3_user_data(sqlite3_context* CTX); Argc: number of parameters argv: Void * pApp, int lLen, const void* lData, int rLen, const void* rData); PApp: application data set in sqlite3_create_COLLation_v2 lLen: length of parameter 1 lData: pointer to parameter 1 rLen: length of parameter 2 rData: pointer to parameter 2 PS: When arguments 1 and 2 are passed to const void* and converted to const char*, the string does not necessarily end in ‘\0’. String lText{static_cast
(lData)}; Another constructor for string should be string lText{static_cast
(lData), 0, lLen}; (5) void *sqlite3_user_data(sqlite3_context* ctx); Gets the value of the pApp argument passed in when sqlite3_create_function_v2 is called. Void * sqlite3_aggregate_context(sqlite3_context* CTX, int nBytes); This function assigns state to each particular instance, and the first time it is called it executes a special aggregate function that allocates nBytes of memory and assigns a value of 0. It is important to note that memory allocation assigns a value of 0, which requires that we set our custom type to be POD when storing aggregate data. Subsequent calls in the same CTX-specified context (an instance of the same aggregation) return the same data, that is, the same address. Aggregation functions must use this function, so the current point of aggregation is to store state between calls to stack data. When the aggregation completes the Finalize () callback, SQLite automatically frees the memory without manual frees.
(7) Type sqlite3_value_Type(sqlite3_value* value); Returns data of Type Type. Sqlite3_value_double (sqlite3_value*); int sqlite3_value_int(sqlite3_value*); sqlite3_int64 sqlite3_value_int64(sqlite3_value*); Int sqlite3_value_bytes(sqlite3_value*); Const void* sqlite3_value_blob(sqlite3_value*); // Return a pointer to the data in the blob buffer const unsigned char* sqlite3_value_text(sqlite3_value*); Const char* Checks the data type function: int sqlite3_value_type(sqlite3_value*); // SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT int sqlite3_value_numeric_type(sqlite3_value*); Pairing uses the sqlite3_value_bytes and sqlite3_value_blob functions to copy data from the result. For example, int valueLen = sqlite3_value_bytes(argv[0]); char* buf = static_cast
(sqlite3_malloc(valueLen + 1)); Void sqlite3_result_text(sqlite3_context* CTX, const char* data, int len, void(*free)(void* data)); void sqlite3_result_blob(sqlite3_context* ctx, const void* data, int bytes, void(*free)(void* data)); void sqlite3_result_double(sqlite3_context*, double); void sqlite3_result_int64(sqlite3_context*, sqlite3_int64); void sqlite3_result_null(sqlite3_context*); Void sqlite3_result_error(sqlite3_context, const char*, int); void sqlite3_result_error_toobig(sqlite3_context*); void sqlite3_result_error_nomem(sqlite3_context*); void sqlite3_result_error_code(sqlite3_context*, int); Void free(void* p); After a user-defined function is invoked, the specified clearing function is invoked to release memory. SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void*))-l) SQLITE_STATIC means that the array memory resides in unmanaged space and SQLite does not require a copy of the data and does not attempt to clean it up. Void * sqlite3_malloc(int); sqlite3_malloc(int); void* sqlite3_realloc(void*, int); void sqlite3_free(void*);
//
// 测试代码
#include <cassert>
#include <fstream>
#include <iostream>
#include <regex>
#include <sstream>
#include <string>
#include "Memory.h"
#include "Rand.h"
#include "sqlite3.h"
using namespace std;
//#define OUTPUT_FILE
#if defined(OUTPUT_FILE)
#define ERR fout << "[" << __func__ << ":" << __LINE__ << "] Error! "
#define INFO fout
#define DEBUG fout
#else
#define ERR cerr << "[" << __func__ << ":" << __LINE__ << "] Error! "
#define INFO cout
#define DEBUG cerr
#endif
#define MEMINFO(msg) Memory::print(msg)
#if defined(OUTPUT_FILE)
fstream fout;
#endif
void initOutputStream()
{
#if defined(OUTPUT_FILE)
fout.open("F:/Sqlite/mysql/log.txt", fstream::ios_base::out | fstream::ios_base::trunc);
#endif
}
void testClean(void* p)
{
DEBUG << "clean:" << reinterpret_cast<int>(p) << endl;
}
string getString(sqlite3_value* value)
{
const char* text = reinterpret_cast<const char*>(sqlite3_value_text(value));
return text ? string{text} : "";
}
/**
* 将字符串拆分成非全数字组成的前缀,和全数字组成的后缀
*/
bool splitName(const string& text, string& prefix, string& suffix)
{
regex reg("^(.*[^\\d]+)(\\d+)$|^(\\d+)$");
smatch match;
if (regex_match(text, match, reg))
{
prefix = match.str(1);
suffix = prefix.empty() ? match.str(3) : match.str(2);
return true;
}
prefix = text;
return false;
}
/**
* 去掉字符串前面的0
*/
string trimPrefixZero(const string& text)
{
size_t pos = text.find_first_not_of('0');
if (string::npos != pos && 0 != pos)
{
return text.substr(pos);
}
return text;
}
/**
* 比较两个数字字符串序列的大小
*/
int compareDigit(const string& lhs, const string& rhs)
{
string lText = trimPrefixZero(lhs);
string rText = trimPrefixZero(rhs);
if (lText.length() != rText.length())
{
return lText.length() - rText.length();
}
for (size_t i = 0; i < lText.length(); ++i)
{
if (lText.at(i) != rText.at(i))
{
return lText.at(i) - rText.at(i);
}
}
return 0;
}
sqlite3* openDB(const string& file)
{
sqlite3* db{};
int ret = sqlite3_open_v2(file.c_str(), &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, 0);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_open_v2 failed." << endl;
return nullptr;
}
return db;
}
bool closeDB(sqlite3* db)
{
int ret = sqlite3_close_v2(db);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_close_v2 failed." << endl;
return false;
}
return true;
}
bool execSQL(sqlite3* db, const string& sql)
{
char* errmsg{};
int ret = sqlite3_exec(db, sql.c_str(), 0, 0, &errmsg);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_exec failed." << errmsg << endl;
}
sqlite3_free(errmsg);
return SQLITE_OK == ret;
}
bool printSQL(sqlite3* db, const string& sql)
{
int ret{};
sqlite3_stmt* stmt{};
ret = sqlite3_prepare_v2(db, sql.c_str(), sql.length(), &stmt, 0);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_prepare_v2 failed." << sqlite3_errmsg(db) << endl;
return false;
}
INFO << sql << endl;
int colCount = sqlite3_column_count(stmt);
if (colCount > 0)
{
for (int col = 0; col < colCount; )
{
INFO << sqlite3_column_name(stmt, col);
if (++col != colCount)
{
INFO << '\t';
}
}
INFO << endl;
}
do
{
ret = sqlite3_step(stmt);
if (SQLITE_ROW != ret)
{
break;
}
for (int col = 0; col < colCount; )
{
const char* text = reinterpret_cast<const char*>(sqlite3_column_text(stmt, col));
INFO << (text ? text : "");
if (++col != colCount)
{
INFO << '\t';
}
}
INFO << endl;
} while (true);
if (SQLITE_DONE != ret)
{
ERR << "sqlite3_step failed." << sqlite3_errmsg(db) << endl;
}
ret = sqlite3_finalize(stmt);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_finalize failed." << sqlite3_errmsg(db) << endl;
return false;
}
return true;
}
// SQLite 函数
void echo(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
assert(1 == argc);
// DEBUG << "user data:" << reinterpret_cast<int>(sqlite3_user_data(ctx)) << endl;
const char* text{reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))};
sqlite3_result_text(ctx, text, -1, 0);
}
void strcat_column(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
const char* delimiter{"\t"};
const int DELIMITER_LEN = strlen(delimiter);
int len = 0;
for (int i = 0; i < argc; ++i)
{
len += sqlite3_value_bytes(argv[i]);
len += DELIMITER_LEN;
}
char* buf = static_cast<char*>(sqlite3_malloc(len));
int pos = 0;
for (int i = 0; i < argc; )
{
int bytes = sqlite3_value_bytes(argv[i]);
memcpy(buf + pos, sqlite3_value_blob(argv[i]), bytes);
pos += bytes;
if (++i != argc)
{
memcpy(buf + pos, delimiter, DELIMITER_LEN);
pos += DELIMITER_LEN;
}
}
buf[pos] = 0;
sqlite3_result_text(ctx, buf, -1, 0);
}
/**
* 找出值为匹配前缀,并且后缀在指定范围内的值
*/
void peekName(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
assert(4 == argc);
bool isFound{false};
string vPrefix;
string vSuffix;
string value = getString(argv[0]);
if (splitName(value, vPrefix, vSuffix))
{
string prefix = getString(argv[1]);
if (vPrefix == prefix)
{
string suffixFrom = getString(argv[2]);
string suffixTo = getString(argv[3]);
if (compareDigit(vSuffix, suffixFrom) >= 0 && compareDigit(vSuffix, suffixTo) <= 0)
{
isFound = true;
}
}
}
sqlite3_result_int(ctx, isFound);
}
// SQLite 聚合函数
struct AggregateCharData
{
int len{};
char* buf{};
};
void strcat_step(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
assert(2 == argc);
AggregateCharData* pData{static_cast<AggregateCharData*>(sqlite3_aggregate_context(ctx, sizeof(AggregateCharData)))};
if (!pData)
{
ERR << "Alloc AggregateData failed!" << endl;
return;
}
int valueLen = sqlite3_value_bytes(argv[0]);
if (!pData->buf)
{
pData->buf = static_cast<char*>(sqlite3_malloc(valueLen + 1));
}
else
{
int delimiterLen = sqlite3_value_bytes(argv[1]);
int len = valueLen + delimiterLen + pData->len + 1;
pData->buf = static_cast<char*>(sqlite3_realloc(pData->buf, len));
memcpy(pData->buf + pData->len, sqlite3_value_blob(argv[1]), delimiterLen);
pData->len += delimiterLen;
}
memcpy(pData->buf + pData->len, sqlite3_value_blob(argv[0]), valueLen);
pData->len += valueLen;
}
void strcat_final(sqlite3_context* ctx)
{
AggregateCharData* pData{static_cast<AggregateCharData*>(sqlite3_aggregate_context(ctx, sizeof(AggregateCharData)))};
if (!pData || !pData->buf)
{
sqlite3_result_text(ctx, pData->buf, pData->len, sqlite3_free);
}
}
/**
* SQLite 排序示例,将值拆分成非全数字组成的前缀,和全数字组成的后缀,前缀不同时,按字典排序;前缀相同时,后缀按数字大小排序
**/
int compareName(void*, int lhsLen, const void* lhsData, int rhsLen, const void* rhsData)
{
string lText(static_cast<const char*>(lhsData), 0, lhsLen);
string lPrefix;
string lSuffix;
if (!splitName(lText, lPrefix, lSuffix))
{
return 0;
}
string rText(static_cast<const char*>(rhsData), 0, rhsLen);
string rPrefix;
string rSuffix;
if (!splitName(rText, rPrefix, rSuffix))
{
return 0;
}
if (lPrefix != rPrefix)
{
return lPrefix.compare(rPrefix);
}
return compareDigit(lSuffix, rSuffix);
}
string randNumString()
{
static const string NUMBERS{"012345678900"};
string ret;
int len = Rand::rand(3, 10);
for (int i = 0; i < len; ++i)
{
ret.push_back(NUMBERS.at(Rand::rand(0, NUMBERS.length() - 1)));
}
return ret;
}
string randPrefix()
{
static const string PREFIX_STR{"01234567890123456789012345678901234567890123456789"
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz+-"};
string ret;
int len = Rand::rand(3, 8);
for (int i = 0; i < len; ++i)
{
ret.push_back(PREFIX_STR.at(Rand::rand(0, PREFIX_STR.length() - 1)));
}
return ret;
}
void insertTestData(sqlite3* db)
{
execSQL(db, "INSERT INTO Foo VALUES(null, 1, 2);");
execSQL(db, "INSERT INTO Foo VALUES(null, 10.5, 20.1);");
execSQL(db, "INSERT INTO Foo VALUES(null, 'abc', 'hehe');");
vector<string> prefixs{randPrefix(), randPrefix(), randPrefix(), randPrefix()};
ostringstream stm;
for (int i = 0; i < 30; ++i)
{
string prefix = prefixs.at(Rand::rand(0, prefixs.size() - 1));
stm.str("");
stm << "INSERT INTO Foo VALUES(null, '" << prefix << randNumString() << "', "
<< Rand::rand(1, 999) << ");";
execSQL(db, stm.str());
stm.str("");
stm << "INSERT INTO Foo VALUES(null, '" << prefix << randNumString() << "', "
<< Rand::rand(1, 999) << ");";
execSQL(db, stm.str());
stm.str("");
stm << "INSERT INTO Foo VALUES(null, '" << prefix << randNumString() << "', "
<< Rand::rand(1, 999) << ");";
execSQL(db, stm.str());
}
}
int main(int, char**)
{
initOutputStream();
MEMINFO("start");
sqlite3* db = openDB("Foo.db");
int ret{};
MEMINFO("create table");
execSQL(db, "CREATE TABLE IF NOT EXISTS Foo"
"(_id INTEGER PRIMARY KEY, name TEXT, info TEXT);");
// insertTestData(db);
MEMINFO("query all");
printSQL(db, "SELECT * FROM Foo;");
MEMINFO("set echo");
ret = sqlite3_create_function_v2(db, "echo", 1, SQLITE_UTF8, reinterpret_cast<void*>(1), echo, 0, 0, testClean);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_create_function_v2 -> echo." << sqlite3_errmsg(db) << endl;
}
MEMINFO("query using echo");
printSQL(db, "SELECT echo('Hello SQLite!') AS replay;");
printSQL(db, "SELECT _id, echo(name) AS name FROM Foo;");
MEMINFO("set strcat_column");
ret = sqlite3_create_function_v2(db, "strcat_column", -1, SQLITE_UTF8, 0, strcat_column, 0, 0, 0);
if (SQLITE_OK != ret)
{
ERR << "sqlite3_create_function_v2 -> strcat_column." << sqlite3_errmsg(db) << endl;
}
MEMINFO("query using strcat_column");
printSQL(db, "SELECT strcat_column(_id, name, info) AS list FROM Foo;");
MEMINFO("set strcat");
ret = sqlite3_create_function_v2(db, "strcat", 2, SQLITE_UTF8, 0, 0, strcat_step, strcat_final, 0);
MEMINFO("query using strcat");
if (SQLITE_OK != ret)
{
ERR << "sqlite3_create_function_v2 -> strcat_column." << sqlite3_errmsg(db) << endl;
}
printSQL(db, "SELECT strcat(_id, ',') AS ids FROM Foo;");
printSQL(db, "SELECT strcat(name, ',') AS names FROM Foo;");
printSQL(db, "SELECT strcat(info, ',') AS infos FROM Foo;");
MEMINFO("set peekName");
ret = sqlite3_create_function_v2(db, "peekName", 4, SQLITE_UTF8, 0, peekName, 0, 0, 0);
MEMINFO("query using peekName");
if (SQLITE_OK != ret)
{
ERR << "sqlite3_create_function_v2 -> peekName." << sqlite3_errmsg(db) << endl;
}
printSQL(db, "SELECT * FROM Foo WHERE peekName(name, '9Y58h', 293, 62804);");
MEMINFO("set compareName");
ret = sqlite3_create_collation_v2(db, "compareName", SQLITE_UTF8, reinterpret_cast<void*>(2), compareName, testClean);
MEMINFO("query using compareName");
if (SQLITE_OK != ret)
{
ERR << "sqlite3_create_collation_v2 -> compareName." << sqlite3_errmsg(db) << endl;
}
printSQL(db, "SELECT * FROM Foo ORDER BY name collate compareName;");
MEMINFO("close db");
closeDB(db);
MEMINFO("end");
return 0;
}
Copy the code// // random number #ifndef RAND_H #define RAND_H #include <random> class Rand {public: static int rand(int minValue, int maxValue); private: static std::default_random_engine DEFAULT_ENGINE; }; #endif // RAND_H #include "Rand.h" #include <ctime> std::default_random_engine Rand::DEFAULT_ENGINE(time(0)); int Rand::rand(int minValue, int maxValue) { std::uniform_int_distribution<int> d(minValue, maxValue); return d(DEFAULT_ENGINE); }Copy the code// // memory printing tools #ifndef MEMORY_H #define MEMORY_H #include <string> using STD ::string; class Memory { public: static void print(const string& tag = ""); private: static string format(long long); static long long m_prevAvaiPhys; }; #endif // MEMORY_H #include "Memory.h" #include <iostream> #include <sstream> #include "windows.h" long long Memory::m_prevAvaiPhys{}; void Memory::print(const string& tag) { MEMORYSTATUSEX mem{}; mem.dwLength = sizeof(MEMORYSTATUSEX); GlobalMemoryStatusEx(&mem); std::cout << "virtual:" << format(mem.ullAvailVirtual) << "/" << format(mem.ullTotalVirtual) << " physical:" << format(mem.ullAvailPhys) << "/" << format(mem.ullTotalPhys); if (0 ! = m_prevAvaiPhys) { std::cout << ", reduce physical:" << (long long)(m_prevAvaiPhys - mem.ullAvailPhys) << "B"; } m_prevAvaiPhys = mem.ullAvailPhys; std::cout << " [" << tag << "]" << std::endl; } string Memory::format(long long bytes) { std::ostringstream stm; //stm << (bytes >> 20) << "MB"; stm << (bytes >> 10) << "KB"; return stm.str(); }Copy the code