[sqlite] Lightweight integrity_check?
After opening, I want to do some basic checking of my database. I’m finding integrity_check much too slow for some of my users (especially with large amounts of data) but I don’t want to just blindly trust the database either. A few options: - Rely on what sqlite does on its own when I start actually using the database - Test for particular tables I expect - Something else Any thoughts? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re-try logic on SQLITE_BUSY/deadlocked?
On 22-May-2009, at 1:11 PM, Rosemary Alles wrote: > Does anyone have solid code examples (in C/C++) or pseudo code of how > to establish re-try code/logic successfully? Just use sqlite3_busy_timeout. http://www.sqlite.org/c3ref/busy_timeout.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_open_v2 and SQLITE_BUSY
I was looking over the requirements for sqlite3_open_v2(), and I'm not clear if this function can ever return SQLITE_BUSY. I initially wrote code to handle this case by sleeping and trying sqlite3_open_v2() again, but it is untested and I've spotted one bug in it already (I wasn't calling sqlite3_close in this case). If I can, I'd like to get rid of the code utterly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3StrICmp
On 04-Apr-2008, at 2:15 PM, Nicolas Williams wrote: > Right, except for the thing about multiple columns with the same name > being OK. "AS" >> 2. I need to use stricmp for comparing column names. I'd rather use >> the same comparison that sqlite3 uses for comparing column NAMES. > > Why can't you use strcasecmp()? Nothing, if you can guarantee me that: strcasecmp( a, b ) == sqlite3StrICmp( a, b ) and stricmp( a, b ) == sqlite3StrICmp( a, b ) ...for all a, and all b, in the present version of sqlite3 and for all future versions of sqlite3, for all our current platforms and all platforms we'll ever deploy to. Otherwise, I'd rather use the same code sqlite3 uses. > IMO a SQLite-specific version of strcasecmp() is only really > valuable if > it can deal with user-defined collations. Otherwise what's the point? > You already have straight strcasecmp() implementations elsewhere (even > ones aware of UTF-8 and UTF-16). See above. I'm not discounting the value of a comparison that deals with collations at all. That would be very useful. It's just not what I'm currently accessing sqlite3 internals for. I'm currently accessing them to find named columns. (I'd rather have a linker error than changed behaviour.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 04-Apr-2008, at 1:17 PM, Nicolas Williams wrote:
> On Fri, Apr 04, 2008 at 01:06:58PM -0700, Steven Fisher wrote:
>>> It's not necessarily the same as strcasecmp(). You can have per-
>>> column collations.
>>
>> Column names, not column contents. :) I don't like to have my C code
>> rely on the order of columns from a query. You can avoid depending on
>> parameter ordering with sqlite3_bind_parameter_index, but there
>> doesn't seem to be an equivalent for result columns.
>
> Sure there is:
>
>const char *sqlite3_column_decltype(sqlite3_stmt*,int);
>int sqlite3_column_type(sqlite3_stmt*, int iCol);
This would be useful but, again, that's not at all what I want. I'm
looking for column NAMES, not contents.
Maybe it'd be better to explain this with psuedo code.
This is what I want to do:
sqlite3_prepare_v2( db, "SELECT ColumnA,ColumnB FROM ATable;", -1,
, );
int column_a_idx = sqlite3_column_index( stmt, "ColumnA" );
int column_b_idx = sqlite3_column_index( stmt, "ColumnB" );
while ( sqlite3_step( db ) == SQLITE_ROW ) {
sqlite3_column_text( stmt, column_a_idx, avalue );
sqlite3_column_text( stmt, column_b_idx, bvalue );
}
sqlite3_fianlize( stmt );
I'm avoiding hard an expectation here that column a is in position 0,
and column b in position 1. This doesn't matter for such a simple
query, but for larger queries future proofing the code from changes to
queries is just good practice.
This code won't run, though, because sqlite3_column_index doesn't
exist. I need to write my own. That means I need to replace
sqlite3_column_index with find_column, which is defined something like
this:
int find_column( sqlite3_stmt * stmt, const char * name )
{
int count = sqlite3_column_count( stmt );
for ( int i = 0; i < count; i++ ) {
const char * column = sqlite3_column_name( stmt, i );
if ( stricmp( column, name ) == 0 )
return i;
}
return -1;
}
There's two problems here:
1. I need to define something to find the column at all. There's a way
to find binding indexes by name, so why not columns? I understand the
need to avoid code bloat, but surely a way to future proof code by not
having to hard-coding column positions is worth the size delta.
2. I need to use stricmp for comparing column names. I'd rather use
the same comparison that sqlite3 uses for comparing column NAMES.
The first problem could be fixed by adding sqlite3_column_index, the
second by adding sqlite3_stricmp. The first would (probably?) increase
the size of sqlite3 slightly, the second would only make an internal
function publicly available.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 04-Apr-2008, at 12:54 PM, Nicolas Williams wrote: > On Fri, Apr 04, 2008 at 12:48:05PM -0700, Steven Fisher wrote: >> On 03-Apr-2008, at 11:22 PM, Matthew L. Creech wrote: >>> We need to either rename it so >>> that it's part of the library's exported API, or do something >>> different in tclsqlite.c. >> >> I would really like to have a few of sqlite3's internal functions >> available to client applications in a straightforward manner. >> sqlite3StrICmp is the top one on my list, though I could see >> sqlite3StrNICmp and sqlite3IsNumber being useful as well. >> >> When comparing column names in my code, for instance, it makes a lot >> of sense to be able to use the same code for comparison that sqlite3 >> uses. Sure, it's probably the same as stricmp/strcasecmp, but will it >> always be so? Probably, but it'd be more future-proof just to use the >> same code. > > It's not necessarily the same as strcasecmp(). You can have per- > column > collations. Column names, not column contents. :) I don't like to have my C code rely on the order of columns from a query. You can avoid depending on parameter ordering with sqlite3_bind_parameter_index, but there doesn't seem to be an equivalent for result columns. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 03-Apr-2008, at 11:22 PM, Matthew L. Creech wrote: > We need to either rename it so > that it's part of the library's exported API, or do something > different in tclsqlite.c. I would really like to have a few of sqlite3's internal functions available to client applications in a straightforward manner. sqlite3StrICmp is the top one on my list, though I could see sqlite3StrNICmp and sqlite3IsNumber being useful as well. When comparing column names in my code, for instance, it makes a lot of sense to be able to use the same code for comparison that sqlite3 uses. Sure, it's probably the same as stricmp/strcasecmp, but will it always be so? Probably, but it'd be more future-proof just to use the same code. I include sqliteInt.h in a small c file just sqlite3StrICmp, in fact. It would be nice not to have to do this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_aggregate_context with C++ classes
Can Final be called without Step first being called? If Step is called, will Final always be called? I know the intention is to call sqlite3_aggregate_context with the size I really need and not allocate anything myself, but I want to store a pointer to a C++ class in the aggregate context. Is it enough to dispose of the pointer in Final, or is this a potential leak? Will calling sqlite3_aggregate_context in Final sometimes do the initial allocation, and if so can it be easily avoided? Thanks... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When I try to .read I get a "can't open" message
On 31-Mar-2008, at 2:11 PM, Douglas McCarroll wrote: > I'm sure I'm doing something simple and obvious wrong here. I'm a > complete > sqlite n00b. > > Help? > > C:\_source>sqlite3 test > SQLite version 3.5.7 > Enter ".help" for instructions > sqlite> .read test.sql; > can't open "test.sql;" You shouldn't be including the semicolon. It's that way for most dot commands. (Wish it was ignored if present; I make this mistake a lot myself.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] since when was fts3 included in binary?
On 2-Mar-2008, at 3:55 AM, Rael Bauer wrote: > It seems that fts3 is now (3.5.6) included in the windows > binary .dll. I'd like to know since when was fts3 included in the > binary? > > Also, since when did the amalgamation include the fts3 sources? According to the web page, 3.5.3. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
On 28-Feb-2008, at 1:29 PM, Yong Zhao wrote: > It seems that sqlite3 does not support DATETIME data type. > > If I have the following data in table t1, how do I select people who > is > older than certain date? Use -MM-DD instead of M/D/Y. Available formats described here under Time Strings: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
On 28-Feb-2008, at 6:22 AM, Mahalakshmi.m wrote: > if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC > WHERE > Name >= '%d%c' LIMIT 1;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) That's not what a bind point looks like. Take a look here: http://www.sqlite.org/c3ref/bind_blob.html I'm not sure you can bind two values in a string this way. Maybe take a look at using one of the sqlite3_printf functions to build the value right into your query string. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Contrib down?
Clicking any of the download links is generating an error:
ERROR: attempt to write a readonly database
attempt to write a readonly database
while executing
"db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}"
invoked from within
"if {[info exists Q(get)]} {
db eval {UPDATE file SET cnt=cnt+1 WHERE rowid=$Q(get)}
content-type application/binary
reply-content [db one {SELEC..."
invoked from within
"::tws::eval [read $fd [file size $argv1]]"
invoked from within
"reply-content [::tws::eval [read $fd [file size $argv1]]]"
invoked from within
"evalfile main.tcl"
(What I'm actually looking for is an example of how to do loadable
functions on Windows, so if anyone has a more direct example of that,
that would be great.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View update performance (was: Updatable views)
On 14-Feb-2008, at 12:27 PM, Stephen Oberholtzer wrote: > I'd love to know > what frame of mind I was in when I wrote it, because I'm pretty sure I > wouldn't have come up with the name 'MaterializeView' if I had tried > to write the patch today. Altered frames of mind are responsible for both the best and worst code in the world. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Web Site
On 6-Feb-2008, at 8:41 PM, Dan wrote: > is not valid HTML. It is valid XHTML of course. All browsers > just ignore the "/" character, but I can't think of any document > where this is defined. Does anybody know? W3C's validator said this: The sequence can be interpreted in at least two different ways, depending on the DOCTYPE of the document. For HMTL 4.01 Strict, the '/' terminates the tag '). However, since many browsers don't interpret it this way, even in the presence of an HMTL 4.01 Strict DOCTYPE, it is best to avoid it completely in pure HTML documents and reserve its use solely for those written in XHTML. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Web Site
On 06-Feb-2008, at 12:33 PM, [EMAIL PROTECTED] wrote: > What do you mean "no longer found"? Do you mean that that > you cannot see anything at all, or that the new design is such > that it is not displayed correctly? Well, the page definitely doesn't validate: http://validator.w3.org/check?uri=http%3A%2F%2Fsqlite.org I don't know if that's the issue or not, of course. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to do this using only SQL?
On 04-Feb-2008, at 3:41 PM, Dennis Volodomanov wrote:
> Is that possible? If not, I'll have to do it in the code, but that
> will
> probably be slower and I'm expecting to have tens of thousands of
> rows.
Sure:
sqlite> create table x(a);
sqlite> insert into x(a) values('ABC');
sqlite> insert into x(a) values('DEF');
sqlite> alter table x add column b;
sqlite> update x set b=lower(a);
sqlite> select * from x;
ABC|abc
DEF|def
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .databases shell command
On 10-Jan-2008, at 1:45 PM, Samuel Gilbert wrote: I though that typing ".mode csv" in your SQLite shell would solve the problem. I though it might be a good idea to test it before suggesting a solution. However, it only works with the results of queries and not with the built-in commands. Well, .databases really just does the column setup and runs a fairly simple query. I can just run that query myself (PRAGMA database_list;) However, I didn't realize that until I read the code to shell.c. So, anyway, when I ran .databases myself and saw the problem, the very first thing I thought was "I'd better increase the column width!" .width 5 5 100 Of course, that didn't help at all, because the width is hard-coded to 58. As someone who didn't know "PRAGMA database_list;", my choice at this point was to read the source or go find the documentation. That's fine, but it'd be better if this documented itself somehow (maybe by repeating the internal query before executing it) or responded to my workaround (which I imagine is one of the first thoughts of many who hit this problem). (As an aside, maybe the auto-formatting isn't right in this case. How many databases do most have open? .mode line might be better here.) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] .databases shell command
Just now I opened a bunch of shells, pasted the same query into all of them, and closed the ones I didn't want after seeing the result. So I ran .databases to see which databases I'm actually left with, and saw something like this (not exact): seq name file --- -- -- 0main C:\Documents and Settings\sfisher\Local Settings\Applicati 1temp C:\DOCUME~1\sfisher\LOCALS~1\Temp\blahblahblah (also too long) Of course, all the databases I opened are in the same directory, so this is really unhelpful in identifying which one I still have open. :) Could the code for .databases be changed to not change the column width to 58 if the width is already set to a larger value? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Explain query plan
On 20-Dec-2007, at 3:02 PM, Kees Nuyt wrote: You will get much more detail with EXPLAIN SELECT ... It shows the VDBE code, which looks cryptic at first but will prove really informative. I'm still at the cryptic phase, but I'll figure it out. Thanks for confirming my suspicions about EXPLAIN QUERY PLAN. Still very useful, though. :) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Explain query plan
So I've been using EXPLAIN QUERY PLAN to try to optimize my queries, and I realized I'm missing something important. It shares what tables are used and what indexes, but as I understand it, it doesn't include whether I'm working entirely off indexes or not. For instance, if I have a line: TABLE Groups WITH INDEX GroupTypeIndex ...does that indicate if GroupTypeIndex fully satisfies the search? How can I tell this? What'd be great is if explain query plan had another column that indicated which columns it had to crawl through... - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DeviceSQL
On 14-Dec-2007, at 5:41 AM, Clay Dowling wrote: I have to say, this discussion has been very informative, although probably not in a way that would make mr Weick happy. I've certainly learned a lot about encirq that tells me what I need to know about doing business with them. Same thought here. Kudos to D. Richard Hipp for keeping his cool under provocation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
On 14-Nov-2007, at 3:37 PM, John Stanton wrote: I am looking at it on a wide screen and it does not render to the full screen width. I would guess that making the toolbar an image would stop the wrapping. The image would scale to 100%. I used to think it was a good thing when web sites took advantage of the full width of my monitor. Then some started doing it. There's nothing worse than trying to read reasonably-sized text that's 20" wide. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
On 13-Nov-2007, at 5:40 PM, [EMAIL PROTECTED] wrote: * Suggestions for something better to put on the home page. Yeah. My first thought when I brought up that page was "There's no way I'm reading all that text!"... and I already use sqlite. I like the points it goes over, though. Maybe just (much) less detail on the main page? Converting to lists (main points bold, small amount of explanation in plain text) would probably be beneficial, too. Lists take less work to skim than a block of text. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [ot] Free formatter?
Does anyone know of a good sql formatter that's compatible with sqlite3's syntax? I'm looking for a command line-based tool with sources that can be cross-compiled to the usual suspects (Win32, Unix, Mac OS X). - To unsubscribe, send email to [EMAIL PROTECTED] -
