Author Topic: sqlite_next_row, null, error, etc  (Read 366 times)

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
sqlite_next_row, null, error, etc
« on: December 12, 2016, 12:01:38 am »
EDITED AND SOLVED :)

So the wiki was of no help to me... http://liberty-unleashed.co.uk/LUWiki/Squirrel/Server/Functions/SQLite/sqlite_next_row

I tested in various ways, Ways that should 100% work and it never did : :(

Code: [Select]
code removed

The Edit

So what I ended up doing was comparing VCMP wiki with ours. With some common sense and configuring I was capable of finding out how to do so :)

Code: [Select]
function onScriptLoad()

    local query = ::sqlite_query( g_database, "SELECT * FROM Accounts" )
    local string = ::sqlite_column_data(query, 0); // Stores the first name.
     
    while (sqlite_next_row(query)) // Gets the second name when the loop starts and continues getting the next row after that until no rows are left.
    {
         
        string += ", " + ::sqlite_column_data(query, 0);
    }
    string += ".";
    print("" + string); // Returns the string containing the user account names.
}
« Last Edit: December 12, 2016, 12:59:14 am by Mötley »

Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #1 on: December 12, 2016, 12:04:32 pm »
Yeah, I think using SQlite is a bad idea for an LU server right now.

There's no proper sql injection prevention, and I've found that the database I/O is dreadfully slow.
My local test server would hang for 10 seconds when updating integers in 6 columns across 3 rows, using three queries (one for each row).
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #2 on: December 12, 2016, 02:33:40 pm »
Oh... D:

Well at-least this is only a study and learning experience. 

I intended to run this script locally one day. There is a shit load of work I was going to add..
I'm tempted to stop as this is the begging D:


Code: [Select]
SQL_Accounts <- array( 0, null )

// Store the accounts in a class so we wont hammer the query when players join
class SQL_Account_Class
{
Name = "";
Password = "";
IP = "";
Autologin = 0;
}

function onScriptLoad()
{

        local iCounter = 0; // Add the counter
        local query = ::sqlite_query( g_database, "SELECT * FROM Accounts" ) // Grab the query
        local string = ::sqlite_column_data(query, 0); // Stores the first name.

        while (sqlite_next_row(query))
{

SQL_Accounts.push(iCounter);
SQL_Accounts[ iCounter ] = SQL_Account_Class();
SQL_Accounts[ iCounter ].Name = ::sqlite_column_data(query, 0);
SQL_Accounts[ iCounter ].Password = ::sqlite_column_data(query, 1);
SQL_Accounts[ iCounter ].IP = ::sqlite_column_data(query, 2);
SQL_Accounts[ iCounter ].Autologin = ::sqlite_column_data(query, 3);

/* Error check the query */
                        print("" + SQL_Accounts[ iCounter ].Name)
print("" + SQL_Accounts[ iCounter ].Password)
print("" + SQL_Accounts[ iCounter ].IP)
print("" + SQL_Accounts[ iCounter ].Autologin)
iCounter ++;

}
        ::sqlite_free( query ); // Free up the query to prevent memory leaks
}


Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #3 on: December 14, 2016, 11:29:33 pm »
It seems like you're on the right track with that.
You use an array to store data ... It keeps you from having to query the database a lot, which is good.

Theres only one thing I recommend, but it's just a suggestion.
Your method is fine, but I'm just gonna show you an alternative:
Remove the iCounter, and store the data from each row in a variable created with the SQL_Account_Class, then push the instance onto the SQL_Accounts array. Something like this:
Code: [Select]
local pAccountData = false;

while ( sqlite_next_row( query ) ) {

    pAccountData = SQL_Account_Class ( );
    pAccountData.Name = ::sqlite_column_data( query , 0 );
    pAccountData.Password = ::sqlite_column_data( query , 1 );
    pAccountData.IP = ::sqlite_column_data( query , 2 );
    pAccountData.Autologin = ::sqlite_column_data( query , 3 );
   
    SQL_Accounts.push ( pAccountData );

}
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #4 on: December 15, 2016, 01:14:37 am »
Well firstly I would like to add that I Updated that method as I realized that... I was not garbing the first account LMAO.

Code: [Select]
// Load the accounts into the class
function Authorize::Accounts( )
{
    local iCounter = 0; // Add the counter
    local query = ::sqlite_query( g_AccountDB, "SELECT * FROM Accounts" ) // Grab the query
    local string = ::sqlite_column_data(query, 0); // Stores the first name.
               
    while (string)
    {
        SQL_Accounts.push(iCounter); // Increase the array Value
        SQL_Accounts[ iCounter ] = SQL_Account_Class();
        SQL_Accounts[ iCounter ].Name = ::sqlite_column_data(query, 0);
        SQL_Accounts[ iCounter ].Password = ::sqlite_column_data(query, 1);
        SQL_Accounts[ iCounter ].IP = ::sqlite_column_data(query, 2);
        SQL_Accounts[ iCounter ].Autologin = ::sqlite_column_data(query, 3);   
               
        iCounter ++;
        string = sqlite_next_row(query);
    }
    sqlite_free( query );
}

I would also Like to say even though I do not want to I have intended to add the iCounter to the query "Accounts"
or to that Class, Not sure yet..

As Well I thought I should add the bool statement from the normal class
"I am assuming you seen my script in the PM".

The reason for adding the iCounter to the query or the class
, Once I find the players accounting data I will no longer need to loop threw, Which I would like to assume would prevent data loss etc.

