Re: [sqlite] Is _reset() removes binding?

[Igor Tandetnik]

On 5/1/2013 7:30 PM, Igor Korot wrote:

1. Is the call to sqlite3_reset() removes the binding for the column a?


No. You can use sqlite3_clear_bindings for that.


2. Do I need to call sqlite3_reset() at all?


Yes. You can't bind parameters to an active statement.
--
Igor Tandetnik

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

Re: [sqlite] How people test db failures

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/13 12:00, Igor Korot wrote:
> So, how people check whether db failure result in graceful program 
> termination/proper flow?

I use a macro that takes this form:

#define TESTPOINT(name, normal, failure)

I use it like this:

   TESTPOINT(StepNoMem, rc=sqlite3_step(stmt), rc=SQLITE_NOMEM);

The macro expands to something like this with debug builds:

  do {
 if(_point_should_fail(#name) { failure; }
 else { normal; }
  } while(0)

The _point_should_fail function returns true once and then false from then
on.  My test suite would then look like:

  def test2c():
 set_point_should_fail("StepNoMem")
 ... code that should handle the failure correctly ...

Of course reality is a little messier than this.  Here is some code that
sets a failure point:

  https://code.google.com/p/apsw/source/browse/src/apsw.c#217

Here is the corresponding test:

  https://code.google.com/p/apsw/source/browse/tests.py#7562

(I use 1/0 to ensure that there will always be an exception, and if it
turns out to be ZeroDivision then I know the previous line didn't cause
one and should have.)

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

iEYEARECAAYFAlGBp+YACgkQmOOfHg372QQsrACgiDu/z2nn+NZdf/Q/Ep4JBrFu
U24An3J26qO54dT89vSVKo/Js60O7pwc
=mc1D
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is _reset() removes binding?

[Igor Korot]

Hi, ALL,
Consider this code:

int var1 = 5, var2 = 10;
query = "SELECT * FROM foo WHERE foo.a = ? AND foo.b = ?;";
if( ( result = sqlite3_prepare_v2( handle, query, -1, , 0 ) ) ==
SQLITE_OK )
{
 sqlite3_bind_int( stmt, 1, var1 );
 for( int i = 0; i < 10; i++ )
 {
   sqlite3_bind_int( stmt, 2, var2++ );
   result = sqlite3( step );
   if( result == SQLITE_DONE )
   sqlite3_reset( stmt );
   else
   printf( "Error occur!" );
 }
 sqlite3_finalize( stmt );
}

1. Is the call to sqlite3_reset() removes the binding for the column a?
2. Do I need to call sqlite3_reset() at all?

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

Re: [sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Richard Hipp]

On Wed, May 1, 2013 at 3:23 PM, Anderson Medeiros Gomes
wrote:

> Hi. I think I found a bug in SQLite, so I'm reporting it in this message.
>
> The print screen I have attached shows a query that SQLite executes and
> brings no results. I believe SQLite should trigger an error while parsing
> my input, because I used an unknown column in the subquery.
>

SQLite is giving the correct response here.  The "foocolumn" in the
subquery refers out to the containing query.  We say that the subquery is a
"correlated subquery" because it contains references to the outer query.

In your case, the query is logically equivalent to:

   SELECT foocolumn FROM footable WHERE 123 NOT IN (SELECT 123 FROM
bartable);

Since bartable is not empty, the NOT EXISTS is always false and the query
returns no rows.



>
> This is the print screen's textual representation:
>
> $ sqlite3 /tmp/test.sqlite
> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE footable (foocolumn INTEGER);
> sqlite> CREATE TABLE bartable (barcolumn INTEGER);
> sqlite> INSERT INTO footable (foocolumn) VALUES (1);
> sqlite> INSERT INTO bartable (barcolumn) VALUES (2);
> sqlite> *SELECT foocolumn FROM footable WHERE foocolumn NOT IN (SELECT
> foocolumn FROM bartable);*
> sqlite> exit
>...> ;
> Error: near "exit": syntax error
> sqlite> .quit
>
>
>
> --
> Anderson Medeiros Gomes
> amg1...@gmail.com
>
> ___
> 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] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Nico Williams]

