[Jonny5]

basically i want to start a small discussion on good methods in sqlite. (a long winded one most likely)
Im hoping this topic ends up with some good general tips and tricks
to help with using sqlite. At the same time i will be seeking info for my own knowledge
but feel free to inquire/posts about different concepts.

for example..
Returning a db row as a string with a delimiter

I find myself using strcat to build a string then i do checks with sscanf to ensure data integrity.
Here is a small piece of code i use to load map icons from a database when a player connects to the server.

pawn Код:

forward LoadMapIconsDb(DB:db);
public LoadMapIconsDb(DB:db)
{
    new
        DBResult:Result,
        mLoaded
    ;
    Result = db_query( db, "SELECT * FROM `GMAPICONS`" );
    for(new a,r=db_num_rows(Result);a<r;a++)
    {
        new str[128],str2[32];
        db_get_field_assoc(Result,"X",str2,32);
        strcat(str,str2);
        strcat(str," ");
        db_get_field_assoc(Result,"Y",str2,32);
        strcat(str,str2);
        strcat(str," ");
        db_get_field_assoc(Result,"Z",str2,32);
        strcat(str,str2);
        strcat(str," ");
        db_get_field_assoc(Result,"TYPE",str2,32);
        strcat(str,str2);
        strcat(str," ");
        new Float:mX, Float:mY, Float:mZ, mtype;
        if(!sscanf(str, "fffi", mX, mY, mZ, mtype))
        {
            mLoaded++;
            CreateDynamicMapIcon(mX, mY, mZ, mtype, 0, -1,-1,-1, MAP_ICON_STREAM_DIS);
        }
        db_next_row(Result);
    }
    db_free_result(Result);
    printf("Loaded %d map icons from: database",mLoaded);
    return 1;
}

note here my delimiter is just a space and my string sizes are not yet optimized.
I also am not checking the data for integrity by using some Optional SSCANF Specifiers and providing default values.
But overlooking that, how can i improve this? I have nothing to compare it to
and know that this code just takes too long to write. I know its small in this example but
take for example a player table with 50 fields of data. I dont think the code is slow, but im sure speed could be
improved, but even thats not what im asking, I want to make it less daunting to write in general.
I actually dont use this method anymore
I am now looping threw the fields and strcat them that way,
although I dont think this is better for speed.
so I ask you to post your (method) of doing this.

this brings me to my next piece of code.

pawn Код:

public CreateDbTable(DB:db, tbl_name[], bool:PrimaryID,...)
{
    new
        str_Query[1024],
        str[256];
    strcat(str_Query,"CREATE TABLE IF NOT EXISTS `");
    strcat(str_Query,tbl_name);
    strcat(str_Query,"` (");
    if (PrimaryID) strcat(str_Query,"`ID` INTEGER PRIMARY KEY AUTOINCREMENT,");
    for (new i = 3,i_n_a=numargs(); i < i_n_a ; ++i)
    {
        if ((i>3)) strcat(str_Query,",");
        //credits to Meta for the code to get a string from the argument.
        //https://sampforum.blast.hk/showthread.php?pid=1426694#pid1426694
        for(new _argcount, _temparg[20], _fullarg[256]; ; _argcount++)
        {
            format(_temparg, sizeof(_temparg), "%s", getarg(i, _argcount));
            if(!strlen(_temparg)) { format(str, sizeof(str), "%s", _fullarg); break; }
            format(_fullarg, sizeof(_fullarg), "%s%s", _fullarg, getarg(i, _argcount));
        }
        strcat(str_Query," ");
        strcat(str_Query,str);
        strcat(str_Query," ");
    }
    strcat(str_Query,")");
    db_query(db,str_Query);
    return 1;
}

what i have here is a function for creating tables if they don't already exist.
With my skill level in pawn it took me a bit of reading to get this one working the way it does.
I have others like it for instance to set a group of fields with a group of values.
I know someone will look at this and say WTH did you do that when you can do it this way.....
Im looking for your response.


anyone interested in using the function above it is used like this

pawn Код:

CreateDbTable
    (
        Database,       //database ref
        "GMAPICONS",    //table name
        true,              //create an index field (ID)
        //table fields
        "`X` REAL",
        "`Y` REAL",
        "`Z` REAL",
        "`TYPE` INTERGER"
    );

okay and on to the next question
and this is not tied to sqlite directly but could be applied.

i have some other publics that the headers look like this

pawn Код:

//returns int
forward GetDbField(DB:db, tbl_name[], field[], clause[], set[]);
//returns float
forward Float:GetDbFloatField(DB:db, tbl_name[], field[], clause[],set[]);
//sets a string by ref.
forward GetDbStrField(DB:db, tbl_name[], field[], clause[], set[], str[]);

these functions are pretty much all the same with the exception of the data types.
I would much rather one function to handle all 3 types, and possible more.
and examples of that in any context would help.





i have more questions but they have slipped my mind atm so i will edit with them later but lets start with this,
and if anyone has some useful tips/methods to share please do so.