Patch: literals in SQL replaced with parameters
Posted: Tue Sep 08, 2009 1:29 pm
Hi,
I have refactored all code which access to sqlite. The most changes occured in dbwrapper.cpp.
Modified files:
M mmex\include\mmex.h
M mmex\include\util.h
M mmex\include\dbwrapper.h
M mmex\include\mmcoredb.h
M mmex\src\mmhomepagepanel.cpp
M mmex\src\assetdialog.cpp
M mmex\src\transdialog.cpp
M mmex\src\qifimport.cpp
M mmex\src\univcsvdialog.cpp
M mmex\src\mmtransaction.cpp
M mmex\src\mmcheckingpanel.cpp
M mmex\src\mmaccount.cpp
M mmex\src\dbwrapper.cpp
M mmex\src\stockspanel.cpp
M mmex\src\budgetingpanel.cpp
M mmex\src\budgetyearentrydialog.cpp
M mmex\src\billsdepositsdialog.cpp
M mmex\src\billsdepositspanel.cpp
M mmex\src\stockdialog.cpp
M mmex\src\newacctdialog.cpp
M mmex\src\mmex.cpp
M mmex\src\currencydialog.cpp
M mmex\src\budgetyeardialog.cpp
M mmex\src\filtertransdialog.cpp
M mmex\src\util.cpp
M mmex\src\assetspanel.cpp
M mmex\src\mmcurrency.cpp
M mmex\src\mmcoredb.cpp
M mmex\src\mmpayee.cpp
List of changes in headers:
1. util.h
These functions were deleted. Now you do not need these functions due to use of SQL parameters.
wxString mmCleanString(const wxString& orig);
wxString mmCleanQuotes(const wxString& orig);
wxString mmUnCleanString(const wxString& orig);
2. dbwrapper.h
Dummy class mmDBWrapper replaced by namespace mmDBWrapper.
3. mmex.h
mmCoreDB* core_ ==> boost::scoped_ptr<mmCoreDB> core_;
wxSQLite3Database* inidb_ ==> boost::scoped_ptr<wxSQLite3Database> inidb_;
Changes in sources
1. Literals in SQL replaced by SQL parameters. This is the main feature of this patch.
NEVER USE LITERALS INSTEAD OF PARAMETERS IN SQL!!!
This is the most common error and misunderstanding how someone should pass parameters to database.
This especially true for client-server databases (Oracle, etc). But sqlite has sql parameters support,
thus this feature is very important for sqlite too.
Functions mmCleanString\mmCleanQuotes\mmUnCleanString became unnesassary due to using parameters.
Why we must use parameters? When database engine executes SQL, one does following steps:
1.Parsing SQL statement.
2.Generating of execution plan for given statement.
3.SQL engine runs prepared execution plan.
SQL engine always have cache of already prepared SQL statements. When sql string matches with string in cache,
engine skips steps #1 & #2. Both these steps can take the same time as step #3.
When you pass to SQL engine strings with literals, almost every sql is unique. There are no cache hits.
Even worse, this flushes prepared statements from cache. But if you pass sql with parameters, the sql string does not change for each execution with different parameters values. Sql engine can find query in cache and execute one without performing first two steps.
2. General optimizations of code.
You should always select from query as much datas as possible.
Always join (if possible) tables in single query instead of executing innner SQL for every record of outer SQL.
This is terrible slow. Only really complex SQL could be better to organize by splitting into several queries.
// ------------
Due to lots of changes across all sources this patch requires goos testing.
I have carefully checked what I changed, but ...
Also I should mention it will be hard to merge these changes to trunk if head revision will go ahead significantly.
// ------------
Finally I recommend you forget about wxSQLite3StatementBuffer, Format, etc.
ALWAYS USE SQL WITH PARAMETERS. wxSQLite3StatementBuffer is completely useless class.
I have refactored all code which access to sqlite. The most changes occured in dbwrapper.cpp.
Modified files:
M mmex\include\mmex.h
M mmex\include\util.h
M mmex\include\dbwrapper.h
M mmex\include\mmcoredb.h
M mmex\src\mmhomepagepanel.cpp
M mmex\src\assetdialog.cpp
M mmex\src\transdialog.cpp
M mmex\src\qifimport.cpp
M mmex\src\univcsvdialog.cpp
M mmex\src\mmtransaction.cpp
M mmex\src\mmcheckingpanel.cpp
M mmex\src\mmaccount.cpp
M mmex\src\dbwrapper.cpp
M mmex\src\stockspanel.cpp
M mmex\src\budgetingpanel.cpp
M mmex\src\budgetyearentrydialog.cpp
M mmex\src\billsdepositsdialog.cpp
M mmex\src\billsdepositspanel.cpp
M mmex\src\stockdialog.cpp
M mmex\src\newacctdialog.cpp
M mmex\src\mmex.cpp
M mmex\src\currencydialog.cpp
M mmex\src\budgetyeardialog.cpp
M mmex\src\filtertransdialog.cpp
M mmex\src\util.cpp
M mmex\src\assetspanel.cpp
M mmex\src\mmcurrency.cpp
M mmex\src\mmcoredb.cpp
M mmex\src\mmpayee.cpp
List of changes in headers:
1. util.h
These functions were deleted. Now you do not need these functions due to use of SQL parameters.
wxString mmCleanString(const wxString& orig);
wxString mmCleanQuotes(const wxString& orig);
wxString mmUnCleanString(const wxString& orig);
2. dbwrapper.h
Dummy class mmDBWrapper replaced by namespace mmDBWrapper.
3. mmex.h
mmCoreDB* core_ ==> boost::scoped_ptr<mmCoreDB> core_;
wxSQLite3Database* inidb_ ==> boost::scoped_ptr<wxSQLite3Database> inidb_;
Changes in sources
1. Literals in SQL replaced by SQL parameters. This is the main feature of this patch.
NEVER USE LITERALS INSTEAD OF PARAMETERS IN SQL!!!
This is the most common error and misunderstanding how someone should pass parameters to database.
This especially true for client-server databases (Oracle, etc). But sqlite has sql parameters support,
thus this feature is very important for sqlite too.
Functions mmCleanString\mmCleanQuotes\mmUnCleanString became unnesassary due to using parameters.
Why we must use parameters? When database engine executes SQL, one does following steps:
1.Parsing SQL statement.
2.Generating of execution plan for given statement.
3.SQL engine runs prepared execution plan.
SQL engine always have cache of already prepared SQL statements. When sql string matches with string in cache,
engine skips steps #1 & #2. Both these steps can take the same time as step #3.
When you pass to SQL engine strings with literals, almost every sql is unique. There are no cache hits.
Even worse, this flushes prepared statements from cache. But if you pass sql with parameters, the sql string does not change for each execution with different parameters values. Sql engine can find query in cache and execute one without performing first two steps.
2. General optimizations of code.
You should always select from query as much datas as possible.
Always join (if possible) tables in single query instead of executing innner SQL for every record of outer SQL.
This is terrible slow. Only really complex SQL could be better to organize by splitting into several queries.
// ------------
Due to lots of changes across all sources this patch requires goos testing.
I have carefully checked what I changed, but ...
Also I should mention it will be hard to merge these changes to trunk if head revision will go ahead significantly.
// ------------
Finally I recommend you forget about wxSQLite3StatementBuffer, Format, etc.
ALWAYS USE SQL WITH PARAMETERS. wxSQLite3StatementBuffer is completely useless class.