On Wed, May 1, 2013 at 2:23 PM, Anderson Medeiros Gomes
 wrote:
> Hi. I think I found a bug in SQLite, so I'm reporting it in this message.

I do't think it's a bug.  Correlated sub-queries can refer to columns
from table sources outside them.  Your example query is silly, no
doubt, but it is not incorrect for it to compile and run.

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

[sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Anderson Medeiros Gomes]

Hi. I think I found a bug in SQLite, so I'm reporting it in this message.

The print screen I have attached shows a query that SQLite executes and
brings no results. I believe SQLite should trigger an error while parsing
my input, because I used an unknown column in the subquery.

This is the print screen's textual representation:

$ sqlite3 /tmp/test.sqlite
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE footable (foocolumn INTEGER);
sqlite> CREATE TABLE bartable (barcolumn INTEGER);
sqlite> INSERT INTO footable (foocolumn) VALUES (1);
sqlite> INSERT INTO bartable (barcolumn) VALUES (2);
sqlite> *SELECT foocolumn FROM footable WHERE foocolumn NOT IN (SELECT
foocolumn FROM bartable);*
sqlite> exit
   ...> ;
Error: near "exit": syntax error
sqlite> .quit



--
Anderson Medeiros Gomes
amg1...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[James K. Lowden]

On Wed, 1 May 2013 07:02:38 -0400
Richard Hipp  wrote:

> On Wed, May 1, 2013 at 12:42 AM, James K. Lowden
> wrote:
> 
> > On Tue, 30 Apr 2013 12:59:17 -0400
> > Richard Hipp  wrote:
> >
> > > http://www.sqlite.org/draft/queryplanner-ng.html
> >
> > Feel free to use this version of the diagram.
> >
> > http://www.sqlite.org/draft/queryplanner-ng.html
> >
> 
> Did you intend the above to be a different hyperlink?

Ach, sorry, yes: 

http://www.schemamania.org/groff/pic/tcp-h-q8/tcp-h-q8.png

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

[sqlite] How people test db failures

[Igor Korot]

Hi, ALL,
I wrote a code that executes fine. There is no memory leaks and no
issues.

However, trying to check whether my error handling is written correctly, I
set
breakpoint before accessing db and when I hit it I manually changes the
value returned.
This is not how it should be done and I feel that the person I'm working
with will need to check it
at some point.

So, how people check whether db failure result in graceful program
termination/proper flow?

I'm working on the desktop application under Windows and am giving the
release version of the program
compiled under MSVC 2010.

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

Re: [sqlite] Performance issue with JOIN and large IN operator

[Richard Hipp]

On Wed, May 1, 2013 at 2:11 PM, jic  wrote:

> "Richard Hipp" wrote...
>
> Dr. Hipp,
>
> will this fix break the work-around you provided,
>
> "
> to put a "+" sign in front of the "elements.id" identifier in the ON
> clause:
>
>  SELECT count(*) FROM elements JOIN tags ON +elements.id = tags.element_id
>WHERE elements.id IN ();
> "
> or will it also work?
>

They should both work.

-- 
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] Performance issue with JOIN and large IN operator

[jic]

"Richard Hipp" wrote...


On Wed, May 1, 2013 at 11:24 AM, Richard Hipp  wrote:




On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer <
martin.altma...@googlemail.com> wrote:


Hi,

I have a query that runs more than 400x slower in 3.7.16.2 than in 
3.7.11.




This seems to be caused by the use of transitive constraints in version
3.7.16.  Your work-around (until an official fix is available in SQLite) 
is

to put a "+" sign in front of the "elements.id" identifier in the ON
clause:

  SELECT count(*) FROM elements JOIN tags ON +elements.id =
tags.element_id

WHERE elements.id IN ();

Thank you for the trouble report.



I think the problem is fixed with 
http://www.sqlite.org/src/info/faedaeace9


Dr. Hipp,

will this fix break the work-around you provided,
"
to put a "+" sign in front of the "elements.id" identifier in the ON clause:

 SELECT count(*) FROM elements JOIN tags ON +elements.id = tags.element_id
   WHERE elements.id IN ();
"
or will it also work?

