I get most of my info from here: http://wiki.mudlet.org/w/Manual:Lua_Fun ... _Functions
And that is where it says to use just the field names.
Mudlet 2.1 Database Problem
Re: Mudlet 2.1 Database Problem
Thanks, I've corrected that. And the source code comments, which is where that was taken from...
I've added this example to the unit testing as well: https://github.com/vadi2/mudlet-lua/blo ... B.lua#L152
I've added this example to the unit testing as well: https://github.com/vadi2/mudlet-lua/blo ... B.lua#L152
Re: Mudlet 2.1 Database Problem
I've also updated the documentation on the fact of db:create() and adding new fields.
Re: Mudlet 2.1 Database Problem
So by messing around with the db:_migrate function, I managed to tweak it so that it still creates new sheets, and also adds new columns to existing sheets. I'm not really sure if it is all kosher, as I know absolutely nothing about SQL, but as far as I can tell it seems to be okay. I scavenged some stuff from db:fetch_sql and then threw out the part db:_coerce_sheet part since all we needed was the names of current columns. I have the part I hacked in outlined, everything else has been left alone.
So, I adjusted it a bit to fix a few problems it was having. It still only works for sheets that have data in them though, so I am thinking of coming up with a way of adding a dummy row of data and then deleting it.
Re: Mudlet 2.1 Database Problem
I finished messing with the db:_migrate function. It now successfully adds columns to existing sheets, whether they have any data in them or not. To do it, I first just select the sheet and try to grab a row. If there is one, I use the columns in it as the current columns. If there isn't, I add a new row (with a fixed value in the first string type column found in the schema), grab it and use the data from it to determine what columns are in the sheet, then delete the row I just added. It then continues as it did before, only this time the current_columns table is correctly populated.
Re: Mudlet 2.1 Database Problem
Could you make it so if the pragma works, it'll use that data instead? As a safeguard for when a LuaSQL with working pragmas is available on the platform.
Re: Mudlet 2.1 Database Problem
Sure, here you go. As is, the only condition I can think of that may present problems here would be if the original schema for a sheet had no string type columns, and then more columns were added. But, even if that case fails, this should still be a big step up in functionality.
Re: Mudlet 2.1 Database Problem
I still need to add this, but it's not forgotten. Just been terribly busy and when not, unmotivated from being busy
Re: Mudlet 2.1 Database Problem
I know the feeling. Been pretty swamped myself recently, and I know that this is a large enough change that it needs some serious testing, and possibly some tweaking to account for special cases. I just hope it makes it into the next release so that I can start using databases for scripts I provide to other people.
Re: Mudlet 2.1 Database Problem
So, I did some digging into the Timestamp issue I have been seeing. First, the database indicates that the datatype for the timestamp field is an integer, but the data actually being stored initially is a string with the timestamp (2013-07-16 16:19:41 is an example from one sample data set I generated). That data is then retrieved and transformed into an epoch value when the data is fetched. However, when that value is turned back into a user readable time, the hour is incorrect (for that example, it displays as 17:19:41, not 16:19:41). Also, I have to assume that those times are in GMT, since the timestamps were generated in the morning for me, as opposed to mid afternoon. When a row is retrieved and updated using the db:update function, the timestamp is overwritten as the calculated epoch value, instead of the string which it is originally assigned. Once it is overwritten, the row is no longer accessible via db:fetch (since it tries to parse the epoch value as if it were a date/time string). I would think that the best approach to fixing this issue would be storing the epoch value initially, with no parsing necessary except for display to the user.
However, since whatever code is involved in setting the timestamp when the new entry is added does not seem to be in DB.lua (the db:add sends the sql command directly, without any timestamp involved, so I have to assume the timestamp is generated either via sql itself, or something buried in the C++ code), it is probably easier to modify the db:_coerce_sheet function to handle timestamps written in either format.
This modified version of db:_coerce_sheet allows timestamps to work via the db:update function (both with the timestamp not being changed, and with it being set to a new value).
However, since whatever code is involved in setting the timestamp when the new entry is added does not seem to be in DB.lua (the db:add sends the sql command directly, without any timestamp involved, so I have to assume the timestamp is generated either via sql itself, or something buried in the C++ code), it is probably easier to modify the db:_coerce_sheet function to handle timestamps written in either format.
This modified version of db:_coerce_sheet allows timestamps to work via the db:update function (both with the timestamp not being changed, and with it being set to a new value).
I did confirm that the "CURRENT_TIMESTAMP" is set as the current GMT time. Also, the one hour difference was due to daylight savings time being set as false, and adjusted for via the os.time function. So long as the only values stored as strings are the initial ones (which are always GMT), it should be fine, but if another value were stored as a string (which has no place in it to record daylight savings time values), it could give incorrect results. As is, I think that usage of timestamps should be documented to encourage providing the timestamp explicitly when adding a new entry (i.e. adding something like: time = db:Timestamp(getTime()) to the table given to the db:add function), rather than relying on the CURRENT_TIMESTAMP functionality provided currently, unless there is no desire to modify those values at a later time, and the user is comfortable with everything being several hours off depending on their location.