Re: [sqlite] Deep copy of 2 sqlite3*?

[Dan Kennedy]

On 08/03/2011 09:20 AM, Nikki Tan wrote:
> Hi sqlite-users!
>  It's just that I'm writing a copy constructor of my own mini sqlite
> wrapper, and I ended up having to do a deep copy of a sqlite3* points to a
> SQLite connection. And I'm just wondering is it okay that I just do it with
> memcpy(), by digging into the code for the definition of struct sqlite3  and
> count the total bytes of this struct?

Use sqlite3_open() to open a new database handle for the copy
of the wrapper class.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Case Insensitive String comparison

[arjabh say]

Hi All,

I have a SELECT query with WHERE-IN clause (string values are present in the
where-in clause)
I need this query to be case insensitive.

I checked with simply WHERE clause and in it, the query can be made
case-insensitive by adding COLLATE NOCASE, but this does not seem to work
with WHERE-IN.
Am I missing something??

For eg, If my table contains two values viz, 'Arjabh' and 'abc'
I want my query "SELECT name FROM table WHERE name IN ('arjabh','ABC')", to
return both the values.

Thanks and Regards,
Arjabh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deep copy of 2 sqlite3*?

[Nikki Tan]

Hi sqlite-users!
It's just that I'm writing a copy constructor of my own mini sqlite
wrapper, and I ended up having to do a deep copy of a sqlite3* points to a
SQLite connection. And I'm just wondering is it okay that I just do it with
memcpy(), by digging into the code for the definition of struct sqlite3  and
count the total bytes of this struct?
Any ideas or suggestions are appreciated!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Simon Slavin]

I just noticed that this is a FAQ:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float value increased as a text field

[Simon Slavin]

On 2 Aug 2011, at 5:52pm, Mathias Legerer wrote:

> Thank you for clarification.

If you need numbers accurate to the 1, use integers.  Thats what they're for.  
Business applications for big businesses don't store money as floating point, 
they store it as integers.  You get more than 17 decimal digits when storing 
integers in SQLite.  That should be enough.

> And yes, you are right with the guess, that the customer wants to
> preserve that much digits

I would love to know who came up with that requirement and why.  I assume that 
the value you quoted

>> 99104499464.0

was just for testing purposes.  That number is about 10^15.  That's a thousand 
times /more/ than there are stars in our galaxy, and there's no way to count 
those before some die and new ones are born.  There are only about a thousand 
that many grains of sand in the world, and by the time you'd counted them 
volcanoes would have fused some, and environmental shaping would have made 
others.

If you're restricting yourself to man-made items, there's only about US$100 
trillion in the entire world economy, including all currency and property owned 
by everyone everywhere from the value of my cat's fragment of sparkly tinsel 
and the gold reserve of national banks.  The number you're trying to track is 
ten times that number.  Your customer might be trying to keep track of every 
cent owned by everyone, but I doubt it.

Or perhaps your customer is a manufacturer.  A Boeing 747 has about six million 
parts, half of which are fasteners which have no function other than to attach 
two other parts together !  Add all the planes which have ever flown and all 
the cars ever manufactured together, and you still have less than 10^13 parts 
in total.  Even the biggest manufacturer doesn't need to keep track of that 
many.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jan Hudec]

On Tue, Aug 02, 2011 at 12:41:55 +, Black, Michael (IS) wrote:
> Since SQLite is type agnostic

Actually no, it's not. The optimizer usually does much better job if the
types are declared (I've seen many cases where it failed to use index when
some column was untyped). Plus there are unique constraints, foreign key
constraints and the very important 'integer primary key'.

> why don't you use generic field names?

You are not serious, are you? That's about the worst thing you could come up
with.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jay A. Kreibich]

