Re: [sqlite] IF...THEN constructs
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
Re: [sqlite] IF...THEN constructs
Sqlite does not have a built in procedural language like PL/SQL. For certain applications we just added Javascript to cover that requirement. It was straightforward using Spidermonkey and had the advantage of being the same language used by the AJAX applications backended by Sqlite so applications programmers had one less skill to master. Jason Salas 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 @ProviderIDINT > > -- 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. > > > Igor Tandetnik wrote: >> "Jason Salas" <[EMAIL PROTECTED]> wrote in message >> news:[EMAIL PROTECTED] >> >>> I'm used to doing lengthy T-SQL programming in SQL Server, so this is >>> kinda new to me. How does one replicate doing IF...THEN conditional >>> blocks in SQLite 3? >>> >> One typically doesn't. Instead, one implements complex logic in one's >> application that hosts SQLite. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
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 @ProviderIDINT >> >> -- 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
Re: [sqlite] IF...THEN constructs
shoot. worst suspicions affirmed. :-) although this is for a C# console app, it's still largely client/server and i designed the back-end as such, to reduce roundtrips to the DB. no sweat, a little refactoring won't hurt. thanks again! Igor Tandetnik wrote: > Jason Salas <[EMAIL PROTECTED]> wrote: > >> Thanks for the insight. I'm used to doing stored procedures for web >> apps >> > > There is no such thing as a stored procedure in SQLite. > > >> How would I got about re-writing something like this in SQLite? >> > > You wouldn't. You would write the logic in whatever application you are > developing that interacts with SQLite database. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
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 @ProviderIDINT > > -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
Jason Salas <[EMAIL PROTECTED]> wrote: > Thanks for the insight. I'm used to doing stored procedures for web > apps There is no such thing as a stored procedure in SQLite. > How would I got about re-writing something like this in SQLite? You wouldn't. You would write the logic in whatever application you are developing that interacts with SQLite database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
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 @ProviderIDINT -- 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. Igor Tandetnik wrote: > "Jason Salas" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > >> I'm used to doing lengthy T-SQL programming in SQL Server, so this is >> kinda new to me. How does one replicate doing IF...THEN conditional >> blocks in SQLite 3? >> > > One typically doesn't. Instead, one implements complex logic in one's > application that hosts SQLite. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF...THEN constructs
"Jason Salas" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm used to doing lengthy T-SQL programming in SQL Server, so this is > kinda new to me. How does one replicate doing IF...THEN conditional > blocks in SQLite 3? One typically doesn't. Instead, one implements complex logic in one's application that hosts SQLite. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
