Hi Tom,
1)
Thanks for all the great work you do. By the way, when I interview people (I'm a software developer and interview people for such positions) I have recently added a standard interview question that I always include:
"Where do you put business logic/rules, in your application or in the database?"
I have yet to interview anyone that says "database". Then I ask, "What happens if I want to use a different application (other than yours) to access the data?" They look at me totally puzzled.
I subscribe to your approach of putting all the business logic you can in the DB, it makes life so much easier down the road and works so well. Sadly, this idea seems to be the minority view, as I constantly see answers like this (note the accepted answer and how many upvotes it got):
http://stackoverflow.com/questions/1473624/business-logic-in-database-versus-code Why do you think so many development people hate putting the logic in the DB?
2)
My main question though concerns your Expert Oracle book regarding the reservation system example from chapter one and how to use select for update to ensure proper concurrency.
You use the approach of SELECT FOR UPDATE to lock the resources record to ensure at most one check can be done against the schedules table for a given resource at a given point in time. Then you did the overlap check after that select. I just wondered if there was anything wrong with the approach of INSERT INTO...WHERE NOT EXISTS, and checking SQL%ROWCOUNT to make sure 1 record was inserted. In other words, we try to insert a record into schedules, but make sure there aren't any overlaps, and we do it using an insert statement. If SQL%ROWCOUNT is 1, we know the record was inserted successfully, otherwise, we know there was an overlap and can raise the error accordingly.
In the book, you say use a single SQL statement if at all possible, so this approach would fall into that category.
Here's a complete example (note that I used a little bit different date logic from what you used in the book, since the book approach would consider 8am-9am and 9am-10am overlaps, because you use a between operator which is inclusive; my approach would not consider those overlaps, but also note it requires a 3rd check so if somebody books the exact same time range we can catch it):
Is there anything I missed by doing it this way?
create table resources ( resource_name varchar2(25) primary key);
create table schedules( resource_name varchar2(25) references resources,
start_time date,
end_time date );
insert into resources values ('r1');
insert into schedules values ('r1',to_date('2014-01-01 12:00','YYYY-MM-DD HH24:MI'),
to_date('2014-01-01 13:00','YYYY-MM-DD HH24:MI')
);
create or replace procedure book_resource
(
p_resource_name resources.resource_name%type
, p_start_time date
, p_end_time date
)
AS
BEGIN
IF p_start_time >= p_end_time
THEN
raise_application_error(-20001,'p_end_time must be > p_start_time');
END IF;
INSERT INTO schedules
( resource_name
, start_time
, end_time )
SELECT p_resource_name
, p_start_time
, p_end_time
FROM dual
WHERE NOT EXISTS
(SELECT *
FROM schedules s
WHERE s.resource_name = p_resource_name
AND ( (p_start_time > s.start_time AND p_start_time < s.end_time)
OR (p_end_time > s.start_time AND p_end_time < s.end_time)
OR (p_start_time = s.start_time AND p_end_time = s.end_time) )
);
-- if a row wasn't inserted, we know the resource was already booked for this timeframe
IF SQL%ROWCOUNT != 1
THEN
raise_application_error(-20001,'Sorry, this resource is already booked for the selected time range. Please try a different time range.');
END IF;
END;
/
declare
begin
/* this will fail because 12:00-12:30 overlaps with 12:00-13:00 */
book_resource('r1',to_date('2014-01-01 12:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 12:30','YYYY-MM-DD HH24:MI'));
end;
/
declare
begin
/* this will succeed because 15:00-16:00 doesn't overlap */
book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
end;
/
declare
begin
/* this will fail because 15:00-16:00 overlaps */
book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
end;
/
commit;
Here's the output:
Table created.
Table created.
1 row created.
1 row created.
Procedure created.
declare
*
ERROR at line 1:
ORA-20001: Sorry, this resource is already booked for the selected time range.
Please try a different time range.
ORA-06512: at "SYSDC.BOOK_RESOURCE", line 33
ORA-06512: at line 4
PL/SQL procedure successfully completed.
declare
*
ERROR at line 1:
ORA-20001: Sorry, this resource is already booked for the selected time range.
Please try a different time range.
ORA-06512: at "SYSDC.BOOK_RESOURCE", line 33
ORA-06512: at line 4
1) because they've been "taught" that "databases must be interchangeable", the programming language we use doesn't, the OS we use doesn't, the technology like SOAP or whatever doesn't - but that database - you had better be able to replace it at the drop of a hat.
One piece of the entire situation has to be disposable - every other bit and byte - not so.
Never mind the fact that an application dependent on the database lives and dies by the database. Never mind the fact that the database's are different (transactions run in different ways on different concurrency models). Never mind the fact that things are possible in one database that are not in another. Never mind the fact that many times we can do in a single command in the database what a developer might write 1,000's of lines of code for (and in many cases, the code is "wrong", doesn't work in a multi-user environment).
Because it is "common knowledge" among developers that databases are black boxes, they are hard, they are impediments.
Because they haven't looked at things really.
2) think about it.....
reads don't block writes
writes don't block reads
you cannot see my data (your where exists cannot see my data) until I commit.
I cannot see your data until you commit.
if we do something at the same time - we won't see each others work.
you used a single transaction, you cannot test multi-user issues with a single transaction.
here is my output using two different transactions (you can do this in two sqlplus sessions for example...)
ops$tkyte%ORA11GR2> declare
2 begin
3 /* this will succeed because 15:00-16:00 doesn't overlap */
4 book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction; /* <================== a new transaction */
3 begin
4 /* this will fail because 15:00-16:00 overlaps */
5 book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA11GR2> select * from schedules;
RESOURCE_NAME START_TIME END_TIME
------------------------- -------------------- --------------------
r1 01-jan-2014 12:00:00 01-jan-2014 13:00:00
r1 01-jan-2014 15:00:00 01-jan-2014 16:00:00
r1 01-jan-2014 15:00:00 01-jan-2014 16:00:00
bummer....
this is why developers might actually have to understand how the database they are using works.... if they do not, they will create bogus data....
how many databases have you seen where the "application manages all of the rules" that have data integrity issues? For me, the hit ratio is about 100% - in 100% of the cases, there are integrity issues in the data. In many cases, the developers KNOW this and have actually written "data integrity checkers" that they run to find and attempt to fix the issue after the fact.