Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

On 6/14/2017 5:42 AM, R Smith wrote: On 2017/06/14 7:08 AM, Wout Mertens wrote: Is there a way to specify the starting rowid when using autoincrement? Or should I insert and then remove a row with the id set to one less than the desired id? This is quite easy, but first it is helpful to

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

On 2017/06/14 7:08 AM, Wout Mertens wrote: Is there a way to specify the starting rowid when using autoincrement? Or should I insert and then remove a row with the id set to one less than the desired id? This is quite easy, but first it is helpful to understand the mechanism by which SQLite

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

from https://sqlite.org/autoinc.html On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the

Re: [sqlite] INTEGER PRIMARY KEY

> But we have to preserve backwards compatibility - even with bugs > like this. ​How about a new release? i.e. sqlite4 No backward compatibilty issues.​ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] INTEGER PRIMARY KEY

On Jun 12, 2017 8:26 PM, "Keith Medcalf" wrote: Additionally, declaring NOT NULL or NULL is ignored. CHECK constraints are honoured. DEFAULT values are ignored. so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1)); & CREATE TABLE x(id INTEGER NULL

Re: [sqlite] INTEGER PRIMARY KEY

On Jun 12, 2017 5:43 PM, "Richard Hipp" wrote: On 6/13/17, Scott Robison wrote: > > Is it fair to say that the rowid aliasing behavior does not require > (by design) the incantation "INTEGER PRIMARY KEY" (all three words in > that order as the "type")

Re: [sqlite] INTEGER PRIMARY KEY

ers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Richard Hipp > Sent: Monday, 12 June, 2017 17:44 > To: SQLite mailing list > Subject: Re: [sqlite] INTEGER PRIMARY KEY > > On 6/13/17, Scott Robison <sc...@casaderobison.com> wrote: > > > > Is

Re: [sqlite] INTEGER PRIMARY KEY

On 6/13/17, Scott Robison wrote: > > Is it fair to say that the rowid aliasing behavior does not require > (by design) the incantation "INTEGER PRIMARY KEY" (all three words in > that order as the "type") as long as the type is INTEGER and the > constraint PRIMARY KEY

Re: [sqlite] INTEGER PRIMARY KEY

On Mon, Jun 12, 2017 at 4:20 PM, Simon Slavin wrote: > > > On 12 Jun 2017, at 11:01pm, Scott Robison wrote: > >> Is it fair to say that the rowid aliasing behavior does not require >> (by design) the incantation "INTEGER PRIMARY KEY" (all three

Re: [sqlite] INTEGER PRIMARY KEY

On 12 Jun 2017, at 11:01pm, Scott Robison wrote: > Is it fair to say that the rowid aliasing behavior does not require > (by design) the incantation "INTEGER PRIMARY KEY" (all three words in > that order as the "type") as long as the type is INTEGER and the >

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

On 19-10-2014 17:48, Keith Medcalf wrote: for a table test(i integer primary key, j integer) the new i (rowid) is as follows: test.i = case when test.i is not null then test.i else case max(test.i) when null then 1 else max(test.i)+1 end end if you add the autoincrement keyword, then the

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

ion commits. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Luuk >Sent: Sunday, 19 October, 2014 08:10 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] 'INTEGER PRIMARY KEY' start value > >

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

On 19-10-2014 15:27, Baruch Burstein wrote: Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually guaranteed to start from 1? Or at least from a positive number? no C:\temp>sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

On 19 Oct 2014, at 2:27pm, Baruch Burstein wrote: > Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually > guaranteed to start from 1? Or at least from a positive number? See the section 'Background' in for the

Re: [sqlite] Integer data type

On 7/19/2013 8:29 AM, Paolo Bolzoni wrote: Interesting problem, can you add a new comparison operator to sqlite3? Yes, but only for strings, not for ints. http://sqlite.org/c3ref/create_collation.html -- Igor Tandetnik ___ sqlite-users mailing

Re: [sqlite] Integer data type

Or just use a BLOB. On Fri, Jul 19, 2013 at 2:29 PM, Simon Slavin wrote: > > On 19 Jul 2013, at 1:20pm, Paolo Bolzoni > wrote: > >> True, I was thinking as follow up of what I >> mentioned in the first message. >> -11 is the result of the

Re: [sqlite] Integer data type

On 19 Jul 2013, at 1:20pm, Paolo Bolzoni wrote: > True, I was thinking as follow up of what I > mentioned in the first message. > -11 is the result of the cast of 4294967285 > from unsigned int to int in a machine > where int are 32 bits long. Then don't cast.

Re: [sqlite] Integer data type

gt; Gesendet: Freitag, 19. Juli 2013 14:11 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Integer data type > > After all do you really care if the unsigned int 4294967285 is stored as -11? > > On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin <slav...@bigfraud.or

Re: [sqlite] Integer data type

True, I was thinking as follow up of what I mentioned in the first message. -11 is the result of the cast of 4294967285 from unsigned int to int in a machine where int are 32 bits long. On Fri, Jul 19, 2013 at 2:16 PM, Richard Hipp wrote: > On Fri, Jul 19, 2013 at 8:11 AM,

Re: [sqlite] Integer data type

It might change the sort order... -Ursprüngliche Nachricht- Von: Paolo Bolzoni [mailto:paolo.bolzoni.br...@gmail.com] Gesendet: Freitag, 19. Juli 2013 14:11 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Integer data type After all do you really care if the unsigned int

Re: [sqlite] Integer data type

On Fri, Jul 19, 2013 at 8:11 AM, Paolo Bolzoni < paolo.bolzoni.br...@gmail.com> wrote: > After all do you really care if the unsigned > int 4294967285 is stored as -11? > To be pedantic: SQLite stores 4294967285 as 4294967285. It is 18446744073709551605 that gets stored as -11. -- D. Richard

Re: [sqlite] Integer data type

After all do you really care if the unsigned int 4294967285 is stored as -11? On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin wrote: > > On 19 Jul 2013, at 11:02am, techi eth wrote: > >> Definition of integer data type will talk for signed integer. What

Re: [sqlite] Integer data type

On 19 Jul 2013, at 11:02am, techi eth wrote: > Definition of integer data type will talk for signed integer. What about > unsigned integer ?Are they also be part of same data type. Yes. SQLite has no special type for an unsigned integer. Just store them as integers. The

Re: [sqlite] Integer data type

As far as I know there is no unsigned integer in sqlite3. If you need cast to a signed integer of the same size before using sqlite3. On Fri, Jul 19, 2013 at 12:02 PM, techi eth wrote: > Definition of integer data type will talk for signed integer. What about > unsigned

Re: [sqlite] Integer Truncation Issue

Mistachkin [sql...@mistachkin.com] Sent: Friday, April 06, 2012 2:28 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Integer Truncation Issue Jay Howard wrote: > > I have gone as far as > > Modifying the table but still no dice.. > Maybe I have to drop and recreate th

Re: [sqlite] Integer Truncation Issue

Jay Howard wrote: > > I have gone as far as > > Modifying the table but still no dice.. > Maybe I have to drop and recreate the table? > How did you change the data type of the column without dropping and re-creating the table? As others have said, System.Data.SQLite uses the declared types

Re: [sqlite] Integer Truncation Issue

vel Ivanov Sent: Friday, April 06, 2012 11:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Integer Truncation Issue > When I browse the table it shows one record I open the db connection > in c# and it still has 31821 records in it... Which means you open connecti

Re: [sqlite] Integer Truncation Issue

half Of Jay Howard > Sent: Friday, April 06, 2012 9:55 AM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] Integer Truncation Issue > > I have gone as far as > > Modifying the table but still no dice.. > Maybe I have to drop and recreate the table? > > > &g

