Re: [sqlite] Inserts get slower and slower

[Steinar Midtskogen]

Thanks to all for suggestions.

> My guesses:
> - Your unix_time values are not successive. In this case your first fast
> results are due to advantages of memory caching. The following slowness is
> the result of the internal fragmentation

All unix_time values should be successive, but in the particular file
used to create the inserts I discovered some corruptions in one area,
random isolated bytes had been replaced with garbage, including bits
of the timestamps, so this was the cause.  Thanks for getting me on
the right track!

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

Re: [sqlite] Inserts get slower and slower

[Max Vlasov]

On Thu, Feb 9, 2012 at 2:08 AM, Steinar Midtskogen wrote:

>
> When I build my database from scratch using millions of inserts, one
> table causes problems.  Inserts get slower and slower.  I have about
> 830,000 inserts for that table.  It gets to 300,000 pretty fast, but
> then it gets slower and slower, and eventually it will only do a few
> inserts per second, and I then I have to kill sqlite3 as it will run
> for hours if not days.
>


My guesses:
- Your unix_time values are not successive. In this case your first fast
results are due to advantages of memory caching. The following slowness is
the result of the internal fragmentation
- You have successive unix_time, but when it goes beyond 32-bit limit your
software wraps around 32-bit value instead of going 64 bit.

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

Re: [sqlite] Inserts get slower and slower

[Simon Slavin]

On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:

> It's the Primary Key that you're using cause for every INSERT it is checked 
> if unix_time is already present in a record.
> 
> So the question is if you really need unix_time as a PK

If you're batching your INSERTs up into transactions, try doing a VACUUM after 
each COMMIT.

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

Re: [sqlite] Inserts get slower and slower

[Oliver Peters]

Am 08.02.2012 23:08, schrieb Steinar Midtskogen:

Hello

I'm having trouble with one table in my database.

When I build my database from scratch using millions of inserts, one
table causes problems.  Inserts get slower and slower.  I have about
830,000 inserts for that table.  It gets to 300,000 pretty fast, but
then it gets slower and slower, and eventually it will only do a few
inserts per second, and I then I have to kill sqlite3 as it will run
for hours if not days.  The -echo option reveals that it gets slower
and slower.  sqlite3 runs at 100% CPU.

I create other similar tables with 830,000 inserts the same way, but
inserts into them don't slow down.

The table in question is:

CREATE TABLE Voksenlia2 (
  temp_in REAL,
  pressure REAL,
  rh_in REAL,
  temp_in_2 REAL,
  temp_in_3 REAL,
  temp_in_4 REAL,
  temp_in_5 REAL,
  temp_ground_0cm REAL,
  temp_ground_10cm REAL,
  temp_ground_20cm REAL,
  temp_ground_50cm REAL,
  radiation INTEGER,
  radiation_2 INTEGER,
  uv REAL,
  temp_uv REAL,

  unix_time INTEGER, PRIMARY KEY (unix_time)
);

The commands start this way:
begin;
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1072915200);
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1075063152);

and eventually end in a "commit".  So only one transaction.

I've tried:

* PRAGMA synchronous=OFF.
* Create the database file in /dev/shm/ (RAM disk).
* Break up the inserts into several transactions.
* Create and fill this table before everything else.
* Upgrade from version 3.4.2 to 3.7.10.

Nothing has made any difference.  Any ideas?  Anything I could try or
any ways to debug this?




It's the Primary Key that you're using cause for every INSERT it is 
checked if unix_time is already present in a record.


So the question is if you really need unix_time as a PK

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

[sqlite] Inserts get slower and slower

[Steinar Midtskogen]

Hello

I'm having trouble with one table in my database.

When I build my database from scratch using millions of inserts, one
table causes problems.  Inserts get slower and slower.  I have about
830,000 inserts for that table.  It gets to 300,000 pretty fast, but
then it gets slower and slower, and eventually it will only do a few
inserts per second, and I then I have to kill sqlite3 as it will run
for hours if not days.  The -echo option reveals that it gets slower
and slower.  sqlite3 runs at 100% CPU.

I create other similar tables with 830,000 inserts the same way, but
inserts into them don't slow down.

The table in question is:

CREATE TABLE Voksenlia2 (
 temp_in REAL,
 pressure REAL,
 rh_in REAL,
 temp_in_2 REAL,
 temp_in_3 REAL,
 temp_in_4 REAL,
 temp_in_5 REAL,
 temp_ground_0cm REAL,
 temp_ground_10cm REAL,
 temp_ground_20cm REAL,
 temp_ground_50cm REAL,
 radiation INTEGER,
 radiation_2 INTEGER,
 uv REAL,
 temp_uv REAL,

 unix_time INTEGER, PRIMARY KEY (unix_time)
);

The commands start this way:
begin;
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1072915200);
insert into Voksenlia2 values(25.010, 1011.260, null, null, null, null, null, 
null, null, null, null, null, null, null, null, 1075063152);

and eventually end in a "commit".  So only one transaction.

I've tried:

* PRAGMA synchronous=OFF.
* Create the database file in /dev/shm/ (RAM disk).
* Break up the inserts into several transactions.
* Create and fill this table before everything else.
* Upgrade from version 3.4.2 to 3.7.10.

Nothing has made any difference.  Any ideas?  Anything I could try or
any ways to debug this?

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

Re: [sqlite] SQLite .NET exception with parallel reading connections

[Yves Goergen]

On 08.02.2012 00:57 CE(S)T, Simon Slavin wrote:
> If most of the time really is spent reading from the file, then it
> may not be worth parallelising your code.  All your data is on the
> same disk and you can read only one sector of the disk at a time.

Most of the file should be in the file system cache anyway. And I'm not
sure about how efficient my reading code is already. I'm going to look
at that, too. But with 2 threads on my Core 2 Duo (no HT) I can see 100%
CPU usage instead of just a bit over 50%, and the time it takes is a bit
below the middle of the longest single job and all jobs sequentially. So
it does seem to help to parallelise it all. (And it's easier for me
because reading and plotting is started from the same function which I
don't want to split.)

> If you can read all your data first in one process, but then
> parallelise the graph-drawing elements, then that might take good
> advantage of multi-cores and multi-processors.

Reading is what takes most of the time for me, plotting is pretty fast.

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interpolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/12 01:47, Steinar Midtskogen wrote:
> OK.  But then I wont have an SQL interface for accessing interpolated 
> data.  It's acceptable.  An SQL interface is mostly a "nice to have", 
> so I could do some quick queries in SQL instead of having to write C 
> code for it.

Making SQL functions that call your C code will do the trick.

Also do you know how easy it is to add a SQL shell to your C code?  Read a
line of input and call sqlite3_complete on it.  If not complete, read
another line appending to earlier line and call sqlite3_complete again.
Rinse and repeat until you have a complete line then prepare, step and
output whatever it returns.

> So, if I go for that approach, you'd recommend that I add functions, 
> such as "reading", and if I want a mean temperature, I should add a 
> function "mean" and not try to change what AVG will do?

Functions you add to SQLite do not have to work on tables mentioned in the
query.  The doc is a little confusing:

  http://www.sqlite.org/c3ref/create_function.html

You need to implement the xFunc callback which is like main() in taking a
count of arguments and their values.  Once decoded just call your existing
C function.

Only you know the nature of missing data, how often it is missing, the
calculations you want to do and how all this affects the final conclusions
you are generating from the data.  (You could also fix the data generator
to synthesize missing values rather than trying to fix it later.)

If you are working with shorter periods (eg a day) then you can also
create temporary tables putting in the missing values.

But if you absolutely need every value to be present for arbitrary SQL
queries then virtual tables will pretty much be the only transparent thing.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8yxmUACgkQmOOfHg372QTN/gCguW0ld/UZxFmjVcLMGFMt88ip
Hz0AoMbgJFwoNXw5DvwLFPe02WCh2Ok1
=C71Q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite .NET exception with parallel reading connections

[Yves Goergen]

On 07.02.2012 23:36 CE(S)T, Joe Mistachkin wrote:
> That issue was fixed prior to release 1.0.77.0, here:
> 
> http://system.data.sqlite.org/index.html/info/13a3981ec0
> 
> If possible, I recommend using the latest released version, 1.0.79.0.

Thanks, that did help. :-)

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

[Dan Kennedy]

On 02/09/2012 12:49 AM, George Ionescu wrote:

Hello Dan,

yes, I thought of that. But wouldn't this break the snippet's function?
If the tokenizer will return text without diacritics, wouldn't the snippet
return the same?


Should be Ok. Snippet should be based on the original content.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Connect As400/Iseries to SQLite .db

[Kevin Benson]

On Wed, Feb 8, 2012 at 10:35 AM, Tim Leland  wrote:

> Hey all,
>
>
>
> Im trying to find a way to connect an as400 to a sqlite database and import
> the data into db2/physical files. Does anyone have any experience with
> something similar to this. I found this site and not sure if this will do
> what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
> greatly appreciated. Thanks
>
>
No knowledge or expertise in this area, but found this tool via web search:

https://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/
IBM Data Movement Tool
Move data from source databases to DB2 in an easy way

It doesn't list SQLite specifically, but mentions using JDBC drivers for a
source database.
Alternately, you may be able to do an intermediate conversion from SQLIte
to a source database type that this tool does support. Hope it helps...

--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on the use of triggers

[Tilsley, Jerry M.]

I will try the check, they may get what I want.  Thanks!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, February 08, 2012 12:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question on the use of triggers

On 2/8/2012 12:30 PM, Tilsley, Jerry M. wrote:
> I creating a new database and would like to use triggers on a couple
> of the tables.  Below are two of the tables I will be working with in
> the triggers;
>
> CREATE TABLE accounts (mrn char(8), acct char(12), discharge_dt int,
> Primary Key(acct), Foreign Key(mrn) references Patient_Info(mrn));
> CREATE TABLE patient_info (name varchar(250), dob varchra(10), mrn
> char(8) primary key not null);
>
> I would like to create a trigger on the patient_info table that is a
> BEFORE TRIGGER

