On 16/10/2010 12:01 PM, Dustin Sallings wrote:
>
> ...but there will also be a unique index on rowid, which will get large
> and need to be maintained. I'm concerned that this alone could be limiting
> me somewhat.
>
> I have a similar application with a single table that I'd like to
This is basically where I am coming from, but deletion (of possibly
millions of entries) is slow and ties up the system (see previous thread
by someone some days back). In experimentation having dynamic tables and
doing a drop table is proving a big winner, though so far only using
about a doze
Hi all
I found the following strange behavior. It's rather easy to stumble upon
with:
sqlite> create table a(id,name);
sqlite> insert into a values (1,'name1');
sqlite> insert into a values (2,'name2');
sqlite> select * from a;
1|name1
2|name2
sqlite> select count(*), name from a;
2|name2
sqlite>
On Oct 15, 2010, at 17:32, Scott Hess wrote:
> Having a table with an owner_id, key, and value, with a unique index
> on (owner_id, key) will probably be more efficient than having a
> separate table per owner. Also, it will be easier to code safely,
> because bind parameters don't work on table
Having a table with an owner_id, key, and value, with a unique index
on (owner_id, key) will probably be more efficient than having a
separate table per owner. Also, it will be easier to code safely,
because bind parameters don't work on table names (I'm assuming you're
using dynamic table names i
There are no schemas per se. Just key+blob. They are backup datasets.
Nothing fancy databasey. Just wondering about the actual impact of
having many tables.
On 15/10/2010 6:54 PM, Simon Slavin wrote:
>
> On 15 Oct 2010, at 7:36am, Andrew Davison wrote:
>
>> What's the take on having hundreds of
On Sat, Oct 16, 2010 at 2:47 AM, Jim Morris wrote:
> Not much help but this removes the multiplication:
> SELECT B1.B + B2.B + B3.B + B4.B FROM
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
> (SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
> (SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
> (SELECT 0 AS B
Not much help but this removes the multiplication:
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4
On 10/15/2010 3:00 PM, Max Vlasov wr
Thanks man... I found I had forgot to fclose() one file after each
iteration, so I'm assuming I was reaching some limit of max files
opened by the same process (though it was the same file each time it
still stacked up).
Ill let it run for a while and see... Iterations were originally set
to 10
Hi,
from time to time I try to solve well-known task of making rows from a
comma-delimited list and the best I could do was this:
- create a user-function returning zero-based Nth item from the list (let's
call it GetItemFromSet)
- make a complex query like this (this one allows up to 16 elements i
On 15 Oct 2010, at 8:11pm, Simon wrote:
> The program is meant to loop forever, grab information from the
> internet and store it in the database. The error happens after 12-24
> hours of execution. The database file is opened at the beginning of
> execution and is never closed (it would if the
On 15 Oct 2010, at 6:43pm, Jeff Flanigan wrote:
> Cool, that definitely tells me the db is corrupt. Is there any way to recover
> a corrupted db, or is it completely borked?
http://www.sqlite.org/sqlite.html
Use the command-line tool to dump the database as a text file (a long list of
SQL com
Hi guys,
first time I see this, not much docs on the topic, or I couldn't
find much on this one...
The program is meant to loop forever, grab information from the
internet and store it in the database. The error happens after 12-24
hours of execution. The database file is opened at the begin
This is most probably a corruption where index have some rowids not
present in table. If nothing else is corrupted you can just drop the
index and recreate it again.
Pavel
On Fri, Oct 15, 2010 at 1:43 PM, Jeff Flanigan wrote:
> Cool, that definitely tells me the db is corrupt. Is there any way t
Cool, that definitely tells me the db is corrupt. Is there any way to recover a
corrupted db, or is it completely borked?
- Original Message -
From: "Simon Slavin"
To: "General Discussion of SQLite Database"
Sent: Friday, October 15, 2010 1:30:34 PM
Subject: Re: [sqlite] Duplicate resul
On 15 Oct 2010, at 6:11pm, Jeff Flanigan wrote:
> My initial guess is this is due to some sort of database corruption,
http://www.sqlite.org/pragma.html#pragma_integrity_check
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.
Gabor,
> If the purpose of this is teaching with R then the R package sqldf
> lets you query all R data frames in your session using sql as if they
> were one big giant database.
No, nothing to do with teaching R, but still a useful point in terms
of broadening the use of SQL with R.
Thanks,
Gr
I am receiving duplicate entries for a given 'row' in a select result. It looks
like the select is finding multiple rows with different rowid (the built-in
hidden column) for a given primary key. My initial guess is this is due to some
sort of database corruption, but any insight would be helpfu
On Fri, Oct 15, 2010 at 12:54 PM, Graham Smith wrote:
> Tom,
>
> Thanks for this.
>
> My main reason for asking is because I am trying to encourage my
> students and indeed clients to think "database" rather than
> "spreadsheet". Most of the time these aren't big or complex data sets
> (normally r
Dan Kennedy-4 wrote:
>
>
> On Oct 15, 2010, at 11:40 PM, Schoinya wrote:
>
>>
>> Hello everybody
>>
>> I'm trying to attach on disk database to in memory database.
>>
>> But I get the strange error : SQLite error unrecognized token: ":"
>>
>> The following is the code:
>>
>>SQ
Tom,
Thanks for this.
My main reason for asking is because I am trying to encourage my
students and indeed clients to think "database" rather than
"spreadsheet". Most of the time these aren't big or complex data sets
(normally records in the hundreds, sometimes the thousands) but still
big enough
On Oct 15, 2010, at 11:40 PM, Schoinya wrote:
>
> Hello everybody
>
> I'm trying to attach on disk database to in memory database.
>
> But I get the strange error : SQLite error unrecognized token: ":"
>
> The following is the code:
>
>SQLiteConnection connInMemory = new
> SQLit
Hello everybody
I'm trying to attach on disk database to in memory database.
But I get the strange error : SQLite error unrecognized token: ":"
The following is the code:
SQLiteConnection connInMemory = new SQLiteConnection("Data
Source=:memory:");
connInMemory.
I also see where you can set the behavior using procnto -- I'll bet "procnto
~i" will make sqlite behave correctly. Though this is a global change.
Anybody who depends on this zeroing though is nuts...
http://www.qnx.com/developers/docs/6.4.0/neutrino/lib_ref/m/munmap_flags.html
There are
The problem is the lack of the unmap call before the 2nd mmap.
It's redundant on most systems but apparently needed to make QNX happy
(including the flags we discussed before on both unmap and mmap).
So we need
ftruncate(fd, 32*1024);
mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd
thank you
De : Igor Tandetnik
À : sqlite-users@sqlite.org
Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s
Objet : Re: [sqlite] restore function
Roger MARTINEZ wrote:
> restore function is not ok for me .
> Before I make a backup with
> /usr/local/sqlite-3.7.2/bin
thank you for answer Igor
At once , i didn't knew if i could use restore function with sqlite corrupt
database file .It seems that if i use corrupt file or empty file, restore
function failed . With database file and table dropped it's ok
Regards
Roger
D
Hi Graham,
>> You don't need to create special tables for data entry. You can create views
>> instead, coupled with "instead of" triggers.
>
>> I'll see if I can put together some SQL with a few examples of how a view
>> updates several >related tables.
>
> As the OP, I would be very intereste
On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote:
> I'm not sure but I suspect sqlite is not calling unmap before
> extending the area.
> That would explain why it still gets zeroed out even with the flags.
>
> Put a break point in the unixShmUnmap call and see if it gets called
> befo
I'm not sure but I suspect sqlite is not calling unmap before extending the
area.
That would explain why it still gets zeroed out even with the flags.
Put a break point in the unixShmUnmap call and see if it gets called before
mmap.
May just need some QNX logic that says "if we're extending a
There are several conditions that should be met for walking the index
to be faster than walking the table.
1) The most important one: index b-tree structure should be organized
in such way that one can walk directly from one leaf to another thus
traversing all leaves without touching interior page
Hi Michael,
Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL" flag to
munmap_flags() call. Don't know where i might be going wrong in SQLite.
As you suggested, I wrote a small application to check if this works.
Fortunately it worked as desired (as given below).
MAP_NOINIT
When
Hilmar Berger wrote:
> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
> any index on b at all, only if I use hardcoded conditions like b > 0.
>
> It appears that the real problem is that SQlite does not use indices for
> both tables
For your problem, there's no way to u
Hilmar Berger wrote:
> Indexes has been created for all fields in A and B (e.g. create index
> name on A(chr, start, stop, strand));
>
> The query is as follows:
>
> select * from
> a, b
> where a.chr = b.chr and a.strand = b.strand and a.start <= b.start
> and a.stop >= b.stop and b.s
Black, Michael (IS) wrote:
> I love simple examples like this can help people with understanding
> things...so I tried this which I thought would do what Hilmar
> wants...but alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite> create table c(achr char,bchr char);
> sqlite> cre
Yup, my bad. Fixed.
On 16/10/2010 12:03 AM, Andrew Davison wrote:
> On 15/10/2010 11:49 PM, Pavel Ivanov wrote:
>>> Now I decide that I want a second type of insert, so I try to use a
>>> prepared statement for that as well. However it always fails. As long as
>>> the other prepared statement is h
I see the difference now...
So I take it that it's faster just to walk the table once rather than walk the
index?
Couldn't you just walk the index once? Smaller data space (quite likely),
better caching? It might be a wash or worse with the potential of having to
retreive other fields from
On 15/10/2010 11:49 PM, Pavel Ivanov wrote:
>> Now I decide that I want a second type of insert, so I try to use a
>> prepared statement for that as well. However it always fails. As long as
>> the other prepared statement is hanging round I can't prepare a new one.
>> Does this seem right or am I
> Now I decide that I want a second type of insert, so I try to use a
> prepared statement for that as well. However it always fails. As long as
> the other prepared statement is hanging round I can't prepare a new one.
> Does this seem right or am I really soing something wrong?
You are doing som
On Fri, Oct 15, 2010 at 8:43 AM, Andrew Davison
wrote:
> In my database I do lots of inserts, of exactly the same nature so I use
> a prepared statement, which I cache, always reseting after use. Works fine.
>
> Now I decide that I want a second type of insert, so I try to use a
> prepared stateme
In my database I do lots of inserts, of exactly the same nature so I use
a prepared statement, which I cache, always reseting after use. Works fine.
Now I decide that I want a second type of insert, so I try to use a
prepared statement for that as well. However it always fails. As long as
the o
> sqlite> create table c(achr char,bchr char);
> sqlite> create index c_chr on c(achr,bchr);
> sqlite> explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?
How do you think it should be used here? It's not that rows with the
same value
Hi,
the condition in your query
select achr,bchr from c where achr=bchr
involves 2 columns of the table. In this case, an index is useless.
If you do
select achr,bchr from c where achr='foo'
then the index will be used.
Martin
Am 15.10.2010 15:09, schrieb Black, Michael (IS):
> Ok then..
Ok then... I added 67,600 records like this and still no index use.
SQLite version 3.7.2
sqlite> select count(*) from c;
67600
sqlite> explain query plan select achr,bchr from c where achr=bchr;
0|0|TABLE c
sqlite> create index c_chr on c(achr,bchr);
sqlite> explain query plan select achr,bchr
On 15-10-10 14:34, Black, Michael (IS) wrote:
> I love simple examples like this can help people with understanding
> things...so I tried this which I thought would do what Hilmar wants...but
> alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite> create table c(achr char,bchr
I love simple examples like this can help people with understanding things...so
I tried this which I thought would do what Hilmar wants...but alaswhat
concept am I missing?
SQLite version 3.7.2
sqlite> create table c(achr char,bchr char);
sqlite> create index c_chr on c(achr,bchr);
sqlite
On 14 October 2010 16:42, Kavita Raghunathan
wrote:
> Hello,
> I’ve been adding and deleting rows from the sqlite database. Now the primary
> ID is non-sequential.
>
>
> 1. How do I get the nth entry in the database
> 2. AND, How do I get the first n rows from the database ?
http://old.nabbl
On 15 October 2010 10:43, Hilmar Berger wrote:
> Thanks !
>
> However, I tried what you suggested and there was no change.
>
> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
> any index on b at all, only if I use hardcoded conditions like b > 0.
>
> It appears that the r
Hello,
I’ve been adding and deleting rows from the sqlite database. Now the primary ID
is non-sequential.
1. How do I get the nth entry in the database
2. AND, How do I get the first n rows from the database ?
Thanks,
Kavita
___
sqlite-users mail
Thanks !
However, I tried what you suggested and there was no change.
I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
any index on b at all, only if I use hardcoded conditions like b > 0.
It appears that the real problem is that SQlite does not use indices for
both t
On 15 Oct 2010, at 7:36am, Andrew Davison wrote:
> What's the take on having hundreds of tables in a database?
Generally not. A database should be designed. By a human. I don't know about
you, but I can't hold hundreds of schema in my head at the same time. Rather
than have two or more tab
Tom
> You don't need to create special tables for data entry. You can create views
> instead, >coupled with "instead of" triggers.
> I'll see if I can put together some SQL with a few examples of how a view
> updates several >related tables.
As the OP, I would be very interested in this. I did
52 matches
Mail list logo
