> I've banged on about this problem and thanks to your assistance it
> seems
> to be resolved now. Perhaps you could update the documentation at
> http://www.sqlite.org/autoinc.html and
> http://www.sqlite.org/lang_createindex.html - I had certainly read
> these many times so a note about
> > > Any comments on this - Richard or Dan?
> >
> > The INTEGER PRIMARY KEY is always included in every index as an
> > implied extra column on the end. If you explicitly add the
> > INTEGER PRIMARY KEY as a column in the index, then you have it
> > in the index twice, which serves no
> > Any comments on this - Richard or Dan?
>
> The INTEGER PRIMARY KEY is always included in every index as an
> implied extra column on the end. If you explicitly add the INTEGER
> PRIMARY KEY as a column in the index, then you have it in the
> index twice, which serves no purpose
I
timescale for fixing the bug?
>
> I will have to bypass the AUTOINCREMENT functionality and create my
> own IDs. That creates more complications.
>
> Hugh
>
> > *Subject:* Problems using AUTOINCREMENT row IDs in indexes
> > *From:* "Hugh Gibson" <[EMAI
> > According to Igor all that happens when you create a primary
> > key is that an index with the unique constraint is created. And
> > the only good reason for not being able to drop it, as far as I
> > can tell, is so that the SQL statement stored for the table is not
> > made invalid.
>
>
> > SQLite will complain because of the duplicate index names, but in
> > other database packages it will be accepted. You then have to
> > specify the table name when deleting indexes.
>
> The only database I'm aware of that does this is SQL Server.
> Don't overgeneralize. :)
No worries - I
> An index is actually indeed associated with a table, and within that
> table with one or more columns.
>
> Hence, dropping an index doesn't require a table name.
I can easily write SQL like this:
CREATE TABLE First (nID, nValue)
CREATE INDEX idxID ON First (nID)
CREATE TABLE Second (nID,
> Clients can be referred to by one or more names and so there's
> another table:
>
> CREATE TABLE client_names (
> id integer,
> name text
> );
>
> Names aren't unique. Two clients can have the same name.
But the combination of id and name are unique: hence try this:
CREATE TABLE
> > I note that SQLite prevents creation of indexes with the same
> > name, regardless of table.
>
> Quite. So it's unclear why you would want to be able to mention
> table name in the DROP INDEX statement.
Fair enough! It does seem strange when an index is associated with a
single table to
We implement automatic instructions to upgrade a database schema. These
include modifying field values and adding/dropping fields, tables,
indexes etc.
I see from the syntax of DROP INDEX that a table name is not specified.
Are there any plans for adding a way of specifying the table?
There are
> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id
> fields in my SQLite projects, yet I would like to try some triggers
> as well. Of course, every time I add a trigger that accesses a
> table with these types of id fields, all sorts of odd things
> happen.
Not to
and create my own
IDs. That creates more complications.
Hugh
> *Subject:* Problems using AUTOINCREMENT row IDs in indexes
> *From:* "Hugh Gibson" <[EMAIL PROTECTED]>
> *To:* sqlite-users@sqlite.org
> *CC:* [EMAIL PROTECTED]
> *Date:* Thu, 13 Nov 2008 14:56 +00
I'm having problems getting good index choice in SQLite 3.6.4 for a field
which is INTEGER PRIMARY KEY AUTOINCREMENT.
I've got the following table:
CREATE TABLE Signals (sSignalID Text DEFAULT '',sText Text DEFAULT
'',sTime Text DEFAULT '',sUserID Text DEFAULT '',nRowID INTEGER PRIMARY
KEY
> You could do this:
>
> SELECT COUNT(*) from X where memberid < 4567373
That assumes that you are sorting by memberid, of course...
Hugh
-
To unsubscribe, send email to [EMAIL PROTECTED]
> That is what I want to do. I want to know where the memberid is in
> the list (imagine the list was a waiting list or something). Is there
> not a way to just get the row number back? Is seems inefficient to
> have to allocate all of the memory to hold all of the results and
> then iterate
> SELECT * FROM tetragrams
> WHERE word1 = 'x' AND word2||'' = 'y'
> ORDER BY occs;
Better as
SELECT * FROM tetragrams
WHERE word1 = 'x' AND +word2 = 'y'
ORDER BY occs;
See http://www.sqlite.org/optoverview.html section 6.
Hugh
> but of course there is an EXACT answer for my question - there are
> selected only the rows when Text2 changes... How to get also always
> the first row from original data set?
Just UNION the result with a query that gets the first row from the table.
Hugh
You could try http://web.utk.edu/~jplyon/sqlite/code/sqaux-userfns.c but it's a
little old now.
Hugh
> I have had a look at the sqlite3_progress_handler() API function but
> it appears that the problem is knowing how many opcodes are required
> to complete the transaction before it is run.
So do this: set up sqlite3_progress_handler() with N = 1. Create a query to
delete one record and count
> I'm intrigued. How do you get SQLite to use a multi-column index as it's
> primary key (i.e. B-tree hash)? Please elaborate.
Simply
CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT
'',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY
(sCommunityID,
> What happens if you create the index on sCommunityID only? Does
> it still do the full table scan?
A bit difficult to drop the (sCommunityID, sTransactionID) index, as it's
the primary key.
> Also, don't overlook using UNION or UNION ALL, ugly as they
> can be. Maybe something like this
The following works for me. The Community table has only one entry per
community ID so it's fast to look up.
SELECT sCommunityID, (SELECT sTransactionID
FROM TransactionList
WHERE sCommunityID = Community.sCommunityID
I have this query:
SELECT sCommunityID, max(sTransactionID)
FROM TransactionList
WHERE sCommunityID in ('a03061bFi','a03064KDy', 'a03068QhK')
GROUP BY sCommunityID
There is an index on (sCommunityID, sTransactionID)
This forces a table scan (perhaps improved
t; *To:* [EMAIL PROTECTED]
> *Date:* Tue, 27 Sep 2005 06:37:44 -0400
>
> On Mon, 2005-09-26 at 12:03 +0100, Hugh Gibson wrote:
> > Hi,
> >
> > Using PySQLite 2.0.4 I get a crash with the following sequence,
> > starting from no database. If the page_size pragma
Assertion failed: n==4-4*pPage->leaf, file btree.c, line 601
Assertion failed: pPage->leaf==0 || pPage->leaf==1, file btree.c, line 599
Hugh Gibson
> The error returned is OperationalError: near ".", syntax error.
Probably because you need to enclose the version strings in single quotes.
> OperationalError: no such column: finally
Use single quotes around values in the SQL. Contents of double quotes are
interpreted as field names.
Hugh
riginal Message-
> > From: Hugh Gibson [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, June 22, 2005 4:22 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] ANN: Sqlite3Explorer.exe version 1.6
> >
> > Just got a crash when viewing table data using SQLite
Just got a crash when viewing table data using SQLite3Explorer version
1.6:
---
sqlite3explorer
---
Access violation at address 00402E29 in module 'sqlite3Explorer.exe'.
Write of address 01357AAE.
---
OK
> I have uploaded v 1.6 of sqlite3Explorer (www.singular.gr/sqlite).
Thanks!
The download page has 16/03/2005 against version 1.6 - should be
16/6/2005.
Hugh
> A few problems with SQLite3Explorer 1.5:
Further to these, I've found that viewing of text data in the grid is
limited to 255 characters. Is it possible to make the default a lot bigger
or have a way of "zooming" a field to get the final value.
Hugh
> > Myself, I'm not a big fan of high-level abstraction from the sql
> > being performed, so I wouldn't use the originally posted idea of
> > editing a recordset.
>
> I don't tend to use it either. But some people like to do things that
> way and I'd like to make it as easy as possible for
I would be interested to know the results for very large data sets.
Indications on the list have been that performance suffers when the number
of records gets very big (> 1 million), possibly due to using an internal
sort.
Hugh
> You might search the list archive and also try a
> Google search on 'celko nested set' and 'adjacency
> list' -- or even just 'sql tree'.
Try also http://www.dbazine.com/tropashko4.shtml
Hugh
> SQLite only uses a single index per table on any give query.
> This is unlikely to change.
Would it be able to use a multi-column query on ipnode + author?
Hugh
> Shi Elektronische Medien GmbH, Peter Spiske wrote:
> >
> > the following simple query is very slow:
> > SELECT title FROM t1
34 matches
Mail list logo
