[sqlite] how to use sqlite in COCOA

[YAN HONG YE]

Who can tell me how to use Sqlite in COCOA  in my MAC system?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Sohail Somani]

On 2014-07-29, 8:23 PM, Will Fong wrote:

Hi,

On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:

'localtime' and 'utc' modifiers.


Ah! I have not explained my issue properly :)  I'm very sorry about that.

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.

Really sorry for the confusion. Late night.


I'd suggest a view on the data that does the conversion. You can also 
use an INSTEAD OF trigger for insertion:


CREATE VIEW user_data_view AS
  SELECT *,to_user_tz(gmt_time,user_tz)
  FROM user_data_view;

When I need to care about timezones, I always store them as GMT and only 
convert them when I need to present them to the user. This makes math 
and comparisons on dates easy.


Fortunately, my applications are usually designed in a manner that there 
are very few lines of code needed to support these to/from conversions.


Sohail


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

Re: [sqlite] Handling Timezones

[Nico Williams]

You can haz per-connection TZ setting: use a temp table and join with it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 10:01 AM, Keith Medcalf  wrote:
> Store and retrieve everything in the database in Zulu time.  Whether this 
> means using timestrings, UNIX timestamps, JD or MJD floats is up to you.  The 
> application (user interface) is responsible for converting retrieved data to 
> the "display timezone" on output and convert data from the "input timezone" 
> on input.
>
> This is the only reliable way to handle multiple timezones.  There are lots 
> of moronic ways and five-nines (asctually more like nine-nines) of all 
> software written use those moronic methods and for that reason do not work 
> properly ("not work properly" being defined as anything somewhere between 
> producing incorrect or ludicrously entertaining results and just puking all 
> over the floor).  In many cases "not working" but "not puking" is acceptable 
> provided that the moronic behaviour is internally consistent.  In others, 
> "not working" is fatal.

Well, PostgreSQL's method for this to be handled at the
client/connection level seemed to have worked very well, and doesn't
seem very "moronic". Having this handled at the database level makes
the application(s) much less prone to bugs.


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

Re: [sqlite] Handling Timezones

[Keith Medcalf]

>> 'localtime' and 'utc' modifiers.
>
>Ah! I have not explained my issue properly :)  I'm very sorry about that.
>
>I'm using SQLite as a backend to a small website and I have users in
>multiple timezones. When users login, their timezone is retrieved from
>the user table.
>
>Really sorry for the confusion. Late night.

Store and retrieve everything in the database in Zulu time.  Whether this means 
using timestrings, UNIX timestamps, JD or MJD floats is up to you.  The 
application (user interface) is responsible for converting retrieved data to 
the "display timezone" on output and convert data from the "input timezone" on 
input.

This is the only reliable way to handle multiple timezones.  There are lots of 
moronic ways and five-nines (asctually more like nine-nines) of all software 
written use those moronic methods and for that reason do not work properly 
("not work properly" being defined as anything somewhere between producing 
incorrect or ludicrously entertaining results and just puking all over the 
floor).  In many cases "not working" but "not puking" is acceptable provided 
that the moronic behaviour is internally consistent.  In others, "not working" 
is fatal.

If your "front-end" programming language cannot handle time and timezones 
properly and you need to support multiple timezones, then you have chosen the 
wrong front-end language and/or datetime/timezone handling library.  Stay away 
from anything Microsoft as it is hopeless at dealing with datetime data and 
timezone conversion.





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

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 9:12 AM, Donald Shepherd
 wrote:
> You can represent time zones as integers by using minutes.  Examples: +600
> for AEST, +330 for IST, -480 for PST.  No string manipulation is needed,
> but depending on what or if you're using libraries, you may need extra
> steps in there for convert those values into a representation supported by
> the library.

Ah! I never thought about that. That seems to be the most
straightforward for my use.

It is too bad it's not possible to set this at a query/connection level :(

Thanks!

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

Re: [sqlite] Handling Timezones

[Donald Shepherd]

You can represent time zones as integers by using minutes.  Examples: +600
for AEST, +330 for IST, -480 for PST.  No string manipulation is needed,
but depending on what or if you're using libraries, you may need extra
steps in there for convert those values into a representation supported by
the library.


On 30 July 2014 10:47, Will Fong  wrote:

> Hi,
>
> On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin 
> wrote:
> > Store their timezones in the format "[+-]HH:MM" and apply them by
> appending that text to any dates they provide.  See the "Time Strings"
> section of
>
> I can store each user's timezone setting as "[+-]HH:MM".  But I can
> only apply that to GMT values. So when I'm reading from the database,
> it's a trivial operation.
>
> However, if a user specifies a datetime, I would have to provide the
> reverse of that value to convert the user time into GMT. It would be a
> bit easier (yet still messy) if the timezone was just an integer, then
> I could just "*-1". But the ":MM" seems to make it a messy string
> operation.
>
> Is this the only option? It seems like there would have been a
> "better" way to handle this.
>
> Thanks,
> -will
> ___
> 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] Handling Timezones

