Re: Transaction Oriented Architecture (TOA)

Thomas Gagne wrote:
If your application's SQL was already referencing the account table it
will need to be modified to reference the view instead--or you'll have
to rename the account table and replace it with a view--which is certain
to mess up other account table dependents.

Why? What is wrong with using the same name? Some DBA's say that one
should put "_VW" in all view names, but that is the same debate as the
hungarian notation debate, and that is a long on-going debate that I
don't want to get into again. I am generally against hungarian
notation, but I am not zealotic against mild usage.

A global search-and-replace of just table names is not that difficult
anyhow. If your app is so important/fragile that such might brake
something, then consider unit testing. Unit testing is generally
recommended for big financial apps anyhow.


Additionally, there's no transaction history on SELECT statements
(unless you wanted to enable security auditing.. but that's nasty).

Are you saying that your DBMS cannot log view-based query access? If
so, that is a flaw/shortcomming of the DB product, not of relational in
general. Some OO'ers suggest they like OO because even though one ends
up reinventing a lot of DB-like features in OO app code, at least they
have control over it such that they can add missing features. But that
is almost like saying one should write their own word-processor because
MS-Word has bugs. There are open-source RDBMS such as PostGre that
permit one to alter the source code if needed, if you want to go that
route.


How
do you make sure the requesting user has permission to see the view?
How do you record their query? How do you make sure they only see
accounts their allowed to see? Those features aren't present in views
'cause views don't have parameters (like user ID etc--unless you've
added application user IDs to the database).

My comments above apply. If you need that tight of control, perhaps
replace the SQL of the affected queries with stored procedures, which
allow custom coding of logging and access control. I generally don't
recommend stored procedures, but for tighter control (at the expense of
more busy-work) they may work better for you.



In our system's we'd have something like p_account(@sessionKey,
@accountKey). The procedure would be able to verify permissions through
the sessionKey (find the user, which party they belong to, which
accounts belong to them, and if they have READ access), can record the
transaction (sessionKey, transaction type, accountNumber), AND bury its
implementation behind its interface (procedure name and parameters) so
that changes to the account table, security, or transaction logging
won't affect application code.

Implementing our system with a TOA/TOP design has afforded us unusual
responsiveness to fixes and enhancements--many able to wind their way
through development, QA, and into production within hours of the
request--with minimal changes to applications. The fewer changes we
make the faster and more confidently we can move to production.

I see no reason why a flexible RDBMS cannot do the same. Switch to
PostGre, the most powerful open-source RDBMS, and add features that it
does not have that you need.

Further, whatever wrapping you suggest for SQL, procedures can do the
same. OOP is not required to wrap SQL. I do function-based wrapping all
the time for commonly-used SQL activities. Thus the "should wrap SQL"
and "OOP wraps better" issues/claims are generally different issues.
I'll believe OOP wraps better than procedural only when I see
side-by-side comparisions of typical, common, and representative
scenarios.


--
Visit <http://blogs.instreamfinancial.com/anything.php>
to read my rants on technology and the finance industry.

-T-

.

Relevant Pages

  • Re: sql problem
    ... Account # is the link field on ... The requirement is I have to come up with a report comparing the two ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... Account # is the link field on ... table1, table2@dblink where ....) ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
  • Re: sql problem
    ... Account # is the link field on ... table1, table2@dblink where ....) ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" ...
    (comp.databases.oracle.server)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... If a SQL account is used then the only thing needed on the ... machine running SQL is SQL login and database grants. ... If integrated, then in addition, I have seen the account need ... then changing this to use trusted connection ...
    (microsoft.public.windows.server.security)
  • Re: Problems connecting to SQL Server
    ... Maximo is specifically stating that you must use the SA user ... to be the SQL 7 Product ID while scrounging around in some boxes. ... I will also try MDAC. ... And they specify the SA account. ...
    (microsoft.public.sqlserver.clients)