Thanks. 


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

Re: [sqlite] Performance issue with JOIN and large IN operator

[Richard Hipp]

On Wed, May 1, 2013 at 11:24 AM, Richard Hipp  wrote:

>
>
> On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer <
> martin.altma...@googlemail.com> wrote:
>
>> Hi,
>>
>> I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11.
>>
>
> This seems to be caused by the use of transitive constraints in version
> 3.7.16.  Your work-around (until an official fix is available in SQLite) is
> to put a "+" sign in front of the "elements.id" identifier in the ON
> clause:
>
>   SELECT count(*) FROM elements JOIN tags ON +elements.id =
> tags.element_id
>
> WHERE elements.id IN ();
>
> Thank you for the trouble report.
>

I think the problem is fixed with http://www.sqlite.org/src/info/faedaeace9



>
>
>
>> Instead of posting the original query, I post a simplified version which
>> still experiences the problem with a factor of over 100x:
>>
>> SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id
>> WHERE elements.id IN ();
>>
>> where  is large (e.g. 1,2,3,...,2000). In my application this is not
>> a contiguous list, so I cannot use BETWEEN.
>>
>> To demonstrate the issue it suffices that both tables just contain a
>> single
>> column which is filled with e.g. the integers from 1 to 4000.
>>
>> CREATE TABLE elements (id INTEGER PRIMARY KEY);
>> CREATE TABLE tags (element_id INTEGER);
>>
>> The running time in 3.7.16.2 increases heavily with the length of ,
>> which is not the case in 3.7.11.
>> As far as I know, indices do not improve the situation (my original
>> database has indices).
>> Removing the join solves the problem, but in the original query the join
>> is
>> necessary, because I do not only select COUNT(*).
>>
>> I tested this on several Linux machines.
>>
>> Thanks in advance,
>> Martin
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org




-- 
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] Performance issue with JOIN and large IN operator

[Richard Hipp]

On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer <
martin.altma...@googlemail.com> wrote:

> Hi,
>
> I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11.
>

This seems to be caused by the use of transitive constraints in version
3.7.16.  Your work-around (until an official fix is available in SQLite) is
to put a "+" sign in front of the "elements.id" identifier in the ON clause:

  SELECT count(*) FROM elements JOIN tags ON +elements.id = tags.element_id
WHERE elements.id IN ();

Thank you for the trouble report.



> Instead of posting the original query, I post a simplified version which
> still experiences the problem with a factor of over 100x:
>
> SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id
> WHERE elements.id IN ();
>
> where  is large (e.g. 1,2,3,...,2000). In my application this is not
> a contiguous list, so I cannot use BETWEEN.
>
> To demonstrate the issue it suffices that both tables just contain a single
> column which is filled with e.g. the integers from 1 to 4000.
>
> CREATE TABLE elements (id INTEGER PRIMARY KEY);
> CREATE TABLE tags (element_id INTEGER);
>
> The running time in 3.7.16.2 increases heavily with the length of ,
> which is not the case in 3.7.11.
> As far as I know, indices do not improve the situation (my original
> database has indices).
> Removing the join solves the problem, but in the original query the join is
> necessary, because I do not only select COUNT(*).
>
> I tested this on several Linux machines.
>
> Thanks in advance,
> Martin
> ___
> 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] Performance issue with JOIN and large IN operator

[Martin Altmayer]

Hi,

I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11.
Instead of posting the original query, I post a simplified version which
still experiences the problem with a factor of over 100x:

SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id
WHERE elements.id IN ();

where  is large (e.g. 1,2,3,...,2000). In my application this is not
a contiguous list, so I cannot use BETWEEN.

To demonstrate the issue it suffices that both tables just contain a single
column which is filled with e.g. the integers from 1 to 4000.

CREATE TABLE elements (id INTEGER PRIMARY KEY);
CREATE TABLE tags (element_id INTEGER);

The running time in 3.7.16.2 increases heavily with the length of ,
which is not the case in 3.7.11.
As far as I know, indices do not improve the situation (my original
database has indices).
Removing the join solves the problem, but in the original query the join is
necessary, because I do not only select COUNT(*).

I tested this on several Linux machines.

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

