Re: [sqlite] sqlite syntax auto suggest

[Laurent Dhont]

Dear Richard & others,


Now it is clear but how do I let the user make for example an UPDATE 
query without the OR.


I have UPDATE on position 0, then I want OR, OF, tableName. This works 
but SET is only available on position 4-5. I want this also on position 2.



What I want to do is get a list of all words that can be used in the 
next word the user is going to type.



I hope I am making sense.


Thanks!


Kind regards,


Laurent


On 29.11.19 14:09, Richard Hipp wrote:

On 11/29/19, Laurent  wrote:

Could you perhaps explain what pos means and what the [isTerminal] = 0
rows mean.


New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.


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

Re: [sqlite] sqlite syntax auto suggest

[Laurent]

Dear Richard,


This has been very helpful, I have the parse.sql file. I feel very 
flattered that you took the time to answer my question. My boss says you 
are the one that should win a nobel prize.


This has been much appreciated!!


I completely understand if you won't answer anymore but I can try.

From what I can understand:

 * the symbol table lists all keywords that can be typed in a query
   where [isTerminal] = 1.
 * The other rows where [isTerminal] = 0 are some kind of
   commands/placeholders. This I do not really know.
 * There is never a direct connection from an [isTerminal] = 1 row to
   an [isTerminal] = 0 row.
 * I think but I am not sure that [pos] means position. But maybe I am
   wrong because if I look at for example the FROM keyword I see [pos]
   = 0 with lowwercase 'from' and from what I understand you cannot use
   the FROM keyword in the first position.


Could you perhaps explain what pos means and what the [isTerminal] = 0 
rows mean.


If you have not time for these things I completely understand, thanks 
anyway!



Laurent

On 28.11.19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  Seehttps://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

[sqlite] sqlite syntax auto suggest

[Laurent Dhont]

Hi all,


I am trying to create a fully automated auto suggestion feature for my 
web application for sqlite.

Currently this is going well, but I realized there are to many features 
in sqlite to hardcode this, so it will be near impossible and take a lot 
off time. The diagrams on the site of sqlite, for example the select 
statement https://sqlite.org/syntax/select-stmt.html, is there an API to 
get this information in a format that is not an image?

I really want to make the auto suggestions very specific:

A very basic SELECT statement without all the complicated stuff: SELECT 
column, column, ... FROM table_name WHERE clause, ...

After "SELECT" is typed I only show columns in the auto suggestions, if 
a "," is typed I again show only column names, if there is no column I 
show only "FROM", after "FROM" I show only table_names, 


Please do not feel obligated to answer this question, if you do this is 
much appreciated!


Thanks in advance.


Kind regards,


Laurent Dhont


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

[sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

[Laurent Dami]

Hi,

While working on the Perl DBD:SQLite driver, I found the following bug 
in sqlite : the last_insert_rowid() method (or SQL function) returns the 
constant value 10 after any insert into a fts5 virtual table. This bug 
is new in fts5 : previous versions fts4 and fts3 returned the correct 
rowid value.


Below is a transcript from the sqlite3 shell that illustrates the 
problem (but the same bug also happens when using the API).


$ ./sqlite3
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table f5 using fts5(c);
sqlite> insert into f5(c) values('foobar');
sqlite> select last_insert_rowid();
10
sqlite> select rowid, c from f5;
1|foobar

[run on a Windows7 machine]

Best regards, Laurent Dami


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

[sqlite] how to use fts5 ?

[Laurent Dami]

Hi there,

I'm interested in testing the fts5 extension, already documented 
(https://www.sqlite.org/fts5.html) , but I didn't find any instructions 
on how to compile it. I found the sources in ext/fts5, but apparently 
there is a piece of yacc grammar that doesn't compile under yacc/bison.

Did I miss some documentation ? Or is it not ready yet for use ?

Thanks in advance,

Laurent Dami

Re: [sqlite] Possible to get list of terms in the Full Text Index?

[Laurent Dami]

Le 02.02.2015 18:00, sqlite-users-requ...@sqlite.org a écrit :

Message: 1
Date: Sun, 01 Feb 2015 23:28:33 +0200
From: Rael Bauer <supp...@bauerapps.com>
To: sqlite-users@sqlite.org
Subject: [sqlite] Possible to get list of terms in the Full Text
Index?
Message-ID: <54ce9a81.2010...@bauerapps.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hi,

Is it at all possible to get a listing of all terms in the Full Text
Index and which rowid's they were found in?




Yes it is possible, look at http://www.sqlite.org/fts3.html#fts4aux

Laurent Dami

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

[sqlite] virtual tables (was : SQLite as a meta database)

[Laurent Dami]

Hi there,

Since virtual tables were recenty mentioned in the thread "SQLite as a 
meta database" , I take this opportunity to announce that the lastest 
version of the  DBD::SQLite driver for Perl now has support for virtual 
tables (see https://metacpan.org/pod/DBD::SQLite ).


This means that you can implement virtual tables through Perl classes 
instead of writing C code, which (at least in my view) is much easier to 
write and to debug.


Laurent Dami



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

[sqlite] new support for virtual tables written in Perl

[Laurent Dami]

Hi,

This is to announce that next version of DBD::SQLite (the Perl driver 
for accessing SQLite databases) will include support for virtual tables 
written in Perl. This is easier than writing virtual table extensions in 
C code, so I hope to see creative uses of this feature  from fellow Perl 
programmers.


To anybody interested : the dev release is at
https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite.pm
and the source repository is at
https://github.com/DBD-SQLite/DBD-SQLite .

The distribution includes two builtin virtual tables :

* FileContent 
(https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite/VirtualTable/FileContent.pm) 
: a variation on test_fs.c from the sqlite source, useful for using FTS4 
indices on content stored in the filesystem rather than in sqlite tables.


* PerlData 
(https://metacpan.org/pod/release/ISHIGAKI/DBD-SQLite-1.43_05/lib/DBD/SQLite/VirtualTable/PerlData.pm) 
: a variation and generalization on test_intarray.c from the sqlite 
source. Various kinds of Perl datastructures can be viewed as SQLite 
tables, both as input and as output, and this can be an easy way to 
communicate between a Perl program and a sqlite database, or for joining 
data from various sources.


The stable release should be published in next September or October.

Comments / remarks are welcome.

Cheers, Laurent Dami


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

[sqlite] documentation bugs

[Laurent Dami]

* In http://www.sqlite.org/src/wiki?name=Bug+Reports , the href to the 
bug list is http://www.sqlite.org/src/report; should be 
http://www.sqlite.org/src/reportlist


*  In vtab.hml: the description of sqlite3_index_info is not up to date 
(missing estimatedRows)


* In compile.html : the compile option SQLITE_ENABLE_FTS4_UNICODE61 is 
missing

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

[sqlite] missing doc for FTS feature "^"

[Laurent Dami]

Hi,

I accidentally found the following interesting feature in the Change 
history :


2011-11-01 (3.7.9)

If a search token (on the right-hand side of the MATCH operator) in 
FTS4 begins with "^" then that token must be the first in its field of 
the document. ** Potentially Incompatible Change **



But this is not mentioned in the fts3.html document, so most users 
probably never heard about this feature.




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

[sqlite] a few documentation bugs

[Laurent Dami]

Hi all,

Thanks for sqlite.

I noticed a couple of documentation bugs.

In compile.html:
  -DSQLITE_DISABLE_FTS3_UNICODE  is documented but obsolete
  -DSQLITE_ENABLE_FTS4_UNICODE61 is not documented
In vtab.html
  - typo "must should" in "The xCreate must should return SQLITE_OK if ..."


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

Re: [sqlite] Cross-platform Intel <-> ARM sqlite file issue...

[Laurent PERROTON]

Hi, 

My proc is an ARM920T, but yes, it did solve the problem. 

Thank you very much !!

L.

Kees Nuyt writes:
 > On Fri, 25 Nov 2011 12:30:10 +0100, Laurent PERROTON
 > <l.perro...@esiee.fr> wrote:
 > 
 > > I am having a cross-platform Intel/ARM sqlite file issue.
 > >
 > > "...the file format is cross-platform. A database that is created
 > > on one machine can be copied and used on a different machine with
 > > a different architecture."
 > >
 > > So, how come I get this (sqlite 3.5.9 and ext3 on both IntelHost
 > > and ARMHost):
 > 
 > [snipped sample]
 > 
 > > Did I miss something ?
 > 
 > Perhaps you missed a compiler option :
 > 
 > http://www.sqlite.org/changes.html#version_3_4_0
 > 
 > * Added the SQLITE_MIXED_ENDIAN_64BIT_FLOAT 
 >   compile-time option to support ARM7 processors
 >   with goofy endianness.
 > 
 > -- 
 > Regards,
 > 
 > Kees Nuyt
 > 
 > ___
 > 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] Cross-platform Intel <-> ARM sqlite file issue...

[Laurent PERROTON]

Hello Everybody,

I am having a cross-platform Intel/ARM sqlite file issue.

"...the file format is cross-platform. A database that is created on one
machine can be copied and used on a different machine with a different
architecture."

So, how come I get this (sqlite 3.5.9 and ext3 on both IntelHost and ARMHost):

IntelHost% sqlite3 test.sqlite 'create table tab (one real, two real);\
insert into tab values (1.2, 1.3);'
IntelHost% sqlite3 test.sqlite 'select * from tab;'
1.2|1.3
IntelHost% scp test.sqlite ARMHost:

ARMHost% sqlite3 test.sqlite 'select * from tab;'
4.66726164313171e-62|-9.25596534088728e+61
ARMHost% sqlite3 test.sqlite 'insert into tab values (2.1, 2.2);'
ARMHost% sqlite3 test.sqlite 'select * from tab;'
4.66726164313171e-62|-9.25596534088728e+61
2.1|2.2

IntelHost% scp ARMHost:test.sqlite .
IntelHost% sqlite3 test.sqlite 'select * from tab;'
1.2|1.3
-9.25596534178523e+61|-2.35343828009147e-185

Did I miss something ?

Thank you very much,

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

[sqlite] Problem installing sw-sso package based on sqlite because of schema version

[Laurent Rathle]

Hello,

I'm trying to install sw-sso package on my Debian server and this package use 
sqlite. The installation fails because of the schema version :

DB file already exists. Let's check for upgrade
DB update failed (Downgrade is not supported (8 -> 7).). See log file for 
detailed output.

Whe I look at the log, I get this :

[2011-04-17T15:10:35Z][4305][DEBUG][Db.php(39) __call] query: select version 
from schema_version;
[2011-04-17T15:10:35Z][4305][INFO][SchemaUtil.php(85) getOrigSchemaVersion] 
Current DB schema version is 8.
[2011-04-17T15:10:35Z][4305][ERROR][DBMigrate.php(27) (main)] [end] DB schema 
update: exception caught at top level Downgrade is not supported (8 -> 7).
[2011-04-17T15:10:36Z][4318][INFO][DBMigrate.php(21) (main)] [begin] DB migrate.
[2011-04-17T15:10:36Z][4318][INFO][SchemaUtil.php(124) update] Required DB 
schema version is 7.
[2011-04-17T15:10:36Z][4318][DEBUG][Db.php(26) __construct] Opening DB 
connection to sqlite:/var/lib/sso/sso.db (readable=true, writable=true)
[2011-04-17T15:10:36Z][4318][DEBUG][Db.php(34) __construct] Opened DB 
connection to sqlite:/var/lib/sso/sso.db
[2011-04-17T15:10:36Z][4318][DEBUG][Db.php(39) __call] query: select version 
from schema_version;
[2011-04-17T15:10:36Z][4318][INFO][SchemaUtil.php(85) getOrigSchemaVersion] 
Current DB schema version is 8.
[2011-04-17T15:10:36Z][4318][ERROR][DBMigrate.php(27) (main)] [end] DB schema 
update: exception caught at top level Downgrade is not supported (8 -> 7).

How can I solve this problem ? The package is used by Plesk and I can't connect 
to it without it.

Debian Lenny 
sqlite 3.5.9-6

Thank you



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

Re: [sqlite] Full text search FTS3 of files

[Dami Laurent (PJ)]

>Is it possible to use FTS3 for search without storing the actual file
>contents/search terms/keywords in a row. In other words, create a FTS3
>tables with rows that only contains an ID and populate the B-Tree with
>keywords for search.
>

Each FTS3 table t is stored internally within three regular tables :
t_content, t_segments and t_segdir. The last two tables contain the
fulltext index. The first table t_content stores the complete documents
being indexed, and is only used when you call the offsets() or
snippets() functions. So if you don't need those functions, you can
cheat : a) call FTS3 to index your document as usual; b) do an update on
the t_content table to remove the document text. 

I did play with that scenario, and gained quite a lot of disk space;
however it's really a hack and maybe wouldn't work in future versions of
SQLite. 
More on
http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite/Cookbook.pod#Spari
ng_database_disk_space 



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

Re: [sqlite] using SQLite with mod_perl

[Dami Laurent (PJ)]

>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] De la part de Clark Christensen
>Envoyé : lundi, 11. octobre 2010 17:26
>À : General Discussion of SQLite Database
>Objet : Re: [sqlite] using SQLite with mod_perl
>
>>So, I want all the perl modules to be loaded when Apache2 starts, and
>>then a $dbh created for each user when the user comes to the web site,
>>but not recreated for the same user on every reload. I am assuming
>>that would be the correct way to work speedily.
>
>I had the same problem, and it's a huge pain, even when you have root
>privilege.
>
>I never did completely solve it.  I had some success with explicit
>$dbh->disconnect calls before the script exits, but I could never prove
>why it
>worked. 

