Re: [sqlite] Bug Report: DateTime incorrect for Windows
I would like to propose this version of winCurrentTime() which generates the
same results as the current implementation but much simpler:
int winCurrentTime(sqlite3_vfs *pVfs, double *prNow) {
FILETIME ft;
ULARGE_INTEGER ft64;
#if SQLITE_OS_WINCE
SYSTEMTIME time;
GetSystemTime(&time);
/* if SystemTimeToFileTime() fails, it returns zero. */
if (!SystemTimeToFileTime(&time,&ft)) {
return 1;
}
#else
GetSystemTimeAsFileTime(&ft);
#endif
UNUSED_PARAMETER(pVfs);
/* FILETIME structure is an unsigned 64-bit value representing the number of
100-nanosecond intervals since January 1, 1601 (= JD 2305813.5).
1000*86400=(2^14)*(5^9)*(3^3)
will need to add 2305813.5 to get Julian day.
Will use 2^13 so that (2*2305813.5)=4611627
can be added in before float computation.
Summary:
Divide by 2^13 i.e. shift right 2^13
Add Julian offset 4611627*(5^9)*(3^3) = 0x0DD2E611DD10D
Divide by remaining factors 2*(5^9)*(3^3) = 105468750
*/
ft64.u.LowPart = ft.dwLowDateTime;
ft64.u.HighPart= ft.dwHighDateTime;
*prNow = ((ft64.QuadPart>>13) + 0x0DD2E611DD10DL) / 105468750.0L;
return 0;
}
4eanlss
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report: DateTime incorrect for Windows
Shane Harrelson writes: > I'd be worried that other 64 bit calculations are going wrong as well. > > Can you verify what type sqlite3_int64 is being set to in your environment? > > Also can you try the following (note the L suffix on the constants): > > static const sqlite3_int64 max32BitValue = > (sqlite3_int64)20L + (sqlite3_int64)20L + > (sqlite3_int64)294967296L; > Results not as I expected. Adding L didn't change anything. However, removing the casts leaving just this: 20L + 20L + 294967296L; results in compiler error: Numeric constant too large in function winCurrentTime Then I tried this: 2L*20L + 294967296L; No compiler error but still left with a result of zero. Remaining options which do work (with or without the L) are: Amazingly: 4294967296L; or this: 0x01L; or this: (sqlite3_int64)1<<32; // but only with typecast 4eanlss ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no such column: UserName - Why?
Thank you, it works now!
Jay A. Kreibich-2 wrote:
>
> On Fri, Feb 26, 2010 at 07:21:08PM -0500, Igor Tandetnik scratched on the
> wall:
>> senglory wrote:
>
>> > insert into TB_GroupCalendar(CalendarName) VALUES('DEFAULT CALENDAR
>> > FOR ' + UserName) ;
>>
>> What does UserName refer to here? If you want to use the value from the
>> just-inserted row, that would be new.UserName
>
> And it would be ||, not +.
>
>-j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs. We have
> a protractor." "I'll go home and see if I can scrounge up a ruler
> and a piece of string." --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://old.nabble.com/no-such-column%3A-UserName---Why--tp27724824p27725263.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] no such column: UserName - Why?
On Fri, Feb 26, 2010 at 07:21:08PM -0500, Igor Tandetnik scratched on the wall:
> senglory wrote:
> > insert into TB_GroupCalendar(CalendarName) VALUES('DEFAULT CALENDAR
> > FOR ' + UserName) ;
>
> What does UserName refer to here? If you want to use the value from the
> just-inserted row, that would be new.UserName
And it would be ||, not +.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no such column: UserName - Why?
senglory wrote:
> tbl definition
>
> CREATE TABLE [TB_User] (
> [ObjectID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> [UserName] VARCHAR(30) UNIQUE NOT NULL,
> [IsActive] bit DEFAULT '1' NULL
> );
>
>
> trigger definition:
>
> CREATE TRIGGER [ON_TBL_TB_USER_INSERT_ADD_DEFAULT_CALENDAR]
> AFTER INSERT ON [TB_User]
> FOR EACH ROW
> BEGIN
>
> insert into TB_GroupCalendar(CalendarName) VALUES('DEFAULT CALENDAR
> FOR ' + UserName) ;
What does UserName refer to here? If you want to use the value from the
just-inserted row, that would be new.UserName
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] no such column: UserName - Why?
tbl definition
CREATE TABLE [TB_User] (
[ObjectID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[UserName] VARCHAR(30) UNIQUE NOT NULL,
[IsActive] bit DEFAULT '1' NULL
);
trigger definition:
CREATE TRIGGER [ON_TBL_TB_USER_INSERT_ADD_DEFAULT_CALENDAR]
AFTER INSERT ON [TB_User]
FOR EACH ROW
BEGIN
insert into TB_GroupCalendar(CalendarName) VALUES('DEFAULT CALENDAR FOR '
+ UserName) ;
insert into TB_User_GroupCalendar(UserID,GroupCalendarID) VALUES(ObjectID,
last_insert_rowid() ) ;
END;
When I try to execute this:
insert into [TB_User](UserName) values('QQ')
I get error " no such column: UserName ". Why? And how to write it
correctly?
--
View this message in context:
http://old.nabble.com/no-such-column%3A-UserName---Why--tp27724824p27724824.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] Prepared statements and locking
>> > So is such behavior documented and can be used or should I still avoid >> > keeping such "live" statements in db-shared enviroment? >> >> It's documented that sqlite3_finalize AND sqlite3_reset release all >> locks acquired by the statement. So after resetting the statement you >> can keep it as long as you want, it won't block out any other process >> from accessing the database. >> >> > Thanks, Pavel, but could not find this in the docs, only found this topic > was discussed previously when drh answered: > >> You should be in the habit of calling sqlite3_reset() on each query >> as soon as that query finishes. Otherwise the query *might* leave a >> read-lock on the database file and thus prevent subsequent write >> operations for working correctly. > > Just hope this information will be included in the article about > sqlite3_reset some day, Some good timing here with this info. Ive just changed all my code to reset after query, instead of reset before bind & query. This info should definitely be mentioned in the reset doc's. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] structure question
On 26 Feb 2010, at 9:25pm, Francisco Azevedo wrote: > I started with a auxiliar table with same columns, > then, before edition i clean auxiliar data and copy from original: > > delete from _table; insert into _table select * from table; > > now, user can edit table (inserts, deletes, updates) and then, if we > want to apply changes i copy back: > > delete from table; insert into table select * from _table; > > if we don't want to preserve, i don't need to do anything because on new > edit, auxiliar table will be cleaned again. > > This works but it's not efficient at all (imagine table with 1.000.000 > records). My question is, what is the best approach to do such thing? You only have that solution and one other: remember the table as it was at a certain point (call it a backup), and every command given since then. If you want to undo a command, restore the backup version of the table then replay all the commands up to that one you want to undo. The 'certain point' mentioned above, of course, might be the original creation of the table: back to the time when it had no rows. Those two systems are really your only options which will correctly cope with any kind of SQL commands. If your application has reduced possibilities, and you can be sure that only certain types of SQL commands will be given, then you may have other options. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] structure question
Hi all, I want to create a "publish/undo system" for some tables but i don't know what is the best approach to do it. Imagine i have a table with columns id (auto-inc), data (text) then i want to edit table data (eg: create 2 new rows now, delete one tomorrow, update 3 rows tomorow too) and then decide if i want to preserve that modification or reverse it to the state it was before start that modifications. How can i do this? I started with a auxiliar table with same columns, then, before edition i clean auxiliar data and copy from original: delete from _table; insert into _table select * from table; now, user can edit table (inserts, deletes, updates) and then, if we want to apply changes i copy back: delete from table; insert into table select * from _table; if we don't want to preserve, i don't need to do anything because on new edit, auxiliar table will be cleaned again. This works but it's not efficient at all (imagine table with 1.000.000 records). My question is, what is the best approach to do such thing? best regards and thanks for your time, Francisco A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report: DateTime incorrect for Windows
On Fri, Feb 26, 2010 at 1:49 AM, 4eanlss <4ean...@engineer.com> wrote: > > Shane, > > I apologize for my quick snap response. > I have debugged the code and have identified what is happening. > The calculation in my environment results in 0 (zero) for this: > static const sqlite3_int64 max32BitValue = > (sqlite3_int64)20 + (sqlite3_int64)20 + > (sqlite3_int64)294967296; > > Which breaks all of the following datetime calculations. > When compiled with the type sqlite3_uint64 then it works correctly. > > I am working on a much simpler winCurrentTime solution but I'm stilling > creating all test cases to verify the algorithm before posting. > > 4eanlss > > I'd be worried that other 64 bit calculations are going wrong as well. Can you verify what type sqlite3_int64 is being set to in your environment? Also can you try the following (note the L suffix on the constants): static const sqlite3_int64 max32BitValue = (sqlite3_int64)20L + (sqlite3_int64)20L + (sqlite3_int64)294967296L; -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupt first sqlite3_step in FTS3 query
On Sat, Feb 20, 2010 at 10:09 PM, Simon wrote:
> I also can stop the process between any two calls to sqlite3_step, my issue
> is with the first one that (to me) does not seem interruptible.
>
> I just tried adding a call to CHECK_FOR_INTERRUPT just after "for(pc=p->pc;
> rc==SQLITE_OK; pc++){" (~line 52715 in the ammalgamation) in sqlite3VdbeExec.
>
> It now behaves to my satisfaction (I can interrupt my query in no time)
>
> So I guess my question now becomes: is it safe?
Can anyone answer this? I've just implemented something similar to
Simon's approach (but constrained to two threads), and trying to
interrupt FTS3 queries can bring my entire development machine to its
knees. So I suspect the code is not safe even as it stands!
Thanks,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] port sqlite to a platform without lock/unlock feature filesystem
thanks for you reply. in my case, it is a multi-client platform, but for one data connection, only one process/client will change it. Superway Simon Slavin-3 wrote: > > > On 26 Feb 2010, at 10:41am, Superway wrote: > >> I am porting sqlite to a platform, where the filesystem do not support >> lock/unlock mechanism. >> does it possible to work on this type platform? if so, how to do it? > > Do you need the results of locking ? In other words, is there any change > that two users or two processes will be trying to access a database at > once ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/port-sqlite-to-a-platform-without-lock-unlock-feature-filesystem-tp27716749p27717371.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] port sqlite to a platform without lock/unlock feature filesystem
On 26 Feb 2010, at 10:41am, Superway wrote: > I am porting sqlite to a platform, where the filesystem do not support > lock/unlock mechanism. > does it possible to work on this type platform? if so, how to do it? Do you need the results of locking ? In other words, is there any change that two users or two processes will be trying to access a database at once ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] port sqlite to a platform without lock/unlock feature filesystem
Hi. I am porting sqlite to a platform, where the filesystem do not support lock/unlock mechanism. does it possible to work on this type platform? if so, how to do it? thanks in advance Superway -- View this message in context: http://old.nabble.com/port-sqlite-to-a-platform-without-lock-unlock-feature-filesystem-tp27716749p27716749.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] Diagrams and ROLLBACK TO
> "Instead of cancelling the transaction, the ROLLBACK TO command > restarts the transaction again at the beginning. All intervening > SAVEPOINTs are cancelled, however." > > Say we have the following events: > > begin > insert > update > savepoint A > update > savepoint B > insert > delete > savepoint C > update > rollback to B > > I would expect the last statement to: > cancel the whole transaction (contrary to what's said) > then re-apply changes: > > begin > insert > update > savepoint A ??? > update That's correct. But savepoint B is opened as well. That's the point of the excerpt - that savepoint B remains open. The difference is: BEGIN; ROLLBACK; /* Database state restored, transaction is no longer open */ vs: SAVEPOINT outer; ROLLBACK TO outer; /* Database state restored, transaction is still open */ Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diagrams and ROLLBACK TO
Forget the previous post as it's probably wrong. I was misinterpreting the ROLLBACK TO discussion. In fact I was led to believe there were two forms: "rollback to" and "rollback to ". This is not the case. But then, I still have hard time understanding this part: "Instead of cancelling the transaction, the ROLLBACK TO command restarts the transaction again at the beginning. All intervening SAVEPOINTs are cancelled, however." Say we have the following events: begin insert update savepoint A update savepoint B insert delete savepoint C update rollback to B I would expect the last statement to: cancel the whole transaction (contrary to what's said) then re-apply changes: begin insert update savepoint A ??? update Is savepoint A considered an "intervening" savepoint? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements and locking
On Thu, Feb 25, 2010 at 6:53 PM, Pavel Ivanov wrote: > > So is such behavior documented and can be used or should I still avoid > > keeping such "live" statements in db-shared enviroment? > > It's documented that sqlite3_finalize AND sqlite3_reset release all > locks acquired by the statement. So after resetting the statement you > can keep it as long as you want, it won't block out any other process > from accessing the database. > > Thanks, Pavel, but could not find this in the docs, only found this topic was discussed previously when drh answered: > You should be in the habit of calling sqlite3_reset() on each query > as soon as that query finishes. Otherwise the query *might* leave a > read-lock on the database file and thus prevent subsequent write > operations for working correctly. Just hope this information will be included in the article about sqlite3_reset some day, Thanks Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Diagrams and ROLLBACK TO
The diagrams in the following pages of the documentation don't allow for the "ROLLBACK TO" construct: lang_savepoint.html and lang_transaction.html lang_savepoint.html also contains a 'tranaction' typo. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