On Tue, Aug 02, 2011 at 12:03:52PM +0100, Jack Hughes scratched on the wall:
> Hello all,
> 
> Is there a workaround for the lack of support for dropping fields?

  While this doesn't solve your immediate problem, I'm wondering if
  it might be possible to add code that simple marks a column invalid
  or unused, effectively making it "hidden."  Any constraints on that
  column would then be ignored (I know that's harder than it sounds).
  The next VACUUM could then clean up the data, completely deleting the
  column.

  How you deal with FKs, triggers, and indexes that are using those
  columns is left as an exercise for the coder.  8-)Humm... maybe
  that's why we don't have a ALTER TABLE...DROP COLUMN.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jan Hudec]

On Tue, Aug 02, 2011 at 12:03:52 +0100, Jack Hughes wrote:
> Any ideas how I can remove unused fields from the database would be 
> appreciated.

I'd consider creating the new database from scratch and importing the data
from the old one (by attaching it and doing insert ... select). That way
you'd only have one code for creating the schema and it would have
side-effect of defragmenting the database. It would of course take longer,
though.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float value increased as a text field

[Mathias Legerer]

Hi Richard,
Thank you for clarification.
And yes, you are right with the guess, that the customer wants to
preserve that much digits ;)
br,
Mathias

2011/8/2 Richard Hipp :
> On Tue, Aug 2, 2011 at 12:18 PM, Mathias Legerer
> wrote:
>
>> Hi,
>> I noticed a strange behaviour, when i have a table with a text-field
>> and i put a big float-number into it.
>> When stored, it is increased by one.
>>
>
> Floating-point numbers are approximations.  Always.  No exceptions.  SQLite
> promises to preserve 14 significant digits.  You're getting 15.5 significant
> digits.  Count your blessings.
>
> (1) Unless you are doing number theory or cryptography, you don't need that
> many significant digits.  No real quantity can be measured with that much
> accuracy.
>
> (2) If your boss or your customer insists that you preserve more digits (in
> spite of the fact that in any real-world application, anything after about
> the 5th or 6th digit is probably just noise), then use integers.
>
> FWIW, Linux preserves all 16 digits.  I suppose the difference is how winXP
> sets up the on-chip floating point hardware.
>
>
>>
>> create table TEST (ordnr int primary key, ktoid TEXT);
>> insert into TEST (ordnr, ktoid) values(0, 99104499464.0);
>> SELECT ordnr, ktoid FROM TEST;
>> 0|99104499465.0
>>
>> I believe, this is just because of some conversion-library.
>> Documenation says: "If numerical data is inserted into a column with
>> TEXT affinity it is converted into text form before being stored"
>>
>>  I thought that this would work without any problems, but it seems
>> there is some limit for floats/real as text if you look at this bigger
>> number:
>> insert into TEST (ordnr, ktoid) values(3, 9910449946222.0);
>> -> 3|9.9104499462e+16
>>
>> Is there some documentation about this limit?
>> Is it system-dependend?
>> I am working on WinXP 32Bit.
>>
>> Thank you for any information.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jay A. Kreibich]

On Tue, Aug 02, 2011 at 12:41:55PM +, Black, Michael (IS) scratched on the 
wall:
> Since SQLite is type agnostic why don't you use generic field names?

  Table definitions consist of a lot more than just column names and
  types.  Defining keys and other constraints are an integral part of
  table and database definition.  Many people also use CHECK typeof()
  constraints, making specific columns more strictly typed.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] float value increased as a text field

[Richard Hipp]

On Tue, Aug 2, 2011 at 12:18 PM, Mathias Legerer
wrote:

> Hi,
> I noticed a strange behaviour, when i have a table with a text-field
> and i put a big float-number into it.
> When stored, it is increased by one.
>

Floating-point numbers are approximations.  Always.  No exceptions.  SQLite
promises to preserve 14 significant digits.  You're getting 15.5 significant
digits.  Count your blessings.

(1) Unless you are doing number theory or cryptography, you don't need that
many significant digits.  No real quantity can be measured with that much
accuracy.

(2) If your boss or your customer insists that you preserve more digits (in
spite of the fact that in any real-world application, anything after about
the 5th or 6th digit is probably just noise), then use integers.

