Thread Status:
Not open for further replies.
  1. Wulf

    Wulf Community Admin Community Admin Oxide Developer

    Poof! @Nogrod has waved his magic wand and extensions came out. As of Oxide 2.0.314, you're now able to work with MySQL databases directly without the need for webrequests and remote PHP scripts. Each plugin can make use of and control their own database through the extensions. Yay! The extensions have had basic testing, so please use them and let us know if you encounter any issues or have any suggestions for improvement. Enjoy!

    Download: http://oxidemod.org/downloads/oxide-2-for-rust-experimental.714/

    Basic Python example:
    Code (Python):
    def dotnet_iter(coll):
        for p in coll:
            yield (p.Key, p.Value)
    db = mysql.OpenDb("localhost", 3306, "rust", "root", "")
    sql = mysql.NewSql().Select("*").From("test").Where("oink=@0", "walla")
    data = mysql.Query(sql, db)
    for y in data:
        for k, v in dotnet_iter(y):
            print "Col: " + k + " Value: " + v
    More examples to come!
     
  2. Feramor

    Feramor Shack Builder Plugin Developer

    I did some tries yesterday :) You can also call it using Append
    Code (Text):
        local MySqlConnection = mysql.OpenDb("xxx.xxx.xxx.xxx", 3306, "xxxxxxxx", "xxxxxxx", "xxxxxxxx");
        local SqlBuilder = mysql.NewSql();
        SqlBuilder:Append("DELETE FROM oxide_Test WHERE ID = '344';");
        mysql.Delete(SqlBuilder,MySqlConnection);
    --- Double Post Merged, Feb 12, 2015 ---
    Currently avaible methods
    1. OpenDb(string host, int port, string database, string user, string password) to create connection to MySQL server.
    2. NewSql() to create sql strings.
    3. Query(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns collection of pairs
    4. ExecuteNonQuery(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns affected rows count as int
    5. Insert(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns affected rows count as int
    6. Update(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns affected rows count as int
    7. Delete(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns affected rows count as int
    8. ExecuteScalar(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns 1st row's 1 coulmn's value good for COUNT(*) and SUM etc...
    9. First(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns first row of query, You can still use LIMIT 1 at the end of the Query :)
    10. FirstOrDefault(sql, db) where db is the variable you create with OpenDb() and sql is created by NewSql; Returns first row if exist and usually null/nil if doesn't exsit.
    For building SQL I will complate soon
     
    Last edited: Feb 12, 2015
    Wulf likes this.
  3. ColonelAngus

    ColonelAngus Wood Hoarder Plugin Developer

    Just like to encourage users to figure out which solution (sqlite vs mysql) will be better for your plugin. In most cases, sqlite will be plenty for what you need to do.
     
  4. Gonzalo_Cardozo

    Gonzalo_Cardozo Naked Wanderer

    Hi! Anyone has any other examples on MySQL usage for Lua?
     
  5. Wulf

    Wulf Community Admin Community Admin Oxide Developer

  6. Gonzalo_Cardozo

    Gonzalo_Cardozo Naked Wanderer

  7. Wulf

    Wulf Community Admin Community Admin Oxide Developer

    I don't know of any others right now, sorry. I'll see if I can get you some though!
     
  8. Gonzalo_Cardozo

    Gonzalo_Cardozo Naked Wanderer

    I have this:

    Lets assume that PlyID and field are defined already on the code, as I only need to check if the methods used are correct

    Code (Lua):

    local IDs = {}
    local database = mysql.OpenDb("XXXXXXX", 3306, "XXXXXXX", "XXXXXX", "XXXXXXXXX")
    local sql = mysql.NewSql()
    sql:Append("CREATE TABLE IF NOT EXISTS PlayerData(PlyID INTEGER, field INTEGER")
    sql:Append("CREATE INDEX IF NOT EXISTS IDX_PID on PlayerData(PlyID)")
    mysql.ExecuteNonQuery(sql, database)

    local sql2 = mysql.NewSql("SELECT * FROM PlayerData")
    PlayerData = mysql.Query(sql2, database)
     
    for k,v in ipairs(PlayerData) do
        if (v.PlyID == PlyID) then
            IDs[PlyID] = true
        end
    end
    if (IDs[PlyID]) then
        sql:Append("UPDATE PlayerData SET (field=? WHERE PlyID=?)", field, PlyID)
        mysql.Update(sql, database)
    else
        sql:Append("INSERT INTO PlayerData (PlyID, field) VALUES (?,?)", PlyID, field)
        mysql.Insert(sql, database)
    end
     
    Are you able to say if this would or would not work? Would really appreciate the help
     
  9. Мизантроп

    Мизантроп Wood Hoarder

    Code (Text):
    local db = mysql.OpenDb("localhost", 3306, "experimental", "root", "")

    function PLUGIN:cmdName(player,cmd,o)
        local sql = mysql.NewSql():Select("*"):From("db_users"):Where("steam_id="..rust.UserIDFromPlayer(player).."", "")
        local data = mysql.Query(sql, db)
        for entry in each(data) do
            for col in each(entry) do
                print(tostring(col.Key) .. " - " .. tostring(col.Value))
                global.ConsoleSystem.Broadcast("chat.add \"TEST\" \""..tostring(col.Key) .. " - ".. tostring(col.Value).."\"")
            end
        end
       
    end

    function each(o)
       local e = o:GetEnumerator()
       return function()
          if e:MoveNext() then
            return e.Current
         end
       end
    end
     
  10. Gonzalo_Cardozo

    Gonzalo_Cardozo Naked Wanderer

    Is anyone able to show an example of how to create a table using this methods?

    I tried this
    Code (Lua):
    local database = mysql.OpenDb("XXXXXXX",3306,"XXXXXXX","XXXXXX","XXXXXXXXX")
    local sql = mysql.NewSql()
    sql:Append("CREATE TABLE IF NOT EXISTS PlayerData(PlyID INTEGER, field INTEGER")
    sql:Append("CREATE INDEX IF NOT EXISTS IDX_PID on PlayerData(PlyID)")
    mysql.ExecuteNonQuery(sql, database)
    But it doesn't seem to work
     
    Last edited by a moderator: Mar 17, 2015
  11. Ryan Skidmore

    Ryan Skidmore Developer

    Have you tried

    Code (Lua):
    local database = mysql.OpenDb("XXXXXXX",3306,"XXXXXXX","XXXXXX","XXXXXXXXX")
    local sql = mysql.NewSql()
    sql:Append("CREATE TABLE IF NOT EXISTS PlayerData(PlyID INTEGER, field INTEGER;")
    sql:Append("CREATE INDEX IF NOT EXISTS IDX_PID on PlayerData(PlyID;")
    mysql.ExecuteNonQuery(sql, database)
     
  12. M@CH!N3

    M@CH!N3 Wood Hoarder Plugin Developer

    we got the server to create the table, and add info to the DB so far but right now we are getting:

    File: *****.lua Line: 130 The CommandText property has not been properly initialized.:

    line 129: local sql2 = mysql.NewSql()
    line 130: PlayerData = mysql.Query(sql2, database)
    --- Double Post Merged, Mar 18, 2015, Original Post Date: Mar 17, 2015 ---
    ok we got way past the part I last posted however we having an issue with it receiving the SteamID from the database, it's changing the value from SQL to server for some reason yet it posts fine.

    example: [Oxide] 12:09 PM [Info] Stored ID = 7.6561198009428e+016 - Your ID: 76561198009428084

    because of this we can't get it to compare online users with stored users. Any idea why its changing the value when receiving?
     
  13. Mughisi

    Mughisi Grenade Master Community Mod Oxide Developer

    Lua doesn't support int64, you can obtain the string value of the id by using the function that was made available for it:
    Code (Text):
    rust.UserIDFromPlayer(player)
    So you will need to store it as a string in the database if you want to be able to work with it.
     
  14. M@CH!N3

    M@CH!N3 Wood Hoarder Plugin Developer

    We got it all working now however, there are 2 files needed from unity to make it work on GS servers:

    I18N.dll
    I18N.West.dll

    these files need to be in the RustDedicated_Data/Managed folder in order for it to use certain SQL functions
     
  15. mira-krasavchik

    mira-krasavchik Naked Wanderer

    Please give an example of code with SQLite, for example to make the name of the user connected to the database SQLite lunguage - C#?
    An example, please. Then I'll understand everything.
     
  16. seanterris

    seanterris Wood Hoarder

    @mira-krasavchik i wanted to write one for you. the problem is, to use sqlite you need to save your database file somewhere, and I've seen it mentioned on those forums that we have NO File I/O access. one of the devs needs to clarify how we can create and handle sqlite DB objects.

    attached code SHOULD work if Oxide let us create the database file, right now it throws exceptions on FileStream operations.

    btw, if you can, try getting mysql on your server, sqlite has significant disadvantages afaik.
     
  17. Mughisi

    Mughisi Grenade Master Community Mod Oxide Developer

    Instead of creating the database as you're attempting to do and creating the sqlConnection, you simply use the OpenDb method that's available in the SQLite extension: https://github.com/OxideMod/Oxide/blob/master/Oxide.Ext.SQLite/Libraries/SQLite.cs
     
  18. inadequatemonkey

    inadequatemonkey Naked Wanderer

    does anyone know what the 3rd Arg for .Query is meant to be in Python?

    Code (Text):
    self.database = mysql.OpenDb("host", port, "db", "user", "pass", self.master.Plugin)
    sql = mysql.NewSql().Select("*").From("users").Where("userID="+player.userID.ToString())
    data = mysql.Query(sql , self.database)
     
    gives me:

    Code (Text):
    RuntimeError: Failed to call OnPlayerRespawned (ArgumentTypeException: Query() takes exactly 3 arguments (2 given))
     
  19. Wulf

    Wulf Community Admin Community Admin Oxide Developer

    The examples in this thread are a bit outdated. See MySQL Extension | Oxide.
     
  20. inadequatemonkey

    inadequatemonkey Naked Wanderer

    thanks a billion dude, don't know how i didnt find this in my hours of searching!
     
Thread Status:
Not open for further replies.