> You will have to provide more detail about what you are doing to change
> the database. Since you say process A is the only one that write to the
> database, you would probably be best to post the actual code, or at
> least the actual SQL statements, that you are using in that process
Ok. Here it is.
Table File:
-------------------------------------------------
FileID INTEGER PRIMARY KEY Auto Incrment
FullPath TEXT
FileName TEXT
CreationDate REAL
ModifiedDate REAL
Table Segments:
-----------------------------------------------
FileID INTEGER REFERENCES File(FileID)
SegmentID TEXT PRIMARY KEY
SegmentName TEXT
Description TEXT
Table Metadata:
----------------------------------------------
FileID INTEGER REFERENCES File(FileID)
Name TEXT
MetaDataID INTEGER PRIMARY KEY
Table SegmentMetadata
-----------------------------------------------
SegmetnID TEXT REFERENCES Segments(SegmentID)
MetadaID INTEGER REFERENCES Metada(MetaDataID)
Process A: (accepts FilePath as input)
------------------------------------------------------------------
a) Check If File exists in table - "SELECT * from File WHERE FullPath =
<FilePath>
b) BEGIN IMMEDIATE TRANSACTION
c) if exists: UPDATE table File SET CreationDate =<file cretion date>,
ModificationDate = <file modified date>
Info Values)
UPDATE table Segments SET SegmentName = <segment name>, Desription =
<description> WHERE FileID = <FileID of FulePath in File Table>
d) If FilePath doesn't exists then create a new entry File Table, Segments
Segments table, Metadata table and in SegmentMetada, in order
E) COMMIT
-------------------------------------------------------------------
Process B:
-----------------------------------------------------------------
a) Query used to Read Sements in File, say File A:
"SELECT * FROM Segments,File WHERE File.FileID = Segments.FileID AND
File.FullPath = <File A>;
b)Invoke Process A for each set of files
C) Repeat Step a)
Hope I am clear.
Is that BEGIN IMMEDIATE TRANSACTION causing problem? It says, it acquires
RESERVED lock, but it allows other process to read.
--
Bharath
On 3/17/08 8:34 PM, "Dennis Cote" <[EMAIL PROTECTED]> wrote:
> Bharath Booshan L wrote:
>>
>> I will give overview of what is happening
>>
>> App A - Writer process
>> -----------------------------------
>> * Open SQLIte Connection
>> * BEGIN IMMEDIATE TRASACTION
>> * Insert/Update some 1000 rows in Table A,B,C
>> * COMMIT
>> * Close SQLite connection
>> -----------------------------------
>>
>>
>> App B - Reader process
>> ------------------------------------
>> A) Open SQLite Connection
>>
>> B) Read rows fro Table A,B,C based on some constraint -( Returned Results
>> are
>> as expected)
>> ...
>>
>> C) Initiate App A to write some 10-20 times information (This step is
>> executed several times, however it is ensured that all these write
>> operations are serialized)
>>
>> D) Read rows fro Table A,B,C based on some constraint -( Returned Results
>> are not as expected. )
>>
>> E) Close SQLite connection
>> -------------------------------------
>>
>> The actual problem I am facing is at the last step in App B, where I expect
>> that all the information updated/inserted to be returned from query, instead
>> some of the rows in table A,B,C are lost forever.
>
> Based on what you have said here there should be no problems.
>
> You will have to provide more detail about what you are doing to change
> the database. Since you say process A is the only one that write to the
> database, you would probably be best to post the actual code, or at
> least the actual SQL statements, that you are using in that process.
>
>> (just to know what is left in database, I tried to open the database in some
>> document application, for example TextEdit, and the row information which
>> looked as erased are available, and in between all these the database size
>> has not changed )
>>
>
> That is a very unreliable way to look an SQLite database. SQLite doesn't
> necessarily remove data that has been deleted, it may remain in unused
> pages and/or records in the database file. SQLite doesn't change the
> size of the file when rows are deleted or updated, any pages that are no
> longer used are added to an internal free page list.
>
> You should use the command line sqlite3 program to view the database file.
>
>> Overall I guess I am using some wrong version which I need to upgrade to new
>> version, but have to re-consider if it requires major change.
>
> There should be no need to use a different version.
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
-----------------------------------------------
Robosoft Technologies - Come home to Technology
Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails to
and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be safe.
However, no warranty is given that this email is free of malicious content or
virus.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
