Moving from account data to seperate SQLite table

[qaisjp]

I previously stored money and some other data as account data and I would like to move it to SQLite tables. Would this work to transfer them?

  
function transfer() 
   executeSQLCreateTable ("userdata", "account TEXT, money TEXT, skin INT, position TEXT" ) 
   for index,value in ipairs(getAccounts()) do 
        local money = getAccountData(value, "money") 
        local skin= getAccountData(value, "skin") 
        local position = getAccountData(value, "lastpos") 
        executeSQLInsert("userdata", "'"..getAccountName(value).."', "..tonumber(money) or 0 .."', "..tonumber(skin) or 0 .."', "..tostring(position).."'") 
    end 
end 
          

Does executeSQLDropTable delete the table? For example

executeSQLDropTable("userdata") 

would remove the table userdata?

Would executeSQLDelete be appropiate for when deleting an account? So I would just need to do the account name as the second value?

[Castillo]

Yes, that should work.

executeSQLDropTable indeed drops a table(removes it).

and executeSQLDelete is used to remove a row in your table.

[qaisjp]

(Thanks Cast)

What do I need to do if I want to add more columns?

[Castillo]

I always drop the table, maybe there's a easy way, but that's the one I always use.

[qaisjp]

So you select the table (and store as a LUA Table) , then you drop it, then you create it again with the extra stuff and then put in the old data (with a script that adds the extra stuff to the lua table)

[Castillo]

Exactly, that's what I did last time.

[SDK]

You can also use the ALTER statement in executeSQLQuery

http://www.w3schools.com/sql/sql_alter.asp

[qaisjp]

Hm... thanks.

source

  
function addSQLTableColumn(string tablename, string columname) 
     return executeSQLQuery("ALTER TABLE "..tostring(tablename).." ADD "..tostring(columnname).." "..tostring(datatype)) 
end 
  

  
function removeSQLTableColumn(string tablename, string columname, string datatype) 
     return executeSQLQuery("ALTER TABLE "..tostring(tablename).." DROP "..tostring(columnname)) 
end 
  

  
function alterSQLTableColumn(string tablename, string columname, string datatype) 
     return executeSQLQuery("ALTER TABLE "..tostring(tablename).." ALTER "..tostring(columnname).." "..tostring(datatype)) 
end