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

[Nathan Catlow]

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 then put any other datatype other that INTEGER in the BIGINT  
PRIMARY KEY (which I was pleased with as it proves the 2 line patch is  
working exactly as intended and a quick win for me). This is what, I,  
as a sensible human being would expect because in my head BIGINT is an  
INTEGER. I suppose that's why I was shocked sqlite didn't *seem* to  
treat it as such.

I suppose though it would break compatibility for people already  
defining BIGINT PRIMARY KEY and sticking BLOBS and whatever into the  
field and finding themselves with a datatype error post patch :(  
Though I think these people don't deserve to design database systems.  
(thanks drh for the missing jigsaw piece there)

My thinking was:

Is BIGINT an INTEGER, yes. Do INTEGERS autoincrement in sqlite, YES!.  
Can a BIGINT autoincrement in sqlite, err, no. :(

Found this by drh in the archive...

"There is a #define that may interest the original poster.
If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are
limited to 32 bits.  This feature is not particularly well
tested (actually, I'm not sure it is tested at all) but it
might work.  It is worth a try, I suppose."

Interesting This could also help me in my situation. Perhaps a  
-DBIGINT_PRIMARY_KEY_AUTOINC patch could also work for people wanting  
compatibility with other db engines that need BIGINT for 64bit and  
autoincrementing, just me by the looks of things (might as well call  
it -DNATHAN)

This in no way affects the libdbi issue about easily and efficiently  
discovering a field that is under the control of sqlite's 64bit  
autoincrement. I think that got slightly lost in the BIGINT issue.  
Though looping round and seeing if pk is set more than once is better  
than nowt i suppose.

regards and thanks, I learned things.

Nathan


Quoting "D. Richard Hipp" :

>
> 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 to the rules for SQLite
> datatypes.  Any other column can hold any datatype, regardless of the
> declared datatype.  But an INTEGER PRIMARY KEY is different.  INTEGER
> PRIMARY KEY is an alias for the underlying rowid.  It can only hold an
> integer.
>
> If we allowed any integer-typed column that was a PRIMARY KEY to
> behave this way, then you would suddenly have many expections to the
> rule, rather than just one.  And there would be no way to declare a
> column that had integer affinity that was the primary key.
>
> The INTEGER PRIMARY KEY exception was first introduced in SQLite
> version 2.2.0, about 7 years ago (2001-12-20).
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>




This message was sent using IMP, the Internet Messaging Program.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding data with periods

[Eric Bohlman]

Mohd Radzi Ibrahim wrote:
> It seems to works either way.
> 
> I'm just wondering is there any hidden reason that single quote is 
> preferred? Portability?
> Or is double-qoute has some kind of special meaning that we should use it 
> for that special purpose?

If what's enclosed in the double quotes winds up being a column name or 
other identifier, SQLite will treat it as an identifier rather than a 
literal. This can cause all sorts of unpleasant surprises, particularly 
of the "this was working fine until I made this cosmetic change..." sort.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IN expression performance

[Igor Tandetnik]

Sander Jansen  wrote:
> Suppose I have query that does:
>
> SELECT  FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15
>  );
>
> The list of numbers comes from a user selection, so it doesn't come
> from some other table.  Would it make a big difference in performance
> if I pre-sort this list of numbers or does SQLITE automatically sort
> this (and perhaps maintains an index as well)?

Looking at EXPLAIN output for such a query, SQLite creates ephemeral 
(in-memory) B-tree for the list of integers (essentially, just the index 
without the underlying table). So, the numbers will be automatically 
arranged into B-tree for fast lookup: their order in the query is 
irrelevant (except perhaps for the marginal speed-up in adding them to 
the B-tree in the first place, which is probably slightly faster if the 
numbers are pre-sorted - but if you don't happen to have them sorted 
already, you'll probably spend as much time doing so as SQLite would).

Igor Tandetnik 



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

Re: [sqlite] Temporary table performance

[Petite Abeille]

On Dec 15, 2008, at 8:31 PM, Ofir Neuman wrote:

> What can I do in order to improve performance?

take a look at pragma temp_store = memory

http://www.sqlite.org/pragma.html#pragma_temp_store

> Will it be better to delete the content of the table instead of  
> delete the table and recreate it?

Arguable, yes... let the db do it for you... as 'delete table foo' is  
equivalent to drop + create table...

Cheers,

--
PA.
http://alt.textdrive.com/nanoki/

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

[sqlite] Temporary table performance

[Ofir Neuman]

Hi All, 
 
I have a some performance problem on INSERT to temporary table. 
 
My temporary table is very simple: 
 
TABLE 
{ 
ID TEXT (Primary key) // The format is GUID 
} 
 
On user request, I receive list of ids from outside component and would like
to store them in a temporary table for later use. 
 
Every time that I receive new ids I delete the old temporary table, create a
new one and store the data again. 
 
Sometimes insert of 7000 ids takes more than 8 sec. 
 
If I receive the same ids again and do the whole thing again (delete the
temp table, create new one and insert) it takes only few milliseconds. 
 
Note: All of the above operations is done within a transaction. 
 
What can I do in order to improve performance? Will it be better to delete
the content of the table instead of delete the table and recreate it? 
 
Thanks, 
Ofir.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking documentation enhancement.

[John Karp]

Speaking of documentation of locks... my one beef with the sqlite
documentation is that its not very explicit about implicit locking. To
find out about implicit locks, you have to read
http://www.sqlite.org/lang_transaction.html, even though implicit
locks aren't necessarily associated with transactions. The C API
functions that actually manage these locks just mention them in
passing.

To me, it makes sense to have a blurb like:
"For 'step' to succeed, the database must either be unlocked, or have
no more than a SHARED lock upon it. Upon successful completion of this
function, the database lock state will be SHARED. (For this lock to be
undone, all statements in the executed state must be either reset or
finalized.)"
at http://www.sqlite.org/c3ref/step.html, since its an important
side-effect of the function.
And perhaps have similar text in all other functions that manipulate
the implicit lock.

-John

On Mon, Dec 15, 2008 at 12:15 PM, Mike Mestnik  wrote:
> On Mon, Dec 15, 2008 at 11:40:45AM -0600, Jay A. Kreibich wrote:
>> On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
>> >   The locking is well documented, but it's not vary clear that the
>> > BEGIN command can be used to acquire locks.  The relation between the
>> > different locking stats and parameters to the BEGIN command could be
>> > spelled out.
>>
>>   You might have missed this.  Assuming you understand the locking
>>   system, it does a pretty good job at filling in the details on BEGIN.
>>
>>   http://www.sqlite.org/lang_transaction.html
>>
>> Transactions can be deferred, immediate, or exclusive. The default
>> transaction behavior is deferred. Deferred means that no locks are
>> acquired on the database until the database is first accessed. Thus
>> with a deferred transaction, the BEGIN statement itself does nothing.
>> Locks are not acquired until the first read or write operation. The
>> first read operation against a database creates a SHARED lock and the
>> first write operation creates a RESERVED lock. Because the acquisition
>> of locks is deferred until they are needed, it is possible that another
>> thread or process could create a separate transaction and write to the
>> database after the BEGIN on the current thread has executed. If the
>
> At this time I have more then enough information to complete my
> project, thank you so much for all the help.
>
>> transaction is immediate, then RESERVED locks are acquired on all
>> databases as soon as the BEGIN command is executed, without waiting for
>
> I think text like the above is missing for each of the BEGIN
> arguments.  To make things easy to find it might also be good to put
> some BEGIN examples into the page that explains how locking works.
>
> DEFERRED/default, then initially ??no?? locks.
> IMMEDIATE, then initially RESERVED lock.
> EXCLUSIVE, then initially ? lock.
>
> There are more locks then there are BEGIN arguments, correct?  Perhaps
> an explanation on why BEGIN has no support for creating this type of
> lock.
>
> I also wonder about nested begins?  For example when you want to
> explicitly change an EXCLUSIVE lock into a IMMEDIATE one, or the
> other way around.
>
>
> Perhaps my confusion simply comes from a lack of understanding basic
> SQL concepts.
>
>> the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
>> that no other thread or process will be able to write to the database
>> or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
>> continue to read from the database, however. An exclusive transaction
>> causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
>> EXCLUSIVE, you are guaranteed that no other thread or process will be
>> able to read or write the database until the transaction is complete.
>>
>>
>>
>> --
>> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>>
>> "Our opponent is an alien starship packed with atomic bombs.  We have
>>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Open the database - Creating the empty database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joanne Pham wrote:
> The problem here is the database is not created but if the second process has 
>tried to access the database then the empty database is created which
has the size of 0.

If you use sqlite3_open_v2 then you can specify flags for creation.
http://sqlite.org/c3ref/open.html

However you still have a race condition as the first process may not
have got around to creating a schema/content etc before the second tries
to read the database.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAklGqcYACgkQmOOfHg372QRwtgCgjlkWDneHVbNPfD96S/oXwrRi
dBMAoLL1u5GTo8vGjY/GkD+hrb7HkSxC
=cKYG
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Open the database - Creating the empty database

[Derrell Lipman]

On Mon, Dec 15, 2008 at 1:43 PM, P Kishor  wrote:

> On 12/15/08, Joanne Pham  wrote:
> > Hi All,
> >  I have this problem about open the database. Here is the detail about
> the problem.
> >
> >  Our application have one process to create the database and another
> process to open the database and creating the report.
> >  The problem here is the database is not created but if the second
> process has tried to access the database then the empty database is created
> which has the size of 0. So the question is there any way the open database
> API should return an error message instead of creating the empty database
> when the second process opens the database.
> >
> >  Thanks,
>
>
> A SQLite database is just a file on the hard disk. Test for the
> existence of the file before trying to open it.
>

That is, unfortunately, a recipe for race conditions.  Application A tests
for whether the file exists and finds it doesn't.  The operating system task
switches to Application B which had previously determined that the file
doesn't exist, and now creates it and adds some data.  Task switch back to
Application A which assumes that the file doesn't exist (since it had
previously determined that) and scribbles over the data just written by
Application B.

An alternative is to open the database normally (sqlite3_open) and issue the
queries

  BEGIN EXCLUSIVE;
  SELECT 1 FROM sqlite_master LIMIT 1;

If you get back a value (1), then the database had already has tables (or
triggers or ...) in it.  If you get back NULL then you can go ahead and
create the database tables et al.  When done creating tables and doing
whatever else you need to do before allowing other applications access,
issue a COMMIT.  Since you had an exclusive transaction, any other
application trying to determine if the database is available or not will
block until your transaction completes.

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

Re: [sqlite] Open the database - Creating the empty database

[Joanne Pham]

Thanks for the respond.
I will test for the existence of the file before trying to open it.
Once again thanks,
JP






From: P Kishor 
To: General Discussion of SQLite Database 
Sent: Monday, December 15, 2008 10:43:57 AM
Subject: Re: [sqlite] Open the database - Creating the empty database

On 12/15/08, Joanne Pham  wrote:
> Hi All,
>  I have this problem about open the database. Here is the detail about the 
>problem.
>
>  Our application have one process to create the database and another process 
>to open the database and creating the report.
>  The problem here is the database is not created but if the second process 
>has tried to access the database then the empty database is created which has 
>the size of 0. So the question is there any way the open database API should 
>return an error message instead of creating the empty database when the second 
>process opens the database.
>
>  Thanks,


A SQLite database is just a file on the hard disk. Test for the
existence of the file before trying to open it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Open the database - Creating the empty database

[P Kishor]

On 12/15/08, Joanne Pham  wrote:
> Hi All,
>  I have this problem about open the database. Here is the detail about the 
> problem.
>
>  Our application have one process to create the database and another process 
> to open the database and creating the report.
>  The problem here is the database is not created but if the second process 
> has tried to access the database then the empty database is created which has 
> the size of 0. So the question is there any way the open database API should 
> return an error message instead of creating the empty database when the 
> second process opens the database.
>
>  Thanks,


A SQLite database is just a file on the hard disk. Test for the
existence of the file before trying to open it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Open the database - Creating the empty database

[Joanne Pham]

Hi All,
I have this problem about open the database. Here is the detail about the 
problem.
 
Our application have one process to create the database and another process to 
open the database and creating the report.
The problem here is the database is not created but if the second process has 
tried to access the database then the empty database is created which has the 
size of 0. So the question is there any way the open database API should return 
an error message instead of creating the empty database when the second process 
opens the database.
 
Thanks,
JP


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

Re: [sqlite] Wildcard searches, partial matches

[aditya siram]

Cool!

-deech

On Mon, Dec 15, 2008 at 12:12 PM, P Kishor  wrote:

> On 12/15/08, aditya siram  wrote:
> > Hi all,
> >  I am trying to implement an autocompleter with a sqlite backend. Is
> there a
> >  way to search the database using wildcards/regexps's without reading the
> >  entire search space into memory?
> >
> >  For example given  the rows:
> >  'hello  world'
> >  'goodbye world'
> >   a query approximately like "* world" should return both.
> >
>
>
> and so it does...
>
> sqlite> CREATE TABLE foo (a);
> sqlite> INSERT INTO foo VALUES ('hello world');
> sqlite> INSERT INTO foo VALUES ('goodbye world');
> sqlite> SELECT * FROM foo WHERE a LIKE '%world';
> hello world
> goodbye world
> sqlite> SELECT * FROM foo WHERE a GLOB '*world';
> hello world
> goodbye world
> sqlite>
>
> Read the docs on the difference between LIKE and GLOB
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking documentation enhancement.

[Mike Mestnik]

On Mon, Dec 15, 2008 at 11:40:45AM -0600, Jay A. Kreibich wrote:
> On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
> >   The locking is well documented, but it's not vary clear that the
> > BEGIN command can be used to acquire locks.  The relation between the
> > different locking stats and parameters to the BEGIN command could be
> > spelled out.
> 
>   You might have missed this.  Assuming you understand the locking
>   system, it does a pretty good job at filling in the details on BEGIN.
> 
>   http://www.sqlite.org/lang_transaction.html
> 
> Transactions can be deferred, immediate, or exclusive. The default
> transaction behavior is deferred. Deferred means that no locks are
> acquired on the database until the database is first accessed. Thus
> with a deferred transaction, the BEGIN statement itself does nothing.
> Locks are not acquired until the first read or write operation. The
> first read operation against a database creates a SHARED lock and the
> first write operation creates a RESERVED lock. Because the acquisition
> of locks is deferred until they are needed, it is possible that another
> thread or process could create a separate transaction and write to the
> database after the BEGIN on the current thread has executed. If the

At this time I have more then enough information to complete my
project, thank you so much for all the help.

> transaction is immediate, then RESERVED locks are acquired on all
> databases as soon as the BEGIN command is executed, without waiting for

I think text like the above is missing for each of the BEGIN
arguments.  To make things easy to find it might also be good to put
some BEGIN examples into the page that explains how locking works.

DEFERRED/default, then initially ??no?? locks.
IMMEDIATE, then initially RESERVED lock.
EXCLUSIVE, then initially ? lock.

There are more locks then there are BEGIN arguments, correct?  Perhaps
an explanation on why BEGIN has no support for creating this type of
lock.

I also wonder about nested begins?  For example when you want to
explicitly change an EXCLUSIVE lock into a IMMEDIATE one, or the
other way around.


Perhaps my confusion simply comes from a lack of understanding basic
SQL concepts.

> the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
> that no other thread or process will be able to write to the database
> or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
> continue to read from the database, however. An exclusive transaction
> causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
> EXCLUSIVE, you are guaranteed that no other thread or process will be
> able to read or write the database until the transaction is complete. 
> 
> 
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Wildcard searches, partial matches

[P Kishor]

On 12/15/08, aditya siram  wrote:
> Hi all,
>  I am trying to implement an autocompleter with a sqlite backend. Is there a
>  way to search the database using wildcards/regexps's without reading the
>  entire search space into memory?
>
>  For example given  the rows:
>  'hello  world'
>  'goodbye world'
>   a query approximately like "* world" should return both.
>


and so it does...

sqlite> CREATE TABLE foo (a);
sqlite> INSERT INTO foo VALUES ('hello world');
sqlite> INSERT INTO foo VALUES ('goodbye world');
sqlite> SELECT * FROM foo WHERE a LIKE '%world';
hello world
goodbye world
sqlite> SELECT * FROM foo WHERE a GLOB '*world';
hello world
goodbye world
sqlite>

Read the docs on the difference between LIKE and GLOB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wildcard searches, partial matches

[aditya siram]

Hi all,
I am trying to implement an autocompleter with a sqlite backend. Is there a
way to search the database using wildcards/regexps's without reading the
entire search space into memory?

For example given  the rows:
'hello  world'
'goodbye world'
 a query approximately like "* world" should return both.

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

Re: [sqlite] Locking documentation enhancement.

[Jay A. Kreibich]

On Mon, Dec 15, 2008 at 09:08:34AM -0600, Mike Mestnik scratched on the wall:
>   The locking is well documented, but it's not vary clear that the
> BEGIN command can be used to acquire locks.  The relation between the
> different locking stats and parameters to the BEGIN command could be
> spelled out.

  You might have missed this.  Assuming you understand the locking
  system, it does a pretty good job at filling in the details on BEGIN.

  http://www.sqlite.org/lang_transaction.html

Transactions can be deferred, immediate, or exclusive. The default
transaction behavior is deferred. Deferred means that no locks are
acquired on the database until the database is first accessed. Thus
with a deferred transaction, the BEGIN statement itself does nothing.
Locks are not acquired until the first read or write operation. The
first read operation against a database creates a SHARED lock and the
first write operation creates a RESERVED lock. Because the acquisition
of locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to the
database after the BEGIN on the current thread has executed. If the
transaction is immediate, then RESERVED locks are acquired on all
databases as soon as the BEGIN command is executed, without waiting for
the database to be used. After a BEGIN IMMEDIATE, you are guaranteed
that no other thread or process will be able to write to the database
or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can
continue to read from the database, however. An exclusive transaction
causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will be
able to read or write the database until the transaction is complete. 



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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Nathan Catlow]

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", "b");

INTEGER PRIMARY KEY is the only type which is checked as a datatype!

Thankyou, thankyou, thankyou

I'll rethink and come back tomorrow. I'm still sure it's possible to  
very simple make BIGINT work as ROWID is 64bit anyways.

In regards to libdbi, i think we are saying if there is one field with  
the pk flag set and its INTEGER then that is PRIMARY KEY (ROWID) and  
autoincrement.

sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||0
2|bigint|BIGINT|0||0

Yes, id is PRIMARY KEY (ROWID)

sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||1
2|bigint|BIGINT|0||0

No, id is not PRIMARY KEY (ROWID)

This does mean an inefficient loop though, would be nice if it could  
provide something like "is_rowid" column. Although being able to  
declare BIGINT PRIMARY KEY would fix all issues.

regards,

Nathan

Quoting "D. Richard Hipp" :

>
> 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 to the rules for SQLite
> datatypes.  Any other column can hold any datatype, regardless of the
> declared datatype.  But an INTEGER PRIMARY KEY is different.  INTEGER
> PRIMARY KEY is an alias for the underlying rowid.  It can only hold an
> integer.
>
> If we allowed any integer-typed column that was a PRIMARY KEY to
> behave this way, then you would suddenly have many expections to the
> rule, rather than just one.  And there would be no way to declare a
> column that had integer affinity that was the primary key.
>
> The INTEGER PRIMARY KEY exception was first introduced in SQLite
> version 2.2.0, about 7 years ago (2001-12-20).
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>




This message was sent using IMP, the Internet Messaging Program.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Thomas Briggs]

