FTS3 IGNORE OR REPLACE????

[sorka]

Is there any way to have an intsert into an FTS3 table ignore a row if the ROWID being inserted already exists? This is turning out to be quite troublesome because I'm inserting thousands of records where just a few like 3 or 4 will have the same rowid as existing records. However, to do the test prior to insertion to delete the duplicates first is quite expensive. It would be much better if the FTS3 insertion routine had the option of ignoring rather than failing on the constraint.

[Simon Slavin-3]

On 4 Nov 2009, at 5:12am, sorka wrote:

> Is there any way to have an intsert into an FTS3 table ignore a row  
> if the
> ROWID being inserted already exists?

First, make whatever column you're using for ROWID explicit, not  
implicit.  Declare one of your own named columns as INTEGER PRIMARY  
KEY.  Then when you use the INSERT command you can use the

INSERT OR IGNORE ...

form of the command and it'll ignore the command if it would duplicate  
the value in that column.

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

[Scott Hess]

On Tue, Nov 3, 2009 at 9:12 PM, sorka <sorka95032@...> wrote:
> Is there any way to have an intsert into an FTS3 table ignore a row if the
> ROWID being inserted already exists? This is turning out to be quite
> troublesome because I'm inserting thousands of records where just a few like
> 3 or 4 will have the same rowid as existing records. However, to do the test
> prior to insertion to delete the duplicates first is quite expensive. It
> would be much better if the FTS3 insertion routine had the option of
> ignoring rather than failing on the constraint.

My experience suggests that the test before the insert is not quite
expensive, it's just shifting some expense from the insert to the test
(presumably a select).  But the overall sequence of:

   BEGIN IMMEDIATE;
     DELETE FROM ftstable WHERE docid = ?;
     INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
   COMMIT;

Should be more-or-less the same speed as if you ran just the INSERT
without an explicit transaction.  Really, you should test it!

Anyhow, if you're still convinced there's a problem, you should just
do the INSERT, then check whether the INSERT failed due to an index
constraint, then do an UPDATE to set things to your desired values.
That's about as well as fts3 would do internally (fts3 UPDATE is
already implemented as internal-delete-operator followed by
internal-insert-operator).

Note that the fts3 implementation provides some advantage to doing
updates in order by docid.  Optimal would be something like:

  BEGIN IMMEDIATE;
    -- for each document to insert, in numerically sorted order
      DELETE FROM ftstable WHERE docid = ?;
      INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
  COMMIT;

If you're doing the deletes as a separate first pass it will be
somewhat slower, because it is unordered WRT the inserts.  If you do
each pass in order, though, the difference might be pretty small.

[If I misunderstand and your goal is to not insert rows which are
already present, then ... I'm confused.  Just insert all the rows you
have, and the ones which are already present will fail to insert, and
that's fine.]

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

[sorka]

I'm doing both delete and insert within the same transaction already. The problem is there will alway be a few duplicates out of the hundreds of records so it will always fail. For whatever reason, the delete, even though it's just 2 or 3 records is taking 10 times longer than just the insert alone where I can artificially make a case where there are no duplicates.

Scott Hess wrote:

My experience suggests that the test before the insert is not quite
expensive, it's just shifting some expense from the insert to the test
(presumably a select).  But the overall sequence of:

   BEGIN IMMEDIATE;
     DELETE FROM ftstable WHERE docid = ?;
     INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
   COMMIT;

Should be more-or-less the same speed as if you ran just the INSERT
without an explicit transaction.  Really, you should test it!

Anyhow, if you're still convinced there's a problem, you should just
do the INSERT, then check whether the INSERT failed due to an index
constraint, then do an UPDATE to set things to your desired values.
That's about as well as fts3 would do internally (fts3 UPDATE is
already implemented as internal-delete-operator followed by
internal-insert-operator).

Note that the fts3 implementation provides some advantage to doing
updates in order by docid.  Optimal would be something like:

  BEGIN IMMEDIATE;
    -- for each document to insert, in numerically sorted order
      DELETE FROM ftstable WHERE docid = ?;
      INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
  COMMIT;

If you're doing the deletes as a separate first pass it will be
somewhat slower, because it is unordered WRT the inserts.  If you do
each pass in order, though, the difference might be pretty small.

[If I misunderstand and your goal is to not insert rows which are
already present, then ... I'm confused.  Just insert all the rows you
have, and the ones which are already present will fail to insert, and
that's fine.]

-scott
_______________________________________________
sqlite-users mailing list
[e-mail]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Scott Hess]