Re: [sqlite] Segmentation fault in SQLite parser

[Richard Hipp]

On Wed, May 1, 2013 at 9:27 AM, Klaas V  wrote:

> Eelco wrote:
> Using version 3.7.16.2
> select * from ((select * from x)) y;
> Segmentation fault
>
>
> On OSX we get 'Bus error'.


Thanks for the report.  This bug has already been fixed.  See
http://www.sqlite.org/src/info/28c6e830f2 for the ticket and
http://www.sqlite.org/src/info/1c79569226 for the patch.



> Not a bug, because you were syntactical not exact enough.
> See http://www.sqlite.org/lang_select.html
>
> You're supposed to use only one pair () not more. Before 3.7.15 or 16 the
> extra pair were user friendly ignored.
>
> Following example from an ancient version where you see that existence of
> a table makes a difference:
>
> SQLite version 3.6.12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table dual (dummy);
> sqlite> insert into dual values (1);
> sqlite> .schema
> CREATE TABLE dual (dummy);
> sqlite> select * from dual;
> 1
> sqlite>  select * from ((select * from x)) y;
> SQL error: no such table: x
> sqlite> select * from ((select * from dual)) bar;
> 1
> sqlite> select * from (select * from dual) bar;
> 1
>
> An example where newer versions ask more attention to developers.
>
> Cordiali saluti/Vriendelijke groeten/Kind regards,
> Klaas "Z4us" V MetaDBA
> ___
> 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] Segmentation fault in SQLite parser

[Klaas V]

Eelco wrote: 
Using version 3.7.16.2
select * from ((select * from x)) y;
Segmentation fault


On OSX we get 'Bus error'. Not a bug, because you were syntactical not exact 
enough.
See http://www.sqlite.org/lang_select.html 

You're supposed to use only one pair () not more. Before 3.7.15 or 16 the extra 
pair were user friendly ignored.

Following example from an ancient version where you see that existence of a 
table makes a difference:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table dual (dummy);
sqlite> insert into dual values (1);
sqlite> .schema
CREATE TABLE dual (dummy);
sqlite> select * from dual;
1
sqlite>  select * from ((select * from x)) y;
SQL error: no such table: x
sqlite> select * from ((select * from dual)) bar;
1
sqlite> select * from (select * from dual) bar;
1

An example where newer versions ask more attention to developers. 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to link the packet in C file then store in sqlite database?

[fnoyanisi]

I cannot understand "So I need to create a table Packet right?", but you do 
need to create a table first.

You may need to bother yourself by reading the API reference for the functions 
I gave.


On 01/05/2013, at 9:09 PM, Newbie89  wrote:

> So I need to create a table Packet right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Convert mysql to sqllite with tooling

[John Ophof]

I am new in the field of sqllite and want to convert my MySQL db to mysql using 
tooling.
I try to use https://gist.github.com/esperlu/943776
When I place my dump file and the sh file to /Applications/mqsql2sqllite/ and 
try to run it with terminal.
I got command not found or permission denied. I gave 777 on the user. I am an 
admin user.
I tried it with sudo in front of it as well such as

sudo ./mysql2sqlite.sh altenappt30_4.sql | sqlite3 altena.db 

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

Re: [sqlite] How to link the packet in C file then store in sqlite database?

[Newbie89]

Fehmi Noyan ISI wrote
> This seems to be a C programming question rather than sqlite related
> issue. 
> 
> You even did not mention which information you would like to store, we
> only have a code snippet that processes a captured network packet.
> 
> something like this would work
> 
> sqlite3_prepare_v2() // use a template with ?
> while(read_packet) {
>     ProcessPack()
>     sqlite3_bind_text()
>     sqlite3_step()
>     sqlite3_reset()
> }
> sqlite3_finalize()

Hi,sorry for my  information not clear,
I want to store packet data into database...
So I need to create a table Packet right?
My table will consist of Src_MAC,Dest_MAC, Net_P,Trans_P, Src_IP,Dest_IP and
Cap_Bytes..




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-link-the-packet-in-C-file-then-store-in-sqlite-database-tp68593p68597.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Richard Hipp]