Re: [sqlite] Integer Truncation Issue

' Subject: Re: [sqlite] Integer Truncation Issue I have gone as far as Modifying the table but still no dice.. Maybe I have to drop and recreate the table? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson Sent

Re: [sqlite] Integer Truncation Issue

Discussion of SQLite Database Subject: Re: [sqlite] Integer Truncation Issue Disclaimer: I am not in any way an expert! As I understand things, in SQLite, any value can be stored in any column, regardless of type. But in C#, the System.Data.SQLite library isn't that flexible. If the table

Re: [sqlite] Integer Truncation Issue

Disclaimer: I am not in any way an expert! As I understand things, in SQLite, any value can be stored in any column, regardless of type. But in C#, the System.Data.SQLite library isn't that flexible. If the table definition says the column contains integers, then all data in the table is

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

GB schrieb am 24.08.2011 19:59: Hi all, I have a table like this: CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); CREATE INDEX createIdx on t(createdAt); SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with current content. When perfoming a Statement like

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

GB schrieb am 25.08.2011 18:27: Simon Slavin schrieb am 25.08.2011 02:00: Had you thought of creating an explicit index on the rowid column, then running ANALYZE again ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Simon Slavin schrieb am 25.08.2011 02:00: > Had you thought of creating an explicit index on the rowid column, then > running ANALYZE again ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org >

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