>>   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 column can hold any datatype, regardless of the
> declared datatype.  But an INTEGER PRIMARY KEY is different.  INTEGER
> PRIMARY KEY is an alias for the underlying rowid.  It can only hold an
> integer.

   I don't think the question here is data types - I think it's data type sizes.

   If the answer to "why are types allowed in the DDL if SQLite
ignores them" is "for the sake of application developers", then I
would argue that in the situation where a particular type is required
that that type be as unambiguous as possible.  Just what defines
"integer" varies by platform, compiler, language, database... etc.
etc.  Sometimes it's 32 bits, sometimes if 64, I even once worked on a
platform where it meant unsigned 32 bit integer and signed ints had to
be declared explicitly.  If the docs said "PKs have to be INTEGERs,
and INTEGERs are signed 64 bit whole-number values" then there could
be no confusion. :)

   All that said I don't think this is worth changing... I was just curious.

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

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

[Jay A. Kreibich]

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 KEY is not an INTEGER PRIMARY KEY and so
> > AUTOINCREMENT won't work on it.
> 
> So you are saying there is an internal difference between INTEGER and  
> BIGINT in sqlite? I thought they were just synonyms for INTEGER.

  I think this is the key point to this whole discussion.

  There is no difference between the types INTEGER and BIGINT in the
  SQLite world, at least in terms of size.

  There *ARE*, however, some very meaningful and fundamental
  differences between columns defined as INTEGER PRIMARY KEY and BIGINT
  PRIMARY KEY.

  INTEGER PRIMARY KEY columns get turned into ROWID columns.  That
  transformation alters the way the table is structured and stored.
  It is a meaningful and significant change to the behavior of the column
  and table.

  For a truly cross-platform design, I think it is best to ignore the
  whole AUTOINCREMENT feature and just define a generic PRIMARY
  KEY NOT NULL** column.  If the application demands a sequence feature,
  the author/library/user needs to roll their own.  AUTOINCREMENT is
  not really a sequencing system, it is a specialized behavior for a
  highly specialized column type.

  (Surely someone somewhere has a sequence plugin for SQLite?)

  ** Yes, SQLite needs the NOT NULL on PK columns.  See docs.

