Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit

[Prashant Shah]

On Wed, May 22, 2013 at 7:53 PM, Stephan Beal  wrote:
> make -f GNUmakefile.linux

libsqlite4.a(fts5func.o): In function `fts5Rank':
/home/user/db/build/sqlite4/src/fts5func.c:159: undefined reference to `log'
collect2: ld returned 1 exit status
make: *** [sqlite4] Error 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite.net] DateTime exception when reading

[James K. Lowden]

On Wed, 22 May 2013 12:23:12 -0700
"Kevin Keigwin"  wrote:

> What I don't understand is why the database is so forgiving of dates
> being saved, while the SQLiteDataAdapter isn't.

As Simon said, "SQLite doesn't have a DateTime type."  As far as SQLite
is concerned, your datestring is a string, not a date.  You have the
option to use a CHECK constraint to restrict the column to valid date
strings.  

HTH.  

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

Re: [sqlite] Julian days in Sqlite

[Joe Mistachkin]

Simon Slavin wrote:
>
> Perhaps you might want to take the source code for the existing julian
> date function and modify it.
>

If the system in question is online, perhaps the following URL (or one
like it) could be fetched periodically and used to help synchronize said
custom extension:

http://maia.usno.navy.mil/ser7/tai-utc.dat 

--
Joe Mistachkin

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

Re: [sqlite] Julian days in Sqlite

[Igor Tandetnik]

On 5/22/2013 6:48 PM, Stephan Buchert wrote:

It seems that I failed to make the point clear:

On June 30, 2012 the 86401st second happened in the real world


I think you mean "a second was artificially added by some, but not all 
or even most, people to some, but not all or even most, calendar 
calculations". I'm pretty sure no singularity point occurred in 
space-time continuum on that date.



it was
working time in parts of the US. In this second there were financial
transactions, photos were taken, sensors delivered data, etc etc, events
that users might want to insert into databases with a correct time stamp,
and where the seconds might matter.


Most of those transactions, photos, and sensor data were reported by 
devices that were blissfully unaware of leap seconds, and never produced 
any timestamps with seconds outside of 00-59 range. At best, some of 
those devices, e.g. GPS receivers, might have noticed that their clock 
was a bit off, and adjusted it accordingly.


Leap seconds simply don't arise in practice, outside perhaps of a few 
specialized applications (astronomical calculations and such) - as 
witnessed by the pronounced lack of sympathy you have experienced in 
this thread.

--
Igor Tandetnik

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

Re: [sqlite] Julian days in Sqlite

[Simon Slavin]

On 22 May 2013, at 11:48pm, Stephan Buchert  wrote:

> However, Sqlite's julianday seems to have limitations because it returns
> NULL for times that fall in leap seconds (in addition, it is off by
> presently about 1 min from the Julian Day that is used in astronomy).
> Perhaps the reason is that leap second times cannot be represented with a
> syntactically valid time string, per
> http://www.sqlite.org/lang_datefunc.html, but it really doesn't matter much
> what the reason is. I'm suggesting, that Sqlite should have a function that
> for the convenience of users returns a monotonically increasing numeric
> timestamp for all practical times in the real world, including the leap
> seconds.

We understand what you want.  However, I think the demand for the time 
conversion function you want is so low it's not worth adding it to SQLite which 
is designed to be a tiny system, just big enough for most users' requirements.

Although your requirements are technically part of the definition of Julian 
Days, there are no facilities in the common operating systems (Unix timestamps, 
Windows, etc.) that would let SQLite know which days have leap seconds in.  
SQLite would have to include its own little table of leap seconds and that kind 
of detail is beyond even the big SQL engines.  The table would have to be 
modified from time to time as new leap seconds are declared.  There might be 
issues relating to time zones I don't know about.

"As represented in seconds since the Epoch, each and every day shall be 
accounted for by exactly 86400 seconds." -- The Open Group, Single Unix 
Specification

As far as I remember the only software I've ever written that had to worry 
about leap seconds was in GPS position calculations, in which an extra second 
leads to significant errors.  Even scientific logging software usually ignores 
the issue.

If the function is important to you, presumably for astronomy or some other 
scientific software, you may choose to implement your desired function yourself 
as a SQLite extension:



Perhaps you might want to take the source code for the existing julian date 
function and modify it.

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

Re: [sqlite] Julian days in Sqlite

[Stephan Buchert]

It seems that I failed to make the point clear:

On June 30, 2012 the 86401st second happened in the real world, it was
working time in parts of the US. In this second there were financial
transactions, photos were taken, sensors delivered data, etc etc, events
that users might want to insert into databases with a correct time stamp,
and where the seconds might matter. This is of course possible, for
example, by using a string like  '2012-06-30T23:59:60', or by using
separate columns for year, month, day, hour, min, sec, or by constructing a
custom numeric time stamp counting the really elapsed seconds and fractions
of seconds from some epoch, an example is the astronomical Julian Day.
However, Sqlite's julianday seems to have limitations because it returns
NULL for times that fall in leap seconds (in addition, it is off by
presently about 1 min from the Julian Day that is used in astronomy).
Perhaps the reason is that leap second times cannot be represented with a
syntactically valid time string, per
http://www.sqlite.org/lang_datefunc.html, but it really doesn't matter much
what the reason is. I'm suggesting, that Sqlite should have a function that
for the convenience of users returns a monotonically increasing numeric
timestamp for all practical times in the real world, including the leap
seconds.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 7:06 PM,   wrote:
>
> (I think Fehmi diagnosed the problem, that you should not use the "sqlite3" 
> command when you are already in the "sqlite3" program.  This is about 
> something completely different.)
>
> If you are using a recent version of Windows, you do not want to be trying to 
> create your database in the c:\windows\system32 folder. (In Windows Vista or 
> later, you need administrative privileges to write to any file in that 
> folder.)
>
> Once SQLite3.exe is installed in the system32 folder, you do not need it to 
> be the current folder to be able to run it (because the system32 folder is in 
> the path).
>
> If you use "Start / Run / cmd" to get to a Command Prompt, you can do 
> something like this:
>
> md c:\mydb
> cd c:\mydb
> sqlite3 test.db
>
> so that your data file is not in the system32 folder. (You only need the "md" 
> command the first time, to create the directory.)  Once this has been done, 
> you should be able to do
>
> Start / Run / sqlite3 c:\mydb\test.db
>
> rather than beginning at a Command Prompt.

Yes, that worked correctly.

Thanks for the help.

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

Re: [sqlite] Getting Started with Sqlite

[j . merrill]

(I think Fehmi diagnosed the problem, that you should not use the "sqlite3" 
command when you are already in the "sqlite3" program.  This is about something 
completely different.)
 
If you are using a recent version of Windows, you do not want to be trying to 
create your database in the c:\windows\system32 folder. (In Windows Vista or 
later, you need administrative privileges to write to any file in that folder.)
 
Once SQLite3.exe is installed in the system32 folder, you do not need it to be 
the current folder to be able to run it (because the system32 folder is in the 
path).
 
If you use "Start / Run / cmd" to get to a Command Prompt, you can do something 
like this:
 
md c:\mydb
cd c:\mydb
sqlite3 test.db
 
so that your data file is not in the system32 folder. (You only need the "md" 
command the first time, to create the directory.)  Once this has been done, you 
should be able to do
 
Start / Run / sqlite3 c:\mydb\test.db
 
rather than beginning at a Command Prompt.
 
---
Date: Wed, 22 May 2013 11:22:22 -0300

From: Sean Dzafovic 
To: Fehmi Noyan ISI ,  General Discussion of
 SQLite Database 
Subject: Re: [sqlite] Getting Started with Sqlite
Message-ID:
 
Content-Type: text/plain; charset=ISO-8859-1

On Wed, May 22, 2013 at 11:17 AM, Fehmi Noyan ISI  wrote:
> Oh my gosh! you are in business man...
[snip]

All right. I was doing the command from the shell and not the command
line. I will try that later but I have to go offline.

I think that may have been my problem though. Thanks for your help.
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Klaas V]

