[sqlite] Compilation error enabling ICU and FTS3, Sqlite 3.6.11, Ubuntu Hardy libicu-dev

[aditya siram]

Hi all,
I am trying to compile Sqlite with full text search and icu support. I
installed libicu-dev from the Ubuntu Hardy repos. I downloaded the
amalgamation package [1] and added the icu and fts option to the sqlite3.c
file [2]. I now get the following compilation error:
gcc -DSQLITE_THREADSAFE=1 -g -O2 -o .libs/sqlite3 shell.o
./.libs/libsqlite3.so -lreadline -lcurses -ldl -lpthread
./.libs/libsqlite3.so: undefined reference to `ubrk_current_3_8'
./.libs/libsqlite3.so: undefined reference to `uregex_matches_3_8'
./.libs/libsqlite3.so: undefined reference to `uregex_setText_3_8'
./.libs/libsqlite3.so: undefined reference to `utf8_nextCharSafeBody_3_8'
./.libs/libsqlite3.so: undefined reference to `ubrk_next_3_8'
./.libs/libsqlite3.so: undefined reference to `uregex_close_3_8'
./.libs/libsqlite3.so: undefined reference to `u_foldCase_3_8'
./.libs/libsqlite3.so: undefined reference to `ubrk_close_3_8'
./.libs/libsqlite3.so: undefined reference to `ubrk_first_3_8'
./.libs/libsqlite3.so: undefined reference to `u_strToUTF8_3_8'
./.libs/libsqlite3.so: undefined reference to `u_isspace_3_8'
./.libs/libsqlite3.so: undefined reference to `ubrk_open_3_8'
./.libs/libsqlite3.so: undefined reference to `u_strToUpper_3_8'
./.libs/libsqlite3.so: undefined reference to `u_strToLower_3_8'
./.libs/libsqlite3.so: undefined reference to `utf8_countTrailBytes_3_8'
./.libs/libsqlite3.so: undefined reference to `u_errorName_3_8'
./.libs/libsqlite3.so: undefined reference to `uregex_open_3_8'
./.libs/libsqlite3.so: undefined reference to `ucol_close_3_8'
./.libs/libsqlite3.so: undefined reference to `ucol_strcoll_3_8'
./.libs/libsqlite3.so: undefined reference to `ucol_open_3_8'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1

Google search yielded nothing. Any ideas?
Thanks,
deech

