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
I seem to be having an odd behavioral problem with calculating time stamps.
The software I'm using drops data into a field as a local time stamp (IE:
'2010-10-18 04:08:04.000') which is fine. However, when trying to pull that
data back out and convert the Sqlite NOW time to local, I'm getting
On 18 October 2010 09:28, Stephen Chrzanowski pontia...@gmail.com wrote:
I seem to be having an odd behavioral problem with calculating time stamps.
.
.
.
For instance:
select strftime('%s','now') RealUTC,strftime('%s','now','localtime')
LocalTime,
strftime('%s','now') -
Interesting. I get the same results as you when I use sqlite3.exe, but, in
a database manager, the result comes back as I reported. I'll contact the
developer of the utility and see if he can come up with something.
On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies
I made an error in my SQL when I did not include one of my non-aggregate
columns in my group. I was surprised that Sqlite did not catch this, and even
more surprised when the docs spelled out this behavior.
Is everyone ok with this?
Do any other SQL engines allow this?
(DB2 does not)
Sent
Hi,
thanks to everybody that answered. I tried your suggestions but there
was no measurable improvement. Possibly this is the best what I can get
out of Sqlite.
However, I tried a similar query on a larger table using both SQlite and
Postgresql (same machine, same table structure + indices,
Hey guys.
If I wish to log how often a user does action x, I'm assuming I would be
best off doing something like:
/SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x
LIMIT 1/
If a record is returned, perform
/UPDATE actionPerfomedTable SET number = incremented number WHERE
I have a query which takes 17 minutes to run with 3.7.3 against 800ms
with 3.7.2
The query is:
SELECT x.sheep_no, x.registering_flock, x.date_of_registration
FROM sheep x LEFT JOIN
(SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
s.date_of_registration, prev.owner_change_date
On Mon, Oct 18, 2010 at 02:16:55PM +0100, Ian Hardingham scratched on the wall:
If I wish to log how often a user does action x, I'm assuming I would be
best off doing something like:
But it strikes me that an alternative is to have one row for each time
the user performs action x, and to
Query hung. Any help. (sqlite dbi)
SQLite version 3.7.2 built on
This is perl, v5.8.8 built for x86_64-linux-thread-multi
DBI 1.611
DBD::SQLite 1.29
Centos Linux(2.6.18-164.11.1.el5xen) 64bit.
Below are output snippets from two dbi trace files. In the first snippet, the
sqlite.db is smallish,
Hey guys. I'm kind of revisiting something I asked about before. I
have a high scores table, and a table of friends, and I wish to select
for user x:
The score of x
The scores of all of x's friends
Ordered by score descending.
I am using this:
SELECT * FROM (SELECT * FROM cupPlayTable
On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote:
I also want to add selecting the highest score, and adding it to the
results assuming it isn't already in there (ie unless it happens your or
a friends' score is the highest). Could anyone advise me on how best to
do that?
Can you not do
Please reply to the list, so that others may take part in the
conversation, and so that others with similar future questions can
search out the answers in the mailing list archive.
On Mon, Oct 18, 2010 at 02:55:44PM +0100, Ian Hardingham scratched on the wall:
Thanks Jay.
A slightly
Thanks Simon.
I have a further question on this topic.
I would like to find out where my user ranks amongst all scores - so I want to
SELECT name, score FROM scoreTable WHERE id=x
And then I wish to know how many rows occur before the one where user=myuser.
I can loop through them in code,
On Mon, Oct 18, 2010 at 8:16 AM, Ian Hardingham i...@omroth.com wrote:
Hey guys.
If I wish to log how often a user does action x, I'm assuming I would be
best off doing something like:
/SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x
LIMIT 1/
If a record is returned,
There appears to be a problem in the FTS3 module relating to tokenizers.
In my case if I register a custom tokenizer on a database connection
that does not have a table using the custom tokenizer, then when I run
my program with Valgrind I get a lot of Use of uninitialised value of
size 4
I'm seeing some scaling issues (which I'm hoping someone else has encountered
before). I have an application which utilizes multiple threads, each of which
never writes to the database, and none of which shares its cache.
In pcache1Fetch, sqlite mutexes around the cache handling, which appears
Tilghman, Jack jack.tilgh...@navteq.com wrote:
sqlite trace: prepare statement: SELECT COUNT(*) FROM link LEFT OUTER JOIN
node ON node.pvid = link.ref_node_pvid WHERE
link.ref_node_pvid != -1 AND link.ref_node_pvid != -2 AND node.pvid IS
NULL;
Try this instead:
SELECT COUNT(*) FROM link
On Sun, Oct 17, 2010 at 11:13 PM, Dami Laurent (PJ)
laurent.d...@justice.ge.ch wrote:
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
On Mon, 18 Oct 2010 15:07:35 +0200, Hilmar Berger
hilmar.ber...@integromics.com wrote:
Hi,
thanks to everybody that answered. I tried your suggestions but there
was no measurable improvement. Possibly this is the best what I can get
out of Sqlite.
However, I tried a similar query on a larger
On 18 Oct 2010, at 5:25pm, Powell, Jeff wrote:
I have an application which utilizes multiple threads, each of which never
writes to the database, and none of which shares its cache.
In pcache1Fetch, sqlite mutexes around the cache handling, which appears to
be causing significant
Thanks Igor, works great!
Btw, was there something incorrect about the way I had the query setup?
Thanks,
Jack
The information contained in this communication may be CONFIDENTIAL and is
intended only for the use of the recipient(s) named above. If you are not the
intended recipient, you
Tilghman, Jack jack.tilgh...@navteq.com wrote:
Thanks Igor, works great!
Btw, was there something incorrect about the way I had the query setup?
Not incorrect, just wasteful. I suspect the query was spending a lot of time
working through rows where link and node do match up, only to throw
In pcache1Fetch, sqlite mutexes around the cache handling, which appears to
be causing significant waits/scalability issues in my application. If I
disable this mutex, the application crashes.
Why do you think that this mutex causes significant waits?
Anyway ...
Is it possible to cache on
Nice Explanation Igor.
As it turns out, virtually all of the rows will match up as you correctly
suspected.
Thanks again.
Jack
The information contained in this communication may be CONFIDENTIAL and is
intended only for the use of the recipient(s) named above. If you are not the
I did some profiling of our current application through the Intel Parallel
Studio tools, and it identified the mutex in pcache1Fetch as the primary source
of waits. Each thread acts on its own, sharing nothing with the other threads,
so I would expect that there is zero waiting.
-Jeff
Yes, each thread does its own sqlite3_open(). In fact, I get the same behavior
when each thread is using completely separate files (for example, making
multiple copies of the database, with each thread using a different copy).
-Jeff
-Original Message-
From:
On 18 Oct 2010, at 6:25pm, Powell, Jeff wrote:
Yes, each thread does its own sqlite3_open(). In fact, I get the same
behavior when each thread is using completely separate files (for example,
making multiple copies of the database, with each thread using a different
copy).
That second
Take a look at the custom tokenizer API. I think tokens returned don't
necessarily have to be substrings of the text. So, maybe the text you
tokenize could be the file path, but the tokens could be things you
pull from the contents of the file.
Just a thought,
Cheers,
Sam
Jeff,
I can agree that on Windows mutex performance can be awful especially
in such frequently called place as pcache1Fetch. So you have only two
options to solve the problem:
1) Split threads into different processes - make it one thread per process.
2) Make your own implementation of pcache.
On Oct 18, 2010, at 6:58 PM, Igor Tandetnik wrote:
In general, I found that the idiom
TableA left join TableB on (TableA.idInTableB = TableB.someId) where
TableB.someId is null
almost always performs worse than the equivalent NOT EXISTS or NOT IN query.
Hmmm... in practice it should be
I'm not sure if this is an error on my end or on SQLite's. I'm using 3.7.2.
I'm creating two tables as follows:
PRAGMA foreign_keys=ON;
CREATE TABLE 'users' (
'id' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'name' VARCHAR(64) NOT NULL,
PRIMARY KEY('id', 'type') ON CONFLICT REPLACE
);
NSRT Mail account. joecool2...@yahoo.com wrote:
The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an
UPDATE as a DELETE via INSERT INTO from ON CONFLICT
REPLACE?
REPLACE involves deleting conflicting rows, followed by INSERT, as explained by
the documentation at
I would use the update if I knew the entry already existed. In my application
however, it doesn't know if the entry already exists. I was looking for
something to replace MySQL's ON DUPLICATE KEY UPDATE.
I modified my application to use two SQL statements instead.
if (!db.execute(INSERT
On 19/10/2010, at 8:10 AM, NSRT Mail account. wrote:
I would use the update if I knew the entry already existed. In my application
however, it doesn't know if the entry already exists. I was looking for
something to replace MySQL's ON DUPLICATE KEY UPDATE.
I modified my application to use
On Tue, 19 Oct 2010 10:54:13 +1100
BareFeetWare list@tandb.com.au wrote:
-- alternatively you could do this, which will update the existing
row, if exists, or insert a new one if it doesn't:
update users set name = 'Joe C', type = 4, where id = 1;
insert or ignore into users (id, type,
I'm not going to pretend to understand the SQLite source, but it seems
like having a mutex per PCache1 (ie the param passed in to pcache1Fetch
and other cache functions) would be a good approach instead of the global
mutex. But that approach wasn't taken, and I've found everything to be
very
On Oct 19, 2010, at 9:01 AM, Doug wrote:
I'm not going to pretend to understand the SQLite source, but it seems
like having a mutex per PCache1 (ie the param passed in to
pcache1Fetch
and other cache functions) would be a good approach instead of the
global
mutex. But that approach
38 matches
Mail list logo