BEFORE what? Before insert, before update, before delete?

> that would verify that the MRN field is 8 characters AND starts with 'MR'.

What should happen when the requirements are met, vs. not met?

If you want to simply fail all attempts to enter a string that doesn't match 
your pattern, you could just put a CHECK constraint on the column:

CREATE TABLE patient_info (name varchar(250), dob varchra(10),
   mrn char(8) primary key not null CHECK(mrn like 'MR__'));

--
Igor Tandetnik

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

Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed.  Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center.  If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited.  If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

[George Ionescu]

Hello Dan,

yes, I thought of that. But wouldn't this break the snippet's function?
If the tokenizer will return text without diacritics, wouldn't the snippet
return the same?

Thanks,
George.

2012/2/8 Dan Kennedy 

> On 02/08/2012 11:34 PM, George Ionescu wrote:
>
>> Hello all,
>> I would like to know how are diacritics handled in FTS, specifically if I
>> can index text with diacritics and search for terms without them.
>>
>> For example, given the queries
>>
>> CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball ro_RO);
>>  INSERT INTO fts_pages (docid,content) VALUES (1, 'România este o ţară
>> frumoasă');
>>
>> the search
>> SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'este'
>> returns 1,
>>
>> but the next search
>> SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'Romania'
>> returns 0.
>>
>> The tokenizer I'm using is based on snowball and can be found at
>> https://bitbucket.org/sevkin/**snowball_fts3
>>
>
> The custom tokenizer needs to normalize the tokens. So when it
> parses "România" it should return "romania" (with no diacritic)
> to FTS. Then when you query for "romania", it will match.
>
> Note that the custom tokenizer is also used to tokenize queries
> as well as documents. So if I query for "România", the tokenizer
> will normalize the query term to "romania" as well - which will
> match the normalized entry in the index.
>
> __**_
> 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] Question on the use of triggers

[Igor Tandetnik]

On 2/8/2012 12:30 PM, Tilsley, Jerry M. wrote:

I creating a new database and would like to use triggers on a couple of the 
tables.  Below are two of the tables I will be working with in the triggers;

CREATE TABLE accounts (mrn char(8), acct char(12), discharge_dt int, Primary 
Key(acct), Foreign Key(mrn) references Patient_Info(mrn));
CREATE TABLE patient_info (name varchar(250), dob varchra(10), mrn char(8) 
primary key not null);

I would like to create a trigger on the patient_info table that is a BEFORE 
TRIGGER


BEFORE what? Before insert, before update, before delete?


that would verify that the MRN field is 8 characters AND starts with 'MR'.


What should happen when the requirements are met, vs. not met?

If you want to simply fail all attempts to enter a string that doesn't 
match your pattern, you could just put a CHECK constraint on the column:


CREATE TABLE patient_info (name varchar(250), dob varchra(10),
  mrn char(8) primary key not null CHECK(mrn like 'MR__'));

--
Igor Tandetnik

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

[sqlite] Question on the use of triggers

[Tilsley, Jerry M.]

All,

I creating a new database and would like to use triggers on a couple of the 
tables.  Below are two of the tables I will be working with in the triggers;

CREATE TABLE accounts (mrn char(8), acct char(12), discharge_dt int, Primary 
Key(acct), Foreign Key(mrn) references Patient_Info(mrn));
CREATE TABLE patient_info (name varchar(250), dob varchra(10), mrn char(8) 
primary key not null);

I would like to create a trigger on the patient_info table that is a BEFORE 
TRIGGER that would verify that the MRN field is 8 characters AND starts with 
'MR'.  Can you assist me in creating this first trigger?

Thanks all!

Jerry


Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RE Connect As400/Iseries to SQLite .db

[Black, Michael (IS)]

So you're wanting to FTP into the AS400 directly?  That makes sense.



All you need to do is download the amalgamation to compile the shell.  Just 
sqlite3.c and shell.c



Hopefully you already know how to use the C compiler on your AS400.



Then transfer one of your sqlite databases over and run "pragma 
integrity_check" and see if it complains.



Good luck.  Looks like you may the first to do this.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 10:46 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

We just want to simplify the process. If pc goes down our files wont be
converted and its just another piece to troubleshoot. As400 can compile C so
that is an option. I don't think anyone has done something like this and
there is not much info on it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Do you have a C compiler on the AS400 you can use?



I couldn't find anybody doing this so if you want to avoid the PC side you
may be on your own to build the shell.



I assume there's some logical reason for not doing the sql dump on the PC
side?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 10:21 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

We are trying to avoid running anything on the pc side. The files are put in
a folder with ftp and then we want to import them into the as400. Attached
is the file format.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Yesjust use the sqlite shell to .dump the database on whatever other
platform you have and copy the file over.

You need to do this in the shell.



.output sqlout.txt

.dump

.quit



If there's nothing crazy in the database it may well be 100% compatible with
db2.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:58 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
sqlite-users 