FWIW, Linux preserves all 16 digits.  I suppose the difference is how winXP
sets up the on-chip floating point hardware.


>
> create table TEST (ordnr int primary key, ktoid TEXT);
> insert into TEST (ordnr, ktoid) values(0, 99104499464.0);
> SELECT ordnr, ktoid FROM TEST;
> 0|99104499465.0
>
> I believe, this is just because of some conversion-library.
> Documenation says: "If numerical data is inserted into a column with
> TEXT affinity it is converted into text form before being stored"
>
>  I thought that this would work without any problems, but it seems
> there is some limit for floats/real as text if you look at this bigger
> number:
> insert into TEST (ordnr, ktoid) values(3, 9910449946222.0);
> -> 3|9.9104499462e+16
>
> Is there some documentation about this limit?
> Is it system-dependend?
> I am working on WinXP 32Bit.
>
> Thank you for any information.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] float value increased as a text field

[Mathias Legerer]

Hi,
I noticed a strange behaviour, when i have a table with a text-field
and i put a big float-number into it.
When stored, it is increased by one.

create table TEST (ordnr int primary key, ktoid TEXT);
insert into TEST (ordnr, ktoid) values(0, 99104499464.0);
SELECT ordnr, ktoid FROM TEST;
0|99104499465.0

I believe, this is just because of some conversion-library.
Documenation says: "If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored"

 I thought that this would work without any problems, but it seems
there is some limit for floats/real as text if you look at this bigger
number:
insert into TEST (ordnr, ktoid) values(3, 9910449946222.0);
-> 3|9.9104499462e+16

Is there some documentation about this limit?
Is it system-dependend?
I am working on WinXP 32Bit.

Thank you for any information.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[BareFeetWare]

On 02/08/2011, at 9:03 PM, Jack Hughes wrote:

> Is there a workaround for the lack of support for dropping fields?

As others have said, you can create a new table and insert data from the old to 
new table. Remember to also recreate any needed triggers and indexes. For 
example, I get my SQLite management app to write all the necessary SQL when the 
user changes, adds or deletes a column from a table or view, as shown here:
http://www.barefeetware.com/databare/trace.html

> I have an application and need to evolve the database schema as features are 
> added and removed. Leaving fields sitting inside the database that are no 
> longer used will lead to a schema that is difficult to understand. Especially 
> so when the field is marked as NOT NULL. Years from now I will need to supply 
> a value for a field when inserting a row that has long ago ceased to be used.

If your app has a dedicated purpose, such as a contact database but is not a 
general purpose database management app, then you probably shouldn't be 
redefining your schema columns anyway. Can you give some more specific detail 
on what you're doing here, such as some sample schema changes?

For a dedicated purpose app/database, you should probably be instead adding or 
removing rows or tables as you add or remove features. For instance, if you 
have a contact database that includes Person and a MySpace contact, but later 
decide not to have MySpace but you want Facebook contact, there are three ways 
to do this:

1. Add and remove a column. This is probably the worst way:

old table:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
,   "MySpace" text collate nocase
)
;

change:

begin immediate
;
create temp table "Person Cache" as select * from "Person"
;
drop table "Person"
;
create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
,   "Facebook" text collate nocase
)
;
insert into "Person" (ID, "Name First", "Name Last", "Email")
select ID, "Name First", "Name Last", "Email" from "Person Cache"
;
drop table temp."Person Cache"
;
commit
;

2. Or, add and remove rows. Probably the best way, if it fits your needs

schema:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
)
;
create table "Service"
(   ID integer primary key not null
,   Name text collate nocase unique not null
)
;
insert into "Service" (Name) values ('MySpace')
;
create table "Person Service"
(   ID integer primary key not null
,   "Person" integer not null references "Person" (ID) on delete cascade
,   "Service" integer not null references "Person" (ID) on delete cascade
,   Name text collate nocase not null
)
;

change:

delete from "Service" where name = 'MySpace'
;
insert into "Service" (Name) values ('Facebook')
;
insert into "Person Service" ("Person", "Service", Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   (select ID from "Service" where Name = 'Facebook')
,   'mickeymouse'
;

3. Or, add and remove related tables. If method 2 doesn't fit your needs.

schema:

create table "Person"
(   ID integer primary key not null
,   "Name First" text collate nocase
,   "Name Last" text collate nocase
,   "Email" text collate nocase
)
;
create table "Person MySpace"
(   ID integer primary key not null references "Person" (ID) on delete 
cascade
,   Name text collate nocase not null
)
;

change:

drop table "Person MySpace"
;
create table "Person Facebook"
(   ID integer primary key not null references "Person" (ID) on delete 
cascade
,   Name text collate nocase not null
)
;
insert into "Person Facebook" (ID, Name)
(select ID from "Person" where "Email" = 'mic...@disney.com')
,   'mickeymouse'
;

I hope that helps,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 32-bit to 64-bit on Snow Leopard

[Viaduct Productions]

Ya the Lasso bit is about their own admin and session management anyway, so 
that's fine.

Not worried, as I can get it back to original.  I keep accounts minimal as I'm 
running off an SSD which has limited space.  

Thanks again.  

Cheers


_
Rich in Toronto

On 2011-08-02, at 9:05 AM, Simon Slavin wrote:

> On 2 Aug 2011, at 1:52pm, Viaduct Productions wrote:
> 
>> ya 3 it is.  A package installed the 32 bit version, and now I have to 
>> install the 64 bit version, and I'm worried it might do irreparable things 
>> to my current tables.  I'm thinking tables are independent of the binaries, 
>> so I'm good, but I wanted to check.  
> 
> Right.  As long as they're all SQLite3 you're fine.  (A little handwaving 
> about some bugs you're very unlikely to trigger in old versions of SQLite.)
> 
>> How do I leave the OS X entries alone?  Ruby needs 64 bit SQLite, so I have 
>> to make sure it can see the new installation.  This is what was recommended:
>> 
>> CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64' 
>> ./configure --disable-dependency-tracking
> 
> That command-line is not a problem.  It specifies compilation options but not 
> any directories where files will end up.  The only question is where you put 
> your files.  Just put your new files in new directories you make, not replace 
> anything that already exists in /usr.
> 
> If you're at all worried about this, do your development (including the 
> compilation) using an account which isn't an administration account. Then it 
> won't have the privileges to do anything bad.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 32-bit to 64-bit on Snow Leopard

[Simon Slavin]

On 2 Aug 2011, at 1:52pm, Viaduct Productions wrote:

> ya 3 it is.  A package installed the 32 bit version, and now I have to 
> install the 64 bit version, and I'm worried it might do irreparable things to 
> my current tables.  I'm thinking tables are independent of the binaries, so 
> I'm good, but I wanted to check.  

Right.  As long as they're all SQLite3 you're fine.  (A little handwaving about 
some bugs you're very unlikely to trigger in old versions of SQLite.)

> How do I leave the OS X entries alone?  Ruby needs 64 bit SQLite, so I have 
> to make sure it can see the new installation.  This is what was recommended:
> 
> CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64' 
> ./configure --disable-dependency-tracking

That command-line is not a problem.  It specifies compilation options but not 
any directories where files will end up.  The only question is where you put 
your files.  Just put your new files in new directories you make, not replace 
anything that already exists in /usr.

If you're at all worried about this, do your development (including the 
compilation) using an account which isn't an administration account.  Then it 
won't have the privileges to do anything bad.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jack Hughes]

I don't think NHibernate would allow me do do that... even if it did it would 
be difficult to understand. Things are hard enough to understand when I name 
the fields as per their intention never mind when there would be a level of 
indirection above it.


>Since SQLite is type agnostic why don't you use generic field names?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 32-bit to 64-bit on Snow Leopard

[Viaduct Productions]

Hi Simon  Thanks for the reply.

ya 3 it is.  A package installed the 32 bit version, and now I have to install 
the 64 bit version, and I'm worried it might do irreparable things to my 
current tables.  I'm thinking tables are independent of the binaries, so I'm 
good, but I wanted to check.  

How do I leave the OS X entries alone?  Ruby needs 64 bit SQLite, so I have to 
make sure it can see the new installation.  This is what was recommended:

CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64' ./configure 
--disable-dependency-tracking

Thanks again.  

Cheers

_
Rich in Toronto

On 2011-08-02, at 6:31 AM, Simon Slavin wrote:

> As long as you stay with SQLite3, not SQLite2, The format of the files is 
> completely unchanged.  You can create a database with one version, make 
> changes with another, then switch to a third one and query the data.
> 
> By the way, in case you were considering this, do /not/ replace the SQLite 
> libraries/frameworks/headerfiles supplied with OS X.  Don't mess with 
> /usr/bin or /usr/lib.  That version is used by many System components and 
> changing it might make them malfunction.  Make your own directory wherever 
> you want, put your 64-bit version in there, and use that for your own 
> programming and editing.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Black, Michael (IS)]

