date:20140731

Re: [sqlite] Only allow child record if another field in parent is false.

2014-07-31 Thread James K. Lowden

On Fri, 1 Aug 2014 09:26:14 +1200
Richard Warburton  wrote:

> This way, you can't change the leaver field if enrolmentItems are
> attached, and you can't add an enrolmentItem if leaver is set to 1.

IIUC, you have tables A and B,  and the rules are 

you can add/delete rows in B while A.bool is false
you cannot modify B while A.bool is true
A.bool can only become true; it cannot revert to false

This isn't a referential integrity problem, and shouldn't be dealt with
in the DDL.  That's not the answer you wanted, I know.  Please allow me
to explain.  

RI boils down to one thing:  If X exists, Y must exist. It does not say
anything about the state of Y or the number of Xs. Its only job is to
prevent the database equivalent of a dangling pointer: a reference to
something nonexistent.  

I considered a few quasiclever ways to express your rules in RI terms,
but they would require the application to insert/delete/update rows in
other tables to express the boolean state.  For example, you could
have a DoNotUpdate table with FK references to every column in table B
("items").  Then you don't need your boolean column, but you do have to
insert the rows in the new table instead.  It's not clear the database
or the application is thus improved.  

Any rule "do not modify B if X is true in A" (where X is not
manifested elsewhere in the database) is not expressible in relational
terms. It's a business rule, and should be encoded in the application
logic.  

As a matter of fact, there probably will come a time when someone with
sufficient authority *will* revert the boolean to false and *will*
delete/update the item rows.  Probably to correct an error.  In so
doing, from a data modelling perspective, he'll be exercising some
authority not given to everyone to modify the database's representation
of the external reality that it reflects.  But he won't have turned the
data into nonsense, won't have violated the database's logical
constraints.  That alone tells you the difference.  

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

Re: [sqlite] Only allow child record if another field in parent is false.

2014-07-31 Thread Simon Slavin

On 31 Jul 2014, at 10:26pm, Richard Warburton  
wrote:

> I'm looking for an elegant way to prevent enrolments having enrolmentItems
> if leaver is set to 1.
> [...]
> Ideally, I'd like a check in enrolmentItem that can examine a different
> field in the referenced enrolment record.

TRIGGERs are old and cold.  FOREIGN KEYs are teh noo secsay.

How about coding it into a FOREIGN KEY requirement for enrolmentItem ?

You would normally do something like

FOREIGN KEY(enrolment) REFERENCES enrolment(id) ON DELETE RESTRICT ON ACTION 
RESTRICT

but you might be able to do

FOREIGN KEY(enrolment,0) REFERENCES enrolment(id,leaver) ON DELETE RESTRICT ON 
ACTION RESTRICT

or possibly

FOREIGN KEY(0,enrolment) REFERENCES enrolment(leaver,id) ON DELETE RESTRICT ON 
ACTION RESTRICT

To make this happen you'd want an index on the enrolment table which reflected 
the foreign key.

I'm not actually sure whether you can use constants in foreign keys like this.  
You'll have to try it.  If it's not allowed, you'll need to create a dummy 
column in enrolmentItem which always contains 0.

See section 3 -- well, all of it, really -- of

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

[sqlite] Only allow child record if another field in parent is false.

2014-07-31 Thread Richard Warburton

Hi,

Consider:

CREATE TABLE enrolment (
id  INTEGER PRIMARY KEY,
child   INT NOT NULL REFERENCES child (id),
 start   INT NOT NULL,
leaver  INT NOT NULL DEFAULT 0 CHECK (leaver BETWEEN 0 AND 1), -- BOOL
);

CREATE TABLE enrolmentItem (
id  INTEGER PRIMARY KEY,
enrolment   INT NOT NULL REFERENCES enrolment (id) ON DELETE CASCADE,
 day INT NOT NULL,
start   INT NOT NULL,
end INT NOT NULL CHECK (start < end),
);

I'm looking for an elegant way to prevent enrolments having enrolmentItems
if leaver is set to 1.
This way, you can't change the leaver field if enrolmentItems are attached,
and you can't add an enrolmentItem if leaver is set to 1.

