On Wed, Feb 27, 2008 at 6:00 PM, Jason Salas <[EMAIL PROTECTED]> wrote:
> Hi Igor,
>
> Thanks for the insight. I'm used to doing stored procedures for web
> apps, which conditionally execute statements based on state and/or the
> presence of variables. Consider this construct, which I built recently
> to populate a table with URL for a web spider bot I built:
>
> CREATE PROCEDURE AddLinkInfo
> (
> @ProviderName VARCHAR(200),
> @LinkPath VARCHAR(200),
> @LinkText VARCHAR(200)
> )
> AS
> DECLARE @ProviderID INT
>
> -- only store a link if it isn't already listed in the database
> IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
> BEGIN
> -- is this a known provider? if not, add it into the DB and
> then assign it's new ID
> IF EXISTS(SELECT ContentProviderID FROM
> SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> BEGIN
> SET @ProviderID = (SELECT ContentProviderID FROM
> SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> END
> ELSE
> BEGIN
> INSERT INTO SpiderBot_ContentProviders VALUES
> (@ProviderName)
> SET @ProviderID = @@IDENTITY
> END
>
> -- do the main content insertion
> INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText)
> VALUES (@ProviderID,@LinkPath,@LinkText)
> END
> GO
>
> How would I got about re-writing something like this in SQLite? Thanks
> again for your help.
>
>
I would create several functions:
function RegisteProvider(providername)
check with a SELECT statement to see if the provider exists
if it does, return the provider ID
insert a new provider
return new provider's ID
function RecordLinkInfo(providername, linkname, linkurl)
check with a SELECT statement to see if linkurl is inuse
if it is, bail
providerId = RegisterProvider(providername)
insert new row with provider ID, link name, link url
Remember to use transactions to significantly boost your insert
performance Maybe one transaction per page?
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