Creating a persistent dbh for each user doesn't make sense to me, because
if the first request from user U1 is served by Apache process P4, there is
no garantee that the next request will again go to process P4; if it goes
to process P2, then that process will need its own connection.  So the 
common practice is one persistent database connection for each Apache process,
not for each user; if you need user_specific stuff, you pass that information
through the shared connection.

Furthermore, SQLite is just a file, it has no notion of users, so one 
connection per user makes even less sense.


 Ultimately, I abandoned mod_perl.  Having to restart Apache
>every time I update code in a module was a big obstacle.
>

Updated scripts are reloaded automatically. Updated modules are not reloaded by
default, but if you want that behaviour you can activate 
http://search.cpan.org/~PHRED/Apache-Reload/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using SQLite with mod_perl

[laurent dami]

  >-Message d'origine-
  >De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
  >boun...@sqlite.org] De la part de P Kishor
  >Envoyé : samedi, 9. octobre 2010 17:10
  >I am just trying to solve the above. It may well be that sqlite and
  >mod_perl may not be good companions (in which case, I am up a 
  >creek without a db, and will have to look for an alternative storage
  >solution).
  >

Sqlite and mod_perl are definitely good companions, as long as one
doesn't mess up with transactions.
Upon startup, Apache starts a collection of servers, which may be either
processes or threads, depending on the MPM (multi-process module, see
http://httpd.apache.org/docs/2.2/mpm.html ); but this doesn't matter much.

Under mod_perl, each of those servers has an embedded perl interpreter,
so indeed they work concurrently. Each server has a loop, listening for
requests, and then producing the answer ... but the server may hold
data that is persistent between requests, and within such data
it is often a very good idea to keep a persistent connection to
the database, avoiding the cost of opening/closing the connection
at each request. Therefore we may have a collection of concurrent
Apache/mod_perl servers, where each server has an open connection
to the database, and that is not a problem.

The important point, however, is to properly open and close a transaction
whenever updating the database, using the begin_work(), commit() and
rollback() methods (see L). This sequence should
ALWAYS happen within a single http request, i.e. do not
start the transaction in one request, expecting the next request to
close that transaction : this will lock your database, because the next
request might never arrive, or it might be served by a different process
or thread than the one that served the initial request.

Good luck,

Laurent Dami


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

Re: [sqlite] EXTERNAL:Re: FTS Question (hyphenated words).

[Dami Laurent (PJ)]

>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] De la part de Black, Michael (IS)
>Envoyé : dimanche, 3. octobre 2010 14:02
>À : General Discussion of SQLite Database
>Objet : Re: [sqlite] EXTERNAL:Re: FTS Question
>
>OK...that make sense...so i hook up my own tokenizerthen how do I
>allow the hyphen to really mean hyphen and not "NOT".
>
>I would've expected "play-off" to be ok and "play -off" to be the NOT
>operator.
>
>Is there some reason why whitespace isn't being taken into account to
>determine the meaning of the hypen?
>


