Re: [sqlite] Does SQLite use field definitions?

[Hick Gunter]

It will if you add AUTOINCREMENT

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clyde Eisenbeis
Gesendet: Donnerstag, 02. Februar 2017 15:12
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] Does SQLite use field definitions?

What about the INTEGER PRIMARY KEY (defined in my first email post)?
Does this field start at 1, and then auto increment for each new line?

On Thu, Feb 2, 2017 at 5:02 AM, R Smith <rsm...@rsweb.co.za> wrote:
>
>
> On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:
>>
>> I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
>> handle char strings longer than 256.
>>
>> However, I don't see any complaints by SQLite when I use MEMO.
>
>
> SQLite will never complain about anything you use as a type. What you
> should to use is TEXT. In SQLite any string value, of any length* can
> be stored in any column, but will be "handled like text" when the
> column is of the TEXT type affinity, and also allow a collation
> method. Honestly, you can put the text of the complete volumes of Don
> Quixotic in a single TEXT field in a database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of
> which may differ based on encoding, and if you add the quoted string
> to the query text directly (as opposed to binding it via the API),
> then the maximum size will be governed by the max character length of
> the query input, which can be configured via the API also. You can add
> the string as memory bytes as a BLOB to go bigger, but then you lose
> collation capability I'll stop there, it's probably more unnecessary info 
> than you wished for already.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Does SQLite use field definitions?

[Keith Medcalf]

On Thursday, 2 February, 2017 09:12, Clyde Eisenbeis  
inquired:

> What about the INTEGER PRIMARY KEY (defined in my first email post)?
> Does this field start at 1, and then auto increment for each new line?

INTEGER PRIMARY KEY declares a field to be an alias for the rowid.

If you insert NULL (or do not specify a value) then the value is one greater 
than the largest rowid currently in use, that is also at least 1.  So newrowid 
= max(1, max(rowid) + 1).

If you add the AUTOINCREMENT keyword, then the semantics changes slightly and 
the max(rowid) ever seen for the table is stored in another table, and that 
value is incremented by 1 to determine the new rowid.  In all cases, the 
minimum value of an automatically generated rowid is 1.

In either case you can explicitly insert (or update) a rowid to contain any 
64-bit signed integer value.  In the case of a definition with AUTOINCREMENT, 
the max rowid ever seen is saved, so if you did something like:

insert into x (key, data) values (NULL, 'test');
update x set key = 1000 where data='test';
delete from x;
insert into x (key, data) values (NULL, 'again');

then if the definition of column key was INTEGER PRIMARY KEY, the end row in x 
would be (1, 'again').  If it was INTEGER PRIMARY KEY AUTOINCREMENT then the 
row should be (1001, 'again').

"INTEGER PRIMARY KEY" is special and can only contain integers -- it is an 
alias for the rowid.  Other primary key (eg TEXT PRIMARY KEY) can contain any 
data type and has the same effect as declaring it TEXT UNIQUE.