[Simon Slavin]

On 30 Jul 2014, at 1:47am, Will Fong  wrote:

> On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin  wrote:
>> Store their timezones in the format "[+-]HH:MM" and apply them by appending 
>> that text to any dates they provide.  See the "Time Strings" section of
> 
> I can store each user's timezone setting as "[+-]HH:MM".  But I can
> only apply that to GMT values.

The SQLite routines will apply those timezones to any times.  You just append 
them to the time string you supply to the routines.

> So when I'm reading from the database,
> it's a trivial operation.
> 
> However, if a user specifies a datetime, I would have to provide the
> reverse of that value to convert the user time into GMT. It would be a
> bit easier (yet still messy) if the timezone was just an integer, then
> I could just "*-1". But the ":MM" seems to make it a messy string
> operation.
> 
> Is this the only option? It seems like there would have been a
> "better" way to handle this.

I would probably write a library routine which converted the zone in the form 
the user supplies it (+3, -9, GMT, +0, whatever) to the form I wanted it 
"[+-]HH:MM", and I'd store both of them  /and/ a copy of "[+-]HH:MM" with the 
sign reversed.

Alternatively, you can provide your users with a popup, but deduce all three 
forms from whatever value they picked in the popup.

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

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 8:38 AM, Simon Slavin  wrote:
> Store their timezones in the format "[+-]HH:MM" and apply them by appending 
> that text to any dates they provide.  See the "Time Strings" section of

I can store each user's timezone setting as "[+-]HH:MM".  But I can
only apply that to GMT values. So when I'm reading from the database,
it's a trivial operation.

However, if a user specifies a datetime, I would have to provide the
reverse of that value to convert the user time into GMT. It would be a
bit easier (yet still messy) if the timezone was just an integer, then
I could just "*-1". But the ":MM" seems to make it a messy string
operation.

Is this the only option? It seems like there would have been a
"better" way to handle this.

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

Re: [sqlite] Handling Timezones

[Simon Slavin]

On 30 Jul 2014, at 1:23am, Will Fong  wrote:

> On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:
>> 'localtime' and 'utc' modifiers.
> 
> Ah! I have not explained my issue properly :)  I'm very sorry about that.
> 
> I'm using SQLite as a backend to a small website and I have users in
> multiple timezones. When users login, their timezone is retrieved from
> the user table.

Store their timezones in the format "[+-]HH:MM" and apply them by appending 
that text to any dates they provide.  See the "Time Strings" section of



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

Re: [sqlite] Handling Timezones

[Igor Tandetnik]

On 7/29/2014 8:23 PM, Will Fong wrote:

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.


Well, SQLite delegates to the C runtime for timezone handling. I suspect 
tzset() et al could be used to trick it into assuming a timezone of your 
choice. That's per process though, not per connection.


Alternatively, there is 'N hours' modifier for date/time functions (N 
may be negative). That's much more manual though, as the modifiers will 
have to be written into every query, possibly as bound parameters.


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

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Wed, Jul 30, 2014 at 8:16 AM, Igor Tandetnik  wrote:
> 'localtime' and 'utc' modifiers.

Ah! I have not explained my issue properly :)  I'm very sorry about that.

I'm using SQLite as a backend to a small website and I have users in
multiple timezones. When users login, their timezone is retrieved from
the user table.

Really sorry for the confusion. Late night.

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

Re: [sqlite] Handling Timezones

[Igor Tandetnik]

On 7/29/2014 8:10 PM, Will Fong wrote:

On Tue, Jul 29, 2014 at 9:56 PM, Gerry Snyder  wrote:

Have you read http://sqlite.org/lang_datefunc.html   ?


Is there something there that I missed? I didn't see anything there
that relates on how to handle timezone operations.


'localtime' and 'utc' modifiers.
--
Igor Tandetnik

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

Re: [sqlite] Handling Timezones

[Will Fong]

Hi,

On Tue, Jul 29, 2014 at 9:56 PM, Gerry Snyder  wrote:
> Have you read http://sqlite.org/lang_datefunc.html   ?

Yes.

Is there something there that I missed? I didn't see anything there
that relates on how to handle timezone operations.

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

Re: [sqlite] Counting changes in a INSERT/REPLACE

[jose isaias cabrera]

"Richard Hipp" wrote...

On Tue, Jul 29, 2014 at 4:49 PM, jose isaias cabrera 