Re: [sqlite] RE Connect As400/Iseries to SQLite .db

[Tim Leland]

We just want to simplify the process. If pc goes down our files wont be
converted and its just another piece to troubleshoot. As400 can compile C so
that is an option. I don't think anyone has done something like this and
there is not much info on it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Do you have a C compiler on the AS400 you can use?



I couldn't find anybody doing this so if you want to avoid the PC side you
may be on your own to build the shell.



I assume there's some logical reason for not doing the sql dump on the PC
side?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 10:21 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

We are trying to avoid running anything on the pc side. The files are put in
a folder with ftp and then we want to import them into the as400. Attached
is the file format.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Yesjust use the sqlite shell to .dump the database on whatever other
platform you have and copy the file over.

You need to do this in the shell.



.output sqlout.txt

.dump

.quit



If there's nothing crazy in the database it may well be 100% compatible with
db2.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:58 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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

Re: [sqlite] RE Connect As400/Iseries to SQLite .db

[Black, Michael (IS)]

Do you have a C compiler on the AS400 you can use?



I couldn't find anybody doing this so if you want to avoid the PC side you may 
be on your own to build the shell.



I assume there's some logical reason for not doing the sql dump on the PC side?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 10:21 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

We are trying to avoid running anything on the pc side. The files are put in
a folder with ftp and then we want to import them into the as400. Attached
is the file format.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Yesjust use the sqlite shell to .dump the database on whatever other
platform you have and copy the file over.

You need to do this in the shell.



.output sqlout.txt

.dump

.quit



If there's nothing crazy in the database it may well be 100% compatible with
db2.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:58 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

[Dan Kennedy]

On 02/08/2012 11:34 PM, George Ionescu wrote:

Hello all,
I would like to know how are diacritics handled in FTS, specifically if I
can index text with diacritics and search for terms without them.

For example, given the queries

CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball ro_RO);
  INSERT INTO fts_pages (docid,content) VALUES (1, 'România este o ţară
frumoasă');

the search
SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'este'
returns 1,

but the next search
SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'Romania'
returns 0.

The tokenizer I'm using is based on snowball and can be found at
https://bitbucket.org/sevkin/snowball_fts3


The custom tokenizer needs to normalize the tokens. So when it
parses "România" it should return "romania" (with no diacritic)
to FTS. Then when you query for "romania", it will match.

Note that the custom tokenizer is also used to tokenize queries
as well as documents. So if I query for "România", the tokenizer
will normalize the query term to "romania" as well - which will
match the normalized entry in the index.

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

[sqlite] Diacritics handling in FTS with a custom tokenizer

[George Ionescu]

Hello all,
I would like to know how are diacritics handled in FTS, specifically if I
can index text with diacritics and search for terms without them.

For example, given the queries

CREATE VIRTUAL TABLE fts_pages USING fts4(tokenize=snowball ro_RO);
 INSERT INTO fts_pages (docid,content) VALUES (1, 'România este o ţară
frumoasă');

the search
SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'este'
returns 1,

but the next search
SELECT COUNT(1) FROM fts_pages WHERE content MATCH 'Romania'
returns 0.

The tokenizer I'm using is based on snowball and can be found at
https://bitbucket.org/sevkin/snowball_fts3

Thank you,
George.

PS: Other FTS engines (e.g. DTSearch/Sphinx) handle this: you can index
text with diacritics and search with or without them.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Connect As400/Iseries to SQLite .db

[Tim Leland]

Ya I just realize that. Its SQL Lite with two 'L's

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Wednesday, February 08, 2012 11:11 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Connect As400/Iseries to SQLite .db

On 02/08/2012 10:35 PM, Tim Leland wrote:
> Hey all,
>
>
>
> Im trying to find a way to connect an as400 to a sqlite database and
import
> the data into db2/physical files. Does anyone have any experience with
> something similar to this. I found this site and not sure if this will do
> what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
> greatly appreciated. Thanks

Looks like an unrelated product to me. Copyright on the site is
1998, which predates even the earliest version of (the www.sqlite.org)
SQLite by a couple of years.

___
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] RE Connect As400/Iseries to SQLite .db

[Tim Leland]

We are trying to avoid running anything on the pc side. The files are put in
a folder with ftp and then we want to import them into the as400. Attached
is the file format.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db

Yesjust use the sqlite shell to .dump the database on whatever other
platform you have and copy the file over.

You need to do this in the shell.



.output sqlout.txt

.dump

.quit



If there's nothing crazy in the database it may well be 100% compatible with
db2.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:58 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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

Re: [sqlite] Connect As400/Iseries to SQLite .db

[Dan Kennedy]

On 02/08/2012 10:35 PM, Tim Leland wrote:

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks


Looks like an unrelated product to me. Copyright on the site is
1998, which predates even the earliest version of (the www.sqlite.org)
SQLite by a couple of years.

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

Re: [sqlite] Is there any way to speed this query up?

[Richard Hipp]

On Wed, Feb 8, 2012 at 10:57 AM, Timothy Anderson <
t...@systemstechnologyresearch.com> wrote:

> I'm using sqlite 3.7.10 to collect a message log file--basically one
> table, with some metadata (timestamp and message type) plus a blob
> containing the raw message; we use this to play sessions back.
> For one of the message types (video frames), the blob size is ~100K; for
> everything else, no more than a few hundred bytes.
> During playback, there is a particular query that is very slow, and I
> don't understand why. Help?
>
> This is fast:
> SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort,
> DestAddress, DestPort, Message FROM Messages WHERE (messagetype =
> 'VideoFrame');
> This is very slow:
> SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort,
> DestAddress, DestPort, Message FROM Messages WHERE (messagetype =
> 'VideoFrame') order by timestamp;
>
> Both the messagetype and timestamp fields are indexed.
>

I think you want messagetype and timestamp to be indexed together and in
that order.  Like this:

   CREATE INDEX whatever ON messages(messagetype, timestamp);


> If I use one of the message types that has small blobs, the query is
> almost instantaneous; similarly, if I select most of the message types,
> including 'VideoFrame', the query is very fast. It's only when the query is
> limited to that one message type that it's slow, and then only if it's
> sorted. That is, if there is no WHERE clause, or if it's
> WHERE (messagetype <> 'NAV')
> or
> WHERE (messagetype = 'NAV')
> then the query is fast, regardless of sorting.
>
> I have tried changing the page_size to higher values (up to 64K), and
> increasing the cache_size, but neither has any effect.
>
> Thanks for any suggestions.
> Tim Anderson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] RE Connect As400/Iseries to SQLite .db