sqlite> create table x (key integer primary key, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
1|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
2|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
1|test
10001|again
sqlite>
 
> On Thu, Feb 2, 2017 at 5:02 AM, R Smith  wrote:
> >
> >
> > On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:
> >>
> >> I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
> >> handle char strings longer than 256.
> >>
> >> However, I don't see any complaints by SQLite when I use MEMO.
> >
> >
> > SQLite will never complain about anything you use as a type. What you
> should
> > to use is TEXT. In SQLite any string value, of any length* can be stored
> in
> > any column, but will be "handled like text" when the column is of the
> TEXT
> > type affinity, and also allow a collation method. Honestly, you can put
> the
> > text of the complete volumes of Don Quixotic in a single TEXT field in a
> > database.
> >
> > * - As long as the string memory doesn't exceed 4GB, the char-size of
> which
> > may differ based on encoding, and if you add the quoted string to the
> query
> > text directly (as opposed to binding it via the API), then the maximum
> size
> > will be governed by the max character length of the query input, which
> can
> > be configured via the API also. You can add the string as memory bytes
> as a
> > BLOB to go bigger, but then you lose collation capability I'll stop
> > there, it's probably more unnecessary info than you wished for already.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Does SQLite use field definitions?

[David Raymond]

An integer primary key is the only field that absolutely must be the type 
declared. It basically says to use the internal rowid as a visible field.

Currently it does start at 1 and any newly inserted record where the id is not 
explicitly specified will get an id of 1 more than whatever's the current 
highest at the time. Though I believe that's not a standard so much as a 
"here's how we decided to implement it for now" thing, and thus shouldn't be 
relied upon.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thursday, February 02, 2017 9:12 AM
To: SQLite mailing list
Subject: Re: [sqlite] Does SQLite use field definitions?

What about the INTEGER PRIMARY KEY (defined in my first email post)?
Does this field start at 1, and then auto increment for each new line?

On Thu, Feb 2, 2017 at 5:02 AM, R Smith <rsm...@rsweb.co.za> wrote:
>
>
> On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:
>>
>> I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
>> handle char strings longer than 256.
>>
>> However, I don't see any complaints by SQLite when I use MEMO.
>
>
> SQLite will never complain about anything you use as a type. What you should
> to use is TEXT. In SQLite any string value, of any length* can be stored in
> any column, but will be "handled like text" when the column is of the TEXT
> type affinity, and also allow a collation method. Honestly, you can put the
> text of the complete volumes of Don Quixotic in a single TEXT field in a
> database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of which
> may differ based on encoding, and if you add the quoted string to the query
> text directly (as opposed to binding it via the API), then the maximum size
> will be governed by the max character length of the query input, which can
> be configured via the API also. You can add the string as memory bytes as a
> BLOB to go bigger, but then you lose collation capability I'll stop
> there, it's probably more unnecessary info than you wished for already.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

What about the INTEGER PRIMARY KEY (defined in my first email post)?
Does this field start at 1, and then auto increment for each new line?

On Thu, Feb 2, 2017 at 5:02 AM, R Smith  wrote:
>
>
> On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:
>>
>> I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
>> handle char strings longer than 256.
>>
>> However, I don't see any complaints by SQLite when I use MEMO.
>
>
> SQLite will never complain about anything you use as a type. What you should
> to use is TEXT. In SQLite any string value, of any length* can be stored in
> any column, but will be "handled like text" when the column is of the TEXT
> type affinity, and also allow a collation method. Honestly, you can put the
> text of the complete volumes of Don Quixotic in a single TEXT field in a
> database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of which
> may differ based on encoding, and if you add the quoted string to the query
> text directly (as opposed to binding it via the API), then the maximum size
> will be governed by the max character length of the query input, which can
> be configured via the API also. You can add the string as memory bytes as a
> BLOB to go bigger, but then you lose collation capability I'll stop
> there, it's probably more unnecessary info than you wished for already.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[R Smith]

On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote:

I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
handle char strings longer than 256.

However, I don't see any complaints by SQLite when I use MEMO.


SQLite will never complain about anything you use as a type. What you 
should to use is TEXT. In SQLite any string value, of any length* can be 
stored in any column, but will be "handled like text" when the column is 
of the TEXT type affinity, and also allow a collation method. Honestly, 
you can put the text of the complete volumes of Don Quixotic in a single 
TEXT field in a database.


* - As long as the string memory doesn't exceed 4GB, the char-size of 
which may differ based on encoding, and if you add the quoted string to 
the query text directly (as opposed to binding it via the API), then the 
maximum size will be governed by the max character length of the query 
input, which can be configured via the API also. You can add the string 
as memory bytes as a BLOB to go bigger, but then you lose collation 
capability I'll stop there, it's probably more unnecessary info than 
you wished for already.


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

Re: [sqlite] Does SQLite use field definitions?

[Donald Griggs]

The default maximum string length is one billion (10 ** 9).   You can
configure the maximum up to about twice that.
https://www.sqlite.org/limits.html

If column affinity matters in your application, you may want to declare
your column as TEXT or maybe CLOB (identical effect.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Jens Alfke]

> On Feb 1, 2017, at 7:41 AM, Clyde Eisenbeis  wrote:
> 
> However, I don't see any complaints by SQLite when I use MEMO.

SQLite actually ignores the column data types completely in a table spec. You 
can store any type of data in any column of any table. (In other words, SQLite 
data typing works like JavaScript, not like C :)

SQLite has no limitations on the lengths of strings or blobs, so don’t worry 
about field widths.

See the section “Manifest Typing” here:
https://www.sqlite.org/different.html 


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

Re: [sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

I don't see MEMO listed.  I needed to use MEMO in Microsoft Access to
handle char strings longer than 256.

However, I don't see any complaints by SQLite when I use MEMO.

On Tue, Jan 31, 2017 at 10:33 AM, Hick Gunter <h...@scigames.at> wrote:
> Yes. See http://sqlite.org/lang_createtable.html for details. I also suggest 
> you look at http://sqlite.org/datatype3.html too
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Clyde Eisenbeis
> Gesendet: Dienstag, 31. Jänner 2017 17:07
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: [sqlite] Does SQLite use field definitions?
>
> In the past, when using Access as a database, I have specified field 
> definitions.  These field definitions have been used when creating a table.
>
> public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo MEMO, 
> fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER PRIMARY KEY ";
>
> Does SQLite (System.Data.SQLite) use field definitions too?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[Hick Gunter]

Yes. See http://sqlite.org/lang_createtable.html for details. I also suggest 
you look at http://sqlite.org/datatype3.html too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clyde Eisenbeis
Gesendet: Dienstag, 31. Jänner 2017 17:07
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] Does SQLite use field definitions?

In the past, when using Access as a database, I have specified field 
definitions.  These field definitions have been used when creating a table.

public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo MEMO, 
fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER PRIMARY KEY ";

Does SQLite (System.Data.SQLite) use field definitions too?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

In the past, when using Access as a database, I have specified field
definitions.  These field definitions have been used when creating a
table.

public const string stFIELD_DEFINITIONS = " fstPriority TEXT, fstInfo
MEMO, fstDateCreated TEXT, fstDateModified TEXT, fiKeyID INTEGER
PRIMARY KEY ";

Does SQLite (System.Data.SQLite) use field definitions too?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users