Don't know the reason, I didn't write that code, I just had to study it while 
working on FTS3 integration into Perl's DBD::SQLite.

I didn't try nor didn't check in detail, but if your tokenizer treats 
"play-off" as a single word, then probably the query analyzer will receive one 
single token and will not interpret the hyphen as NOT.

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

Re: [sqlite] FTS Question

[Dami Laurent (PJ)]

Hi Michael,

While indexing, the standard tokenizer treats 'play-off' as two different 
words, so the indexing is exactly the same as for 'play off' or 'play, off'.

Now when querying, the query analyzer treats the hyphen as 'AND NOT', so your 
query really becomes 'play AND NOT off', which explains the result.

Clearly this behaviour is quite rudimentary, not what one would expect when 
being accustomed to Google or other powerful search engines ... but that's what 
the current version of FTS3 has to offer.

To obtain more sophisticated behaviour, you would have to implement your own 
tokenizer and hook it to FTS3. The doc explains how to do this.

Best regards, Laurent Dami


>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] De la part de Black, Michael (IS)
>Envoyé : vendredi, 1. octobre 2010 16:54
>À : General Discussion of SQLite Database
>Objet : [sqlite] FTS Question
>
>I'm experimenting with the FTS capability and have a question
>
>How do I enter a value in the database that is hyphenated?  Seems to be
>backwards form what I would expectif the content contains a hyphen
>then only non-hyphenated query returns the correct value whereas the
>hyphenated one returns the wrong one.
>
>This seems to not work correctly...or am I missing a concept here?
>
>SQLite version 3.7.2
>sqlite> create virtual table dict using fts3(content);
>sqlite> insert into dict values('play-off');
>sqlite> insert into dict values('play-back');
>sqlite> select * from dict where content match 'play-back';
>play-off
>sqlite> select * from dict where content match 'play-off';
>play-back
>sqlite> select * from dict where content match 'play off';
>play-off
>sqlite> select * from dict where content match 'play back';
>play-back
>
>
>Michael D. Black
>Senior Scientist
>Advanced Analytics Directorate
>Northrop Grumman Information Systems
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Disambiguation of Latin accent characters for FTS3

