Re: [sqlite] sqlite3.exe and formatting binary (GUID) data

[John Machin]

On 25/05/2009 2:30 PM, Philip Warner wrote:

> I use binary GUID values and when I 'select' them in the sqlite3 shell,
> they display as *binary* data, often screwing up the terminal. When I
> use .dump, or use .mode tcl I get terminal-friendly output. Is there any
> simple way (or just a hack) that I could persuade the shell to output
> GUIDs (or all binary data) in the same format as for .dump?

Assuming your guid is a BLOB, then
   SELECT other_stuff, QUOTE(guid), etc
will display it as hex e.g. X'01020304'
otherwise if your guid is TEXT, all I can say is "don't do that".


> P.S. Of course even better would be the ability to read/write GUIDs as
> properly formatted strings! (Note: I am not asking that they be treated
> internally as anything other than a binary chunk of data -- just the
> text  form being changed).

On output, how do you expect it to determine what blobs are guids? 
column_name like  '%guid%' ??

On input: does the SQL standard define a guid literal?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3.exe and formatting binary (GUID) data

[Philip Warner]

Hi,

I'm using sqlite3 and am very impressed. But, have one small problem.

I use binary GUID values and when I 'select' them in the sqlite3 shell,
they display as *binary* data, often screwing up the terminal. When I
use .dump, or use .mode tcl I get terminal-friendly output. Is there any
simple way (or just a hack) that I could persuade the shell to output
GUIDs (or all binary data) in the same format as for .dump?

Thanks,

Philip Warner

P.S. Of course even better would be the ability to read/write GUIDs as
properly formatted strings! (Note: I am not asking that they be treated
internally as anything other than a binary chunk of data -- just the
text  form being changed).

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

Re: [sqlite] dealing with dates...

[Igor Tandetnik]

"Sam Carleton" 
wrote in message
news:8d38ca0a0905241955j2f5f8062l43e68c8f9e48f...@mail.gmail.com
> I am using julianday('now') to set the insertedon date to my tables.
> The value needs to be returned to the client via Web Service so that
> the client and return it later to get the next inserted record. I am
> currently returning it as a double, somewhere along the way it is
> getting trunkated:
>
> From 2454976.5929914005 to 2454976.592991

Are you using sprintf("%f"), by any chance? Default precision is 6 
fractional digits.

Note that a double can accurately represent 15 decimal digits, so you 
are not losing all that much. But you do need 8 fractional digits if you 
want millisecond accuracy.

> How best to solve this issue? I am thinking it is best to convert to
> a string format for transport via Web Service and convert back to the
> native format for the next select. My option problem is I cannot make
> heads or tails of how to convert the double into
> -MM-DDTHH:MM:SS.SSS and back.

select strftime('%Y-%m-%dT%H:%M:%f', 2454976.5929914005)
2009-05-25T02:13:54.457

select julianday('2009-05-25T02:13:54.457')
2454976.5929914

Igor Tandetnik 



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

Re: [sqlite] about sqlite on line back up API

[pierr chen]

Hi Dan,
  I checked the implementation of back up API and did not notice anywhere
had turned on "PRAGMA locking_mode=exclusive" or any other similar PRAGMA
explicitly.
  And another thing wonder me is why I did not see the journey file before
power off ?


>
> >  b. There always a journey file exist when I reset the system. I
> > am
> > thinking that If I reset the system after the loadOrSaveDb()
> > returned ,as
> > shown below,there should be no journey file exist as the write has
> > finished.
>
> Did you open the flash database with "PRAGMA locking_mode=exclusive", or
> some other option that would turn on persistent journals?
>
> Dan.
>
>
>


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

[sqlite] dealing with dates...

[Sam Carleton]

I am using julianday('now') to set the insertedon date to my tables.
The value needs to be returned to the client via Web Service so that
the client and return it later to get the next inserted record.  I am
currently returning it as a double, somewhere along the way it is
getting trunkated:

From 2454976.5929914005 to 2454976.592991

How best to solve this issue?  I am thinking it is best to convert to
a string format for transport via Web Service and convert back to the
native format for the next select.  My option problem is I cannot make
heads or tails of how to convert the double into
-MM-DDTHH:MM:SS.SSS and back.

Might there be a better way?

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

Re: [sqlite] queries for a fulltext-engine

[John Machin]

