Simon, The issue was not storing 1 or 0 per se, but rather thinking through the logic of what it could be under certain circumstances.
Our app provides highly personalised traffic information for Londonders. One aspect of the app is to allow alerts to be sent to people when their app isn?t even on. The user may indicate they are on holiday in which case no information is sent, however we allow a highly detached ack mode of working with the app from the database. We were trying to think through the impact of providing a default to the system, e.g. they are on or not on holiday and how that would play out when synchronisation eventually occurs. The user may not have put anything in for their holiday and should we make any assumptions. We used to work for IBM which is heavy on Lotus Notes, the normal way of using Notes is detached from a main server and syncing every X minutes, the problems occur when the back end Notes database gets out of sync with the front end client database. You get conflicts where the system doesn?t know which is the master version of a document. We are trying to think through these scenarios and make sure we don?t end up in a Save/Replication conflict.This is not the biggest problem for us, but we want to make sure we?ve done the job right. BTW This is not a slight on SQLIte at all, its the classic problem of data potentially being written in two places and then who wins. IBM never really resolved the issue with Lotus Notes and we are trying to think things through to avoid future issues. We *may* be overthinking the problem but at least we?ve tried. We are still working our way through the potential issues and trying to make sure the schema is right. Its a lot easier to fix the schema now than in a years time :) Thanks for the helpful reply, Rob > On 25 May 2015, at 13:34, Simon Slavin <slavins at bigfraud.org> wrote: > > > On 23 May 2015, at 5:21pm, Rob Willett <rwillett at robertwillett.com> wrote: > >> If we can force holiday_mode to be set to either 0 or 1 then the problem >> goes away, which comes down to getting the design right. > > I don't know how your synchronisation works, but you can definitely make sure > only 'legal' values are stored in a column: > > CREATE TABLE myTable ( > holiday_mode INTEGER > DEFAULT 0 > CHECK (holiday_mode = 0 OR holiday_mode = 1) > ) > > You don't need a DEFAULT constraint in there too, I just put it in to show > it's possible. > >> I think that this ?issue? we have is indicative of a poor DB design and >> wrong assumptions (bad pun) and we should fix that. > > In previous jobs I have inherited databases which would have required a lot > of work to fix in their current forms but would take care of themselves with > properly re-written schema. But sometimes you can't do that because it would > require retesting huge amounts of legacy code. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
