Re: [sqlite] "unable to open database file" on DROP
D. Richard Hipp wrote: > > (2) Formal and detail requirements that define precisely what SQLite > does. > > http://www.sqlite.org/draft/tokenreq.html > http://www.sqlite.org/draft/syntax.html > These look great. I noticed a few typos, but on the whole they are very good. I noticed that in many places you specify that sqlite will fail with an error, but you do not specify what the error will be (i.e. the error code and or message isn't specified). Perhaps these details should be added. BTW, have you noticed how much closer these documents are to the style of the SQL standard than your previous, less formal and more more ambiguous, documents were? :-) > > (6) New test harnesses and requirements-based test cases that give > 100% branch coverage and that can be run on embedded systems. > Will this test system be written in C instead of TCL to make it portable to a wider variety of system? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
D. Richard Hipp wrote: > > Adding new result codes is complicated. There exist applications that > only check for the current set of result codes. So if new result > codes are added, those applications might fail. Hence adding new > result codes can lead to compatibility problems in legacy applications. > Oh, that again. :-) It still seems to me that most applications embed the source of a particular version of sqlite, and those applications are safe until the developer updates them to a newer version. That developer is then responsible for ensuring his application code works correctly with the new version of sqlite he is updating to. He is also free to continue to use the older version of sqlite in his application if he doesn't want to make any sqlite related changes for a particular release. This sort of compatibility problem would only be an issue for updates to an sqlite shared library that multiple applications are linking to at run time. So this is perhaps another good reason not to do this. Perhaps the next major release should address this by specifically reserving the right to add new error codes in future versions, and requiring application developers (who are using a shared library) to be prepared to handle new, unknown, error codes. They can still call sqlite3_errmsg to get a string to report the new error, they just won't be able to assume that they can do anything in particular to recover from the new error. If they aren't prepared to do that, they can always eliminate the concern by statically linking to, or embedding, a particular version of sqlite, for which they can handle every possible error. > We have bigger fish to fry. > What kind of fish are you frying now, or is it top secret? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On Aug 12, 2008, at 1:37 PM, Dennis Cote wrote: > Igor Tandetnik wrote: >> "Robert Latest" <[EMAIL PROTECTED]> >> wrote in message >> news:[EMAIL PROTECTED] >>> $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite >>> SQLite version 3.5.1 >>> Enter ".help" for instructions >>> sqlite> create view y as select * from tools; >>> sqlite> create temporary view z as select * from modules; >>> SQL error: unable to open database file >> >> SQLite can't write to your temp directory. That's why you can >> create a >> regular view, but not a temporary view (which requires opening a temp >> database). >> > > I see this has come up yet again. > > Richard, have you given any more thought to implementing a better > error > message for this situation? We discussed this error a while ago in > this > thread > http://thread.gmane.org/gmane.comp.db.sqlite.general/38797/focus=38825 > but you never commented on the viability of the changes I suggested. > Adding new result codes is complicated. There exist applications that only check for the current set of result codes. So if new result codes are added, those applications might fail. Hence adding new result codes can lead to compatibility problems in legacy applications. We would need to add a new extended result code to handle this case. #define SQLITE_CANTOPEN_MAIN (SQLITE_CANTOPEN | (1<<8)) #define SQLITE_CANTOPEN_TEMP (SQLITE_CANTOPEN | (2<<8)) This is feasible, but doing it will require a fairly large effort in making sure that the appropriate error message text is generated in each case, verifying that behavior is unchanged when extended result codes are disabled, fixing legacy test cases, writing new test cases, and so forth. This is at least one full day's worth of uninterrupted work to get right. And the benefit does not seem worth the effort right now. We have bigger fish to fry. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
Igor Tandetnik wrote: > "Robert Latest" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED] >> $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite >> SQLite version 3.5.1 >> Enter ".help" for instructions >> sqlite> create view y as select * from tools; >> sqlite> create temporary view z as select * from modules; >> SQL error: unable to open database file > > SQLite can't write to your temp directory. That's why you can create a > regular view, but not a temporary view (which requires opening a temp > database). > I see this has come up yet again. Richard, have you given any more thought to implementing a better error message for this situation? We discussed this error a while ago in this thread http://thread.gmane.org/gmane.comp.db.sqlite.general/38797/focus=38825 but you never commented on the viability of the changes I suggested. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
Thanks, that was it. I don't know exactly how the errors were interrelated, but once I told sqlite (via the PRAGMA thing) to use memory for tmp stuff, all was fine. I'm working under a cygwin environment which seems to be a bit shaky when it comes to system-specific stuff like permissions. robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
"Robert Latest" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite > SQLite version 3.5.1 > Enter ".help" for instructions > sqlite> create view y as select * from tools; > sqlite> create temporary view z as select * from modules; > SQL error: unable to open database file SQLite can't write to your temp directory. That's why you can create a regular view, but not a temporary view (which requires opening a temp database). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On Mon, Aug 11, 2008 at 1:42 PM, P Kishor <[EMAIL PROTECTED]> wrote: > ahhh... I did misread the question. Yes, the above explanation seems > logical. Your app is probably tying up the db, so you can't drop the > table from the command line. No it ain't. That's of course the first thing I checked. I fact I re-booted the machine just to make sure that no rogue process was holding a lock ono the db. Check this out: $ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> create view y as select * from tools; sqlite> create temporary view z as select * from modules; SQL error: unable to open database file sqlite> drop view y; sqlite> .quit $ So I may create a view, y. Then I'm not allowed to create a temporary view, z (I never am when mucking around with this particular db). After that I want to drop view y (which I just created) and can't, either. This is really puzzling. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On 8/11/08, Mihai Limbasan <[EMAIL PROTECTED]> wrote: > Robert Latest wrote: > > > Hello people, > > > > why is it that I can look at the ".schema" of a db with the sqlite3 > > command line tool, but can't drop a table or view? After all, if the > > db file weren't open, I couldn't even see the schema. > > > > My problem is that I've written an app that uses views to access data. > > After usage, I don't want those views no more. Unfortunately I can't > > DROP them, so I create more and more views with different names and > > keep littering my db with an increasing number of usesless views that > > I can't delete. > > > > CREATE TEMPORARY VIEW produces an "unable to open database file" error as > well. > > > > I'm up a bit of a stump here because re-creating the database (which > > contains millions of lines but is only about 140M total file size) > > takes about a week. > > > > Here's a screenshot that should tell the whole story. I happened to > > try to drop a table, but it really doesn't matter what I drop. > > > > $ sqlite3.exe clhist.sqlite > > SQLite version 3.5.1 > > Enter ".help" for instructions > > sqlite> .schema > > CREATE TABLE Batches ( > >Id TEXT PRIMARY KEY, > >Moves_V1 INTEGER, > >Moves_V2 INTEGER, > >Moves_V3 INTEGER, > >Moves_V4 INTEGER, > >Moves_V5 INTEGER, > >Moves_R1 INTEGER, > >Moves_R2 INTEGER, > >Moves_R3 INTEGER, > >Moves_R4 INTEGER, > >Moves_R5 INTEGER > > ); > > CREATE TABLE Logs ( > >Shortname TEXT PRIMARY KEY, > >Status INTEGER > > ); > > CREATE TABLE Modules ( > >Name TEXT > > ); > > CREATE TABLE Moves ( > >Batch TEXT, > >SlotNo INTEGER, > >Tool TEXT, > >Module TEXT, > >TimeStart INTEGER, > >Duration INTEGER, > >ZipDate TEXT > > ); > > CREATE TABLE Tools ( > >Name TEXT > > ); > > CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; > > CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; > > CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; > > CREATE INDEX MovesBatch ON Moves(Batch); > > sqlite> DROP TABLE "Batches" ; > > SQL error: unable to open database file > > sqlite> .quit > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > Is it possible that the database file is at the same time in use from a > different process? If yes, try shutting down the other database consumers > and retry then. ahhh... I did misread the question. Yes, the above explanation seems logical. Your app is probably tying up the db, so you can't drop the table from the command line. > > -- > Multumesc, > Mihai Limbasan > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
Robert Latest wrote: Hello people, why is it that I can look at the ".schema" of a db with the sqlite3 command line tool, but can't drop a table or view? After all, if the db file weren't open, I couldn't even see the schema. My problem is that I've written an app that uses views to access data. After usage, I don't want those views no more. Unfortunately I can't DROP them, so I create more and more views with different names and keep littering my db with an increasing number of usesless views that I can't delete. CREATE TEMPORARY VIEW produces an "unable to open database file" error as well. I'm up a bit of a stump here because re-creating the database (which contains millions of lines but is only about 140M total file size) takes about a week. Here's a screenshot that should tell the whole story. I happened to try to drop a table, but it really doesn't matter what I drop. $ sqlite3.exe clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> .schema CREATE TABLE Batches ( Id TEXT PRIMARY KEY, Moves_V1 INTEGER, Moves_V2 INTEGER, Moves_V3 INTEGER, Moves_V4 INTEGER, Moves_V5 INTEGER, Moves_R1 INTEGER, Moves_R2 INTEGER, Moves_R3 INTEGER, Moves_R4 INTEGER, Moves_R5 INTEGER ); CREATE TABLE Logs ( Shortname TEXT PRIMARY KEY, Status INTEGER ); CREATE TABLE Modules ( Name TEXT ); CREATE TABLE Moves ( Batch TEXT, SlotNo INTEGER, Tool TEXT, Module TEXT, TimeStart INTEGER, Duration INTEGER, ZipDate TEXT ); CREATE TABLE Tools ( Name TEXT ); CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; CREATE INDEX MovesBatch ON Moves(Batch); sqlite> DROP TABLE "Batches" ; SQL error: unable to open database file sqlite> .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Is it possible that the database file is at the same time in use from a different process? If yes, try shutting down the other database consumers and retry then. -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "unable to open database file" on DROP
On 8/11/08, Robert Latest <[EMAIL PROTECTED]> wrote: > Hello people, > > why is it that I can look at the ".schema" of a db with the sqlite3 > command line tool, but can't drop a table or view? After all, if the > db file weren't open, I couldn't even see the schema. I have no idea what you are talking about... of course, you can drop a view or a table. Here you go... [04:23 PM] ~$sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> CREATE TABLE foo (a, b); sqlite> CREATE VIEW bar AS SELECT * FROM foo; sqlite> .s CREATE TABLE foo (a, b); CREATE VIEW bar AS SELECT * FROM foo; sqlite> DROP VIEW bar; sqlite> .s CREATE TABLE foo (a, b); sqlite> DROP TABLE foo; sqlite> .s sqlite> Maybe I didn't understand your question. > > My problem is that I've written an app that uses views to access data. > After usage, I don't want those views no more. Unfortunately I can't > DROP them, so I create more and more views with different names and > keep littering my db with an increasing number of usesless views that > I can't delete. > > CREATE TEMPORARY VIEW produces an "unable to open database file" error as > well. > > I'm up a bit of a stump here because re-creating the database (which > contains millions of lines but is only about 140M total file size) > takes about a week. > > Here's a screenshot that should tell the whole story. I happened to > try to drop a table, but it really doesn't matter what I drop. > > $ sqlite3.exe clhist.sqlite > SQLite version 3.5.1 > Enter ".help" for instructions > sqlite> .schema > CREATE TABLE Batches ( > Id TEXT PRIMARY KEY, > Moves_V1 INTEGER, > Moves_V2 INTEGER, > Moves_V3 INTEGER, > Moves_V4 INTEGER, > Moves_V5 INTEGER, > Moves_R1 INTEGER, > Moves_R2 INTEGER, > Moves_R3 INTEGER, > Moves_R4 INTEGER, > Moves_R5 INTEGER > ); > CREATE TABLE Logs ( > Shortname TEXT PRIMARY KEY, > Status INTEGER > ); > CREATE TABLE Modules ( > Name TEXT > ); > CREATE TABLE Moves ( > Batch TEXT, > SlotNo INTEGER, > Tool TEXT, > Module TEXT, > TimeStart INTEGER, > Duration INTEGER, > ZipDate TEXT > ); > CREATE TABLE Tools ( > Name TEXT > ); > CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; > CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; > CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; > CREATE INDEX MovesBatch ON Moves(Batch); > sqlite> DROP TABLE "Batches" ; > SQL error: unable to open database file > sqlite> .quit > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "unable to open database file" on DROP
Hello people, why is it that I can look at the ".schema" of a db with the sqlite3 command line tool, but can't drop a table or view? After all, if the db file weren't open, I couldn't even see the schema. My problem is that I've written an app that uses views to access data. After usage, I don't want those views no more. Unfortunately I can't DROP them, so I create more and more views with different names and keep littering my db with an increasing number of usesless views that I can't delete. CREATE TEMPORARY VIEW produces an "unable to open database file" error as well. I'm up a bit of a stump here because re-creating the database (which contains millions of lines but is only about 140M total file size) takes about a week. Here's a screenshot that should tell the whole story. I happened to try to drop a table, but it really doesn't matter what I drop. $ sqlite3.exe clhist.sqlite SQLite version 3.5.1 Enter ".help" for instructions sqlite> .schema CREATE TABLE Batches ( Id TEXT PRIMARY KEY, Moves_V1 INTEGER, Moves_V2 INTEGER, Moves_V3 INTEGER, Moves_V4 INTEGER, Moves_V5 INTEGER, Moves_R1 INTEGER, Moves_R2 INTEGER, Moves_R3 INTEGER, Moves_R4 INTEGER, Moves_R5 INTEGER ); CREATE TABLE Logs ( Shortname TEXT PRIMARY KEY, Status INTEGER ); CREATE TABLE Modules ( Name TEXT ); CREATE TABLE Moves ( Batch TEXT, SlotNo INTEGER, Tool TEXT, Module TEXT, TimeStart INTEGER, Duration INTEGER, ZipDate TEXT ); CREATE TABLE Tools ( Name TEXT ); CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330"; CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P"; CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1"; CREATE INDEX MovesBatch ON Moves(Batch); sqlite> DROP TABLE "Batches" ; SQL error: unable to open database file sqlite> .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
