Re: [sqlite] CURRENT_DATE Behavior
cmartin-2 wrote: > > I suspect it is quite common, e.g., web apps built on SQLServer backends > are quite likely to use UTC. At any rate, it is definitely a design > decision, if one expects that local times will always work in all > scenarios, but all means use local times. If design criteria change > unexpectedly in the future to the point where local times become > problematic, for reasons already mentioned, it will need a redesign. By > using UTC from the outset, it will be robust to all possible > datetime-related needs in the future. > Yeah, I appreciate the discussion of UTC. Although I don't fully understand how to convert UTC to local time on a Website, I do some Web development and so I can imagine scenarios where they may be appropriate. -- View this message in context: http://www.nabble.com/CURRENT_DATE-Behavior-tp20075044p20104213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] genfkey tool
> And now I'd like to check that, for all foreign keys, there is an > associated index. > Is it feasible? IIRC, it's tricky. In function populateTempTable(), there is code that checks that all parent keys are either the PRIMARY KEY of their table or subject to a UNIQUE constraint (have a UNIQUE index on them). Dan. > Regards. > ___ > 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] Foreign Key Constraints
On Oct 21, 2008, at 10:33 PM, Daniel Zingaro wrote: > > However, then, on the SQLite wiki, I found that there is a way to > implement FK constraints using triggers!: > http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > I'm wondering if anyone can help me understand the discrepancy here. > Are > recursive triggers required to implement some FK constraints that the > above solution cannot impose? > Does http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/ genfkey.README answer your questions? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign Key Constraints
Hi all, I've just joined this list hoping to learn more about SQLite. I'm using SQLite to teach the SQL portion of a college databases course. The one thing that concerns me a little is the lack of foreign key constraints. (I've been yapping about referential integrity for weeks and really want students to be able to enforce it =).) I have a copy of The Definitive Guide to SQLite, in which it states: ": Complete trigger support. There is some support for triggers but it is not complete. Missing features include ... and recursive triggers--triggers that trigger themselves. Recursive triggers are needed in order to implement foreign key constraints. :" However, then, on the SQLite wiki, I found that there is a way to implement FK constraints using triggers!: http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers I'm wondering if anyone can help me understand the discrepancy here. Are recursive triggers required to implement some FK constraints that the above solution cannot impose? Thanks for your help, Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Case Insensitive Comparison for Double Byte Characters
Hi Mike, Thanks for the information. Will be looking at it. Alex On Tue, Oct 21, 2008 at 6:47 PM, MikeW <[EMAIL PROTECTED]> wrote: > Alexander Yap <[EMAIL PROTECTED]> writes: > > > > > Hi All, > > I am new here and would like to learn on how to do Case Insensitive > > comparison of Double Byte Characters? > > > > Thanks in advance. > > > > Alex > > To begin with, read the useful advice here: > http://thread.gmane.org/gmane.comp.db.sqlite.general/41826/focus=41843 > > Regards, > MikeW > > > > > ___ > 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] CURRENT_DATE Behavior
On Tue, 21 Oct 2008, jonwood wrote: > mikewhit wrote: >> >> Using UTC in the DB stops you going mad when something happens >> on the DST changeover (localtime hours vanish, or happen twice), >> or you have systems running in or across different countries. >> >> It also means you can subtract two times and always get the right answer >> for elapsed time ! >> > > Yes, as pointed out in my original post, I've heard the arguments for using > UTC time. Folks are just going to need to trust me to make the decision as > to how appropriate this approach is for my particular project. > > BTW, MS SQL Server doesn't do anything like this as far as I know. Given > some of the comments here, one might wonder how those poor MS SQL Server > folks are able to get anything working at all. ;-) SQLServer folks use UTC datetimes, if their design requires it, by invoking the GetUTCDate() function: SQLServer: INSERT INTO foo (somedatetime) VALUES(GetUTCDate()); I suspect it is quite common, e.g., web apps built on SQLServer backends are quite likely to use UTC. At any rate, it is definitely a design decision, if one expects that local times will always work in all scenarios, but all means use local times. If design criteria change unexpectedly in the future to the point where local times become problematic, for reasons already mentioned, it will need a redesign. By using UTC from the outset, it will be robust to all possible datetime-related needs in the future. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting text data w/ embedded nulls from shell
Roger Binns rogerb-at-rogerbinns.com |Sqlite| wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Evan Burkitt wrote: >> Roger Binns rogerb-at-rogerbinns.com |Sqlite| wrote: >>> select cast(column as blob) >> I'm glad to know this syntax, but no dice. The nulls seem to be >> insurmountable barriers. > > That is due to the sqlite3_exec api used by the shell which returns null > terminated strings for every column and obviously there is no way to > tell the difference between the null being part of or terminating the > string. > > Anyway you can use hex to work around this: > >select hex(cast(column as blob)) ... Now that does work. Thanks. -evan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with INTEGER PRIMARY KEY ASC AUTOINCREMENT column
What does your insert statement look like? --Original Message-- From: Kenneth McDonald Sender: [EMAIL PROTECTED] To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Sent: Oct 20, 2008 10:23 Subject: [sqlite] problem with INTEGER PRIMARY KEY ASC AUTOINCREMENT column Basically, my database INSERT commands seem to still expect a value for this, even though my expectation is that it will be computed automatically, starting with (I assume) a default of 0 or 1. But I'm getting errors of the sort "table images has 12 columns but 11 values were supplied." Is there something else I need to do to have the primary key column computed automatically? Thanks, Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CURRENT_DATE Behavior
mikewhit wrote: > > Using UTC in the DB stops you going mad when something happens > on the DST changeover (localtime hours vanish, or happen twice), > or you have systems running in or across different countries. > > It also means you can subtract two times and always get the right answer > for elapsed time ! > Yes, as pointed out in my original post, I've heard the arguments for using UTC time. Folks are just going to need to trust me to make the decision as to how appropriate this approach is for my particular project. BTW, MS SQL Server doesn't do anything like this as far as I know. Given some of the comments here, one might wonder how those poor MS SQL Server folks are able to get anything working at all. ;-) -- View this message in context: http://www.nabble.com/CURRENT_DATE-Behavior-tp20075044p20099042.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with INTEGER PRIMARY KEY ASC AUTOINCREMENT column
On 10/20/08, Kenneth McDonald <[EMAIL PROTECTED]> wrote:
> Basically, my database INSERT commands seem to still expect a value
> for this, even though my expectation is that it will be computed
> automatically, starting with (I assume) a default of 0 or 1. But I'm
> getting errors of the sort "table images has 12 columns but 11 values
> were supplied." Is there something else I need to do to have the
> primary key column computed automatically?
1. You don't have to specify ASC AUTOINCREMENT. Just saying INTEGER
PRIMARY KEY does all that for you;
2. You do have to specify what cols you are updating. Given that you
have not provided your SQL as an example, consider the following --
CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO foo VALUES ('some string');
How should the database know where to stuff that 'some string'?
You could do any of the following and get your desired behavior --
INSERT INTO foo (b) VALUES ('some string');
or
INSERT INTO foo VALUES (null, 'some string');
or
INSERT INTO foo (a, b) VALUES (null, 'some string');
or
INSERT INTO foo (a, b) VALUES (10034, 'some string');
In the last case, 10034 would be inserted into col 'a', but if 10034
already existed, the program would croak. In all other cases, the
effect would the same as
UPDATE foo SET a = Max(a) + 1;
>
> Thanks,
> Ken
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with INTEGER PRIMARY KEY ASC AUTOINCREMENT column
some sql examples would help, With out that I will attempt an answer. 1. Could be that you are including your auto increment field in the list of fields. So just don't reference the field, it will be done automatically. 2. Or you could be msiing the field list all together and just supplying values. if so I would add the field list on the insert. On Mon, Oct 20, 2008 at 10:23 AM, Kenneth McDonald <[EMAIL PROTECTED]> wrote: > Basically, my database INSERT commands seem to still expect a value > for this, even though my expectation is that it will be computed > automatically, starting with (I assume) a default of 0 or 1. But I'm > getting errors of the sort "table images has 12 columns but 11 values > were supplied." Is there something else I need to do to have the > primary key column computed automatically? > > Thanks, > Ken > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] genfkey tool
Hello,
I discovered the great 'genfkey' tool with the last release (3.6.4).
And I tried to customize it such as the output is not "CREATE TRIGGER"
statements but
"SELECT" statements.
Indeed, my SQLite database is populated from a MySQL dump and is not modified
afterwards.
The order of record insertion prevents me from using triggers.
So I replaced the original zSql string with:
const char *zSql =
"SELECT multireplace('"
"SELECT DISTINCT /rkey_list/, ''/tbl/(/rkey_list/)'' FROM /tbl/ tbl WHERE
\n"
"/key_notnull/ AND NOT EXISTS (SELECT 1 FROM /ref/ ref WHERE
/cond1/);\n"
"'"
", '/tbl/',from_tbl"
", '/ref/',to_tbl"
", '/key_notnull/', sj('tbl.' || from_col || ' IS NOT NULL', ' AND ')"
", '/fkey_list/', sj(to_col, ', ')"
", '/rkey_list/', sj(from_col, ', ')"
", '/cond1/', sj(multireplace('tbl./from/ == ref./to/'"
", '/from/', from_col"
", '/to/', to_col"
"), ' AND ')"
") FROM temp.fkey "
"GROUP BY from_tbl, fkid"
;
And now I'd like to check that, for all foreign keys, there is an associated
index.
Is it feasible?
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem with INTEGER PRIMARY KEY ASC AUTOINCREMENT column
Basically, my database INSERT commands seem to still expect a value for this, even though my expectation is that it will be computed automatically, starting with (I assume) a default of 0 or 1. But I'm getting errors of the sort "table images has 12 columns but 11 values were supplied." Is there something else I need to do to have the primary key column computed automatically? Thanks, Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting text data w/ embedded nulls from shell
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Evan Burkitt wrote: > Roger Binns rogerb-at-rogerbinns.com |Sqlite| wrote: >> select cast(column as blob) > > I'm glad to know this syntax, but no dice. The nulls seem to be > insurmountable barriers. That is due to the sqlite3_exec api used by the shell which returns null terminated strings for every column and obviously there is no way to tell the difference between the null being part of or terminating the string. Anyway you can use hex to work around this: select hex(cast(column as blob)) ... (The casting doesn't actually make any difference but at least shows your intentions). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkj+Kf0ACgkQmOOfHg372QQg2wCcC2ngZk6u8gU+h4EiU3K7IzoQ MuYAoJ/EspfvUNP9B7fHxaouaLXe5yqy =htIn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CURRENT_DATE Behavior
jonwood <[EMAIL PROTECTED]> writes: > > > Greetings, > > Okay, I understand that the designer of SQLite felt it was important that > fields with a default value of CURRENT_DATE should be initialized to the > current date in a DIFFERENT time zone. Setting aside for now that I've read > all the reasons for this and am very much against the decision, I have the > following question. > > Is there ANY way to override this behavior? Or must I simply initialize all > such fields explicity if I would like to set it to the current date in my > particular time zone? > > Thanks. Warning ! SQLite also uses the Common/Christian Era calendar, and not the Islamic or Jewish one ! ;-) Using UTC in the DB stops you going mad when something happens on the DST changeover (localtime hours vanish, or happen twice), or you have systems running in or across different countries. It also means you can subtract two times and always get the right answer for elapsed time ! Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting text data w/ embedded nulls from shell
Roger Binns rogerb-at-rogerbinns.com |Sqlite| wrote: > > select cast(column as blob) I'm glad to know this syntax, but no dice. The nulls seem to be insurmountable barriers. > > You have the full source to the shell and can change the text printing > routine to do whatever you want Yeah, I know, but that's, like, work. :) What I want is a tool I (and tech support) can use to at least view, and possibly modify, the data outside my application. I can write a simple dump program but having the full capabilities of the shell would be much preferable. Hacking the source actually sounds like a good plan. Thanks for your help. -evan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does a default value for fieldtype "time" look ?
"PEL" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> "time" time NOT NULL DEFAULT HH:MM
What's that supposed to achieve? If you want current time in HH:MM
format, make it
DEFAULT (strftime('%H:%M', 'now'))
If you want, say, midnight in HH:MM format, make it
DEFAULT '00:00'
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does a default value for fieldtype "time" look ?
"time" time NOT NULL DEFAULT HH:MM gets me that error: SQL Error: near ":MM": syntax error found this: 1. -MM-DD 2. -MM-DD HH:MM 3. -MM-DD HH:MM:SS 4. -MM-DD HH:MM:SS.SSS 5. -MM-DDTHH:MM 6. -MM-DDTHH:MM:SS 7. -MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. . so I used HH:MM or hh:mm in my sqlite database designer but it doesnt compile :/ -- View this message in context: http://www.nabble.com/How-does-a-default-value-for-fieldtype-%22time%22-look---tp20086163p20089380.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CURRENT_DATE Behavior
In an effort to share some hard-won insight:
I used to store local times in the database, but it's a bad idea and I got
bit. Think about what gets stored: A number of seconds/nanoseconds/whatever
from some time in the past (Jan 1, 1970 for example). When daylight savings
arrives, all of your stored values are now off by an hour (and if they're
around midnight, they're on the wrong date too). And times you save now
will similarly be off when you leave day light savings. It's much better to
store UTC and then convert to local time as needed.
I (and some customers) went through a lot of pain as I switched everything
to UTC -- life has been good since then.
Doug
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:sqlite-users-
> [EMAIL PROTECTED] On Behalf Of jonwood
> Sent: Monday, October 20, 2008 9:03 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] CURRENT_DATE Behavior
>
>
>
> D. Richard Hipp wrote:
> >
> > CREATE TABLE whatever(
> >
> > timestamp DATE DEFAULT (datetime('now','localtime')),
> > ...
> > );
> >
>
> Really? I can do that? Great!
>
> Thanks!
> --
> View this message in context: http://www.nabble.com/CURRENT_DATE-
> Behavior-tp20075044p20082173.html
> Sent from the SQLite mailing list archive at Nabble.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] How does a default value for fieldtype "time" look ?
"PEL" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I just created in sqlite database a field called time with fieldtype > time. What should i set the time as default value maybe 00:00 ? > doesnt work... http://article.gmane.org/gmane.comp.db.sqlite.general/38853 Figure out how you want to represent the time, and the default value will become obvious. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Case Insensitive Comparison for Double Byte Characters
Alexander Yap <[EMAIL PROTECTED]> writes: > > Hi All, > I am new here and would like to learn on how to do Case Insensitive > comparison of Double Byte Characters? > > Thanks in advance. > > Alex To begin with, read the useful advice here: http://thread.gmane.org/gmane.comp.db.sqlite.general/41826/focus=41843 Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
John Belli <[EMAIL PROTECTED]> writes:
> GIVEN: db1 is the open handle of the first database, and db2 is the
> open handle of the second database
>
> WANTED: attach database open as db1 to db2 to do an atomic update
> * * * WARNING: untested code ahead! * * *
> char **table;
> sqlite3_get_table(db1, "PRAGMA database_list", , NULL, NULL,
> NULL);
> char *tmp = sqlite3_mprintf("ATTACH %q AS a", table[5]);
> sqlite3_free_table(table);
> sqlite3_exec(db2, tmp, NULL, NULL, NULL);
> sqlite3_free(tmp);
>...do your stuff here...
> sqlite3_exec(db2, "DETACH a", NULL, NULL, NULL);
>
> There should be a bunch of error checking in there. This is left as an
> exercise for the reader.
>
> This will not work if db1 is a memory db. In that case, you would have
> to do it the other way around. If both are memory dbs, then you're out
> of luck.
>
> JAB
Thanks - but it looks implementation-dependent, so better not use
in production code since it would be 'fragile' wrt SQLite changes.
Will accept the small risk of loss of failure due to power-off
between separate COMMITs
BEGIN; ... UPDATE db1
BEGIN; ... UPDATE db2
... all well so far ...
COMMIT db1;
... still OK ... hope no power fail here !
COMMIT db2;
... made it ...
Regards,
MikeW
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How does a default value for fieldtype "time" look ?
Hello, I just created in sqlite database a field called time with fieldtype time. What should i set the time as default value maybe 00:00 ? doesnt work... -- View this message in context: http://www.nabble.com/How-does-a-default-value-for-fieldtype-%22time%22-look---tp20086163p20086163.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