> > It seems equally logical to me that one of A or B might be evaluated, and if > it were false, then the other might not be evaluated. I don't think so if efficiency matters. Of course the rule could be to evaluated from right to left instead, but it's good to have just one rule, and again,

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

logical? It seems equally logical to me that one of A or B might be evaluated, and if it were false, then the other might not be evaluated. And it would be logical to choose which of A or B to evaluated on a predicted cost and probability of an advantageous false result. but hay. Who said

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 11:18pm, GB wrote: > Well, that is exactly what the sqlite_stat2 table is meant for. It's > information is supposed to make the query planner able to decide upon > the usefulness of an index. Unfortunately, histogram information is not > collected for the implicit rowid

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Simon Slavin schrieb am 24.08.2011 23:33: > On 24 Aug 2011, at 9:59pm, GB wrote: > >> Simon Slavin schrieb am 24.08.2011 22:38: >> >>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >>> createdAt>= '2011-08-01' createdAt<= '2011-08-02' >> Thank you for your thoughts but I

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 9:59pm, GB wrote: > Simon Slavin schrieb am 24.08.2011 22:38: > >> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >> createdAt>= '2011-08-01' createdAt<= '2011-08-02' > > Thank you for your thoughts but I already tried this with no different > results

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Simon Slavin schrieb am 24.08.2011 22:38: > On 24 Aug 2011, at 6:59pm, GB wrote: > >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' > Just out of curiosity, try changing both the BETWEEN formulations so it says > > SELECT

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 24 Aug 2011, at 6:59pm, GB wrote: > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' Just out of curiosity, try changing both the BETWEEN formulations so it says SELECT itemID FROM t WHERE itemID >= 100 AND itemID <=

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

No, SQLite (as well as most other database systems) does a more elaborate evaluation. I breaks the statement apart into subterms and tries to determine which one makes the most beneficial use of an index so the order of the statement does not matter. See http://www.sqlite.org/optoverview.html

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Igor Tandetnik schrieb am 24.08.2011 20:20: > On 8/24/2011 1:59 PM, GB wrote: >> When perfoming a Statement like this: >> >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' >> >> the analyzer always chooses the rowid index which

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

Don't know how SQLite should behave in this case, but seems logical to me that A and B would force that A is always evaluated, and B is evaluated only if A is true. I would change the order of the two betweens: SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND '2011-08-02' AND

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

On 8/24/2011 1:59 PM, GB wrote: > When perfoming a Statement like this: > > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' > > the analyzer always chooses the rowid index which results in a scan over > one million rows. It would

