[sqlite] Updating the 'default' clause of a column?
Thanks Simon! Don't worry about the type 'timestamp'. We know how SQLite works with types. We use timestamp because it is a good thing for documentation purpose. We actually store an integer or real in there according to our application rules. For the short story we initially chose to always store Juliandays in our databases. But recently decided to change for a custom binary integer format which includes date, time, and bias from UTC. This is why we need to patch some older existing databases to use default (now()) instead of default (julianday()). Our now() returns the properly encoded integer. We also have overloads for date(), time() and datetime() to returning readable string values when some hand work has to be done on the command line. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia (from mobile device), integral.be/om > Le 23 mai 2016 ? 13:12, Simon Slavin a ?crit : > > I notice only one thing, but you're at the time when it's easy to change it. > The 'type' you've picked for this column is 'timestamp'. SQLite has no such > type. I recommend you use one of INTEGER, REAL or TEXT depending on the > value that your 'now()' function returns. > > You won't have found a problem with it so far, but you may encounter one in > other parts of your program which set or obtain a value in that column.
[sqlite] Updating the 'default' clause of a column?
Hello, Assume a table simplified for this discussion as: create table T(C timestamp default (julianday()) not null); I simply have to use a new function (let's call it 'now()' other than julianday() to define the default for this column. I have apparently successfully tested this: pragma writable_schema=1; update sqlite_master set sql='create table T(C timestamp default (now()) not null)' where type='table' and name='T'; pragma writable_schema=0; (and then disconnect / reconnect). The automated patch would actually select the sql column value for the right table, edit the string appropriately, changing nothing else, then update it as above. This will happen in a situation where a single connection (that one) is made to the db (that can be guaranteed by the host app). Apparently, from testing, I do not seem to suffer from side-effects and the default is properly applied when needed. But am I missing some important detail that could hurt me later? Or do you agree with this procedure? -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] Updating the 'default' clause of a column?
On 23 May 2016, at 11:14am, Olivier Mascia wrote: > But am I missing some important detail that could hurt me later? I notice only one thing, but you're at the time when it's easy to change it. The 'type' you've picked for this column is 'timestamp'. SQLite has no such type. I recommend you use one of INTEGER, REAL or TEXT depending on the value that your 'now()' function returns. You won't have found a problem with it so far, but you may encounter one in other parts of your program which set or obtain a value in that column. Simon.