> I see no evidence to support the assertion that BIGINT PRIMARY KEY !=  
> INTEGER PRIMARY KEY.

  Then you're not reading the docs:

 http://sqlite.org/lang_createtable.html

 ...if primary key is on a single column that has datatype INTEGER,
 then that column is used internally as the actual key of the B-Tree
 for the table. This means that the column may only hold unique
 integer values.  (Except for this one case, SQLite ignores the
 datatype specification of columns and allows any kind of data to
 be put in a column regardless of its declared datatype.) If a table
 does not have an INTEGER PRIMARY KEY column, then the B-Tree key
 will be a automatically generated integer.  The B-Tree key for a
 row can always be accessed using one of the special names "ROWID",
 "OID", or "_ROWID_". This is true regardless of whether or not
 there is an INTEGER PRIMARY KEY.

  While these differences are much more significant to the SQLite world
  that other databases, they are still significant differences that have
  deep and far-reaching meaning in the database engine.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Nicolas Williams]

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 work on it.
> 
> So you are saying there is an internal difference between INTEGER and  
> BIGINT in sqlite? I thought they were just synonyms for INTEGER. It  
> seems from my patch it is very simple to make autoincrement work for  
> BIGINT as to sqlite it's just another way of saying INTEGER. I could  
> be wrong here, I haven't examined the code close enough.

There is no difference between INTEGER and INT and BIGINT and SMALLINT.
There is a difference between "INTEGER PRIMARY KEY" and all of the
above.  The reason is that there are two kinds of columns in SQLite3:

 - ROWID

   ROWID is always be a unique 64-bit integer.

 - all other columns

   These are always ducktyped with a preference derived from the
   declared type.

and the thing is that "INTEGER PRIMARY KEY" is an alias for "ROWID,"
which means "INTEGER PRIMARY KEY" is different from "all other columns."