Re: [sqlite] 'integer'

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/18/2011 02:20 AM, Stefan Keller wrote: > for it kind of weak typing (meaning > 'weak' compared to the information schema). SQLite's typing is not weak. YOU GET BACK OUT WHAT YOU PUT IN. I'd argue other implementations are the problem since

Re: [sqlite] 'integer'

Slavin [slav...@bigfraud.org] Sent: Monday, April 18, 2011 7:45 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] 'integer' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite

Re: [sqlite] 'integer'

On 18 Apr 2011, at 10:20am, Stefan Keller wrote: > Simon wrote: >> Stefan, please read this: >> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt > > Thank you for the weblink. > Here are some citations from it which could be of interest to you too > (see below). > I don't think SQLite

Re: [sqlite] 'integer'

Again a disclaimer: I use SQlite often e.g. for continuous testing and in Desktop Apps. - and I like it as it is except for it's homepage declaration and (more formally) for it kind of weak typing (meaning 'weak' compared to the information schema). Weak typing in databases assigns the house

Re: [sqlite] 'integer'

On 18 Apr 2011, at 1:53am, Roger Binns wrote: > Your complaints are really that SQLite doesn't function exactly how you are > used to. Note that it does do exactly what you tell it - the behaviour is > not random. It's probably worth Stefan reading the SQL specification. Stefan, please read

