Mudlet 2.1 Database Problem

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Also, I ran into an error creating a new sheet, which I fixed via a small tweak of the db:_migrate function, shown here:
Code: [show] | [select all] lua
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
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.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

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:
Code: [show] | [select all] lua
function db:_coerce(field, value)
   if field.type == "number" then
      return tonumber(value) or "'"..value.."'"
   elseif field.type == "datetime" then
      if value._timestamp == false then
         return "NULL"
      elseif value._timestamp == "CURRENT_TIMESTAMP" then
         return tonumber(os.time())
      else
         return tonumber(value._timestamp) or "'"..value.."'"
      end
   else
      return "'"..tostring(value):gsub("'", "''").."'"
   end
end
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.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

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.
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!

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:
Code: [show] | [select all] lua
$ busted -l lua DB.lua 

●●●●●●●●●●●● 
10 successes / 2 failures / 0 pending : 0.521624 seconds.

Failure → ./DB.lua @ 177
Should sort the fields by level first and then name, both in descending order
../lua/DB.lua:1441: LuaSQL: there are open cursors

Failure → ./DB.lua @ 216
Should add a row successfully to an empty db
../lua/DB.lua:905: LuaSQL: no such column: row3
So I'll continue working on this when I can.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

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.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

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.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

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?

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

The "try until it works" design seems to be working, horray.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

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.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Thanks Vadi. It will be good to have working database scripts in the next release.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

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.

Post Reply