[Dami Laurent (PJ)]

Hi Travis,

You need to define a "tokenizer" to be used by FTS3; something somehow similar 
to user-defined collating sequences.
See  http://www.sqlite.org/fts3.html#section_5_1 

The ICU library has language-specific library functions for  ignoring accents 
while tokenizing.
 
The Perl binding for SQLite has a general-purpose "unaccent" tokenizer, see
http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#FULLTEXT_SEARCH 
and
http://search.cpan.org/~dami/Search-Tokenizer-1.00/lib/Search/Tokenizer.pm 

Or you can write your own tokenizer in C ...

Best regards,
Laurent Dami


>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] De la part de Travis Orr
>Envoyé : jeudi, 30. septembre 2010 17:36
>À : sqlite-users@sqlite.org
>Objet : [sqlite] Disambiguation of Latin accent characters for FTS3
>
>I know it is possible but can't figure out what needs to be done to be
>able to make FTS3 see E as being equal to É. And other similar cases.
>
>
>
>I have a custom collation sequence that does this disambiguation for
>sorting query results, but it doesn't appear to be functioning when
>performing FTS3 queries.
>
>
>
>Thanks
>
>
>
>___
>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] using sqlitejdbc-v056 with ant build file

[Laurent MICHEL]

s appear in the database.
>>>> It will be something to do with these statements not being queries. I
>>>> don't know how you are calling these statements, but often there are two
>>>>
>>>> different methods/functions:
>>>>
>>>> - an exec function which doesn't expect a result set to be returned
>>>> (DELETE, INSERT, UPDATE etc.)
>>>>
>>>> - a query function which does expect a result set (SELECT)
>>>>
>>>> It looks like the statement is being called with a query type function
>>>> or
>>>> method, where as the exec type one would be more suited to the
>>>> statements
>>>> you are calling.
>>>>
>>>> Maybe that will help, maybe not. Is ant talking directly to sqlite-jdbc,
>>>>
>>>> or is there some of your Java code in between (which you could modify)?
>>>>
>>>> Swithun.
>>>> ___
>>>> 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-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-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


-- 
!! BOTH MAIL AND PHONE NUMBER CHANGED!!
 Laurent MICHEL  Tel  (33 0) 3 68 85 24 37
  Observatoire de Strasbourg  Fax  (33 0) 3 68 85 24 32
  11 Rue de l'Universite  Mail laurent.mic...@astro.unistra.fr
  67000 Strasbourg (France)   Web  http://astro.u-strasbg.fr/~michel
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing Regular Expression Support...?