Dear fellow SQLite afficionados,

  Thanumalayan Sankaranarayana Pillai  wrote:
  "I expect it wouldn't be a problem with WAL"
Thé SQLite (not wanting, but cobsidering him at leat  kind of) Force D. Richard 
H.  [who does not know Him don't read this message, you won;t understand enough 
I'm afraid]  answered to   
  There are no configuration options set wrong.  You shouldn't ever have to
set configuration options in order to get SQLite to work right.  I think
instead you are just running up against fundamental limits of modern
consumer-grade hardware.  I don't think there is anything anybody can do
about it" I'd say as a fellow consumer of a wee little bit different hardware 
as well as software
"asking has some kind of answering included sometimes"  I admit since it's not 
my native lingo in imperfect English, I'm brave and arrogant enough to 
encourage mr. Pillai to download and move to a directory within her or his PATH 
the brand new/recommended RVU(P) 3.17.0(±130520) ASAP (i.e. As Soon As Possible 
[just in the very unlikely case y'all did not know yet this geek speak]
To make a long story shorter: Use WAL to open not only yer eyes, but possibly 
you guys's (and gal's) mind as well. Hopefully it's not (yet) banned in your 
part of this wondrtful globe.
 
Just my not extremely humble opinion. Thanks a million for paying eventual 
attention, friends (and 'why not' foes),
 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
kla...@innocentisart.eu

P.S.In bocca al lupo, tutti!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Julian days in Sqlite

[Igor Tandetnik]

On 5/22/2013 5:28 PM, Stephan Buchert wrote:

Sqlite's julianday in the leap second seems to be NULL:

sqlite> select julianday('2012-06-30T23:59:60')*86400;


So is julianday('foobar'). '2012-06-30T23:59:60' is simply not a 
syntactically valid time string, per 
http://www.sqlite.org/lang_datefunc.html



For comparison, the mktime function of the standard library in my computer
(CET) returns
1341093599, 1341093600, 1341093600 for the
times 2012-06-30T23:59:59, 2012-06-30T23:59:60, and 2012-07-31T00:00:00,
respectively.


mktime also accepts 2012-06-30T23:59:61, 2012-06-30T23:59:62 and so on, 
as well as, say, 2012-15-35T00:00:00. This has nothing to do with leap 
seconds, and everything to do with the fact that mktime accepts values 
out of range and automatically normalizes them.

--
Igor Tandetnik

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

Re: [sqlite] Using "COLLATE nocase" with BETWEEN

[Yongil Jang]

Thank you, igor.
I've learned one more, today!
2013. 5. 22. 오후 10:20에 "Igor Tandetnik" 님이 작성:

> On 5/22/2013 12:53 AM, Yongil Jang wrote:
>
>> But, in case of using BETWEEN operator with "COLLATE nocase", it returns
>> unexpected result as follows.
>>
>> sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
>> nocase;
>>
>
> Make it
>
> where data COLLATE nocase between 'abc/' and 'ABC0'
>
> The expression "x between a and b" behaves the same as "x >= a AND x <=
> b". In your example, you end up with
>
> data >= 'abc/' and data <= 'ABC0' COLLATE nocase;
>
> The two comparisons use different collations.
> --
> Igor Tandetnik
>
> __**_
> 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] Julian days in Sqlite

[Stephan Buchert]

Yes, the documentation gives there a hint that also the Sqlite time
functions have the usual problems with the leap seconds.

A specific example:

sqlite> select julianday('2012-06-30T23:59:59')*86400;
212207860799.0

The up to now most recent leap second then was from 2012-06-30T23:59:60
to 2012-06-30T23:59:60.999 ..., i.e. June 30, 2012 had 86401 seconds.
Sqlite's julianday in the leap second seems to be NULL:

sqlite> select julianday('2012-06-30T23:59:60')*86400;

sqlite> select julianday('2012-07-01T00:00:00')*86400;
212207860800.0

In reality two seconds elapsed between 2012-06-30T23:59:59
and 2012-07-01T00:00:00.

For comparison, the mktime function of the standard library in my computer
(CET) returns
1341093599, 1341093600, 1341093600 for the
times 2012-06-30T23:59:59, 2012-06-30T23:59:60, and 2012-07-31T00:00:00,
respectively. Neither correct, but perhaps better than Sqlite's NULL for
the leap second time.

It has been suggested to stop inserting leap seconds in order to avoid
these problems with computerized time stamps. The committees in charge have
deferred a decision on the leap seconds until 2015, so likely there will be
at least 1-2 more of them.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite.net] DateTime exception when reading

[Simon Slavin]

On 22 May 2013, at 8:23pm, "Kevin Keigwin"  wrote:

> And I've discovered the source of the problem by downloading the source code
> and debugging the SQLiteDataAdapter.  I had created some test data through
> the GUI tool and entered a non-ISO8601 date string.  Because SQLite is so
> forgiving when it comes to datatypes, it inserted the value even though it's
> not valid.  But when I tried to read it through SQLite.NET, it threw an
> exception.

Glad you sorted it.  Well done.

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

Re: [sqlite] [SQLite.net] DateTime exception when reading

[Kevin Keigwin]

>Can you try the other stuff I wrote ?  We don't yet have enough information
to figure out what's going wrong.

>Simon.

Sorry, Simon.  I missed what else you wrote because it was embedded in my
original email text.  My error.

And I've discovered the source of the problem by downloading the source code
and debugging the SQLiteDataAdapter.  I had created some test data through
the GUI tool and entered a non-ISO8601 date string.  Because SQLite is so
forgiving when it comes to datatypes, it inserted the value even though it's
not valid.  But when I tried to read it through SQLite.NET, it threw an
exception.

What I don't understand is why the database is so forgiving of dates being
saved, while the SQLiteDataAdapter isn't.

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

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Yeah, the index maintenance is enormous, but it means that the select times are 
fast.  Medical info systems are usually pretty heavily oriented toward fast 
read operations.

Our table here has a set of repeated substructures (8-column structures that we 
have 6 of, with certain fields used on each, since the type isn't fully 
repeating.)  However, we use the whole subset of fields the vast majority of 
times we pull it, and so it's much faster to pull it out of the flat structure 
into our internal structure instead of doing the top select, then selecting all 
the subtypes.  The reverse, on insert, is true as well.  One insert is better 
than a master insert and then blowing away a bunch of subtable rows and then 
re-inserting.

We're changing our data model generation tool to handle all of the indexes on 
our schema, and to make sure to output all of those first in the table 
generation order.  That'll keep us under 63 for now...

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I was just thinking of that... if you have needs to dozens of covering indexes, 
then the index maintenance anytime you modify the table must be enormous. It 
makes me think you might be better off using triggers to maintain separate 
tables with covered data instead of indexes.  The only downside to that, I 
suppose, is that you have to pick the right table when doing the select.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 3:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] Julian days in Sqlite

[Keith Medcalf]

> The time available in computers (smartphones etc) is normally UTC and a
> local time offset, which is sufficient for almost all times, but not
> quite all. On average every 18 months leap seconds are inserted. Events
> during these leap seconds cannot be unambiguously timestamped by a
> representation of time based on UTC. According to the IAU the Julian
> day, JD, should be calculated from a date/time presentations using TT
> (terrestrial time) rather than UTC. TT is presently 67.184 s ahead of
> UTC, this changes with time, especially with every inserted leap second.

The "time" is GMT (UT1).  UTC has leap seconds to correct UTC to GMT (UT1).  
Computers (smartphones etc) all use minutes which have exactly sixty seconds 
and are syncronized to the Mean Observed Solar Zenith at Grenwich.  Just 
because a cutesy name (UTC) replaces the correct name (GMT), does not mean that 
the time (or how it is calculated and used) is at all different because of it.

> 1) do nothing, or maybe just document more clearly, that the julianday
> in Sqlite, for date/time input in UTC, returns JD(UTC) in IAU
> terminology, which, for example, does give time differences (or nr of
> days elapsed since epoch) ignoring leap seconds and is off by about
> 1 minute with respect to astronomical JD.