Think you're going to have to post an example of how to replicate it,
then.  A really good way to demonstrate is using self-contained code
(the tcl bindings are great for this kind of thing).  Your description
doesn't match my experience with fts3, so obviously either you're
doing something differently than I would, or I'm understanding the
operation of your problem differently than your computer is.

-scott


On Wed, Nov 4, 2009 at 7:35 AM, sorka <sorka95032@...> wrote:

>
> I'm doing both delete and insert within the same transaction already. The
> problem is there will alway be a few duplicates out of the hundreds of
> records so it will always fail. For whatever reason, the delete, even though
> it's just 2 or 3 records is taking 10 times longer than just the insert
> alone where I can artificially make a case where there are no duplicates.
>
>
> Scott Hess wrote:
>>
>> My experience suggests that the test before the insert is not quite
>> expensive, it's just shifting some expense from the insert to the test
>> (presumably a select).  But the overall sequence of:
>>
>>    BEGIN IMMEDIATE;
>>      DELETE FROM ftstable WHERE docid = ?;
>>      INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
>>    COMMIT;
>>
>> Should be more-or-less the same speed as if you ran just the INSERT
>> without an explicit transaction.  Really, you should test it!
>>
>> Anyhow, if you're still convinced there's a problem, you should just
>> do the INSERT, then check whether the INSERT failed due to an index
>> constraint, then do an UPDATE to set things to your desired values.
>> That's about as well as fts3 would do internally (fts3 UPDATE is
>> already implemented as internal-delete-operator followed by
>> internal-insert-operator).
>>
>> Note that the fts3 implementation provides some advantage to doing
>> updates in order by docid.  Optimal would be something like:
>>
>>   BEGIN IMMEDIATE;
>>     -- for each document to insert, in numerically sorted order
>>       DELETE FROM ftstable WHERE docid = ?;
>>       INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?);
>>   COMMIT;
>>
>> If you're doing the deletes as a separate first pass it will be
>> somewhat slower, because it is unordered WRT the inserts.  If you do
>> each pass in order, though, the difference might be pretty small.
>>
>> [If I misunderstand and your goal is to not insert rows which are
>> already present, then ... I'm confused.  Just insert all the rows you
>> have, and the ones which are already present will fail to insert, and
>> that's fine.]
>>
>> -scott
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@...
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-----tp26191125p26198341.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

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

[sorka]

Hmm. Have you actually tried this yourself?

Here's what I get with a simplified example:

CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY, title);
INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
sqlite> select * from keyword;
3|A
3|A

So clearly this doesn't work as now duplicates are allowed to be inserted. If I execute this command now:

INSERT OR IGNORE INTO keyword (ROWID, title) VALUES(3, "A");
SQL error: constraint failed

So, clearly there is no duplicate checking at all when the explicit key name is used but then it's useless as I can't have duplicate keys.

Simon Slavin-3 wrote:

On 4 Nov 2009, at 5:12am, sorka wrote:

> Is there any way to have an intsert into an FTS3 table ignore a row  
> if the
> ROWID being inserted already exists?

First, make whatever column you're using for ROWID explicit, not  
implicit.  Declare one of your own named columns as INTEGER PRIMARY  
KEY.  Then when you use the INSERT command you can use the

INSERT OR IGNORE ...

form of the command and it'll ignore the command if it would duplicate  
the value in that column.

Simon.
_______________________________________________
sqlite-users mailing list
[e-mail]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin-3]

On 4 Nov 2009, at 5:05pm, sorka wrote:

> Hmm. Have you actually tried this yourself?
>
> Here's what I get with a simplified example:
>
> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY,
> title);
> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
> sqlite> select * from keyword;
> 3|A
> 3|A

Erm ... I don't use FTS3 or VIRTUAL TABLEs but can someone else tell  
me whether this shows a bug for a VIRTUAL TABLE ?

sorka, instead of using INTEGER PRIMARY KEY, can you try one or both  
of these:

CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER UNIQUE,  
title);
CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER  
AUTOINCREMENT, title);

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

[sorka]

There have been past discussions asking the same thing and various sqlite developers saying I'll check and reporting back that FTS3 doesn't implement replace or ignore.

I was hoping someone had come up with a work around solution since then that doesn't have a big performance penalty.

Simon Slavin-3 wrote:

On 4 Nov 2009, at 5:05pm, sorka wrote:

> Hmm. Have you actually tried this yourself?
>
> Here's what I get with a simplified example:
>
> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY,
> title);
> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
> sqlite> select * from keyword;
> 3|A
> 3|A

Erm ... I don't use FTS3 or VIRTUAL TABLEs but can someone else tell  
me whether this shows a bug for a VIRTUAL TABLE ?