[Dami Laurent (PJ)]

 

>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org 
>[mailto:sqlite-users-boun...@sqlite.org] De la part de J. King
>Envoyé : mardi, 8. décembre 2009 18:24
>À : General Discussion of SQLite Database
>Objet : Re: [sqlite] Implementing Regular Expression Support...?
>
>On Tue, 08 Dec 2009 12:11:13 -0500, li...@mgreg.com   
>wrote:
>
>> Hi All,
>>
>> I'm currently using SQLITE in a few production apps.  I'm 
>using various  
>> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
>> expressions in some of my queries, but I'm not sure how to 
>implement  
>> "user defined functionality".  Where are the hooks?  Is there a  
>> particular mechanism/language I must use to create them?  Is this  
>> something I'm required to recompile SQLITE for?
>
>You do so by defining a user function called 'regexp'.  The 
>means by which  
>one defines a user function depends on the language.  See, for 
>instance,  
>[1] for Ruby.  For a 'regexp' function you would specify two 
>arguments,  
>pattern and string to match against.
>
>[1]  
>ase.html#M000115>
>
>-- 
>J. King

Hi,

If you use Perl, make sure to get the latest version of DBD::SQLite, where the 
SQLite "regexp" function is automatically hooked to Perl regexes. See 
http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#REGEXP_function : 

SQLite includes syntactic support for an infix operator 'REGEXP', but without 
any implementation. The DBD::SQLite driver automatically registers an 
implementation that performs standard perl regular expression matching, using 
current locale. So for example you can search for words starting with an 'A' 
with a query like

  SELECT * from table WHERE column REGEXP '\bA\w+'

If you want case-insensitive searching, use perl regex flags, like this :

  SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] accessing a sqlite db with multiple java processes.

[Laurent Burgy]

my bad...
but i don't know how to block the writing to the database to wait until the
release of the lock... It seems to me that when the writing is impossible
because there's a lock, the driver raises the exception...
Maybe the good thing to do would be to wait for a regular FileLock to be
released and then write...


On Wed, May 27, 2009 at 10:19 AM, Martin.Engelschalk <
engelsch...@codeswift.com> wrote:

> http://www.sqlite.org/faq.html#q5




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

[sqlite] accessing a sqlite db with multiple java processes.

[Laurent Burgy]

Hi,
I was using sqlite with only one java process (using the sqlitejdbc driver)
and everything was ok... now i'm trying to run several instances of the same
program on the same db...and i've a bunch of "database is locked"
exceptions.

i was wandering if it was possible to access the same db with several jobs
?

If so (and maybe this is not the right place to ask), what are the best
practices using sqlitejdbc ? (or any other jdbc driver)...


thanks

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

Re: [sqlite] experiencing SQLite crash (Apache/mod_perl/Catalyst)

[Dami Laurent (PJ)]

>Hi all,
>
>I have a Perl Catalyst app using SQLite as storage. It has been running
>for several months without any problem.
>Now in the last few days we had several crashes of SQLite (file gets
>corrupted). This is just incomprehensible because all development and
>production engineers claim that nothing has changed in 
>application code,
>nor in Catalyst, DBD::Sqlite, Apache, or operating system 
>(Solaris), and
>we can't reproduce the problem in dev. So we don't even know where to
>start our investigations.
>
>Any hints, suggestions, related info, etc. would be welcome.
>
>The production server is running Apache 2.2.8 Solaris / mod_perl 2.04 /
>Perl 5.7.8 / Catalyst 5.7007 / DBD::SQlite 1.13.
>
>Thanks in advance, 
>
>   Laurent Dami
>


For info : we partially understood the problem.

The production server uses a private Apache authentication module
written in C.
That module was accidentally compiled with a debug flag and therefore
was sending
printf(..) statements to STDOUT (probably a very bad idea, instead of
going through Apache logging methods!). 
This went undetected for several months -- the printf were written God
knows where.

What happened in the last few days is that in some occasions the file
descriptor
for accessing SQLite was the same as the stdout used by C printf --
so some logging data was overwriting some blocks inside the SQLite
binary file!
No idea if this sharing of file descriptor is because of Apache or
mod_perl or 
DBD::Sqlite or still something else, nor why this sharing only started a
few
days ago  (maybe a difference in load of the server).

