Ulysses

General => Developers Corner => Topic started by: DeimosTK on January 10, 2012, 06:33:33 PM

Title: Possible to add new columns to an existing SQL table?
Post by: DeimosTK on January 10, 2012, 06:33:33 PM

I just started learning Lua/SQL a few days ago and pestered Megiddo with some questions, so I thought I'd give him a break and ask for help here.

I'm trying to set up a function that allows me to add new columns to an existing SQL table. Is this possible?

This is the non-functioning code I have thus far. It runs when a player joins the server, but I just realized it would probably be more efficient if it ran only once when the server booted up, so I'll have to modify it to do that.

sv_serverflags.lua

Code: [Select]

-- Create the SQL table if it doesn't exist
if not sql.TableExists( "server_flags" ) then
    sql.Query( "CREATE TABLE IF NOT EXISTS server_flags ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, player INTEGER NOT NULL, initialflag INTEGER NOT NULL );" )
end

-- Set up the flags
local ServerFlags = {
"testflag1",
"testflag2",
"another_flag"
}

-- Add flags to the player if they don't already have them when they join the server
function FlagSetup( ply )

    local uid = ply:UniqueID()
    -- Make sure the player has a row in the table - blatantly plagiarized from UTime
    local row = sql.QueryRow( "SELECT initialflag FROM server_flags WHERE player = " .. uid .. ";" )
    if not row then
        sql.Query( "INSERT into server_flags ( player, initialflag ) VALUES ( " .. uid .. ", " .. 1 .. " );" )
    end
   
    -- Cycle through the list of flags
    for k,v in pairs(ServerFlags) do
        local currentflag = sql.Query("SELECT " .. ServerFlags[k] .. " FROM server_flags WHERE player = " .. uid .. ";")
        -- If the player doesn't have this flag in their row in the SQL db, add it
        if not currentflag then
            -- Add the column
            sql.Query("ALTER TABLE server_flags ADD " .. Flags[k] .. " INTEGER NOT NULL WHERE player = " .. uid .. ";")
            -- Make the flag value "0" (Hope this doesn't make everyone's Flag[k] value "0"...)
            sql.Query("INSERT into server_flags (" .. Flags[k] .. ") VALUES (" .. 0 .. ");")
        end
    end
       
end
hook.Add( "PlayerInitialSpawn", "FlagSetup", FlagSetup)

I have pretty much no idea what I'm doing when it comes to SQL, so don't be afraid to treat me like a child in your explanation.

EDIT: Realized I forgot to assign "player" a value in the table, that would definitely be a problem. Added that fix.

Title: Re: Possible to add new columns to an existing SQL table?
Post by: MrPresident on January 11, 2012, 12:05:25 AM

When doing ANYTHING related to coding (web in particular) use this website. It will be your best friend.

http://www.w3schools.com/

the SQL query you are looking for to add a column to an existing SQL table is as follows:

Code: [Select]

ALTER TABLE table_name ADD column_name datatype
datatypes can be found Here  (http://www.w3schools.com/sql/sql_datatypes.asp)

Title: Re: Possible to add new columns to an existing SQL table?
Post by: MrPresident on January 11, 2012, 12:07:50 AM

Oh, read your code a little...

sql.Query("ALTER TABLE server_flags ADD " .. Flags[k] .. " INTEGER NOT NULL WHERE player = " .. uid .. ";")

you would not need the WHERE portion of this query. You are adding a column to the table, you don't need to define a place to add it within the table.

Title: Re: Possible to add new columns to an existing SQL table?
Post by: DeimosTK on January 11, 2012, 12:17:56 AM

Quote from: MrPresident on January 11, 2012, 12:07:50 AM

Oh, read your code a little...

sql.Query("ALTER TABLE server_flags ADD " .. Flags[k] .. " INTEGER NOT NULL WHERE player = " .. uid .. ";")

you would not need the WHERE portion of this query. You are adding a column to the table, you don't need to define a place to add it within the table.

Yeah, I finally got the code to work due to correcting that error, as well as becoming aware of the fact that ALTER TABLE doesn't seem to like INTEGER NOT NULL, whereas regular INTEGER worked. I also replaced INSERT with UPDATE. I appreciate the help, and I've been referring to that w3schools link a lot lately.

It's now as follows:

Code: [Select]

if not sql.TableExists( "server_flags" ) then
    sql.Query( "CREATE TABLE IF NOT EXISTS server_flags ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, player INTEGER NOT NULL, initialflag INTEGER NOT NULL );" )
end

-- Flags
local Flags= {
"testflag1",
"testflag2",
"another_flag"
}

-- Add flags to the player if they don't already have them when they join the server
function ServerFlags( ply )
   
    local uid = ply:UniqueID()
    -- Make sure the player has a row in the table - blatantly plagiarized from UTime
    local row = sql.QueryRow( "SELECT initialflag FROM server_flags WHERE player = " .. uid .. ";" )
    if not row then
        sql.Query( "INSERT into server_flags ( player, initialflag ) VALUES ( " .. uid .. ", " .. 1 .. " );" )
    end
   
    -- Cycle through the list of flags
    for k,v in pairs(Flags) do
        local flag = sql.Query("SELECT " .. Flags[k] .. " FROM server_flags WHERE player = " .. uid .. ";")
        -- If the player doesn't have this flag in their row in the SQL db, add it
        if not flag then
            -- Add the column
            sql.Query("ALTER TABLE server_flags ADD " .. Flags[k] .. " INTEGER;")
            -- Give the flag a value
            sql.Query( "UPDATE server_flags SET " .. Flags[k] .. " = " .. 0 .. " WHERE player = " .. ply:UniqueID() .. ";" )
        end
    end     
end
hook.Add( "PlayerInitialSpawn", "ServerFlags", ServerFlags)


Title: Re: Possible to add new columns to an existing SQL table?
Post by: MrPresident on January 11, 2012, 09:31:31 AM

Good deal, glad I could be of help.

w3schools is an amazing resource, you should be sure to use it. You will learn a LOT.