Quantcast

Does SQLite use field definitions?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]] Im Auftrag von Clyde Eisenbeis
Gesendet: Dienstag, 31. Jänner 2017 17:07
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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:[hidden email]] Im Auftrag von Clyde Eisenbeis
> Gesendet: Dienstag, 31. Jänner 2017 17:07
> An: SQLite mailing list <[hidden email]>
> 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
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Does SQLite use field definitions?

Jens Alfke-2

> On Feb 1, 2017, at 7:41 AM, Clyde Eisenbeis <[hidden email]> 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 <https://www.sqlite.org/different.html>

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Does SQLite use field definitions?

Donald Griggs
In reply to this post by Clyde Eisenbeis
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Does SQLite use field definitions?

R Smith
In reply to this post by Clyde Eisenbeis


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]] 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Does SQLite use field definitions?

Keith Medcalf
In reply to this post by Clyde Eisenbeis

On Thursday, 2 February, 2017 09:12, Clyde Eisenbeis <[hidden email]> 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=10000 where value='test';
sqlite> select * from x;
10000|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=10000 where value='test';
sqlite> select * from x;
10000|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=10000 where value='test';
sqlite> select * from x;
10000|test
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
10000|test
10001|again
sqlite>
 

> On Thu, Feb 2, 2017 at 5:02 AM, R Smith <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Does SQLite use field definitions?

Hick Gunter
In reply to this post by Clyde Eisenbeis
It will if you add AUTOINCREMENT

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Clyde Eisenbeis
Gesendet: Donnerstag, 02. Februar 2017 15:12
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...