Re: [sqlite] New SQLite Forum established - this mailing list is deprecated
> Please consider subscribing to the new Forum. The intent is that the forum > will eventually replace this mailing list. Having just had a go at subscribing, I screwed up and mistyped my email address. Is there any way for me to change it? Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maintaing a Last updated timestamp column using triggers
>Thanks Andy >> In addition, the role of the "when" clause is unclear. Is it necessary? >> >> I don't think it is. I have a very similar trigger which I've been >> using for several years And it doesn't have the where... >> >> CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports FOR EACH ROW >> BEGIN >>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ; >> END >> >What stops the >UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ; from also >triggering the AFTER UPDATE ON recursively? >Perhaps a pragma or inSQLite are triggers non-recursive by default? It looks like this answers your question... https://stackoverflow.com/questions/21223434/do-sqlite-triggers-trigger-other-triggers Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maintaing a Last updated timestamp column using triggers
>On 12/03/2020 08:47, David Blake wrote:
>> I'm looking for an easy way to maintain a last updated column for each
>> record in several tables and considering if using a triggers is viable.
>>
>> I thought that defining a trigger like this on each table would work
>>
>> CREATE TRIGGER my_update_trigger BEFORE UPDATE ON My_table
>>FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>>BEGIN
>>UPDATE song SET lastupdated = DATETIME('now') WHERE id = NEW.id
>>END
...
> In addition, the role of the "when" clause is unclear. Is it necessary?
I don't think it is. I have a very similar trigger which I've been using for
several years
And it doesn't have the where...
CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports
FOR EACH ROW
BEGIN
UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
END
Andy
**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If
you are not the addressee of this message, you may not copy, use or deliver
this message to anyone. In such event, you should destroy the message and
kindly notify the sender by reply e-mail. It is understood that opinions or
conclusions that do not relate to the official business of the company are
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> > > This is what I would call "forward compatibility": You expect an old > > application > > > to be able to read file formats of a future version. Do you have an > > example > > > where there is really required? > > > > I have an Android app that lets you share the database between users. The > > app will run on a variety of versions of Android with a similar variety > > of versions of sqlite. Currently any version can read the database from > > any other > > version regardless of whether it is old reading new or vice versa. > > > > Then if you want to retain that, just don't use new features that might > break fwd-compatibility. Yes, I am aware of this. I was just providing a "real" example to answer the question posed by the OP. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> This is what I would call "forward compatibility": You expect an old > application > to be able to read file formats of a future version. Do you have an example > where there is really required? I have an Android app that lets you share the database between users. The app will run on a variety of versions of Android with a similar variety of versions of sqlite. Currently any version can read the database from any other version regardless of whether it is old reading new or vice versa. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round function inconsistent
> I do not know what the underlying representation for floating point > numbers is in Excel, but as your experiment shows, it is probably not > IEEE754 double-precision binary. > Well according to this is does... https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel But that seems to be lying. If I enter the two number into Excel, they behave differently. So something in Excel "knows" There are some exceptions listed in that document, but they don't seem to explain the difference for these two numbers. ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
> On 4/5/19 11:14 AM, Arthur Blondel wrote: > > I have enough disk space. I just limit the database file size artificially > > for testing purpose as you can see. > > There is no problem of privilege and there is nothing else than the code I > > sent. No other access to the DB. > > I'm using sqlite 3.16.2 > > As has been pointed out, one issue is that not all records, even if the > 'same' take the same space, so deleting one record may not make enough > room for another. > And I would also point out, the data for each row is not the same. The id is incrementing. So id 4000 may not be able to be put on the same page as the id 1 you have just deleted. Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best practices for forward conversion of database formats?
> This is not strictly a SQLite question, though in my case it is. > > Any application that involves a "persistent" database, i.e., one where the > data is long-lived and expected to exist and evolve over time, sooner or later > has the issue of moving customers from a V1 database to a V2 database. Several have given examples of holding a schema version number in the database and having your application know how to upgrade from one to the next. I have used similar mechanisms, but one thing I would say. If you have to maintain older versions of your app, make a jump in the schema version when you switch major version of you app. For example, if you have version 1.5 of your app and you move to version 2.0, then you need to make a version 1.6 for some reason and it needs a schema change. If you jumped to say, schema version 100 for app 2.0, it is much easier to add a new schema version for 1.6 that can then be handled by 2.x Also, one system I worked on used SQL script files to make the database changes. e.g. There would be a script that updated from schema 2 to 3. To go with this there would also be a script that downgraded from schema 3 to 2. These scripts where left by the installer, so if you needed to install an earlier version of code, it could find the "downgrade" scripts and run them without the earlier version of code having to know how to downgrade. HTH Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
> another point that I did not make clear. The accounting programs are not > associated with the technical programs, different people, different security > access. The tech databases and programs are in portable computers that go out > in the field, but not the accounting, etc. There indexes would have to be > updated when the computers are back at the office. I have a feeling that what the OP is calling an index isn’t really. I think what he is talking about is the list of customer IDs. So customer data can get updated “back at the office” and when the portable computers get back they need to be updated with the changes. In sqlite terms, an index is a sorted list of the data in one or more columns of a table to help speed up access to the data in that table. Once defined, the index is automatically updated by sqlite as the data in the table is changed. Perhaps we could get some clarification from the OP about what is actually wanted. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
> another point that I did not make clear. The accounting programs are not > associated with the technical programs, different people, different security > access. The tech databases and programs are in portable computers that go out > in the field, but not the accounting, etc. There indexes would have to be > updated when the computers are back at the office. I have a feeling that what the OP is calling an index isn’t really. I think what he is talking about is the list of customer IDs. So customer data can get updated “back at the office” and when the portable computers get back they need to be updated with the changes. In sqlite terms, an index is a sorted list of the data in one or more columns of a table to help speed up access to the data in that table. Once defined, the index is automatically updated by sqlite as the data in the table is changed. Perhaps we could get some clarification from the OP about what is actually wanted. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