This alias is baked into the parser.  You could certainly bake in other
aliases.

You've been told is that that's not likely to happen.  But I think you
could make a stronger case: it's not that you are surprised here, but
that other SQL implementations have BIGINT PRIMARY KEY with semantics
that are compatible with INTEGER PRIMARY KEY (do they?) and for
portability's sake it'd be nice for SQLite3 to provide BIGINT PRIMARY
KEY as well.

> I see no evidence to support the assertion that BIGINT PRIMARY KEY !=  
> INTEGER PRIMARY KEY. I can JOIN tables on BIGINT and INTEGER because  
> sqlite regards them as the same INTEGER type, sqlite just doesn't  
> care. This is great, until it comes to autoincrementing then it's  
> unusually fussy.

You've seen evidence: the maintainers have to you so, the documentation
has told you so, and experience has told you so.

Perhaps some SQL standard says that you can have BIGINT PRIMARY KEY, but
unless it says implementations of SQL must provide BIGINT PRIMARY KEY
then SQLite3 needn't actually provide it.

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

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

[Igor Tandetnik]

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 special one.
>>
>
> Your reply seems to imply that I might end up having several INTEGER
> columns with the pk column set to 1.

Of course:

create table t(x integer, y integer, primary key(x, y));

> Does that mean that I cannot
> unambiguously identify that one autoincrementing column?

You can: none of them.

Igor Tandetnik



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

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

[D. Richard Hipp]

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 to the rules for SQLite  
datatypes.  Any other column can hold any datatype, regardless of the  
declared datatype.  But an INTEGER PRIMARY KEY is different.  INTEGER  
PRIMARY KEY is an alias for the underlying rowid.  It can only hold an  
integer.

If we allowed any integer-typed column that was a PRIMARY KEY to  
behave this way, then you would suddenly have many expections to the  
rule, rather than just one.  And there would be no way to declare a  
column that had integer affinity that was the primary key.

The INTEGER PRIMARY KEY exception was first introduced in SQLite  
version 2.2.0, about 7 years ago (2001-12-20).


D. Richard Hipp
d...@hwaci.com



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

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

[Markus Hoenicka]

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 having several INTEGER  
columns with the pk column set to 1. Does that mean that I cannot  
unambiguously identify that one autoincrementing column?

regards,
Markus


-- 
Markus Hoenicka
markus.hoeni...@cats.de
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

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

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

[Igor Tandetnik]

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
> BIGINT);
> sqlite> PRAGMA table_info(test);
> 0|id|INTEGER|0||1
> 1|int|INTEGER|0||0
> 2|bigint|BIGINT|0||0
>
> We've heard in this discussion repeatedly that all integers are
> created equal (as far as SQLite is concerned), and that applications
> using SQLite should keep track of the data types themselves if size
> matters. However, as Richard points out, INTEGER PRIMARY KEY is
> different from INTEGER and from anything else. All I need as an
> application (or abstraction layer FWIW) author therefore is that
> SQLite tells me that that particular column is different.

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.

Igor Tandetnik 



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

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

[Ralf Junker]

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 table along with their 
types. Check the "pk" result column to see if a named column is a PRIMARY KEY 
column.

Official documentation is here

  http://www.sqlite.org/pragma.html#pragma_table_info

Ralf 

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

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

[Nathan Catlow]

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 defining it like this?
>
> First off, AUTOINCREMENT means something different to SQLite than it
> does to MySQL.  Do not be confused by the similarity in names.

I don't require the use of the AUTOINCREMENT keyword and it doesn't  
appear in my CREATE TABLE statement.

When I transfer the table to mysql I appreciate the table will not  
increment there, what I do want is mysql to reserve the correct size  
and accept sqlite's autoincrement values for a primary key. Which  
would be greatly simplified if i can just read in the dump from sqlite.

>
> 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.

So you are saying there is an internal difference between INTEGER and  
BIGINT in sqlite? I thought they were just synonyms for INTEGER. It  
seems from my patch it is very simple to make autoincrement work for  
BIGINT as to sqlite it's just another way of saying INTEGER. I could  
be wrong here, I haven't examined the code close enough.

I see no evidence to support the assertion that BIGINT PRIMARY KEY !=  
INTEGER PRIMARY KEY. I can JOIN tables on BIGINT and INTEGER because  
sqlite regards them as the same INTEGER type, sqlite just doesn't  
care. This is great, until it comes to autoincrementing then it's  
unusually fussy.

It's like you can use BIGINT and INTEGER interchangeably everywhere  
except when it comes to incrementing. Nobody seems to have a valid  
reasoning behind this except "It's always been that way". Which is  
cool if that's the only reason, if there is a technical reason why my  
patch won't work please tell me. I want to know if it will 8 byte me  
in the ass later :)

My path of least resistance is:
Go with the patch (easy)
Do my own autoincrement with re-usable ids (more coding)

regards,

Nathan

>>
>>
>> This would make the "reverse trip" easier surely? Well it would for me
>> anyway (It allows me to easily import into mysql without writing an
>> "edit program", mysql understands TEXT too).
>>
>> My patch works for me, so i can go with that, I'm just unsure about
>> the reasonings behind the autoincrement restriction. Which is just a
>> syntax parsing issue in the end.
>>
>> Or am I demanding too much?
>>
>> regards,
>>
>> nat
>>
>> Quoting "John Stanton" :
>>
>>> 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 resolve any data format issues.
>>>
>>> Note that Sqlite maps to commonly used scripting systems seamlessly.
>>> Transferring data from somethiong like Mysql/DB2/Oracle/PostreSQL
>>> etc is
>>> very simple.  Making the reverse trip may not be so simple and in
>>> general would require some form of edit program to transform TEXT
>>> columns into the typed columns of say Mysql.  If such a transfer is
>>> important to you an application software layer can enforce the
>>> decclared
>>> types of Sqlite.  Otherwise use Mysql or preferably PostreSQL.
>>>
>>> Finally, Sqlite is simple and easy to use, hence the "lite".  Let it
>>> make life easy for you and don't fight it.  You will be rewarded
>>> handsomely.
>>>
>>> Nathan Catlow wrote:
 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 and parses that name to decide on the
> underlying type as basically either text or numeric.  It  decides
> at run
> time how to store the  data.
>

 Yes, I understand this, sqlite's lovelyness.


> The declared type in Sqlite is  in essence an assist for the
> programmer
> and is useful at application level to determine how to handle a
> column.
> For example a floating point number declared as DATE would be
> processed
> differently from one declared as ISOTOPE_COUNT.
>

 You've hit the nail on the head, I am trying to do exactly that!
 Providing an assist for an application level by explicitly declaring
 PRIMARY KEY BIGINT. This can then be transferred safely to another
 database (mysql in this example) even by doing the following;

 $ sqlite3 sqlitedb .d > 

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

[Thomas Briggs]

   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 thus curious myself.

   -T


On Mon, Dec 15, 2008 at 10:58 AM, D. Richard Hipp  wrote:
>
> 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.
>>
>>   I think he understands that. :)  His question is why.  I think that
>> the answer is simply "convention", FWIW.
>>
>>   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.
>
> 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 supplied on the ticket.
>
> My doubts about whether or not it will work derive from the fact that
> a BIGINT column can store strings and blobs in addition to integers.
> How do you AUTOINCREMENT a blob?
>
>>
>>
>>   -T
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Markus Hoenicka]

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 supplied on the ticket.
>
> My doubts about whether or not it will work derive from the fact that
> a BIGINT column can store strings and blobs in addition to integers.
> How do you AUTOINCREMENT a blob?
>

I'd like to jump in here as one of the authors of libdbi and its  
sqlite driver. I've followed this discussion with some amusement as it  
perfectly reflects the pain when attempting to fit SQLite into a  
database abstraction layer :-)

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  
BIGINT);
sqlite> PRAGMA table_info(test);
0|id|INTEGER|0||1
1|int|INTEGER|0||0
2|bigint|BIGINT|0||0