[Black, Michael (IS)]

Yesjust use the sqlite shell to .dump the database on whatever other 
platform you have and copy the file over.

You need to do this in the shell.



.output sqlout.txt

.dump

.quit



If there's nothing crazy in the database it may well be 100% compatible with 
db2.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:58 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] RE Connect As400/Iseries to SQLite .db

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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] Using WAL without shared memory, no exclusive, but single file descriptor

[Marc L. Allen]

Have a file system that lacks file sharing and based on responses from this 
group, I've implemented a VFS that has all connections using the same file 
share a file descriptor for that file.

It seems to me that this has the same effect as a memory mapped file would.  
Does this mean that I could operate with WAL turned on, even without exclusive 
access?

Thanks,

Marc

--
**
* *  *
* Marc L. Allen   *  "... so many things are *
* *  possible just as long as you*
* Outsite Networks, Inc.  *  don't know they're impossible." *
* (757) 853-3000 #215 *  *
* *  *
* mlal...@outsitenetworks.com *   -- The Phantom Tollbooth   *
* *  *
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RE Connect As400/Iseries to SQLite .db

[Tim Leland]

No we do not need an ongoing connection. The dump method would be fine but
does it have to first be run on the pc side. Anyone try compile sqlite3 to
run on an as400?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite .db

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using
PHP but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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] Is there any way to speed this query up?

[Timothy Anderson]

I'm using sqlite 3.7.10 to collect a message log file--basically one table, 
with some metadata (timestamp and message type) plus a blob containing the raw 
message; we use this to play sessions back.
For one of the message types (video frames), the blob size is ~100K; for 
everything else, no more than a few hundred bytes.
During playback, there is a particular query that is very slow, and I don't 
understand why. Help?

This is fast:
SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort, 
DestAddress, DestPort, Message FROM Messages WHERE (messagetype = 'VideoFrame');
This is very slow:
SELECT Timestamp, MessageType, Topic, IsUDP, SourceAddress, SourcePort, 
DestAddress, DestPort, Message FROM Messages WHERE (messagetype = 'VideoFrame') 
order by timestamp;

Both the messagetype and timestamp fields are indexed.
If I use one of the message types that has small blobs, the query is almost 
instantaneous; similarly, if I select most of the message types, including 
'VideoFrame', the query is very fast. It's only when the query is limited to 
that one message type that it's slow, and then only if it's sorted. That is, if 
there is no WHERE clause, or if it's
WHERE (messagetype <> 'NAV')
or
WHERE (messagetype = 'NAV')
then the query is fast, regardless of sorting.

I have tried changing the page_size to higher values (up to 64K), and 
increasing the cache_size, but neither has any effect.

Thanks for any suggestions.
Tim Anderson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] RE Connect As400/Iseries to SQLite .db

[Black, Michael (IS)]

Any reason you can't just .dump your SQLite database and import into db2?

Or do you need an ongoing connection?



There's a guy in this thread that connected his Linux system to AS400 using PHP 
but the .dump/import would be a lot easier.

http://php.net/manual/en/function.odbc-connect.php





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tim Leland [tlel...@wleeflowers.com]
Sent: Wednesday, February 08, 2012 9:35 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Connect As400/Iseries to SQLite .db

Hey all,



Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks



Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731



___
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] Connect As400/Iseries to SQLite .db

[Tim Leland]

Hey all,

 

