Re: [sqlite] What's best table schema?

Moreover, I can't use rowid to communicate between playersinleague and playersposition tables Thank you. On Thu, Jul 4, 2013 at 7:27 PM, Igor Korot wrote: > Hi, Simon, > > On Thu, Jul 4, 2013 at 6:50 PM, Simon Slavin wrote: > >> >> On 5 Jul 2013,

Re: [sqlite] What's best table schema?

Hi, Simon, On Thu, Jul 4, 2013 at 6:50 PM, Simon Slavin wrote: > > On 5 Jul 2013, at 2:10am, Igor Korot wrote: > > > CREATE TABLE players(playerid integer primary key, name char(50), age > > integer, value integer, currvalue double...); > > CREATE

Re: [sqlite] What's best table schema?

On 5 Jul 2013, at 2:10am, Igor Korot wrote: > CREATE TABLE players(playerid integer primary key, name char(50), age > integer, value integer, currvalue double...); > CREATE TABLE playersinleague(id integer, playerid integer, value integer, > currvalue double, draft boolean,

[sqlite] What's best table schema?

Hi, ALL, Consider following task/schema: CREATE TABLE leagues(id integer primary key, name char(50),); CREATE TABLE position(positionid integer foreign key, positionname char(10)); CREATE TABLE players(playerid integer primary key, name char(50), age integer, value integer, currvalue

Re: [sqlite] table format for most efficient query

On 7/4/2013 5:29 PM, Paul Sanderson wrote: select * from master as m, lookup as l where x >= start and and x < end and m.index = l.index You might want to look at the RTree module: http://www.sqlite.org/rtree.html It's specifically designed to implement such range queries efficiently. --

Re: [sqlite] table format for most efficient query

On Thu, 4 Jul 2013 22:52:26 +0100, Simon Slavin wrote: > > I assume you missed a comma: > > create table lookup (index int, start int, end int) indeed > But actually it’s a bad idea to use the words > 'index' and 'end’ for columns because they're > used as reserved words

Re: [sqlite] table format for most efficient query

Thanks Simon - i'll have a play. tomorrow On 4 July 2013 22:52, Simon Slavin wrote: > > On 4 Jul 2013, at 10:29pm, Paul Sanderson > wrote: > > > create table lookup (index int, start int end int) > > I assume you missed a comma: > > create

Re: [sqlite] table format for most efficient query

On 4 Jul 2013, at 10:29pm, Paul Sanderson wrote: > create table lookup (index int, start int end int) I assume you missed a comma: create table lookup (index int, start int, end int) But actually it’s a bad idea to use the words 'index' and 'end’ for columns

[sqlite] table format for most efficient query

I need to craete a lookup table which has the form create table lookup (index int, start int end int) The takle will be joined on a second table via the index column the table is likely to have a few million rows and I will be doing many thousands of lookups consequtively. My current lookups

Re: [sqlite] Another 2 questions about SQLite

On Thu, 4 Jul 2013 15:15:14 -0400, "James K. Lowden" wrote: > This weird case is one of (I would say) misusing the connection. IMO > SQLite should return an error if prepare is issued on a connection for > which a previous prepare was not finalized or reset. That

Re: [sqlite] Another 2 questions about SQLite

On 7/4/2013 3:15 PM, James K. Lowden wrote: This weird case is one of (I would say) misusing the connection. IMO SQLite should return an error if prepare is issued on a connection for which a previous prepare was not finalized or reset. That would forestall discussions like, this and prevent

Re: [sqlite] Another 2 questions about SQLite

On 7/4/2013 3:15 PM, James K. Lowden wrote: If two processes sharing a connection... This is a physical impossibility. There ain't no such thing as two processes sharing a connection. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] Another 2 questions about SQLite

On 4 Jul 2013, at 8:15pm, James K. Lowden wrote: > It doesn't usually matter, right? The fact that the atomic SELECT is > spread out across N function calls is irrelevant if they are executed > in uninterrupted sequence, because other connections are blocked from >

Re: [sqlite] DLL Size differences and other info requested

On Thu, 4 Jul 2013 17:36:37 +0200 Philip Bennefall wrote: > Do you have any views on compiling SqLite optimized for speed rather > than size? These days, size is speed. The smaller the code, the better it fits in cache, the faster it runs. The days of unrolling loops to

Re: [sqlite] Another 2 questions about SQLite

On Thu, Jul 4, 2013 at 9:15 PM, James K. Lowden wrote: > On Mon, 01 Jul 2013 23:59:15 -0400 > Igor Tandetnik wrote: > > > > 2. Trying to re-use a single connection to issue a second query > > > before finalizing the first one should return an error

Re: [sqlite] Another 2 questions about SQLite

On Tue, 2 Jul 2013 11:57:43 +0100 Simon Slavin wrote: > The SELECT statement is fine and consistent. But the SELECT > statement is all of _prepare(), _step(), and _finalize(). Igor is > pointing out that that if you stop before _step() has returned > SQLITE_DONE then you

Re: [sqlite] Another 2 questions about SQLite

On Mon, 01 Jul 2013 23:59:15 -0400 Igor Tandetnik wrote: > > 2. Trying to re-use a single connection to issue a second query > > before finalizing the first one should return an error > > No it should not, and does not. Try it. > > > because the library is being improperly

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Wed, 3 Jul 2013 22:49:51 -0500 "Jay A. Kreibich" wrote: > So anyways, I don't actually care about the actual number of orders, > which is mostly likely what my SQL query returns, I just want the > ranking-- who is first, second, and third. I can get that from an >

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Wed, 03 Jul 2013 11:11:29 +0200 Gabriel Corneanu wrote: > I reply from the web and I can't easily quote. Acknowledged, but it does make the thread more difficult to read. :-/ > I don't really want to argue whether it's a workaround or not. I > understand

Re: [sqlite] DLL Size differences and other info requested

On 4 Jul 2013, at 4:36pm, Philip Bennefall wrote: > Thanks for that info. Do you have any views on compiling SqLite optimized for > speed rather than size? Is the difference in performance generally small > enough to be ignored? I am using Vc++ 2010 express and have been

Re: [sqlite] DLL Size differences and other info requested

Hi Dan, Thanks for that info. Do you have any views on compiling SqLite optimized for speed rather than size? Is the difference in performance generally small enough to be ignored? I am using Vc++ 2010 express and have been optimizing for speed up until now. Kind regards, Philip Bennefall

Re: [sqlite] DLL Size differences and other info requested

On 07/04/2013 05:49 AM, Philip Bennefall wrote: Hi Stephen, I don't know what compiler is used to build the official SqLite dll, but provided it is some version of Vc++ my experience is that MinGw often produces larger and sometimes significantly slower binaries on Windows than VC++ does. In