We've heard in this discussion repeatedly that all integers are  
created equal (as far as SQLite is concerned), and that applications  
using SQLite should keep track of the data types themselves if size  
matters. However, as Richard points out, INTEGER PRIMARY KEY is  
different from INTEGER and from anything else. All I need as an  
application (or abstraction layer FWIW) author therefore is that  
SQLite tells me that that particular column is different. However, the  
example above shows that SQLite hides the fact that the INTEGER  
PRIMARY KEY column is internally handled differently as it disguises  
it as an INTEGER. 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.

regards,
Markus

-- 
Markus Hoenicka
markus.hoeni...@cats.de
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

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

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

[Jay A. Kreibich]

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 "int" is going to be integer.  You can declare the column
  "MyVerySpecialInt" and it will be an INTEGER.

  By recognizing a wider array of types, SQLite is better able to digest
  SQL written for other systems.  While this might be unsafe in other
  database systems, it isn't that big of a deal with SQLite, since
  SQLite's manifest typing means that if the SQL parser guesses wrong,
  it doesn't really matter.

> But there is a difference, autoincrement ONLY works with "INTEGER", why?

  You're skipping over some important points there.  AUTOINCREMENT does
  not work with INTEGER...  AUTOINCREMENT only works with "INTEGER
  PRIMARY KEY" columns, and those columns have some very specific and
  very special meaning in SQLite-- mainly that they become a substitute
  ROWID column.

  You seem to be caught up on making a column, with a specific type of
  your choice, into a ROWID column just so you can utilize the
  AUTOINCREMENT feature.  That strikes me as slightly backwards motivation.
  Merging a column into the ROWID column makes a deep and fundamental
  change to the table definition, changing the way the table is
  structured and stored.  If you use it, you bring on number of
  fundamental changes in the behavior of the column.  ROWID columns have
  a larger number of constraints and restrictions on them and don't act
  like "normal" SQLite columns.

  That's why the definition "INTEGER PRIMARY KEY" is *SO* specific...
  it triggers a rather meaningful change in the way the table and
  database operates, so the specific type declaration tries to protect
  against doing it on accident.  "INTEGER" is a pretty good choice for
  this, since in traditional SQL it is a rather oddball type.



  If all you want is the ability to sequence a column, you can setup a
  trigger to do that for you on any column you want.  For example, just
  copy the native ROWID back into your ID column, or whatever you're
  trying to make a BIGINT, or setup a sequence table or any number of
  other techniques.

  Yes, most database systems have some kind of support to do sequences
  and automatic assignments on arbitrary columns.  For good or for bad,
  SQLite does not.  (Mostly, I assume, because it is fairly straight
  forward to roll your own.)  But most database systems don't let you
  alter the meaning of the ROWID column.

  If you have compatibility concerns, I'd be significantly more
  concerned about the ROWID aliasing than I would be with types.
  It is a much larger departure from other database systems.


> I suppose if i wrote my own autoincrement all this would go away as I  
> could ensure its length.

  Yes, it seems like all of your problems could be solved by just defining
  your main column as BIGINT PRIMARY KEY (or whatever type will make all
  your support libs happy) and setting up your own sequence mechanism.
  This also has the advantage of keeping the ROWID column its own
  unique column, which is most likely what any kind of cross-platform
  SQL system is going to expect.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[D. Richard Hipp]

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.
>
>   I think he understands that. :)  His question is why.  I think that
> the answer is simply "convention", FWIW.
>
>   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.

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 supplied on the ticket.

My doubts about whether or not it will work derive from the fact that  
a BIGINT column can store strings and blobs in addition to integers.   
How do you AUTOINCREMENT a blob?

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

D. Richard Hipp
d...@hwaci.com



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

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

[Brad Stiles]

> 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 the answer to
that is something on the order of "How many other variations of 'INT'
or 'INTEGER' would have to be implemented to be compatible with the
plethora of other database systems out there?  And why should SQLite
be responsible for making sure it's interoperable with every single
one of them?  SQLite is SQLite, and no other."

But then, I'm a jerk, so don't listen to closely to what I say.

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

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

[Thomas Briggs]

> 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.  I think that
the answer is simply "convention", FWIW.

   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.

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

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

[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 defining it like this?

First off, AUTOINCREMENT means something different to SQLite than it  
does to MySQL.  Do not be confused by the similarity in names.

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.

>
>
> This would make the "reverse trip" easier surely? Well it would for me
> anyway (It allows me to easily import into mysql without writing an
> "edit program", mysql understands TEXT too).
>
> My patch works for me, so i can go with that, I'm just unsure about
> the reasonings behind the autoincrement restriction. Which is just a
> syntax parsing issue in the end.
>
> Or am I demanding too much?
>
> regards,
>
> nat
>
> Quoting "John Stanton" :
>
>> 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 resolve any data format issues.
>>
>> Note that Sqlite maps to commonly used scripting systems seamlessly.
>> Transferring data from somethiong like Mysql/DB2/Oracle/PostreSQL  
>> etc is
>> very simple.  Making the reverse trip may not be so simple and in
>> general would require some form of edit program to transform TEXT
>> columns into the typed columns of say Mysql.  If such a transfer is
>> important to you an application software layer can enforce the  
>> decclared
>> types of Sqlite.  Otherwise use Mysql or preferably PostreSQL.
>>
>> Finally, Sqlite is simple and easy to use, hence the "lite".  Let it
>> make life easy for you and don't fight it.  You will be rewarded  
>> handsomely.
>>
>> Nathan Catlow wrote:
>>> 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 and parses that name to decide on the
 underlying type as basically either text or numeric.  It  decides  
 at run
 time how to store the  data.

>>>
>>> Yes, I understand this, sqlite's lovelyness.
>>>
>>>
 The declared type in Sqlite is  in essence an assist for the  
 programmer
 and is useful at application level to determine how to handle a  
 column.
 For example a floating point number declared as DATE would be  
 processed
 differently from one declared as ISOTOPE_COUNT.

>>>
>>> You've hit the nail on the head, I am trying to do exactly that!
>>> Providing an assist for an application level by explicitly declaring
>>> PRIMARY KEY BIGINT. This can then be transferred safely to another
>>> database (mysql in this example) even by doing the following;
>>>
>>> $ sqlite3 sqlitedb .d > out.sql
>>> shell> mysql mysqldb < out.sql
>>>
>>> All the autoincrement values can now be safely carried across  
>>> because
>>> I could provide the assistance with an explicit BIGINT (This is
>>> already possible but *without* the autoincrement feature)
>>>
>>> I fail to understand the limiting of autoincrement to just INTEGER
>>> where there is no difference internally to sqlite between INTEGER  
>>> and
>>> BIGINT etc. It is just an unnecessary restriction.
>>>
>>> Consider this 2 line patch which works against sqlite-3.6.2, it  
>>> could
>>> be extended to all the other (external) integer types with no  
>>> adverse
>>> effect. Am I missing something here?
>>>
>>>   --- build.c.old 2008-12-14 20:53:19.0 +
>>>   +++ build.c 2008-12-14 16:29:03.0 +
>>>   @@ -1165,7 +1165,7 @@
>>>  if( iCol>=0 && iColnCol ){
>>>zType = pTab->aCol[iCol].zType;
>>>  }
>>>   -  if( zType && sqlite3StrICmp(zType, "INTEGER")==0
>>>   +  if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||
>>> sqlite3StrICmp(zType, "BIGINT")==0)
>>>&& sortOrder==SQLITE_SO_ASC ){
>>>pTab->iPKey = iCol;
>>>pTab->keyConf = onError;
>>>   @@ -1174,7 +1174,7 @@
>>>  }else if( autoInc ){
>>>#ifndef SQLITE_OMIT_AUTOINCREMENT
>>>sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on  
>>> an "
>>>   -   "INTEGER PRIMARY KEY");
>>>   +   "INTEGER or BIGINT PRIMARY KEY");
>>>#endif
>>>  }else{
>>>sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,
>>> sortOrder, 0);
>>>
>>> Thank you for all your patience.
>>>
>>> regards,
>>>
>>> Nathan
>>>

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