sorka, instead of using INTEGER PRIMARY KEY, can you try one or both  
of these:

CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER UNIQUE,  
title);
CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER  
AUTOINCREMENT, title);

Simon.
_______________________________________________
sqlite-users mailing list
[e-mail]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Scott Hess]

fts3 columns are all implicitly TEXT, no matter how you dress them up.
 There's already docid as a primary-key alias for rowid.

-scott


On Wed, Nov 4, 2009 at 11:03 AM, Simon Slavin <slavins@...> wrote:

>
> On 4 Nov 2009, at 5:05pm, sorka wrote:
>
>> Hmm. Have you actually tried this yourself?
>>
>> Here's what I get with a simplified example:
>>
>> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY,
>> title);
>> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
>> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A");
>> sqlite> select * from keyword;
>> 3|A
>> 3|A
>
> Erm ... I don't use FTS3 or VIRTUAL TABLEs but can someone else tell
> me whether this shows a bug for a VIRTUAL TABLE ?
>
> sorka, instead of using INTEGER PRIMARY KEY, can you try one or both
> of these:
>
> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER UNIQUE,
> title);
> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER
> AUTOINCREMENT, title);
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

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

[Gary_Gabriel]

Hi All,

I want to use the Virtualtext extension in an application and I
downloaded it from VirtualText extension
<http://mobigroup.ru/files/sqlite-ext/virtualtext/>.
I tried the following compilation using the latest version of MinGW and
Msys on Win32 XP SP2. After reviewing the documentation I found I tried
compiling the _19 version of SQLite with the extension and then the
extension alone. But both compilations produced errors. The following
compilation is for SQLite. See (EXT) for the compilation commands used
that I took from the file Virtualtext.c. Any guidance in correcting the
errors and compiling the extension would be appreciated.

I appreciate the product and this forum, thanks for your help- Gary Gabriel

