Even though it's a small app, I hit several things that are worth mentioning. Please note that this is not a recommendation to start doing things differently, it's just a series of issues I found in the process.
Strings
The standard way is to enclose stringsin SQL in simple quotes. No need to escape anything but the simple quote itself. Escaping is done with another simple quote.
insert into contactInfo (contactId, lastName) values (100, 'O''Neal');
Escaping % and _ in the LIKE function is done by adding an explicit the escape character:
select * from contactInfo where firstName like 'hola\_mundo' escape '\';
In Mysql we were using double quotes, and we escape with backslash. This is not cross platform (see ANSI_QUOTES bellow).
Object names - identifiers- (tables, columns, functions, etc)
Almost all database engines are case insensitive when referring to object names. Double quotes is used when you want to use a reserved word, special characters, unicode characters or case sensitivity. If you don't use double quotes, then the names are usually translated to uppercase in the database dictionary, but it remains case insensitive when referring to it.
In Mysql, table names are case sensitive (because they are files in a case sensitive filesystem, at least on unix/linux). This could be changed using the lower_case_table_names parameter. Mysql would convert all table names to lowercase for storage and lookups.
But also in MySQL, columns names are not case sensitive. So
select FirstName from contactInfo;
and
select firstname from contactInfo;
is the same, no matter how you write the SQL to create the table.
In Mysql you can use, like we were doing, backquotes; but that won't help in setting a case to column names (or index, store routines and column aliases)
Mysql Standard SQL
create table `table` ( create table "table" (
`select` varchar(10) "select" varchar(10)
); );
Regarding the use of quotes, we could change the behavior to use the standard doble quotes by setting the ANSI_QUOTES sql mode. Note that once you do change it, all double-quoted strings become identifiers, so all strings are required to be enclosed by single quotes.
SQL statement diferences
INSERT INTO TABLE SET col1 = val1, col2 = val2 is not a valid SQL in other database apart from Mysql. The correct use is INSERT INTO table (<columnlist>) values (<valuelist>), although I have to say that I like this syntax.
SQLite3 does not support TRUNCATE TABLE, but when doing a DELETE without a where clause, it is treated as a truncate statement.
MySQL's
"INSERT IGNORE", "INSERT REPLACE", "INSERT ... ON DUPLICATE KEY UPDATE" and "REPLACE",
and SQLite3's
"INSERT OR REPLACE INTO"
are not standard SQL. The correct SQL to do these tasks is the MERGE statement, but non of them supports it (today). It would be a good practice to wrap all this queries using classes, that way time and work for switching dbs -if we need to- will be greatly reduce.
SQLite3 Types
SQLite3 supports less types than mysql:
SQLITE3_INTEGER => 'int',
SQLITE3_FLOAT => 'float',
SQLITE3_NULL => 'null',
SQLITE3_TEXT => 'text',
SQLITE3_BLOB => 'blob'
You don't need to specify a character set for a text column, you simply insert what you have in a variable. If its UTF8, you insert UTF8, if its ASCII, you insert ASCII.
Bind Variables (or Bound Parameters)
Both databases now support bind variables. We do not use it today, but when running the same query several times Binding variables have good performance advantages since the query is only parsed once. It also removes the SQL Injection problem, since there is no need to escape strings anymore.
Here's a quick example, inserting 10000 rows in a table:
if ($testType == 'Bind') {
$stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values (?,?,?,?,?,?,?,?,?,?)");
for($i=1;$i<=10000;$i++) {
$stmt->bind_param("iiiiiiiiii", $i,$i,$i,$i,$i,$i,$i,$i,$i,$i);
$stmt->execute();
}
} else {
for($i=1;$i<=10000;$i++) {
$stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values ($i,$i,$i,$i,$i,$i,$i,$i,$i,$i)");
$stmt->execute();
}
}
Results:
Bind 10000 inserts done in 0.57499408721924 seconds
No bind 10000 inserts done in 1.006609916687 seconds
The difference could be even greater on SELECT statements with a lot of tables, since the time to parse and generate an execution plan will increase.
When having hundreds of queries per second, like we do, time and CPU reduction could be considerable. Since MySQL 5.1.17, prepared SQL Statements are cached too.
Regards,
Jose.
]]>