Re: [sqlite] block alter table command / table Constraints
On 1 Apr 2011, at 3:25am, Simon Slavin wrote: > > On 1 Apr 2011, at 3:12am, RAKESH HEMRAJANI wrote: > >> 1) how to make a table read only, such that any user can only insert but >> can't update (table schema) or drop the table >> >>alter table / drop table arent allowed on sqlite_master, need similar >> restrictions on my custom table. >> >>by setting the tabFlags to TF_Readonly, i m able to prevent the user from >> dropping the table but >>alter table is still allowed (can add or drop columns) > > SQLite does not really have a user/privilege/protection model. However, you > can put the table in its own database file, and open the database file > read-only, using a flag value of 1: > > http://www.sqlite.org/c3ref/open.html > http://www.sqlite.org/c3ref/c_open_autoproxy.html I'm sorry, I did not read that correctly. Please ignore what I wrote and pay attention to Igor. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] block alter table command / table Constraints
On 1 Apr 2011, at 3:12am, RAKESH HEMRAJANI wrote: > 1) how to make a table read only, such that any user can only insert but > can't update (table schema) or drop the table > > alter table / drop table arent allowed on sqlite_master, need similar > restrictions on my custom table. > > by setting the tabFlags to TF_Readonly, i m able to prevent the user from > dropping the table but > alter table is still allowed (can add or drop columns) SQLite does not really have a user/privilege/protection model. However, you can put the table in its own database file, and open the database file read-only, using a flag value of 1: http://www.sqlite.org/c3ref/open.html http://www.sqlite.org/c3ref/c_open_autoproxy.html > 2) How to apply constraints similar to given below as part of create table > statement, if not possible can it be done after create statement? > >table sales(cost price, sale price) > i need a constraint cost price < sale price so that i dont have to do > code level checks during insert statements Use a CONSTRAINT. See this page http://www.sqlite.org/lang_createtable.html the section 'SQL Data Constraints', under that the 'CHECK' paragraph. The result would look something like this: CREATE TABLE sales ( costPrice REAL, salePrice REAL, CHECK (costPrice < salePrice) ) You cannot add new constraints after creating the TABLE. You can work around this by creating another TABLE with your new constraint, and copying the data from the old to the new TABLE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] block alter table command / table Constraints
On 3/31/2011 10:12 PM, RAKESH HEMRAJANI wrote:
> 1) how to make a table read only, such that any user can only insert but
> can't update (table schema) or drop the table
>
> alter table / drop table arent allowed on sqlite_master, need similar
> restrictions on my custom table.
>
> by setting the tabFlags to TF_Readonly, i m able to prevent the user
> from dropping the table but
> alter table is still allowed (can add or drop columns)
SQLite doesn't support dropping columns, on read-only tables or otherwise.
> 2) How to apply constraints similar to given below as part of create table
> statement, if not possible can it be done after create statement?
>
> table sales(cost price, sale price)
> i need a constraint cost price< sale price so that i dont have to do
> code level checks during insert statements
create table sales("cost price", "sale price", check("cost price" <
"sale price"));
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] block alter table command / table Constraints
Hi Team, Need ur expertise with below 2 problems 1) how to make a table read only, such that any user can only insert but can't update (table schema) or drop the table alter table / drop table arent allowed on sqlite_master, need similar restrictions on my custom table. by setting the tabFlags to TF_Readonly, i m able to prevent the user from dropping the table but alter table is still allowed (can add or drop columns) 2) How to apply constraints similar to given below as part of create table statement, if not possible can it be done after create statement? table sales(cost price, sale price) i need a constraint cost price < sale price so that i dont have to do code level checks during insert statements thanks and regards Rakesh Hemrajani ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