On Wed, May 1, 2013 at 12:42 AM, James K. Lowden
wrote:

> On Tue, 30 Apr 2013 12:59:17 -0400
> Richard Hipp  wrote:
>
> > http://www.sqlite.org/draft/queryplanner-ng.html
>
> Feel free to use this version of the diagram.
>
> http://www.sqlite.org/draft/queryplanner-ng.html
>

Did you intend the above to be a different hyperlink?


>
> The directory contains the source file and PDF, too.
>
> Commands are:
>
> $ groff -p -ms -mpdf -Tpdf tcp-h-q8.ms  > tcp-h-q8.pdf
> $ convert -trim -border 12x12 -bordercolor white  \
> tcp-h-q8.pdf  tcp-h-q8.png
>
> HTH.
>
> --jkl
> ___
> 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] Slowness with FTS Queries and non-FTS tables

[Dan Kennedy]

On Tue, 30 Apr 2013 23:56:35 -0700 (PDT)
Paul Vercellotti  wrote:

> 
> 
> 
> Hi All,
> 
> We've got some trouble with FTS4 queries taking too long in which
> we're looking for a subset of matching records in the FTS table, as
> narrowed by a non-FTS table.
> 
> CREATE TABLE metadata( key AS INTEGER PRIMARY KEY, sectionID AS
> INTEGER ); CREATE INDEX sectionIdx on metadata(sectionID);
> CREATE VIRTUAL TABLE ftstable USING fts4( content );
> 
> There is a pseudo foreign key relationship between the rowid's of
> ftstable and key in metadata.  There's one entry in metadata for each
> entry in ftstable.
> 
> Our query is of the general form:
> 
> SELECT key FROM metadata WHERE sectionID = 12345 AND
> key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' ),
> LIMIT 100;
> 
> And it's quite slow (like a minute or two for a 1GB to 3GB database).
>  Now you'll see the cause of the slowness - the subquery matching
> 'the' in the fts table returns millions of results in our case.
>  Presumably that gets pumped into a temporary place, then joined with
> the first part of the query, which selects only those records which
> match the sectionID that we're interested in.   In fact, of those,
> we're only interested in the first hundred.
> 
> If I move "LIMIT 100" to the subquery, like this
> 
> SELECT key FROM metadata WHERE sectionID = 12345 AND
> key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' LIMIT
> 100);
> 
> It's lightning fast again (6ms), but of course that doesn't work
> because we don't find the 100 records that match the sectionID in the
> outer query.
> 
> Is there any good way to tell the MATCH query to only consider those
> records selected by the first part of the query, or any other way to
> speed this up?   I feel like it's doing more work than is ideal to
> find the records that match the sectionID and contain the text.

Not really. All FTS has to work with is an index that maps from 'the'
to a sorted list of docids.

If you make it a join it might be a little better:

  SELECT key FROM ftstable, metadata 
  WHERE sectionID=12345 AND 
AND ftstable.docid=metadata.key 
AND content MATCH 'the'
  LIMIT 100;


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

Re: [sqlite] How to link the packet in C file then store in sqlite database?

[Fehmi Noyan ISI]

This seems to be a C programming question rather than sqlite related issue. 

You even did not mention which information you would like to store, we only 
have a code snippet that processes a captured network packet.


something like this would work

sqlite3_prepare_v2() // use a template with ?
while(read_packet) {
    ProcessPack()
    sqlite3_bind_text()
    sqlite3_step()
    sqlite3_reset()
}
sqlite3_finalize()




 From: Newbie89 
To: sqlite-users@sqlite.org 
Sent: Wednesday, May 1, 2013 4:57 PM
Subject: [sqlite] How to link the packet in C file then store in sqlite 
database?
 

Here is the  c file code I need to link and store in database:


struct Packet
{
char Src_MAC[18], Dest_MAC[18];
char Net_P[5],Trans_P[5];
char Src_IP[16], Dest_IP[16];
long int Src_Port,Dest_Port, Cap_Bytes; //[ long int Range: −2,147,483,648
to 2,147,483,647]
};

struct Packet Pack[6];

