A simpler DB container.

[Rakon]

Using the above code in an alias, then calling it, creates both the container file and the table properly.

[Rakon]

Okay, I removed the last argument to 'assert' so that I could get back more meaningful errors. Fixed up my code, and added in a 'display(results)' to see if I got anything back. Then I ran the following dba function:

Code: Select all

/dba.query([[SELECT * FROM weapons where type="rapier" and spd>='227']])

Which (properly I think) returned :

Code: Select all

table {
  'count': function
  'pairs': function
}

So I assume that means that the phpTable results, has been created correctly. However, this is a local variable and not accessiable once the code is done running. So, how do I access the phpTable results to modify them, or display them instead of the call to the phpTable function?

[tsuujin]

Code: [show] | [select all] lua

for row,results in dba.query([[SELECT * FROM weapons where type="rapier" and spd>='227']]) do 
    display(results)
end

edit: Sorry for the delay on that, by the way. Been nursing an injured hand.

[Rakon]

No worries, thanks. I've done that via query, and gotten a script to properly display results, that is the results:phpTable() items using a

Code: [show] | [select all] lua

 
for k,v in results:pairs() do
 <stuff>
end

Using your above code, again, only the pairs/count phpTable function is returned as a table, not the actual information inside of it.

Thanks again for the code, and assistance Tsuujin.

[Rakon]

Okay, I've gotten the majority of this script working, with your help and thanks to you.

I can query and return results:

Code: [show] | [select all] lua

results = dba.query([[select * from slain where name like "%]] .. matches[3]:trim() ..[[%" order by count desc]])

for k,v in results:pairs() do
  table.insert(disp,string.format("[%i]  %s", v.count,name))
end

And I can execute statements against the database:

Code: [show] | [select all] lua

dba.execute([[update slain set count=]] .. num .. [[and where name="]] .. name ..[["]])

But, using the following code, I cannot TEST for exsistance of a value in the database:

Code: [show] | [select all] lua

local name = multimatches[2][3]
local results = dba.query([[select * from slain where name="]] .. name .. [["]])

for row,col in results:pairs() do
 if not col.count then  
  dba.execute([[insert into slain values ("]].. name .. [[", 1)]])
 else
  local num = col.count + 1
  dba.execute([[update slain set count=]] .. num .. [[and where name="]] .. name ..[["]])
 end
end

The thing is the statement 'if not col.count' ... I don't know how to test for this 'name' existing in the results phpTable. I've tried to use:

if not col
if #col == 0

And just about everything else testing for existance. nil, len(), #, display(), echo(), table.contains() ... just everything I could think of.

Also tried this code to no avail:

Code: [show] | [select all] lua

for row,col in results:pairs() do
 display(col) -- displays NOTHING
 if table.contains(col,name) then 
  dba.execute([[update slain set count=]] .. num .. [[and where name="]] .. name ..[["]])
 else
  al_alert("DOES NOT CONTAIN!")
  dba.begin()
  dba.execute([[insert into slain values ("]] .. name.. [[",1)]])
  dba.commit()
 end
end

Any ideas or working code to test the results phpTable for an item??

Thanks

[tsuujin]

Ok, A couple of things here.

the php styled table doesn't return data like you may expect. It keeps data sorted by actually adding whatever you insert into the table into a sub-table, indexed by number. As you may or may not know, Lua will sort numerically indexed tables by the order in which they were added for you, but if you use keyed indexes (like a string) it actually sorts it by the hash value of the key. php:Table is an answer to this.
So, if you do phptable['me'] = "tsuujin", the metatable information on phptable diverts the information into a hidden subtable, in order to maintain organization. This is why you can't just display(phptable), because display looks at the raw table information without respecting the metatable data.
Here's a piece of code I just verified as working from my actual system:

Code: [show] | [select all] lua

lua for row,results in dba.query([[SELECT * FROM player_relations]]):pairs() do display(results) end