[Nathan Catlow]

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 easily import into mysql without writing an  
"edit program", mysql understands TEXT too).

My patch works for me, so i can go with that, I'm just unsure about  
the reasonings behind the autoincrement restriction. Which is just a  
syntax parsing issue in the end.

Or am I demanding too much?

regards,

nat

Quoting "John Stanton" :

> 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 resolve any data format issues.
>
> Note that Sqlite maps to commonly used scripting systems seamlessly.
> Transferring data from somethiong like Mysql/DB2/Oracle/PostreSQL etc is
> very simple.  Making the reverse trip may not be so simple and in
> general would require some form of edit program to transform TEXT
> columns into the typed columns of say Mysql.  If such a transfer is
> important to you an application software layer can enforce the decclared
> types of Sqlite.  Otherwise use Mysql or preferably PostreSQL.
>
> Finally, Sqlite is simple and easy to use, hence the "lite".  Let it
> make life easy for you and don't fight it.  You will be rewarded handsomely.
>
> Nathan Catlow wrote:
>> 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 and parses that name to decide on the
>>> underlying type as basically either text or numeric.  It  decides at run
>>> time how to store the  data.
>>>
>>
>> Yes, I understand this, sqlite's lovelyness.
>>
>>
>>> The declared type in Sqlite is  in essence an assist for the programmer
>>> and is useful at application level to determine how to handle a column.
>>> For example a floating point number declared as DATE would be processed
>>> differently from one declared as ISOTOPE_COUNT.
>>>
>>
>> You've hit the nail on the head, I am trying to do exactly that!
>> Providing an assist for an application level by explicitly declaring
>> PRIMARY KEY BIGINT. This can then be transferred safely to another
>> database (mysql in this example) even by doing the following;
>>
>> $ sqlite3 sqlitedb .d > out.sql
>> shell> mysql mysqldb < out.sql
>>
>> All the autoincrement values can now be safely carried across because
>> I could provide the assistance with an explicit BIGINT (This is
>> already possible but *without* the autoincrement feature)
>>
>> I fail to understand the limiting of autoincrement to just INTEGER
>> where there is no difference internally to sqlite between INTEGER and
>> BIGINT etc. It is just an unnecessary restriction.
>>
>> Consider this 2 line patch which works against sqlite-3.6.2, it could
>> be extended to all the other (external) integer types with no adverse
>> effect. Am I missing something here?
>>
>>--- build.c.old 2008-12-14 20:53:19.0 +
>>+++ build.c 2008-12-14 16:29:03.0 +
>>@@ -1165,7 +1165,7 @@
>>   if( iCol>=0 && iColnCol ){
>> zType = pTab->aCol[iCol].zType;
>>   }
>>-  if( zType && sqlite3StrICmp(zType, "INTEGER")==0
>>+  if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||
>> sqlite3StrICmp(zType, "BIGINT")==0)
>> && sortOrder==SQLITE_SO_ASC ){
>> pTab->iPKey = iCol;
>> pTab->keyConf = onError;
>>@@ -1174,7 +1174,7 @@
>>   }else if( autoInc ){
>> #ifndef SQLITE_OMIT_AUTOINCREMENT
>> sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
>>-   "INTEGER PRIMARY KEY");
>>+   "INTEGER or BIGINT PRIMARY KEY");
>> #endif
>>   }else{
>> sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,
>> sortOrder, 0);
>>
>> Thank you for all your patience.
>>
>> regards,
>>
>> Nathan
>>
>>
>>> Ypu can rely on Sqlite storing  large integer value as 64 bits and a
>>> small one using less space.  The actual storage form for a particular
>>> column may vary from row to row according to decisions made by Sqlite at
>>> run time after it analyzes the data value.
>>> JS
>>>
>>> Hi,
>>>
 I am perfectly aware of the size of INTEGERS on differing platforms
 and of sqlite's typeless nature.

 Can you tell me why you can even specify BIGINT to sqlite then? Or
 SMALLINT or any other datatype? What is the difference between INTEGER
 and any other type? none of course!

 sqlite> CREATE TABLE 

[sqlite] Locking documentation enhancement.

[Mike Mestnik]

  The locking is well documented, but it's not vary clear that the
BEGIN command can be used to acquire locks.  The relation between the
different locking stats and parameters to the BEGIN command could be
spelled out.

For example:
BEGIN IMMEDIATE -> RESERVED lock
(Check this, it may not be factual.)

I think what confused me the most was:
http://www.sqlite.org/lockingv3.html

Under: 7.0 Transaction Control At The SQL Level

> The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is
> optional) is used to take SQLite out of autocommit mode. Note that the
> BEGIN command does not acquire any locks on the database. After a
> BEGIN command, a SHARED lock will be acquired when the first SELECT
> statement is executed. A RESERVED lock will be acquired when the first
> INSERT, UPDATE, or DELETE statement is executed.

This makes it sound like the only way to acquire a RESERVED lock is to
use a BEGIN/UPDATE combo.  I was about ready to create a table for me
to update, but then I asked on IRC and quotemstr directed me back the
the BEGIN command.

Thank you.

I'm not receiving mail from the list, so please CC me on replies.  Also
notify me if you would not like to be CCed on replies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[John Stanton]

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 resolve any data format issues.

Note that Sqlite maps to commonly used scripting systems seamlessly.  
Transferring data from somethiong like Mysql/DB2/Oracle/PostreSQL etc is 
very simple.  Making the reverse trip may not be so simple and in 
general would require some form of edit program to transform TEXT 
columns into the typed columns of say Mysql.  If such a transfer is 
important to you an application software layer can enforce the decclared 
types of Sqlite.  Otherwise use Mysql or preferably PostreSQL.

Finally, Sqlite is simple and easy to use, hence the "lite".  Let it 
make life easy for you and don't fight it.  You will be rewarded handsomely.

Nathan Catlow wrote:
> 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 and parses that name to decide on the
>> underlying type as basically either text or numeric.  It  decides at run
>> time how to store the  data.
>> 
>
> Yes, I understand this, sqlite's lovelyness.
>
>   
>> The declared type in Sqlite is  in essence an assist for the programmer
>> and is useful at application level to determine how to handle a column.
>> For example a floating point number declared as DATE would be processed
>> differently from one declared as ISOTOPE_COUNT.
>> 
>
> You've hit the nail on the head, I am trying to do exactly that!  
> Providing an assist for an application level by explicitly declaring  
> PRIMARY KEY BIGINT. This can then be transferred safely to another  
> database (mysql in this example) even by doing the following;
>
> $ sqlite3 sqlitedb .d > out.sql
> shell> mysql mysqldb < out.sql
>
> All the autoincrement values can now be safely carried across because  
> I could provide the assistance with an explicit BIGINT (This is  
> already possible but *without* the autoincrement feature)
>
> I fail to understand the limiting of autoincrement to just INTEGER  
> where there is no difference internally to sqlite between INTEGER and  
> BIGINT etc. It is just an unnecessary restriction.
>
> Consider this 2 line patch which works against sqlite-3.6.2, it could  
> be extended to all the other (external) integer types with no adverse  
> effect. Am I missing something here?
>
>--- build.c.old 2008-12-14 20:53:19.0 +
>+++ build.c 2008-12-14 16:29:03.0 +
>@@ -1165,7 +1165,7 @@
>   if( iCol>=0 && iColnCol ){
> zType = pTab->aCol[iCol].zType;
>   }
>-  if( zType && sqlite3StrICmp(zType, "INTEGER")==0
>+  if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||
> sqlite3StrICmp(zType, "BIGINT")==0)
> && sortOrder==SQLITE_SO_ASC ){
> pTab->iPKey = iCol;
> pTab->keyConf = onError;
>@@ -1174,7 +1174,7 @@
>   }else if( autoInc ){
> #ifndef SQLITE_OMIT_AUTOINCREMENT
> sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
>-   "INTEGER PRIMARY KEY");
>+   "INTEGER or BIGINT PRIMARY KEY");
> #endif
>   }else{
> sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,  
> sortOrder, 0);
>
> Thank you for all your patience.
>
> regards,
>
> Nathan
>
>   
>> Ypu can rely on Sqlite storing  large integer value as 64 bits and a
>> small one using less space.  The actual storage form for a particular
>> column may vary from row to row according to decisions made by Sqlite at
>> run time after it analyzes the data value.
>> JS
>>
>> Hi,
>> 
>>> I am perfectly aware of the size of INTEGERS on differing platforms
>>> and of sqlite's typeless nature.
>>>
>>> Can you tell me why you can even specify BIGINT to sqlite then? Or
>>> SMALLINT or any other datatype? What is the difference between INTEGER
>>> and any other type? none of course!
>>>
>>> sqlite> CREATE TABLE t(i SMALLINT, t TEXT);
>>> sqlite> INSERT INTO t(i,t) VALUES(, 'test');
>>> sqlite> SELECT * FROM t;
>>> |test
>>>
>>> But there is a difference, autoincrement ONLY works with "INTEGER",
>>> why? sqlite quite rightly allows the code above due to all types being
>>> treated the same, but all of a sudden starts getting all fussy when I
>>> want to autoincrement a BIGINT. If ROWID == INTEGER then it must match
>>> the others too ROWID == INTEGER == BIGINT == SMALLINT sqlite just
>>> shouldn't care.
>>>
>>> The point about "cross platform SQL" and using a library like libdbi,
>>> is that it tries to ensure that a particular length of data can fit in
>>> all makes of SQL.
>>>

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

