Hi Jason,
> I'm used to doing stored procedures for web apps, which
> conditionally execute statements based on state and/or the
> presence of variables.
As others have pointed out, SQLite doesn't (currently at least) offer
stored procedures, so no branching in SQL such as if/then etc. But SQL
(in SQLite or SQL Server or whatever) is a language for describing
sets, rather than procedures anyway, so I think it's usually a case of
changing your thinking to deal with sets and subsets, rather than
thinking of procedures.
The most basic example of this is rather than thinking of "step
through each row, if condition then insert", you need to instead think
"insert all the new rows where condition".
> Consider this construct, which I built recently to populate a table
> with URL for a web spider bot I built:
I considered it. It seems to use a procedure to do in a convoluted way
a task that is better described in terms of sets and SQL:
In pseudo code:
insert (or ignore if already existing) a new row into
SpiderBot_ContentProviders;
insert (or ignore if already existing) a new row into SpiderBot,
looking up the ContentProviderID from SpiderBot_ContentProviders;
> How would I got about re-writing something like this in SQLite?
If I'm interpreting your structure correctly, you just need to define
your tables to have the necessary unique (or primary key) columns and
use "insert or ignore" to skip the insertion of any rows that already
exist. So, assuming that your schema is something like:
create table SpiderBot
(
ContentProviderID integer primary key
, LinkPath text collate nocase unique
, LinkText text
);
create table SpiderBot_ContentProviders
(
ContentProviderID integer primary key
, ProviderName text collate nocase unique
);
create table AddLinkInfo -- containing all the data that you want to
import into your other tables
(
ProviderName text
, LinkPath text
, LinkText text
);
Then I think your SQL set based solution is simply something like:
-- is this a known provider? if not, add it into the DB:
insert or ignore into SpiderBot_ContentProviders( ProviderName )
select ProviderName from AddLinkInfo
;
-- do the main content insertion and assign its new ID:
insert or ignore into SpiderBot (ContentProviderID, LinkPath, LinkText)
select
(select ContentProviderID from SpiderBot_ContentProviders where
ProviderName = AddLinkInfo.ProviderName)
, LinkPath
, LinkText
from AddLinkInfo
;
That's just two steps, without nested if/then etc. It should process a
lot faster than the procedure you have and certainly much faster than
moving the if/then logic out of SQL into your program, which would add
a whole lot of delay accessing the database multiple times.
It's well worth moving your mindset out of the procedural approach and
into the more natural dealing with sets which achieves more natural
SQL syntax and faster results.
I've had to interpret your purpose, so forgive me if I've missed
something here.
I hope this helps,
Tom
BareFeet
--
Widest range of Macs and accessories in Australia
http://www.tandb.com.au/forsale/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
