nice work!
Stephen Oberholtzer wrote: > 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? > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
