Re: [sqlite] Storing monetary values and calculations
Hi John If sourcecode for that type (DECIMAL) is available for public, please let us know the link and we can explore it for SQLite. Thanks John Stanton wrote: > > That is an interesting way to store money. We developed a fixed point > arithmetic library of arbitrary precision using the algorithms described > by Knuth in his semi-numerical algorithms volume and using standard > DECIMAL(n,m) definition. Rounding is precise using an algorithm which > does not drift and intermediate. Finally the numbers are stored in > display format, right justified and with leading spaces and signs so > that they can be displayed in HTML pages or printed output without > reformating. > ... > ... > ... > -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12401463 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Looking for a cryptographic library
Please look at the following link, for few easy-to-use free simple code-packages (two or three) for Encryption and Decryption. http://www.efgh.com/software/ Rohit -- View this message in context: http://www.nabble.com/Looking-for-a-cryptographic-library-tf4298572.html#a12334506 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing monetary values and calculations
While doing currency math, a useful money class at following link, may be used as a a reference. http://www.di-mare.com/adolfo/p/money.htm Yet Another C++ Money Class (by Adolfo Di Mare), The C Users Journal, Vol.10 No.4, pp [58-64], April 1992 Rohit. -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12333186 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] RE: Question regarding INTEGER PRIMARY KEY (zero value in column) ?
Hi
I have a Question regarding INTEGER PRIMARY KEY (zero value in column) ?
Example table => create table {id INTEGER PRIMARY KEY, name TEXT};
Is it ever possible that value 0 (zero) will be inserted in a column
declared as INTEGER PRIMARY KEY (and not as AUTOINCREMENT) ? Inserts are
always with NULL value for that column. (i.e zero is never inserted
implicitly in that column)
PS. I did refer to SQLite FAQ regarding this.
Thanks for any idea/hint.
Rohit
--
View this message in context:
http://www.nabble.com/RE%3A-Question-regarding-INTEGER-PRIMARY-KEY-%28zero-value-in-column%29---tf4072581.html#a11574190
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] RE: How do I know what DBs I have attached?
Command Syntax => ATTACH [DATABASE] database-filename AS database-name If it is possible, keep database-name unique for perticular database-filename i.e. when issuing attach database command, everytime it should be same for perticular database-filename. So no need to track, just try to attach db file which is already attached with same unique database-name will give that error. Just my 2 cents. Rohit -- View this message in context: http://www.nabble.com/How-do-I-know-what-DBs-I-have-attached--tf3376318.html#a9399633 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
Friends, for value of fist column in csv, any of the following do not work
(e.g. '', null, 'null' )
,'name1'
'','name2'
null,'name3'
'null','name4'
Table is
Create table t1
{
id INTEGER PRIMARY KEY,
name TEXT
}
No workaround to import null value from csv file for INTEGER PRIMARY KEY ?
Thanks for helping.
Rohit
--
View this message in context:
http://www.nabble.com/import-operation---primary-key-need-to-be-automatically-generated-by-SQLite-%28not-from-csv-file%29-tf3360094.html#a9388070
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
csv file is generated by a program. Program generatin csv do not know the existing id values from database table. Program can keep id blank or '' or null or some suitable value in csv file, which SQLite import should understand to generate next id automatically by SQLite itself to use it as primary key. But then for first column what to write in csv file ? Any of the following do not work. /* sample records from csv file data.csv */ ,'name_text_1' '','name_text_3' null,'name_text_2' 'null','name_text_4' Thanks for helping. Rohit -- View this message in context: http://www.nabble.com/import-operation---primary-key-need-to-be-automatically-generated-by-SQLite-%28not-from-csv-file%29-tf3360094.html#a9359741 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
/* SQLite 3.3.8 (Windows) used */ /* table t1 */ /* only two columns are given because other columns are irrelevant here */ create table t1 (id INTEGER PRIMARY KEY, name TEXT); /* few sample records from csv file data.csv */ 1,'name_text_1' 2,'name_text_2' 3,'name_text_3' 4,'name_text_4' /* import statement */ .import imp3.csv t1 I must have proper primary key valures in csv file. And when table already has records, and importing more from csv file, I need to edit primary key valures again to avoid error. (It becomes cumbersome when csv file has thousands of records) How to achieve import operation - primary key values should be automatically incremented by SQLite and not to be used from csv file ? I truly appreciate any help. Thanks Rohit -- View this message in context: http://www.nabble.com/import-operation---primary-key-need-to-be-automatically-generated-by-SQLite-%28not-from-csv-file%29-tf3360094.html#a9346705 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Mike When are you planning to put code of your SQL functions for SQLite ? Waiting...eagerly... I may try to use it in my app. Thanks Rohit -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6887312 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DOUBLE / REAL column affinity, any difference ?
Please ignore Q1 and Q2. I already found answer to Q1 and Q2. Rohit. -- View this message in context: http://www.nabble.com/DOUBLE---REAL-column-affinity%2C-any-difference---tf2363636.html#a6585007 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DOUBLE / REAL column affinity, any difference ?
Hi create table t1 ( c1 REAL, c2 DOUBLE ); In above table, Q1. What will be column affinity of column c1 ? NUMERIC ? Q2. What will be column affinity of column c2 ? NUMERIC ? Q3. If I use DOUBLE in place of REAL, will there be any consequence / performance penalty Note: I am using SQLite 3.3.4 (Win32). I truly appreciate any guidance/hint. Rohit -- View this message in context: http://www.nabble.com/DOUBLE---REAL-column-affinity%2C-any-difference---tf2363636.html#a6584995 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] record size limit ???
Hi Q1. Need to know if there is maximum limit for record size ? Q2. What are the consequence of using such large record size ? I may need large record size in two-three tables. In two or three of my tables, I may need to have 64 columns or more and maximum record size may reach to approx. 2 MB. for some records if user fills data fully in all columns. I am using column types INTEGER, REAL, TEXT only (as shown in sample table below). Not using BLOB in any column of table. (SQLite 3.3.4, Win32) Thanks Rohit /* CREATE Statement for sample table */ /* Note: VARCHAR will be TEXT in SQlite. VARCHAR and size is just used so it may work for other databases. */ create table customers ( id INTEGER PRIMARY KEY, dts REAL, /* Date Time Stamp (When record updated lastly) */ useridwml INTEGER, /* id of user who modified last (this record) */ szname VARCHAR(32), /* Customer Name */ blockstatus INTEGER,/* Locked or Not */ bactivestatus INTEGER, /* Active or Not */ bvisibilitystatus INTEGER, /* Visible or Not */ obal REAL, /* Opening Balance */ obalcr INTEGER, /* Cr or Dr Balance */ dtobal REAL,/* Date (of Opening Balance) */ szdesc VARCHAR(32), /* Description / Note / Comment */ szsalu VARCHAR(10), /* Salutation -> Mr./Ms./.. */ szfnm VARCHAR(20), /* First Name */ szmnm VARCHAR(20), /* Middle Name */ lnm VARCHAR(20),/* Last Name */ /* Bill_To Address */ sza1 VARCHAR(32), /* Address Line 1 */ sza2 VARCHAR(32), /* Address Line 2 */ sza3 VARCHAR(32), /* Address Line 3 */ sza4 VARCHAR(32), /* Address Line 4 */ szcity VARCHAR(20), /* City */ szzip VARCHAR(12), /* Zip / Postal Code */ szstate VARCHAR(20),/* State / Province */ szcntry VARCHAR(20),/* Country / Region */ /* Ship_To Address */ sz2a1 VARCHAR(32), /* Address Line 1 */ sz2a2 VARCHAR(32), /* Address Line 2 */ sz2a3 VARCHAR(32), /* Address Line 3 */ sz2a4 VARCHAR(32), /* Address Line 4 */ sz2city VARCHAR(20),/* City */ sz2zip VARCHAR(12), /* Zip / Postal Code */ sz2state VARCHAR(20), /* State / Province */ sz2cntry VARCHAR(20), /* Country / Region */ szfullnmcontact VARCHAR(32),/* Name of primary contact person */ szph1 VARCHAR(20), /* Phone Number 1 */ szph2 VARCHAR(20), /* Phone Number 2 */ szfax1 VARCHAR(20), /* Fax Number 1 */ szemail1 VARCHAR(32), /* Email Address 1 */ szwww1 VARCHAR(32), /* WebSite Address 1 */ /* Additional Info */ ltypid INTEGER, ltermsid INTEGER, dcrlmt REAL, lpricelvlid INTEGER, lnum INTEGER, szresalenum VARCHAR(20), bdoc1 INTEGER, bdoc2 INTEGER, bdoc3 INTEGER, szrep VARCHAR(20), lprefcorrtypid INTEGER, lTaxCode INTEGR, szit VARCHAR(20), szvat1 VARCHAR(20), szvat2 VARCHAR(20), szst VARCHAR(20), szst VARCHAR(20), srt VARCHAR(20), szcex VARCHAR(20), szlic VARCHAR(20), szudcf1 VARCHAR(20), szudcf2 VARCHAR(20), szudcf3 VARCHAR(20) ); -- View this message in context: http://www.nabble.com/record-size-limit-tf2355094.html#a6559539 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using SQLite on networked drive
Thomas It is not safe to use SQLite on networked drive because of file locking problems and how OS caching takes place. Please refer answers by DRH above as well as in other question-answers. Different OS have different file locking and caching mechanism. Probably Windows 2000 might have suitable one to what SQLite database needs. That is why I asked about if any one has idea/experience of sharing SQLite database by multi-users while file is on Windows 2000 (in perticular). Regarding sharing MS Access database file: You should not keep database file on Windows 95 / 98 / (ME) file share but keep it on Windows NT/2000. In some MSDN articles, microsoft recommends about sharing MS Access database : - Do not keep database file on Windows 95 / 98 / (ME) file share - Store the database file and that you share the database file on a Windows NT server or on Windows 2000 server with opportunistic locking disabled. Please refer (Knowlegde Base Articles) -> How to keep a Jet 4.0 database in top working condition. -> How To Synchronize Writes and Reads with the Jet OLE DB Provider and ADO -> How To Synchronizing Reads and Writes Between Two DAO Processes Especially refere to topic, "Issues to consider when you share a Microsoft Jet database" Rohit -- View this message in context: http://www.nabble.com/Using-SQLite-on-networked-drive-tf1213767.html#a6499937 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using SQLite on networked drive
I have read articles and understood that it is not safe to access SQLite database file on network drive. (on all windows). But what about Windows 2000 (Server) ??? i.e. If SQLite (3.3.4 or 3.3.6) database file resides on disk drive of Windows 2000 Server, then is it safe to access that SQLite database via network ? Any thoughts...Any one's experience ? Thanks for any help. Rohit -- View this message in context: http://www.nabble.com/Using-SQLite-on-networked-drive-tf1213767.html#a6492384 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to maintain EXCLUSIVE access to database continuously ?
Thanks for alternative ideas. In my app, user need Exclusive mode occassionally. But when uses Exclusive mode, user may enter/import some data or may change permissions for other users, or some report generation. User may use Exclusive mode for few minutes to may be 1-2 hours. That time no other user should be able to access the database. I tried to use extra file (non-database file, same filename) to maintain exclusive access. When that extra file is present/locked, other user can not access that database (company). But I think, there must be some way/trick to maintain EXCLUSIVE access to database continuously (need to COMMIT data in-between) ? i.e. to maintain EXCLUSIVE access, and still commiting data in-between ? Rohit -- View this message in context: http://www.nabble.com/How-to-maintain-EXCLUSIVE-access-to-database-continuously---tf2326092.html#a6479634 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to maintain EXCLUSIVE access to database continuously ?
Hi SQLite experts/users In my multi-user app (Win32, SQLite3.3.4), one database file for each company (accounts). User can access company accounts in two modes, normal mode and exclusive mode. In exclusive mode, only one user will have access, no other user should be able to access that company database. I know that BEGIN EXCLUSIVE allows such access. User may enter info even in exclusive mode. But when user enters and saves some info, I need to COMMIT and again need to issue BEGIN EXCLUSIVE (because I can't wait till last for commiting). Before issuing BEGIN EXCLUSIVE again, in-between, if other user gets access to database, then first user (with exclusive mode) will not be able to maintain his/her exclusive access. How to maintain EXCLUSIVE access to database continuously (need to COMMIT data in-between) ? i.e. is there any way to maintain EXCLUSIVE access, and still commiting data in-between ? Thanks for any guidance. Rohit -- View this message in context: http://www.nabble.com/How-to-maintain-EXCLUSIVE-access-to-database-continuously---tf2326092.html#a6471314 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] concurent writes and locks
>> >> You can avoid the deadlock by having the mixed reader/writer start its >> transaction with BEGIN IMMEDIATE. >> >> Igor Tandetnik Will BEGIN IMMEDIATE surely avoid deadlock ? or BEGIN EXCLUSIVE is better ? i.e. in such cases, what advantage BEGIN IMMEDIATE gives over BEGIN EXCLUSIVE ? Thanks Rohit Igor Tandetnik wrote: > > eric-5PSWdYX/[EMAIL PROTECTED] wrote: >> I have two threads heavily writing to the db. Hence, I get some >> SQLITE_BUSY return values. >> >> If I get it from sqlite3_step(), I wait a few ms and call >> sqlite3_step() again etc. This happens in one thread, thread A. >> >> The other thread (thread B) however, is calling the registered busy >> handler while executing a commit with an sqlite3_exec() call. And >> this is not going away either. even if I let thread A wait forever >> (so don't do anything there) thread B is getting SQLITE_BUSY (in >> commit with sqlite3_exec()). Both threads are not progressing any >> more... > > It appears you are getting into a deadlock situation. A deadlock is > possible in SQLite in the presence of two writers, where at least one of > them peforms a SELECT first before issuing a modifying statement. In > this situation, the following scenario may occur: > - thread A begins reading and acquires SHARED lock > - thread B wants to write, acquires PENDING lock and waits for readers > to clear. > - thread A now also wants to write and tries to promote to RESERVED > lock. > > At this point, the two threads wait for each other and neither can > proceed. The only way out of this deadlock is for one thread to roll > back its transaction and start from the beginning. No amount of waiting > will help. > > You can avoid the deadlock by having the mixed reader/writer start its > transaction with BEGIN IMMEDIATE. > > Igor Tandetnik > -- View this message in context: http://www.nabble.com/concurent-writes-and-locks-tf2084058.html#a5765548 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
Many thanks for helping.
While using SQLite dll Version 3.3.4 on Windows
- Multiple threads/processes access SQLite database,
- Each thread does some SELECTs, INSERTs or UPDATEs.
If for some single SELECT (where user input is used in SQL statement, so to
avoid SQL injection),
sqlite3_prepare()/sqlite3_bind_()/sqlite3_step()/sqlite3_finalize() are
used
then in that case
Q1. Which one is preferable -> BEGIN TRANSACTION or BEGIN IMMEDIATE ?
Q2. In the sample code below, if I remove two statements containing "BEGIN
TRANSACTION;" and "COMMIT TRANSACTION;" then for how long lock will be
acquired i.e. at which point of time lock will be acquired and which point
of time lock will be released ?
Please throw some light on that.
Thanks
Rohit
/* Sample code */
sqlite3* pDB;
sqlite3_stmt* pStmt;
char* szError = 0;
char* szTail = 0;
if( sqlite3_open("test.db", ) != SQLITE_OK )
{
printf("Couldn't open the database.\n");
exit(1);
}
string name;
/* get from user input */
name = "ABCD"; // for testing
nRet = sqlite3_exec(pDB, "BEGIN TRANSACTION;", 0, 0, );
<<---
const char* szSQL = "SELECT id, name, birthdate FROM table1 WHERE name = ?;"
;
nRet = sqlite3_bind_text(mpVM, nParam, szValue, -1, SQLITE_TRANSIENT);
if( sqlite3_prepare(pDB, szSQL, -1, , ) != SQLITE_OK )
throw "sqlite3_prepare Failed";
if ( sqlite3_step(pStmt) == SQLITE_DONE )
{
int i=0;
int empid = sqlite3_column_int(pStmt, ++i);
string name = (const char*) sqlite3_column_text(pStmt, ++i);
double birthdate = sqlite3_column_double(pStmt, ++i);
/* print something */
std::cout << id << "-" << name << "-" << birthdate << endl;
}
nRet = sqlite3_finalize(pStmt);
nRet = sqlite3_exec(pDB, "COMMIT TRANSACTION;", 0, 0, );
<<---
--
View this message in context:
http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5765261
Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Recovery tool ?
Once I experienced a problem during testing where multi-threaded Win32 Application (using SQLite 3.3.4) was running (in development environment) and due to power-failure, rollback journal file was OK but disk bad-sector damaged the sqlite db-file. When I restarted App, db could not be opened (data file was probably corrupted), So I tried to copy db-file to another place and it could not copy (due to disk bad-sector). (I guess, at the time of power-failure, my app may be writing to db-file (after rollback generation) exactly that same moment of power failure, which caused bad sector in disk db-file and db-file got corrupted, so could not be rolled back while opened for the next time). I needed to get copy of db-file from previous night backup. (As hardware failure (bad-sector), does not left us many choices). Rohit -- View this message in context: http://www.nabble.com/Recovery-tool---tf2071432.html#a5737892 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
Thanks for clearing doubt. Now question is... While using SQLite dll Version 3.3.4 on Windows - Multiple threads/processes access SQLite database, - Each thread does some SELECTs, INSERTs or UPDATEs. Wrapping all read-only SELECEs with BEGIN TRANSACTION and using BEGIN EXCLUSIVE to wrap all UPDATEs or INSERTs (including their related SELECTs), Are their possibilities of writer starvation ? And if yes, what is the preferable solution ? (I mean what is the better approach to handle that) Thanks again. Rohit -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5737733 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?
Thanks for the answer and clarification. > BEGIN IMMEDIATE blocks writers, not readers. I think, BEGIN IMMEDIATE surely blocks writers. And also blocks new reader(s) if any new reader tries to do BEGIN IMMEDIATE. Is this correct ? Ref: Quote from SQLite Document (http://www.sqlite.org/lang_transaction.html) "After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE." Rohit -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5713686 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?
Thanks a lot for the answers. Still I have a doubt (and a question). Quote from SQLite Document (http://www.sqlite.org/lang_transaction.html) "After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE." As I understand, it means after issuing BEGIN IMMIDIATE for read-only SELECT(s), no other process can do BEGIN IMMIDIATE even if its for read-only purpose. Even new reader will be blocked if it tries to do BEGIN IMMEDIATE. So if I use BEGIN IMMEDIATE for multiple SELECTs, no other reader thread/process can do BEGIN IMMEDIATE even if it has read-only SELECTs If this is correct, for multiple SELECT statements (read-only, no plan to write), Which one is preferable -> BEGIN TRANSACTION or BEGIN IMMEDIATE ? Rohit -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5713541 Sent from the SQLite forum at Nabble.com.
[sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
Hi All, While using SQLite dll Version 3.3.4 on Windows - Multiple threads/processes access SQLite database, - Each thread does some SELECTs, INSERTs or UPDATEs. Scenario 1 If action of some user needs to execute multiple SELECT statements (read-only, no plan to write), it needs to start explicit transaction to get consistent reads across read-only multiple statements. Q1. Which one is preferable -> BEGIN TRANSACTION or BEGIN IMMEDIATE ? Q2. What is preferable After Multiple SELECT statements gets over -> COMMIT or ROLLBACK ? (note: no change is made since only SELECTs) Scenario 2 If action of some user needs to execute only single SELECT statement (read-only, no plan to write)... As what I understand, there is no point in wrapping such single SELECT in transaction. Q3. What is preferable, whether to start explicit transaction or not for single SELECT ? I truly appreciate any help/guidance. Rohit -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5704541 Sent from the SQLite forum at Nabble.com.
[sqlite] RE: UNICODE Support
Hi Dennis Volodomanov I am using SQLite 3.3.4. My Win32 Application needs international language support (Chinese, Japanese). I need my Win32 Application to build such that, MBCS defined for Windows 98/ME and UNICODE (and _UNICODE) defined for Windows NT/2000/2003/XP. Can you help me by giving some sample code which inserts/selects/updates SQLite db (UTF-8) ? Also if you can help me with some guidelines from your experience regarding using SQLite db (UTF-8) for international languages ? Thank you for any help. Rohit -- View this message in context: http://www.nabble.com/UNICODE-Support-tf58444.html#a5644461 Sent from the SQLite forum at Nabble.com.
[sqlite] INT and INTEGER are not same, behaves differently (for PRIMARY KEY) ?
INT and INTEGER behaves differently (for PRIMARY KEY) !!!
(SQLite 3.3.4)
create table t1 ( id INTEGER PRIMARY KEY );
create table t2 ( id INT PRIMARY KEY );
insert into t1 values(NULL);
insert into t1 values(NULL);
insert into t2 values(NULL);
insert into t2 values(NULL);
/* insert into t1 values('a'); fails */
insert into t2 values('a');
select rowid, id, (id IS NULL) from t1;
select rowid, id, (id IS NULL) from t2;
My observations:
if null is inserted for id column in table t1, it contains 1, 2
if null is inserted for id column in table t2, it contains null values
if text is inserted for id column in table t1, it fails
if text is inserted for id column in table t2, it allows to insert text
Is it advisable not to use INT but use INTEGER for PRIMARY KEY ??
Rohit
--
View this message in context:
http://www.nabble.com/INT-and-INTEGER-are-not-same%2C-behaves-differently-%28for-PRIMARY-KEY%29---tf1929820.html#a5285466
Sent from the SQLite forum at Nabble.com.
Re: [sqlite] id INTEGER PRIMARY KEY vs PRIMARY KEY (id) ???
Thank you very much for clearing my doubt. Rohit. -- View this message in context: http://www.nabble.com/id-INTEGER-PRIMARY-KEY--vs--PRIMARY-KEY-%28id%29-tf1922943.html#a5285302 Sent from the SQLite forum at Nabble.com.
[sqlite] id INTEGER PRIMARY KEY vs PRIMARY KEY (id) ???
Hi SQLite users, In SQLite3 (3.3.4), Will both of the following statements create same tables ? 'id' column will be internally same or different ? create table t1 ( id INTEGER PRIMARY KEY, name CHAR(20) ); create table t1 ( id INTEGER, name CHAR(20), PRIMARY KEY (id) ); What I mean is, -> Following Both are equivalent or not ? id INTEGER PRIMARY KEY and id INTEGER PRIMARY KEY (id) Thanks for any help or clarification. Rohit -- View this message in context: http://www.nabble.com/id-INTEGER-PRIMARY-KEY--vs--PRIMARY-KEY-%28id%29-tf1922943.html#a5265104 Sent from the SQLite forum at Nabble.com.
[sqlite] Storage class and Affinity for BOOL/BOOLEAN, NUMBER, FLOAT, DOUBLE ?
Hi SQLite Gurus, I have gone through online documentation of SQLite3 data types. I need to know (in SQLite 3.3.4), what will be the internal storage class and Column Affinity for columns defined in a table with BOOL/BOOLEAN, NUMBER, FLOAT and DOUBLE ? e.g. CREATE TABLE t1 ( bActive BOOL, bVisible BOOLEAN, number1 NUMBER, number2 FLOAT, number2 DOUBLE ); Thanks for any help or clarification. Rohit -- View this message in context: http://www.nabble.com/Storage-class-and-Affinity-for-BOOL-BOOLEAN%2C-NUMBER%2C-FLOAT%2C-DOUBLE---tf1922928.html#a5265049 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
[EMAIL PROTECTED] wrote: > > Process A wants to modify the database, so it flock()s > the rows it needs to changes and starts changing them. > But half way in the middle of the change, somebody sends > process A a SIGKILL and it dies. The OS automatically > releases the flocks as process A dies, leaving the > database half-way updated and in an inconsistent state, > with no locks. When Process A is being killed and OS automatically releases the flocks, will rollback journal be present or not ? I think, in this situation, hot journal will be left when Process A is killed. > Process B comes along and opens the database, see the > inconsistent state, and reports database corruption. > If Process B finds a rollback journal...then...it can use that to rollback database and bring it to consistent state. --- Curiously thinking on this... What if a new SYSTEM-LOCK-MGMT-TABLE is added in SQLite database managed by itself for locking management ? (Surely, it is not an easy task to manage SYSTEM-LOCK-MGMT-TABLE..!!! ) Assuming that, if there is one an added SYSTEM-LOCK-TABLE in SQLite database managed by itself for locking management. Then when Process A wants to modify the database and it flock()s the rows it needs to changes, it should add corresponding info as record in SYSTEM-LOCK-TABLE for that... When somebody sends process A a SIGKILL and it dies, OS will automatically release flocks as process A dies. Process B (any other process accessing database) can find necessary locking related info from SYSTEM-LOCK-TABLE, as well as hot journal and so Process B can bring database back to consistent state. Rohit -- View this message in context: http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-tf1797052.html#a5255158 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Preferred way to copy/flush new memory db to disk db ?
My requirements are > database file must be removed from disk if any error while > creating/copying tables, records or > indices > other application or other instance of same app must not be able to access > the database, till > database is not ready with necessary minimum tables and records. So to avoid other app accessing, I thought of creating memory db. Rohit -- View this message in context: http://www.nabble.com/Preferred-way-to-copy-flush-new-memory-db-to-disk-db---tf1843573.html#a5081614 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Which is most appropriate encoding ?
> FWIW, if Windows is your only target, UTF-16 is the best choice for > you, because wchar_t is in UTF-16 on Windows and you can avoid some > string conversion in this case, while still be able to enable the > stuff for other OSes as soon as there is a need for it. > > Alexei Alexandrov I think wchar_t will not work on Windows 98. (I do not want to use MS Unicode layer on Windows98). I need my app to work on Windows 98 also. Rohit -- View this message in context: http://www.nabble.com/Which-is-most-appropriate-encoding---tf1780532.html#a5081544 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Dumping Memory-DB to File
Hi I need some help on this. I need to create a new SQLite database with all necessary tables, records and indices. Database file must be removed from disk if any error while creating/copying tables, records or indices. Other application or other instance of same app must not be able to access the database, till database is not ready with necessary minimum tables and records. Question is: How to maintain exclusive access to disk db file till creation/copying from memory db is finished ? Thanks Rohit -- View this message in context: http://www.nabble.com/RE%3A-Dumping-Memory-DB-to-File-t1601385.html#a5033569 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Locking
FoxPro supports row level locking. Ofcourse FoxPro creates one file for each table. There must be some way to implement row level locking. Probable by locking region in a file or somehow. Just thinking curiously...how MS could have implemented row level locking in FoxPro. Rohit -- View this message in context: http://www.nabble.com/Locking-t799702.html#a4899211 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Row Locking
FoxPro has Row Level locking. There must be some way with which it must have been implemented. Just curious...how MS would have implemented row level locking ??? Rohit -- View this message in context: http://www.nabble.com/Row-Locking-t69657.html#a4898759 Sent from the SQLite forum at Nabble.com.
[sqlite] SQLite Vs VistaDB - Comparison ???
Hello I was just comparing embedded SQL database engines (SQLite Vs VistaDB) for my knowledge. Itmight be of help for someone. In case, someone might be interested to know and/or add more feature comparisons. SQLite HomePage : www.sqlite.org More Features : www.sqlite.org, www.sqlite.org/docs.html, www.sqlite.org/faq.html SQLite is a free open-source embedded SQL database engine (for multiple platforms) Cost? : Free Version (with no encryption feature, No password protection) Encryption?: Encryption extension is Commercial, need to purchase license Platforms? : For multiple platforms Source Code Available? : Yes, for free version (No Encryption) Size? : Small 250 KB footprint Single-file database format, database files can be freely shared between machines with different byte orders. Multiple threads/processes can have the same database open at the same time Supports concurrancy Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures Zero-configuration - no setup or administration needed. SQL? : SQLite Implements most of SQL92 (standard SQL language). But does omit some features and added a few features of its own. CSV and text file import and export UTF-8, UTF-16 Support Supports C/C++, PHP, Pearl, tcl, Python and many other languages through C or through own wrappers, .NET port available, WindowsCE port available. More Features : www.sqlite.org, www.sqlite.org/docs.html, www.sqlite.org/faq.html VistaDB HomePage : www.vistadb.com More Features : www.vistadb.com/features.asp VistaDB is a commercial embedded SQL database engine (only for .NET and Win32) Cost? : Need to purchase license, then Royalty free distribution Encryption?: Secure Blowfish encryption and password protection Platforms? : For Different Window Versions Source Code Available? : No Size? : Small 500KB footprint for Embedded Editions Single-file database format Single and Multi-User support Supports concurrancy SQL? : Easy-to-use full featured RDBMS, provides industry support for SQL-92 and powerful Direct Data Access Fast performance No deadlocks - Snapshot Isolation level Transaction Processing SureCommit Row and Table-level locking Automatic Storage Recycling XML Import and Export Automatic data synchronization In-memory databases and tables Write-behind data caching International support Supports C#, VB.NET, Delphi, C++Builder, VB and classic ASP More Features : www.vistadb.com/features.asp Please put forward your views, ideas, thoughts, comparisons (if any) ??? I might have missed many points of comparison/similarity. Rohit -- View this message in context: http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4897159 Sent from the SQLite forum at Nabble.com.
[sqlite] Two instances of app access same db, one instance hangs or killed
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= *
Multiple instances of application can be started at the same time *
* For example two instances of application are running *
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= An
Win32 Application runs on Windows 98/XP/2000
It uses SQLite 3.3.5 compiled with -DTHREADSAFE=1
App Creates/opens two SQLite databases one by one. (DB files are not on
network)
Checks in each database if necessary tables exist, if not creates necessary
tables in each database.
Then in loop, application accepts user commands/requests and processes
User screens allow to Add/View/Modify/Delete records.
User screens allow to get some lists/records based on queries and
calculations.
Application uses db1 and db2 as and when needed.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
* Multiple instances of application can be started at the same time
*
* For example two instances of application are running *
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Two instances of app are running. While each app is inside loop, if one app
instance hangs or killed or user terminates abruptly (Presses
Ctrl+Alt+Delete and kills the app), each database file will be in a
consistant state (SQLite is crash prrof and power-failure proof. Might leave
some rollback journal).
Questions:
1. If app instance is executing some transaction, so it might have locked
database(s) and if that app instance is being killed or hanged, will that
database will remain in locked state or lock will be released ?
2. Pointers to opened databases (db1, db2) in killed app will be consuming
resources, what will happen to them ?
2. How safe it is to continue using those databases by other app instance(s)
?
3. What precautions must be taken care of ? (to avoid database corruption,
to maintain consistant database and for better performance and to avoid any
deadlock)
/* app code snippent just to describe the logic */
int rc;
sqlite3* db1;
sqlite3* db2;
rc = sqlite3_open("C:\file1.db", );
if(rc) {
printf("Cannot open file1.db: %s\n", sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_open("C:\file2.db", );
if(rc) {
printf("Cannot open file2.db: %s\n", sqlite3_errmsg(db2));
exit(1);
}
...
/* code to check if necessary tables exist in db1, if not create tables,
indexes etc.*/
/* code to check if necessary tables exist in db2, if not create tables,
indexes etc.*/
...
bool bExit = false;
do { /* loop for processing user commands */
...
/* User screens allow to Add/View/Modify/Delete records */
/* User screens allow to get some lists/records based on queries and
calculations */
/* Uses db1 and db2 as necessary */
...
if(...user chose to exit...)
bExit = true;
} while (bExit != true);
sqlite3_close(db1);
sqlite3_close(db2);
--
View this message in context:
http://www.nabble.com/Two-instances-of-app-access-same-db%2C-one-instance-hangs-or-killed-t1797037.html#a4897129
Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
> SQLite seeks to keep its database file size minimized. > I think that SQLite should by default continue to follow > its current strategy of minimizing file size. But I > am not adverse to adding a PRAGMA that will put the > database into a different "preallocation" mode where > the database size increases by larger chunks. At the > time of each size inceased, the additional unused space > can simply be added to the freelist. There are a few > unused bytes in the database file header that can be > used to record the fact that the "preallocation" pragma > is in force, so no file format change of any kind is > required - older versions of SQLite would continue to > be able to read and write newer databases. > This is something we may look into over the next > few months > -- > D. Richard Hipp <[EMAIL PROTECTED]> That looks promising. Because SQLite is very good to manage freelists, so such type of preallocation will require to add some more chunk to freelist in SQLite DB file. And app developers who can estimate how SQLite DB file size will be increasing will be able to reduce fragmentation to get improved SQLite performance. (In many cases, end-users of app may not have even idea which database their app is using, where and how app is storing data. So app developers may use such feature for performance improvement) Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4871149 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
Thanks for prompt reply. Agreed. SQLite needs zero-configuration. But applications using multiple SQLite database files for read and write, makes those files with many-many fragments in disk. Which definitely degrades database file read/write performance tremendously. Any solution to that (which does not force end-user of app to manage sqlite file fragments or to defragment disk) ? Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4863094 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
I know and have used deframentation apps. Thats good. But why to force end-users to defragment their disk. SQLite manages free-space in file very-well (after deleting records). So I also created one template database file with optimum number of records, then deleted records and copied that file while creating new company db file. But like some other Databases, if SQLite does provide any way to set auto-increment for database file by some fixed percentage (say 20%, 40% etc.) such that everytime database file reaches some threashould size it automatically add free space, that will reduce file defragmentation. What are your thoughts on that ? Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4862560 Sent from the SQLite forum at Nabble.com.
[sqlite] Avoiding Fragmentation of SQLite database file ???
Hi SQLiteUsers Developing MFC Application (Small Business Accounting Application) (developed using Visual Studio) - App will run on Windows 98/2000 - App uses SQLite database files for storage of data - It will have one database for each company accounts/info. So if accounts of 10 companies, then info will be in 10 different SQLite DB files. - All Database files on same disk. - User of App may create more company file for accounts of another company. - User of App may open any existing company file and enter/modify transactions through different GUI screens. - User closes file and may open another company file and enter/modify transactions through different GUI screens. After using such application, all used SQLite DB files gets fragmented. How to avoid such fragmentation ? Is there any feature, to pre-allocate disk-space to SQLite database file so that initially some free space will be allocated to new file ? Is there any feature, so that when database file gets filled, everytime automatically increment file size by say 25% or 40% of size ? FYI: Such pre-allocation of space and automatic incrementing file size is provided for Database files in SQLServer, Oracle etc. Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4848030 Sent from the SQLite forum at Nabble.com.
[sqlite] Which is most appropriate encoding ?
Hi SQLiteUsers, Need some guidance. While developing Win32/MFC Application (with Visual C++ 6.0) - Application uses SQLite DB for it's data storage - Application must run on most windows (Windows 98, ME, NT, XP, 2000) - User should be able to copy Database from one PC to another PC (one PC may be running Windows 98 and another one Windows 2000 or XP) In this case, which encoding should be used for SQLite database ? Default encoding, UTF-8 or UTF-16 Thanks for any suggestion. Rohit -- View this message in context: http://www.nabble.com/Which-is-most-appropriate-encoding---t1780532.html#a4847672 Sent from the SQLite forum at Nabble.com.
