Re: [sqlite] Virtual Table and the xBestIndex

[Dariusz Matkowski]

Hi all,
Where can I get an example of xFilter implementation? The issue I am having 
here is that when I am trying to execute a "select * from tablex where value=1" 
all I get is the last result from the file (336cc2282716::1).


int my_filter( sqlite3_vtab_cursor* pCursor, int idxNum, const char *idxStr,
int argc, sqlite3_value **argv )
{
csv_cursor * cur = (csv_cursor *) pCursor;
csv_vtab   * tab = (csv_vtab *) cur->cursor.pVtab;

csvfile_rewind( tab->csv );
csvfile_read( tab->csv );   /* skip first row with column names */
cur->csvpos = csvfile_tell( tab->csv );
csvfile_read( tab->csv );   /* skip first row with column names */

return SQLITE_OK;
}

***first row in the file not important
ed527dc53686::1
336cc2282716::1
41f955b0::0
00225828815a::1
00271368ace9::0
f0def10e9046::0
70b1e68e9e67::0
ed527dc53686::1
336cc2282716::1

When I do "select * from tablex" I get all the results fine. It is only when I 
use the where clause is that I get strange results.


Any hints will be greatly appreciated.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Friday, February 25, 2011 11:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table and the xBestIndex

On Fri, Feb 25, 2011 at 09:30:00PM -0500, Dariusz Matkowski scratched on the 
wall:

> I am trying to implement a simple virtual table that reads in a file
> with two columns and "::" separator. My question is: is it necessary
> to implement xBestIndex to run a simple where clause queries?

  You must implement a xBestIndex() function, but it does not need to
  do anything other than return SQLITE_OK.  This will result in a full
  table scan for each query, but for something like a flat file (where
  you cannot easily jump to specific records) that's usually not a
  significant limitation.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Table and the xBestIndex

[Dariusz Matkowski]

Thank you very much. Now that you cleared that for me I need to look in my code 
why simple query like "select * from table x" works fine but as soon as I call 
"select * from table x where value=1" is only returning the last line from the 
file.

Thank you
Darek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Friday, February 25, 2011 11:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table and the xBestIndex

On Fri, Feb 25, 2011 at 09:30:00PM -0500, Dariusz Matkowski scratched on the 
wall:

> I am trying to implement a simple virtual table that reads in a file
> with two columns and "::" separator. My question is: is it necessary
> to implement xBestIndex to run a simple where clause queries?

  You must implement a xBestIndex() function, but it does not need to
  do anything other than return SQLITE_OK.  This will result in a full
  table scan for each query, but for something like a flat file (where
  you cannot easily jump to specific records) that's usually not a
  significant limitation.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table and the xBestIndex

[Dariusz Matkowski]

Hi all,
I am trying to implement a simple virtual table that reads in a file with two 
columns and "::" separator. My question is: is it necessary to implement 
xBestIndex to run a simple where clause queries?

Thank you.

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Degrees of separation

[Dariusz Matkowski]

Phobic

- Original Message -
From: Simon Slavin [mailto:slav...@bigfraud.org]
Sent: Friday, December 03, 2010 06:36 PM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Degrees of separation


On 3 Dec 2010, at 11:14pm, Drake Wilson wrote:

> Another would be to
> rewrite the query to union the two directions together

You could do this with a VIEW, of course.  Have a VIEW that reflects the UNION 
of the 'know' table with its elements in the opposite order.  Then query this 
VIEW instead of the original table.

An alternative which would lead to faster code would be a TRIGGER which every 
time something was put in the 'know' table inserted another record with the 
elements reversed.  It should, of course, be an INSERT OR IGNORE, and you'd 
need a UNIQUE key on the table to prevent duplication.

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

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

[Dariusz Matkowski]

On boot up the device will discover the servers and enumerate what they have, 
then when it is done it will just wait for updates from the servers. I do not 
know how big the servers are, each server/media library may be different.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 10:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Queries will be done across the servers to aggregate the content. I am
> concern about the locking mechanism, if I write to the single database and
> I represent the servers as tables I will have no access to read the other
> servers/tables, but if I distribute the servers across different DBs I can
> write into one and the other ones are open to read.

   How frequently are data written to the tables? How much data per write?

   It appears that you want to use separate databases for each server so you
might try that and see how well it works for you.

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

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

[Dariusz Matkowski]

Queries will be done across the servers to aggregate the content. I am concern 
about the locking mechanism, if I write to the single database and I represent 
the servers as tables I will have no access to read the other servers/tables, 
but if I distribute the servers across different DBs I can write into one and 
the other ones are open to read.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Only one user (the browser). The DB is on a device the same place where
> the user (Browser) is. A process will collect the information about the
> servers and their contents and story it to the DB at the same time the
> user may ask for the contents to display on the screen.

   Then the next question is wether queries are restricted to each server. If
so, you could have a separate database for each. Alternatively, you can
define a server table and associate each one with the other data you collect
and retrieve. The latter approach is more flexible and allows easier changes
if/when your information needs change.

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

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

[Dariusz Matkowski]

Only one user (the browser). The DB is on a device the same place where the 
user (Browser) is. A process will collect the information about the servers and 
their contents and story it to the DB at the same time the user may ask for the 
contents to display on the screen.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 9:24 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> in  your opinion is it better (performance, maintainability etc...) to
> have multiple databases or multiple tables. The problem I am facing is as
> follows. I have many media servers containing a large amount of images
> music and videos, let's assume 5. I would like to gather the information
> (metadata, thumbnails, location etc) and story it in the database. I will
> also have a GUI/Browser that will display that information. Now, the
> question is it better to use multiple databases, each server = one
> database or each server = one table.

Daiuusz,

   I suggest you're asking the wrong questions. How many simultaneous users
will access data in the database? Will the database be stored on one server
and accessed across the network?

   It might be that for your application SQLite is not the appropriate tool.

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

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple databases vs. Multiple tables.

[Dariusz Matkowski]

Question,
in  your opinion is it better (performance, maintainability etc...) to have 
multiple databases or multiple tables. The problem I am facing is as follows. I 
have many media servers containing a large amount of images music and videos, 
let's assume 5. I would like to gather the information (metadata, thumbnails, 
location etc) and story it in the database. I will also have a GUI/Browser that 
will display that information. Now, the question is it better to use multiple 
databases, each server = one database or each server = one table.

Regards,

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users