Im trying to find a way to connect an as400 to a sqlite database and import
the data into db2/physical files. Does anyone have any experience with
something similar to this. I found this site and not sure if this will do
what Im looking for. http://www.rpgiv.com/sqllite.html Any help will be
greatly appreciated. Thanks

 

Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731

 

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

Re: [sqlite] How complicated can trigger statements be?

[Igor Tandetnik]

Rob Richardson  wrote:
> The example of a CREATE TRIGGER statement from the help page is:
> 
> CREATE TRIGGER update_customer_address UPDATE OF address ON customers
>  BEGIN
>UPDATE orders SET address = new.address WHERE customer_name = old.name;
>  END;
> 
> The use of BEGIN and END to wrap the statement leads me to believe that it's 
> possible to have more than one statement between
> them.

Yes it is.

> And is it possible to have a conditional statement?

There are no conditional statements in SQL. But there are WHERE clauses on DML 
statements, and WHEN clause on CREATE TRIGGER statement

> CREATE TRIGGER record_big_order AFTER INSERT ON orders
>  BEGIN
>IF new.value > 100 THEN
>  INSERT INTO big_orders (customer_name, salesman_id, value)
>  VALUES (new.customer_name, new.salesman_id, new.value)
>END IF;
>  END;

You can make it either

CREATE TRIGGER record_big_order AFTER INSERT ON orders
WHEN new.value > 100
  BEGIN
  INSERT INTO big_orders (customer_name, salesman_id, value)
  VALUES (new.customer_name, new.salesman_id, new.value);
  END;

or

CREATE TRIGGER record_big_order AFTER INSERT ON orders
  BEGIN
  INSERT INTO big_orders (customer_name, salesman_id, value)
  SELECT new.customer_name, new.salesman_id, new.value
  WHERE new.value > 100;
  END;

The former should be a bit more efficient, but you can only have one condition 
for the whole trigger. With the latter technique, you could place conditions on 
each individual statement inside the trigger.
-- 
Igor Tandetnik

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

Re: [sqlite] How complicated can trigger statements be?

[Richard Hipp]

On Wed, Feb 8, 2012 at 9:12 AM, Rob Richardson wrote:

>
> And is it possible to have a conditional statement?
>
> CREATE TRIGGER record_big_order AFTER INSERT ON orders
>  BEGIN
>IF new.value > 100 THEN
>  INSERT INTO big_orders (customer_name, salesman_id, value)
>  VALUES (new.customer_name, new.salesman_id, new.value)
>END IF;
>  END;
>


CREATE TRIGGER record_big_order AFTER INSERT ON orders
WHEN new.value > 100
BEGIN
   INSERT INTO big_orders ...;
END;

See http://www.sqlite.org/lang_createtrigger.html



>
> Thank you very much.
>
> RobR
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] How complicated can trigger statements be?

[Rob Richardson]

The example of a CREATE TRIGGER statement from the help page is:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

The use of BEGIN and END to wrap the statement leads me to believe that it's 
possible to have more than one statement between them:


CREATE TRIGGER update_customer_address UPDATE OF address ON customers

  BEGIN

UPDATE orders SET address = new.address WHERE customer_name = old.name;

INSERT INTO orders_history (customer_name, address) VALUES 
(old.customer_name, old.address);

  END;


And is it possible to have a conditional statement?

CREATE TRIGGER record_big_order AFTER INSERT ON orders
  BEGIN
IF new.value > 100 THEN
  INSERT INTO big_orders (customer_name, salesman_id, value)
  VALUES (new.customer_name, new.salesman_id, new.value)
END IF;
  END;

Thank you very much.

RobR

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

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Kit]

> SELECT a.temp+(b.temp-a.temp)/(b.time-a.time)*(strftime('%s','2012-02-08
> 11:37:00')-a.time) FROM
>   (select time, temp FROM tp
> WHERE strftime('%s','2012-02-08 11:37:00')*1>=time
> ORDER BY time DESC LIMIT 1) AS a,
>   (select time, temp FROM tp
> WHERE strftime('%s','2012-02-08 11:37:00')*1 ORDER BY time LIMIT 1) AS b;

Thanks!  I will try to rewrite this as a view.
-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Kevin Martin]

> For the quick and dirty solution, I think you can use something like this to 
> create your view. You would need to index time, and even with the index, I'm 
> not too sure about speed.
>
> select x1.time, x1.value, x2.time from x as x1 left join x as x2 on 
> x2.time=(select max(time) from x where time solutions require more complicated queries to account for the fact
> that interpolation is going on.

Yes, but if creating views does the queries fast enough for me, I'll
be pragmatic about this. :)

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

Re: [sqlite] freelist_count - can it shrink back?

[Richard Hipp]

On Tue, Feb 7, 2012 at 4:09 PM, Orit Alul  wrote:

> Hi,
>
> I'm using freelist_count pragma to test whether my sqlite db needs
> vacuuming.
> I'm running intensive inserts and deletes to the DB very often.
> I run the freelist_count pragma periodically and I get different values
> every time - sometimes I get large number and sometimes I get 0 or 1.
> Does this makes sense? How can it be explained?
>

When you delete things, that frees up space in the database file.  The
freed space is added to the freelist.  So between iteration 1 and iteration
2 below, you must have had a net deletion of 40011 pages worth of data.



>
> For example:
> Iteration 1:
> Page_count: 17234731
> Freelist_count:  1
>
> Iteration 2:
> Page_count: 17498568
> Freelist_count:  40012
>
> Please advise,
> Thanks,
> Orit
>
>
>
> [Creative Zone by MediaMind]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] sqlite3_changes()!=0 after UPDATE OR IGNORE without change