wrote:



Is there a way to keep a count of the INSERTS/REPLACES that took place
between the BEGIN and END?  I was looking into the SQL changes() 
function,


http://www.sqlite.org/lang_corefunc.html#changes

but wouldn't it have to be done after each INSERT?



Use the total_changes() function both before and after the transaction, 
and

take the difference between the two.


Thank you, Dr. Hipp.  Works like a charm.

josé 


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

Re: [sqlite] Counting changes in a INSERT/REPLACE

[Richard Hipp]

On Tue, Jul 29, 2014 at 4:49 PM, jose isaias cabrera 
wrote:

>
> Is there a way to keep a count of the INSERTS/REPLACES that took place
> between the BEGIN and END?  I was looking into the SQL changes() function,
>
> http://www.sqlite.org/lang_corefunc.html#changes
>
> but wouldn't it have to be done after each INSERT?
>

Use the total_changes() function both before and after the transaction, and
take the difference between the two.


-- 
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] ISO time leap second.

[Nico Williams]

On Tue, Jul 29, 2014 at 10:02 AM, Simon Slavin  wrote:
> You're never going to get non-scientific programmers to do this properly 
> anyway.  Every financial programmer knows that there are exactly 60*60*24 = 
> 86,400 seconds in a day.  You've never going to get them to use library 
> routines to work out how many seconds there are in a 30 day period.

I'm not sure that this doesn't matter to people dealing with financial
data, but I suspect that if someone cares about TAI in financial data
then they probably care about having very high resolution timestamps
too (think of high speed trading).

