Re: [sqlite] prepackaged sql statement
Henrik Bechmann <[EMAIL PROTECTED]> wrote: > My purpose was to find the least intrusive way of implementing > vertical partitioning as a security measure, for example allowing a > vendor to only see his/her records in a billing table. My idea was > that a view constrained by a condition on a foreign key would work. > In the absence of passing in a parameter, can I join the invoice > table to a "ghost" condition table or view that is created at > runtime, whereby the condition table contains only the runtime client > vendor id? Or do I have to implement a full select or view on the > invoice table at runtime? Suggestions? You can do CREATE TEMP VIEW to create a filtering view on the fly once vendor id is known. Or, as you suggest, you can create a one-row-one-colum table that would contain the vendor id, and have the view join it in. Update this table with the vendor Id as soon as it's known. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
My purpose was to find the least intrusive way of implementing vertical partitioning as a security measure, for example allowing a vendor to only see his/her records in a billing table. My idea was that a view constrained by a condition on a foreign key would work. In the absence of passing in a parameter, can I join the invoice table to a "ghost" condition table or view that is created at runtime, whereby the condition table contains only the runtime client vendor id? Or do I have to implement a full select or view on the invoice table at runtime? Suggestions? - Henrik Stephen Woodbridge wrote: > Alex Katebi wrote: > >> The problem with the view is that you can not pass a parameter or variable >> from the outer select to the views select. >> > > Right that is not the purpose of a view. Think of a view as a virtual > TABLE. You can not pass a parameter to a table either. You can just > query the table or view and you CAN query with a parameter in the select > statement. > > select * from where > > Maybe it would be help to restate what you are trying to do. > > -Steve > > >> On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> >> wrote: >> >> >>> "Henrik Bechmann" <[EMAIL PROTECTED]> >>> wrote in message news:[EMAIL PROTECTED] >>> Thanks Igor! And the SQL statement can be bound to parameters in the usual ways? >>> No, not in the view. You can, of course, select from the view (as if it >>> were a table), and _that_ query can be parameterized. >>> >>> 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 > > -- Henrik Bechmann bechmann.ca Webmaster, celos.ca webhosting services ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
Hi Steve, This is my problem: create table inet0 (dest, mask, nexthop); create index inet0_idx on inet0(mask, destination); insert into inet0 values(x'0100', x'FF00', x'08080808'); insert into inet0 values(x'0101', x'', x'16161616'); insert into inet0 values(x'01010100', x'FF00', x'24242424'); insert into inet0 values(x'01010100', x'FF00', x''); select * from inet0 where destination=bitand(x'01010101', (select mask from inet0 where destination=bitand(x'01010101, mask) order by mask desc)) order by mask desc; The objective of the above select is to find one or more longest mask nexthop values. I want to make a view for the above select and pass in a destination variable as parameter. Thanks, -Alex On Tue, Jul 1, 2008 at 2:41 PM, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > The problem with the view is that you can not pass a parameter or > variable > > from the outer select to the views select. > > Right that is not the purpose of a view. Think of a view as a virtual > TABLE. You can not pass a parameter to a table either. You can just > query the table or view and you CAN query with a parameter in the select > statement. > > select * from where > > Maybe it would be help to restate what you are trying to do. > > -Steve > > > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> > > wrote: > > > >> "Henrik Bechmann" <[EMAIL PROTECTED]> > >> wrote in message news:[EMAIL PROTECTED] > >>> Thanks Igor! And the SQL statement can be bound to parameters in the > >>> usual ways? > >> No, not in the view. You can, of course, select from the view (as if it > >> were a table), and _that_ query can be parameterized. > >> > >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
Alex Katebi wrote: > The problem with the view is that you can not pass a parameter or variable > from the outer select to the views select. Right that is not the purpose of a view. Think of a view as a virtual TABLE. You can not pass a parameter to a table either. You can just query the table or view and you CAN query with a parameter in the select statement. select * from where Maybe it would be help to restate what you are trying to do. -Steve > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> > wrote: > >> "Henrik Bechmann" <[EMAIL PROTECTED]> >> wrote in message news:[EMAIL PROTECTED] >>> Thanks Igor! And the SQL statement can be bound to parameters in the >>> usual ways? >> No, not in the view. You can, of course, select from the view (as if it >> were a table), and _that_ query can be parameterized. >> >> 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] prepackaged sql statement
The problem with the view is that you can not pass a parameter or variable from the outer select to the views select. On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Henrik Bechmann" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] > > Thanks Igor! And the SQL statement can be bound to parameters in the > > usual ways? > > No, not in the view. You can, of course, select from the view (as if it > were a table), and _that_ query can be parameterized. > > 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] prepackaged sql statement
"Henrik Bechmann" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Thanks Igor! And the SQL statement can be bound to parameters in the > usual ways? No, not in the view. You can, of course, select from the view (as if it were a table), and _that_ query can be parameterized. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
Thanks Igor! And the SQL statement can be bound to parameters in the usual ways? - Henrik Igor Tandetnik wrote: > "Henrik Bechmann" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] > >> The definition for SQLite CREATE VIEW refers to pre-packaged select >> statements. >> >> What's the "pre-packaged" refer to? >> > > The view itself. A view is simply a way to name a SQL statement and save > it in the database - "package" it. > > >> Are the select statements named >> and stored, so that I can create a temporary named view from a >> prepared selection of named select statements >> > > The view _is_ a named select statement. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Henrik Bechmann bechmann.ca Webmaster, celos.ca webhosting services ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepackaged sql statement
"Henrik Bechmann" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The definition for SQLite CREATE VIEW refers to pre-packaged select > statements. > > What's the "pre-packaged" refer to? The view itself. A view is simply a way to name a SQL statement and save it in the database - "package" it. > Are the select statements named > and stored, so that I can create a temporary named view from a > prepared selection of named select statements The view _is_ a named select statement. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
