ALTER TABLE Player RENAME TO Player_old
CREATE TABLE Player (name TEXT,pass TEXT,ipv4 INTEGER,alive INTEGER,karma INTEGER,regdate INTEGER,lastlog INTEGER,spawntime INTEGER,spawns INTEGER,warnings INTEGER,aimshout TEXT,gpci TEXT)
INSERT INTO Player (name,pass,ipv4,alive,karma,regdate,lastlog,spawntime,spawns,warnings,aimshout,gpci) SELECT DISTINCT lower(name),pass,ipv4,alive,karma,regdate,lastlog,spawntime,spawns,warnings,aimshout,gpci FROM Player_old
1) There is no need to destroy and recreate the table to remove duplicates.
2) That query is incomplete. 3) Just set a UNIQUE column. |
ALTER TABLE Player RENAME TO Player_old
CREATE TABLE Player (name TEXT,pass TEXT,ipv4 INTEGER,alive INTEGER,karma INTEGER,regdate INTEGER,lastlog INTEGER,spawntime INTEGER,spawns INTEGER,warnings INTEGER,aimshout TEXT,gpci TEXT)
INSERT INTO Player (name,pass,ipv4,alive,karma,regdate,lastlog,spawntime,spawns,warnings,aimshout,gpci) SELECT DISTINCT lower(name),pass,ipv4,alive,karma,regdate,lastlog,spawntime,spawns,warnings,aimshout,gpci FROM Player_old
CREATE TABLE Player (name TEXT, ...)
CREATE TABLE Player ( name TEXT NOT NULL UNIQUE COLLATE NOCASE, pass TEXT NOT NULL, ipv4 INTEGER NOT NULL, alive INTEGER DEFAULT 0, karma INTEGER DEFAULT 0, regdate INTEGER NOT NULL, lastlog INTEGER NOT NULL, spawntime INTEGER DEFAULT 0, spawns INTEGER DEFAULT 0, warnings INTEGER DEFAULT 0, aimshout TEXT DEFAULT "Drop your weapon!", gpci TEXT NOT NULL, active INTEGER DEFAULT 1 )
Firstly, SELECT DISTINCT lower(name) is a bad method for what you're doing. Declare the column using a case-insensitive collation and a UNIQUE constraint.
Instead of your current table declaration: Код:
CREATE TABLE Player (name TEXT, ...) Код:
CREATE TABLE Player ( name TEXT NOT NULL UNIQUE COLLATE NOCASE, pass TEXT NOT NULL, ipv4 INTEGER NOT NULL, alive INTEGER DEFAULT 0, karma INTEGER DEFAULT 0, regdate INTEGER NOT NULL, lastlog INTEGER NOT NULL, spawntime INTEGER DEFAULT 0, spawns INTEGER DEFAULT 0, warnings INTEGER DEFAULT 0, aimshout TEXT DEFAULT "Drop your weapon!", gpci TEXT NOT NULL, active INTEGER DEFAULT 1 ) The UNIQUE constraint will also create the index on the name column for you implicitly, so you don't have to declare it separately. This way, you will never risk duplicate entries. You should modify the table declaration at https://github.com/Southclaw/Scaveng...counts.pwn#L96 |