SQLite calculates the Julian Date from the time returned by the Operating 
System.  Except for very special platforms, this is UT1 (GMT).  Almost no 
computer system actually uses UTC -- in fact, UTC is a "display format" only.  
Some people "call" it UTC, but it is not.  It is Mean Observed Solar Time at 
Grenwich.  Each minute contains 60 seconds, each hour contains 60 minutes, and 
so forth.  Leap Seconds only exist to correct the artificial UTC periodically 
to UT1 (or Mean Observed Solar Time at Grenwich).

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I haven't had a table that large, but I have had big ones... the disadvantage 
is the number of records you can scan in a single disk read, but an advantage 
is that you don't have to take the time to join tables, especially when you 
need to do it ALL THE TIME.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Wednesday, May 22, 2013 3:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?


On 22 May 2013, at 7:58pm, David de Regt  wrote:

>  if I have a 300 column table

I'm going to sound my customary note of caution here.  Do you really have a 300 
column table or is it several thinner tables which have the same primary key ?  
Or do you really have a property list which should be one thinner table with a 
two-column primary key ?

Generally in database design you should be able to hold a table schema in your 
head.  When you find yourself numbering columns it's usually a sign you're 
doing something wrong.

Not true in every case, of course, and you may have one of the incredibly rare 
cases which really is best represented with a 300 column table.  In which case, 
please excuse me.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I was just thinking of that... if you have needs to dozens of covering indexes, 
then the index maintenance anytime you modify the table must be enormous. It 
makes me think you might be better off using triggers to maintain separate 
tables with covered data instead of indexes.  The only downside to that, I 
suppose, is that you have to pick the right table when doing the select.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 3:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 

Re: [sqlite] Max of 63 columns for a covering index to work?

[Simon Slavin]

On 22 May 2013, at 7:58pm, David de Regt  wrote:

>  if I have a 300 column table

I'm going to sound my customary note of caution here.  Do you really have a 300 
column table or is it several thinner tables which have the same primary key ?  
Or do you really have a property list which should be one thinner table with a 
two-column primary key ?

Generally in database design you should be able to hold a table schema in your 
head.  When you find yourself numbering columns it's usually a sign you're 
doing something wrong.

Not true in every case, of course, and you may have one of the incredibly rare 
cases which really is best represented with a 300 column table.  In which case, 
please excuse me.

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

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
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] Max of 63 columns for a covering index to work?

[Petite Abeille]

On May 22, 2013, at 8:58 PM, David de Regt  wrote:

> Back to the trenches to rearchitect this…

Perhaps an opportunity to introduce bitmap indexes to SQLite… which would 
render compound indexes a thing of the past for certain class of problems such 
as yours...

http://en.wikipedia.org/wiki/Bitmap_index

Looking forward to it :D
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[Richard Hipp]

On Wed, May 22, 2013 at 3:01 PM, Marc L. Allen
wrote:

> I think there might be a disconnect.
>
> You can have a covering index on a 300 column table... it just can't cover
> any column past the 63rd (or 64th?).
>

63rd.  The 64th bit is catch-all used to mean that some column past the
63rd is used.


>
> It's not perfect, but not as bad as not being able to have a covering
> index at all.
>
> At least, that's how I read some of the answers.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of David de Regt
> Sent: Wednesday, May 22, 2013 2:59 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Max of 63 columns for a covering index to work?
>
> Hm.  That's a wee bit of an issue for us, then.  May want to stick that on
> the limitations page... :)
>
> It seems like covering indexes become increasingly useful the more columns
> you have on a table.  When I have a 4-column table, if my covering index
> uses 3 columns, that's not as big a read savings as if I have a 300 column
> table that I only need to handle 3 columns from in a WHERE, and it
> otherwise needs to pull the row/page from the original table to get the
> value on.
>
> Back to the trenches to rearchitect this...
>
> Thanks for the quick clarification. :)
>
> -David
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Wednesday, May 22, 2013 11:53 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Max of 63 columns for a covering index to work?
>
> On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:
>
> > I'm experimenting with covering indices on one of our larger tables.
> >
> > *[many words expressing concern that SQLlite does not use covering
> > indices on tables with more than 63 colums]...*
> >
> >
> Your observations are correct.  If a query uses any column of a table past
> the 63rd column, then that query cannot use a covering index on that table.
>  This is due to the use of 64-bit unsigned integer bitmasks to keep track
> of which columns have been used in order to discover whether or not a
> covering index will work.
>
> --
> 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
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> 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] [SQLite.net] DateTime exception when reading

[Simon Slavin]

On 22 May 2013, at 7:59pm, Kevin Keigwin  wrote:

> I understand.

Can you try the other stuff I wrote ?  We don't yet have enough information to 
figure out what's going wrong.

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

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite.net] DateTime exception when reading

[Kevin Keigwin]

>SQLite doesn't have a DateTime type.  If you are using some sort of library
which does type testing and produces an error if the value is of >the wrong
type, it will always produce an error message when checking for DateTime.

>Simon.

I understand.  As I said in my post, the underlying type of these columns is
text.  However, the SQLiteDataAdapter class tags the columns in the
DataTable as type DateTime when the FillSchema() method is called.  Even
when I alter this to make the columns of type String, a subsequent call to
Fill() generates the error.  Whatever is happening is inside the
SQLiteDataAdapter.

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

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

--
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] Max of 63 columns for a covering index to work?

[Richard Hipp]

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past
the 63rd column, then that query cannot use a covering index on that
table.  This is due to the use of 64-bit unsigned integer bitmasks to keep
track of which columns have been used in order to discover whether or not a
covering index will work.

-- 
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] Max of 63 columns for a covering index to work?

[David de Regt]

I'm experimenting with covering indices on one of our larger tables.  I started 
seeing really inconsistent behavior, and made the following sample setup code 
that demonstrates it:

DROP TABLE IF EXISTS test;

CREATE TABLE test(
col01 integer,col02 integer,col03 integer,col04 integer,col05 integer,col06 
integer,col07 integer,col08 integer,col09 integer,col10 integer,
col11 integer,col12 integer,col13 integer,col14 integer,col15 integer,col16 
integer,col17 integer,col18 integer,col19 integer,col20 integer,
col21 integer,col22 integer,col23 integer,col24 integer,col25 integer,col26 
integer,col27 integer,col28 integer,col29 integer,col30 integer,
col31 integer,col32 integer,col33 integer,col34 integer,col35 integer,col36 
integer,col37 integer,col38 integer,col39 integer,col40 integer,
col41 integer,col42 integer,col43 integer,col44 integer,col45 integer,col46 
integer,col47 integer,col48 integer,col49 integer,col50 integer,
col51 integer,col52 integer,col53 integer,col54 integer,col55 integer,col56 
integer,col57 integer,col58 integer,col59 integer,col60 integer,
col61 integer,col62 integer,col63 integer,col64 integer,col65 integer,col66 
integer,col67 integer,col68 integer,col69 integer,col70 integer
);

CREATE INDEX test1 ON test(col01,col02,col03);
CREATE INDEX test2 ON test(col01,col02,col63);
CREATE INDEX test3 ON test(col62,col63,col64);
CREATE INDEX test4 ON test(col64,col65,col66);
CREATE INDEX test5 ON test(col66,col67,col10);

--With that initial setup, here's a pile of sample EXPLAIN QUERY PLANs and 
their result, as to whether the covering index works:

EXPLAIN QUERY PLAN
SELECT SUM(col03) FROM test WHERE col01=0 AND col02=1;
--test1 COVERING

EXPLAIN QUERY PLAN
SELECT SUM(col63) FROM test WHERE col01=0 AND col02=1;
--test2 COVERING

EXPLAIN QUERY PLAN
SELECT col02,col63 FROM test WHERE col01=0;
--test2 COVERING

EXPLAIN QUERY PLAN
SELECT SUM(col64) FROM test WHERE col62=0 AND col63=1;
--test3 NONCOVERING

EXPLAIN QUERY PLAN
SELECT col63 FROM test WHERE col62=0;
--test3 COVERING

EXPLAIN QUERY PLAN
SELECT col63,col64 FROM test WHERE col62=0;
--test3 NONCOVERING

