[sqlite] Create Table oddity
I've been talking with Bogdan about a change to SQLite Expert and ran across something I did NOT expect. I have this schema: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT AppID); When I insert a new row, I get the literal string AppID as a default value for that new row. I was expecting a number or null, or something. Not a string. Is this intended behavior? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
I've been talking with Bogdan about a change to SQLite Expert and ran across something I did NOT expect. I have this schema: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT AppID); When I insert a new row, I get the literal string AppID as a default value for that new row. I was expecting a number or null, or something. Not a string. Is this intended behavior? Looks like another case where SQLite tries its best to avoid balking at a statement, just like when it tries to auto-correct single vs double quotes. Try using square brakets or double quotes around AppID: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT [AppID]); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
This is a feature (or a bug, depending on your view). A column default must be
a constant, so your reference to AppID is translated to the string 'AppID'
because it cannot be a column name since that would not be a constant. Perhaps
your intent to do the following:
CREATE TABLE tApplicationPaths (
AppID INTEGER PRIMARY KEY,
ApplicationName CHAR,
ApplicationMonitorPath CHAR,
SearchSubDirs BOOL DEFAULT 1,
SearchMask CHAR);
CREATE TRIGGER tApplicationPathsInsert
AFTER INSERT ON tApplicationPaths
WHEN new.SearchMask is NULL
BEGIN
UPDATE tApplicationPaths SET SearchMask = CAST(AppID as CHAR) WHERE AppID =
new.AppID;
END;
insert into tApplicationPaths (ApplicationName, ApplicationMonitorPath) values
('Name', 'Monitor');
select * from tApplicationPaths;
1|Name|Monitor|1|1
If you use this format in your CREATE TABLE you get a meaningful error message:
CREATE TABLE tApplicationPaths (
AppID INTEGER PRIMARY KEY,
ApplicationName CHAR,
ApplicationMonitorPath CHAR,
SearchSubDirs BOOL DEFAULT 1,
SearchMask CHAR DEFAULT ([AppID]));
Error: default value of column [SearchMask] is not constant
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Stephen Chrzanowski
Sent: Sunday, 19 May, 2013 18:05
To: General Discussion of SQLite Database
Subject: [sqlite] Create Table oddity
I've been talking with Bogdan about a change to SQLite Expert and ran
across something I did NOT expect.
I have this schema:
CREATE TABLE [tApplicationPaths] (
[AppID] INTEGER PRIMARY KEY,
[ApplicationName] CHAR,
[ApplicationMonitorPath] CHAR,
[SearchSubDirs] BOOL DEFAULT 1,
[SearchMask] CHAR DEFAULT AppID);
When I insert a new row, I get the literal string AppID as a default
value for that new row. I was expecting a number or null, or something.
Not a string. Is this intended behavior?
___
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] Create Table oddity
While Keith is closer to the right explanation than I was, there are a couple of points: default does not need a constant: a function call is valid as well, e.g. date() The syntax: CREATE TABLE tApplicationPaths ( AppID INTEGER PRIMARY KEY, ApplicationName CHAR, ApplicationMonitorPath CHAR, SearchSubDirs BOOL DEFAULT 1, SearchMask CHAR DEFAULT [AppID]); doesn't deliver the expected default value to SearchMask (for reason Keith exposed), but it doesn't bark either and simply insert 0 as default value AFAICT. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
While Keith is closer to the right explanation than I was, there are a couple of points: default does not need a constant: a function call is valid as well, e.g. date() It can be a constant expression if surrounded by brackets. This does not mean that the value of the expression is a constant, rather that the expression is constant. Create table a (id integer primary key, date text default (date())); There is, however, no way to refer to a column by name or embed a correlated subquery as a default ... The syntax: CREATE TABLE tApplicationPaths ( AppID INTEGER PRIMARY KEY, ApplicationName CHAR, ApplicationMonitorPath CHAR, SearchSubDirs BOOL DEFAULT 1, SearchMask CHAR DEFAULT [AppID]); doesn't deliver the expected default value to SearchMask (for reason Keith exposed), but it doesn't bark either and simply insert 0 as default value AFAICT. For me this inserts the text 'AppID' ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
For me this inserts the text 'AppID' ... Sorry my bad, I tried a dummy table but made the type integer not char. So it was displaying 0 but hold text indeed. It's too late here for me to post anything, must be age! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
On Sun, May 19, 2013 at 06:05:05PM -0400, Stephen Chrzanowski scratched on the wall: I've been talking with Bogdan about a change to SQLite Expert and ran across something I did NOT expect. I have this schema: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT AppID); When I insert a new row, I get the literal string AppID as a default value for that new row. I was expecting a number or null, or something. Not a string. Is this intended behavior? As shown here: http://sqlite.org/syntaxdiagrams.html#column-constraint bare words are considered literal values. If you want to reference a column, you need to use parens to create an expression. SQLite correctly identifies this as invalid: sqlite CREATE TABLE [tApplicationPaths] ( ...[AppID] INTEGER PRIMARY KEY, ...[ApplicationName] CHAR, ...[ApplicationMonitorPath] CHAR, ...[SearchSubDirs] BOOL DEFAULT 1, ...[SearchMask] CHAR DEFAULT (AppID) ); Error: default value of column [SearchMask] is not constant -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
Maybe this will help someone else later on down the road when dealing with defaults and how the library handles them. My intention was to see about changing the behavior of SQLite Expert when it came to generating the SQL statement to create a new table. Currently, Expert takes whatever is in the default form field and drops it in with little to no mutation of what I entered. Bogdan raises a good point that it should be up to me to explicitly decide what precisely the field should contain as a default and as what type the application should expect. What SearchMask is intended for in my application is a colon delimited file mask. *.txt:*.exe:TextFile_*.txt, etc. (I picked colon because it isn't a valid character in a file name in Windows, while coma and semicolon is, and I'm not concerned with drives as 'ApplicationMonitorPath' handles that). Needless to say, when I went to enter an unquoted * in Expert as a default value, it threw an exception when I went to apply the changes because of the unquoted star. So I thought that regardless of whether or not the library cares about field types, *TO ME* if I specify a field as a char type, maybe Expert could take a jump at logic and say You know, he's decided this is a character field, and he put something in the default field. Maybe I should make that a literal entry since the library defines a string default with single quotes. Needless to say, Bogdan raises the excellent point that the I-D-TEN-T (My words, not his) designing the table should specifically dictate what the default should be. He's right. Expert shouldn't, make assumptions as to what needs to be put in place. The four schemas I was looking at were as follows, and what made me raise my brow: CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT *); CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT '*'); CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT 8*8); CREATE TABLE [tApplicationPaths] ( [AppID] INTEGER PRIMARY KEY, [ApplicationName] CHAR, [ApplicationMonitorPath] CHAR, [SearchSubDirs] BOOL DEFAULT 1, [SearchMask] CHAR DEFAULT '8*8'); As far as the library is concerned, the first schema threw an exception, which is 100% acceptable. The second works as I desire. The third one is a calculable value, so when I insert a new row with nothing defined in SearchMask it returns 64, not 8*8 even though the schema is defined with 8*8 and shows in the master table as such. I wasn't totally surprised by that behavior. The fourth entry works the same as the second. Then, as I was typing the email this AM, I tried adding just a series of characters, then I got the new error I'd never seen about constants. So tried a field name, and it surprised me that it accepted the new schema, but got a wildly different answer to what I was expecting, which is why I came here since it looked like this issue was the library's intention of what should be going on more so than something wrong with Expert. Bogdan; if you're reading. Thanks for sticking to your guns. I appreciate SQLite Expert tremendously. :] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Table oddity
On 20 May 2013, at 4:33am, Stephen Chrzanowski pontia...@gmail.com wrote:
CREATE TABLE [tApplicationPaths] (
[AppID] INTEGER PRIMARY KEY,
[ApplicationName] CHAR,
[ApplicationMonitorPath] CHAR,
[SearchSubDirs] BOOL DEFAULT 1,
[SearchMask] CHAR DEFAULT 8*8);
Hmm. I wonder if there's a difference between
DEFAULT date('now')
and
DEFAULT (date('now'))
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