On 24/05/2009 4:30 AM, Lukas Haase wrote:
> Hi,
> 
> Sorry for the subject - I just do not know for what to search of what to 
> ask - I actually do not know where is exactly my problem :-(
> 
> I have a database containing thousands of HTML pages ("topics"). There 
> is a fulltext index for these topics. First there is a table containing 
> all single words. Each word is identified by its "fulltextID":
> 
> CREATE TABLE fulltext(
>   fulltextID INTEGER PRIMARY KEY,
>   word VARCHAR(100) COLLATE NOCASE
> );
> 
> Now there is a linking table between the words and the HTML pages (topics):
> 
> CREATE TABLE topic_fulltext(
>   topicID INTEGER,
>   fulltextID INTEGER,
>   PRIMARY KEY(topicID, fulltextID)
> );

Call me crazy, but shouldn't the two components of the PK be reversed?

> Finding a topic containing a specific word is not too hard:
> 

Just put EXPLAIN QUERY PLAN in here ...

> SELECT topic_fulltext.topicID
> FROM fulltext
> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
> WHERE word LIKE 'Word%';

and you will see:
0|0|TABLE fulltext
1|1|TABLE topic_fulltext

but if you change the order of the columns in the PK you will see:
0|0|TABLE fulltext
1|1|TABLE topic_fulltext WITH INDEX sqlite_autoindex_topic_fulltext_1

which I'd interpret as a significant improvement.

Second suggestion: Although you may be a careful programmer and quite 
sure that you will never have duplicate or null words in your fulltext 
table, let's just go into nanny mode and tack UNIQUE NOT NULL onto the 
definition of the "word" column ... with this result:

0|0|TABLE fulltext WITH INDEX sqlite_autoindex_fulltext_1
1|1|TABLE topic_fulltext WITH INDEX sqlite_autoindex_topic_fulltext_1

Crazy ** 2 :-)

Third suggestion: read up carefully what has been written about when 
LIKE will use an index.