Re: [sqlite] 'integer'

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/17/2011 04:43 PM, Stefan Keller wrote: > I clearly prefer 'weakly typed' (or even type-less) over "dynamically > typed" (mentioned in http://www.sqlite.org/datatype3.html ). The irony is that SQLite is more strongly typed in my opinion. If you

Re: [sqlite] 'integer'

Sorry for those brutally honest words - and yes, datastore is not well defined. But look at how homepage introduces SQLite: a "serverless, ... SQL database engine". As said, I believe SQLite could avoid traps by declaring more clearly what it is and what it is *not*. Wikipedia says: it is "an

Re: [sqlite] 'integer'

Stefan, > SQLite should'nt be called a "SQL database" (as e.g. touted on its homepage). > Instead it should clearly declare itself as an "In-memory SQL > Datastore" or a "Data container with SQL capabilities". This is quite serious allegations. Making them you should explain what they are based

Re: [sqlite] 'integer'

I'm new around here - exactly what element of SQLite precludes it from being a database? On 17/04/2011 13:12, Stefan Keller wrote: > Michael and Jay are right about the subtleties on how SQlite > interprets what is a data type, a primary key and a database schema > and it's ACID implementation

Re: [sqlite] 'integer'

Michael and Jay are right about the subtleties on how SQlite interprets what is a data type, a primary key and a database schema and it's ACID implementation in general. To me, the main reason - and remedy - of this FAQ is that SQlite should'nt be called a "SQL database" (as e.g. touted on its

Re: [sqlite] 'integer'

Seems to behave OK for me on 3.7.5 on Windows. What version are you using on what OS with what compile flags? You also "said" it didn't work but you didnt' actually what what you did. Like this... SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";"

Re: [sqlite] 'integer'

On Sat, Apr 16, 2011 at 07:40:59PM +0200, Tobias Vesterlund scratched on the wall: > Hi, > > I ran into something I don't understand, maybe someone here can shed some > light on it for me. > > I have a table named Tg which is created (with tcl) by: > > CREATE TABLE Tg (TgConfigId INTEGER

Re: [sqlite] 'integer'

On 16 Apr 2011, at 6:40pm, Tobias Vesterlund wrote: > If I do: > INSERT INTO Tg (TgConfigId) VALUES (1); > > The following select works: > SELECT * FROM Tg WHERE TgConfigId = 1; > > But if I insert '1' instead I have to select on '1', 1 no longer works. That > makes some sense, but not

Re: [sqlite] 'integer'

http://www.sqlite.org/datatype3.html F. On Sat, Apr 16, 2011 at 7:40 PM, Tobias Vesterlund wrote: > Hi, > > I ran into something I don't understand, maybe someone here can shed some > light on it for me. > > I have a table named Tg which is created (with tcl)

Re: [sqlite] integer or string in fts3 table

On Mon, Jan 31, 2011 at 9:27 PM, Samuel Adam wrote: > I can’t help you there other than to say, give your boss the facts and > point out that if QA approved that schema, QA failed its job. Would that it were it so easy. Unfortunately I work for a startup, and this code was

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 15:33:24 -0500, Gabe da Silveira wrote: > On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam wrote: > >> I suggested rewriting your schema. Non-TEXT data which will not be >> subjected to a MATCH search is best stored in another table

Re: [sqlite] integer or string in fts3 table

Here's a standalone script that provides an id field with an int on my local OS X machine as I desire, and creates an id field with text when run on the Gentoo machine as breaks the [difficult-to-patch] application: https://gist.github.com/aae2366637c94a2861ae Going home for the night now

Re: [sqlite] integer or string in fts3 table

On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam wrote: > I suggested rewriting your schema.  Non-TEXT data which will not be > subjected to a MATCH search is best stored in another table and JOINed > with the FTS3 table, as Mr. Hess also explained.  Also, specifications > such

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 14:05:15 -0500, Gabe da Silveira wrote: [...] > On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam wrote: [...] >>* Is the database being populated with parameter binding, or >> not? If so, >> is id being bound as

Re: [sqlite] integer or string in fts3 table

On Mon, Jan 31, 2011 at 5:34 PM, Samuel Adam wrote: > According to the docs, results from those queries should be reversed.  I > suspect that Mr. da Silveira’s different platforms actually have different > SQLite versions, and that one version is consistent with the docs

Re: [sqlite] integer or string in fts3 table

First of all, thank ou both Simon and Samuel for such thorough consideration of this problem. I've been in meetings all afternoon (London time) and will respond to each email where appropriate in turn... On Mon, Jan 31, 2011 at 1:24 PM, Samuel Adam wrote: > On Mon, 31 Jan

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 13:37:35 -0500, Samuel Adam wrote: > The FTS3 > columns declared INTEGER indeed seem to behave as regular INTEGER columns > in regular, non-MATCH queries (although I did not test to see if affinity > would coerce a '1' to INTEGER on insertion). No

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 13:16:55 -0500, Scott Hess wrote: > The FTS3 code mostly passes values directly down to and up from the > underlying tables, so if you bound an integer, it is likely to come > back out as an integer when you query. Just to be clear: In the tests I posted

Re: [sqlite] integer or string in fts3 table

On Mon, Jan 31, 2011 at 9:15 AM, Samuel Adam wrote: > On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam wrote: >> On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin >> wrote: >>> In the definition given in the original post, which I

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 12:34:24 -0500, Samuel Adam wrote: > According to the docs, results from those queries should be reversed. I > suspect that Mr. da Silveira’s different platforms actually have > different SQLite versions, and that one version is consistent with the

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 06:30:55 -0500, Gabe da Silveira wrote: > I have an issue with an fts3 table sqlite 3.6.22. I have a PHP script > that builds an sqlite3 client database for a ps3 application. > Depending on where I run the build the script (Gentoo or Mac OS X) I > get

Re: [sqlite] integer or string in fts3 table

On 31 Jan 2011, at 5:15pm, Samuel Adam wrote: > Spoke too soon. SQLite actually ignores the PK definition, and does *not* > use "id" as an alias for the rowid. Evidence coming in another post; I > think I found a bug. Oooh. Interesting. Simon.

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam wrote: > On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin > wrote: > >> In the definition given in the original post, which I quoted, the table >> was defined as follows: >> >>> CREATE VIRTUAL TABLE

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin wrote: > In the definition given in the original post, which I quoted, the table > was defined as follows: > >> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT >> NULL, first_name TEXT NOT NULL,

Re: [sqlite] integer or string in fts3 table

On 31 Jan 2011, at 2:26pm, Samuel Adam wrote: > On Mon, 31 Jan 2011 08:44:22 -0500, Simon Slavin > wrote: > >> On 31 Jan 2011, at 1:24pm, Samuel Adam wrote: > [snip] >>> Actually since this is an FTS3 table, the form with the quotes is the >>> correct one. As Mr. da

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 08:44:22 -0500, Simon Slavin wrote: > On 31 Jan 2011, at 1:24pm, Samuel Adam wrote: [snip] >> Actually since this is an FTS3 table, the form with the quotes is the >> correct one. As Mr. da Silveira alluded later in his post, in FTS3, the >> row ID

Re: [sqlite] integer or string in fts3 table

On 31 Jan 2011, at 1:24pm, Samuel Adam wrote: > On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin > wrote: > >> On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: >>> >>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT >>> NULL, first_name TEXT NOT

Re: [sqlite] integer or string in fts3 table

On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin wrote: > On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: > >> I have a PHP script >> that builds an sqlite3 client database for a ps3 application. >> Depending on where I run the build the script (Gentoo or Mac OS X) I

Re: [sqlite] integer or string in fts3 table

On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote: > I have a PHP script > that builds an sqlite3 client database for a ps3 application. > Depending on where I run the build the script (Gentoo or Mac OS X) I > get a database file that has different semantics for a column declared > as an

Re: [sqlite] Integer Storage class

On 2 Sep 2009, at 3:26pm, Sebastian Bermudez wrote: > i chose Integer for Performance isues... i'm develop. an POS > Software and our articles table has lot's of records. i need to > optimize search. an Integer (1,2,4,6,8 bytes) is faster that 13- > bytes-ean text for

Re: [sqlite] Integer Storage class

Beau Wilkinson wrote: >> i chose Integer for Performance isues... i'm develop. an POS Software >> and our articles table has lot's of >records. i need to optimize >> search. an Integer (1,2,4,6,8 bytes) is faster that 13-bytes-ean text >> for >comparison. >> > > That's

Re: [sqlite] Integer Storage class

On Wed, Sep 02, 2009 at 10:47:53AM -0500, Beau Wilkinson scratched on the wall: > That's reasonable, but I think Sqlite stores everything as textual > data anyway. SQLite2 did store everything as text strings, but SQLite3 does not. See "Storage Classes": http://sqlite.org/datatype3.html

Re: [sqlite] Integer Storage class

n though both are still larger than the integer 100). But if INT simply doesn't have the necessary range, your options are limited. --- El mié 2-sep-09, Beau Wilkinson <b...@mtllc.us> escribió: > De: Beau Wilkinson <b...@mtllc.us> > Asunto: Re: [sqlite] Integer Storage class &g

Re: [sqlite] Integer Storage class

cribió: > De: Beau Wilkinson <b...@mtllc.us> > Asunto: Re: [sqlite] Integer Storage class > Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Fecha: miércoles, 2 de septiembre de 2009, 11:14 am > I probably wouldn't use INT fo

Re: [sqlite] Integer Storage class

) will fit in two bytes. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies [simon.james.dav...@googlemail.com] Sent: Wednesday, September 02, 2009 9:05 AM To: General Discussion of SQLite Database Subject: R