[Richard Hipp]

On Tue, Feb 7, 2012 at 11:42 AM, Scholz Maik (CM-AI/PJ-CF42) <
maik.sch...@de.bosch.com> wrote:

> Hi,
>
> I have some problem with my understanding of "UPDATE OR IGNORE" and the
> sqlite3_changes() function.
> My expectation is, that changes() gives the number of changed rows.
> When I use "UPDATE OR IGNORE" with same data, so no data change is needed,
> I expect that
> the number of changes is null.
>
> I am wrong?
>

UPDATE OR IGNORE means that the change is not applied if it would have
resulted in a uniqueness or check constraint violation.  The change still
occurs if it is a "no-op" change - if the value being changed too is the
value that was in the table originally.  If you want to avoid a no-op
change, use a WHERE clause:

UPDATE OR IGNORE mytable SET idint=1 WHERE idint<>1;



>
> How can I count the number of rows with data changed?
>
> Regards
>
> Maik Scholz
>
> My Test:
> .headers ON
> .echo ON
> CREATE TABLE mytable (version INT, idint INT, idblob BLOB);
>
> INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'');
> SELECT total_changes(),changes();
>
> SELECT rowid,idint,hex(idblob) from mytable;
> SELECT total_changes(),changes();
>
> UPDATE OR IGNORE mytable SET idint=1;
> SELECT total_changes(),changes();
>
> SELECT rowid,idint,hex(idblob) from mytable;
>
> Execution Log:
> CREATE TABLE mytable (version INT, idint INT, idblob BLOB);
> INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'');
> SELECT total_changes(),changes();
> total_changes()|changes()
> 1|1
> SELECT rowid,idint,hex(idblob) from mytable;
> rowid|idint|hex(idblob)
> 1|1|
> SELECT total_changes(),changes();
> total_changes()|changes()
> 1|1
> UPDATE OR IGNORE mytable SET idint=1;
> SELECT total_changes(),changes();
> total_changes()|changes()
> 2|1
>
> ??? Here I expect total_changes()==1 && changes()==0 because the idint
> column was not changed.
>
> SELECT rowid,idint,hex(idblob) from mytable;
> rowid|idint|hex(idblob)
> 1|1|
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] freelist_count - can it shrink back?

[Orit Alul]

Hi,

I'm using freelist_count pragma to test whether my sqlite db needs vacuuming.
I'm running intensive inserts and deletes to the DB very often.
I run the freelist_count pragma periodically and I get different values every 
time - sometimes I get large number and sometimes I get 0 or 1.
Does this makes sense? How can it be explained?

For example:
Iteration 1:
Page_count: 17234731
Freelist_count:  1

Iteration 2:
Page_count: 17498568
Freelist_count:  40012

Please advise,
Thanks,
Orit



[Creative Zone by MediaMind]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_changes()!=0 after UPDATE OR IGNORE without change

[Scholz Maik (CM-AI/PJ-CF42)]

Hi,

I have some problem with my understanding of "UPDATE OR IGNORE" and the 
sqlite3_changes() function.
My expectation is, that changes() gives the number of changed rows.
When I use "UPDATE OR IGNORE" with same data, so no data change is needed, I 
expect that
the number of changes is null.

I am wrong?

How can I count the number of rows with data changed?

Regards

Maik Scholz

My Test:
.headers ON
.echo ON
CREATE TABLE mytable (version INT, idint INT, idblob BLOB);

INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'');
SELECT total_changes(),changes();

SELECT rowid,idint,hex(idblob) from mytable;
SELECT total_changes(),changes();

UPDATE OR IGNORE mytable SET idint=1;
SELECT total_changes(),changes();

SELECT rowid,idint,hex(idblob) from mytable;

Execution Log:
CREATE TABLE mytable (version INT, idint INT, idblob BLOB);
INSERT OR IGNORE INTO mytable (version,idint,idblob) VALUES (1,1,x'');
SELECT total_changes(),changes();
total_changes()|changes()
1|1
SELECT rowid,idint,hex(idblob) from mytable;
rowid|idint|hex(idblob)
1|1|
SELECT total_changes(),changes();
total_changes()|changes()
1|1
UPDATE OR IGNORE mytable SET idint=1;
SELECT total_changes(),changes();
total_changes()|changes()
2|1

??? Here I expect total_changes()==1 && changes()==0 because the idint column 
was not changed.

SELECT rowid,idint,hex(idblob) from mytable;
rowid|idint|hex(idblob)
1|1|



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

Re: [sqlite] Interpolation

[Kit]

