On 15/08/2011, at 10:40 PM, Simon Slavin wrote:
> which is why proper code should never do it in one instruction. You do the
> SELECT first, then use your programming language to do the right thing for
> each of the three cases where 0, 1 or more records are returned.
I disagree with this more general statement (as I have before). In general, it
is desirable to do as much SQL in one transaction as possible, for speed,
consistency and so SQLite's internal optimisers can do their thing. Selecting
and then re-injecting the result of a select is unnecessary double handling.
Having said that, all of the other assertions hold true:
1. Only select a person based on a unique column. So only match against
LastName if it is defined as unique.
2. Be prepared for a returned null.
In reality, you are probably creating an invoice for a person chosen by the
operator, so instead of noting the LastName of that person, note the unique ID
instead.
So, something like this:
create table Person
( ID integer primary key not null
, LastName text collate nocase
, FirstName text collate nocase
, Email text collate nocase
)
;
create table "Order"
( ID integer primary key not null
, Person_ID integer references Person (ID) on delete restrict
, Date real
)
;
In your code, when the operator selects a Person, store the ID of that person.
Then to create an Order, do this:
insert into "Order" (Person_ID, Date) values (?, julianday('now'))
;
where the ? gets replaced by the chosen Person's ID.
The Order ID (ie Order number) will be automatically allocated.
You could also include, in the same transaction, allocation of items to the
order etc, but that depends of your user interface.
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