Re: [sqlite] Integer Storage class

2009/9/2 Sebastian Bermudez : > Ok. my problem is my SQLITE front end (SQLITE ADMINISTRATOR v 0.8.3.2) ... > show me 0 (cero) in that column. Looks like SQLITE ADMINISTRATOR v 0.8.3.2 only deals with signed 32 bit values. 2147483647 it accepts as a valid value,

Re: [sqlite] Integer Storage class

Ok. my problem is my SQLITE front end (SQLITE ADMINISTRATOR v 0.8.3.2) ... show me 0 (cero) in that column. thanks --- El mié 2-sep-09, Simon Davies <simon.james.dav...@googlemail.com> escribió: > De: Simon Davies <simon.james.dav...@googlemail.com> > Asunto: Re: [sqlite] Int

Re: [sqlite] Integer Storage class

2009/9/2 Sebastian Bermudez : > hi! i have an table ( articles ) with a column for EAN13 Barcodes like ( > 7790080066784). > I have created the column with Integer data type... ( i have chose that data > type after read the SQLITE DOC where it' say: "INTEGER. The

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

Pavel Ivanov wrote: > Could you explain why this scenario doesn't cause infinite call cycle > of the trigger by itself? Is there some protection in SQLite which > breaks such cycles? SQLite doesn't support recursive triggers: a trigger cannot call itself, directly or indirectly. SQLite keeps

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

Oliver Peters wrote: > > I want the "normal" user only identify himself by putting his id into > the field identity and afterwards let the system decide in what field to > put his id (INSERT = creator, UPDATE = editor). Doing this for every > record I can show who created it and who was the last

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

On , Pavel Ivanov wrote: > Hi, Richard! > > Could you explain why this scenario doesn't cause infinite call cycle > of the trigger by itself? Is there some protection in SQLite which > breaks such cycles? > Many dbms forbid recursive trigger calls that modify a table that

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

Am Montag, den 22.06.2009, 17:39 -0600 schrieb Dennis Cote: > Oliver Peters wrote: > > sorry: my code wasn't completely what I wanted so here again: > > > > CREATE TRIGGER IF NOT EXISTS test > > BEFORE INSERT ON "a" > > BEGIN > > INSERT INTO

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

Oliver Peters wrote: > sorry: my code wasn't completely what I wanted so here again: > > CREATE TRIGGER IF NOT EXISTS test > BEFORE INSERT ON "a" > BEGIN > INSERT INTO a(code,name,creator) > VALUES(new."code",new."name",new."identity"); >

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

Hi, Richard! Could you explain why this scenario doesn't cause infinite call cycle of the trigger by itself? Is there some protection in SQLite which breaks such cycles? Pavel On Mon, Jun 22, 2009 at 4:10 PM, D. Richard Hipp wrote: > > On Jun 22, 2009, at 3:33 PM, Oliver Peters

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote: > Hello out there, > > > to my mind I get false entries in sqlite_sequence using this code: > > > CREATE TABLE IF NOT EXISTS a( > id INTEGER PRIMARY KEY AUTOINCREMENT, > code

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[...] > > The ROWID is not generated until the INSERT statement actually runs. > Hence the BEFORE trigger does not have access to it and the BEFORE > trigger sees a NULL. Change the trigger to an AFTER trigger and it > will work. > [...] > > D. Richard Hipp > d...@hwaci.com Thanks for

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote: > Hello out there, > > > to my mind I get false entries in sqlite_sequence using this code: > > > CREATE TABLE IF NOT EXISTS a( > id INTEGER PRIMARY KEY AUTOINCREMENT, > code

Re: [sqlite] INTEGER PRIMARY KEY and triggers

> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id > fields in my SQLite projects, yet I would like to try some triggers > as well. Of course, every time I add a trigger that accesses a > table with these types of id fields, all sorts of odd things > happen. Not to

Re: [sqlite] INTEGER PRIMARY KEY and Triggers

On Nov 17, 2008, at 12:45 AM, [EMAIL PROTECTED] wrote: >> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id >> fields in my SQLite projects, yet I would like to try some triggers >> as >> well. Of course, every time I add a trigger that accesses a table >> with these types

Re: [sqlite] INTEGER PRIMARY KEY and triggers

On Nov 16, 2008, at 2:46 AM, Craig Smith wrote: > I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id > fields in my SQLite projects, yet I would like to try some triggers as > well. Of course, every time I add a trigger that accesses a table > with these types of id fields, all

  1   2   >