Since SQLite is type agnostic why don't you use generic field names?



Just name your fields 0-NN and keep a set of defines for field names.



Then you just use #define to name the fields.



create table mytable (field1,field2,field3,field4);



#define NAME "field1"

#define ADDR "field2"

#define CITY "field3"

#define ZIP "field4"



All you need to do is put quotes around the field names (note: it's actually 
just separate strings but putting quotes around the field names is the visual 
effect and easier to think about).

char *sql = "select "NAME" from mytable where "ZIP" like '3%'";



sql will then contain:

select field1 from mytable where field4 like '3%'



Dropping fields from your defines will automatically give you syntax errors 
during compilation as long as you don't name your new fields the same as ones 
you just deleted (can you say long-term maintenance?).





This is something you can't do with a strongly-typed database.







Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jack Hughes [jhug...@openxtra.co.uk]
Sent: Tuesday, August 02, 2011 6:03 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Field drop work around

Hello all,

Is there a workaround for the lack of support for dropping fields? I have an 
application and need to evolve the database schema as features are added and 
removed. Leaving fields sitting inside the database that are no longer used 
will lead to a schema that is difficult to understand. Especially so when the 
field is marked as NOT NULL. Years from now I will need to supply a value for a 
field when inserting a row that has long ago ceased to be used.

Any ideas how I can remove unused fields from the database would be appreciated.

Regards,

Jack Hughes

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Stephan Beal]

On Tue, Aug 2, 2011 at 1:13 PM, Simon Slavin  wrote:

> copy your data across using INSERT INTO myTable SELECT a,b,c,d FROM
> myOldTable
> DROP the old TABLE
>

or, similarly: CREATE TABLE myTable AS SELECT ... FROM myOldTable;

Though there might be subtle differences between the two which i'm not aware
of.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Jack Hughes]

Thank you for your response Simon + Teg.

The absence of the full ALTER table support turns something that would have 
been as simple as writing "Delete.Column("ColumnName").FromTable("TableName");" 
in c# using the fluent migrator project into something that is far from simple 
and probably quite error prone.

I'm sure there are good reasons not to support the full ALTER table syntax but 
in cases like these it does make life a lot more difficult.

Regards,
Jack

>> Any ideas how I can remove unused fields from the database would be 
>> appreciated.

>Only by doing it manually:
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wal index file size

[sreekumar . tp]

Hello,

Is there a relation between the wal index file size and the wal log file ? How 
does sqlite allocate the max size for the index file. Could figure out scanning 
thru the source code. However, if someone has already figured out, please share.

Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Where to put the SLITE_OMIT_* options?

[Richard Hipp]

On Tue, Aug 2, 2011 at 4:54 AM, Baruch Burstein wrote:

> I am trying to edit the makefile, as mentioned here
> http://www.sqlite.org/compile.html#omitfeatures. I am unclear as to where
> these options should go in the makefile, as these are not strictly compile
> options, as no compiling is done (the end result is a source file). Where
> would I put them?
>