Anyway, the fix is of course to disable debug flag in that C module, so
that
it no longer prints to stdout.

Many thanks to people who responded to my previous call for help.

Best regards,

Laurent Dami





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

[sqlite] experiencing SQLite crash (Apache/mod_perl/Catalyst)

[Dami Laurent (PJ)]

Hi all,

I have a Perl Catalyst app using SQLite as storage. It has been running
for several months without any problem.
Now in the last few days we had several crashes of SQLite (file gets
corrupted). This is just incomprehensible because all development and
production engineers claim that nothing has changed in application code,
nor in Catalyst, DBD::Sqlite, Apache, or operating system (Solaris), and
we can't reproduce the problem in dev. So we don't even know where to
start our investigations.

Any hints, suggestions, related info, etc. would be welcome.

The production server is running Apache 2.2.8 Solaris / mod_perl 2.04 /
Perl 5.7.8 / Catalyst 5.7007 / DBD::SQlite 1.13.

Thanks in advance, 

Laurent Dami





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

[sqlite] Raise not working.

[Laurent LAVAUD]

I have a problem to get < RAISE > function working.

When the trigger is triggered by an insert, i only want to execute the <
update > and skip the first insert.

 

So i tried this with sqlite version 3.3.8 :

 

--

create table blocked (

   idcharacter varying(200),

   ips   character varying(40),

   ipd   character varying(40),

   proto smallint,

   pdinteger,

   hits  bigint default 1

);

 

create trigger checkdrop before INSERT on blocked when (select count(*)
from blocked where id=new.id) > 0

BEGIN

when

update blocked set hits = hits + 1 where id = new.id;

RAISE (IGNORE)

END;

--

 

But i have a syntax error.

I would like to have an equivalent from postgresql with the < return
null > possibility.

 

Thank you in advance for your help.

Re: [sqlite] DLLs containing user-defined SQL functions

[Laurent Blanquet]

Hello,

It could be done using something like that  :

===
sharedenv.h// used in calling program and DLL.
===
typedef  struct{
pointer to functions funA,funB ... etc
} mysharedfuns;

=
Main Program
=
mysharedfuns sharedfuns;

main()
{
 sharedfuns.funA= 
.. etc
 LoadLibray (DLL);
 InitDLL();
}

=
 DLL:
=
#include 
mysharedfuns mainfuns;

int InitDLL(mysharefuns *src)
{
mainfuns.funA=src->funA;
   etc ..
}

The InitDLL function has to be called from the main program just after the
LoadLibrary.
The shared environment is then initialized; functions funA,funB ... are
callable from the DLL..

Best regards,

Laurent Blanquet.
==

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions


I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite.  Things are working great on Unix, but I'm having
issues with Windows.  Windows experts, please help me.

Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA().  The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB().  Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.

This all works great on Unix.  When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.

But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().

Another way to ask the question is this:  How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?

--
D. Richard Hipp   <[EMAIL PROTECTED]>

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions


I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite.  Things are working great on Unix, but I'm having
issues with Windows.  Windows experts, please help me.

Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA().  The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB().  Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.

This all works great on Unix.  When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.

But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().

Another way to ask the question is this:  How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?

--
D. Richard Hipp   <[EMAIL PROTECTED]>

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, June 07, 2006 4:30 PM
Subject: [sqlite] DLLs containing user-defined SQL functions


I'm trying to add the ability to dynamically load DLLs
containing SQL functions and collating sequences to
SQLite.  Things are working great on Unix, but I'm having
issues with Windows.  Windows experts, please help me.

Suppose the main program (the .exe file) contains a
function procA() and the DLL contains a function procB().
I want procB() to be able to call procA().  The idea
is that the main program uses LoadLibrary() to pull
in the DLL, then GetProcAddress() to find the address
of procB().  Then the main program calls procB() in
the DLL which in turn calls procA() in the main program.

This all works great on Unix.  When I use dlopen() to
attach the shared library, the procA() reference in
the shared library is automatically resolved to the
address of procA() in the main program.

But on Windows, I cannot get the DLL to compile because
it is complaining about the missing procA().

Another way to ask the question is this:  How do I build
a DLL in windows that can call routines contained in the
main program that attached the DLL using LoadLibrary()?

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] SQLite to MySQL

