Re: [sqlite] problem building reduced-size sqlite
I checked in some updates to the "configure" support that will hopefully do the right thing and pass any OMIT options to lemon and mkkeywordhash. There was also a minor fix to handle SQLITE_OMIT_VIEW being defined while SQLITE_OMIT_SUBQUERY is undefined in select.c (something you probably rand into with your example.) Also note that in your example, -DTEMP_STORE=2 should probably be replaced with some variation of the --enable-tempstore configure option. HTH. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] format the date time
"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> I have the following statement:
> select CURRENT_TIMESTAMP;
> the output is :
> 2008-07-22 01:10:34
> and I would like to have the following output:
> TUE JULY 22 01:10:34 2008
> Is there any function that I can format the data to show as above.
select
(case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON ... when 6
then 'SAT' end)
|| ' ' ||
(case strftime('%m', d) when 1 then 'JAN' ... when 12 then 'DEC' end)
|| ' ' ||
strftime('%d %H:%M:%S %Y', d)
from (select CURRENT_TIMESTAMP as d);
--
With best wishes,
Igor Tandetnik
With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead. -- RFC 1925
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] format the date time
Hi All, I have the following statement: select CURRENT_TIMESTAMP; the output is : 2008-07-22 01:10:34 and I would like to have the following output: TUE JULY 22 01:10:34 2008 Is there any function that I can format the data to show as above. Thanks JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
> > That was it! It seems to work now. > > // new code > int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, > ((alen < blen) ? alen : blen) / sizeof(wchar_t)); > > Is the fact that the callback strings are not NUL terminated and the > lengths are in bytes documented anywhere? If not, it might be a good > idea. If they are documented, can someone point me to the url? I would > like to see if there are any other gotchas before relying on this. > > csmith > I appreciate everyone's help with this issue. The below documentation is from here http://www.sqlite.org/c3ref/create_collation.html "The remaining arguments to the application-supplied routine are two strings, each represented by a (length, data) pair and encoded in the encoding that was passed as the third argument when the collation sequence was registered." May I suggest: "...are two strings, excluding NULs, each represented by a (length in bytes not chars, data) pair and encoded..." That would most likely clear up any confusion. When I read that paragraph initially, "two strings" made me think NUL terminated. When I think string in C I think NUL terminated. The "(length, data) pair encoded" part made me think that the length matched the encoding; meaning character count. Once again, thanks for helping me out. csmith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Format of the data
Hi All, I am current using sqlite 3.5.9 and below is command to get the data from my database as: .output '/opt/phoenix/monitor/exportData' .mode csv select '#Monitored applications' , group_concat(appName) from appMapTable; The output is: "#Monitored applications","new1,nsc1,Oracle,MSSQL,Sybase,Exchange,Informix,DB2,SQLite3,MySql,nsc2,nsc3" but I would like to have the format below: #Monitored applications, new1,nsc1,Oracle,MSSQL,Sybase,Exchange,Informix,DB2,SQLite3,MySql,nsc2,nsc3 (without ") Can I get the output without double quote. Thanks, JP ___ 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] hello, new to list
Greg Morphis <[EMAIL PROTECTED]> wrote: > I've been playing around with SQLite for a few days now.. It's pretty > impressive. > I was curious if there were any plans to support right outer joins and > full outer joins? > I was hoping to utilize SQLite for a couple home projects I have > created in Postgres. > I don't think I need a huge horse like Postgres for what I'm doing but > in a couple queries I use right outer joins. Chances are high these queries could be reformulated in terms of left joins, if you are willing to consider such an approach. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS index size
On Fri, Jul 18, 2008 at 12:54 AM, Jiri Hajek <[EMAIL PROTECTED]> wrote:
> from what I have read about FTS3, it stores the original data as well as the
> index needed for fast full-text access. Therefore, in several posts here it
> was recommended to use two tables joined one-to-one in case it's needed to
> store both text data and some other, possibly numeric data. Let's use an
> example:
>
> Table1:
> ID Int
> Value1 Int
> Value2 Int
>
> Table2 (FTS3):
> Text1
> Text2
>
> However, according to my tests, the join needed in case I want to get all
> data in tables Table1 and Table2 takes some time, it looks like it's almost
> twice as slow as having all the data in one table only.
It's hard to say - you might want to describe your tables and the join
by simply including the SQL itself, rather than a loose description of
it. It may be that there's a simple change which will clear things
up.
> My question to SQLite experts is whether there is any solution to this
> choice between speed and DB size. Preferrably I'd need to specify that just
> some fields of an ordinary table participate in FTS3 index, but that
> obviously isn't possible, at least not yet. Are there any plans for this?
No plans at this time. The goal of the current functionality
breakdown was to keep SQLite core from having to understand too much
about FTS, and FTS from having to understand too much SQL. For
instance, one way you could accomplish this would be to have the FTS
create statement be able to handle something like this:
CREATE VIRTUAL TABLE t USING fts3(
ID integer primary key autoincrement,
Value1 INTEGER,
Value2 INTEGER,
Text1 TEXT,
Text2 TEXT
);
As you start making it more and more powerful, FTS has to handle
things like "Text1 TEXT UNIQUE" or "Text1 TEXT CHECK(Text1 IN ('a',
'b', 'c'))", and make the appropriate decisions based on that, and in
the limit FTS becomes a little SQL engine of its own. I've thought a
little about how you could simply reflect things into the backing
tables, but it seems likely that there will be some subtle issues to
think through. Even then, you would need some way to have the
equivalent of a non-FTS index on such a table, I have no idea how to
model that.
-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] hello, new to list
I've been playing around with SQLite for a few days now.. It's pretty impressive. I was curious if there were any plans to support right outer joins and full outer joins? I was hoping to utilize SQLite for a couple home projects I have created in Postgres. I don't think I need a huge horse like Postgres for what I'm doing but in a couple queries I use right outer joins. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem building reduced-size sqlite
Steve Friedman wrote:
>
>case 275: /* trigger_cmd ::= UPDATE orconf nm SET setlist
> where_opt */
> { yygotominor.yy243 = sqlite3TriggerUpdateStep(pParse->db,
> &yymsp[-3].minor.yy0, yymsp[-1].minor.yy174, yymsp[0].minor.yy172,
> yymsp[-4].minor.yy46); }
> break;
>case 276: /* trigger_cmd ::= insert_cmd INTO nm inscollist_opt
> VALUES LP itemlist RP */
> {yygotominor.yy243 = sqlite3TriggerInsertStep(pParse->db,
> &yymsp[-5].minor.yy0, yymsp[-4].minor.yy432, yymsp[-1].minor.yy174, 0,
> yymsp[-7].minor.yy46);}
> break;
>case 277: /* trigger_cmd ::= insert_cmd INTO nm inscollist_opt
> select */
> {yygotominor.yy243 = sqlite3TriggerInsertStep(pParse->db,
> &yymsp[-2].minor.yy0, yymsp[-1].minor.yy432, 0, yymsp[0].minor.yy219,
> yymsp[-4].minor.yy46);}
> break;
>case 278: /* trigger_cmd ::= DELETE FROM nm where_opt */
> {yygotominor.yy243 = sqlite3TriggerDeleteStep(pParse->db,
> &yymsp[-1].minor.yy0, yymsp[0].minor.yy172);}
> break;
>case 279: /* trigger_cmd ::= select */
> {yygotominor.yy243 = sqlite3TriggerSelectStep(pParse->db,
> yymsp[0].minor.yy219); }
>
>
These items should have been deleted when the parse.c file was created.
They were not since the omit options are not passed to the lemon
compiler by the makefile generated by the configure script.
The sqlite3Trigger* functions are referenced by parse.c but they are
omitted from the trigger.c code by the CFLAGS. So these functions are
assumed to return an integer (since they lack a prototype). The compiler
is complaining about the implied integer to pointer conversion.
To fix this you will need to manually edit the configure generated
makefile and add a line that defines the OPTS variable which will be
passed to lemon. I tested with the SQLITE_OMIT_TRIGGER option only, but
you should probably add all the options you are omitting.
OPTS = D=SQLITE_OMIT_TRIGGER D=SQLITE_OMIT_ALTERTABLE ...
>> Can you post the contents of these lines from your custom built
>> sqlite3.c file? They are all together (13 lines) and might provide some
>> context about these warnings.
>>
>>> sqlite3.c: In function ‘rtreeCreate’:
>>> sqlite3.c:91283: warning: cast from pointer to integer of different size
>
>return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux);
>
>>> sqlite3.c: In function ‘rtreeConnect’:
>>> sqlite3.c:91296: warning: cast from pointer to integer of different size
>
>return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 0, (int)pAux);
>
This a normal warning about the cast from a pointer (64 bit) to an
integer (32 bit) where the pAux argument is cast above.
>
>> Same goes for these lines.
>>
>>> sqlite3.c: At top level:
>>> sqlite3.c:9677: warning: ‘sqlite3CreateView’ used but never defined
> SQLITE_PRIVATE void
> sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int,int);
>
> #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
> SQLITE_PRIVATE int sqlite3ViewGetColumnNames(Parse*,Table*);
> #else
> # define sqlite3ViewGetColumnNames(A,B) 0
> #endif
>
>
>>> sqlite3.c:9732: warning: ‘sqlite3Vacuum’ used but never defined
> SQLITE_PRIVATE void sqlite3Vacuum(Parse*);
> SQLITE_PRIVATE int sqlite3RunVacuum(char**, sqlite3*);
>
>>> sqlite3.c:9781: warning: ‘sqlite3MaterializeView’ used but never defined
>>> sqlite3.c:9823: warning: ‘sqlite3Attach’ used but never defined
>>> sqlite3.c:9824: warning: ‘sqlite3Detach’ used but never defined
>>> sqlite3.c:9912: warning: ‘sqlite3AlterRenameTable’ used but never defined
>>> sqlite3.c:9919: warning: ‘sqlite3AlterFinishAddColumn’ used but never
>>> defined
>>> sqlite3.c:9920: warning: ‘sqlite3AlterBeginAddColumn’ used but never defined
>>> sqlite3.c:9923: warning: ‘sqlite3Analyze’ used but never defined
>> This looks like some references to these functions were not omitted even
>> though the functions themselves were. I think these are all just
>> warnings because these functions could be provided by a different file
>> at link time.
>
These will also be corrected by passing he appropriate omit flags to
lemon. The parser is still referencing these files even though they have
been omitted from the source.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem building reduced-size sqlite
I rebaselined to the latest CVS as of this morning before rerunning the
make command, so the lines have shifted, but the same issues as before.
Steve
Dennis Cote wrote:
> Steve Friedman wrote:
>> libtool: compile: gcc -g -O3 -DSQLITE_ENABLE_RTREE=1 -DTEMP_STORE=2
>> -DSQLITE_DEFAULT_CACHE_SIZE=100 -DSQLITE_DEFAULT_PAGE_SIZE=32768
>> -DSQLITE_OMIT_ALTERTABLE=1 -DSQLITE_OMIT_ANALYZE=1
>> -DSQLITE_OMIT_ATTACH=1 -DSQLITE_OMIT_AUTHORIZATION=1
>> -DSQLITE_OMIT_AUTOVACUUM=1 -DSQLITE_OMIT_BLOB_LITERAL=1
>> -DSQLITE_OMIT_DATETIME_FUNCS=1 -DSQLITE_OMIT_EXPLAIN=1
>> -DSQLITE_OMIT_FOREIGN_KEY=1 -DSQLITE_OMIT_PROGRESS_CALLBACK=1
>> -DSQLITE_OMIT_TRACE=1 -DSQLITE_OMIT_TRIGGER=1 -DSQLITE_OMIT_UTF16
>> -DSQLITE_OMIT_VIEW=1 -DSQLITE_OS_UNIX=1 -I. -I../sqlite-latest/src
>> -D_HAVE_SQLITE_CONFIG_H -DNDEBUG -DSQLITE_THREADSAFE=0
>> -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c
>> sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o
>> sqlite3.c: In function ‘yy_reduce’:
>> sqlite3.c:78433: warning: assignment makes pointer from integer without
>> a cast
>> sqlite3.c:78436: warning: assignment makes pointer from integer without
>> a cast
>> sqlite3.c:78439: warning: assignment makes pointer from integer without
>> a cast
>> sqlite3.c:78442: warning: assignment makes pointer from integer without
>> a cast
>> sqlite3.c:78445: warning: assignment makes pointer from integer without
>> a cast
>
case 275: /* trigger_cmd ::= UPDATE orconf nm SET setlist
where_opt */
{ yygotominor.yy243 = sqlite3TriggerUpdateStep(pParse->db,
&yymsp[-3].minor.yy0, yymsp[-1].minor.yy174, yymsp[0].minor.yy172,
yymsp[-4].minor.yy46); }
break;
case 276: /* trigger_cmd ::= insert_cmd INTO nm inscollist_opt
VALUES LP itemlist RP */
{yygotominor.yy243 = sqlite3TriggerInsertStep(pParse->db,
&yymsp[-5].minor.yy0, yymsp[-4].minor.yy432, yymsp[-1].minor.yy174, 0,
yymsp[-7].minor.yy46);}
break;
case 277: /* trigger_cmd ::= insert_cmd INTO nm inscollist_opt
select */
{yygotominor.yy243 = sqlite3TriggerInsertStep(pParse->db,
&yymsp[-2].minor.yy0, yymsp[-1].minor.yy432, 0, yymsp[0].minor.yy219,
yymsp[-4].minor.yy46);}
break;
case 278: /* trigger_cmd ::= DELETE FROM nm where_opt */
{yygotominor.yy243 = sqlite3TriggerDeleteStep(pParse->db,
&yymsp[-1].minor.yy0, yymsp[0].minor.yy172);}
break;
case 279: /* trigger_cmd ::= select */
{yygotominor.yy243 = sqlite3TriggerSelectStep(pParse->db,
yymsp[0].minor.yy219); }
> Can you post the contents of these lines from your custom built
> sqlite3.c file? They are all together (13 lines) and might provide some
> context about these warnings.
>
>> sqlite3.c: In function ‘rtreeCreate’:
>> sqlite3.c:91283: warning: cast from pointer to integer of different size
return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux);
>> sqlite3.c: In function ‘rtreeConnect’:
>> sqlite3.c:91296: warning: cast from pointer to integer of different size
>
return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 0, (int)pAux);
> Same goes for these lines.
>
>> sqlite3.c: At top level:
>> sqlite3.c:9677: warning: ‘sqlite3CreateView’ used but never defined
SQLITE_PRIVATE void
sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int,int);
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
SQLITE_PRIVATE int sqlite3ViewGetColumnNames(Parse*,Table*);
#else
# define sqlite3ViewGetColumnNames(A,B) 0
#endif
>> sqlite3.c:9732: warning: ‘sqlite3Vacuum’ used but never defined
SQLITE_PRIVATE void sqlite3Vacuum(Parse*);
SQLITE_PRIVATE int sqlite3RunVacuum(char**, sqlite3*);
>> sqlite3.c:9781: warning: ‘sqlite3MaterializeView’ used but never defined
>> sqlite3.c:9823: warning: ‘sqlite3Attach’ used but never defined
>> sqlite3.c:9824: warning: ‘sqlite3Detach’ used but never defined
>> sqlite3.c:9912: warning: ‘sqlite3AlterRenameTable’ used but never defined
>> sqlite3.c:9919: warning: ‘sqlite3AlterFinishAddColumn’ used but never
>> defined
>> sqlite3.c:9920: warning: ‘sqlite3AlterBeginAddColumn’ used but never defined
>> sqlite3.c:9923: warning: ‘sqlite3Analyze’ used but never defined
>
> This looks like some references to these functions were not omitted even
> though the functions themselves were. I think these are all just
> warnings because these functions could be provided by a different file
> at link time.
Yes, it seems that they should be elided by the pre-processor, but
lacked the appropriate ifdef's.
>
>> libtool: compile: gcc -g -O3 -DSQLITE_ENABLE_RTREE=1 -DTEMP_STORE=2
>> -DSQLITE_DEFAULT_CACHE_SIZE=100 -DSQLITE_DEFAULT_PAGE_SIZE=32768
>> -DSQLITE_OMIT_ALTERTABLE=1 -DSQLITE_OMIT_ANALYZE=1
>> -DSQLITE_OMIT_ATTACH=1 -DSQLITE_OMIT_AUTHORIZATION=1
>> -DSQLITE_OMIT_AUTOVACUUM=1 -DSQLITE_OMIT_BLOB_LITERAL=1
>> -DSQLITE_OMIT_DATETIME_FUNCS=1 -DSQLITE_OMIT_EXPLAIN=1
>> -DSQLITE_OMIT_FOREIGN_KEY=1 -DSQLITE_OMIT_PROGRESS_CALLBACK=1
>> -DSQLITE_OMIT_TRACE=1 -DSQLITE_OMIT_TRIGGER=
[sqlite] sqite wildcard search with indexing
Salutations, Is it possible to do a simple wildcard/regexp-type search in sqlite databases, but with indexing? For example, if I want to search for the string "aCa", we would do only one SELECT * FROM a WHERE a = "aCa", and it would interpret "C" as any letter in "bdgjklmnpqrstv". So, one SELECT would be equivalent to several ones with "C" replaced by any of those letters: SELECT * FROM a WHERE a = "aba" SELECT * FROM a WHERE a = "ada" SELECT * FROM a WHERE a = "aga" SELECT * FROM a WHERE a = "aja" ... Or, for example, if I want to call SELECT * FROM a WHERE a = "string", have it search for "string", " string", "string " and " string ". I think that, in an indexed database, making several SELECT's like that would be faster than using LIKE or REGEXP. Of course, we could implement the generation of all the SELECT's in C and/or Perl, for example, but it would be extremely more practical if we could call SELECT only once and have it consider "C" as any consonant, and it would be equivalent to calling SELECT several times. Thank you in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does changing the db change result sets?
Roger Binns wrote:
>
> The cursor object in apsw wraps a prepared statement. Since the cursor
> gets reused the earlier results are no longer available:
>
> cursor.execute("select * from numbers")
> ...
> cursor.execute("delete from numbers where no=5")
>
OK, that makes sense.
>
> Alternatively the "delete" statement can be done with a different cursor
> so that the earlier one is not clobbered:
>
> cursor2=conn.cursor()
> cursor2.execute("delete from numbers where no=5")
>
This is effectively what the sqlite3_exec(db, "delete") call does
internally in the C API program I posted.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG in RTree ?
Thank You, I'll keep looking further. Xevi En/na Kees Nuyt ha escrit: > On Sun, 20 Jul 2008 09:03:44 +0200, you wrote: > > >> Hello to all, >> >> I think I have found a bug in the RTree extension (I'm using version 3.6.0) >> If I run this script : >> > > It works perfectly for me. > [snip] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
>>> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, >>> (alen < blen) ? alen : blen); > > Maybe the length is still wrong. The lengths passed to an sqlite > collation sequence callback are in bytes. But _wcsnicmp() is > probably in characters, no? > > That was it! It seems to work now. // new code int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b, ((alen < blen) ? alen : blen) / sizeof(wchar_t)); Is the fact that the callback strings are not NUL terminated and the lengths are in bytes documented anywhere? If not, it might be a good idea. If they are documented, can someone point me to the url? I would like to see if there are any other gotchas before relying on this. csmith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 8:10 PM, D. Richard Hipp wrote:
>
> On Jul 21, 2008, at 8:32 AM, C. Smith wrote:
>>
>> I didn't know the strings weren't nul terminated. I changed my
>> callback to:
>>
>> static int _cmp(void *pCtx, int alen, const void *a,
>> int blen, const void *b)
>> {
>> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
>> (alen < blen) ? alen : blen);
Maybe the length is still wrong. The lengths passed to an sqlite
collation sequence callback are in bytes. But _wcsnicmp() is
probably in characters, no?
Dan.
>>
>> if(r == 0)
>> r = alen - blen;
>>
>> return r;
>> }
>>
>> Still doesn't work though :(
>>
>
> How did you register the collating sequence? Did you use the
> SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] User-defined collation UNIQUE INDEX
>
> How did you register the collating sequence? Did you use the
> SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
>
I am doing the below.
sqlite3_create_collation(db, "PATH", SQLITE_UTF16, NULL, _cmp);
I changed it to use "SQLITE_UTF16_ALIGNED" but it didn't load properly.
I modified my extension init func to print out the return code of
create collation:
int rc;
SQLITE_EXTENSION_INIT2(pApi)
rc = sqlite3_create_collation(db, "PATH", SQLITE_UTF16_ALIGNED,
NULL, _cmp);
fprintf(stderr, "collation create: %d\n", rc);
sqlite> pragma encoding = 'utf16le';
sqlite> select load_extension('path.dll');
collation create: 0
sqlite> create table test (str collate path);
SQL error: no such collation sequence: path
sqlite>
If I set it back to SQLITE_UTF16, it recognizes the "path" collation again.
csmith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 8:32 AM, C. Smith wrote:
>
> I didn't know the strings weren't nul terminated. I changed my
> callback to:
>
> static int _cmp(void *pCtx, int alen, const void *a,
> int blen, const void *b)
> {
> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
> (alen < blen) ? alen : blen);
>
> if(r == 0)
> r = alen - blen;
>
> return r;
> }
>
> Still doesn't work though :(
>
How did you register the collating sequence? Did you use the
SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
D. Richard Hipp wrote:
> On Jul 21, 2008, at 12:05 AM, C. Smith wrote:
>
>> The collation is
>> a case-insensitive wchar compare for windows (using _wcsicmp).
>
> The strings passed to a collating function are not zero-terminated.
> Are you making a copy of both input strings and adding a zero
> terminator yourself, or are you really using _wcsnicmp()?
>
> You might want to have a look at how the "NOCASE" collation is
> implemented in the nocaseCollatingFunc() function in the main.c source
> file of SQLite.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
I didn't know the strings weren't nul terminated. I changed my callback to:
static int _cmp(void *pCtx, int alen, const void *a,
int blen, const void *b)
{
int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
(alen < blen) ? alen : blen);
if(r == 0)
r = alen - blen;
return r;
}
Still doesn't work though :(
sqlite> pragma encoding = 'utf16le';
sqlite> select load_extension('ext.dll');
sqlite> create table test (str collate path);
sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'ABC';
sqlite> select * from test where str = 'abc';
sqlite> insert into test values ('abc');
sqlite> select 'abc' = 'ABC' collate path;
1
sqlite>
Still let me put two 'abc' records in and it still doesn't find any 'abc'
records. Yet, the collation is working if you look at the last select.
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select All Tables?
"cstrader" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is there a way to SELECT UNION all of the tables in a database -- > that is to create a big table with rows from each of the individual > tables? I don't see how this is supposed to work, in general. For one thing, tables might have different number of columns. > I understand I can do multiple selects, but the command gets > very long if there are a lot of tables. No, there is no magic bullet. If you want a union of 100 tables, you need to write 100 selects joined by UNION ALL. I suspect you can solve your problem by changing your database design. When I see people want to do something like this, it's usually because they have many identical tables - e.g. each representing sales data for one month (with a new table created each month). The SQL way is to have a single table instead, with an extra column representing the month each record is for. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select All Tables?
Is there a way to SELECT UNION all of the tables in a database -- that is to create a big table with rows from each of the individual tables? I understand I can do multiple selects, but the command gets very long if there are a lot of tables. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Robert Simpson wrote:
> I just tried the same steps on a memorydb using the NOCASE collation
> sequence, and it worked fine ...
>
>
> C:\Src>sqlite3 :memory:
> SQLite version 3.6.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .headers on
> sqlite> create table foo(myvalue text collate nocase);
> sqlite> create unique index fooidx on foo(myvalue);
> sqlite>
> sqlite> insert into foo(myvalue) values('abc');
> sqlite> insert into foo(myvalue) values('abc');
> SQL error: column myvalue is not unique
> sqlite> insert into foo(myvalue) values('Abc');
> SQL error: column myvalue is not unique
> sqlite> insert into foo(myvalue) values('ABC');
> SQL error: column myvalue is not unique
> sqlite> drop index fooidx;
> sqlite> select * from foo;
> myvalue
> abc
> sqlite> create unique index fooidx on foo(myvalue);
> sqlite> insert into foo(myvalue) values('ABC');
> SQL error: column myvalue is not unique
>
>
>
Okay. Two things:
1. NOCASE is a built-in collation. My issue is with a user-defined.
2. NOCASE only folds ASCII (26 chars), I need a more complete solution.
Try the below steps on windows. Not sure if you have microsoft's compiler
though, cl.exe. I am using version 7 (2003).
#include
#include
SQLITE_EXTENSION_INIT1
static int _cmp(void *pCtx, int alen, const void *a,
int blen, const void *b)
{
// for testing, not sure this crt func is the best solution
return _wcsicmp((const wchar_t *)a, (const wchar_t *)b);
}
__declspec(dllexport) int sqlite3_extension_init(sqlite3 *db,
char **pzErrMsg,const sqlite3_api_routines *pApi)
{
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_collation(db, "PATH", SQLITE_UTF16, NULL, _cmp);
return 0;
}
Compile the above: (sqlite3ext.h in current directory)
C:\test\sqlite> cl /nologo /W3 /LD /I. /Fepath.dll path.c sqlite3.lib
path.c
Creating library andy.lib and object andy.exp
C:\test\sqlite> sqlite testdb
sqlite> select load_extension('path.dll');
Now repeat your steps but use 'collate path'.
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Is there a way to SELECT UNION all of the tables in a database -- that is to create a big table with rows from each of the individual tables? I understand I can do multiple selects, but the command gets very long if there are a lot of tables. Thanks - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Monday, July 21, 2008 7:18 AM Subject: Re: [sqlite] User-defined collation UNIQUE INDEX > > On Jul 21, 2008, at 12:05 AM, C. Smith wrote: > >> The collation is >> a case-insensitive wchar compare for windows (using _wcsicmp). > > The strings passed to a collating function are not zero-terminated. > Are you making a copy of both input strings and adding a zero > terminator yourself, or are you really using _wcsnicmp()? > > You might want to have a look at how the "NOCASE" collation is > implemented in the nocaseCollatingFunc() function in the main.c source > file of SQLite. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 12:05 AM, C. Smith wrote: > The collation is > a case-insensitive wchar compare for windows (using _wcsicmp). The strings passed to a collating function are not zero-terminated. Are you making a copy of both input strings and adding a zero terminator yourself, or are you really using _wcsnicmp()? You might want to have a look at how the "NOCASE" collation is implemented in the nocaseCollatingFunc() function in the main.c source file of SQLite. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
Igor Tandetnik schrieb: > "Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> ? ? ?? Sunday 20 July 2008 21:20:19 Jay A. Kreibich >> ???(?): >>> The good news is that you can re-implement the LIKE function fairly >>> easily. There have been a number of posts in the past dealing with >>> using external Unicode/I18N libraries to implement a more complete >>> 'LIKE' function. >> There is unicode extension in /ext/icu of SQLite source code. > > With ICU extension, LIKE operator may consider Ö and ö to be equal, but > is still unlikely to make ö and o equal. > You might try to store all your words in one of the decomposition normalization forms (NFD or KNFD would work, see http://unicode.org/reports/tr15/) than your LIKE match should find o and ö, but you might need to apply some other normalization to compose chars again on output. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