$ cd sqlite
$ mkdir bld; cd bld
$ ../configure
$ 'cd'
$ cd sqlite
$ gcc -O2 -DNDEBUG=1 -DTHREADSAFE=1 -DSQLITE_ENABLE_COLUMN_METADATA
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_ENABLE_RTREE -DSQLITE_DEFAULT_FILE_FORMAT=4 -c *.c
virtualtext.c: In Funktion »text_clean_text«:
virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
virtualtext.c: In Funktion »text_parse«:
virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
(EXT) $ gcc -fPIC -lm -shared virtualtext.c -o libsqlitevirtualtext.so
virtualtext.c:1: Warnung: -fPIC f├╝r Ziel ignoriert (der gesamte Code
ist positionsunabhängig)
virtualtext.c: In Funktion »text_clean_text«:
virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
virtualtext.c: In Funktion »text_parse«:
virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
C:\DOKUME~1\GARYGA~1\LOKALE~1\Temp\ccuokH1y.o:virtualtext.c:(.text+0x4cc):
undefined reference to `iconvConvertToUTF8'
C:\DOKUME~1\GARYGA~1\LOKALE~1\Temp\ccuokH1y.o:virtualtext.c:(.text+0xcbd):
undefined reference to `iconvCreateUTF8Converter'
C:\DOKUME~1\GARYGA~1\LOKALE~1\Temp\ccuokH1y.o:virtualtext.c:(.text+0xdda):
undefined reference to `iconvFreeUTF8Converter'
collect2: ld gab 1 als Ende-Status zur├╝ck
$ gcc -s -o sqlite3 *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'
collect2: ld gab 1 als Ende-Status zur├╝ck
$ rm shell.o
$ gcc -shared -s -o sqlite.dll *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'
collect2: ld gab 1 als Ende-Status zur├╝ck




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

[Alexey Pechnikov-2]

Hello!

On Thursday 05 November 2009 14:47:03 Gary_Gabriel wrote:
> I want to use the Virtualtext extension in an application and I
> downloaded it from VirtualText extension
> <http://mobigroup.ru/files/sqlite-ext/virtualtext/>.

Oh, I'm sorry, is needed the http://mobigroup.ru/files/sqlite-ext/iconv/ extension too.

Compile as
gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o libsqlitevirtualtext.so

I did fix the comment in code. Thanks for your report!

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Gary_Gabriel]

Hi Alexey thanks for the prompt answer. I saw your extension and tried
it in the Spatialite GUI. The trial worked well in the GUI and it is
useful for messaging environments. However I am still not sure what the
recommended way of compiling it is.

- Should it be compiled when compiling SQLite or is it (normally)
compiled alone afterwards?
- What is the order of compiling?
- First http://mobigroup.ru/files/sqlite-ext/iconv/ extension. Compile
as gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o
libsqlitevirtualtext.so
- Virtualtext extension. Compile as gcc -fPIC -lm -shared virtualtext.c
-o libsqlitevirtualtext.so.
- I assume that both *.c files are put into the SQLite directory for
compiling. Does "../iconv/iconv.c" from the compile commands mean that
iconv stores in a sub-directory?


Thanks for the clarifications- I'm looking forward to using it. - Gary

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

[Alexey Pechnikov-2]

Hello!

On Friday 06 November 2009 09:31:11 Gary_Gabriel wrote:
> Hi Alexey thanks for the prompt answer. I saw your extension and tried
> it in the Spatialite GUI. The trial worked well in the GUI and it is
> useful for messaging environments. However I am still not sure what the
> recommended way of compiling it is.
>
> - Should it be compiled when compiling SQLite or is it (normally)
> compiled alone afterwards?

I'm compile it as part of my own SQLite build. The debian lenny repository
is here:
deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free

> - What is the order of compiling?
> - First http://mobigroup.ru/files/sqlite-ext/iconv/ extension. Compile
> as gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o
> libsqlitevirtualtext.so

This command compile both extensions and you can already load
libsqlitevirtualtext.so library to SQLite.

> - Virtualtext extension. Compile as gcc -fPIC -lm -shared virtualtext.c
> -o libsqlitevirtualtext.so.

See above.

> - I assume that both *.c files are put into the SQLite directory for
> compiling. Does "../iconv/iconv.c" from the compile commands mean that
> iconv stores in a sub-directory?

Yes, the file ../iconv/iconv.c is stored in other directory. Of cource you can put
iconv.c and iconv.h files from http://mobigroup.ru/files/sqlite-ext/iconv/
to the virtualtext directory and compile as

gcc -fPIC -lm -shared iconv.c virtualtext.c -o libsqlitevirtualtext.so

The iconv may be useful without virtualtext extension and so I did place it
in the different directory.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Gary_Gabriel]

Hi Alexey

Thanks for your input. I have tried variations of your suggestions, but
there are still questions about the errors and warnings that I would
like to clarify. I am still new at this so I maybe making some errors as
well, any suggestions would be appreciated.

Concluding test today: by placing virtualtext.c in the \iconc directory
together with these files then SQLite3 compiled and produced the *.exe
and .dll. But VirtualText did not compile.

Previous tests on the weekend: This test used two subdirectories where
VirtualText compiled, and SQLite3 did not compile. The best
configuration was as you suggested to put iconv in it's own subdirectory
so for your info the Msys configuration looks like:
home\ SQLite\virtualtext.c
iconv\iconv.h, iconv.c
In configuring iconv there were path errors so I changed the compiler
commands to:
gcc -fPIC -lm -shared -I ../iconv/hdr -c ../sqlite/virtualtext.c -o
libsqlitevirtualtext.so to eliminate the errors. The changes reference
this document: HOWTO Specify the Header File Include Path for use with
MinGW Compilers | MinGW MinGW Paths
<http://www.mingw.org/wiki/IncludePathHOWTO>

Is it right that VirtualText compiles to
C:\msys\1.0\home\iconv\libsqlitevirtualtext.so and \iconv as well?

SQLite3 does not compile the *.dll, *.exe now. What needs to be changed
so that it compiles properly? The current errors follow below.

Thanks I appreciate your effort- Gary Gabriel

PS- the MinGw mailing list discussed placement of the -l option. Could
this be an issue?

Compile Iconv, VirtualText.
$ gcc -fPIC -lm -shared -I ../iconv/hdr -c ../sqlite/virtualtext.c -o
libsqlitevirtualtext.so
../sqlite/virtualtext.c:1: Warnung: -fPIC f├╝r Ziel ignoriert (der
gesamte Codeist positionsunabhängig)
../sqlite/virtualtext.c: In Funktion »text_clean_text«:
../sqlite/virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von
Ganzzahl ohne Typkonvertierung
../sqlite/virtualtext.c: In Funktion »text_parse«:
../sqlite/virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von
Ganzzahl ohne Typkonvertierung
Compile SQLite
$ gcc -O2 -DNDEBUG=1 -DTHREADSAFE=1 -DSQLITE_ENABLE_COLUMN_METADATA
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_ENABLE_RTREE -DSQLITE_DEFAULT_FILE_FORMAT=4 -c *.c
virtualtext.c: In Funktion »text_clean_text«:
virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
virtualtext.c: In Funktion »text_parse«:
virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
$ gcc -s -o sqlite3 *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'collect2: ld gab 1 als Ende-Status zur├╝ck
$ gcc -shared -s -o sqlite3.dll *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'collect2: ld gab 1 als Ende-Status zur├╝ck








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

[Alexey Pechnikov-2]

Hello!

1. Compiling as library

veter@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$ ls
virtualtext.c
veter@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$ gcc -fPIC -lm -shared virtualtext.c ../iconv/iconv.c -o libsqlitevirtualtext.so
virtualtext.c: In function ‘text_clean_text’:
virtualtext.c:328: warning: assignment makes pointer from integer without a cast
virtualtext.c: In function ‘text_parse’:
virtualtext.c:587: warning: assignment makes pointer from integer without a cast
veter@veter-laptop:/mnt/work/chroot/lenny/tmp/sqlite3-3.6.20/ext/virtualtext$ ls
libsqlitevirtualtext.so  virtualtext.c

If MinGW does not recognize it try these commands:
gcc -fPIC -lm -L../iconv/ -shared virtualtext.c -o libsqlitevirtualtext.so
or
gcc -fPIC -lm -L../iconv/ -shared virtualtext.c ../iconv/iconv.c -o libsqlitevirtualtext.so

2. Compiling with SQLite

2.1 Patch main.c like to
 #ifdef SQLITE_ENABLE_ICU
 # include "sqliteicu.h"
 #endif
+#ifdef SQLITE_ENABLE_VIRTUALTEXT
+# include "iconv.h"
+#endif

...

+#ifdef SQLITE_ENABLE_VIRTUALTEXT
+  if( !db->mallocFailed && rc==SQLITE_OK ){
+    rc = sqlite3VirtualTextInit(db);
+  }
+#endif
+
   sqlite3Error(db, rc, 0);
.
   /* -DSQLITE_DEFAULT_LOCKING_MODE=1 makes EXCLUSIVE the default locking

2.2. Patch mksqlite3c.tcl
    sqliteLimit.h
    vdbe.h
    vdbeInt.h
+
+   iconv.h
 } {
   set available_hdr($hdr) 1
 }

...

    tokenize.c
    complete.c
+   iconv.c
+   virtualtext.c
+
    main.c
    notify.c
.

2.3 Patch Makefile.in
 SRC += \
   $(TOP)/ext/rtree/rtree.h \
   $(TOP)/ext/rtree/rtree.c
+SRC += \
+  $(TOP)/ext/iconv/iconv.h \
+  $(TOP)/ext/iconv/iconv.c \
+  $(TOP)/ext/virtualtext/virtualtext.c

2.4 Build SQLite with option -DSQLITE_ENABLE_VIRTUALTEXT

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Gary_Gabriel]

Thanks Alexey,

Great response. I'll make the changes and get back to you.

Take care- Gary Gabriel


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

[Gary_Gabriel]

Alexey Pechnikov wrote:
> Original SQLite source tree has "ext " directory for extensions and I did
> place my extensions into this directory
>
> $ ls sqlite3-3.6.20/ext
> async  billing  compress  empty  env  fts1  fts2  fts3  functions  iconv  icu  inet  key  md5  README.txt  rtree  tablefunc  undo  uuid  versioning  virtualtext
>
>  
Ok! That's good to know. Now I understand better.

Based on your comments I reversed the changes to determine the essential
ones. Here's the one that fit's with your corrections and works:
Best. gcc -fPIC -lm -L../iconv/ -c -shared virtualtext.c -o
libsqlitevirtualtext.so. The  -c command is essential.

Now I want to load the library. Spatialite doesn't cover loading the
extension because it loads with the database. It starts with creating
the Virtual Table.

Here is what I tried and the errors:
 SQLite version 3.6.20
        Enter ".help" for instructions
        Enter SQL statements terminated with a ";"
        sqlite> .load libsqlitevirtualtext.so (also tried
'libsqlitevirtualtext.so')
        Error: %1 ist keine zul├ñssige Win32-Anwendung.
 Query: SELECT load_extension('libsqlitevirtualtext.so'); - executeStep
failed
            Exception Name: NS_ERROR_FAILURE
            Exception Message: Component returned failure code:
0x80004005 (NS_ERROR_FAILURE)
 SQLiteManager: Query: select load_extension('libsqlitevirtualtext.so',
'auto_load'); - executeStep failed
            Exception Name: NS_ERROR_FAILURE
            Exception Message: Component returned failure code:
0x80004005 (NS_ERROR_FAILURE)

Thanks for help to move me along- Gary Gabriel





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