[Laurent Goussard]

Ho, thank you Herbet !

But, hmmm, is there a chance you know an opensource or free solution
instead of this expensive one ?

2006/2/6, Laurent Goussard <[EMAIL PROTECTED]>:
> I don't know, I suppose my queries are not as optimized as I thought
> (even if this optimization was my leitmotiv for all the development
> part), or perhaps it's an apache2+php5 issue on my windows computer...
>
> But the fact is since the database has grown (like my traffic : 6000
> visitors/day and 22Mb db file), I've got more and more "maximum
> execution time" errors at the peak hours. I've monitored them, and it
> seems a lot of simultaneous queries are freezing the server and
> finally generates this error.
> The interresting point is the same queries sent a testing mysql db
> while the sqlite part is not responding anymore are working very well,
> So that's the reason why I consider to switch on a mysql solution for
> this website.
>
> Do you got clues concerning conversion ?
>
>
> 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:
> > > Hi there,
> > >
> > > I use SQLite on my website for 2 years now. I do like SQLite a lot and
> > > will use it for a lot of new web projects but, because I got more and
> > > more traffic, I consider to move this one to MySQL in order to reduce
> > > the over load of my computer (I host it @ home).
> >
> > How is this going to reduce load?
> >
> > sqlite = mysql - server code
> >
> > You're adding server code. More code = More load.
> >
>

Re: [sqlite] SQLite to MySQL

[Laurent Goussard]

I don't know, I suppose my queries are not as optimized as I thought
(even if this optimization was my leitmotiv for all the development
part), or perhaps it's an apache2+php5 issue on my windows computer...

But the fact is since the database has grown (like my traffic : 6000
visitors/day and 22Mb db file), I've got more and more "maximum
execution time" errors at the peak hours. I've monitored them, and it
seems a lot of simultaneous queries are freezing the server and
finally generates this error.
The interresting point is the same queries sent a testing mysql db
while the sqlite part is not responding anymore are working very well,
So that's the reason why I consider to switch on a mysql solution for
this website.

Do you got clues concerning conversion ?


2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>:
> > Hi there,
> >
> > I use SQLite on my website for 2 years now. I do like SQLite a lot and
> > will use it for a lot of new web projects but, because I got more and
> > more traffic, I consider to move this one to MySQL in order to reduce
> > the over load of my computer (I host it @ home).
>
> How is this going to reduce load?
>
> sqlite = mysql - server code
>
> You're adding server code. More code = More load.
>

[sqlite] SQLite to MySQL

[Laurent Goussard]

Hi there,

I use SQLite on my website for 2 years now. I do like SQLite a lot and
will use it for a lot of new web projects but, because I got more and
more traffic, I consider to move this one to MySQL in order to reduce
the over load of my computer (I host it @ home).

Do you know if there is a way to convert easily an SQLite database
into a MySQL one ?
(I'm especially thinking about the escapestring and other "create
table [myTable] {" issues...)

Thanks,

Laurent

Re: [sqlite] Problem with DETACH on 2.8.16

[Laurent]

Hello Richard,I'm sorry, but if I do :1. Create a new 
directory.2. Download sqlite 2.8.16 (Windows version for sqlite.org).3. 
Create 2 files script1 and script2 (see attached):4.  Run the 
commands:  sqlite x1.dbx<script1.txt  sqlite 
x2.dbx<script1.txt  sqlite  myfile<script2.txtI get 
this error on Windows XP and Windows W2K.Best 
regards,Laurent.===
attach 'x1.dbx' as d1; 
attach 'x2.dbx' as d2;
detach d1;


 
create table documents (a);
create index i on documents(a);

 

[sqlite] Problem with DETACH on 2.8.16

[Laurent]

Hello,

I'm using sqlite 2.8.16.
I have 2 databases, x1.dbx and x2.dbx, created with the same commands: 

  create table documents (a);
  create index i on documents(a);

Then, if I run :

  attach 'x1.dbx' as d1; 
  attach 'x2.dbx' as d2;
  detach d1;

I get the error :

   Assertion failed: xHash!=0, file hash.c, line 299
   Abnormal program termination

This error will occur using the command line (under Windows) or the DLL.

Could you please check.  
Thanks in advance.

Laurent.