Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Where did you get your pre-occupation with BIGINT? Sqlite handles INTEGERS and makes them up to 64 bits as necessary. An INTEGER primary key will autoincrement,. Sql;ite lets you introduce a type BIGINT as a declared type, but makes its own decision as to underlying type. Nathan Catlow

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

I'm back, and it's 5am, back from the dark shift :) I chose BIGINT because it matches exactly the internal represention of ROWID (from a mysql perspective, granted). but it wouldn't matter if it didn't anyway. The only adverse reaction I can see from my patch is it doesn't allow you to

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Eurika! I get it! # sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> CREATE TABLE t(i INTEGER PRIMARY KEY, t TEXT); sqlite> INSERT INTO t(i,t) VALUES ("a", "b"); SQL error: datatype mismatch sqlite> CREATE TABLE t2(i INTEGER, t TEXT); sqlite> INSERT INTO t2(i,t) VALUES ("a",

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

>> I think Nathan's point is that the integer you get when declaring a >> column INTEGER PRIMARY KEY can hold a 64-bit value anyway, so why >> couldn't it simply be declared differently and behave the same? > > INTEGER PRIMARY KEY is the exception to the rules for SQLite > datatypes. Any other

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Mon, Dec 15, 2008 at 04:34:29PM +, Nathan Catlow scratched on the wall: > Quoting "D. Richard Hipp" : > > Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY > > KEY, not on any other kind of primary key or on any non-primary-key > > field. BIGINT PRIMARY

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Mon, Dec 15, 2008 at 04:34:29PM +, Nathan Catlow wrote: > > Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY > > KEY, not on any other kind of primary key or on any non-primary-key > > field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so > > AUTOINCREMENT won't

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Markus Hoenicka wrote: > Quoting Igor Tandetnik : > >> >> Well, you get that 1 in the last column, indicating the column is in >> fact part of a primary key. So, if it's INTEGER, and it's the only >> column marked PRIMARY KEY, then it's the

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Dec 15, 2008, at 11:23 AM, Thomas Briggs wrote: > I think Nathan's point is that the integer you get when declaring a > column INTEGER PRIMARY KEY can hold a 64-bit value anyway, so why > couldn't it simply be declared differently and behave the same? INTEGER PRIMARY KEY is the exception

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Quoting Igor Tandetnik : > > Well, you get that 1 in the last column, indicating the column is in > fact part of a primary key. So, if it's INTEGER, and it's the only > column marked PRIMARY KEY, then it's the special one. > Your reply seems to imply that I might end up

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Markus Hoenicka wrote: > I doubt that allowing BIGINT to auto-increment is the proper solution > of the underlying problem. I'd like to focus your attention again on > the example of the OP: > > sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, int INTEGER, bigint >

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Markus Hoenicka wrote: >If there's a way to find out at runtime that a column has been defined as >INTEGER PRIMARY KEY instead of as INTEGER, all is well and I'll be able to fix >the sqlite driver accordingly. pragma table_info(table); In SQLite, this SQL returns all columns defined for

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Quoting "D. Richard Hipp" : > > On Dec 15, 2008, at 10:27 AM, Nathan Catlow wrote: > >> Please understand that the problem boils down to this: >> >> Why can't I CREATE TABLE t(i BIGINT PRIMARY KEY) and have it >> autoincrement? >> >> What internal mechanisms am I breaking by

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

I think Nathan's point is that the integer you get when declaring a column INTEGER PRIMARY KEY can hold a 64-bit value anyway, so why couldn't it simply be declared differently and behave the same? Personally I think this is an application problem, but I thought his point was valid and I'm

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Quoting "D. Richard Hipp" : > It might be possible to get BIGINT PRIMARY KEY AUTOINCREMENT to work > like INTEGER PRIMARY KEY AUTOINCREMENT. Or perhaps it is not. That > is unclear. Certainly it would be a rather substantial change - much, > much larger than the little patch

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Mon, Dec 15, 2008 at 11:24:30AM +, Nathan Catlow scratched on the wall: > Can you tell me why you can even specify BIGINT to sqlite then? The world of SQL standard types is poor enough that SQLite makes best guesses for any reasonable type. In this case, anything with the string

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Dec 15, 2008, at 10:52 AM, Thomas Briggs wrote: >> Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY >> KEY, not on any other kind of primary key or on any non-primary-key >> field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so >> AUTOINCREMENT won't work on it. >

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

> That, unfortunately, leads directly to the follow-up question of > "can BIGINT PRIMARY KEY AUTOINCREMENT" be made to work the same as > INTEGER PRIMARY KEY AUTOINCREMENT". I believe the answer is yes, but > I wouldn't bet my life on it. If I knew anything at all about SQLite, I'd probably say

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

> Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY > KEY, not on any other kind of primary key or on any non-primary-key > field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so > AUTOINCREMENT won't work on it. I think he understands that. :) His question is why.

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Dec 15, 2008, at 10:27 AM, Nathan Catlow wrote: > Please understand that the problem boils down to this: > > Why can't I CREATE TABLE t(i BIGINT PRIMARY KEY) and have it > autoincrement? > > What internal mechanisms am I breaking by defining it like this? First off, AUTOINCREMENT means

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Please understand that the problem boils down to this: Why can't I CREATE TABLE t(i BIGINT PRIMARY KEY) and have it autoincrement? What internal mechanisms am I breaking by defining it like this? This would make the "reverse trip" easier surely? Well it would for me anyway (It allows me to

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

You are still missing something. Apply some deeper thought to the concepts behind Sqlite and the elegance will become clear. At run time the Sqlite programmer has access to the declared type and the actual storage type of the data. An API layer between the Sqlite API and the application can

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Quoting "John Stanton" : > You have not grasped the fundamental concept of typing used by Sqlite. > It implements manifest typeing in the manner of scripting systems like > Javascript etc. It has a clever feature which permits you to declare a > type as anything you like

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Mon, 15 Dec 2008 11:24:30 +, "Nathan Catlow" wrote in General Discussion of SQLite Database : >PRAGMA table_info(t); >PRAGMA index_list(t); > >Both those give me no love. Try: sqlite> select * from sqlite_sequence; name seq

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

You have not grasped the fundamental concept of typing used by Sqlite. It implements manifest typeing in the manner of scripting systems like Javascript etc. It has a clever feature which permits you to declare a type as anything you like and parses that name to decide on the underlying

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

Quoting "Jay A. Kreibich" : > On Sun, Dec 14, 2008 at 08:25:02PM +, Nathan Catlow scratched on > the wall: > >> I am trying to use libdbi + sqlite for my project. It is impossible >> for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA >> table_info(). > >

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

On Sun, Dec 14, 2008 at 08:25:02PM +, Nathan Catlow scratched on the wall: > I am trying to use libdbi + sqlite for my project. It is impossible > for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA > table_info(). Why impossible? The type in INTEGER, just as returned.

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nathan Catlow wrote: > ... INTEGER (32 bit in at least mysql) ... > ... (id BIGINT ... You seem to be under the impression that SQLite has multiple variable sized integers. It doesn't. The SQLite integer type is 64 bit signed. (It will use less

[sqlite] Impossible to declare field type BIGINT PRIMARY KEY

I am trying to use libdbi + sqlite for my project. It is impossible for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA table_info(). This has completely crippled my project that relies on autoincrement. As the PRIMARY KEY is an alias to ROWID (64bit), the data should be