Perhaps the same goes for astronomers, actually: millisecond
resolution probably isn't enough if you really care about one second.
(Of course, the accumulated error from ignoring leap seconds across
decades-long intervals will be larger than one second, but still, I
think it's fair to infer that astronomers probably care about higher
time resolutions than a millisecond.

I think that's getting close to beating this poor horse a bit too
much.  Count me as in favor ignoring second values of 60 when parsing
seconds.

Dealing with TAI reliably would require extending the existing date
functions, or adding new ones, to at minimum do TAI<->UTC (and/or
Julan day) conversions.  Given that it should be possible to produce
functions that do date arithmetic by converting inputs to TAI as
necessary (assuming it's possible to tell UTC inputs from TAI inputs).

FYI: http://cr.yp.to/proto/utctai.html

DJB also has a library for converting between TAI and UTC that might
be useful here: http://cr.yp.to/libtai.html .

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

[sqlite] Counting changes in a INSERT/REPLACE

[jose isaias cabrera]

Greetings!

I have this update:

BEGIN;
   INSERT OR REPLACE INTO A
   SELECT * FROM client.A WHERE id = 1 AND Date != '2014-06-22';
   INSERT OR REPLACE INTO A
   SELECT * FROM client.A WHERE id = 2 AND Date != '2014-06-22';
...
...
   INSERT OR REPLACE INTO A
SELECT * FROM client.A WHERE id = 19 AND Date != '2014-06-22';
   INSERT OR REPLACE INTO A
SELECT * FROM client.A WHERE id = 20 AND Date != '2014-06-22';
END;

Is there a way to keep a count of the INSERTS/REPLACES that took place 
between the BEGIN and END?  I was looking into the SQL changes() function,


http://www.sqlite.org/lang_corefunc.html#changes

but wouldn't it have to be done after each INSERT?

thanks. 


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

Re: [sqlite] ISO time leap second.

[Simon Slavin]

On 29 Jul 2014, at 5:36am, Nico Williams  wrote:

> Note that SQLite3 apparently does no
> corrections for leap seconds anyways in date arithmetic (which it
> can't do if you're using numeric arithmetic on Julian days anyways!),
> which it would have to do (since UTC is non-uniform).  I'm not
> entirely sure what it means for anyone who really cares about TAI, or
> who they might be besides astronomers.  If all you want to do is parse
> dates, then indeed, raising an error would be better than ignoring a
> leap second, but if you want to do any arithmetic on dates, then
> you're already in trouble, given which I think the fair thing to do is
> ignore leap seconds, but I'll freely admit that it's not ideal.

Agree with all of that.

You're never going to get non-scientific programmers to do this properly 
anyway.  Every financial programmer knows that there are exactly 60*60*24 = 
86,400 seconds in a day.  You've never going to get them to use library 
routines to work out how many seconds there are in a 30 day period.

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

Re: [sqlite] Handling Timezones

[Gerry Snyder]

Have you read http://sqlite.org/lang_datefunc.html   ?

On 7/29/2014 6:41 AM, Will Fong wrote:

Hi,

How are timezones best handled? Since dates are stored in GMT, when I
go to display them, I need to add/subtract the timezone. That's not
too hard when I can just store the timezone as "-5" for EST. When I'm
providing a date to query on, I would have to apply the reverse of the
timezone, "+5", to normalize it to GMT.

That kinda sucks :(

I come from PostgreSQL, so I normally set at the connection level the
timezone and PG handles all the conversions. Does SQLite have a
similar feature?

Is there a standard way to handle this?

Thanks,
-will
___
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] Handling Timezones

[Will Fong]

Hi,

How are timezones best handled? Since dates are stored in GMT, when I
go to display them, I need to add/subtract the timezone. That's not
too hard when I can just store the timezone as "-5" for EST. When I'm
providing a date to query on, I would have to apply the reverse of the
timezone, "+5", to normalize it to GMT.

That kinda sucks :(

I come from PostgreSQL, so I normally set at the connection level the
timezone and PG handles all the conversions. Does SQLite have a
similar feature?

Is there a standard way to handle this?

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

Re: [sqlite] Long execution time since sqlite 3.8

[Michael]

CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE tbl_lib_filters(UUID TEXT NOT NULL PRIMARY KEY, template
TEXT NOT NULL, library TEXT NOT NULL, rules TEXT);
CREATE TABLE tbl_library(UUID TEXT NOT NULL PRIMARY KEY, TITLE TEXT,
ICON_URI TEXT, DESCCRIPTION TEXT, REMOVED INTEGER NOT NULL,
sortTemplateUUID TEXT, groupTemplateUUID TEXT, sortDirection INTEGER,
templateVersion INTEGER, googleDocId TEXT, googleDocWorksheet TEXT,
googleDocNeedSync INTEGER, googleDocTemplateVersion INTEGER,
picasaAlbumId TEXT, public_id TEXT, lib_alias TEXT, allowed_users TEXT,
private_lib INTEGER, need_update_pub_template INTEGER, type INTEGER,
group_id INTEGER, google_sync_time INTEGER, public_time INTEGER,
protected INTEGER, encripted INTEGER, lib_order INTEGER, tile_columns
INTEGER, tile_color INTEGER, edit_time INTEGER, filter_uuid TEXT,
tile_text_color INTEGER, cloud_storage TEXT, cloud_folder_id TEXT,
lock_edit INTEGER, template_gd_file_id TEXT, entry_pages TEXT,
sort_options TEXT);
CREATE TABLE tbl_library_item(UUID TEXT NOT NULL PRIMARY KEY, LIB_UUID
TEXT, REMOVED INTEGER NOT NULL, creation_date INTEGER, REMOVED_TIME
INTEGER, VIEW_TIME INTEGER, FAVORITE INTEGER, EDIT_TIME INTEGER, FTS3_ID
INTEGER);
CREATE TABLE tbl_flex_content2(id INTEGER PRIMARY KEY AUTOINCREMENT,
stringContent TEXT, realContent REAL, intContent INTEGER, ownerUUID TEXT
NOT NULL, templateUUID TEXT NOT NULL);
CREATE TABLE tbl_filter_profiles(UUID TEXT NOT NULL PRIMARY KEY, library
TEXT NOT NULL, filter_name TEXT);
CREATE TABLE tbl_flex_template(UUID TEXT NOT NULL PRIMARY KEY, title
TEXT, type_code TEXT NOT NULL, usage INTEGER NOT NULL, number INTEGER
NOT NULL, encripted INTEGER, req INTEGER, LIB_UUID TEXT, stats TEXT,
hint TEXT, depends TEXT, display_title INTEGER);
CREATE TABLE tbl_gdocs_item_handlers(item_uuid TEXT NOT NULL PRIMARY
KEY, lib_uuid TEXT NOT NULL, gdocs_record_id TEXT NOT NULL, remove_flag
INTEGER, etag TEXT NOT NULL, edited INTEGER);
CREATE TABLE tbl_picasa_images(picasa_url TEXT NOT NULL, local_uri TEXT
NOT NULL, lib_uuid TEXT NOT NULL);
CREATE TABLE tbl_pub_item_handlers(item_uuid TEXT NOT NULL PRIMARY KEY,
lib_uuid TEXT NOT NULL, pub_id TEXT NOT NULL, remove_flag INTEGER,
version INTEGER, edited INTEGER);
CREATE TABLE tbl_groups(id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT
NULL, sys_code TEXT);
CREATE TABLE tbl_master_hash (pass_hash TEXT NOT NULL PRIMARY KEY);
CREATE TABLE tbl_icon_storage(id INTEGER PRIMARY KEY AUTOINCREMENT, icon
BLOB);
CREATE TABLE tbl_field_email_format_2 (template_uuid TEXT NOT NULL,
lib_uuid TEXT NOT NULL, new_line INTEGER, ex_options TEXT,
colon_after_name INTEGER, email_template INTEGER, send_by_email INTEGER);
CREATE TABLE tbl_reminders3(id INTEGER PRIMARY KEY AUTOINCREMENT,
rem_minutes INTEGER, rem_time INTEGER);
CREATE TABLE tbl_email_templ (id INTEGER PRIMARY KEY
AUTOINCREMENT,lib_uuid TEXT NOT NULL,title TEXT);
CREATE TABLE tbl_cloud_files(cloud_url TEXT NOT NULL, local_uri TEXT NOT
NULL, cloud_id TEXT NOT NULL, lib_uuid TEXT NOT NULL);
CREATE VIRTUAL TABLE library_fts3 USING fts3();
CREATE TABLE 'library_fts3_content'(docid INTEGER PRIMARY KEY, 'c0content');
CREATE TABLE 'library_fts3_segments'(blockid INTEGER PRIMARY KEY, block
BLOB);
CREATE TABLE 'library_fts3_segdir'(level INTEGER,idx INTEGER,start_block
INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY
KEY(level, idx));
CREATE TABLE category_alias (id integer PRIMARY KEY, category_id
integer, name string, Unique (name));
CREATE TABLE disks_bez (id integer PRIMARY KEY, name string, bez string,
Unique (name));
CREATE TABLE item_ref(id integer PRIMARY KEY,item1_id integer,
item1_type_id integer, item2_id integer, item2_type_id integer, unique
(item1_id, item1_type_id, item2_id, item2_type_id));
CREATE TABLE links(id integer PRIMARY KEY,name text,mediatype
text,source text,mirror text,dateAdded datetime,zone integer default 3,
pid integer, fid integer, votes integer default 0 not null, rating
integer default 0 not null, unique (source));
CREATE TABLE zone_bez (id integer PRIMARY KEY, zone integer, bez string,
Unique (zone));
CREATE TABLE zones (id integer PRIMARY KEY, zones integer, zone integer,
Unique (zones,zone));
CREATE TABLE category_item(id integer PRIMARY KEY,category_id integer
not null,item_id integer not null,item_type_id integer not null,UNIQUE
(category_id,item_id,item_type_id));
CREATE TABLE history(item_id integer, item_type_id integer, search_term
text, categories text, ex_categories text, zones integer, timestamp
datetime, command text, parameters text, output text);
CREATE TABLE category(id integer PRIMARY KEY,name text,parent integer,
zone integer,UNIQUE (name));
CREATE TABLE files(id integer PRIMARY KEY,name text,mediatype
text,disksource text,inode integer,bdisksource text,binode
integer,source text,zone integer default 3, votes integer default 0 not
null, rating integer default 0 not null, UNIQUE (disksource,inode));
CREATE TABLE UUID_infosystem(UUID TEXT NOT NULL PRIMARY KEY,item_id
integer not 

Re: [sqlite] Long execution time since sqlite 3.8

[Michael]

Ok that's the output of sqlite_stat1:

tbl|idx|stat
android_metadata||1
tbl_flex_template|idx_flex_template_lib|10 10
tbl_flex_template|sqlite_autoindex_tbl_flex_template_1|10 1
zones|sqlite_autoindex_zones_1|84 6 1
history||5870
tbl_flex_content2|idx_flex_content_temp|20010 2001
tbl_flex_content2|idx_flex_content_owner|20010 10
tbl_library|idx_library_removed|1 1
tbl_library|sqlite_autoindex_tbl_library_1|1 1
tbl_lib_filters|sqlite_autoindex_tbl_lib_filters_1|4 1
tbl_filter_profiles|idx_filters2_library|4 4
tbl_filter_profiles|sqlite_autoindex_tbl_filter_profiles_1|4 1
category_item|sqlite_autoindex_category_item_1|6827 20 2 1
library_fts3_segdir|sqlite_autoindex_library_fts3_segdir_1|28 10 1
library_fts3_segments||254
infos||1293
tbl_groups||1
disks_bez|sqlite_autoindex_disks_bez_1|11 1
tbl_library_item|idx_library_item_fts3|2000 1
tbl_library_item|idx_library_item_removed|2000 2000
tbl_library_item|idx_library_item_lib|2000 2000
tbl_library_item|sqlite_autoindex_tbl_library_item_1|2000 1
category|sqlite_autoindex_category_1|416 1
library_fts3_content||2000
zone_bez|sqlite_autoindex_zone_bez_1|4 1
category_alias|sqlite_autoindex_category_alias_1|114 1
item_ref|sqlite_autoindex_item_ref_1|93 2 2 1 1
links|sqlite_autoindex_links_1|2762 2

and the schema attached


> Gesendet: Dienstag, 29. Juli 2014 um 12:52 Uhr
> Von: "Richard Hipp" 
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] Long execution time since sqlite 3.8
>
> On Tue, Jul 29, 2014 at 4:55 AM, Michael 
wrote:
>
> >
> > Should I post the EXPLAIN?
> >
>
> No.  You should post the database schema and the content of the
> sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables if such tables
exist.
>
>
> --
> 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

[sqlite] ADO Support for SQLite using Windows 8.1 and Windows Phone 8.1?

[Ken Wenyon]

Is there ADO Support for SQLite using Windows 8.1 and Windows Phone 8.1?  I am 
looking for a Cross-Platform ADO wrapper for iOS, Android and Win 8.1 WP 8.1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite

[Richard Hipp]

On Tue, Jul 29, 2014 at 4:48 AM, Clemens Ladisch  wrote:

>
> Simplified for plain ol' SQLite as follows:
>

Thank you.  That is exactly what we needed.

The bug has now been characterized, studied, and fixed.  See [
http://www.sqlite.org/src/info/d2889096e7bdeac6] for the ticket and [
http://www.sqlite.org/src/info/8cc41b0bf365] for a patch that fixes the
problem.  The problem is fixed on trunk and an amalgamation build of the
latest trunk is available on the download page at [
http://www.sqlite.org/download.html]



>
> CREATE TABLE L (Lid INTEGER PRIMARY KEY);
> INSERT INTO L VALUES(1);
> CREATE TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER);
> INSERT INTO P VALUES(1,1);
> INSERT INTO P VALUES(2,1);
> CREATE VIRTUAL TABLE R USING rtree(Pid, xmin, xmax);
> INSERT INTO R VALUES(1,0,0);
> INSERT INTO R VALUES(2,0,0);
> CREATE INDEX idx_P_Lid ON P (Lid);
>
> -- fails, result should be two rows:
> SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;
>
>
> Regards,
> Clemens
> ___
> 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] parser stack overflow in view

[Richard Hipp]

On Tue, Jul 29, 2014 at 8:13 AM, Michael  wrote:

> I coudn't imagine that nobody else had such problems.
>
> The fallowing select statement was existing without problems in older
> sqlite database.
> It's created with another application of me and so it's hard to replace
> the subselects.
>
> which other infos would help you to find the problem?
>

The database schema.

To study and understand these kinds of problems, we need to run your SQL
statements to see what is happening.  But we cannot do that unless we know
the database schema.



>
>
> CREATE VIEW v_food_and_drinks as
> select infos.name, infos.text, count(*) as category_match, infos.rating,
> DateTime(infos.dateAdded), DateTime(infos.expiration)
>   from (  select v_infos.id, v_infos.name, v_infos.text, v_infos.rating,
> v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
> DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id
> in
>   (
>select item_id from category_info, category where
> category_info.category_id=category.id AND category.id in
>(
> select id from category where id in (select id from category
> where (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown')))
>   or parent in
>   (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'
>   or parent in
>   (
>select id from category where parent in
>
>(select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'
>
>   )
>   or parent in
>   (
>select id from category where parent in
>(
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'
>)
>   )
>   or parent in
>   (
>select id from category where parent in
>(
> select id from category where parent in
> (
>  select id from category where parent in
>
>  (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'
>
> )
>
>)
>
>   ) ) and category.zone in (select zone from zones where
> zones.zones=1)
> )
>   ) UNION ALL select v_infos.id, v_infos.name, v_infos.text,
> v_infos.rating, v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
> DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id
> in
>   (
>select item_id from category_info, category where
> category_info.category_id=category.id AND category.id in
>(
> select id from category where id in (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks')))
>   or parent in
>   (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'
>   or parent in
>   (
>select id from category where parent in
>
>(select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'
>
>   )
>   or parent in
>   (
>select id from category where parent in
>(
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'
>
>)
>
>   )
>   or parent in
>   (
>select id from category where parent in
>
>
>(
> select id from category where parent in
>
>
> (
>  select id from category where parent in
>
>  (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'
>
> )
>
>)
>
>   ) ) and category.zone in (select zone from zones where zones.zones=1)
>   )
>   )) as infos where 1=1 and infos.zone in (select zone from zones
> where zones.zones=1)
>   group by infos.id having category_match>=2  order by rating desc,
> DateAdded desc;
>

Re: [sqlite] parser stack overflow in view

[Michael]

I coudn't imagine that nobody else had such problems.

The fallowing select statement was existing without problems in older
sqlite database.
It's created with another application of me and so it's hard to replace
the subselects.

which other infos would help you to find the problem?


CREATE VIEW v_food_and_drinks as
select infos.name, infos.text, count(*) as category_match, infos.rating,
DateTime(infos.dateAdded), DateTime(infos.expiration)
  from (  select v_infos.id, v_infos.name, v_infos.text, v_infos.rating,
v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id in
  (
   select item_id from category_info, category where
category_info.category_id=category.id AND category.id in
   (
select id from category where id in (select id from category
where (lower(category.name)=lower('hometown') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('hometown')))
  or parent in
  (select id from category where
(lower(category.name)=lower('hometown') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('hometown'
  or parent in
  (
   select id from category where parent in

   (select id from category where
(lower(category.name)=lower('hometown') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('hometown'

  )
  or parent in
  (
   select id from category where parent in
   (
select id from category where parent in

(select id from category where
(lower(category.name)=lower('hometown') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('hometown'
   )
  )
  or parent in
  (
   select id from category where parent in
   (
select id from category where parent in
(
 select id from category where parent in

 (select id from category where
(lower(category.name)=lower('hometown') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('hometown'

)

   )

  ) ) and category.zone in (select zone from zones where
zones.zones=1)
)
  ) UNION ALL select v_infos.id, v_infos.name, v_infos.text,
v_infos.rating, v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id in
  (
   select item_id from category_info, category where
category_info.category_id=category.id AND category.id in
   (
select id from category where id in (select id from category where
(lower(category.name)=lower('food_and_drinks') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('food_and_drinks')))
  or parent in
  (select id from category where
(lower(category.name)=lower('food_and_drinks') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('food_and_drinks'
  or parent in
  (
   select id from category where parent in

   (select id from category where
(lower(category.name)=lower('food_and_drinks') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('food_and_drinks'

  )
  or parent in
  (
   select id from category where parent in
   (
select id from category where parent in

(select id from category where
(lower(category.name)=lower('food_and_drinks') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('food_and_drinks'

   )

  )
  or parent in
  (
   select id from category where parent in


   (
select id from category where parent in


(
 select id from category where parent in

 (select id from category where
(lower(category.name)=lower('food_and_drinks') or category.id in (select
distinct category_id from category_alias where
lower(name)=lower('food_and_drinks'

)

   )

  ) ) and category.zone in (select zone from zones where zones.zones=1)
  )
  )) as infos where 1=1 and infos.zone in (select zone from zones
where zones.zones=1)
  group by infos.id having category_match>=2  order by rating desc,
DateAdded desc;




> Gesendet: Dienstag, 29. Juli 2014 um 12:54 Uhr
> Von: "Richard Hipp" 
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] parser stack overflow in view
>
> On Tue, Jul 29, 2014 at 5:00 AM, Michael 
wrote:
>
> > I have a view with about 6 Unions and a depth of about 6 subselects in
> > each select.
> > Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
> > Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many 

Re: [sqlite] parser stack overflow in view

[Richard Hipp]

On Tue, Jul 29, 2014 at 5:00 AM, Michael  wrote:

> I have a view with about 6 Unions and a depth of about 6 subselects in
> each select.
> Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
> Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many queries
> with this problem now...
>
> Can anyone help me please
>

Can you post an example of your problem.  You haven't given us much to go
on.


-- 
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] Long execution time since sqlite 3.8

[Richard Hipp]

On Tue, Jul 29, 2014 at 4:55 AM, Michael  wrote:

>
> Should I post the EXPLAIN?
>

No.  You should post the database schema and the content of the
sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables if such tables exist.


-- 
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] System.Data.Sqlite

[Eric DAVID]

Yes, this is exactly the same issue I encounter.
There are 2 rows in the result with Spatialite GUI, but only one with
System.Data.Sqlite.

-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Clemens Ladisch
Envoyé : mardi 29 juillet 2014 10:49
À : sqlite-users@sqlite.org
Objet : Re: [sqlite] System.Data.Sqlite

Eric DAVID wrote:
> Well, It seems to be impossible to join files to mails. So, here are 
> the statements :
>
> CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT 
> NULL, lig_nom TEXT NOT NULL); SELECT AddGeometryColumn('Lignes', 
> 'lig_geom', 2154, 'LINESTRING', 'XY'); ...

Simplified for plain ol' SQLite as follows:

CREATE TABLE L (Lid INTEGER PRIMARY KEY); INSERT INTO L VALUES(1); CREATE
TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER); INSERT INTO P VALUES(1,1);
INSERT INTO P VALUES(2,1); CREATE VIRTUAL TABLE R USING rtree(Pid, xmin,
xmax); INSERT INTO R VALUES(1,0,0); INSERT INTO R VALUES(2,0,0); CREATE
INDEX idx_P_Lid ON P (Lid);

-- fails, result should be two rows:
SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;


Regards,
Clemens
___
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] parser stack overflow in view

[Michael]

I have a view with about 6 Unions and a depth of about 6 subselects in
each select.
Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many queries
with this problem now...
 
Can anyone help me please
Any Ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Long execution time since sqlite 3.8

[Michael]

Hello,
 
The following query needs about 8 seconds since 3.8. In sqlite-3.7.17 it
was less than a second.
 
select infos.name, infos.id, infos.rating, DateTime(infos.date),
DateTime(infos.expiration)
from infos  
where infos.id not in  
(
  select distinct infos.id from infos, category, category_info where
category_info.category_id=category.id and category_info.item_id=infos.id
and category.name in ('done','monthly','biweekly','weekly')
  or  
  category.id in  
  (
select distinct category_id from category_alias where name in
('done','monthly','biweekly','weekly')
  )  
  and category.zone in (select zone from zones where zones.zones=5)
)
and (Date(date)='2014-07-20' OR Date(dateAdded)='2014-07-20')  
and infos.zone in (select zone from zones where zones.zones=1)
group by infos.id order by date, rating;
 
Does anyone have an idea what's the problem with the query planer in
this case?
If I remove the part ...
or 
category.id in ()
... it's mutch faster

Should I post the EXPLAIN?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite

[Clemens Ladisch]

Eric DAVID wrote:
> Well, It seems to be impossible to join files to mails. So, here are the
> statements :
>
> CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
> lig_nom TEXT NOT NULL);
> SELECT AddGeometryColumn('Lignes', 'lig_geom', 2154, 'LINESTRING', 'XY');
> ...

Simplified for plain ol' SQLite as follows:

CREATE TABLE L (Lid INTEGER PRIMARY KEY);
INSERT INTO L VALUES(1);
CREATE TABLE P (Pid INTEGER PRIMARY KEY, Lid INTEGER);
INSERT INTO P VALUES(1,1);
INSERT INTO P VALUES(2,1);
CREATE VIRTUAL TABLE R USING rtree(Pid, xmin, xmax);
INSERT INTO R VALUES(1,0,0);
INSERT INTO R VALUES(2,0,0);
CREATE INDEX idx_P_Lid ON P (Lid);

-- fails, result should be two rows:
SELECT * FROM P JOIN L USING (Lid) JOIN R USING (Pid) WHERE L.Lid = 1;


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

Re: [sqlite] Where exactly are parameters accepted in an expression?

[Clemens Ladisch]

Zsbán Ambrus wrote:
> CREATE VIEW vp AS SELECT ? AS x;
>
> Error: parameters are not allowed in views
>
> This error seems reasonable, but I can't find anything about it in
> documentation.  Could you tell me where the documentation explains
> where exactly I can use parameters (bound parameters, variables,
> placeholders, question marks) in an expression?

I don't know about the documentation, but you must not use parameter
markers where they would be stored in the database, which is in _all_
these cases:

>  - The SELECT part in a CREATE VIEW statement.
>  - The default values of columns in a CREATE TABLE statement.
>  - The CHECK constraint expressions in a CREATE TABLE statement.
>  - The trigger conditions after WHEN in a CREATE TRIGGER statement.
>  - Inside statements in a trigger.

... even if SQLite isn't paranoid enough:

sqlite> create table t(x default ?);
Error: near "?": syntax error
sqlite> create table t(x default (?));
sqlite> insert into t default values;
sqlite> select quote(x) from t;
X''


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

[sqlite] Where exactly are parameters accepted in an expression?

[Zsbán Ambrus]

Hello sqlite list!

The following statement gives an error in sqlite 3.8.4.3.

CREATE VIEW vp AS SELECT ? AS x;

The error message is:

Error: parameters are not allowed in views

The parameter (placeholder) is definitely be the problem here, because
this similar statement executes without error.

CREATE VIEW vs AS SELECT 'flt' AS x;

This error seems reasonable, but I can't find anything about it in
documentation.  Could you tell me where the documentation explains
where exactly I can use parameters (bound parameters, variables,
placeholders, question marks) in an expression?  In particular,
whether it is valid to use a placeholder in any of the following.

 - The SELECT part in a CREATE VIEW statement.
 - The default values of columns in a CREATE TABLE statement.
 - The CHECK constraint expressions in a CREATE TABLE statement.
 - The trigger conditions after WHEN in a CREATE TRIGGER statement.
 - Inside statements in a trigger.

As a special case, the documentation already tells that parameters are
invalid in conditions for partial indexes in a CREATE INDEX statement.

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