[DarkLored]

Quote:

Originally Posted by Biesmen I do highly recommend you to normalize your database, but it won't solve the problem.. Your column "origloc" doesn't have a default value, so it's NULL at this moment.

But origloc is a string which I have specified in the database to not be null, none of my other databases experience this issue other than this one. I had this issue before but couldn't figure out what causes it.

Here's a database that works.

pawn Код:

stock LoadDatabase()
{
    new Query[4000];
    Database = db_open("scrp.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), score INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "money INTEGER DEFAULT 500 NOT NULL, admin INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL, kills INTEGER DEFAULT 0 NOT NULL, deaths INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "passedquiz INTEGER DEFAULT 0 NOT NULL, skin INTEGER DEFAULT 0 NOT NULL, age VARCHAR(200), birthplace VARCHAR(100) , race INTEGER DEFAULT 0 NOT NULL, culture VARCHAR(100),", sizeof(Query));
    strcat(Query, "userposx FLOAT DEFAULT 0.0 NOT NULL, userposy FLOAT DEFAULT 0.0 NOT NULL, userposz FLOAT DEFAULT 0.0 NOT NULL,", sizeof(Query));
    strcat(Query, "userrota FLOAT DEFAULT 0.0 NOT NULL, faction INTEGER DEFAULT 0 NOT NULL, factionrank INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "interior INTEGER DEFAULT 0 NOT NULL, vw INTEGER DEFAULT 0 NOT NULL, bankcash INTEGER DEFAULT 2000 NOT NULL, playinghours INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "paycheck INTEGER DEFAULT 0 NOT NULL, savings INTEGER DEFAULT 0 NOT NULL )", sizeof(Query));
    db_query(Database, Query);
}

@Vince by any chance is there a max amount of columns you can have per table? I am also having trouble normalizing the database, I don't really understand how it works.

I tried following your tutorial and another SQLite Foreign Key support to understand the process and made this up.

pawn Код:

LoadDatabase()
{
    new Query[2000];
    Database = db_open("scrp.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), level INTEGER DEFAULT 1 NOT NULL, money INTEGER DEFAULT 5000 NOT NULL,", sizeof(Query));
    strcat(Query, "bank INTEGER DEFAULT 10000 NOT NULL, savings INTEGER DEFAULT 0 NOT NULL, savingson INTEGER DEFAULT 0 NOT NULL, skin INTEGER DEFAULT 0 NOT NULL, keys INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "job INTEGER DEFAULT 0 NOT NULL, hours INTEGER DEFAULT 0 NOT NULL, paycheck INTEGER DEFAULT 0 NOT NULL, interior INTEGER DEFAULT 0 NOT NULL, vw INTEGER DEFAULT 0 NOT NULL, posx FLOAT DEFAULT 0.0 NOT NULL, posy FLOAT DEFAULT 0.0 NOT NULL, posz FLOAT DEFAULT 0.0 NOT NULL,", sizeof(Query));
    strcat(Query, "posa FLOAT DEFAULT 0.0 NOT NULL, admin INTEGER DEFAULT 0 NOT NULL, tester INTEGER DEFAULT 0 NOT NULL, upgradep INTEGER DEFAULT 0 NOT NULL, health FLOAT DEFAULT 100.0 NOT NULL, armor FLOAT DEFAULT 0.0 NOT NULL, duty INTEGER DEFAULT 0 NOT NULL, vip INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "isswat INTEGER DEFAULT 0 NOT NULL, isdetective INTEGER DEFAULT 0 NOT NULL, age INTEGER DEFAULT 0 NOT NULL, origin VARCHAR(30) NOT NULL)", sizeof(Query));
    strcat(Query, "CREATE TABLE IF NOT EXISTS FactionMember (userid INTEGER, factionid INTEGER, rank INTEGER, FOREIGN KEY(userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE)", sizeof(Query));
    db_query(Database, Query);
    print(Query);
}

Would this new database workout? could you explain how the factionid and rank updates if the only reference is the player id?