EXPLAIN QUERY PLAN
SELECT SUM(col66) FROM test WHERE col64=0 AND col65=1;
--test4 NONCOVERING

EXPLAIN QUERY PLAN
SELECT SUM(col10) FROM test WHERE col66=0 AND col67=1;
--test5 NONCOVERING

EXPLAIN QUERY PLAN
SELECT col67,col10 FROM test WHERE col66=0;
--test5 NONCOVERING

Help? :)

We'd really rather keep our table denormed, as we really do pull and use all 
>64 columns of data with every query, but if this is just a limitation, then 
we'll have to figure out a solution.  It's not listed anywhere on the limits 
page, though, so I'm wondering if this needs to be added, or if I'm just doing 
something stupid or something. :)

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

Re: [sqlite] [SQLite.net] DateTime exception when reading

[Simon Slavin]

On 22 May 2013, at 5:47pm, "Kevin Keigwin"  wrote:

> When our code tries to execute "SELECT * FROM  ORDER by
> .id", I get the exception "String was not recognized as a valid
> DateTime".

Use your code to populate your database but don't worry about a SELECT command.

Download the sqlite3 shell tool and try some select commands for the your 
database in that.  Try something simple with a fixed tablename like

SELECT * FROM myTable

Does it work ?

Can you prove that your code is correctly executing any statement at all ?

> I've even tried modifying the code to change all DateTime columns in the
> DataTable to type String before the SELECT is executed, but it continues to
> complain about invalid DateTimes.

SQLite doesn't have a DateTime type.  If you are using some sort of library 
which does type testing and produces an error if the value is of the wrong 
type, it will always produce an error message when checking for DateTime.

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

[sqlite] [SQLite.net] DateTime exception when reading

[Kevin Keigwin]

I've been given the task of creating an adapter for our software to read
existing SQLite/spatialite data tables.  I am using the latest version of
SQLite.net, which I obtained from the precompiled x86 binaries for .NET 4.0.
When our code tries to execute "SELECT * FROM  ORDER by
.id", I get the exception "String was not recognized as a valid
DateTime".

 

The underlying table has three columns that store datetimes, all of type
text.  Our infrastructure first creates an empty datatable based on the
database schema using 

 

DataAdapter.FillSchema(p_data_table, SchemaType.Source)

 

The data adapter has the SELECT command set on it, then is populated using 

 

DataAdapter.Fill(p_data_table);

 

I've even tried modifying the code to change all DateTime columns in the
DataTable to type String before the SELECT is executed, but it continues to
complain about invalid DateTimes.

 

I've seen many posts elsewhere on this, but no answers that work.  Any help
would be greatly appreciated.

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

Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit

[Stephan Beal]

On Wed, May 22, 2013 at 4:13 PM, Prashant Shah wrote:

> How can I compile sqlite4 ? Is there a amalgamation file available ?
>

Try:

make -f GNUmakefile.linux
or:
ln -s GNUmakefile.linux GNUmakefile
make

That "should" do it for you.

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

Re: [sqlite] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 11:17 AM, Fehmi Noyan ISI  wrote:
> Oh my gosh! you are in business man...
>
> You run the sqlite without any problems, don't you? Have a look at the 
> step-by-step cmd.exe example below
> As other guys pointed out in their previous posts, you supposed to use 
> "sqlite3 test.db" command from the Windows Command line. Not inside the 
> sqlite shell.
>

All right. I was doing the command from the shell and not the command
line. I will try that later but I have to go offline.

I think that may have been my problem though. Thanks for your help.


>
> C:\Users\fnoyanisi>d:
> D:\>cd dev
>
> D:\Dev>dir
>
>  Volume in drive D is Data
>  Volume Serial Number is 918C-D840
>
>  Directory of D:\Dev
>
> 05/22/2013  11:35 PM  .
> 05/22/2013  11:35 PM  ..
> 05/22/2013  11:35 PM  asd
> 11/13/2012  07:14 PM  fltk
> 02/07/2013  08:18 PM  mingw
> 02/06/2013  07:57 PM  msys
> 03/22/2013  11:28 PM  myLib
> 02/17/2013  06:25 PM  pugixml
> 03/13/2013  06:33 PM  python2.7.1
> 11/16/2012  11:32 PM  sqlite3
>0 File(s)  0 bytes
>   10 Dir(s)   3,550,380,032 bytes free
>
> D:\Dev>cd sqlite3
> D:\Dev\sqlite3>dir
>
>  Volume in drive D is Data
>  Volume Serial Number is 918C-D840
>
>  Directory of D:\Dev\sqlite3
>
> 11/16/2012  11:32 PM  .
> 11/16/2012  11:32 PM  ..
> 10/05/2012  05:19 AM96,378 shell.c
> 11/16/2012  11:32 PM 4,813,228 sqlite3.c
> 11/12/2012  10:34 PM   510,464 sqlite3.dll
> 11/12/2012  10:35 PM49,152 sqlite3.exe
> 10/05/2012  05:19 AM   340,171 sqlite3.h
> 10/05/2012  05:19 AM24,158 sqlite3ext.h
>6 File(s)  5,833,551 bytes
>2 Dir(s)   3,550,380,032 bytes free
>
> D:\Dev\sqlite3>sqlite3.exe test.db
> SQLite version 3.7.14.1 2012-10-04 19:37:12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
>
>
> 
>  From: Sean Dzafovic 
> To: General Discussion of SQLite Database 
> Sent: Wednesday, May 22, 2013 11:38 PM
> Subject: Re: [sqlite] Getting Started with Sqlite
>
>
> On Wed, May 22, 2013 at 10:59 AM,   wrote:
>> The dll and exe should be sufficient to run sqlite.
>>
>> What version of sqlite are you using?
>> sqlite3 --version
>>
>> Not sure about this, but may it be something related to user privilages?
>
> When I start the shell i see
>
> SQLite version 3.7.17 2013-05-20 00:56:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
>
> If I use the cmd line i get the path above the SQLite version
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[Donald Griggs]

Regarding:   Either way when I open the shell it gives me a sqlite> prompt,
not sqlite3>
   Yes, I suspect Igor intended
sqlite>

But to his larger point,  am I close to correct in replicating your error
below?   I strongly suspect you're typing "sqlite3" when you are already
inside the sqlite3 program and getting the sqlite> prompt.

C:\Users\ad>sqlite3
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> sqlite3 test.db
   ...> ;
Error: near "sqlite3": syntax error
sqlite>


The error comes after giving sqlite a semicolon to tell the system you are
finally done with a command.

So be sure that when you get to the windows command line and in your
desired directory, your first command should be:
sqlite3   test.db

Then you should get an sqlite prompt, and should *already* be working in
your new database named test.db
sqlite>

Sorry to waste bits if your problem lies elsewhere,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compiling sqlite4 on ubuntu 12.04 64 bit

[Prashant Shah]

Hi,

Also, how do I build a .so file ?

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

Re: [sqlite] Getting Started with Sqlite

[Richard Hipp]

On Wed, May 22, 2013 at 10:16 AM, Sean Dzafovic  wrote:

>
> I only downloaded 2 files. The Sqlite shell and the Sqlite .dll listed
> under precompiled binaries for windows on the download page. I did not
> compile anything. I moved the .dll to the system32 folder and tried to
> run the shell from both the desktop and the system32 folder (both by
> clicking the icon and using the command line).
>
> Same error in all cases.
>
> Did I miss a step?
>
>
You added a step.  The DLL is not necessary.  The sqlite3.exe file is
statically linked.

That said, downloading the DLL too should be harmless.  You must be doing
something else wrong.  I cannot imagine what that is, though.


-- 
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] Getting Started with Sqlite

[Fehmi Noyan ISI]

Oh my gosh! you are in business man...

You run the sqlite without any problems, don't you? Have a look at the 
step-by-step cmd.exe example below
As other guys pointed out in their previous posts, you supposed to use "sqlite3 
test.db" command from the Windows Command line. Not inside the sqlite shell.


C:\Users\fnoyanisi>d:
D:\>cd dev

D:\Dev>dir

 Volume in drive D is Data
 Volume Serial Number is 918C-D840

 Directory of D:\Dev

