Re: [sqlite] malformed disk image
At 03:43 PM 12/22/2004, you wrote: See section 6.0 in http://www.sqlite.org/lockingv3.html. That article is on SQLite version 3, but the methods for corrupting a database apply equally well to version 2. Thanks. Unfortunately, none of these seem terribly likely. The user reported that nothing unusual occurred. The corruption occurred after installing some new software on his Palm which created a new entry in the main table (DBBackup). He is an IT guy, so reasonably knowledgable. He claims that he hasn't had any incidents of disk corruption to date. Short of writing randomly into the database file, is there any other software means I could corrupt the db than to write binary data without encoding it? Is there any way to tell if that is what likely occurred if I trace into the validation pragma query? thanks. michael
Re: [sqlite] Detecting if the db has changed
Are you using your Python binding? If yes, maybe you can make the "commit" action invoke a callback which updates said private table. Every code path that writes to tables does so within a transaction, no? Of course, if your code commits by issuing an SQL statement in place instead of calling a Python function that wraps said SQL statement, then you still have to touch every code path. The APSW wrapper does not have a commit function since the C API to SQLite doesn't have one (APSW maps closely to how SQLite actually works as opposed to how DBAPI defines things). I can certainly hack my own scheme based on your suggestion or the bytes Richard mentioned. It would feel even cleaner available as a pragma. Roger
Re: [sqlite] Detecting if the db has changed
On Wed, Dec 22, 2004 at 06:53:15AM -0800, Roger Binns wrote: > >If concurrent access is by instances of your program only, they can > >rendezvous via a private table. > > Yes, but that involves modifying every single code path that could > update any of the many tables I use to update the private table. Are you using your Python binding? If yes, maybe you can make the "commit" action invoke a callback which updates said private table. Every code path that writes to tables does so within a transaction, no? Of course, if your code commits by issuing an SQL statement in place instead of calling a Python function that wraps said SQL statement, then you still have to touch every code path. Cheers. -- Ng Pheng Siong <[EMAIL PROTECTED]> http://sandbox.rulemaker.net/ngps -+- M2Crypto, ZServerSSL for Zope, Blog http://www.sqlcrypt.com -+- Database Engine with Transparent AES Encryption
Re: [sqlite] ODBC Driver
Dan Keeley wrote: Hi, I've finally got round to building sqlite. Now i'm after the odbc driver for it. Only thing is I can't seem to find the page on the sqlite website that lists other programs, such as an odbc driver for use with sqlite? Is there a recommended odbc driver to use? Dan, The page that gives the (very impressive) list of drivers and wrappers for SQLite is: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers The specific one you want is: http://www.ch-werner.de/sqliteodbc/ I hope you will enjoy working with SQLite as much as I have. Gerry
Re: [sqlite] malformed disk image
Michael Hunley wrote: What else could cause this "corruption?" See section 6.0 in http://www.sqlite.org/lockingv3.html. That article is on SQLite version 3, but the methods for corrupting a database apply equally well to version 2. Is there any way to repair the db so the user does not lose his data? Not really. You can try to recover some of the data by using the ".dump" command on individual tables. Any tables that do not contain corruption should come out OK. Version 3 does a better job of trying to make sense of a corrupt database, but that doesn't really help users of 2.8. In either version, the database file does not contain enough redundancy to do any meaningful recovery once corruption sets in. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] ODBC Driver
Dan Keeley wrote: Hi, I've finally got round to building sqlite. Now i'm after the odbc driver for it. Only thing is I can't seem to find the page on the sqlite website that lists other programs, such as an odbc driver for use with sqlite? Is there a recommended odbc driver to use? You do not need an ODBC Driver in order to use a sqlite database. You can directly talk to database via API. Regards Oliver
Re: [sqlite] Changing table and security
> > > > > >You're altering tables you don't understand the structure of? > > > > > > > > > Think about a php forum... someone could choose to make a mod and add > a > column for the birthday in the users table... you can't know if > someone did it or not... Arg! Just invites people to crash your web site. = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
Re: [sqlite] Changing table and security
Jay wrote: --- Paolo Vernazza <[EMAIL PROTECTED]> wrote: Tell your friends that they need to read the FAQ. http://www.sqlite.org/faq.html#q13 Sorry, Richard, but I think that it isn't so simple you must also store triggers and indexes... and maybe you don't know what's the table structure is, so you must parse youd table structure and modify it... You're altering tables you don't understand the structure of? Think about a php forum... someone could choose to make a mod and add a column for the birthday in the users table... you can't know if someone did it or not... Paolo
Re: [sqlite] Changing table and security
On Tue, 21 Dec 2004, Paolo Vernazza wrote: >D. Richard Hipp wrote: > >> >> >> Tell your friends that they need to read the FAQ. >> http://www.sqlite.org/faq.html#q13 > >Sorry, Richard, but I think that it isn't so simple you must also >store triggers and indexes... and maybe you don't know what's the table >structure is, so you must parse youd table structure and modify it... Or, you could have a seperate script create the schema, then import the data from the old schema. Using sqlite shell and ATTACH, you should be able to script any data migration. As this sounds like a new project, they have the flexibility to initialise the schema how they want. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] What's the difference of "select * from tb" and "select "ID" from tb"?
CARIOTOGLOU MIKE wrote:
> I can verify this :
>
> Select "columnName" from someTable gives columns with quoted names !
> ("ColumnName") instead of ColumnName
>
> This *feels* like a bug
>
I believe this is a bug as well.
The following script demonstrates the problems using the sqlite3 shell. I
use a column name with a space that requires quoting when it is created. SQL
uses single quotes to demark literal strings, and double quotes to demark
column names.
mode column
headers on
create table t ('I D' varchar);
insert into t values ('abcd');
select * from t;
I D
--
abcd
select I D from t;
SQL error: no such column: I
select 'I D' from t;
'I D'
--
I D
select 'I D' as 'I D' from t;
I D
--
I D
select "I D" from t;
"I D"
--
abcd
select "I D" as 'I D' from t;
I D
--
abcd
select "I D" as "I D" from t;
I D
--
abcd
select "I D" as I D from t;
SQL error: near "D": syntax error
The "select *" case returns an un-quoted column name. This is correct.
The un-quoted column name case returns an error as it should.
Adding single quotes to the name make it a literal character value, which
returns the correct value, but the column name that is generated by SQLite
includes the single quote characters. I believe this is incorrect. It should
produce the same column name as the next case where a literal string is used
as the column name. If there are wrappers that need to quote these names
(for a CSV file output for example) that should be done by the wrapper, not
by SQLite.
Similarly, the double quoted column name case returns the correct result
with the wrong column name. SQLite has included the quotes in the column
name. Again the correct result can be achieved by specifying the column name
as a literal string.
Interestingly, SQLite also allows the literal name to be specified using
double quote characters. This lead me to check the SQL standard. It says
that the only legal way to specify a literal column name in an AS clause is
using double quotes. So SQLite's previous acceptance of the single quoted
literals can also be viewed as a standard non-conformance bug.
Finally, SQLite generates an error, as it should, if you try using an
un-quoted column name literal with a space.
Dennis Cote
Re: [sqlite] Changing table and security
D. Richard Hipp wrote: > Before doing the "DELETE TABLE t1" you can run this query: > > SELECT sql FROM sqlite_master WHERE tbl_name!=name AND > tbl_name='t1'; > > That query will give you the complete text of all CREATE INDEX > and CREATE TRIGGER statements associated with table t1. Save > these results. Then delete table t1 and recreate it in its > new form. Then rerun the CREATE INDEX and CREATE TRIGGER > statements that you saved. This will automatically recreate > all of your indices and triggers. Richard, This will help in many cases, but it won't work if the triggers or indexes reference columns that were deleted or renamed by the "ALTER TABLE".
Re: [sqlite] Changing table and security
> > You missed his point. When you use the steps in the FAQ to alter > > a table, one of the steps involves deleting it. When you do that > > SQLite also deletes all existing triggers and indices on the > > table. > > > > Consequently one piece of code that is simple (alter table) > suddenly has > > to be aware of a lot more other things. It > > is all a simple matter of programming, but makes the code > > more coupled, complex and brittle. True, but it's important to know the database structure, and why it was made the way it was, before you start changing it. > > > > Before doing the "DELETE TABLE t1" you can run this query: > > SELECT sql FROM sqlite_master WHERE tbl_name!=name AND > tbl_name='t1'; > > That query will give you the complete text of all CREATE INDEX > and CREATE TRIGGER statements associated with table t1. Save > these results. Then delete table t1 and recreate it in its > new form. Then rerun the CREATE INDEX and CREATE TRIGGER > statements that you saved. This will automatically recreate > all of your indices and triggers. Very useful info! Thanks everyone :) = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Send holiday email and support a worthy cause. Do good. http://celebrity.mail.yahoo.com
Re: [sqlite] Changing table and security
Roger Binns wrote: You're altering tables you don't understand the structure of? You missed his point. When you use the steps in the FAQ to alter a table, one of the steps involves deleting it. When you do that SQLite also deletes all existing triggers and indices on the table. Consequently one piece of code that is simple (alter table) suddenly has to be aware of a lot more other things. It is all a simple matter of programming, but makes the code more coupled, complex and brittle. Before doing the "DELETE TABLE t1" you can run this query: SELECT sql FROM sqlite_master WHERE tbl_name!=name AND tbl_name='t1'; That query will give you the complete text of all CREATE INDEX and CREATE TRIGGER statements associated with table t1. Save these results. Then delete table t1 and recreate it in its new form. Then rerun the CREATE INDEX and CREATE TRIGGER statements that you saved. This will automatically recreate all of your indices and triggers. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Detecting if the db has changed
If concurrent access is by instances of your program only, they can rendezvous via a private table. Yes, but that involves modifying every single code path that could update any of the many tables I use to update the private table. The counter Richard mentioned is matches exactly what my requirements are, except not being accessible from SQL itself. Roger
Re: [sqlite] Changing table and security
You're altering tables you don't understand the structure of? You missed his point. When you use the steps in the FAQ to alter a table, one of the steps involves deleting it. When you do that SQLite also deletes all existing triggers and indices on the table. Consequently one piece of code that is simple (alter table) suddenly has to be aware of a lot more other things. It is all a simple matter of programming, but makes the code more coupled, complex and brittle. Roger
Re: [sqlite] Changing table and security
--- Paolo Vernazza <[EMAIL PROTECTED]> wrote: > > Tell your friends that they need to read the FAQ. > > http://www.sqlite.org/faq.html#q13 > > Sorry, Richard, but I think that it isn't so simple you must also > > store triggers and indexes... and maybe you don't know what's the > table > structure is, so you must parse youd table structure and modify it... You're altering tables you don't understand the structure of? = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Bug in subselect
This bug is a beauty: text column comparison in joins may follow NUMERIC
rules, even if
the column(s) have text affinity!
consider this case. Tables A,B are a typical one-to-many relation, where B
has N records
for each A, and one field (key) relates them.
CREATE TABLE a(
key VARCHAR(2),
data VARCHAR(30)
);
-- this table contains N records for each A, with period being 1..N
CREATE TABLE b(
key VARCHAR(2),
period INTEGER
);
insert into a values('01','data01');
insert into a values('+1','data+1');
insert into b values ('01',1);
insert into b values ('01',2);
insert into b values ('+1',3);
insert into b values ('+1',4);
select a.*,a1.*
from a,
(select key,sum(period) from b group by key) as a1
where a.key=a1.key
this select creates a temporary table because of the subselect, and then
joins the two.
the subselect, when run by itself, creates a table of two rows, as expected.
thus, one would
expect to get a result of two rows, like this:
01 data01 01 3
+1 data+1 +1 7
however, 4 rows are returned, like this :
01 data01 +1 7
01 data01 01 3
+1 data+1 +1 7
+1 data+1 01 3
the reason seems to be that '01' and '+1' are considered equal, something
which would be
correct IF column(s) 'key' were of numeric affinity, which they are NOT.
However, if the subselect is saved in a table, and the join is done after,
the correct result will be returned:
create table x as select key,sum(period) from b group by key
select a.*,x.*
from a,x
where a.key=x.key
01 data01 01 3
+1 data+1 +1 7
I suspect that this bug arises from the fact that the intermediate table
that is created due to
the subselect has no type information, for some reason.
I have created ticket #1047 on this.
RE: [sqlite] What's the difference of "select * from tb" and "sel ect "ID" from tb"?
I can verify this :
Select "columnName" from someTable gives columns with quoted names !
("ColumnName") instead of ColumnName
This *feels* like a bug
> -Original Message-
> From: red forks [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 22, 2004 6:07 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] What's the difference of "select * from
> tb" and "select "ID" from tb"?
>
> I'm using SQLite 3.08 for Windows, SQLite.Net.
> Sqlite3Explorer(developed by delphi) can also see this situation.
>
[sqlite] ODBC Driver
Hi, I've finally got round to building sqlite. Now i'm after the odbc driver for it. Only thing is I can't seem to find the page on the sqlite website that lists other programs, such as an odbc driver for use with sqlite? Is there a recommended odbc driver to use? Rgds, Dan
Re: [sqlite] Detecting if the db has changed
On Tue, Dec 21, 2004 at 07:58:46PM -0800, Roger Binns wrote: > Richard already answered. There is no mystery about who else is writing to > the db - it will be another instance of my program. If concurrent access is by instances of your program only, they can rendezvous via a private table. Cheers. -- Ng Pheng Siong <[EMAIL PROTECTED]> http://sandbox.rulemaker.net/ngps -+- M2Crypto, ZServerSSL for Zope, Blog http://www.sqlcrypt.com -+- Database Engine with Transparent AES Encryption
