Re: [sqlite] Field not editable

[James K. Lowden]

On Thu, 13 Feb 2014 10:01:28 +
Simon Slavin  wrote:

> Your question is an example of a long term discussion about whether
> your entire business philosophy should be encoded within your SQL
> databases, with numerous FOREIGN KEYs, CONSTRAINTs and TRIGGERs used
> to enforce how you run the business.  The two sides of the argument
> are about evenly matched and there are no conclusions yet.

I think you will concede there is some distance between implementing
security in the DBMS and encoding one's entire busness philosophy in
it.  ;-)

The idea of a Database Manage System arose from the need to manage the
database.  One important aspect was to arbitrate changes to the data
such that different applications would not update it in ways that were
mutually inconsistent or logically inconsistent.  It was never a
philosophical question, but a practical matter of protecting the data.  

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field not editable

[James K. Lowden]

On Thu, 13 Feb 2014 21:40:53 +0100
Zsbán Ambrus  wrote:

> > I want the name of the country can not be changed.
> 
> You can put all the tables you want to be not changable to a separate
> database file which you attach as read-only.  Use the ATTACH statement
> with the URI filename syntax, as documented in
> "http://sqlite.org/uri.html; , to attach the database as read-only.

It occurs to me there's an even stronger (and more involved) solution.
A virtual table (http://www.sqlite.org/vtab.html) could return values
from any source, and prevent updates by setting xUpdate to NULL.  

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field not editable

[Zsbán Ambrus]

On 2/13/14, O.D. vidal  wrote:
> I would like a field is not editable. What is the best solution?
>
> I want the name of the country can not be changed.

You can put all the tables you want to be not changable to a separate
database file which you attach as read-only.  Use the ATTACH statement
with the URI filename syntax, as documented in
"http://sqlite.org/uri.html; , to attach the database as read-only.
Of course, when you initially create and fill the table, you have to
attach the same database file in read-write mode, but later your
application can open it read-only.

Ambrus
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field not editable

[O.D. vidal]

sorry, it works well with a trigger and raise(abort, ‘cause').

Le 13 févr. 2014 à 09:50, O.D. vidal  a écrit :

> 
> Hello all,
> 
> I would like a field is not editable. What is the best solution? 
> 
> For example, I have a table Country: 
> 
> id - Name 
> 
> 1 - Brazil 
> 2 - Canada 
> ... 
> 
> I want the name of the country can not be changed.
> 
> Thank you!
> Greetings
> 
> olivier
> ___
> 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] Field not editable

[O.D. vidal]

ok, thank you very much Simon!

olivier

Le 13 févr. 2014 à 11:01, Simon Slavin  a écrit :

> 
> On 13 Feb 2014, at 9:55am, O.D. vidal  wrote:
> 
>> Why do you say:
>> 
>>> This would normally be done inside your programming language.
>> 
>> strengthen the rule directly in the database seems common sense.
> 
> I can see that argument, which is why I bothered figuring out the TRIGGER.
> 
> However, anyone who is able to make changes to the data in that database can 
> also make changes to the TRIGGERs in the database.  So they could just DROP 
> that TRIGGER.  They would be less likely to be able to change the software 
> that is being used to edit the data.
> 
> Your question is an example of a long term discussion about whether your 
> entire business philosophy should be encoded within your SQL databases, with 
> numerous FOREIGN KEYs, CONSTRAINTs and TRIGGERs used to enforce how you run 
> the business.  The two sides of the argument are about evenly matched and 
> there are no conclusions yet.
> 
> I hope you manage to implement a solution which suits you.
> 
> 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

Re: [sqlite] Field not editable

[Simon Slavin]

On 13 Feb 2014, at 9:55am, O.D. vidal  wrote:

> Why do you say:
> 
>> This would normally be done inside your programming language.
> 
> strengthen the rule directly in the database seems common sense.

I can see that argument, which is why I bothered figuring out the TRIGGER.

However, anyone who is able to make changes to the data in that database can 
also make changes to the TRIGGERs in the database.  So they could just DROP 
that TRIGGER.  They would be less likely to be able to change the software that 
is being used to edit the data.

Your question is an example of a long term discussion about whether your entire 
business philosophy should be encoded within your SQL databases, with numerous 
FOREIGN KEYs, CONSTRAINTs and TRIGGERs used to enforce how you run the 
business.  The two sides of the argument are about evenly matched and there are 
no conclusions yet.

I hope you manage to implement a solution which suits you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field not editable

[O.D. vidal]

Thank you Simon!

Why do you say:

> This would normally be done inside your programming language.

strengthen the rule directly in the database seems common sense.

olivier


Le 13 févr. 2014 à 10:37, Simon Slavin  a écrit :

> 
> On 13 Feb 2014, at 8:50am, O.D. vidal  wrote:
> 
>> I would like a field is not editable. What is the best sol


>> ution? 
>> 
>> For example, I have a table Country: 
>> 
>> id - Name 
>> 
>> 1 - Brazil 
>> 2 - Canada 
>> ... 
>> 
>> I want the name of the country can not be changed.
> 
> This would normally be done inside your programming language.  However it 
> might be possible to enforce the rule inside SQLite using a TRIGGER:
> 
> 
> 
> It might looks something like
> 
> CREATE a TRIGGER
>   UPDATE ON Country
>   WHEN new.name != old.name
>   BEGIN
>   RAISE (FAIL, 'Not allowed to change the name of a country.')
>   END
> 
> Note: I just made that up off the top of my head and have no idea whether it 
> will work.
> 
> You will have to guard against
> 
> (A) people DROPping the TRIGGER, and
> (B) people deleting the row from the Country table and making a new one with 
> a different name.
> 
> 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

Re: [sqlite] Field not editable

[Simon Slavin]

On 13 Feb 2014, at 8:50am, O.D. vidal  wrote:

> I would like a field is not editable. What is the best solution? 
> 
> For example, I have a table Country: 
> 
> id - Name 
> 
> 1 - Brazil 
> 2 - Canada 
> ... 
> 
> I want the name of the country can not be changed.

This would normally be done inside your programming language.  However it might 
be possible to enforce the rule inside SQLite using a TRIGGER:



It might looks something like

CREATE a TRIGGER
UPDATE ON Country
WHEN new.name != old.name
BEGIN
RAISE (FAIL, 'Not allowed to change the name of a country.')
END

Note: I just made that up off the top of my head and have no idea whether it 
will work.

You will have to guard against

(A) people DROPping the TRIGGER, and
(B) people deleting the row from the Country table and making a new one with a 
different name.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Field not editable

[O.D. vidal]

Hello all,

I would like a field is not editable. What is the best solution? 

For example, I have a table Country: 

id - Name 

1 - Brazil 
2 - Canada 
... 

I want the name of the country can not be changed.

Thank you!
Greetings

olivier
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users