I think 2 triggers could work, but it seems overkill.  I could have a
leaver field in enrolmentItem referencing leaver in enrolment and put a
check against that, but I'd be storing unneeded data.

Ideally, I'd like a check in enrolmentItem that can examine a different
field in the referenced enrolment record.

Suggestions?

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

Re: [sqlite] Misleading example of INSTEAD OF trigger

2014-07-31 Thread Richard Hipp

On Thu, Jul 31, 2014 at 2:42 PM, Christian Höhne  wrote:

> The CREATE TRIGGER example shown in the INSTEAD OF page (
> http://www.sqlite.org/lang_createtrigger.html#instead_of_trigger) is
> misleading because it does not show the correct use of the INSTEAD OF
> clause in the trigger statement, it actually misses this clause altogether:
>

The "Examples" section is suppose to cover the entire document, not just
the INSTEAD OF trigger.  Though I admit that is not exactly clear.  I will
reword it.


>
> Examples
>
> Assuming that customer records are stored in the "customers" table, and
> that order records are stored in the "orders" table, the following trigger
> ensures that all associated orders are redirected when a customer changes
> his or her address:
>
> CREATE TRIGGER update_customer_address UPDATE OF address ON customers
>   BEGIN
> UPDATE orders SET address = new.address WHERE customer_name = old.name
> ;
>   END;
>
> It should read:
>
> CREATE TRIGGER update_customer_address *INSTEAD OF* UPDATE OF address
> ON customers
>
> --
> Christian Hoehne
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Misleading example of INSTEAD OF trigger

2014-07-31 Thread Christian Höhne

The CREATE TRIGGER example shown in the INSTEAD OF page (
http://www.sqlite.org/lang_createtrigger.html#instead_of_trigger) is
misleading because it does not show the correct use of the INSTEAD OF
clause in the trigger statement, it actually misses this clause altogether:

Examples

Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

It should read:

CREATE TRIGGER update_customer_address *INSTEAD OF* UPDATE OF address
ON customers

-- 
Christian Hoehne
___
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

2014-07-31 Thread Richard Hipp

I can't find any difference in the query plans for 3.7.17 and 3.8.5.

Can you send me the database file (as an attachment to a private email) so
that I can run experiments on the original content?


On Thu, Jul 31, 2014 at 10:06 AM, Michael  wrote:

> I also tried to remove the views of some queries and create index. But
> nothing worked so far.
> Anyway it couldn't be the reason for the "parser stack overflow" of my
> other question.
> Is the information I sent sufficient?
>
> *Gesendet:* Dienstag, 29. Juli 2014 um 15:17 Uhr
> *Von:* Michael 
> *An:* sqlite-users@sqlite.org
> *Betreff:* Re: [sqlite] Long execution time since sqlite 3.8
> 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

Re: [sqlite] Long execution time since sqlite 3.8

2014-07-31 Thread Richard Hipp

I have attempted to reformat your query to make it more readable.  Did I
translate it correctly?

CREATE TABLE infos(
  idINTEGER PRIMARY KEY,
  name  TEXT,
  text  TEXT,
  zone  INTEGER DEFAULT 3,
  dateAdded DATATIME,
  lastModified  DATETIME,
  votes INTEGER DEFAULT 0 NOT NULL,
  ratingINTEGER DEFAULT 0 NOT NULL,
  expirationDATETIME,
  exp_actionINTEGER,
  date  DATETIME
);
CREATE TABLE category(
  idINTEGER PRIMARY KEY,
  name  TEXT UNIQUE,
  parentINTEGER,
  zone  INTEGER
);
CREATE TABLE category_item(
  idINTEGER 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 category_alias(
  idINTEGER PRIMARY KEY,
  category_id   INTEGER,
  name  STRING UNIQUE
);
CREATE TABLE zones(
  idINTEGER PRIMARY KEY,
  zones INTEGER,
  zone  INTEGER,
  UNIQUE(zones,zone)
);
CREATE VIEW category_info AS
  SELECT category_id, item_id
FROM category_item
   WHERE item_type_id=1;


EXPLAIN QUERY PLAN
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;



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

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



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

2014-07-31 Thread Michael

I also tried to remove the views of some queries and create index. But
nothing worked so far.
Anyway it couldn't be the reason for the "parser stack overflow" of my
other question.
Is the information I sent sufficient?

*Gesendet:* Dienstag, 29. Juli 2014 um 15:17 Uhr
*Von:* Michael 
*An:* sqlite-users@sqlite.org
*Betreff:* Re: [sqlite] Long execution time since sqlite 3.8
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

Re: [sqlite] Logging and timing queries

2014-07-31 Thread Simon Slavin

On 31 Jul 2014, at 1:41pm, Ketil Froyn  wrote:

> I find it would be
> very useful to log some or all queries sent to the database, ideally also
> with timing information on how long it took to gather and send the
> response, so that it can be determined what queries are received and how
> long each query actually takes.

This probably wouldn't get built into the standard SQLite library.  Bear in 
mind that of the literally billions of installations of SQLite, the vast 
majority are on smartphones, DVRs, set-top boxes, embedded controllers, or 
other small devices.  So adding code to SQLite is rarely done just for things 
which would be done only on 'normal' computers.

For the people who want it, it's easy to build a shell around the SQLite calls 
like this:

int pseudo_sqlite3_exec(commandString) {
log commandString
log start time
int theResult = sqlite3_exec(commandString)
log finish time
return theResult
}

Log to a text file, and write a utility to analyse the text file later.

SQLite does, however, include calls which can be used to monitor changes made 
to the database:

SQLite also includes a command specifically made for spotting badly-optimized 
combinations of commands and databases:

You can feed any statement to this and /instead of executing it/ SQLite will 
list the steps it would take to execute it.  So you can use it for testing 
without having to worry that your database will be changed.  If the query plan 
for a commonly used command includes scanning a long table (rather that using 
an index, which is shown differently) you think about and rephrasing it or 
creating a good index for it.

You can even execute EXPLAIN QUERY PLAN in the SQLite shell tool, which outputs 
the results neatly, and means you don't have to hack up your own software just 
for testing purposes.

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

Re: [sqlite] Logging and timing queries

2014-07-31 Thread Dominique Pellé

Ketil Froyn  wrote:

> Hi,
>
> In debugging and inspecting applications using sqlite, I find it would be
> very useful to log some or all queries sent to the database, ideally also
> with timing information on how long it took to gather and send the
> response, so that it can be determined what queries are received and how
> long each query actually takes. It's possible to do this at the application
> level, but I think it would be very useful to do this using sqlite itself.
> If detailed query logging could be enabled in the database with a pragma or
> by using a debug version of the libarry somehow, it would let users and
> developers get standardised details about what queries are run and how long
> they take, even temporarily on a live system.
>
> There's lots of situations where this could be useful, like switching on
> and off debugging on the fly in live/production systems to identify
> transient problems or bottlenecks, and debugging software using sqlite with
> a live dataset without necessarily having to recreate the working
> conditions in a controlled environment, and without recompiling, and
> without developing/enabling support for sql debugging in the application.
>
> I've used mysql-proxy in the past to debug mysql databases in this way, and
> it was very effective. But since there's no network connection involved in
> sqlite, this method can't be used to temporarily redirect the queries in
> the same way.
>
> Have I missed a way to do something like this in sqlite already, or would
> you regard this as a feature request?
>
> Thanks, Ketil

Hi Ketil

SQLite allows you to that.  See:

sqlite3_profile(...);
sqlite3_trace(...);

http://www.sqlite.org/c3ref/profile.html

You call sqlite3_profile(...) once to register a callback.
I use something like this:

sqlite3_profile(db, SqliteProfileCallback, db);

And for the callback, I use something like this:

static void SqliteProfileCallback(void* aDb, const char* aQueryStr,
sqlite3_uint64 aTimeInNs)
{
  sqlite3* db = static_cast(aDb);
  const char* dbName = sqlite3_db_filename(db, "main");

  // Statistics per DB connection.
  // See: http://www.sqlite.org/c3ref/db_status.html
  int cacheUsed[2]  = { 0, 0 };
  int schemaUsed[2] = { 0, 0 };
  int stmtUsed[2]   = { 0, 0 };
  int cacheHit[2]   = { 0, 0 };
  int cacheMiss[2]  = { 0, 0 };
  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED,  &cacheUsed[0],
&cacheUsed[1],  0);
  sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &schemaUsed[0],
&schemaUsed[1], 0);
  sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED,   &stmtUsed[0],
&stmtUsed[1],   0);
  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_HIT,   &cacheHit[0],
&cacheHit[1],   0);
  sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS,  &cacheMiss[0],