05/22/2013  11:35 PM              .
05/22/2013  11:35 PM              ..
05/22/2013  11:35 PM              asd
11/13/2012  07:14 PM              fltk
02/07/2013  08:18 PM              mingw
02/06/2013  07:57 PM              msys
03/22/2013  11:28 PM              myLib
02/17/2013  06:25 PM              pugixml
03/13/2013  06:33 PM              python2.7.1
11/16/2012  11:32 PM              sqlite3
               0 File(s)              0 bytes
              10 Dir(s)   3,550,380,032 bytes free

D:\Dev>cd sqlite3
D:\Dev\sqlite3>dir

 Volume in drive D is Data
 Volume Serial Number is 918C-D840

 Directory of D:\Dev\sqlite3

11/16/2012  11:32 PM              .
11/16/2012  11:32 PM              ..
10/05/2012  05:19 AM            96,378 shell.c
11/16/2012  11:32 PM         4,813,228 sqlite3.c
11/12/2012  10:34 PM           510,464 sqlite3.dll
11/12/2012  10:35 PM            49,152 sqlite3.exe
10/05/2012  05:19 AM           340,171 sqlite3.h
10/05/2012  05:19 AM            24,158 sqlite3ext.h
               6 File(s)      5,833,551 bytes
               2 Dir(s)   3,550,380,032 bytes free

D:\Dev\sqlite3>sqlite3.exe test.db
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>



 From: Sean Dzafovic 
To: General Discussion of SQLite Database  
Sent: Wednesday, May 22, 2013 11:38 PM
Subject: Re: [sqlite] Getting Started with Sqlite
 

On Wed, May 22, 2013 at 10:59 AM,   wrote:
> The dll and exe should be sufficient to run sqlite.
>
> What version of sqlite are you using?
> sqlite3 --version
>
> Not sure about this, but may it be something related to user privilages?

When I start the shell i see

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If I use the cmd line i get the path above the SQLite version
___
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] Julian days in Sqlite

[Richard Hipp]

On Wed, May 22, 2013 at 10:05 AM, Stephan Buchert wrote:

> 1) do nothing, or maybe just document more clearly, that the julianday
> in Sqlite, for date/time input in UTC, returns JD(UTC) in IAU
> terminology, which, for example, does give time differences (or nr of
> days elapsed since epoch) ignoring leap seconds and is off by about
> 1 minute with respect to astronomical JD.
>

The final two sentences of the documentation (
http://www.sqlite.org/lang_datefunc.html), under the "Caveats and Bugs"
heading, says as much: specifically that SQLite assumes that every day is
exactly 86400 seconds long.

-- 
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] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 11:07 AM, Fehmi Noyan ISI  wrote:
> I think it depends on how you compiled sqlite. I my case, it is dynamically 
> linked to dll, so I do need the dll file (with a smaller exe size). However, 
> a static link seems suites better the case for sqlite (how big can its file 
> size be!?)

I am obviously doing something wrong then.

I only downloaded 2 files. The Sqlite shell and the Sqlite .dll listed
under precompiled binaries for windows on the download page. I did not
compile anything. I moved the .dll to the system32 folder and tried to
run the shell from both the desktop and the system32 folder (both by
clicking the icon and using the command line).

Same error in all cases.

Did I miss a step?

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

[sqlite] compiling sqlite4 on ubuntu 12.04 64 bit

[Prashant Shah]

Hi,

How can I compile sqlite4 ? Is there a amalgamation file available ?

I tried downloading the fossil package :

$fossil clone http://www.sqlite.org/src4/sqlite4.fossil sqlite4

$ ./autogen.sh
configure.in: warning: missing AC_CHECK_FUNCS([fdatasync]) wanted by:
src/lsm_unix.c:181
configure.in: warning: missing AC_CHECK_FUNCS([ftruncate]) wanted by:
src/lsm_unix.c:138
configure.in: warning: missing AC_CHECK_FUNCS([getcwd]) wanted by:
src/lsm_unix.c:251
configure.in: warning: missing AC_CHECK_FUNCS([gettimeofday]) wanted
by: src/os.c:39
configure.in: warning: missing AC_CHECK_FUNCS([localtime_r]) wanted
by: src/date.c:450
configure.in: warning: missing AC_CHECK_FUNCS([memmove]) wanted by:
src/utf.c:399
configure.in: warning: missing AC_CHECK_FUNCS([memset]) wanted by: src/utf.c:473
configure.in: warning: missing AC_CHECK_FUNCS([munmap]) wanted by:
src/lsm_unix.c:211
configure.in: warning: missing AC_CHECK_FUNCS([select]) wanted by:
src/parse.y:169
configure.in: warning: missing AC_CHECK_FUNCS([strchr]) wanted by:
tool/lemon.c:1739
configure.in: warning: missing AC_CHECK_FUNCS([strcspn]) wanted by:
ext/fts1/simple_tokenizer.c:131
configure.in: warning: missing AC_CHECK_FUNCS([strdup]) wanted by:
test/test_thread0.c:68
configure.in: warning: missing AC_CHECK_FUNCS([strerror]) wanted by:
lsm-test/lsmtest_main.c:985
configure.in: warning: missing AC_CHECK_FUNCS([strrchr]) wanted by:
tool/lemon.c:2720
configure.in: warning: missing AC_CHECK_FUNCS([strtol]) wanted by:
src/shell.c:2162
configure.in: warning: missing AC_CHECK_HEADERS([fcntl.h]) wanted by:
src/lsm_file.c:154
configure.in: warning: missing AC_CHECK_HEADERS([malloc.h]) wanted by:
src/mem1.c:96
configure.in: warning: missing AC_CHECK_HEADERS([stddef.h]) wanted by:
src/sqliteInt.h:304
configure.in: warning: missing AC_CHECK_HEADERS([sys/time.h]) wanted
by: src/os.c:21
configure.in: warning: missing AC_FUNC_FORK wanted by: test/crashtest1.c:84
configure.in: warning: missing AC_FUNC_MALLOC wanted by: src/shell.c:320
configure.in: warning: missing AC_FUNC_MMAP wanted by: src/lsm_unix.c:227
configure.in: warning: missing AC_FUNC_REALLOC wanted by: src/shell.c:326
configure.in: warning: missing AC_FUNC_STRTOD wanted by: tool/lemon.c:1765
configure.in: warning: missing AC_HEADER_STDBOOL wanted by: src/mem1.c:238
configure.in: warning: missing AC_PREREQ wanted by: autoscan
configure.in: warning: missing AC_PROG_MAKE_SET wanted by: tool/warnings.sh:7
configure.in: warning: missing AC_TYPE_INT32_T wanted by:
ext/fts2/fts2_icu.c:105
configure.in: warning: missing AC_TYPE_OFF_T wanted by: src/lsm_unix.c:56
configure.in: warning: missing AC_TYPE_SIZE_T wanted by: src/lsm_str.c:98
configure.in: warning: missing AC_TYPE_SSIZE_T wanted by: src/lsm_unix.c:120
configure.in: warning: missing AC_TYPE_UID_T wanted by: src/shell.c:2435
configure.in: warning: missing AC_TYPE_UINT8_T wanted by: ext/icu/icu.c:69

$./configure
configure: creating ./config.status
config.status: creating Makefile
config.status: creating config.h

$make
.c
src/mem2.c: In function ‘sqlite4MemsysGetHeader’:
src/mem2.c:155:7: warning: variable ‘pU8’ set but not used
[-Wunused-but-set-variable]
src/mem2.c:154:8: warning: variable ‘pInt’ set but not used
[-Wunused-but-set-variable]
src/mem2.c: In function ‘sqlite4MemSetDefault’:
src/mem2.c:374:7: error: ‘sqlite4_env’ has no member named ‘m’
src/mem2.c: In function ‘sqlite4MemdebugSetType’:
src/mem2.c:381:29: error: ‘struct sqlite4_env’ has no member named ‘m’
src/mem2.c: In function ‘sqlite4MemdebugHasType’:
src/mem2.c:400:29: error: ‘struct sqlite4_env’ has no member named ‘m’
src/mem2.c: In function ‘sqlite4MemdebugNoType’:
src/mem2.c:422:29: error: ‘struct sqlite4_env’ has no member named ‘m’
make: *** [mem2.o] Error 1

