[sqlite] Need help
Hello, I am very new database development and sqlite. I have a requirement where I want to restrict the size of my integer primary key to 3 bytes. In my database, entries will be added and removed very frequently. So If the primary key limit of 3 bytes is reached, it should try to reuse the deleted entries. Is this possible with sqlite? Please help me. Thanks in Advance, Regards, Bala ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mutex assert_fail in btreeInvokeBusyHandler occasionally in a periodic DB update in 3.5.7, It's ok in 3.6.22(-DSQLITE_THREADSAFE=1)
Since it apperas you're running your commit in a separate thread and are
therefore muilti-threaded I do belive you need:
SQLITE3_THREADSAFE=2
From http://www.sqlite.org/compile.html#threadsafe
To put it another way, SQLITE_THREADSAFE=1 sets the default threading mode to
Serialized. SQLITE_THREADSAFE=2 sets the default threading mode to
Multi-threaded. And SQLITE_THREADSAFE=0 sets the threading mode to
Single-threaded.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of ChingChang Hsiao [chingchang.hs...@overturenetworks.com]
Sent: Wednesday, March 30, 2011 8:29 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] mutex assert_fail in btreeInvokeBusyHandler occasionally
in a periodic DB update in 3.5.7, It's ok in 3.6.22(-DSQLITE_THREADSAFE=1)
Please neglect the previous 2 emails. Sorry for the inconvenience.
Version is 3.5.7
journal mode = DELETE
SELECT sqlite_source_id(); 2011-01-28 17:03:50
ed759d5a9edb3bba5f48f243df47be29e3fe8cd7
-DSQLITE_THREADSAFE=1 in Makefile
static int btreeInvokeBusyHandler(void *pArg){
BtShared *pBt = (BtShared*)pArg;
assert( pBt-db );
assert( sqlite3_mutex_held(pBt-db-mutex) );
return sqlite3InvokeBusyHandler(pBt-db-busyHandler);
}
There is a periodic(1 second) DB commit update. It will receive signal 6
assertion fail occasionally. Assert_fail is for sqlite3_mutex_held. Is it
something to do with the flag SQLITE_THREADSAFE. Is there anyone could have
some ideas for the possible assert_fail reason?
It never happened in version 3.6.22. The journal mode is DELETE too. What could
be the changes in 3.7.5 cause this problem?
(gdb) bt
#0 0x3370bb04 in raise () from /lib/libc.so.6
#1 0x3370d2f4 in abort () from /lib/libc.so.6
#2 0x337032a4 in __assert_fail () from /lib/libc.so.6
#3 0x100dc940 in btreeInvokeBusyHandler (pArg=0x102b3b50) at sqlite3.c:47153
#4 0x1013f1dc in sqlite3VdbeHalt (p=0x103ae298) at sqlite3.c:38543
#5 0x1018fda8 in sqlite3VdbeExec (p=value optimized out) at sqlite3.c:63340
#6 sqlite3Step (p=0x103ae298) at sqlite3.c:59036
#7 0x101987e8 in sqlite3_step (pStmt=0x103ae298) at sqlite3.c:59101
#8 0x1016cb7c in sqlite3_exec (db=0x10856e18, zSql=0x106b3aa4 COMMIT;,
xCallback=0, pArg=0x0, pzErrMsg=0x388a87c0) at sqlite3.c:84523
#9 0x1003f744 in SqlQuery::execw (this=0x388a8844,
sql_stmt=0x106b3aa4 COMMIT;, context=0x101b91b8 SlotUtilEvent.cpp,
linenum=69, warnings=value optimized out) at SqlQuery.cpp:281
#10 0x10089db8 in SlotUtilEvent::run (this=0x10a81e94) at SlotUtilEvent.cpp:94
#11 0x10003f40 in HwMonListener::run (this=0x106b28a8)
at
/mnt/local/cch/bugfix_test_11_01_02232011/isg6000/isg6k/mgmt-crd/linuxapps/hwmon/hwmon.cpp:1993
#12 0x10025c8c in Thread::start_thread (arg=0x106b28a8) at thread.cpp:199
#13 0x334265cc in ?? () from /lib/libpthread.so.0
#14 0x337b0b88 in clone () from /lib/libc.so.6
Backtrace stopped: previous frame inner to this frame (corrupt stack?)
___
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] Need help
Balasubramani Vivekkanandan bvivekkanan...@mvista.com wrote: I am very new database development and sqlite. I have a requirement where I want to restrict the size of my integer primary key to 3 bytes. In my database, entries will be added and removed very frequently. So If the primary key limit of 3 bytes is reached, it should try to reuse the deleted entries. What purpose exactly is this, rather bizarre, requirement supposed to serve, if you don't mind me asking? Is this possible with sqlite? Not automatically. You can implement it in your application code. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help
Igor Tandetnik itandetnik@... writes: Balasubramani Vivekkanandan bvivekkanandan@... wrote: I am very new database development and sqlite. I have a requirement where I want to restrict the size of my integer primary key to 3 bytes. In my database, entries will be added and removed very frequently. So If the primary key limit of 3 bytes is reached, it should try to reuse the deleted entries. What purpose exactly is this, rather bizarre, requirement supposed to serve, if you don't mind me asking? Is this possible with sqlite? Not automatically. You can implement it in your application code. sorry that I've to contradict but I believe(!) that it should be possible this way: 1. create table using INTEGER PRIMARY KEY (without autoincrement) 2. create an extra table with an 1 column 3. INSERT a 0 (zero) in that extra table 4. create a TRIGGER that fires if the id reaches the max(id) + 1, checks the Integer in the extra table and INSERTS integer + 1 in main table (REPLACE), INSERTS the new id in extra table by REPLACING the old one (DELETE and INSERT) just a few thopughts without having tested greetings oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help
Igor Tandetnik itandetnik@... writes: Balasubramani Vivekkanandan bvivekkanandan@... wrote: I am very new database development and sqlite. I have a requirement where I want to restrict the size of my integer primary key to 3 bytes. In my database, entries will be added and removed very frequently. So If the primary key limit of 3 bytes is reached, it should try to reuse the deleted entries. What purpose exactly is this, rather bizarre, requirement supposed to serve, if you don't mind me asking? Is this possible with sqlite? Not automatically. You can implement it in your application code. sorry that I cant agree but with an extra table to store the latest id, a little TRIGGER gaming and DELETE and REPLACE the job could be done (I believe) but it isn't worth to waste time with this because using the application is much more comfortable. greetings oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] using sqlite3_get_table
All I need to do is see how many rows a table has. I stumbled across this
function and used it thusly in my code (I removed the error checking for the
sake of brevity):
Result = sqlite3_initialize();
sqlite3 *db_ptr;
Result = 0;
Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READWRITE, NULL);
char ***CArray = NULL;
int iRow, iCol;
char **err = NULL;
sqlite3_get_table(db_ptr, Select * from Admin, CArray, iRow, iCol, err);
Unfortunately, when I execute the sqlite_get_table call, I get an error message
telling me that I have an unhandled error. It says that the error is
occurring here:
struct unixShm {
unixShmNode *pShmNode; /* The underlying unixShmNode object */
unixShm *pNext;/* Next unixShm with the same unixShmNode */
u8 hasMutex; /* True if holding the unixShmNode mutex */
u16 sharedMask;/* Mask of shared locks held */
u16 exclMask; /* Mask of exclusive locks held */
#ifdef SQLITE_DEBUG
u8 id; /* Id of this connection within its unixShmNode */
#endif
I am QUITE CERTAIN that my problem lies with the way I am declaring the arrays,
but not having an example to teach me, I have no idea what the correct method
is. If anyone has an example of how he or she is using sqlite3_get_table that
he or she wouldn't mind sharing, I would be appreciative.
TIA!
R,
John A.M. Darnell
Senior Programmer
Walsworth Publishing Company
Brookfield, MO
John may also be reached at
johnamdarn...@gmail.commailto:johnamdarn...@gmail.com
Trivia question: Who saved Gandalf from his imprisonment at the Tower of
Isengard in book 1 of The Lord of the Rings (i.e. The Fellowship of the Ring)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] using sqlite3_get_table --additional info...
BTW, if there is a better way to get a row count without using
sqlite3_get_table() that would also work.
_
From: john darnell
Sent: Thursday, March 31, 2011 9:27 AM
To: 'General Discussion of SQLite Database'
Subject: using sqlite3_get_table
All I need to do is see how many rows a table has. I stumbled across this
function and used it thusly in my code (I removed the error checking for the
sake of brevity):
Result = sqlite3_initialize();
sqlite3 *db_ptr;
Result = 0;
Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READWRITE, NULL);
char ***CArray = NULL;
int iRow, iCol;
char **err = NULL;
sqlite3_get_table(db_ptr, Select * from Admin, CArray, iRow, iCol, err);
Unfortunately, when I execute the sqlite_get_table call, I get an error message
telling me that I have an unhandled error. It says that the error is
occurring here:
struct unixShm {
unixShmNode *pShmNode; /* The underlying unixShmNode object */
unixShm *pNext;/* Next unixShm with the same unixShmNode */
u8 hasMutex; /* True if holding the unixShmNode mutex */
u16 sharedMask;/* Mask of shared locks held */
u16 exclMask; /* Mask of exclusive locks held */
#ifdef SQLITE_DEBUG
u8 id; /* Id of this connection within its unixShmNode */
#endif
I am QUITE CERTAIN that my problem lies with the way I am declaring the arrays,
but not having an example to teach me, I have no idea what the correct method
is. If anyone has an example of how he or she is using sqlite3_get_table that
he or she wouldn't mind sharing, I would be appreciative.
TIA!
R,
John A.M. Darnell
Senior Programmer
Walsworth Publishing Company
Brookfield, MO
John may also be reached at
johnamdarn...@gmail.commailto:johnamdarn...@gmail.com
Trivia question: Who saved Gandalf from his imprisonment at the Tower of
Isengard in book 1 of The Lord of the Rings (i.e. The Fellowship of the Ring)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using sqlite3_get_table --additional info...
On Mar 31, 2011, at 9:30 AM, john darnell wrote:
BTW, if there is a better way to get a row count without using
sqlite3_get_table() that would also work.
maybe I am missing something, but what is wrong with SELECT Count(*) FROM
table?
_
From: john darnell
Sent: Thursday, March 31, 2011 9:27 AM
To: 'General Discussion of SQLite Database'
Subject: using sqlite3_get_table
All I need to do is see how many rows a table has. I stumbled across this
function and used it thusly in my code (I removed the error checking for the
sake of brevity):
Result = sqlite3_initialize();
sqlite3 *db_ptr;
Result = 0;
Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READWRITE,
NULL);
char ***CArray = NULL;
int iRow, iCol;
char **err = NULL;
sqlite3_get_table(db_ptr, Select * from Admin, CArray, iRow, iCol, err);
Unfortunately, when I execute the sqlite_get_table call, I get an error
message telling me that I have an unhandled error. It says that the error
is occurring here:
struct unixShm {
unixShmNode *pShmNode; /* The underlying unixShmNode object */
unixShm *pNext;/* Next unixShm with the same unixShmNode */
u8 hasMutex; /* True if holding the unixShmNode mutex */
u16 sharedMask;/* Mask of shared locks held */
u16 exclMask; /* Mask of exclusive locks held */
#ifdef SQLITE_DEBUG
u8 id; /* Id of this connection within its unixShmNode */
#endif
I am QUITE CERTAIN that my problem lies with the way I am declaring the
arrays, but not having an example to teach me, I have no idea what the
correct method is. If anyone has an example of how he or she is using
sqlite3_get_table that he or she wouldn't mind sharing, I would be
appreciative.
TIA!
R,
John A.M. Darnell
Senior Programmer
Walsworth Publishing Company
Brookfield, MO
John may also be reached at
johnamdarn...@gmail.commailto:johnamdarn...@gmail.com
Trivia question: Who saved Gandalf from his imprisonment at the Tower of
Isengard in book 1 of The Lord of the Rings (i.e. The Fellowship of the Ring)?
___
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] using sqlite3_get_table --additional info...
On 31.03.2011 16:30 john darnell said the following:
BTW, if there is a better way to get a row count without using
sqlite3_get_table() that would also work.
This will work withput fetching the table
dbrc = sqlite3_prepare_v2 (db, select count(*) from table, -1,
stmt, NULL);
if (dbrc != SQLITE_OK)
{
//error
}
if (sqlite3_step(stmt) == SQLITE_ROW)
{
count = sqlite3_column_int (stmt, 0);
}
else
{
count = 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using sqlite3_get_table
On 3/31/2011 10:27 AM, john darnell wrote: All I need to do is see how many rows a table has. select count(*) from TableName; char ***CArray = NULL; int iRow, iCol; char **err = NULL; sqlite3_get_table(db_ptr, Select * from Admin, CArray,iRow,iCol, err); char** CArray = NULL; int iRow, iCol; char* err = NULL; sqlite3_get_table(db_ptr, Select * from Admin, CArray, iRow, iCol, err); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_step() error and constraints
Hi, When sqlite3_step() fail because a UNIQUE constraint is not satisfied, it returns SQLITE_ERROR. I see that there is an error code named SQLITE_CONSTRAINT. Why sqlite3_step() does not return this one? Having a specific error code would be useful to warn the user that the data he submitted already exist into the database. Thank you! Regards, Julien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_step() error and constraints
On 3/31/2011 2:02 PM, Julien Laffaye wrote: When sqlite3_step() fail because a UNIQUE constraint is not satisfied, it returns SQLITE_ERROR. I see that there is an error code named SQLITE_CONSTRAINT. Why sqlite3_step() does not return this one? For reasons described in the documentation: http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_step() error and constraints
On 03/31/2011 19:08, Igor Tandetnik wrote: On 3/31/2011 2:02 PM, Julien Laffaye wrote: When sqlite3_step() fail because a UNIQUE constraint is not satisfied, it returns SQLITE_ERROR. I see that there is an error code named SQLITE_CONSTRAINT. Why sqlite3_step() does not return this one? For reasons described in the documentation: http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. Oh, I feel stupid now... Let's use the v2 function then. Anyway, thank you very much for your quick reply. Regards, Julien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_step() error and constraints
http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. I believe it was changed with recent versions of SQLite. Is call to sqlite3_extended_result_codes (http://www.sqlite.org/c3ref/extended_result_codes.html) not needed anymore? Pavel On Thu, Mar 31, 2011 at 2:08 PM, Igor Tandetnik itandet...@mvps.org wrote: On 3/31/2011 2:02 PM, Julien Laffaye wrote: When sqlite3_step() fail because a UNIQUE constraint is not satisfied, it returns SQLITE_ERROR. I see that there is an error code named SQLITE_CONSTRAINT. Why sqlite3_step() does not return this one? For reasons described in the documentation: http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. -- 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] sqlite3_step() error and constraints
On 3/31/2011 2:12 PM, Pavel Ivanov wrote: http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. I believe it was changed with recent versions of SQLite. Is call to sqlite3_extended_result_codes (http://www.sqlite.org/c3ref/extended_result_codes.html) not needed anymore? It is needed if you are interested in extended result codes. SQLITE_CONSTRAINT isn't one of them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)
I have tried the latest Explorer and it gets a syntax error on STDEV. However, that function works in Eclipse just fine, to take the standard deviation of a column (like min, max, avg). Is there a workaround or other fix available? Explorer doesn't recognize VAR or VARIANCE either. I tried some alternate spellings with no luck. -- Mike Rychener 919-929-5091 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)
On 3/31/2011 2:27 PM, Mike Rychener wrote: I have tried the latest Explorer and it gets a syntax error on STDEV. However, that function works in Eclipse just fine, to take the standard deviation of a column (like min, max, avg). Is there a workaround or other fix available? Explorer doesn't recognize VAR or VARIANCE either. I tried some alternate spellings with no luck. These functions are not built into SQLite. Eclipse must be adding them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_step() error and constraints
On 31 Mar 2011, at 7:08pm, Igor Tandetnik wrote: For reasons described in the documentation: http://sqlite.org/c3ref/step.html . See Goofy Interface Alert section at the bottom. giggle Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)
On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote: I have tried the latest Explorer and it gets a syntax error on STDEV. However, that function works in Eclipse just fine, to take the standard deviation of a column (like min, max, avg). Is there a workaround or other fix available? See http://www.sqlite.org/contrib extension-functions.c e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL checkpoints not working
I can't get WAL checkpoints to work, automatically or forced. The WAL file seems to grow forever until the database is shutdown. I have tried: sqlite3_wal_checkpoint() and PRAGMA wal_checkpoint (with all different options) My 84MB data file is now paired with a 538MB WAL file. Any ideas why this would happen? Thanks dw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL checkpoints not working
On Thu, Mar 31, 2011 at 4:44 PM, Dave White dwh...@companioncorp.comwrote: I can't get WAL checkpoints to work, automatically or forced. The WAL file seems to grow forever until the database is shutdown. I have tried: sqlite3_wal_checkpoint() and PRAGMA wal_checkpoint (with all different options) My 84MB data file is now paired with a 538MB WAL file. Any ideas why this would happen? You have a read transaction being held open. The checkpoint cannot run to completion when there is a read transaction open. (It does as much as it can and quits.) The reason is that if it were to run to completion, it would delete content out from under the read transaction. Thanks dw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL checkpoints not working
On Thu, Mar 31, 2011 at 3:48 PM, Richard Hipp d...@sqlite.org wrote:
On Thu, Mar 31, 2011 at 4:44 PM, Dave White dwh...@companioncorp.comwrote:
Any ideas why this would happen?
You have a read transaction being held open. The checkpoint cannot run to
completion when there is a read transaction open. (It does as much as it
can and quits.) The reason is that if it were to run to completion, it
would delete content out from under the read transaction.
Maybe SQLite3 could track all the open read transactions (and zero or
one write transactions) so that their {PID, thread ID, hostname} can
be accessed via a pragma for checkpoint (and other) diagnostic
purposes?
___
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
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
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 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