&cacheMiss[1],  0);

  fprintf(stderr, "SQLite profile: msec=[%llu] mem/high/lim=[%lld/%lld/%lld] "
 "schema=[%d] stmt=[%d] cache=[%d]
hit/miss=[%d/%d] db=[%s] sql=[%s]",
   aTimeInNs/100, // Time taken by the query
in milliseconds.

   sqlite3_memory_used(), // Global memory used by
SQLite now in bytes.
   sqlite3_memory_highwater(0),   // Global high water mark of
memory used by SQLite in bytes.
   sqlite3_soft_heap_limit64(-1), // Global current heap limit
in bytes (a hint only).

   schemaUsed[0], // Memory used by this
connection for the schema.
   stmtUsed[0],   // Memory used by this
connection for statements.
   cacheUsed[0],  // Memory used by this
connection for cache.

   cacheHit[0], cacheMiss[0], // SQLite cache hit/miss stats.
   dbName, aQueryStr);
}

That gives lots of useful information to find slow queries, memory
usage, etc.  But I'm curious whether there is anything else useful
that could be logged inside the profile or trace callback.

One thing that I would like, is to be able to call sqlite3_stmt_status(...)
to get information about the statement inside the callback. But
the sqlite3_stmt object is not available inside the callback
unfortunately and I don't see how to make it available, other
than changing SQLite source (which I have not tried). It would
have been nice it it was available somehow.

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