After removing the #if from the src/sqlitInt.h it compiles, but is
this the right way ?

2413 #if 0
2414   sqlite4_mem_methods m;/* Low-level memory
allocation interface */
2415 #endif
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 10:59 AM,   wrote:
> The dll and exe should be sufficient to run sqlite.
>
> What version of sqlite are you using?
> sqlite3 --version
>
> Not sure about this, but may it be something related to user privilages?

When I start the shell i see

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If I use the cmd line i get the path above the SQLite version
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[Fehmi Noyan ISI]

I think it depends on how you compiled sqlite. I my case, it is dynamically 
linked to dll, so I do need the dll file (with a smaller exe size). However, a 
static link seems suites better the case for sqlite (how big can its file size 
be!?)



 From: Adam DeVita 
To: General Discussion of SQLite Database  
Sent: Wednesday, May 22, 2013 11:31 PM
Subject: Re: [sqlite] Getting Started with Sqlite
 

When you open the command prompt you will see something like this:
c:\PINTS>sqlite3.exe
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

The above clearly indicates version 3.6.10.

Others on the list will correct me if I am wrong, but the command line
utility does not use the DLL.

If the path to the exe file is in your windows PATH environment
variable folder you will be able to execute it.  I normally start it
from a command prompt (Start->run "cmd")

regards,
Adam




On Wed, May 22, 2013 at 9:51 AM, Sean Dzafovic  wrote:
> On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik  wrote:
>> On 5/22/2013 8:58 AM, Sean Dzafovic wrote:
>>>
>>> I downloaded the shell and the dll from the sqlite.org site. I put the
>>> .dll in the windows/system32 folder. However, when I try to create a
>>> test db using the command "sqlite3 test.db" as per the example, I get
>>> "Error: near "sqlite3" :syntax error.
>>>
>>> What am I doing wrong?
>>
>>
>> You are running this command on sqlite3 command line (do you see "sqlite3>"
>> prompt?) Instead, you should run this command on Windows command line (Start
>>> Run > cmd), in order to start sqlite3 shell with a given DB file as a
>> parameter.
>
> Tried it with the shell on my desktop and got the error.
> Moved it to the system32 folder along with the .dll and got the same
> error. (Both of these by clicking on the icon).
>
> Same when I tried using the Windows command line.
>
> Either way when I open the shell it gives me a sqlite> prompt, not sqlite3>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
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

[sqlite] Julian days in Sqlite

[Stephan Buchert]

Sqlite provides in its SQL the julianday function, which can serve as a
timestamp with a good resolution (millisecs, double precision
floating point numbers are used) and for times way back into the past
and far into the future. However, as presently implemented, the
function does not really return the Julian day as it is recommended by
the IAU (International Astronomical Union). 

The time available in computers (smartphones etc) is normally UTC and a
local time offset, which is sufficient for almost all times, but not
quite all. On average every 18 months leap seconds are inserted. Events
during these leap seconds cannot be unambiguously timestamped by a
representation of time based on UTC. According to the IAU the Julian
day, JD, should be calculated from a date/time presentations using TT
(terrestrial time) rather than UTC. TT is presently 67.184 s ahead of
UTC, this changes with time, especially with every inserted leap second.

What could be done about this in Sqlite:

1) do nothing, or maybe just document more clearly, that the julianday
in Sqlite, for date/time input in UTC, returns JD(UTC) in IAU
terminology, which, for example, does give time differences (or nr of
days elapsed since epoch) ignoring leap seconds and is off by about
1 minute with respect to astronomical JD.

2) introduce (an) additional function(s) that return an also in leap
seconds increasing time stamp based on TAI (temps atomic
international)/TT. Because the underlying OSs normally don't provide the
necessary information, sqlite would have to keep a list of the so far
25 inserted leap seconds, and it would need to be upgraded for every
future leap second going to be inserted. The SOFA library backed by
the IAU (http://www.iausofa.org) uses this approach with
respect to handling the leap seconds. GPS related software packages are
doing it similarly.

Sqlite would, AFAIK, be the first general database supporting a correct
handling of timestamps with respect to leap seconds, if something like
option 2) above is adopted. I would think it would be nice to have at
least in Sqlite4.

Stephan

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

Re: [sqlite] Getting Started with Sqlite

[Adam DeVita]

When you open the command prompt you will see something like this:
c:\PINTS>sqlite3.exe
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

The above clearly indicates version 3.6.10.

Others on the list will correct me if I am wrong, but the command line
utility does not use the DLL.

If the path to the exe file is in your windows PATH environment
variable folder you will be able to execute it.  I normally start it
from a command prompt (Start->run "cmd")

regards,
Adam




On Wed, May 22, 2013 at 9:51 AM, Sean Dzafovic  wrote:
> On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik  wrote:
>> On 5/22/2013 8:58 AM, Sean Dzafovic wrote:
>>>
>>> I downloaded the shell and the dll from the sqlite.org site. I put the
>>> .dll in the windows/system32 folder. However, when I try to create a
>>> test db using the command "sqlite3 test.db" as per the example, I get
>>> "Error: near "sqlite3" :syntax error.
>>>
>>> What am I doing wrong?
>>
>>
>> You are running this command on sqlite3 command line (do you see "sqlite3>"
>> prompt?) Instead, you should run this command on Windows command line (Start
>>> Run > cmd), in order to start sqlite3 shell with a given DB file as a
>> parameter.
>
> Tried it with the shell on my desktop and got the error.
> Moved it to the system32 folder along with the .dll and got the same
> error. (Both of these by clicking on the icon).
>
> Same when I tried using the Windows command line.
>
> Either way when I open the shell it gives me a sqlite> prompt, not sqlite3>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Started with Sqlite

[fnoyanisi]

The dll and exe should be sufficient to run sqlite.

What version of sqlite are you using?
sqlite3 --version

Not sure about this, but may it be something related to user privilages?

On 22/05/2013, at 11:21 PM, Sean Dzafovic  wrote:

> On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik  wrote:
>> On 5/22/2013 8:58 AM, Sean Dzafovic wrote:
>>> 
>>> I downloaded the shell and the dll from the sqlite.org site. I put the
>>> .dll in the windows/system32 folder. However, when I try to create a
>>> test db using the command "sqlite3 test.db" as per the example, I get
>>> "Error: near "sqlite3" :syntax error.
>>> 
>>> What am I doing wrong?
>> 
>> 
>> You are running this command on sqlite3 command line (do you see "sqlite3>"
>> prompt?) Instead, you should run this command on Windows command line (Start
>>> Run > cmd), in order to start sqlite3 shell with a given DB file as a
>> parameter.
> 
> Tried it with the shell on my desktop and got the error.
> Moved it to the system32 folder along with the .dll and got the same
> error. (Both of these by clicking on the icon).
> 
> Same when I tried using the Windows command line.
> 
> Either way when I open the shell it gives me a sqlite> prompt, not sqlite3>
> ___
> 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] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik  wrote:
> On 5/22/2013 8:58 AM, Sean Dzafovic wrote:
>>
>> I downloaded the shell and the dll from the sqlite.org site. I put the
>> .dll in the windows/system32 folder. However, when I try to create a
>> test db using the command "sqlite3 test.db" as per the example, I get
>> "Error: near "sqlite3" :syntax error.
>>
>> What am I doing wrong?
>
>
> You are running this command on sqlite3 command line (do you see "sqlite3>"
> prompt?) Instead, you should run this command on Windows command line (Start
>> Run > cmd), in order to start sqlite3 shell with a given DB file as a
> parameter.

Tried it with the shell on my desktop and got the error.
Moved it to the system32 folder along with the .dll and got the same
error. (Both of these by clicking on the icon).

Same when I tried using the Windows command line.

Either way when I open the shell it gives me a sqlite> prompt, not sqlite3>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Thanumalayan Sankaranarayana Pillai]