void ProcessPack(u_char *ul,const struct pcap_pkthdr* pkthdr,const
u_char*pckt)
{
// Declare pointers to packet headers
const struct sniff_ip *ip; // IP Header Pointer
const struct sniff_tcp *tcp; // TCP Header Pointer
const struct sniff_udp *udp; // TCP Header Pointer
struct sniff_ethernet *eptr; // Ethernet Header Pointer

int size_ip;
int size_tcp;
char buffer[65535];

// Capture packet length in Bytes
Pack[Cnt].Cap_Bytes = pkthdr->len;
TB += pkthdr->len;

// Ethernet Header Info ==

eptr = (struct sniff_ethernet*)(pckt);

ip = (struct sniff_ip*) (pckt + SIZE_ETHERNET);
size_ip = IP_HL(ip)*4;

tcp = (struct sniff_tcp*) (pckt + SIZE_ETHERNET + size_ip);
size_tcp = TH_OFF(tcp)*4;

if ( (size_ip < 20) || (size_tcp < 20))
{

if (size_ip < 20)
{ Wrg_IpH++; T_Wrg_IpH++; }
if (size_tcp < 20)
{ Wrg_TcpH++; T_Wrg_TcpH++; }

}

// Check to see if we have an ip packet
if (ntohs (eptr->ether_type) == ETHERTYPE_IP)
{
strcpy(Pack[Cnt].Net_P,"IP");
Ip++;
T_IP++;
}
else if (ntohs (eptr->ether_type) == ETHERTYPE_ARP)
{
strcpy(Pack[Cnt].Net_P,"ARP");
Arp++;
T_ARP++;
}
else
{
strcpy(Pack[Cnt].Net_P,"OTH");
OTH_Net1++;
T_OTH_Net++;
}

// Source and Destination MAC address.
strcpy(Pack[Cnt].Src_MAC,(char*)ether_ntoa(eptr->ether_shost));
strcpy(Pack[Cnt].Dest_MAC,(char*)ether_ntoa(eptr->ether_dhost));

// IP header Info =



strcpy(Pack[Cnt].Src_IP,"");
strcpy(Pack[Cnt].Dest_IP,"");

strcpy(Pack[Cnt].Src_IP,inet_ntoa(ip->ip_src)); //Source IP
strcpy(Pack[Cnt].Dest_IP,inet_ntoa(ip->ip_dst)); // Destination IP


if(strstr(Pack[Cnt].Src_IP,"255") || strstr(Pack[Cnt].Dest_IP,"255") )
{
BdCast++; T_BdCast++;
}

//= TCP & UDP header Info 

udp = (struct sniff_udp*) (pckt + SIZE_ETHERNET + size_ip);

// Determine protocol
switch(ip->ip_p)
{
// IPPROTO_TCP = 6, Transmission Control Protocol.
case IPPROTO_TCP:
strcpy(Pack[Cnt].Trans_P,"TCP");
Pack[Cnt].Src_Port = ntohs(tcp->th_sport); // Src Port
Pack[Cnt].Dest_Port = ntohs(tcp->th_dport); // Dest Port
Tcp++;
T_TCP++;
break;

// IPPROTO_UDP = 17, User Datagram Protocol.
case IPPROTO_UDP:
strcpy(Pack[Cnt].Trans_P,"UDP");
Pack[Cnt].Src_Port = ntohs(udp->source); // Src Port
Pack[Cnt].Dest_Port = ntohs(udp->dest); // Dest Port
Udp++;
T_UDP++;
break;

// IPPROTO_ICMP = 1, Internet Control Message Protocol.
case IPPROTO_ICMP:
strcpy(Pack[Cnt].Trans_P,"ICMP");
//strcpy(Pack[Cnt].Trans_P,"OTH");
Pack[Cnt].Src_Port = 0; // Source Port
Pack[Cnt].Dest_Port = 0; // Destination Port
Icmp++;
//OTH_Trans1++;
T_ICMP++;
//T_OTH_Trans++;

break;

default:
strcpy(Pack[Cnt].Trans_P,"OTH");
Pack[Cnt].Src_Port = 0; // Src Port
Pack[Cnt].Dest_Port = 0; // Dest Port
OTH_Trans1++; T_OTH_Trans++;
break;
}

Cnt++;

return;
}