Re: [sqlite] Sqlite querie delete bug

2014-07-31 Thread Rob Richardson

This doesn't sound like an SQLite problem to me.  Instead it sounds like a 
problem with whatever visualization tool you are using.  What tool are you 
using?  If you can find a user's group for that tool, you may get more helpful 
answers there.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Oto _
Sent: Wednesday, July 30, 2014 8:53 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite querie delete bug

There is an issue that if entry is selected in Table view then editing in Query 
view and clicking delete key then it doesn't delete query but asks to delete 
database entry which is not expected behavior.
___
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] Logging and timing queries

2014-07-31 Thread Ketil Froyn

Hi,

In debugging and inspecting applications using sqlite, I find it would be
very useful to log some or all queries sent to the database, ideally also
with timing information on how long it took to gather and send the
response, so that it can be determined what queries are received and how
long each query actually takes. It's possible to do this at the application
level, but I think it would be very useful to do this using sqlite itself.
If detailed query logging could be enabled in the database with a pragma or
by using a debug version of the libarry somehow, it would let users and
developers get standardised details about what queries are run and how long
they take, even temporarily on a live system.

There's lots of situations where this could be useful, like switching on
and off debugging on the fly in live/production systems to identify
transient problems or bottlenecks, and debugging software using sqlite with
a live dataset without necessarily having to recreate the working
conditions in a controlled environment, and without recompiling, and
without developing/enabling support for sql debugging in the application.

I've used mysql-proxy in the past to debug mysql databases in this way, and
it was very effective. But since there's no network connection involved in
sqlite, this method can't be used to temporarily redirect the queries in
the same way.

Have I missed a way to do something like this in sqlite already, or would
you regard this as a feature request?

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

Re: [sqlite] Sqlite querie delete bug

2014-07-31 Thread Richard Hipp

On Wed, Jul 30, 2014 at 8:53 AM, Oto _  wrote:

> There is an issue that if entry is selected in Table view then editing in
> Query view and clicking delete key then it doesn't delete query but asks to
> delete database entry which is not expected behavior.
>

It sounds as if you are describing a problem with a third-party database
access application, not with the core SQLite library.  This mailing list
covers the core SQLite library.  Please contact your vendor for support
with third-party tools that use SQLite.

-- 
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] Sqlite querie delete bug

2014-07-31 Thread Oto _