No, I have reported everything. The only thing I missed might be that it's
not "5 seconds" always, but rather the configurable commit interval of the
filesystem, which is by default 5 seconds in most desktop Linux distros.

I only read through the source code of test6.c, and misunderstood that
ftruncate() was assumed to be synchronous. Sorry. I still think that
test6.c might assume unlink() to be synchronous, though again, that is just
a "think".

Thanks for the replying to my questions. They are really useful. I am
extremely surprised to learn that unlink() cannot be made durable in some
systems. Do answer [c] too, if that's possible.  I still am examining
SQLite and a bunch of other DBMSs for power-crash scenarios, so might come
back to badger you about "bugs" in the future.

--
Thanumalayan Sankaranarayana Pillai
(Graduate student at the University of Wisconsin-Madison)


On Wed, May 22, 2013 at 8:05 AM, Richard Hipp  wrote:

>
>
> On Wed, May 22, 2013 at 8:31 AM, thanumalayan mad wrote:
>
>>
>> Also, not to spam, but it would be great if you could answer these
>> questions for my research (you might send me a reply directly without going
>> through the mailing list): [a] Was it always understood that unlink() and
>> ftruncate() are not synchronous, and that SQLite transactions in DELETE
>> mode are not immediately-durable in Linux; or had you initially
>> misunderstood the semantics of those calls, or left-off the fsync() because
>> of a typo error?
>>
>
> We are aware of the need to fsync() the directory that contains a file
> after unlink() in order to make the unlink() durable.  We have deliberately
> chosen to avoid that fsync() for performance reasons.
>
> Note that on some systems (ex: AIX and the Chromium sandbox) it is not
> possible to fsync() a directory and therefore it is not possible to make
> unlink() durable.
>
>
>
>
>> [b] While designing the crash-tests, were the semantics of the calls in
>> Unix understood? What if ftruncate() not being synchronous did lead to a
>> consistency-loss? Was it reasoned-out that the non-synchronous ftruncate
>> would not produce corruption?
>>
>
> Over the past 12 years of developing SQLite, we have become acutely aware
> of the semantics of unix, both as published and as commonly implemented,
> which are not necessarily the same thing.
>
> Crash-tests are looking for corruption problems only.  Crash testing does
> not look for durability.
>
> I do not believe that our crash-testing assumes that ftruncate() is
> synchronous.  Have you detected an error that you have not reported?
>
>
>
>>  [c] How much of a loss in durability (what other than 5 seconds) would
>> be "good enough" in most cases?
>>
>
>
>
>
>
> --
> 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] Getting Started with Sqlite

[Igor Tandetnik]

On 5/22/2013 8:58 AM, Sean Dzafovic wrote:

I downloaded the shell and the dll from the sqlite.org site. I put the
.dll in the windows/system32 folder. However, when I try to create a
test db using the command "sqlite3 test.db" as per the example, I get
"Error: near "sqlite3" :syntax error.

What am I doing wrong?


You are running this command on sqlite3 command line (do you see 
"sqlite3>" prompt?) Instead, you should run this command on Windows 
command line (Start > Run > cmd), in order to start sqlite3 shell with a 
given DB file as a parameter.

--
Igor Tandetnik

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

Re: [sqlite] Getting Started with Sqlite

[Sean Dzafovic]

On Wed, May 22, 2013 at 10:06 AM, Fehmi Noyan ISI  wrote:
> I do not have this issue with my sqlite 3.7.14 installation in Win7.
>
> Any more details?

Windows XP SP2.

Did I forget to download some files?

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

Re: [sqlite] Using "COLLATE nocase" with BETWEEN

[Igor Tandetnik]

On 5/22/2013 12:53 AM, Yongil Jang wrote:

But, in case of using BETWEEN operator with "COLLATE nocase", it returns
unexpected result as follows.

sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
nocase;


Make it

where data COLLATE nocase between 'abc/' and 'ABC0'

The expression "x between a and b" behaves the same as "x >= a AND x <= 
b". In your example, you end up with


data >= 'abc/' and data <= 'ABC0' COLLATE nocase;

The two comparisons use different collations.
--
Igor Tandetnik

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

Re: [sqlite] Getting Started with Sqlite

[Fehmi Noyan ISI]

I do not have this issue with my sqlite 3.7.14 installation in Win7.

Any more details?



 From: Sean Dzafovic 
To: sqlite-users@sqlite.org 
Sent: Wednesday, May 22, 2013 10:28 PM
Subject: [sqlite] Getting Started with Sqlite
 

I downloaded the shell and the dll from the sqlite.org site. I put the
.dll in the windows/system32 folder. However, when I try to create a
test db using the command "sqlite3 test.db" as per the example, I get
"Error: near "sqlite3" :syntax error.

What am I doing wrong?

Thanks,
Sean Dzafovic
___
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] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Richard Hipp]

On Wed, May 22, 2013 at 8:31 AM, thanumalayan mad wrote:

>
> Also, not to spam, but it would be great if you could answer these
> questions for my research (you might send me a reply directly without going
> through the mailing list): [a] Was it always understood that unlink() and
> ftruncate() are not synchronous, and that SQLite transactions in DELETE
> mode are not immediately-durable in Linux; or had you initially
> misunderstood the semantics of those calls, or left-off the fsync() because
> of a typo error?
>

We are aware of the need to fsync() the directory that contains a file
after unlink() in order to make the unlink() durable.  We have deliberately
chosen to avoid that fsync() for performance reasons.

Note that on some systems (ex: AIX and the Chromium sandbox) it is not
possible to fsync() a directory and therefore it is not possible to make
unlink() durable.




> [b] While designing the crash-tests, were the semantics of the calls in
> Unix understood? What if ftruncate() not being synchronous did lead to a
> consistency-loss? Was it reasoned-out that the non-synchronous ftruncate
> would not produce corruption?
>

Over the past 12 years of developing SQLite, we have become acutely aware
of the semantics of unix, both as published and as commonly implemented,
which are not necessarily the same thing.

Crash-tests are looking for corruption problems only.  Crash testing does
not look for durability.

I do not believe that our crash-testing assumes that ftruncate() is
synchronous.  Have you detected an error that you have not reported?



>  [c] How much of a loss in durability (what other than 5 seconds) would be
> "good enough" in most cases?
>





-- 
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] Getting Started with Sqlite

[Sean Dzafovic]

I downloaded the shell and the dll from the sqlite.org site. I put the
.dll in the windows/system32 folder. However, when I try to create a
test db using the command "sqlite3 test.db" as per the example, I get
"Error: near "sqlite3" :syntax error.

What am I doing wrong?

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

Re: [sqlite] Deleting with offset when grouping

[Patrik Nilsson]

Thank you very much. It works.

Vielen Dank,
Patrik

On 05/22/2013 12:22 PM, Hick Gunter wrote:
> 
> DELETE FROM history WHERE 2 < (SELECT count() FROM history n WHERE 
> n.id=history.id and n.lastactivity > history.lastactivity);
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Mittwoch, 22. Mai 2013 10:48
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Deleting with offset when grouping
> 
> Hi All,
> 
> I can't figure out how to delete with offset using groups.
> 
> Imagine I have a table
> create table history(id integer, lastactivity datetime default
> (datetime('now')))
> 
> and I insert values as
> 
> insert into history (id) values (1)
> 
> A resulting table might look
> 
> 1|2013-05-22 07:50:05
> 1|2013-05-22 07:50:08
> 1|2013-05-22 07:50:10
> 1|2013-05-22 07:50:12
> 2|2013-05-22 07:50:16
> 1|2013-05-22 07:50:18
> 2|2013-05-22 07:50:20
> 2|2013-05-22 07:50:21
> 2|2013-05-22 07:50:22
> 1|2013-05-22 07:50:24
> 3|2013-05-22 07:50:34
> 1|2013-05-22 07:50:36
> 3|2013-05-22 07:50:37
> 3|2013-05-22 07:50:39
> 1|2013-05-22 07:50:41
> 6|2013-05-22 07:58:24
> 
> Now I would like to delete the oldest rows if a group of id has more than 
> three, but I would like to keep the youngest ones in each id group.
> I can delete with offset for the overall table. Is this possible for a group?
> 
> The lastactivity column is unique.
> 
> Cheers,
> Patrik
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Richard Hipp]