[Nathan Catlow]

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 and parses that name to decide on the
> underlying type as basically either text or numeric.  It  decides at run
> time how to store the  data.

Yes, I understand this, sqlite's lovelyness.

>
> The declared type in Sqlite is  in essence an assist for the programmer
> and is useful at application level to determine how to handle a column.
> For example a floating point number declared as DATE would be processed
> differently from one declared as ISOTOPE_COUNT.

You've hit the nail on the head, I am trying to do exactly that!  
Providing an assist for an application level by explicitly declaring  
PRIMARY KEY BIGINT. This can then be transferred safely to another  
database (mysql in this example) even by doing the following;

$ sqlite3 sqlitedb .d > out.sql
shell> mysql mysqldb < out.sql

All the autoincrement values can now be safely carried across because  
I could provide the assistance with an explicit BIGINT (This is  
already possible but *without* the autoincrement feature)

I fail to understand the limiting of autoincrement to just INTEGER  
where there is no difference internally to sqlite between INTEGER and  
BIGINT etc. It is just an unnecessary restriction.

Consider this 2 line patch which works against sqlite-3.6.2, it could  
be extended to all the other (external) integer types with no adverse  
effect. Am I missing something here?

   --- build.c.old 2008-12-14 20:53:19.0 +
   +++ build.c 2008-12-14 16:29:03.0 +
   @@ -1165,7 +1165,7 @@
  if( iCol>=0 && iColnCol ){
zType = pTab->aCol[iCol].zType;
  }
   -  if( zType && sqlite3StrICmp(zType, "INTEGER")==0
   +  if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||
sqlite3StrICmp(zType, "BIGINT")==0)
&& sortOrder==SQLITE_SO_ASC ){
pTab->iPKey = iCol;
pTab->keyConf = onError;
   @@ -1174,7 +1174,7 @@
  }else if( autoInc ){
#ifndef SQLITE_OMIT_AUTOINCREMENT
sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
   -   "INTEGER PRIMARY KEY");
   +   "INTEGER or BIGINT PRIMARY KEY");
#endif
  }else{
sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,  
sortOrder, 0);

Thank you for all your patience.

regards,

Nathan

>
> Ypu can rely on Sqlite storing  large integer value as 64 bits and a
> small one using less space.  The actual storage form for a particular
> column may vary from row to row according to decisions made by Sqlite at
> run time after it analyzes the data value.
> JS
>
> Hi,
>> I am perfectly aware of the size of INTEGERS on differing platforms
>> and of sqlite's typeless nature.
>>
>> Can you tell me why you can even specify BIGINT to sqlite then? Or
>> SMALLINT or any other datatype? What is the difference between INTEGER
>> and any other type? none of course!
>>
>> sqlite> CREATE TABLE t(i SMALLINT, t TEXT);
>> sqlite> INSERT INTO t(i,t) VALUES(, 'test');
>> sqlite> SELECT * FROM t;
>> |test
>>
>> But there is a difference, autoincrement ONLY works with "INTEGER",
>> why? sqlite quite rightly allows the code above due to all types being
>> treated the same, but all of a sudden starts getting all fussy when I
>> want to autoincrement a BIGINT. If ROWID == INTEGER then it must match
>> the others too ROWID == INTEGER == BIGINT == SMALLINT sqlite just
>> shouldn't care.
>>
>> The point about "cross platform SQL" and using a library like libdbi,
>> is that it tries to ensure that a particular length of data can fit in
>> all makes of SQL.
>>
>> My code drives the databases not the other way around, so if *I*
>> decide an integer is only 32bits, then I don't give a damn if
>> sqlite/oracle or whatever wastes space be putting it in a 64bit space.
>> The ultimate goal is running the same code on all DB engines. The goal
>> is not to take an arbitrary database and expect libdbi to read it
>> efficiently or even correctly.
>>
>> The only thing I have no control over is when using the autoincrement
>> feature, as this is driven by sqlite, and will always attempt to use
>> the full 64bit space. I need to know this to ensure the correct memory
>> is allocated.
>>
>> I completely accept your point about assumption, but there has to be a
>> compromise on allocating 64bits everywhere, which is inefficient on
>> small systems. I want to raise this point with the libdbi developers.
>> Their code is broken, I know that, but an acceptable compromise is
>> nearly there. I just need to determine that a field is a
>> autoincrementing PRIMARY KEY or be able to explicitly state BIGINT
>> PRIMARY KEY.
>>
>> I have one question, lets forget about the argument about types, it is
>> a red herring.
>>
>> 

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

[Kees Nuyt]

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
-  --
sometablename  4

Every table with an INTEGER PRIMARY KEY AUTOINDEX
declaration is registerd there, one row per table.
You only have to consult sqlite_sequence if PRAGMA
table_info() returns 'INTEGER' for type and 1 for pk.

>I suppose if i wrote my own autoincrement all this
>would go away as I could ensure its length.

SQLite is an embedded database, not a generic RDBMS backend
engine, and doesn't have strong typing. It assumes the
application (anything that calls the SQLite library) knows
the database schema. That may make life a bit harder for
generic wrapper libraries and database management tools.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 and parses that name to decide on the 
underlying type as basically either text or numeric.  It  decides at run 
time how to store the  data.

The declared type in Sqlite is  in essence an assist for the programmer 
and is useful at application level to determine how to handle a column.  
For example a floating point number declared as DATE would be processed 
differently from one declared as ISOTOPE_COUNT.

Ypu can rely on Sqlite storing  large integer value as 64 bits and a 
small one using less space.  The actual storage form for a particular 
column may vary from row to row according to decisions made by Sqlite at 
run time after it analyzes the data value.
JS
 