[1] http://www.sqlite.org/sqlite-amalgamation-3.6.11.tar.gz
[2] Head of sqlite3.c:
/**
** This file is an amalgamation of many separate C source files from SQLite
 ...
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
#ifndef SQLITE_PRIVATE
# define SQLITE_PRIVATE static
#endif
#ifndef SQLITE_API
# define SQLITE_API
#endif
#define SQLITE_ENABLE_FTS3
#define SQLITE_ENABLE_FTS3_PARENTHESIS
#define SQLITE_ENABLE_ICU
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Accent Insensitive search

[aditya siram]

Hi all,
Is there a way to do an accent/diacritic insensitive search in sqlite? For
example I want to a query to find " `a blanc " with the search term "a
blanc".

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

[sqlite] Extraction SQL query from a sqlite3_stmt

[aditya siram]

Hi all,
Once a sqlite3_stmt has been prepared and variables bound, is there some way
of extracting the SQL query? I have a query that runs fine on command line
and if I prepare and run it without variables. So something in the way I am
binding the variables is returns zero rows. I would like to see the final
SQL before it is run.

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

Re: [sqlite] selecting the top 3 in a group

[aditya siram]

Hi Robert,
SQL has a LIMIT keyword. I have used it to take the top 'x' entries of a
large table , so for example:
SELECT * from table LIMIT 20

You should be able to use it in your query like so:
select div, team, max(wins) from teams group by div limit 5;

-deech

On Thu, Jan 8, 2009 at 12:06 AM, Robert Citek wrote:

> How can I construction a SQL query to pick the top three (3) items in a
> group?
>
> I have a list of sports teams which are grouped into divisions, say A,
> B, C, D, etc.  At the end of the season I would like to get a list of
> the top three teams (those with the most wins) in each division.  If I
> wanted the best team from each division, I could write this:
>
> select div, team, max(wins) from teams group by div ;
>
> Unfortunately, there's no option to max to specify more than one item,
> e.g. max(wins,3) to specify the top 3.
>
> In pseudocode, I want to do something similar to this:
>
> for $i in (select div from teams) {
>  select div, team, wins from teams where div=$i order by wins desc limit 3
> ;
> }
>
> Is there a way to do the equivalent using only SQL?
>
> Thanks in advance for any pointers.
>
> Regards,
> - Robert
> ___
> 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] Exporting database to CSV file

[aditya siram]

Open the sqlite database using:
> sqlite3 database.sqlite
>> .mode csv
>> .output yourfilename
>> your-sql -query

-deech

On Mon, Jan 5, 2009 at 4:21 PM, Jonathon  wrote:

> Hello all,
>
> I am interested in exporting a particular table to a CSV file.  I've
> browsed
> google for a bit, but all I could find is third party programs.  I was
> hoping this functionality was provided in sqlite and that it was super
> duper
> fast :)
>
> Thanks!
>
> J
> ___
> 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] Inserting an image file?

[aditya siram]

Hi all,
How do I insert an image file into sqlite table? Can I just use a
path-specifier? For instance,
create table ("name" varchar, "data" blob);
insert into table ("someBlob", "./blob.jpg");

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

Re: [sqlite] Wildcard searches, partial matches

[aditya siram]

Cool!

-deech

On Mon, Dec 15, 2008 at 12:12 PM, P Kishor <punk.k...@gmail.com> wrote:

> On 12/15/08, aditya siram <aditya.si...@gmail.com> wrote:
> > Hi all,
> >  I am trying to implement an autocompleter with a sqlite backend. Is
> there a
> >  way to search the database using wildcards/regexps's without reading the
> >  entire search space into memory?
> >
> >  For example given  the rows:
> >  'hello  world'
> >  'goodbye world'
> >   a query approximately like "* world" should return both.
> >
>
>
> and so it does...
>
> sqlite> CREATE TABLE foo (a);
> sqlite> INSERT INTO foo VALUES ('hello world');
> sqlite> INSERT INTO foo VALUES ('goodbye world');
> sqlite> SELECT * FROM foo WHERE a LIKE '%world';
> hello world
> goodbye world
> sqlite> SELECT * FROM foo WHERE a GLOB '*world';
> hello world
> goodbye world
> sqlite>
>
> Read the docs on the difference between LIKE and GLOB
> ___
> 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] Wildcard searches, partial matches

[aditya siram]

Hi all,
I am trying to implement an autocompleter with a sqlite backend. Is there a
way to search the database using wildcards/regexps's without reading the
entire search space into memory?

For example given  the rows:
'hello  world'
'goodbye world'
 a query approximately like "* world" should return both.

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

Re: [sqlite] Adding data with periods

[aditya siram]

Thanks a lot. The issue has been fixed with:
INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");

I was thrown by the error message that seemed to be complaining about the
periods when I was actually missing the VALUES(...) clause.

Appreciate the quick response ...
deech

On Sun, Dec 14, 2008 at 11:21 PM, John Stanton <jo...@viacognis.com> wrote:

> Note that literal delimiters in SQL are single quotes, e.g. 'This is an
> SQL literal'.
>
> It is good practice with Sqlite to use bound variables.  You avoid
> possible SQL injection attacks and limit sensitivity to data content.
>
> aditya siram wrote:
> > Hi all,
> > I'm having trouble adding data with period characters in it. I tries to
> > escape the period with a `'` but that didn' t seem to work. Here is an
> > example interaction:
> >
> > sqlite> create table test_table ("Contents" varchar);
> > sqlite> insert into test_table "hello . world";
> > SQL error: near ""hello . world"": syntax error
> > sqlite> insert into test_table "hello '. world";
> > SQL error: near ""hello '. world"": syntax error
> >
> > Thanks ...
> > deech
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Adding data with periods

[aditya siram]

Hi all,
I'm having trouble adding data with period characters in it. I tries to
escape the period with a `'` but that didn' t seem to work. Here is an
example interaction:

sqlite> create table test_table ("Contents" varchar);
sqlite> insert into test_table "hello . world";
SQL error: near ""hello . world"": syntax error
sqlite> insert into test_table "hello '. world";
SQL error: near ""hello '. world"": syntax error

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