http://www.sqlite.org/src/artifact/91d710bdc49?ln=61


> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3_step problem

[Stephan Beal]

On Tue, Aug 2, 2011 at 12:49 PM, Prasanth R Kosigi Shroff <
prasanth@globaledgesoft.com> wrote:

> i am trying to run a compiled query and i am passing it through
> sqlite3_step API the return value is SQLITE_DONE. But the same query
> works fine when i execute the same query using command line tool.
> ...what could be the reason
>

Can you show us the C code? Without that we can only guess.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Teg]

Hello Jack,

I just migrate the tables forward.  Generate a new one in my program
and do a bulk insert from the old to the new, then drop the old one.
At other times, I've just re-purposed the unused field.

C

Tuesday, August 2, 2011, 7:03:52 AM, you wrote:

JH> Hello all,

JH> Is there a workaround for the lack of support for dropping
JH> fields? I have an application and need to evolve the database
JH> schema as features are added and removed. Leaving fields sitting
JH> inside the database that are no longer used will lead to a schema
JH> that is difficult to understand. Especially so when the field is
JH> marked as NOT NULL. Years from now I will need to supply a value
JH> for a field when inserting a row that has long ago ceased to be used.

JH> Any ideas how I can remove unused fields from the database would be 
appreciated.

JH> Regards,

JH> Jack Hughes

JH> ___
JH> sqlite-users mailing list
JH> sqlite-users@sqlite.org
JH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Field drop work around

[Simon Slavin]

On 2 Aug 2011, at 12:03pm, Jack Hughes wrote:

> Is there a workaround for the lack of support for dropping fields? I have an 
> application and need to evolve the database schema as features are added and 
> removed. Leaving fields sitting inside the database that are no longer used 
> will lead to a schema that is difficult to understand. Especially so when the 
> field is marked as NOT NULL. Years from now I will need to supply a value for 
> a field when inserting a row that has long ago ceased to be used.
> 
> Any ideas how I can remove unused fields from the database would be 
> appreciated.

Only by doing it manually:

rename your old TABLE using ALTER TABLE ... RENAME TO ...
create a new TABLE with just the columns you want, in the order you want them
copy your data across using INSERT INTO myTable SELECT a,b,c,d FROM myOldTable
DROP the old TABLE
CREATE appropriate INDEXes for the new table

It's just four commands plus one for each index you want, though I know it's 
tedious to have to do it like that rather than all in one operation.

Note that having TRIGGERS or FOREIGN KEYS defined complicates this.  And always 
take a backup before starting anything like this !

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite3_step problem

[Prasanth R Kosigi Shroff]

hi

i am trying to run a compiled query and i am passing it through 
sqlite3_step API the return value is SQLITE_DONE. But the same query 
works fine when i execute the same query using command line tool.

#sqlite3 tmp.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>select name from (select * from some_tbl order by length(name), 
name) where ((length(name) > length('MANGO')) or (length(name) == 
length('MANGO') and (name > 'MANGO'))) limit 1;
name = TANGO

what could be the reason

Regards,
Prasanth K S R
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Field drop work around

[Jack Hughes]

Hello all,

Is there a workaround for the lack of support for dropping fields? I have an 
application and need to evolve the database schema as features are added and 
removed. Leaving fields sitting inside the database that are no longer used 
will lead to a schema that is difficult to understand. Especially so when the 
field is marked as NOT NULL. Years from now I will need to supply a value for a 
field when inserting a row that has long ago ceased to be used.

Any ideas how I can remove unused fields from the database would be appreciated.

Regards,

Jack Hughes

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug!)

[Simon Slavin]

On 2 Aug 2011, at 9:06am, Igor Sereda wrote:

> Thanks, Simon - it's a bug then. Hope it will get pulled into the bug
> tracker.

