I was ?bitten? by the fact that in SQLite primary keys can contain NULL
values. As I understood it, I was certainly not the first person to who
this happened. It would not surprise me if there are more deviations that
could spell problems for users of SQLite. That is why I think it would be a
good
On 2016/04/15 11:53 PM, Simon Slavin wrote:
I'm with Simon in response to Cecil on the idea of adding a shortlist of
known "bite" possibilities, something like the whentouse.html or the
howtocorrupt.html. Perhaps named commonmistakes.html or the like.
Maybe we could ask for contributions
I have two tables and a join table, in principle like this:
CREATE TABLE records (id INTEGER PRIMARY KEY, data TEXT);
CREATE TABLE features (id INTEGER PRIMARY KEY, data TEXT UNIQUE);
CREATE TABLE records_features (id_r INTEGER, id_f INTEGER, ord INTEGER);
A record consists of one or more
I would simply use a fourth table that is essentially itself just an
Index, say:
CREATE TABLE records_features_u (
id_r INT NOT NULL,
combo_features TEXT NOT NULL,
PRIMARY KEY (id_r, combo_features)
) WITHOUT ROWID;
IF SUCCEED(
INSERT INTO combo_features_u 10, "20,21,22";
) THEN {
Actually, this would achieve the same, without the extra table:
IF NO_ROWS_ARE_RETURNED_FOR (
SELECT 1 FROM (
SELECT id_r, GROUP_CONCAT(id_f) AS combo
FROM records_features
WHERE id_r = 10
GROUP BY id_r
) WHERE combo = '20,21,22'
) THEN {
INSERT INTO
On 2016/04/16 3:03 AM, R Smith wrote:
> Actually, this would achieve the same, without the extra table:
No it won't work this way at all, I misjudged the outcome. That's what I
get for not testing it - Apologies!
Hello !
Today I decided to see why my applications stop using index when using
my user defined "like" function and it seems that during the creation of
the "LIKE" optimization sqlite3 made some special settings to the
builtin "like" function but didn't exposed it to third party developers.
I
I am playing a bit with SQLite. I first had a table with 1E8 elements. When
trying to drop this it looked like SQLite got hung. I tried it from DB
Browser and a Java program.
I just tried it with a table of 1E7 elements. That was dropped in about 13
seconds.
I will try it again with 1E8 elements,
On 16 Apr 2016, at 10:59am, Cecil Westerhof wrote:
> I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung.
Please tell us which version of SQLite and which journal mode you're using.
My guess is that the operations for 1e7 rows fit in one of the
On 2016/04/16 11:59 AM, Cecil Westerhof wrote:
> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung. I tried it from DB
> Browser and a Java program.
> I just tried it with a table of 1E7 elements. That was dropped in
2016-04-16 14:51 GMT+02:00 Simon Slavin :
>
> On 16 Apr 2016, at 10:59am, Cecil Westerhof
> wrote:
>
> > I first had a table with 1E8 elements. When
> > trying to drop this it looked like SQLite got hung.
>
> Please tell us which version of SQLite and which journal mode you're using.
>
?I work
2016-04-16 14:52 GMT+02:00 R Smith :
>
>
> On 2016/04/16 11:59 AM, Cecil Westerhof wrote:
>
>> I am playing a bit with SQLite. I first had a table with 1E8 elements.
>> When
>> trying to drop this it looked like SQLite got hung. I tried it from DB
>> Browser and a Java program.
>> I just tried it
On 16 Apr 2016, at 2:32pm, Cecil Westerhof wrote:
> ?I work with Java. With:
>SELECT SQLITE_VERSION()
> I get:
>3.8.11?
Thanks.
> ?How can I get the journal mode in Jav??
> With DB Browser I get Delete.
> But when I in sqlite3 give:
>PRAGMA schema.journal_mode;
> I get:
>
When filling a table with 10.000 records the file is 501 KB without auto
commit and 500 KB with auto commit. Not a big difference, but I am
intrigued: can auto commit result in smaller SQLite files?
Is with Java and SQLite 3.8.11.
--
Cecil Westerhof
2016-04-16 15:41 GMT+02:00 Simon Slavin :
>
> > ?How can I get the journal mode in Jav??
> > With DB Browser I get Delete.
> > But when I in sqlite3 give:
> >PRAGMA schema.journal_mode;
> > I get:
> >Error: unknown database schema
>
> That is not well explained. Try just
>
> PRAGMA
2016-04-16 15:44 GMT+02:00 Cecil Westerhof :
> When filling a table with 10.000 records the file is 501 KB without auto
> commit and 500 KB with auto commit. Not a big difference, but I am
> intrigued: can auto commit result in smaller SQLite files?
>
> Is with Java and SQLite 3.8.11.
>
?It is
2016-04-16 16:00 GMT+02:00 R Smith :
>
>
> On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
>
>> 2016-04-16 14:52 GMT+02:00 R Smith :
>>
>>-- 2016-04-16 14:44:55.054 | [Success]Script Success.
>>>
>>> As you can see, the INSERT obviously takes some time (even more-so if the
>>> CHECK
On 2016/04/16 4:09 PM, Cecil Westerhof wrote:
> One strange thing the commandline and DB Browser are using ?3.8.10.2
> while Java is using 3.8.11.
Your command-line is simply outdated - you can download the newest from
http://sqlite.org/download/
DB-Browser might have a newer version also,
2016-04-16 16:19 GMT+02:00 R Smith :
>
>
> On 2016/04/16 4:09 PM, Cecil Westerhof wrote:
>
>> One strange thing the commandline and DB Browser are using ?3.8.10.2
>> while Java is using 3.8.11.
>>
>
> Your command-line is simply outdated - you can download the newest from
>
On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
> 2016-04-16 14:52 GMT+02:00 R Smith :
>
>> Let me try the 100 million rows, this may take some time - I will post
>> again when it is done.
>>
> ?I am curious.
Well, here it is then, 100-million rows: The INSERT took a lot of time,
near 5 hours,
2016-04-16 20:36 GMT+02:00 R Smith :
>
>
> On 2016/04/16 3:39 PM, Cecil Westerhof wrote:
>
>> 2016-04-16 14:52 GMT+02:00 R Smith :
>>
>> Let me try the 100 million rows, this may take some time - I will post
>>> again when it is done.
>>>
>>> ?I am curious.
>>
>
> Well, here it is then,
On 16 Apr 2016, at 8:25pm, Cecil Westerhof wrote:
> I am filling the database again, but now with text UUID instead of blob
> UUID. That takes a ?little? more time. When it is filled I try again.
I assume you're doing many INSERT commands between BEGIN and END.
> Have another problem also. My
Cecil,
If you have a load average of 15 then that normally means you have a
massively overloaded Linux box. I don?t know your system but I get
worried around a load average of 3-4 on our boxes. Load Average is a
very crude measurement but a high number tends to be bad.
If your CPU is only
2016-04-16 21:44 GMT+02:00 Rob Willett :
> If you have a load average of 15 then that normally means you have a
> massively overloaded Linux box. I don?t know your system but I get worried
> around a load average of 3-4 on our boxes. Load Average is a very crude
> measurement but a high number
> Have another problem also. My CPU is about 15%, but the load average is
> also about 15. (This is on a Linux system.) This results (sometimes) in a
> very sluggish system. Can the load be a SQLite problem, or is it a Java
> problem? (When the program is not running, the load average is a lot
>
25 matches
Mail list logo