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 rowid's role
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.
Yes, however,
?
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 [EMAIL PROTECTED]
*To:* sqlite-users@sqlite.org
*CC:* [EMAIL PROTECTED]
*Date:* Thu
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 tried
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
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 was just
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 delete it
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
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, nValue,
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 mention
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
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 + (GMT Standard Time)
I'm having problems
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
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
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]
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 how
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, sTransactionID))
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
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
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 could
:* 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 is removed it
works OK. Reproducible under Windows 2K and XP. Running Python
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
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
: 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 SQLite3Explorer version
1.6:
snip
The only thing that could affect
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 them.
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 WHERE
34 matches
Mail list logo