Hi,
> I am perfectly aware of the size of INTEGERS on differing platforms  
> and of sqlite's typeless nature.
>
> Can you tell me why you can even specify BIGINT to sqlite then? Or  
> SMALLINT or any other datatype? What is the difference between INTEGER  
> and any other type? none of course!
>
> sqlite> CREATE TABLE t(i SMALLINT, t TEXT);
> sqlite> INSERT INTO t(i,t) VALUES(, 'test');
> sqlite> SELECT * FROM t;
> |test
>
> But there is a difference, autoincrement ONLY works with "INTEGER",  
> why? sqlite quite rightly allows the code above due to all types being  
> treated the same, but all of a sudden starts getting all fussy when I  
> want to autoincrement a BIGINT. If ROWID == INTEGER then it must match  
> the others too ROWID == INTEGER == BIGINT == SMALLINT sqlite just  
> shouldn't care.
>
> The point about "cross platform SQL" and using a library like libdbi,  
> is that it tries to ensure that a particular length of data can fit in  
> all makes of SQL.
>
> My code drives the databases not the other way around, so if *I*  
> decide an integer is only 32bits, then I don't give a damn if  
> sqlite/oracle or whatever wastes space be putting it in a 64bit space.  
> The ultimate goal is running the same code on all DB engines. The goal  
> is not to take an arbitrary database and expect libdbi to read it  
> efficiently or even correctly.
>
> The only thing I have no control over is when using the autoincrement  
> feature, as this is driven by sqlite, and will always attempt to use  
> the full 64bit space. I need to know this to ensure the correct memory  
> is allocated.
>
> I completely accept your point about assumption, but there has to be a  
> compromise on allocating 64bits everywhere, which is inefficient on  
> small systems. I want to raise this point with the libdbi developers.  
> Their code is broken, I know that, but an acceptable compromise is  
> nearly there. I just need to determine that a field is a  
> autoincrementing PRIMARY KEY or be able to explicitly state BIGINT  
> PRIMARY KEY.
>
> I have one question, lets forget about the argument about types, it is  
> a red herring.
>
> Is there a way through sqlite API or PRAGMA to determine a field is an  
> autoincrementing INTEGER PRIMARY KEY? The only way i've found is to  
> parse the create table statement from sqlite_master which is cludgy.
>
> PRAGMA table_info(t);
> PRAGMA index_list(t);
>
> Both those give me no love.
>
> I suppose if i wrote my own autoincrement all this would go away as I  
> could ensure its length.
>
> regards,
>
> Nathan
>
>
>
> 
> This message was sent using IMP, the Internet Messaging Program.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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

Re: [sqlite] Adding data with periods

[Chris Wedgwood]

On Sun, Dec 14, 2008 at 11:04:56PM -0600, aditya siram wrote:

> sqlite> create table test_table ("Contents" varchar);
> sqlite> insert into test_table "hello . world";
> SQL error: near ""hello . world"": syntax error

insert into test_table values("hello . world");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

[Igor Tandetnik]

"hussainfarzana"
 wrote in
message news:21009480.p...@talk.nabble.com
> We have used BEGIN and COMMIT while executing our statements.While
> executing the BEGIN the return value is 0,but for COMMIT its
> returning 1.

COMMIT is failing. Without seeing your code, it's hard to know why.

One possible reason is that you still have open statements. Make sure to 
sqlite3_reset or sqlite3_finalize all open statement handles before 
executing COMMIT.

Igor Tandetnik



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

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

[Nathan Catlow]

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().
>
>   Why impossible?  The type in INTEGER, just as returned.
>
>> As the PRIMARY KEY is an alias to ROWID (64bit),
>
>   The PRIMARY KEY is *ONLY* an alias for ROWID if it is defined
>   as "INTEGER PRIMARY KEY".  You can define any arbitrary PRIMARY KEY,
>   including a multi-column key.
>
>> the data should be returned as a 64bit integer.
>
>   And it is.  All INTEGER valued types in SQLite are capable of holding
>   a 64 bit value.
>
>> Unfortunately the PRAGMA table_info()
>> command returns a type of INTEGER (32 bit in at least mysql).
>
>   Yes.  It returns INTEGER because that's what it is-- at least if
>   you're doing a ROWID alias with AUTOINCREMENT.
>
>   What MySQL might or might not define INTEGER to be is irrelevant.
>   The SQL standard doesn't have specs for how many bits different types
>   of numbers take.  If you, or any libraries or software you are using
>   makes such assumptions, you're going to have a lot of headaches.
>
>   "INTEGER" in Oracle, for example, defines a 38 digit (base-10)
>   integer.  That's a bit more than 120 bits.
>
>   On the flip side, I *wouldn't* assume a ROWID is a 64 bit integer.
>   That happens to be true in SQLite, but there is nothing that says
>   that has to be true.  Again, it isn't true in Oracle.
>
>> It is
>> also impossible to help by defining primary key as BIGINT PRIMARY KEY,
>> as the field does not then autoincrement in sqlite.
>
>   Right.  The docs are quite specific.  You can define a BIGINT
>   PRIMARY KEY, but since it is not "INTEGER PRIMARY KEY" it won't
>   become a ROWID alias.  AUTOINCREMENT only works on ROWID columns.
>
>> This would then
>> mean it will be returned via the PRAGMA statement as BIGINT and DB
>> independant libraries can return the correct datatype and also be SQL
>> compatible with other DB engines.
>
>   The fault is in the libraries for making rash and incorrect
>   assumptions about types and sizes, not in anything SQLite is doing.
>   Anything that assumes "INTEGER" in SQL is a 32 bit number (or that
>   any column marked ROWID must be a 64 bit integer) is making
>   poor and incorrect assumptions.
>
>> Another example would be to move data from sqlite to mysql, because
>> the datatype INTEGER PRIMARY KEY is only 32bits on mysql, if I were to
>> dump the database from sqlite the CREATE TABLE test(id INTEGER PRIMARY
>> KEY) would only be able to hold a 32bit integer in mysql where the
>> data in sqlite is actually 64bits (ROWID) resulting in an overflow.
>
>   Yes.  You'll find similar problems with nearly any other numeric
>   type in SQL.  This is not C or C++ (and even C does not define a
>   specific size for "int"...).
>
>   Welcome to the wonderful world of cross-platform SQL.
>
>> This is a real showstopper for me, I want to use sqlite, but have an
>> compatible way of supporting other db engines.
>
>   If compatibility is the issue, you might have a look at the SQL
>   standards and what assumptions you can actually make about types,
>   sizes, and value domains.  It seems you, or the software you're using,
>   has a lot of misconceptions about the type systems used in different
>   flavors of SQL.   In short, you can't assume much of anything.
>
>   You definitely can't assume something as generic as "INTEGER" has a
>   specific domain, like a 32-bit number, or that "ROWID" is a 64-bit
>   integer.
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Hi,

I am perfectly aware of the size of INTEGERS on differing platforms  
and of sqlite's typeless nature.

Can you tell me why you can even specify BIGINT to sqlite then? Or  
SMALLINT or any other datatype? What is the difference between INTEGER  
and any other type? none of course!

sqlite> CREATE TABLE t(i SMALLINT, t TEXT);
sqlite> INSERT INTO t(i,t) VALUES(, 'test');
sqlite> SELECT * FROM t;
|test

But there is a difference, autoincrement ONLY works with "INTEGER",  
why? sqlite quite rightly allows the code above due to all types being  
treated the same, but all of a sudden starts getting all fussy when I  
want to autoincrement a BIGINT. If ROWID == INTEGER then it must match  
the others too ROWID == INTEGER == BIGINT == SMALLINT sqlite just  
shouldn't care.

The point about "cross platform SQL" and using a library like libdbi,  
is that it tries 

Re: [sqlite] Adding data with periods

[Eugene Wee]

Additionally, double quotes do have a "special meaning" in that they are
used to delimit identifiers.

Regards,
Eugene

On Mon, Dec 15, 2008 at 3:15 PM, John Stanton  wrote:

> Single quotes are SQL, as chosen by he designers.  It is good practice
> to stick to the standard rather than rely on extensions which vary from
> implementatiopn tio implementation.
>
> Mohd Radzi Ibrahim wrote:
> > It seems to works either way.
> >
> > I'm just wondering is there any hidden reason that single quote is
> > preferred? Portability?
> > Or is double-qoute has some kind of special meaning that we should use it
> > for that special purpose?
> >
> >
> > -radzi-
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users