function db:_migrate(db_name, s_name)
local conn = db.__conn[db_name]
local schema = db.__schema[db_name][s_name]
local current_columns = {}
-- The PRAGMA table_info command is a query which returns all of the columns currently
-- defined in the specified table. The purpose of this section is to see if any new columns
-- have been added.
local cur = conn:execute("PRAGMA table_info('"..s_name.."')") -- currently broken - LuaSQL bug, needs to be upgraded for new sqlite API
if type(cur) ~= "number" then
local row = cur:fetch({}, "a")
if row then
while row do
current_columns[row.name] = row.type
row = cur:fetch({}, "a")
end
cur:close()
else
--------------- GETS ALL COLUMNS FROM SHEET IF IT EXISTS
db:echo_sql("SELECT * FROM "..s_name)
cur = conn:execute("SELECT * FROM "..s_name) -- select the sheet
if cur and cur ~= 0 then
local row = cur:fetch({}, "a") -- grab the first row, if any
if not row then -- if no first row then
local col = ""
for k,v in pairs(schema.columns) do -- look through sheet schema to find the first column that is text
if type(k) == "number" then
if string.sub(v,1,1) ~= "_" then col = v break end
else
if string.sub(k,1,1) ~= "_" and type(v) == "string" then col = k break end
end
end
db:add({_db_name = db_name, _sht_name = s_name},{[col] = "test"}) -- add row with found column set as "test"
cur = conn:execute("SELECT * FROM "..s_name) -- select the sheet
row = cur:fetch({}, "a") -- grab the newly created row
-- delete the newly created row
db:delete({_db_name = db_name, _sht_name = s_name},db:eq({database = db_name, sheet = s_name, name = col, type = "string"},"test"))
--display(row)
end
cur:close()
if row then -- add each column from row to current_columns table
for k,v in pairs(row) do
current_columns[k] = ""
end
end
end
end
end
-- The SQL definition of a column is:
-- "column_name" column_type NULL
-- The db module does not presently support columns that are required. Everything is optional,
-- everything may be NULL / nil.
-- If you specify a column's type, you also specify its default value.
local sql_column = ', "%s" %s NULL'
local sql_column_default = sql_column..' DEFAULT %s'
if table.is_empty(current_columns) then
-- At this point, we know that the specified table does not exist in the database and so we
-- should create it.
-- Every sheet has an implicit _row_id column. It is not presently (and likely never will be)
-- supported to define the primary key of any sheet.
local sql_chunks = {"CREATE TABLE ", s_name, '("_row_id" INTEGER PRIMARY KEY AUTOINCREMENT'}
-- We iterate over every defined column, and add a line which creates it.
for key, value in pairs(schema.columns) do
local sql = ""
if value == nil then
sql = sql_column:format(key, db:_sql_type(value))
else
sql = sql_column_default:format(key, db:_sql_type(value), db:_sql_convert(value))
end
if (type(schema.options._unique) == "table" and table.contains(schema.options._unique, key))
or (type(schema.options._unique) == "string" and schema.options._unique == key) then
sql = sql .. " UNIQUE"
end
sql_chunks[#sql_chunks+1] = sql
end
sql_chunks[#sql_chunks+1] = ")"
local sql = table.concat(sql_chunks, "")
db:echo_sql(sql)
conn:execute(sql)
else
-- At this point we know that the sheet already exists, but we are concerned if the current
-- definition includes columns which may be added.
local sql_chunks = {}
local sql_add = 'ALTER TABLE %s ADD COLUMN "%s" %s NULL DEFAULT %s'
for k, v in pairs(schema.columns) do
t = db:_sql_type(v)
v = db:_sql_convert(v)
-- Here we test it a given column exists in the sheet already, and if not, we add that
-- column.
if not current_columns[k] then
-- display("Adding: " .. k)
local sql = sql_add:format(s_name, k, t, v)
conn:execute(sql)
db:echo_sql(sql)
end
end
end
-- On every invocation of db:create we run the code that creates indexes, as that code will
-- do nothing if the specific indexes already exist. This is enforced by the db:_index_name
-- function creating a unique index.
--
-- Note that in no situation will an existing index be deleted.
-- make up current_columns, as pragma_info currently does not populate it, due to luasql bug
for key, value in pairs(schema.columns) do
current_columns[key] = db:_sql_type(value)
end
db:_migrate_indexes(conn, s_name, schema, current_columns)
db:echo_sql("COMMIT")
conn:commit()
conn:execute("VACUUM")
end
Mudlet 2.1 Database Problem
Re: Mudlet 2.1 Database Problem
Also, I ran into an error creating a new sheet, which I fixed via a small tweak of the db:_migrate function, shown here:
For some reason the PRAGMA SQL command was returning a non-zero number, so I made the if statement check to see if cur was a number, rather than if it was zero.
Re: Mudlet 2.1 Database Problem
In addition to the change to the db:_coerce_sheet function to allow timestamps to be properly updated, if the db:_coerce function can be changed to allow "CURRENT_TIMESTAMP" to be used for a timestamp entry into a database, like this:
Note that this gives different results than the default "CURRENT_TIMESTAMP" entry in a database, but it allows any possible output of the db:Timestamp function to be handled without error (and in a correct way, I think) when using the db:update and db:add functions.
Re: Mudlet 2.1 Database Problem
Finally got around to doing this. There was a bug in this in the fact that the cursor was being overwritten and thus wasn't being :closed() properly, which the testing picked up - I'm already happy it paid off there!Jor'Mox wrote:Also, I ran into an error creating a new sheet, which I fixed via a small tweak of the db:_migrate function, shown here:
For some reason the PRAGMA SQL command was returning a non-zero number, so I made the if statement check to see if cur was a number, rather than if it was zero.
Once I've fixed that though and started adding tests for a new row being added properly, some new ones popped up though and I ran out of time by then to fix it:
So I'll continue working on this when I can.
Re: Mudlet 2.1 Database Problem
Ah, the other error located was that col could sometimes selec the new row you're trying to add, thus causing an obvious problem of trying to use a row that doesn't exist yet.
Re: Mudlet 2.1 Database Problem
That one I actually knew about, I just didn't have any idea of how to go about addressing it. Not really knowing much about databases and the interface used to work with them made it difficult for me to finesse my way around to finding out the currently existing columns. I thought of trying to pull a row by the row_id and use that, but there is no row that is guaranteed to be there, since deleted rows don't have to be replaced.
Any thoughts on how to get around this? Or have you already fixed it?
Also, on an entirely unrelated note, I realized that the new gauge functions I wrote didn't create any actual label with the name given to the gauge. I would think changing the top label (the one for the text) to use that name would make the most sense.
Any thoughts on how to get around this? Or have you already fixed it?
Also, on an entirely unrelated note, I realized that the new gauge functions I wrote didn't create any actual label with the name given to the gauge. I would think changing the top label (the one for the text) to use that name would make the most sense.
Re: Mudlet 2.1 Database Problem
I'm still thiking on how to fix it. I at first went with abusing _unique, but that failed when you don't specify one in the schema (and you don't have to).
I'll setup something that loops through the rows it finds until one insert actually works. That design is sound as you have to be supplying at least one row for a schema to make any sense anyhow (or in this case - two, as you can't delete rows and thus not supplying one that exists will cause problems).
I haven't noticed that about the gauges, could you post a fix to that thread?
I'll setup something that loops through the rows it finds until one insert actually works. That design is sound as you have to be supplying at least one row for a schema to make any sense anyhow (or in this case - two, as you can't delete rows and thus not supplying one that exists will cause problems).
I haven't noticed that about the gauges, could you post a fix to that thread?
Re: Mudlet 2.1 Database Problem
The "try until it works" design seems to be working, horray.
Re: Mudlet 2.1 Database Problem
Thanks, the change has been added with the fixes and I've added new unit tests to cover it. Sorry it took so long!
I'll get to the timestamps later on, have to cover other things now.
I'll get to the timestamps later on, have to cover other things now.
Re: Mudlet 2.1 Database Problem
Thanks Vadi. It will be good to have working database scripts in the next release.
Re: Mudlet 2.1 Database Problem
I've started looking into timestamps - the timestamp is properly being stored as an interger in the database, and the code for that is correct as well at a quick overview. My SQL viewer was rendering the epoch as human-readable time - maybe yours was doing this as well and that is what confused you?
It does store in GMT by default, and you can use select datetime(<field>, 'localtime') from <table> to get the version of the time in your local time.
The timestamp is generated by SQLite, that's correct (CURRENT_TIMESTAMP in https://www.sqlite.org/lang_createtable.html).
I wouldn't go your way of providing the current time because that would break when the user crosses timezones. I'll look into making the data retrieval from the DB properly though.
It does store in GMT by default, and you can use select datetime(<field>, 'localtime') from <table> to get the version of the time in your local time.
The timestamp is generated by SQLite, that's correct (CURRENT_TIMESTAMP in https://www.sqlite.org/lang_createtable.html).
I wouldn't go your way of providing the current time because that would break when the user crosses timezones. I'll look into making the data retrieval from the DB properly though.