> Simon Slavin-3 wrote:
> 
>> The spec for '<=' should say that comparing any number with NULL always
>> gives a NULL result.  If SQLite is doing anything apart from that, it's a
>> bug.
>> 
>> Okay, here it is: SQL92 8.2 (1) (a):
>> 
>> "If XV or YV is the null value, then "X  Y" is unknown."
>> 
>> In this context, returning 'unknown' means returning NULL.

You know, I might have to withdraw the last part of that.  The SQL92 spec talks 
about truth functions having three possible answers: FALSE, TRUE, and UNKNOWN.  
It treats UNKNOWN as a reserved word in the same way as it treats TRUE, 
contains statements like

"If "R   T" is neither true nor false,
  then it is unknown."

and the truth tables in section 8.12 give results for TRUE, FALSE, and UNKNOWN. 
 And nowhere can I find any statement that says UNKNOWN means NULL.  And I can 
a distinction between the two: NULL means "I don't have that datum" whereas 
UNKNOWN means "I can't work that out".

But I don't think SQLite implements UNKNOWN.  So we need someone intimately 
familiar with the guts of SQLite, which I'm not.

Either way, I don't think it should be possible to come up with a well-ordering 
system that includes NULL, numbers and strings and stays within the SQL 
standard.  The big question is whether any of the big users of SQLite is 
depending on this behaviour.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 32-bit to 64-bit on Snow Leopard

[Simon Slavin]

On 2 Aug 2011, at 6:09am, Viaduct Productions wrote:

> Just wondering if the tables will remain if I configure SQLite to install the 
> 64 bit version on Snow Leopard over my current 32 bit version.  

As long as you stay with SQLite3, not SQLite2, The format of the files is 
completely unchanged.  You can create a database with one version, make changes 
with another, then switch to a third one and query the data.

By the way, in case you were considering this, do /not/ replace the SQLite 
libraries/frameworks/headerfiles supplied with OS X.  Don't mess with /usr/bin 
or /usr/lib.  That version is used by many System components and changing it 
might make them malfunction.  Make your own directory wherever you want, put 
your 64-bit version in there, and use that for your own programming and editing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Where to put the SLITE_OMIT_* options?

[Baruch Burstein]

I am trying to edit the makefile, as mentioned here
http://www.sqlite.org/compile.html#omitfeatures. I am unclear as to where
these options should go in the makefile, as these are not strictly compile
options, as no compiling is done (the end result is a source file). Where
would I put them?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug!)

[Igor Sereda]

Thanks, Simon - it's a bug then. Hope it will get pulled into the bug
tracker.

Igor



Simon Slavin-3 wrote:
> 
> 
> On 2 Aug 2011, at 1:10am, Igor Sereda wrote:
> 
>> To my humble knowledge, operations with NULL have well-defined semantics,
>> both in SQL-you-name-it standards and in SQLite. "A < B" may have three
>> results - TRUE, FALSE and NULL. It doesn't matter whether you can make
>> any
>> sense of it - it's the spec ;)
> 
> The spec for '<=' should say that comparing any number with NULL always
> gives a NULL result.  If SQLite is doing anything apart from that, it's a
> bug.
> 
> Okay, here it is: SQL92 8.2 (1) (a):
> 
> "If XV or YV is the null value, then "X  Y" is unknown."
> 
> In this context, returning 'unknown' means returning NULL.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Virtual-Table-xBestIndex-and-NULL-Search-Conditions-%28Bug-%29-tp32172549p32175828.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/8/2 Eric Scouten :
> It falls apart badly in a highly distributed environment where ...
>
> ...

May be a RDF storage is more reasonable for this. Operations with
atomic facts can be highly distributed. And SPARQL is similar to SQL.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Java

[Martin Engelschalk]

Yin,

A Google search of "sqlite java api" gives several good hits.

Click on the first http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers 
and scroll down to Java

Martin

Am 02.08.2011 06:01, schrieb yinlijie2011:
> Dear,
>  I want use SQLite, but my program language is Java. And 
> thewww.sqlite.org not supply API for Java. What should I do?
>  Thank you!
>
>  Yin Lijie
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users