[sqlite] tool to browse a sqlite database
I'm loking for some gui tools for looking at and changing my sqlite database, the only ones I know of are the firefox extension and sqlitemanager. the firefox extension fails with: Error in opening file messages.sqlite - perhaps this is not an sqlite db file Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageService.openUnsharedDatabase] sqlitmanager has it's own problems. the database seems sane, sqlite3 likes it and the app runs. any other suggestions on what gui management tools are available? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple tables within a database
what's the general rule for deciding when to put multiple tables within a single sqlite db file? I think the answer is something like you put tables together in one database file if they refer to different aspects of the same data element and you put them in separate database files if there's no connection except the databases are used in the same application. For example, in my case, I have one table which contains the raw original data, a thoroughly cooked form of the original data, and a series of data elements which are used for searching and display. The related table contains information derived from postprocessing and will also be used for searching and graphing. The second table's information could be regenerated anytime at a cost of running through every record in the database and recalculating it. As a result of this association, I figure it's appropriate to place both records in the same database file. The third table tracks data from another part of the process and has no connection to the first two tables except that it is used as part of the postprocessing calculations that feed the second table described above. I figure the third table should go in its own database. For what it's worth, record counts could easily cross 100,000 for each one of the tables. Hope it's not time for mysql :-) Thanks for a feedback. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last N records
Dennis Cote wrote: Eric S. Johansson wrote: what is the easiest way to hold on to the last N records and delete all older? I can't figure out the right where expression. --- eric Eric, I posted some sample code to use a table as FIFO (last N records) to the list a while ago. See http://article.gmane.org/gmane.comp.db.sqlite.general/16175/match=fifo for details. thanks Dennis. That looks interesting. ---eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] last N records
what is the easiest way to hold on to the last N records and delete all older? I can't figure out the right where expression. --- eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: create or update question
Igor Tandetnik wrote: Eric S. Johansson <[EMAIL PROTECTED]> wrote: I'm trying to figure out how to do do the following: if the record exists Update record with calculation ( a = a + v) else insert record with default values It seems like the "insert or replace into" capabilities is close to what I need but I can't figure out how to update with a default on the first record and update with a calculation on existing records. I can't think of any way to avoid two requests. Run the update statement, use sqlite3_changes to check whether any row has actually been updated. If not, run the insert. since I'm using pysqlite, I'm using the following model (which will probably make sql knowledgeable folks cringe) --- insert_command = 'insert into test (x, y, z) values (?,?,?)' update_command='update test set z = z + ? where (x=? and y=?)' try: self.cursor.execute(insert_command, (ext_map, ID, 0.0)) except Exception, error: self.cursor.execute(update_command,(4.0, ext_map, ID,)) self.connection.commit() --- if the insert fails (i.e. record exists), it triggers an exception which I use to trigger an update. I get many more updates than inserts of course but I haven't figured out how to trigger an exception on update if the record doesn't exist. -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] create or update question
I'm trying to figure out how to do do the following: if the record exists Update record with calculation ( a = a + v) else insert record with default values It seems like the "insert or replace into" capabilities is close to what I need but I can't figure out how to update with a default on the first record and update with a calculation on existing records. Seems like this should be a very common idiom but I can't find any notes on how to do it. --- eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
Samuel R. Neff wrote: Some of this performance gain is probably related to caching data and query plan, not just opening the connection, but still that caching is connection related and is lost when you close the connection so it's a very real-world valid comparison. no surprise that connect() is expensive but what is the cost of cursor()? is it cheap or expensive? -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] more questions from the reasonably naïve
I think some of these questions fall in the category of "running in the dark with scissors". If it's a simple matter of I need to read something, just point me at the appropriate documentation. I'm trying to get a handle on this cursor thing. obviously it preserves state of some sort but I'm missing what state it is preserving. For example, do I need a different cursor for each table in the database? if it helps any, multiple tables will be in play at the same time. Record locking. I did read http://www.sqlite.org/lockingv3.html I do a fair number of read/modify/write cycles on individual rows in all of the tables from multiple processes. if I interpret the documentation correctly, I need to create a transaction and when I am done changing things, "commit" which is really just turning on auto commit. Committing. how often should one commit? Is it even necessary if you close? What happens in case of a crash? Does journaling save one's bacon? If the change has not been "committed" will be visible to other processes using the same table? Many thanks for your answers and patience. -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] query on match between application and sqlite
Eric S. Johansson wrote: Again, I do most sincerely apologize for size of this request. I've spent 30 years avoiding databases and I guess this is payback. :-) Any advice, good online tutorials on SQL, or feedback would be valued. urk. I hate it when I forget to ask for what I need after info dumping what I got. 1) need to know if sqlite can help me cut down on the number of little db's (shat well trod path do I take) 2) can I use it for a message store and have it run faster than a 1 message per file system? 3) how would one allocate the different data sets across how many dbms? again, many thanks and I'm sorry about being frazzled. --- eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] query on match between application and sqlite
I apologize if this comes across as a "do my homework for me" type of query but I'm in a bit of a bind and I'm trying to come up to some reasonable amount of knowledge in less than no time. I've been using ad hoc DBM files for a variety of small databases and well, it's crumbling. I'm seeing the disk light on solid for what I consider low volumes of traffic. The application is an experimental anti-Spam testbed exploring the use of reputation through proof for puzzles and user interaction. The list of things I'm tracking is fairly significant (for me) and I'm trying to pull things together. the entire system has been implemented in Python so that puts additional constraints on the solution. -- the players -- Reputation database is keyed by IP address and had a single integer (reputation). Its visibility is exposed lan wide by a remote object call. Rate of arrival database key is recipient address and contains 10 timestamps for the most recently received messages. Again, exposed via remote object call. received messages database. All messages that come in are saved in one of three categories green, yellow, red (a.k.a. ham, mystery meat, spam) on a per user basis. Associated with each message are a series of additional data elements such as filter scored, any transitions between colors, source IP address, last filter seen. visibility is strictly on a single machine although among a small number of processes. for me the challenge with the received message database is accessing messages, expiration of messages and transitions between colors. info exhaust: a list of all things to be graphed/tracked. Probably best as a log file but I thought I would add it just in case it made sense as a database. needs to roll over periodically and expire older data. additionally, is a friends white list. The key is the e-mail address of the "friend" and the data doesn't really matter. It's the presence of the key that counts. This is also a per user database used between a small number of processes. Again, I do most sincerely apologize for size of this request. I've spent 30 years avoiding databases and I guess this is payback. :-) Any advice, good online tutorials on SQL, or feedback would be valued. ---eric -- Speech-recognition in use. It makes mistakes, I correct some. - To unsubscribe, send email to [EMAIL PROTECTED] -