This calls display not on the base table, but on the results table for each entry in it. You can use this to see each and every result, it's kind of handy. The key to remember here is that when you call pairs() on a php:Table, you're going to get the numerical index as the first argument ("row") and a table containing that rows data ("results") that you can work with.

php:Table is actually public code that I've just modified in a small way, but one of those modifications is very important to another of your questions. I added a count() feature, which returns the number of rows in the table. So, if you wanted to quickly see how many results you returned, do:

Code: [show] | [select all] lua

local results = dba.query([[some query]])
display(results:count())

You could of course expand that into expanded error checking or data reporting.

On a side note: learn to use string.format. Your life will be much less complex once you realize how damned useful it is, and how much cleaner your code is to read.
Instead of:

Code: [show] | [select all] lua

results = dba.query([[select * from slain where name like "%]] .. matches[3]:trim() ..[[%" order by count desc]])

do

Code: [show] | [select all] lua

results = dba.query(string.format([[SELECT * FROM slain WHERE name LIKE "%%%s%%" ORDER BY count DESC]], matches[3]:trim())) 

While I know the requirement of escaping percents with a double percent is kind of ugly, you won't always run into this, and it's still much easier to read than breaking the string down into parts and concatenating. Also helps because a lot of syntax highlighters get confused with the opening and closing quotations. Finally, string.format tends to be faster, and avoids a bug in Lua's core coding regarding bulk concatenation and the garbage collector.

[Rakon]

Okay, thank you for that code clarification. I'll use the string.format technique from now on, it is easier to ready.

Question though, how do I utilize the transaction functions in the script, IE
begin()
commit()

I've tried using them as shown in my previous post (modified to the below):

Code: [show] | [select all] lua

 dba.begin()
 dba.execute(string.format([[insert into slain values ("%s",1)]],name))
 dba.commit()

But I get :
'Unable to begin transaction'
'Unable to commit transaction'

or just

'Unable to commit transaction'

Am I using them wrong?

[tsuujin]

I changed the way the transactions are handled, and have modified the first post in this thread to reflect the changes. Try them out, see if they work better for you.

All you need to do is call dba.begin() before executing in bulk, and dba.commit() afterwards. From one of my files:

Code: [show] | [select all] lua

function dbSave()
    if not dba.begin() then
        tempTimer(1,[[player.dbSave()]])
        return false
    end
    for name,tbl in pairs(list) do
        dba.execute(string.format([[INSERT INTO player_relations VALUES ("%s","%s","%s")]],name,dbString(tbl["ally"]),dbString(tbl["enemy"])))
    end
    dba.commit()
end

Note that dba.begin() returns false if a transaction is already in the works, and the first few lines of that snippet give example on how to wait until the current transaction is finished before beginning a new one.

[Rakon]

Right, okay. When I do like I stated:

Code: [show] | [select all] lua

 dba.begin()
 dba.execute(string.format([[insert into slain values ("%s",1)]],name))
 --[[ Lots more stuff here ]]--
 dba.commit()

The first one completes, but I get an assert error on the second dba.execute for 'LuaSQL database locked'. In order to resolve this, I have to completely shut down Mudlet, then restart.
I don't know why this happens considering that's the point of transaction/commiting a bulk set.

[Rakon]

When I'm trying to use being(), execute()*40, then call commit(), an 'lsof RAKON.db' command returns ... a lot of open connections to the file; not good. And sometimes, I get the error:

</.config/mudlet/profiles/Rakon/dba.lua:70: LuaSQL: SQL logic error or missing
database>

Also getting this error at times, for what reason... I do not know:

</.config/mudlet/profiles/Rakon/dba.lua:44: calling 'execute' on bad self
(LuaSQL: connection is closed)>

How do I prevent this, when utilizing luaSQL transaction history, without turning on autocommit and using the begin() ' BEGIN TRANSACTION' statements?