On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote:

>
> Expected result: You always find that the transaction had been executed.
> Observed result: You sometimes find that the transaction did not execute.
>

The core team has discussed this.  In order to avoid a substantial
performance hit against transaction COMMIT, we have chosen to not do fsyncs
on the directory when a file is unlinked, and thus to allow loss of
durability following a power loss event.  ACI without the D is still
guaranteed.  But not the D.  The overwhelming majority of applications care
not one wit about durability following power loss.  For most applications,
it is sufficient that the file is uncorrupted.  If recovery gives you a
snapshot of the file as it existed 5 seconds prior to the power loss,
that's fine.

WAL-mode transactions should be durable across power-loss events.  So if
durability is vitally important to you, you can always set PRAGMA
journal_mode=WAL.  Are you observing loss of durability following power
loss in WAL mode?

Is there any place in the documentation that we have overlooked where
SQLite claims to be durable across a power loss in rollback mode?


-- 
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] Deleting with offset when grouping

[Hick Gunter]

DELETE FROM history WHERE 2 < (SELECT count() FROM history n WHERE 
n.id=history.id and n.lastactivity > history.lastactivity);

-Ursprüngliche Nachricht-
Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
Gesendet: Mittwoch, 22. Mai 2013 10:48
An: General Discussion of SQLite Database
Betreff: [sqlite] Deleting with offset when grouping

Hi All,

I can't figure out how to delete with offset using groups.

Imagine I have a table
create table history(id integer, lastactivity datetime default
(datetime('now')))

and I insert values as

insert into history (id) values (1)

A resulting table might look

1|2013-05-22 07:50:05
1|2013-05-22 07:50:08
1|2013-05-22 07:50:10
1|2013-05-22 07:50:12
2|2013-05-22 07:50:16
1|2013-05-22 07:50:18
2|2013-05-22 07:50:20
2|2013-05-22 07:50:21
2|2013-05-22 07:50:22
1|2013-05-22 07:50:24
3|2013-05-22 07:50:34
1|2013-05-22 07:50:36
3|2013-05-22 07:50:37
3|2013-05-22 07:50:39
1|2013-05-22 07:50:41
6|2013-05-22 07:58:24

Now I would like to delete the oldest rows if a group of id has more than 
three, but I would like to keep the youngest ones in each id group.
I can delete with offset for the overall table. Is this possible for a group?

The lastactivity column is unique.

Cheers,
Patrik

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deleting with offset when grouping

[Patrik Nilsson]

Hi All,

I can't figure out how to delete with offset using groups.

Imagine I have a table
create table history(id integer, lastactivity datetime default
(datetime('now')))

and I insert values as

insert into history (id) values (1)

A resulting table might look

1|2013-05-22 07:50:05
1|2013-05-22 07:50:08
1|2013-05-22 07:50:10
1|2013-05-22 07:50:12
2|2013-05-22 07:50:16
1|2013-05-22 07:50:18
2|2013-05-22 07:50:20
2|2013-05-22 07:50:21
2|2013-05-22 07:50:22
1|2013-05-22 07:50:24
3|2013-05-22 07:50:34
1|2013-05-22 07:50:36
3|2013-05-22 07:50:37
3|2013-05-22 07:50:39
1|2013-05-22 07:50:41
6|2013-05-22 07:58:24

Now I would like to delete the oldest rows if a group of id has more
than three, but I would like to keep the youngest ones in each id group.
I can delete with offset for the overall table. Is this possible for a
group?

The lastactivity column is unique.

Cheers,
Patrik

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Thanumalayan Sankaranarayana Pillai]

Thank you for your replies! I now fully understand (and appreciate) that
the "ACI" part of transactions is the most important. Also, I didn't notice
any of ACI being broken: SQLite guarantees those conditions really well.

However, just to be clear, my "potential bug" affects out-of-the-box Fedora
and Ubuntu, even with costly SCSI drives, and with the OS installed using
all default options. On the other hand, in these environments, SQLite
transactions will be immediately durable if fsync()-s are issued after the
ftruncate() or unlink() calls while resetting the rollback journal; I
verified this by modifying unixDelete() within SQLite, setting dirSync = 1
always. I'm writing the rest of this email assuming that this is an
interesting piece of news (and not something already known). Also,
confession: I am a grad student researching filesystems, and I haven’t
directly used SQLite much; I looked at SQLite because I observed some funny
disk activity. So if I am totally out-of-place with this rigmarole,
imagining things about SQLite that aren’t actually true, please forgive the
spam.

It is entirely true that desktop SATA drives with disk-caches, some bad
filesystems, and crazy OS configurations, all disrespect the entire concept
of flushing. But, the setup I verified used either Fedora 17 and Ubuntu
12.04, and either Ext4, Ext3, or Btrfs. I did my best to make sure that the
effects are neither due to a lying disk, nor because of configuration stuff
like disabling filesystem barriers. Moreover, *the last SQLite
transaction's durability is almost always delayed by 5 seconds* (it's not
something you observe only once in a thousand power crashes), if there are
no other applications flushing to the disk. If other applications do flush
to the disk, then the delay is only till when the next flush (of another
application) happens. The bug probably does not affect Windows and other
OSes - it deals with the "Unix" VFS exclusively.

Not calling fsync() might be based on the assumption that they are
synchronous. As far as I can tell, both the ftruncate() and unlink() system
calls are not synchronous (not immediately **issued to the disk**) in the
Linux distros I used, independent of the disk. I understand that such
semantics (of whether the system calls are synchronous) are often
non-standard; at other times, the documentation is not clear about what the
standards are. However, some manpages (http://linux.die.net/man/2/fsync for
ftruncate, and http://linux.die.net/man/8/mount for unlink: look for
"dirsync") suggest that most Linux distributions require an fsync() for
unlink and ftruncate to be synchronous. Some filesystems or distros might,
however, automatically flush, though: I haven't yet looked into the
behavior of other distributions such as RHEL, other Unix-like OSes such as
FreeBSD, or file systems such as XFS. But, I'll be looking into them soon,
as part of my research, so do let me know if you are interested in their
behavior.

Thanks for reading all the way through! Again, it might be a better
decision to not issue the fsync()-s after unlink and ftruncate: "D" is
probably less important than the extra overhead. I'm just trying to make
sure this is by design, and not an accident.

--
Thanu


On Tue, May 21, 2013 at 11:52 AM, Richard Hipp  wrote:
>
> On Tue, May 21, 2013 at 12:04 PM, Thanumalayan Sankaranarayana Pillai <
> madth...@cs.wisc.edu> wrote:
>
> > Hi all,
> >
> > Did anyone look into this? I might be setting some config option wrong,
> > so it would be great if you sent me a "you did something wrong" reply if
> > you feel that I might have the wrong config (or might be doing something
> > totally idiotic).
> >
>
> There are no configuration options set wrong.  You shouldn't ever have to
> set configuration options in order to get SQLite to work right.   I think
> instead you are just running up against fundamental limits of modern
> consumer-grade hardware.  I don't think there is anything anybody can do
> about it.
>
>
> >
> > I tested with a few other Linux machines and a few different SQLite
> > versions, and found that there is an upto-5-second delay (the default
> > filesystem commit interval) in the transactions getting durable (with
power
> > crashes, that is).
> >
> > Thanks in advance,
> > Thanu
> >
> > On Sat, May 18, 2013 at 3:41 AM, thanumalayan mad  > >wrote:
> >
> > > Hi All,
> > >
> > > I was testing out SQLite with a framework I developed. I believe,
while
> > > running on Linux, transactions might not be durable when a power crash
> > > happens immediately after a commit. I observed this using "SQLite
version
> > > 3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64".
Steps
> > to
> > > reproduce:
> > >
> > > 1. Use a Linux machine with an Ext4 filesystem (default mount
options).
> > > 2. Create a database file, set journal_mode to DELETE, perform a
> > > transaction using "begin transaction ... commit;".
> > > 3. Pull the power plug