Re: [sqlite] select all fields of a column in one table that are not in a column in another table
On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all name fields in itms that match neither std_name or raw_name in aliases and where the resulting list of names contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that std_name would be better placed in another table eg std_names and I'm not sure how you'd change the query to reflect the extra table ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8
Hi -- Okay. It looks like sqlite3_win32_mbcs_to_utf8() is being called from a number of functions, including sqlite3_value_blob, sqlite3_randomness, sqlite3_extended_errcode, and sqlite3_os_init.From a spot check of the execution trace it looks like sqlite3_os_init is probably the most frequent caller. But tons and tons of calls to this function are being made, often within the scope of a single prepared statement execution. Here's a pretty typical sample piece of the execution trace: sqldb::sqlite::Sqlite3PreparedStatement::execute sqlite3_step sqlite3_declare_vtab sqlite3_declare_vtab sqlite3_backup_step sqlite3_value_blob sqlite3_value_blob sqlite3_win32_mbcs_to_utf8 sqlite3_os_init sqlite3_win32_mbcs_to_utf8 sqlite3_win32_mbcs_to_utf8 sqlite3_randomness sqlite3_mutex_leave sqlite3_win32_mbcs_to_utf8 sqlite3_os_init sqlite3_strnicmp sqlite3_extended_errcode sqlite3_enable_shared_cache sqlite3_backup_step sqlite3_value_numeric_type sqlite3_backup_pagecount sqlite3_extended_errcode sqlite3_sql sqlite3_enable_shared_cache sqlite3_value_blob sqlite3_value_blob sqlite3_value_blob sqlite3_os_init sqlite3_win32_mbcs_to_utf8 sqlite3_win32_mbcs_to_utf8 I have no way of verifying if the indentation in the snippet above truly accurately reflects call chains, but presumably you will be able to determine this. I have the distinct feeling that something significant fundamental is not configured correctly. Any help you can provide would be greatly appreciated. Also, quick question. Where would be the most appropriate place to issue the PRAGMA you suggest.Does it operate globally or on a per database basis? And if the latter, would I want to issue it before or after actually creating the database schema? Thanks. Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, October 16, 2013 8:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 On Wed, Oct 16, 2013 at 7:51 AM, Mike Clagett mike.clag...@mathworks.comwrote: Hi - We have a C++ (VisualC++) app that is reading from and writing to a sqlite database. Profiling reveals that
Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8
On Thu, Oct 17, 2013 at 8:31 AM, Mike Clagett mike.clag...@mathworks.comwrote: Hi -- Okay. It looks like sqlite3_win32_mbcs_to_utf8() is being called from a number of functions, including sqlite3_value_blob, sqlite3_randomness, sqlite3_extended_errcode, and sqlite3_os_init.From a spot check of the execution trace it looks like sqlite3_os_init is probably the most frequent caller. But tons and tons of calls to this function are being made, often within the scope of a single prepared statement execution. Here's a pretty typical sample piece of the execution trace: sqldb::sqlite::Sqlite3PreparedStatement::execute sqlite3_step sqlite3_declare_vtab sqlite3_declare_vtab This trace looks wrong. sqlite3_step() shouldn't ever be calling sqlite3_declare_vtab(). And sqlite3_declare_vtab() is not recursive. Perhaps your debugger is only seeing exported symbols and is identifying internal routines using the nearest exported symbol? -- 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] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8
Hi -- Okay. This is helpful. It appears the profiler is not doing such a good job at name resolution. So I've been looking down a wrong path. However, there definitely is a problem; whether or not I run the profiler, the app is taking a huge amount of time to execute. So I don't think the profiler is causing the problem. Do you have any ideas about how I might be able to diagnose this? Is there something I can add to the picture to give the profiler a better chance to resolve the symbols correctly? Or is there an alternate way to identify the code spots that are adding this execution time? You mentioned in your first message turning on logging. I've read the link and am going to do this. But perhaps you could save me some time by verifying my understanding. I am writing a component that is called from a larger application. I do not have the opportunity to register anything at the start of the app (or at least I'm not aware of the mechanism to do this). My code is being invoked from an internal application command line long after the app itself has launched. So I would like to activate error logging at the initialization of my component. Am I correct to assume that I can issue this sqlite3_config statement right before I create my database and that as long as my code is the only code using sqlite, this will have the desired effect and install my callback? Clarification of this would be helpful. Thanks. Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, October 17, 2013 8:42 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 On Thu, Oct 17, 2013 at 8:31 AM, Mike Clagett mike.clag...@mathworks.comwrote: Hi -- Okay. It looks like sqlite3_win32_mbcs_to_utf8() is being called from a number of functions, including sqlite3_value_blob, sqlite3_randomness, sqlite3_extended_errcode, and sqlite3_os_init.From a spot check of the execution trace it looks like sqlite3_os_init is probably the most frequent caller. But tons and tons of calls to this function are being made, often within the scope of a single prepared statement execution. Here's a pretty typical sample piece of the execution trace: sqldb::sqlite::Sqlite3PreparedStatement::execute sqlite3_step sqlite3_declare_vtab sqlite3_declare_vtab This trace looks wrong. sqlite3_step() shouldn't ever be calling sqlite3_declare_vtab(). And sqlite3_declare_vtab() is not recursive. Perhaps your debugger is only seeing exported symbols and is identifying internal routines using the nearest exported symbol? -- 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] select all fields of a column in one table that are not in a column in another table
On 10/17/2013 3:35 AM, dean gwilliam wrote: On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all name fields in itms that match neither std_name or raw_name in aliases and where the resulting list of names contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that std_name would be better placed in another table eg std_names and I'm not sure how you'd change the query to reflect the extra table select distinct name from items where name not in (select std_name from std_names) and name not in (select raw_name from aliases); -- or select distinct name from items where name not in ( select std_name from std_names union all select raw_name from aliases ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8
On Thu, Oct 17, 2013 at 9:37 AM, Mike Clagett mike.clag...@mathworks.comwrote: Hi -- Okay. This is helpful. It appears the profiler is not doing such a good job at name resolution. So I've been looking down a wrong path. However, there definitely is a problem; whether or not I run the profiler, the app is taking a huge amount of time to execute. So I don't think the profiler is causing the problem. Do you have any ideas about how I might be able to diagnose this? Is there something I can add to the picture to give the profiler a better chance to resolve the symbols correctly? Or is there an alternate way to identify the code spots that are adding this execution time? This suggests that it isn't really sqlite3_win32_msbc_to_utf8() that is causing the problem. Instead, it is probably some nearby routine like winRead() ro winWrite() - routines that are doing actual file I/O. I'm guessing you have some SQL that is doing a full table scan rather than using an index. The solution is probably just an appropriate CREATE INDEX statement. Consider activating the sqlite3_profile() callback ( http://www.sqlite.org/c3ref/profile.html) in order to see what SQL statements you are running and how long each is taking. That might give a better clue that your profiler. Or activate the sqlite3_trace() callback and use that to capture a script of the SQL you are running. The reply that script using the SQLite command-line shell after activating the .timer on command and/or the .stats on command to get a better idea of which SQL statement or statements are running slow. Or make use of the sqlite3_stmt_status() API ( http://www.sqlite.org/c3ref/stmt_status.html) to find one which SQL statements are doing full table scan or creating automatic indices, or doing large sorts, etc. You mentioned in your first message turning on logging. I've read the link and am going to do this. But perhaps you could save me some time by verifying my understanding. I am writing a component that is called from a larger application. I do not have the opportunity to register anything at the start of the app (or at least I'm not aware of the mechanism to do this). My code is being invoked from an internal application command line long after the app itself has launched. So I would like to activate error logging at the initialization of my component. Am I correct to assume that I can issue this sqlite3_config statement right before I create my database and that as long as my code is the only code using sqlite, this will have the desired effect and install my callback? Clarification of this would be helpful. Yes, I believe that is correct. Of course, our point of view is that SQLite should be a built-in standard component of Matlab so that a full-blown SQL database engine would be available to all Matlab users without having to load an extension or configure an external server. In that case, error logging could be enabled at application launch. :-) -- 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] Table constraints
Joseph L. Casale wrote:
CREATE TABLE t (
id INTEGER NOT NULL,
a VARCHAR NOT NULL COLLATE 'nocase',
b VARCHAR COLLATE 'nocase',
c VARCHAR CHECK (c IN ('foo', 'bar', NULL)) COLLATE 'nocase',
PRIMARY KEY (id)
);
How does one elegantly construct an index or constraint such that for any
row, column a may appear twice
You cannot look at other records without a subquery, which is not
allowed in CHECK constraints. You have to write a trigger instead.
And when you say column a, do you actually mean each distinct value
in column a?
... with column c having a value of 'foo' and 'bar', unless this value
for column a appears with a null value in column c where no other rows
may now exist for that value of column a.
In other words, for each record, there must not exist any other record
with the same value in column a and a value in column c that is either
equal or NULL?
If I have decoded correctly what you were trying to say, use a trigger
like this, and duplicate it for UPDATE:
CREATE TRIGGER t_c_check_insert
AFTER INSERT ON r
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, '...')
FROM t
WHERE a = NEW.a
AND (c = NEW.c OR c IS NULL)
AND id NEW.id;
END;
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select all fields of a column in one table that are not in a column in another table
On 17/10/2013 14:45, Igor Tandetnik wrote: On 10/17/2013 3:35 AM, dean gwilliam wrote: On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all name fields in itms that match neither std_name or raw_name in aliases and where the resulting list of names contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that std_name would be better placed in another table eg std_names and I'm not sure how you'd change the query to reflect the extra table select distinct name from items where name not in (select std_name from std_names) and name not in (select raw_name from aliases); -- or select distinct name from items where name not in ( select std_name from std_names union all select raw_name from aliases ); Igor. Thank you very much indeed! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select all fields of a column in one table that are not in a column in another table
L'accesoire indispensable : http://www.adafruit.com/products/1533 On 17 October 2013 16:29, dean gwilliam mgbg25...@blueyonder.co.uk wrote: On 17/10/2013 14:45, Igor Tandetnik wrote: On 10/17/2013 3:35 AM, dean gwilliam wrote: On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all name fields in itms that match neither std_name or raw_name in aliases and where the resulting list of names contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that std_name would be better placed in another table eg std_names and I'm not sure how you'd change the query to reflect the extra table select distinct name from items where name not in (select std_name from std_names) and name not in (select raw_name from aliases); -- or select distinct name from items where name not in ( select std_name from std_names union all select raw_name from aliases ); Igor. Thank you very much indeed! __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Noël Frankinet Strategis sprl 0478/90.92.54 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select all fields of a column in one table that are not in a column in another table
oups, sorry ! On 17 October 2013 16:36, Noel Frankinet noel.franki...@gmail.com wrote: L'accesoire indispensable : http://www.adafruit.com/products/1533 On 17 October 2013 16:29, dean gwilliam mgbg25...@blueyonder.co.ukwrote: On 17/10/2013 14:45, Igor Tandetnik wrote: On 10/17/2013 3:35 AM, dean gwilliam wrote: On 16/10/2013 22:04, Igor Tandetnik wrote: On 10/16/2013 4:49 PM, dean gwilliam wrote: if I have two tables 1 aliases (std_name, raw_name) 2 items (name..) what would the query look like to select all name fields in itms that match neither std_name or raw_name in aliases and where the resulting list of names contains no duplicates. select distinct name from items where not exists (select 1 from aliases where std_name = name or raw_name = name); Thank you very much Igor. Your answer is very much appreciated. It seems that std_name would be better placed in another table eg std_names and I'm not sure how you'd change the query to reflect the extra table select distinct name from items where name not in (select std_name from std_names) and name not in (select raw_name from aliases); -- or select distinct name from items where name not in ( select std_name from std_names union all select raw_name from aliases ); Igor. Thank you very much indeed! __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Noël Frankinet Strategis sprl 0478/90.92.54 -- Noël Frankinet Strategis sprl 0478/90.92.54 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table constraints
If I have decoded correctly what you were trying to say, use a trigger like this, and duplicate it for UPDATE: Thanks Clemens, this got me sorted out. jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Final preparations for the release of System.Data.SQLite v1.0.89.0 have begun...
If you have any issues with the current code, please report them via this mailing list (and/or by creating a ticket on https://system.data.sqlite.org/;) prior to next Friday, October 25th. Thanks. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_PROTOCOL
Was there any current or future resolution to this issue: http://www.mail-archive.com/sqlite-users@sqlite.org/msg57117.html We seem to be hitting the same problem. We have a series of servers on AWS that use sqlite for backend storage. Under relatively rare circumstances (suddenly we have a lot of concurrent work to do across several processes hitting the same database at once, which coincides with unfavourable EBS latency) we get SQLITE_PROTOCOL error results. The frequency of this error is roughly bi-weekly. But I doubt that information will help because I'm quite confident it is the ganging up of our workload along side EBS slow downs that trigger it. Cheers, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
