Bharath Booshan L wrote: > > 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)
It seems strange to change the creation date for an update. I would think this should only happen when the record is created in step d. What do you mean by the line that says "Info Values)"? Where do the <file cretion date> and <file modified date> come from? > UPDATE table Segments SET SegmentName = <segment name>, Desription = > <description> WHERE FileID = <FileID of FulePath in File Table> > Where do the <segment name> and <description> come from? > d) If FilePath doesn't exists then create a new entry File Table, Segments > Segments table, Metadata table and in SegmentMetada, in order > Where does all the data to create the new records come from? You say Process A only has a FilePath as input. > E) COMMIT > ------------------------------------------------------------------- > > Process B: > ----------------------------------------------------------------- > a) Query used to Read Sements in File, say File A: > So, a File has one or more segments. > "SELECT * FROM Segments,File WHERE File.FileID = Segments.FileID AND > File.FullPath = <File A>; > This will return the primary key for each segment associated with the file. With that you can get any other info about the segment that may be needed. select SegmentID from File join Segments using(FileID) where FullPath = <File A>; > b)Invoke Process A for each set of files Where is this "set of files" coming from? The query in step a returns results for a single file only. Process A takes a FilePath parameter, the same as used in the where clause in query above I would assume. > > C) Repeat Step a) > This is where you say you are having problems, correct? Are you saying that you are not getting the same segments for the file FileA after running process A with a parameter value of FileA? This is unexpected since you say Process A only updates existing rows if the file FileA exists. It only adds new rows if FileA doesn't exist. But if FileA doesn't exist when you run Process A then there would have been nothing to return from the query in step a above, so any new rows would be expected. If the file exists, then you update some column values, but not the values that are used to select the results in the query in step a above, so the results should be the same. Is there more stuff happening elsewhere that you haven't described? Are the file and/or segment rows ever deleted? > > Is that BEGIN IMMEDIATE TRANSACTION causing problem? It says, it acquires > RESERVED lock, but it allows other process to read. I don't think that is your problem. The reserved lock simply lets other processes read until the holder is ready to start making changes. The reserved lock will change to an exclusive lock when process A starts its first update command. It is intended to let a process read with the assurance that no other process can write to the database. It would typically be used to move your step a in process A to a point between step b and step c like this. a) BEGIN IMMEDIATE TRANSACTION b) Check If File exists in table - "SELECT * from File WHERE FullPath = <FilePath> c) if exists: UPDATE table File SET CreationDate =<file cretion date>, Process A knows it will write later, so the reserved lock at step a lets others continue to read until it gets to step c or step d. The advantage of this is that it prevents some other process form executing a delete between your steps a and c for example. If the file existed when your process A did its existence check as step a, but didn't exist when it got to step c, there would be problems (not the problems you are seeing though). It is probably a good idea to acquire the lock (i.e. start the immediate transaction) before you start reading the database though. Dennis Cote _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