The reason for the bool statement is, If the player Intends to change autologin or there password I can add a check to find every account that the bool returned true, Then update the query, We will assume I will do this every hour. And if onScriptUnload() can catch it on time.



With saying this, How does that script work? I thought
Code: [Select]
SQL_Accounts.push Must be called first? As I have the array starting from 0, If the array starts from zero it is even with the number of current accounts. So with saying that wont I take a possibility of overwriting data from the last account class if it is not called first? And what is with the bool???

Sorry, I am sure it is more efficient I just really do not get it atm..

Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #5 on: December 15, 2016, 01:33:41 am »
I would also Like to say even though I do not want to I have intended to add the iCounter to the query "Accounts"
or to that Class, Not sure yet..
I don't know what you mean by this.

As Well I thought I should add the bool statement from the normal class
"I am assuming you seen my script in the PM".
No, sorry, I haven't had a chance to look it yet. I'll take a look after I submit this post.

The reason for adding the iCounter to the query or the class, Once I find the players accounting data I will no longer need to loop threw, Which I would like to assume would prevent data loss etc.
Well, there's no reason you need to use the iCounter for that. It doesn't distinguish the account data in the database from the data in your SQL_Accounts array. I would recommend using a primary key field in your accounts table within the database. You can easily reference accounts using that, and it's much faster than using an account name because a query that checks an integer is faster than checking for an account name. Plus, it provides a unique identifier for each account, so if you want to change a name, you don't have to change every table that references an account by it's name. You only need to change the ID.

With saying this, How does that script work? I thought
Code: [Select]
SQL_Accounts.push Must be called first? As I have the array starting from 0, If the array starts from zero it is even with the number of current accounts. So with saying that wont I take a possibility of overwriting data from the last account class if it is not called first? And what is with the bool???

Sorry, I am sure it is more efficient I just really do not get it atm..
No, the push function will add a new slot to the end of an array, and put whatever you pass as an argument as the value of the new slot.

Why do you need to load every account? That is the dumbest idea I've ever heard. When the script loads, create an array the same size as the max amount of players to hold each player's data. When a player connects, grab only their account from the database, create an instance of SQL_Accounts_Class, put their account data into it and put the instance into the array. Then, when they disconnect, take the data from the player's slot in the array, and save it to the database to only their account.
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #6 on: December 15, 2016, 01:55:34 am »
The reason to loading every account when the scripts are loaded...........

I find it to be bad practice to hammer the database on every player join yet /reconnect.. There is so many things wrong with that.

My way only needs to access it one time and it's done. When a player joins find out if the player has an existing account. If it does not exist, register etc..

I find this to be of good practice. I think hammering the database on every player join and reconnect is just terrible. It's bad, Even sounds bad.. So now I would like to assume you see why I did that? There are so many things I am cutting out from when a player joins that could have heavy effects on the server etc..

Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #7 on: December 15, 2016, 02:02:45 am »
Querying the database on every connect and disconnect, and every hour is not bad at all.
Besides, you are accessing the database via a file, not a network socket.

To prevent reconnect spam, just add in a race-attack prevention. Maybe wait for a couple of seconds before actually loading their account and check to see if they're still connected before doing so.

What's gonna happen if you have 100 accounts in your database? How about 500?
Are you seriously going to load all of that into memory on your server? Do you even realize badly your server will hang if you attempt that?
You won't have any memory left to actually run a gamemode.

But whatever, do it as you please. I can't waste my time to provide support to you if you plan on ignoring it because you don't realize how inefficient your methods are.

I assume you did that because you don't realize how ignorant it sounds.
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #8 on: December 15, 2016, 02:15:43 am »
Well at-least this is only a study and learning experience. 


 :) I'm learning and finding different scripting practice.

At the same time.. I think I have learned what I need to know with SQL.
While what I was studying from is outdated and there are more resources out there like

http://tutorialzine.com/2016/01/learn-sql-in-20-minutes/

But I find the resources hear better
http://www.tizag.com/sqlTutorial/

Therefore I do not intend to study SQL anymore.

In all honestly. I really do not like the architecture of SQL...

Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #9 on: December 15, 2016, 02:21:11 am »
SQL in general is my preferred method of storage. SQL is just a language but the types of server options provided for it are awesome. I only deal with SQlite and MySQL. However, I find the SQlite plugin for LU is lacking in terms of performance and ability.

It's easy to store and access data. If you ever learn about relations across tables (and some of the other more advanced features of MySQL), you'll be shitting bricks.
« Last Edit: December 15, 2016, 02:22:53 am by Vortrex »
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #10 on: December 15, 2016, 02:24:13 am »
Any preferred places to study MYSQL? Even if it's outdated..

I will study it sometime hear http://www.tizag.com/mysqlTutorial/

Vortrex

  • Full Member
  • ***
  • Posts: 258
  • Karma: +51/-51
  • Developer for Argonath RPG
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #11 on: December 15, 2016, 02:26:38 am »
It's been a long time since I've "studied" MySQL. My experience for the past several years have been mostly hands-on and actual use, not learning.
Developer for Argonath RPG's LU server.

Mötley

  • Full Member
  • ***
  • Posts: 315
  • Karma: +32/-29
  • Scripter
    • View Profile
Re: sqlite_next_row, null, error, etc
« Reply #12 on: December 15, 2016, 02:31:02 am »
:D okay.

I might study it in February as if I want a scripting/study break.

For now. I am just going to create a log of what I want to do and possibly "Actually create a normal server"...

 

© Liberty Unleashed Team.