Re: [sqlite] Booleans in SQLite

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

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

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

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

Re: [sqlite] Booleans in SQLite

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

Re: [sqlite] Booleans in SQLite

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

Re: [sqlite] shared library location on Linux question

> 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.

Re: [sqlite] Booleans in SQLite

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

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

Re: [sqlite] SQLite 3.6.17

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

[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, 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

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

Re: [sqlite] SQLite 3.6.17

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

Re: [sqlite] confused about column affinity

> > 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

Re: [sqlite] Booleans in SQLite

> 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)

Re: [sqlite] Booleans in SQLite

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

Re: [sqlite] confused about column affinity

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

Re: [sqlite] Booleans in SQLite

> 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

> > 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

Re: [sqlite] confused about column affinity

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

Re: [sqlite] confused about column affinity

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

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>

Re: [sqlite] confused about column affinity

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

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"

Re: [sqlite] confused about column affinity

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

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); >

Re: [sqlite] Booleans in SQLite

> 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

Re: [sqlite] Booleans in SQLite

> 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

[sqlite] confused about column affinity

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

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

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

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

Re: [sqlite] Defining user groups in SQLite table

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

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

[sqlite] SQLite to Access

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

Re: [sqlite] Defining user groups in SQLite table

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

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:

[sqlite] Defining user groups in SQLite table

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

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

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

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

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

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

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

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

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

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

*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

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

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

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

> 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,

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

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

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

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

> 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

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

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

>>> 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

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

> 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 >

Re: [sqlite] Booleans in SQLite

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

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:

Re: [sqlite] Making Update fast

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

Re: [sqlite] Making Update fast

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

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

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