> 
> But now I want to be able to search with more complex queries. For example:
> 
> * List all topics containing (word1 AND word2)
> * List all topics containing (word1 OR word2)
> * List all topics containing (word1 AND word2 AND ... AND word10)
> * List all topics containing ((word1 OR word2) AND word3 OR word3)
> * ...
> 
> But now I have no clue how to accomplish this query. I just can't extend 
> my single-word query from above :-(
> 
> Can anyone give me a hint?

This problem has been addressed more than once over the last 30 or so 
years ... I'd be very surprised to find that any serious implementation 
used SQL queries for 100% of the work.

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

Re: [sqlite] queries for a fulltext-engine

[Simon Slavin]

On 24 May 2009, at 1:28pm, Lukas Haase wrote:

> Simon Slavin schrieb:
>> On 23 May 2009, at 7:30pm, Lukas Haase wrote:
>>> SELECT topic_fulltext.topicID
>>> FROM fulltext
>>> JOIN topic_fulltext ON topic_fulltext.fulltextID =  
>>> fulltext.fulltextID
>>> WHERE word LIKE 'Word%';
>>
>> WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%')
>> WHERE (word LIKE 'Word1%') OR (word LIKE 'Word2%')
>
> No, this is unfortunately not the case :-( I know the usage of LIKE, %
> and =.
>
> But the problem is here that
>
> SELECT topic_fulltext.topicID
> FROM fulltext
> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
> WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%');
>
> would give me no results.

Oh, I get it.  You have indexed your documents and have a many-to-many  
relationship which is something like

column 1 = word
column 2 = document number

So one word appears in many documents and one document contains many  
words.  That makes sense.

In the case it looks like the best way is to build up a TEMPORARY  
table something like

column 1 = document

using INSERT OR IGNORE, parsing your search requirements (AND, OR,  
NOT, etc.) yourself and turning them into successive INSERT and DELETE  
commands.  So an 'OR' relationship would turn into two INSERT  
commands, whereas an 'AND' relationship would be an INSERT and then a  
DELETE for all cases where the document didn't contain the second  
word.  If you want to allow bracketing in your search strings the  
programming can be complicated.  If you don't, and just work from the  
left to the right of your search strings it should be simple.

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

Re: [sqlite] queries for a fulltext-engine

[Emil Obermayr]

On Sun, May 24, 2009 at 02:28:36PM +0200, Lukas Haase wrote:
> 
> SELECT topic_fulltext.topicID
> FROM fulltext
> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
> WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%');

Obviously this must be empty because a string can't start with "Word1"
and start with "Word2" at the same time.

Maybe you should try % on both ends of the search string.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] queries for a fulltext-engine

[Igor Tandetnik]

"Lukas Haase"  wrote in
message news:gv9fcm$5r...@ger.gmane.org
> I have a database containing thousands of HTML pages ("topics"). There
> is a fulltext index for these topics. First there is a table
> containing all single words. Each word is identified by its
> "fulltextID":
>
> CREATE TABLE fulltext(
> fulltextID INTEGER PRIMARY KEY,
> word VARCHAR(100) COLLATE NOCASE
> );
>
> Now there is a linking table between the words and the HTML pages
> (topics):
>
> CREATE TABLE topic_fulltext(
> topicID INTEGER,
> fulltextID INTEGER,
> PRIMARY KEY(topicID, fulltextID)
> );
>
> Finding a topic containing a specific word is not too hard:
>
> SELECT topic_fulltext.topicID
> FROM fulltext
> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
> WHERE word LIKE 'Word%';
>
> But now I want to be able to search with more complex queries. For
> example:
>
> * List all topics containing (word1 AND word2)

You could do something like this:

SELECT topic_fulltext.topicID FROM topic_fulltext
where exists (select 1 from fulltext
WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 
'word1%')
and exists (select 1 from fulltext
WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 
'word2%')

-- or

SELECT topic_fulltext.topicID FROM topic_fulltext
where fulltextID in (
select fulltextID from topic_fulltext where word LIKE 'word1%'
intersect
select fulltextID from topic_fulltext where word LIKE 'word2%');

Test it, see which one works faster.

> * List all topics containing (word1 OR word2)

Similar to above, but replace AND with OR, and INTERSECT with UNION ALL.

> * List all topics containing (word1 AND word2 AND ... AND word10)
> * List all topics containing ((word1 OR word2) AND word3 OR word3)

The approach above should work for any boolean combination.

Igor Tandetnik 



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

Re: [sqlite] queries for a fulltext-engine

[Lukas Haase]

Hi,

Igor Tandetnik schrieb:
> "Lukas Haase"  wrote in
> message news:gv9fcm$5r...@ger.gmane.org
>> I have a database containing thousands of HTML pages ("topics"). There
>> is a fulltext index for these topics. First there is a table
>> containing all single words. Each word is identified by its
>> "fulltextID":
> 
> You seem to be reinventing FTS:
> 
> http://www.sqlite.org/cvstrac/wiki?p=FtsUsage

Thank you, but unfortunately not.

First, there are 2 topics, second, these topics are HTML code 
fragments and third, they are all compressed externally with gzip for 
saving memory.

For that reason I built this index, listing all words for a
specific topic...

Luke

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

Re: [sqlite] queries for a fulltext-engine

[Lukas Haase]

Hi,

At first, thank you for your answer!

Simon Slavin schrieb:
> On 23 May 2009, at 7:30pm, Lukas Haase wrote:
>> SELECT topic_fulltext.topicID
>> FROM fulltext
>> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
>> WHERE word LIKE 'Word%';
>>
>> But now I want to be able to search with more complex queries. For  
>> example:
>>
>> * List all topics containing (word1 AND word2)
>> * List all topics containing (word1 OR word2)
> 
> It works perfectly to do things like
> 
> WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%')
> WHERE (word LIKE 'Word1%') OR (word LIKE 'Word2%')

No, this is unfortunately not the case :-( I know the usage of LIKE, % 
and =.

But the problem is here that

SELECT topic_fulltext.topicID
FROM fulltext
JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%');

would give me no results.

And the one statement with

WHERE (word LIKE 'Word1%') OR (word LIKE 'Word2%');

would work...

And, I have forgotten some other search cirteria:

* List all topics containing (word1 AND NOT word2)

> [...]
>> * List all topics containing (word1 AND word2 AND ... AND word10)
>> * List all topics containing ((word1 OR word2) AND word3 OR word3)
> 
> You could write something to transform those into the format I showed  
> above.  And you could get the results directly using SELECT or use  
> CREATE VIEW to reflect them.

This indeed no problem but the query does not work. The problem is that 
I have a list of words and then a table which topic contains which 
words. So if I want to know which *topics* are linked with

* word1 OR word2
* word1 AND word2
* word1 AND NOT word2

I need a completely different query.

> But there are other ways to do it that might be more efficient (i.e.  
> faster) or simpler to program and debug.  For instance, if you have a  
> chain of conditions you could CREATE TEMPORARY a table to then use it  
> to accumulate (OR) or eliminate (AND) the pages you want using the  
> form of INSERT that takes a SELECT argument.  Depending on the size  
> and shape of your database this may or may not be faster.

Yes, the DB is very huge. There are 20k topics and the fulltext and 
topic_fulltext tables are approx. 50MB.

> [...]
> Simon.

Luke

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