There is an issue that if entry is selected in Table view then editing in
Query view and clicking delete key then it doesn't delete query but asks to
delete database entry which is not expected behavior.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling Timezones

2014-07-31 Thread Rob Willett

The problem with doing this is that many IPs addresses are exit IP addreses, 
I.e the ISP or company brings things into their own network and only has a few 
exit nodes, so regardless of where you connect from, everybody comes out of one 
time zone. I know AOL used to do this and I know that IBM does this. 
Disclaimer, I work for IBM and can't access some parts of the BBC website as it 
thinks I'm calling from Germany when I'm in London.  

My own ISP puts my exit node a few hundred miles from where I am and I'm only 
in the UK. 

These databases are best guesses only and so long as you know that them use 
them accordingly. 

Previous suggestions of using the browser or client computer settings seem 
sensible to me.  

-- 
Rob Willett
Sent from my mobile phone

On Thursday, 31 July 2014 at 08:54, Stephen Chrzanowski wrote:

> Looking back at the clarification of what the OP wanted to do, I've got
> this to recommend;
> 
> If your users are talking to your server via the internet and not via a VPN
> connection, instead of relying on what time zone your users browser is
> giving you, look at what IP they're calling in from and do an IP to
> geographical look up to find out where they are. From there you'd be able
> to catalog a 'best time of contact' based on what the Geolocation service
> gives you.
> 
> Doing a quick google search on "ip to geo" I found these two:
> http://www.iplocation.net/ and http://www.geoiptool.com/
> 
> By the looks of it, for a modest yearly fee, you'd be able to download a
> database of IPs to locations and you'd be able to get time zone information
> right from there.
> ___
> 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

2014-07-31 Thread Donald Shepherd

Speaking as someone who's work routes their internet traffic through a
gateway in Phoenix, AZ despite being based in Australia, guessing time
zones based off IP location is a lot more prone to error than detecting it
based off the client.


On 31 July 2014 17:54, Stephen Chrzanowski  wrote:

> Looking back at the clarification of what the OP wanted to do, I've got
> this to recommend;
>
> If your users are talking to your server via the internet and not via a VPN
> connection, instead of relying on what time zone your users browser is
> giving you, look at what IP they're calling in from and do an IP to
> geographical look up to find out where they are.  From there you'd be able
> to catalog a 'best time of contact' based on what the Geolocation service
> gives you.
>
> Doing a quick google search on "ip to geo" I found these two:
> http://www.iplocation.net/ and http://www.geoiptool.com/
>
> By the looks of it, for a modest yearly fee, you'd be able to download a
> database of IPs to locations and you'd be able to get time zone information
> right from there.
> ___
> 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

2014-07-31 Thread Stephen Chrzanowski

Looking back at the clarification of what the OP wanted to do, I've got
this to recommend;

If your users are talking to your server via the internet and not via a VPN
connection, instead of relying on what time zone your users browser is
giving you, look at what IP they're calling in from and do an IP to
geographical look up to find out where they are.  From there you'd be able
to catalog a 'best time of contact' based on what the Geolocation service
gives you.

Doing a quick google search on "ip to geo" I found these two:
http://www.iplocation.net/ and http://www.geoiptool.com/

By the looks of it, for a modest yearly fee, you'd be able to download a
database of IPs to locations and you'd be able to get time zone information
right from there.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Only allow child record if another field in parent is false.

Re: [sqlite] Only allow child record if another field in parent is false.

[sqlite] Only allow child record if another field in parent is false.

Re: [sqlite] Misleading example of INSTEAD OF trigger

[sqlite] Misleading example of INSTEAD OF trigger

Re: [sqlite] Long execution time since sqlite 3.8

Re: [sqlite] Long execution time since sqlite 3.8

Re: [sqlite] Long execution time since sqlite 3.8

Re: [sqlite] Logging and timing queries

Re: [sqlite] Logging and timing queries

Re: [sqlite] Sqlite querie delete bug

[sqlite] Logging and timing queries

Re: [sqlite] Sqlite querie delete bug

[sqlite] Sqlite querie delete bug

Re: [sqlite] Handling Timezones

Re: [sqlite] Handling Timezones

Re: [sqlite] Handling Timezones

17 matches

Site Navigation

Mail list logo

Footer information