2012/2/8 Steinar Midtskogen :
> 1. I'd like to be able to look up any timestamp between the oldest and
> the newest in the database, and if there is no value stored for that
> timestamp, the value given should be an interpolation of the two
> closest.  So, if the table has:
>
> 1325376000 (Jan 1 2012 00:00:00 UTC) | 5.0
> 1325376300 (Jan 1 2012 00:05:00 UTC) | 10.0
>
> and I do "SELECT temp FROM temperature WHERE unix_time = 1325376120"
> (00:02:00) I should get 7.0.

create table tp (time default (datetime('%s','now')), temp real);

insert into tp values (1328700574,2.5);
insert into tp values (1328701004,4.8);
insert into tp values (1328701060,5.1);
insert into tp values (1328701093,5.2);

select datetime(time,'unixepoch'),temp from tp;

-- 2012-02-08 11:29:34|2.5
-- 2012-02-08 11:36:44|4.8
-- 2012-02-08 11:37:40|5.1
-- 2012-02-08 11:38:13|5.2

SELECT a.temp+(b.temp-a.temp)/(b.time-a.time)*(strftime('%s','2012-02-08
11:37:00')-a.time) FROM
  (select time, temp FROM tp
WHERE strftime('%s','2012-02-08 11:37:00')*1>=time
ORDER BY time DESC LIMIT 1) AS a,
  (select time, temp FROM tp
WHERE strftime('%s','2012-02-08 11:37:00')*1http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interpolation

[Kevin Martin]

For the quick and dirty solution, I think you can use something like this to 
create your view. You would need to index time, and even with the index, I'm 
not too sure about speed.

select x1.time, x1.value, x2.time from x as x1 left join x as x2 on 
x2.time=(select max(time) from x where time Related to this thread, I wonder if it's possible to create a view
> which can give me a value from the row immediately above.  E.g. given
> the table:
> 
> unix_time  val
> --+---
> 1325376000|val1
> 1325376300|val2
> 1325376600|val3
> 1325376900|val4
> 
> (the first column is a unix timestamp and unique)
> 
> can I create a view which gives me:
> 
> unix_time  val  prev_unix_time
> --++--
> 1325376000|val1|
> 1325376300|val2|1325376000
> 1325376600|val3|1325376300
> 1325376900|val4|1325376600
> 
> Something like this will not work:
> 
> create view new as select unix_time, val, (select unix_time from old where 
> new.unix_time < old.unix_time order by unix_time desc limit 1) as 
> prev_unix_time from old;
> 
> as I can't refer to new.unix_time inside the view that defines "new".
> 
> The idea is, if this is possible, then I should be able to get my
> weighted average by something like this (not verified, but you get the
> idea):
> 
> select sum(val * (unix_time - prev_unix_time)) / sum(unix_time - 
> prev_unix_time) from new;
> 
> -- 
> Steinar
> ___
> 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] Interpolation

[Steinar Midtskogen]

Related to this thread, I wonder if it's possible to create a view
which can give me a value from the row immediately above.  E.g. given
the table:

unix_time  val
--+---
1325376000|val1
1325376300|val2
1325376600|val3
1325376900|val4

(the first column is a unix timestamp and unique)

can I create a view which gives me:

unix_time  val  prev_unix_time
--++--
1325376000|val1|
1325376300|val2|1325376000
1325376600|val3|1325376300
1325376900|val4|1325376600

Something like this will not work:

 create view new as select unix_time, val, (select unix_time from old where 
new.unix_time < old.unix_time order by unix_time desc limit 1) as 
prev_unix_time from old;

as I can't refer to new.unix_time inside the view that defines "new".

The idea is, if this is possible, then I should be able to get my
weighted average by something like this (not verified, but you get the
idea):

 select sum(val * (unix_time - prev_unix_time)) / sum(unix_time - 
prev_unix_time) from new;

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

Re: [sqlite] Interpolation

[Steinar Midtskogen]

[Roger Binns]

> I'd recommend you write code in your application first that knows how to
> calculate the values you want.  That way you can ensure the calculations
> are correct, you have something for test harnesses that produces "good"
> values and you have something to port to your final solution.
>
> I'd probably stop there.  Remember that your application code and the
> SQLite library are running in the same process.  It is almost certain that
> it is (better/more convenient/easier to develop and use) for this code to
> be app code than within SQLite.

OK.  But then I wont have an SQL interface for accessing interpolated
data.  It's acceptable.  An SQL interface is mostly a "nice to have",
so I could do some quick queries in SQL instead of having to write C
code for it.

> If someone will be doing queries expecting to match a row with second
> granularity then your SQLite side solutions are virtual tables and
> functions.  The former is well documented.  For the latter you can make a
> function like "reading" which behind the scenes calls your app code which
> prepares a statement, finds neighbouring readings and returns the
> interpolated result - eg `select reading("2012-01-01T012345")`

So, if I go for that approach, you'd recommend that I add functions,
such as "reading", and if I want a mean temperature, I should add a
function "mean" and not try to change what AVG will do?

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