Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote: > Most varints are "type varints" and type varints are almost always a > single byte (the only exceptions being for large blobs or strings). > Varints are also used to store the total number of bytes in a row > (also usually one byte). Most varints are a single byte.

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote: > Most varints are "type varints" and type varints are almost always a > single byte (the only exceptions being for large blobs or strings). > Varints are also used to store the total number of bytes in a row > (also usually one byte). Most varints are a single byte.

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread D. Richard Hipp
On Sep 3, 2009, at 8:25 PM, Mark Spiegel wrote: > D. Richard Hipp wrote: >> You are both right and both wrong. There are two different integer >> representations used in SQLite. >> >> (1) "varint" or variable length integer is an encoding of 64-bit >> signed integers into between 1 and 9 bytes.

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
D. Richard Hipp wrote: > You are both right and both wrong. There are two different integer > representations used in SQLite. > > (1) "varint" or variable length integer is an encoding of 64-bit > signed integers into between 1 and 9 bytes. Negative values use the > full 9 bytes as do large

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread D. Richard Hipp
On Sep 3, 2009, at 7:25 PM, Mark Spiegel wrote: > Jay A. Kreibich wrote: >> Integer values between -128 and 127 use only a single byte of >> storage above and beyond the header size that all values have. >> > Not quite. Values between 0 & 127 use 1 byte of storage. Negative > values use the

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Mark Spiegel
Jay A. Kreibich wrote: > Integer values between -128 and 127 use only a single byte of storage above > and beyond the header size that all values have. > Not quite. Values between 0 & 127 use 1 byte of storage. Negative values use the full 9 bytes in my experience. (I'm setting aside the

Re: [sqlite] shared library location on Linux question

2009-09-03 Thread Schrum, Allan
> Subject: [sqlite] shared library location on Linux question > > I'm trying to run a .NET program on Linux using MONO that makes use of > SQLite and nHibernate. I've got the managed code only > System.Data.SQLite.dll and use the sqlite3.dll on Vista and my program > and tests run fine. However,

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Jay A. Kreibich
On Thu, Sep 03, 2009 at 03:47:50PM -0400, Wilson, Ronald scratched on the wall: > > You can convert a V1 database into a V4 database by opening it, > > setting the legacy PRAGMA to false, and the VACUUMing the database. > If this is true, the documentation doesn't even hint at the feature: >

[sqlite] Surprisingly slow performance

2009-09-03 Thread James Turner
I'm having a performance problem with sqlite, where a particular select operation (involving a join) seems to be taking much longer than I'd expect (by an order of magnitude). I've been through several iterations of searching for information on the web, adjusting the table structures, and m

Re: [sqlite] SQLite 3.6.17

2009-09-03 Thread Nicolas Williams
On Thu, Sep 03, 2009 at 03:27:38PM +0100, Simon Slavin wrote: > On 3 Sep 2009, at 1:38am, P Kishor wrote: > > well, I think the problem is with the sqlite3 command line tool. > I agree. I just checked it with OS X 10.6, which comes with SQLite > version 3.6.12, and got the same problem: you can'

[sqlite] shared library location on Linux question

2009-09-03 Thread Daniel Napierski
I'm trying to run a .NET program on Linux using MONO that makes use of SQLite and nHibernate. I've got the managed code only System.Data.SQLite.dll and use the sqlite3.dll on Vista and my program and tests run fine. However, I don't know where to put the sqlite-3.6.17.so shared library, or if

Re: [sqlite] Defining user groups in SQLite table

2009-09-03 Thread Simon Slavin
On 3 Sep 2009, at 6:57pm, karenebs wrote: > I have a database table that holds about 20,000 codes. Each code > can be used > by several different user groups. I could add a column to the > database > table for each user group to indicate which codes that particular > group has > access to

Re: [sqlite] SQLite 3.6.17

2009-09-03 Thread Simon Slavin
On 3 Sep 2009, at 1:38am, P Kishor wrote: > well, I think the problem is with the sqlite3 command line tool. I agree. I just checked it with OS X 10.6, which comes with SQLite version 3.6.12, and got the same problem: you can't type accented characters into the sqlite3 tool. So I guess the

Re: [sqlite] confused about column affinity

2009-09-03 Thread Wilson, Ronald
> > The documentation refers to the column affinities TEXT, NUMERIC, > > INTEGER, REAL, and NONE, but NONE is not identified as a column > > affinity by the SQL parser, which treats it as NUMERIC. > > Column affinity is not part of the syntax - declared type is. The > affinity is inferred from the

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Wilson, Ronald
> Heh. But, actually, why doesn't SQLite3 produce an error when unknown > pragmas are used? Wouldn't that be the right thing to do? I would > thinks so. I think the docs say unknown pragmas are treated like no-ops. (yeah, I read the docs.) RW Ron Wilson, Engineering Project Lead (o) 434.455.

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Nicolas Williams
On Thu, Sep 03, 2009 at 04:21:28PM -0400, Wilson, Ronald wrote: > > Wrong pragma. Try: > > Thanks. I'm going to stop talking for a few days now. Enough gaffs for > one day. Heh. But, actually, why doesn't SQLite3 produce an error when unknown pragmas are used? Wouldn't that be the right thin

Re: [sqlite] confused about column affinity

2009-09-03 Thread Igor Tandetnik
Wilson, Ronald wrote: >>> Though, now I'm curious what your opinion on this is: >> >> I don't have an opinion on this. Should I? Is there a question in >> there somewhere? >> > The documentation refers to the column affinities TEXT, NUMERIC, > INTEGER, REAL, and NONE, but NONE is not identified as

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Wilson, Ronald
> Wrong pragma. Try: > > sqlite> pragma legacy_file_format; > 1 > sqlite> pragma legacy_file_format=0; > sqlite> pragma legacy_file_format; > 0 > sqlite> Thanks. I'm going to stop talking for a few days now. Enough gaffs for one day. Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m)

Re: [sqlite] confused about column affinity

2009-09-03 Thread Wilson, Ronald
> > Though, now I'm curious what your opinion on this is: > > I don't have an opinion on this. Should I? Is there a question in there > somewhere? > > Igor Tandetnik The documentation refers to the column affinities TEXT, NUMERIC, INTEGER, REAL, and NONE, but NONE is not identified as a column a

Re: [sqlite] confused about column affinity

2009-09-03 Thread Igor Tandetnik
Wilson, Ronald wrote: > Sigh, I thought I had read all the applicable sections. Thanks Igor. > > Though, now I'm curious what your opinion on this is: > >> SQLite version 3.6.10 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> create table none (i NONE);

Re: [sqlite] confused about column affinity

2009-09-03 Thread Wilson, Ronald
Yeah it seems confusing but once you get your head around it you're ok. The documentation is all there, and I'm sure there are a million threads on the archive about the same thing. If I had the time I'd write an 'affinity for dummies' section for the documentation. In the end, I think a lot

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Nicolas Williams
On Thu, Sep 03, 2009 at 03:57:14PM -0400, Wilson, Ronald wrote: > Hmm. I can't get the pragma to return a value at all. > > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> pragma default_file_format; Wrong pragma. Try: sqlite> pragm

Re: [sqlite] confused about column affinity

2009-09-03 Thread Pavel Ivanov
Hi, Ronald! You seem to mix terms "affinity", "data type" and "declared type of column" again. > The default affinity is NONE if left unspecified. Mixed statement which is right and wrong in some sense. If you look closer to 2.1 of cited document you'll see that the default affinity is NUMERIC (

Re: [sqlite] confused about column affinity

2009-09-03 Thread Wilson, Ronald
Sigh, I thought I had read all the applicable sections. Thanks Igor. Though, now I'm curious what your opinion on this is: > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table none (i NONE); > sqlite> insert into "none" valu

Re: [sqlite] confused about column affinity

2009-09-03 Thread D. Richard Hipp
On Sep 3, 2009, at 3:30 PM, Wilson, Ronald wrote: > > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .read sql.txt > create table "d" (i); > create table "n" (i numeric); > create table "i" (i integer); > insert into "d" values (1); >

Re: [sqlite] confused about column affinity

2009-09-03 Thread Igor Tandetnik
Wilson, Ronald wrote: > Creating a new thread for this topic... I'm confused by affinity. > Here is my munging of Pavel's examples from another thread: > > --- sql.txt --- > .echo on > create table "d" (i); > create table "n" (i numeric); > create table "i" (i integer); > insert into "d" values (1

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Wilson, Ronald
> I would test it, but "When the pragma is issued with no argument, it > returns the setting of the flag. This pragma does not tell which file > format the current database is using. It tells what format will be used > by any newly created databases." Hmm. I can't get the pragma to return a value

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Wilson, Ronald
> You can convert a V1 database into a V4 database by opening it, > setting the legacy PRAGMA to false, and the VACUUMing the database. > You can convert back in a similar way. In fact, be cautious of that. > If you have a build that defaults to V1, make sure you turn the > legacy PRAGMA

[sqlite] confused about column affinity

2009-09-03 Thread Wilson, Ronald
Creating a new thread for this topic... I'm confused by affinity. Here is my munging of Pavel's examples from another thread: --- sql.txt --- .echo on create table "d" (i); create table "n" (i numeric); create table "i" (i integer); insert into "d" values (1); insert into "n" values (1); insert

Re: [sqlite] SQLite to Access

2009-09-03 Thread Guy Hachlili
Well, Pighin, Ryan wrote: > Hi All - We have a new utility in our environment using SQLite and I was > wondering if there was a way to dump all the database into Access so we > can create reports on the databases? Why not attach the SQLite database into Access and create your reports? That way c

Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?

2009-09-03 Thread Igor Tandetnik
Atul_Vaidya wrote: > I want to make this query faster, for this, i created > indexes for Entity_Id Entity_Id doesn't appear anywhere in the query. How is an index on it supposed to help? > and grpuid Which of them? There are three tables, all having a column named grpuid. Run the que

Re: [sqlite] Defining user groups in SQLite table

2009-09-03 Thread Nicolas Williams
On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs wrote: > I have a database table that holds about 20,000 codes. Each code can be used > by several different user groups. I could add a column to the database > table for each user group to indicate which codes that particular group has > access

Re: [sqlite] SQLite to Access

2009-09-03 Thread Robert Citek
The ODBC connector for SQLite will allow you to connect Access to SQLite without having to import/export: http://www.ch-werner.de/sqliteodbc/ Regards, - Robert On Thu, Sep 3, 2009 at 2:33 PM, Pighin, Ryan wrote: > Hi All - We have a new utility in our environment using SQLite and I was > wonderi

[sqlite] SQLite to Access

2009-09-03 Thread Pighin, Ryan
Hi All - We have a new utility in our environment using SQLite and I was wondering if there was a way to dump all the database into Access so we can create reports on the databases? Thanks, Ryan Pighin ___ sqlite-users mailing list sqlite-use

Re: [sqlite] Defining user groups in SQLite table

2009-09-03 Thread Jay A. Kreibich
On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs scratched on the wall: > > I have a database table that holds about 20,000 codes. Each code can be used > by several different user groups. I could add a column to the database > table for each user group to indicate which codes that particular

Re: [sqlite] Defining user groups in SQLite table

2009-09-03 Thread Sebastian Bermudez
table groups ( group_id, group_descri ) table access ( rela_group, rela_code) --- El jue 3-sep-09, karenebs escribió: > De: karenebs > Asunto: [sqlite] Defining user groups in SQLite table > Para: sqlite-users@sqlite.org > Fecha: jueves, 3 de septiembre de 2009, 2:57 pm > > I have a databa

[sqlite] Defining user groups in SQLite table

2009-09-03 Thread karenebs
I have a database table that holds about 20,000 codes. Each code can be used by several different user groups. I could add a column to the database table for each user group to indicate which codes that particular group has access to. But, I have over 100 different groups! Can anyone suggest a

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread P Kishor
On Thu, Sep 3, 2009 at 10:30 AM, Jim Showalter wrote: > At the risk of throwing gasoline on a fire... > > I didn't react badly to Rod's original post. The original post was fine. It was the third post that was inflammatory. > Yes, it could have been > worded more diplomatically, but why so thin-s

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Jim Showalter
At the risk of throwing gasoline on a fire... I didn't react badly to Rod's original post. Yes, it could have been worded more diplomatically, but why so thin-skinned about it? He reported some issues. The response was that they weren't issues. Can you see how that might be construed as unhelpf

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread D. Richard Hipp
On Sep 3, 2009, at 10:43 AM, Rod Dav4is wrote: >*re applied affinity:* If that is what is meant, then the document > should say it, instead of leaving it to the reader's imagination. >Since column typing was superfluous in version2, it seems that the > version3 adoption of typing, as defi

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
Whoa! All I did was report two problems that I encountered when upgrading from version2 to version3! I was told that my problems were not problems at all; In fact one of them was a feature! That sounds kinda arrogant to me. As far as freezing the language, I made no such statement or r

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
*re applied affinity:* If that is what is meant, then the document should say it, instead of leaving it to the reader's imagination. Since column typing was superfluous in version2, it seems that the version3 adoption of typing, as defined, would perhaps be an upgrade compatibility issue

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Jean-Christophe Deschamps
Umm, At 05:16 03/09/2009, you wrote: ´¯¯¯ >Thanks for reminding me: A thing's value is generally proportional to >its cost. And the attitude of its support team figures in there, too. >-R. > > >> Whether _you_ consider them problems or not, they were certainly > >> problems for me, migrating a wor

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Pavel Ivanov
> Yes, I know how it works. But that seems to contradict the > documentation. The first field of the record inserted should have a type > of numeric, as types are associated with the data not with the column > declaration. So the phrase Where n = '1' should fall into the first > bullet case Rod, t

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
Pavel Ivanov wrote: >> Perhaps the fact that my column definitions declared no >> typing has an effect here? >> > > Yes, that means that your columns have no affinity, all data stored in > it as you give and no conversions done during insertions and > comparisons: > > sqlite> create table t (

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
Pavel Ivanov wrote: >> 2. *re integer vs string:* Version 3 differs from version 2 here. >> Version 2 was declared to be typeless and "Select Where column = >> 1" behaved identically with "Select Where column = '1'". Version >> 3 behavior is different in that the two previous exam

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Pavel Ivanov
> Perhaps the fact that my column definitions declared no > typing has an effect here? Yes, that means that your columns have no affinity, all data stored in it as you give and no conversions done during insertions and comparisons: sqlite> create table t (n, t); sqlite> insert into t values (1, '

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
Pavel Ivanov wrote: 2. *Quotes in SELECT*: Specification of Field='3' failed to find hits; Field=3 (i.e. without quotes) was required. >> This is a feature, not a bug. SQLite 3.x distinguishes between >> integers and strings and does not consider them equal to one an

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Pavel Ivanov
>>> 2. *Quotes in SELECT*: Specification of Field='3' failed to find >>> hits; Field=3 (i.e. without quotes) was required. > > This is a feature, not a bug. SQLite 3.x distinguishes between > integers and strings and does not consider them equal to one another. > You might have some rows wh

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Pavel Ivanov
> 2. *re integer vs string:* Version 3 differs from version 2 here. > Version 2 was declared to be typeless and "Select Where column = > 1" behaved identically with "Select Where column = '1'". Version > 3 behavior is different in that the two previous examples produce > diff

Re: [sqlite] Booleans in SQLite

2009-09-03 Thread Dan Bishop
Nicolas Williams wrote: > On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote: > >> I'm just curious how difficult it would be to add >> support for booleans in SQLite. This would most likely involve adding a >> new type affinity as well as adding "true" and

Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3

2009-09-03 Thread Rod Dav4is
1. *re OID vs ROWID:* I don't understand your statement that the "name" of a result column is undefined. The documentation clearly states "A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: "*ROWID*",

Re: [sqlite] Making Update fast

2009-09-03 Thread Kosenko Max
souvik.datta wrote: > Update set Flag=1 where Filename=; Check index presence on Filename column and still make updates in 1 large transaction. - Best Regards. Max Kosenko. -- View this message in context: http://www.nabble.com/Making-Update-fast-tp25269409p25273296.html Sent from the SQ

Re: [sqlite] Making Update fast

2009-09-03 Thread Atul_Vaidya
Did you tried using the pragmas refer sqlite documentation for pragmas http://www.sqlite.org/pragma.html Atul souvik.datta wrote: > > > > Update set Flag=1 where Filename=; > > The updates taking huge amount of time. I tried to wrap these updates > within transactions (50 updates within

Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?

2009-09-03 Thread Atul_Vaidya
Hi List, I want to make this query faster, for this, i created indexes for Entity_Id and grpuid and also used a pragmas : PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; PRAGMA count_changes = OFF; PRAGMA cache_size = 12000; I also do Begin Transaction