how to do from c file to link with sqlite to store into database?thanks 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-link-the-packet-in-C-file-then-store-in-sqlite-database-tp68593.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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] How to link the packet in C file then store in sqlite database?

[Newbie89]

Here is the  c file code I need to link and store in database:


struct Packet
{
char Src_MAC[18], Dest_MAC[18];
char Net_P[5],Trans_P[5];
char Src_IP[16], Dest_IP[16];
long int Src_Port,Dest_Port, Cap_Bytes; //[ long int Range: −2,147,483,648
to 2,147,483,647]
};

struct Packet Pack[6];

void ProcessPack(u_char *ul,const struct pcap_pkthdr* pkthdr,const
u_char*pckt)
{
// Declare pointers to packet headers
const struct sniff_ip *ip; // IP Header Pointer
const struct sniff_tcp *tcp; // TCP Header Pointer
const struct sniff_udp *udp; // TCP Header Pointer
struct sniff_ethernet *eptr; // Ethernet Header Pointer

int size_ip;
int size_tcp;
char buffer[65535];

// Capture packet length in Bytes
Pack[Cnt].Cap_Bytes = pkthdr->len;
TB += pkthdr->len;

// Ethernet Header Info ==

eptr = (struct sniff_ethernet*)(pckt);

ip = (struct sniff_ip*) (pckt + SIZE_ETHERNET);
size_ip = IP_HL(ip)*4;

tcp = (struct sniff_tcp*) (pckt + SIZE_ETHERNET + size_ip);
size_tcp = TH_OFF(tcp)*4;

if ( (size_ip < 20) || (size_tcp < 20))
{

if (size_ip < 20)
{ Wrg_IpH++; T_Wrg_IpH++; }
if (size_tcp < 20)
{ Wrg_TcpH++; T_Wrg_TcpH++; }

}

// Check to see if we have an ip packet
if (ntohs (eptr->ether_type) == ETHERTYPE_IP)
{
strcpy(Pack[Cnt].Net_P,"IP");
Ip++;
T_IP++;
}
else if (ntohs (eptr->ether_type) == ETHERTYPE_ARP)
{
strcpy(Pack[Cnt].Net_P,"ARP");
Arp++;
T_ARP++;
}
else
{
strcpy(Pack[Cnt].Net_P,"OTH");
OTH_Net1++;
T_OTH_Net++;
}

// Source and Destination MAC address.
strcpy(Pack[Cnt].Src_MAC,(char*)ether_ntoa(eptr->ether_shost));
strcpy(Pack[Cnt].Dest_MAC,(char*)ether_ntoa(eptr->ether_dhost));

// IP header Info =



strcpy(Pack[Cnt].Src_IP,"");
strcpy(Pack[Cnt].Dest_IP,"");

strcpy(Pack[Cnt].Src_IP,inet_ntoa(ip->ip_src)); //Source IP
strcpy(Pack[Cnt].Dest_IP,inet_ntoa(ip->ip_dst)); // Destination IP


if(strstr(Pack[Cnt].Src_IP,"255") || strstr(Pack[Cnt].Dest_IP,"255") )
{
BdCast++; T_BdCast++;
}

//= TCP & UDP header Info 

udp = (struct sniff_udp*) (pckt + SIZE_ETHERNET + size_ip);

// Determine protocol
switch(ip->ip_p)
{
// IPPROTO_TCP = 6, Transmission Control Protocol.
case IPPROTO_TCP:
strcpy(Pack[Cnt].Trans_P,"TCP");
Pack[Cnt].Src_Port = ntohs(tcp->th_sport); // Src Port
Pack[Cnt].Dest_Port = ntohs(tcp->th_dport); // Dest Port
Tcp++;
T_TCP++;
break;

// IPPROTO_UDP = 17, User Datagram Protocol.
case IPPROTO_UDP:
strcpy(Pack[Cnt].Trans_P,"UDP");
Pack[Cnt].Src_Port = ntohs(udp->source); // Src Port
Pack[Cnt].Dest_Port = ntohs(udp->dest); // Dest Port
Udp++;
T_UDP++;
break;

// IPPROTO_ICMP = 1, Internet Control Message Protocol.
case IPPROTO_ICMP:
strcpy(Pack[Cnt].Trans_P,"ICMP");
//strcpy(Pack[Cnt].Trans_P,"OTH");
Pack[Cnt].Src_Port = 0; // Source Port
Pack[Cnt].Dest_Port = 0; // Destination Port
Icmp++;
//OTH_Trans1++;
T_ICMP++;
//T_OTH_Trans++;

break;

default:
strcpy(Pack[Cnt].Trans_P,"OTH");
Pack[Cnt].Src_Port = 0; // Src Port
Pack[Cnt].Dest_Port = 0; // Dest Port
OTH_Trans1++; T_OTH_Trans++;
break;
}

Cnt++;

return;
}

