[Help] Autoincrement not working via Primary key

[sparcdr]

I am trying to synchronize varying tables 'userid' column to match the user being registered. I looked at the mtasa-blue source where it returns the pid to fill the userid column of the internal.db table accounts, but the accounts table is read-protected by direct sql queries.

As a workaround I considered having a populate function onResourceStart create the first row for the Console user so any further users would align to 2+. Since primary keys wasn't working for me, I'm trying to get a count of how many rows exist in the licenses table so I can upon table injection by each user registration increment +1 the returned count value.

Executing: " SELECT count(*) from licenses" in SQLite Database Browser returns a column with count(userid) and value 2 signifying the number of rows. The alternative

local getcount = sql.Query( "select COALESCE(MAX(userid)+1, 0) from licenses" ); 

also returns a table. I've tried i,v and key,val to iterate but I'm unsure what's in the tables so I can't assume what to select or print. I need to be able to figure out what the structure inside is and then select the first field's first index to return the value. To me it is acting like it's multidimensional since I can put a secondary iterator and still have it tell me there's a table.

Current MTASA Code:

  
local sql = { Query = executeSQLQuery }; 
result = executeSQLQuery ( " SELECT count(*) from licenses" ); 
outputServerLog(tostring(result)) 
  

Returns a table ID (In hex)

The creation stage has

  
local createtable = sql.Query( "CREATE TABLE IF NOT EXISTS licenses (userid INT PRIMARY KEY AUTOINCREMENT, lic_driver INT, player text)"); 
  
if (createtable) then 
        local select = executeSQLSelect("licenses", "player") 
        if ( type( select ) == "table" and #select == 0 ) or not select then 
            outputServerLog("Record not found, creating Console user") 
            local insert = executeSQLQuery ("INSERT INTO licenses ( userid, lic_driver, player ) VALUES (NULL, '0', 'Console')"); 
        else 
            outputServerLog("Record for Console user already exists") 
        end 
    end 
  
end 
  

Each population stage (Triggered by user) is:

client -> register (pass getLocalPlayer as myPlayer through a trigger) -> populateLicenseTable

  
function populateLicenseTable(myPlayer) 
local thePlayer = getPlayerName(myPlayer) 
local insert = executeSQLQuery ("INSERT INTO licenses ( userid, lic_driver, player) VALUES (NULL, '0', '".. thePlayer .. "' )"); 
if (insert) then 
    outputServerLog("Inserted values") 
else 
    outputServerLog("Trouble inserting values") 
end 
end 
  

The player value already works, the issue is the primary key not autoincrementing. I've tried 'NULL' as well as '' to try and coerce it. Looking at the SQLite documentation is what lead me to use NULL for setting up it to increment.

Thanks.

Update:

Use getAccounts to fetch n rows + 1 for userid and force it into the column.

  
local thePlayer = getPlayerName(myPlayer) 
local accountTable = getAccounts () 
if #accountTable == 0 then 
    outputServerLog("No accounts currently exist") 
    else 
        outputServerLog("There are " .. #accountTable .. " accounts") 
end 
local theid = #accountTable + 1 
outputServerLog(theid) 
  
local sql = { Query = executeSQLInsert }; 
local insert = executeSQLQuery ("INSERT INTO licenses ( userid, lic_driver, player ) VALUES ('".. theid .. "', '0', '".. thePlayer .."' )"); 
  

Edited October 19, 2010 by Guest