Thanks for replies. I want to make things clear. there are some rules in my system, such as : process whose name is proc_host can see all the records, and process whose name is proc_client1 can see all the records except rowid 1. It looks like temporary view and temporary table is good solutions, which one is better and which one could be more efficient?
2011/7/16, David Bicking <dbic...@yahoo.com>: > I don't know I have much to offer here as I still really don't > understand what you are trying to accomplish. > > But I looked and it appears that sqlite supports TEMPORARY VIEW, which, > I believe, is only visible to the process that created it. And it will > automatically go away when that process ends. > > Sqlite doesn't allow you to insert, update or delete records in a view, > but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view, > which you then have update the underlying real table. If you go with > temporary views, you'll have to recreate the triggers each time you > recreate the view. > > In some of your emails you say that hidden records are to be hidden from > all processes, yet you seem to not like the idea of deleting them. I am > assuming that our statement is really, "some records are to be hidden > from all processes AT THIS TIME." In the future, a process may be > allowed to see the visible record, and thus you can't just delete it. Am > I right in this? > > Who is creating the queries that you want to hide records from? If you, > then just don't include those records in the WHERE clause. If the end > user is typing in adhoc queries, than even if you go with using views, > you are going to having to make sure they don't figure out the > underlying real table name. I've never used it, but I gather that is > what the authorizer callback is for. > > How do you determine which records should be visible to which process? > Are you looking at the user information captured in the process? The > actual pid, as I understand it, changes each time a program is run, so I > don't see you doing a lookup on that number. > > At any rate, I suspect you will want to create a temporary table in > which you will either populate the real table rows that you want to > allow or disallow for the given process. You could then create the > temporary view as a join between the real table and the temp table. You > also need to create the associated triggers for the user in that process > to use. > > But in the end, I am just guessing what you are really trying to do, so > I may be off by a mile. > > David > > > On 07/16/2011 12:01 AM, san long wrote: >> sqlite3 support a trigger on SELECT ? View is a good solution, but I >> want to let different process see different records, like: >> pid A sees rowid 1,2 >> pid B sees rowid 1,3 >> >> >> 2011/7/16, san long<kerneltrap...@gmail.com>: >>> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process >>> B could see this VIEW or not? >>> A and B run at the same thime. >>> >>> 2011/7/16, Simon Slavin<slav...@bigfraud.org>: >>>> >>>> On 16 Jul 2011, at 4:23am, san long wrote: >>>> >>>>> Thanks for advice, delete or update the record in a view could affect >>>>> the >>>>> true table in sqlite now? >>>> >>>> No. Either use the VIEW for SELECT and the TABLE for changes, or >>>> implement >>>> triggers so attempts to change your VIEW actually change the underlying >>>> TABLE. >>>> >>>>> And, if I create a view dynamically using sqlite3_exec, is it visible >>>>> to >>>>> other process who opens the same database? >>>> >>>> Yes. A VIEW is a stored SELECT statement (or it has a similar effect). >>>> >>>> >>>> On 16 Jul 2011, at 4:25am, san long wrote: >>>> >>>>> right, but now I just want to hide these records to all processes. >>>> >>>> Then DELETE them ! >>>> >>>> Simon. > _______________________________________________ > 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