how to do from c file to link with sqlite to store into database?thanks 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-link-the-packet-in-C-file-then-store-in-sqlite-database-tp68593.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Timezone is supported by date/time functions but is not documented

[gwenn]

Hello,
SQLite datetime function correctly parses timestring with timezone:

sqlite> select datetime('2013-04-30T18:38:54Z');
2013-04-30 18:38:54
sqlite> select datetime('2013-04-30T20:38:54+02:00');
2013-04-30 18:38:54

But this is not documented:
http://sqlite.org/lang_datefunc.html
http://sqlite.org/datatype3.html#datetime

May I suggest updating these pages accordingly.

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

Re: [sqlite] Query optimizer bug?

[Kevin Benson]

>
> On Tue, 30 Apr 2013 12:59:17 -0400
> Richard Hipp  wrote:
>
> > http://www.sqlite.org/draft/queryplanner-ng.html
>
>
Just a "heads-up" that the  paragraph has a couple of
accidental transpositions:

*IS*

"TCP-H Q8" is a test query from the Transaction Processing Performance
Council . SQLite version 3.7.17 and earlier do
not choose a good query plan for TCP-H Q8.

*SHOULD BE*

"TPC-H Q8" is a test query from the Transaction Processing Performance
Council . SQLite version 3.7.17 and earlier do
not choose a good query plan for TPC-H Q8.

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

[sqlite] Slowness with FTS Queries and non-FTS tables

[Paul Vercellotti]

Hi All,

We've got some trouble with FTS4 queries taking too long in which we're looking 
for a subset of matching records in the FTS table, as narrowed by a non-FTS 
table.

CREATE TABLE metadata( key AS INTEGER PRIMARY KEY, sectionID AS INTEGER );
CREATE INDEX sectionIdx on metadata(sectionID);
CREATE VIRTUAL TABLE ftstable USING fts4( content );

There is a pseudo foreign key relationship between the rowid's of ftstable and 
key in metadata.  There's one entry in metadata for each entry in ftstable.

Our query is of the general form:

SELECT key FROM metadata WHERE sectionID = 12345 AND
key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' ), LIMIT 100;

And it's quite slow (like a minute or two for a 1GB to 3GB database).  Now 
you'll see the cause of the slowness - the subquery matching 'the' in the fts 
table returns millions of results in our case.  Presumably that gets pumped 
into a temporary place, then joined with the first part of the query, which 
selects only those records which match the sectionID that we're interested in.  
 In fact, of those, we're only interested in the first hundred.

If I move "LIMIT 100" to the subquery, like this

SELECT key FROM metadata WHERE sectionID = 12345 AND
key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' LIMIT 100);

It's lightning fast again (6ms), but of course that doesn't work because we 
don't find the 100 records that match the sectionID in the outer query.

Is there any good way to tell the MATCH query to only consider those records 
selected by the first part of the query, or any other way to speed this up?   I 
feel like it's doing more work than is ideal to find the records that match the 
sectionID and contain the text.

Thanks!

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

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Mario M. Westphal]

Thank you for providing this pre-release amalgamation ;-)
I downloaded it immediately and compiled it into my software.
 
The problem has been ++resolved++ and the performance is at least as good as
with previous versions of SQLite.
It even feels a bit faster, although I only could try it with a 20% sample
of the typical data volume.
 
Thank you for the excellent support. Much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users