[sqlite] efficient way to figure out if a table is empty
Not that is critical for my application, but just for curiosity which is the recommended idiom to figure out whether a table has any register? -- fxn - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: locked implies exception?
On Jul 24, 2006, at 11:47, Xavier Noria wrote: I am trying to understand a crash I get very often. There are two processes accessing to the same database in a Windows XP (through Active Record) doing simple CRUDs on tables, and from any of the two at random I get an SQLite3::BusyException, for example (copied by hand): SQLite3::BusyException: database is locked: SELECT count(*) AS count_all ... The processes use transactions, but my understanding is that if the database is locked the other one will wait to do its stuff until it is released. Is that right? If so, all of this is quite quick, is there any tiny timeout that raises the exception if the lock is not acquired? Os does it sound like an adapter issue to you? Just for the archives, the problem is fixed. Precisely, by default the SQLite adapter does nothing special with SQLITE_BUSY and provides hooks busy_handler and busy_timeout. Michael Meckler gave this solution in the Rails mailing list, which is tu subclass the main persitence class to configure that stuff per connection: class RetryModel < ActiveRecord::Base self.abstract_class = true self.connection.raw_connection.busy_timeout(2000) if self.connection.adapter_name == 'SQLite' end and make all persistence models be children of that one. -- fxn
Re: [sqlite] sqlite3_prepare = SQLITE_BUSY????
On Jul 24, 2006, at 14:49, [EMAIL PROTECTED] wrote: Daniel van Ham Colchete <[EMAIL PROTECTED]> wrote: I was trying to understand why would a sqlite3_prepare return SQLITE_BUSY. If SQLite does not already have the database schema loaded into cache, it needs to read the schema out of the SQLITE_MASTER table before it can prepare the new SQL statement. If another process has the database locked, this read is not possible and sqlite3_prepare will return SQLITE_BUSY. Maybe this has something to do with the problem I reported in "locked implies exception?". Active Record in development mode calls table_info a lot and if that does not place nice with locks at least I know what to fix. Is it a good guess? -- fxn
Re: [sqlite] locked implies exception?
On Jul 24, 2006, at 11:47, Xavier Noria wrote: The processes use transactions, Not necessarily. Could it have to do with AR asking for metadata about the schema? Just an idea.
[sqlite] locked implies exception?
I am trying to understand a crash I get very often. There are two processes accessing to the same database in a Windows XP (through Active Record) doing simple CRUDs on tables, and from any of the two at random I get an SQLite3::BusyException, for example (copied by hand): SQLite3::BusyException: database is locked: SELECT count(*) AS count_all ... The processes use transactions, but my understanding is that if the database is locked the other one will wait to do its stuff until it is released. Is that right? If so, all of this is quite quick, is there any tiny timeout that raises the exception if the lock is not acquired? Os does it sound like an adapter issue to you? -- fxn
Re: [sqlite] updating SQLite to implement The Third Manifesto
On Mar 11, 2006, at 7:58, Andrew Piskorski wrote: On Fri, Mar 10, 2006 at 04:37:36PM -0800, Darren Duncan wrote: 3. There is no such thing as a NULL. 3.1 All logic is 2VL (true, false) not 3VL (true, false, unknown). There is no such thing as null, really? So, when you do an outer join between two tables, which in SQL would produce null columns in the result set, what do YOU propose producing instead of those nulls? Perhaps I missed it, but in my brief reading of some of Date's work, I never saw him answer that question. I never understood that restriction. I read in the books: "since we have defined things this ways from a formal point of view there's no room for NULL". And my question is well, why don't you change the definitions to augment the datatype sets with a special constant NULL which is by definition not present in any datatype? Wouldn't that give an analogous theory more aligned with real world? The formalism in the relational model looks so-so to me (with due respect), starting from the fact that "tuples" are _sets_ instead of elements of a Cartesian Product, which by the way is what relations are defined from in Set Theory, they are subsets of Cartesian Products, not some kind of ad-hoc object. Sounds souspicious the claimed math foundation with so fundamental deviations from basic, standard math conventions. More than math foundation it is in my view, I don't know, just some stuff presented formally and with some degree of rigour. -- fxn
Re: [sqlite] Book?
On Feb 11, 2006, at 17:59, Clint Bailey wrote: Hi, Is there any know book in print for sqlite that is included in PHP5? I just finished "SQLite" http://www.bookpool.com/sm/067232685X and has helped me a lot, then the deltas in the web site helps to stay in sync with 3.x. I saw in Amazon.com this new book is expected to be out by May: http://www.amazon.com/gp/product/1590596730 -- fxn
Re: [sqlite] Question about Regular Expression
On Feb 10, 2006, at 16:51, malcom wrote: Hello, I have a sqlite column with a string. This string is composed by n different lines separated by an \n character. Each line is composed by : . So my final string is something like this: : \n : \n : Now I need to search *only* inside a particular key value. For example I would to see if the value of key_2 contains a string 'test' (and if possible return the entire value of key key_2). Is it possible? My solution is to use regular expression (anyone can hep me? I don't know more about them). Let me double-check the problem: you are given the text, then key_2, and then you need to search whether key_2 contains "text"? I guess this is not the case because otherwise it would be trivial. How is the "particular key" to search in specified? An index? Or is that you need to fetch all the keys that contain "text"? In which programming language? -- fxn
Re: [sqlite] Creating a (really) big table
On Feb 10, 2006, at 13:01, James Biggs wrote: I can do for example $dbh->do( "CREATE TABLE my_table (etc etc etc)"); but i don't know a Perl command for creating a table with many columns. I did not find one in the docs either. Thanks The idea is that you build the SQL string dynamically: my @column_defs = fetch_column_defs_from_somwehere(); my $sql = <
Re: [sqlite] delete all tables
On Feb 9, 2006, at 23:02, Marian Olteanu wrote: I would say that the fastest way (CPU cycles and lines of code) to delete all tables would be to delete the file in which the database is stored. Clever!
[sqlite] delete all tables
In the schema definition I would like to avoid the combo delete if exists table_name; create table_name ( ... ); Can I query sqlite_master about tables, indexes, etc. in a way that allows the deletion of everything in one shot beforehand? -- fxn
Re: [sqlite] question about performance
On Feb 8, 2006, at 17:24, [EMAIL PROTECTED] wrote: If you do not do a BEGIN...COMMIT around your inserts, then each insert has an implied BEGIN...COMMIT around itself. That means you are doing 50 COMMITs. A COMMIT is slow because it is "Durable" (The "D" in ACID). That means that the operation will not complete until all the information is safely written to the surface of the disk and can survive a power failure. Making sure everything is on the disk surface, and not just in OS cache buffers, normally requires 2 or 3 complete rotations of the disk platter. Depending on your disk drive, the platter probably spins about 120 times per second. So a COMMIT requires about 1/40th to 1/60th of a second to complete. The CPU is mostly idle during this time - the time is spent waiting on the disk platter to rotate under the write head again. But it is still time. By wrapping the inserts into a single transaction, you only do a single COMMIT at the end, instead of 50 individual COMMITs. The final commit is a lot bigger, but it still only requires 2 or 3 disk platter rotations, so it does not require any extra wall-clock time. 50 times less waiting makes things run a lot faster. Really really interesting, I had no idea there were so many fine details behind a transaction. Thank you all for your responses! -- fxn
Re: [sqlite] question about performance
On Feb 8, 2006, at 17:10, Doug Nebeker wrote: When you don't wrap everything in a transaction, each statement becomes it's own transaction. And the database file is opened, updated, and closed on each transaction. So your first case had roughly 50 times the amount of file I/O and transaction startup/commit overhead as the second case. I see. Let me ask a few more questions to help me get the picture. A transaction is stored in memory until committed? As a rule of thumb can I imagine that one transaction equals roughly to a single open/ edit/close? Is the file opened and locked right when the transaction begins? -- fxn
[sqlite] question about performance
I have a simple schema and a sql loader that fills a table with initial values: delete from foo; insert into foo ...; insert into foo ...; ... about 50 inserts ... To my surprise, the execution of these inserts took a few seconds (SQLite is 3.3.3). However, if I wrapped the entire loader in a transaction: begin transaction; delete from foo; insert into foo ...; insert into foo ...; ... about 50 inserts ... commit transaction; then it was immediate. Why? -- fxn
[sqlite] about character encodings
I have a couple questions regarding character encodings in SQLite 3: * Do databases or tables have an associated character encoding? If they do how is it configured? * The description for the TEXT class mentions "using the database encoding (UTF-8, UTF-16BE or UTF-16-LE)", is there support for more encodings? (not that I need them, just to know the features of the database). -- fxn
[sqlite] OT: in-memory for testing in Rails
I asked this in a couple of Rails places without luck. I guess this is actually related to Rails, just ask it here in case someone knows about it. The file config/database.yml rails generates has as SQLite database for testing ":memory:", but the simplest of the tests in the Agile book does not work, it produces this trace: % ruby test/unit/product_test.rb Loaded suite test/unit/product_test Started E/usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/lib/sqlite3/ errors.rb:94:in `check': cannot rollback - no transaction is active (SQLite3::SQLException) from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/resultset.rb:76:in `check' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/resultset.rb:68:in `commence' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/resultset.rb:61:in `initialize' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/statement.rb:158:in `execute' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/database.rb:211:in `execute' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/database.rb:186:in `prepare' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/database.rb:210:in `execute' from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.1.0/ lib/sqlite3/database.rb:620:in `rollback' ... 11 levels... from /usr/local/lib/ruby/1.8/test/unit/autorunner.rb:200:in `run' from /usr/local/lib/ruby/1.8/test/unit/autorunner.rb:13:in `run' from /usr/local/lib/ruby/1.8/test/unit.rb:285 from test/unit/product_test.rb:11 I am lost here. Does that ring any bell? Tests run fine with a regular SQLite database. -- fxn "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." -- Larry Wall in comp.lang.perl
[sqlite] advice on schema evolution
Say you are doing some web development with SQLite in the backend. You progressively fill the database with the very web interface you are writing, throwing that data is not desirable in general. But at the same time the schema evolves with the project, tables are added, columns are renamed, etc. How do you deal with this? Any best practices you'd like to share? -- fxn
