DB Discussions: Transaction Model, Savepoints, Locking; Exception Handling; Caching: Difference between revisions
No edit summary |
|||
| (6 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
==Overview== | |||
''This page served as a 'blackboard' for the Architecture Team to discuss aspects of DB access in OpenPetra. As such, personal opinions and findings are recorded.'' | |||
== Notes Thias == | |||
Never add values to the SQL statement string directly. Instead, always use SQL Statement Parameters! Otherwise there is a good chance that you will run into errors because of not correctly quoting values to strings! --[[User:Thiasg|Thiasg]] 08:38, 10 May 2011 (UTC) | |||
ChristianK adds: Using SQL Statement Parameters also helps in preventing [https://www.owasp.org/index.php/SQL_Injection SQL Injection Attacks]. | |||
==Transaction Model== | ==Transaction Model== | ||
TODO Documentation | TODO Documentation | ||
| Line 42: | Line 50: | ||
===General=== | ===General=== | ||
Maybe utilise the 'general wrapper Class for UIConnectors and WebConnectors' mentioned above to implement general DB Access Exception Handling? | Maybe utilise the 'general wrapper Class for UIConnectors and WebConnectors' mentioned above to implement general DB Access Exception Handling? | ||
Until we have that in place the guidance found [[DB Access Exception Handling | here]] needs to be followed. | |||
===SubmitChanges Methods=== | ===SubmitChanges Methods=== | ||
At the moment our auto-generated '...SubmitChanges' Methods in the openPETRA Datastore return a TVerificationResultCollection, but that should be changed. We are planning changes on Exception Handling in openPETRA - these Methods will then throw a specific Exception instead (with InnerExceptions, if necessary), which can be evaluated and displayed on the client side in a standard way. There isn't really a point in handling database errors in TVerifcationResultCollections, as a database error is always unexpected when data is saved and therefore it is appropriate to raise an Exception in this case, which can escalate quickly and can't be missed. | At the moment our auto-generated '...SubmitChanges' Methods in the openPETRA Datastore return a TVerificationResultCollection, but that should be changed. We are planning changes on Exception Handling in openPETRA - these Methods will then throw a specific Exception instead (with InnerExceptions, if necessary), which can be evaluated and displayed on the client side in a standard way. There isn't really a point in handling database errors in TVerifcationResultCollections, as a database error is always unexpected when data is saved and therefore it is appropriate to raise an Exception in this case, which can escalate quickly and can't be missed. | ||
--> '''This has been adressed''' in January 2014: DB-level, DB-abstraction layer-level and DataAccess-Class-Level Exceptions are no longer wrapped in TVerificationResultCollection objects. Since this change they are are simply allowed to happen so they can escalate and can be handled in the usual C# way. --[[User:Christiankatict|Christiankatict]] 07:56, 27 March 2014 (UTC) | |||
==Caching of Data (general)== | ==Caching of Data (general)== | ||
TODO | TODO | ||
<nowiki>Insert non-formatted text here</nowiki> | |||
Latest revision as of 14:27, 24 April 2014
Overview
This page served as a 'blackboard' for the Architecture Team to discuss aspects of DB access in OpenPetra. As such, personal opinions and findings are recorded.
Notes Thias
Never add values to the SQL statement string directly. Instead, always use SQL Statement Parameters! Otherwise there is a good chance that you will run into errors because of not correctly quoting values to strings! --Thiasg 08:38, 10 May 2011 (UTC)
ChristianK adds: Using SQL Statement Parameters also helps in preventing SQL Injection Attacks.
Transaction Model
TODO Documentation
ChristianK to come up with a general wrapper Class for UIConnectors and WebConnectors which would start and commit/rollback DB Transactions for us (as suggested by ThiasG), relieving programmers of writing code for those tasks over and over.
Savepoints
As of now, we don't use Savepoints in openPETRA.
Investigation
We should do an investigation of whether Savepoints are available.
- Savepoints got generally introduced to .NET with ADO.NET 2.0, but their support is depending on the underlying DB system
- Need to check availability for and perform tests with
- PostgreSQL
- MySQL
- SQLite
- ODBC in general (for other DB systems without a specific .NET driver)
ChristianK recons we can use Savepoints only
- if they are supported at least with PostgreSQL, MySQL and SQLite
- if they work on Windows and Linux alike.
ChristianK's 'dumping ground' of some initial investigations:
- General
- PostgreSQL
Locking
TODO Documentation
DB Access Exception Handling
General
Maybe utilise the 'general wrapper Class for UIConnectors and WebConnectors' mentioned above to implement general DB Access Exception Handling?
Until we have that in place the guidance found here needs to be followed.
SubmitChanges Methods
At the moment our auto-generated '...SubmitChanges' Methods in the openPETRA Datastore return a TVerificationResultCollection, but that should be changed. We are planning changes on Exception Handling in openPETRA - these Methods will then throw a specific Exception instead (with InnerExceptions, if necessary), which can be evaluated and displayed on the client side in a standard way. There isn't really a point in handling database errors in TVerifcationResultCollections, as a database error is always unexpected when data is saved and therefore it is appropriate to raise an Exception in this case, which can escalate quickly and can't be missed.
--> This has been adressed in January 2014: DB-level, DB-abstraction layer-level and DataAccess-Class-Level Exceptions are no longer wrapped in TVerificationResultCollection objects. Since this change they are are simply allowed to happen so they can escalate and can be handled in the usual C# way. --Christiankatict 07:56, 27 March 2014 (UTC)
Caching of Data (general)
TODO Insert non-formatted text here