Thankyou Dennis. You have been a fantastic help. I will be sure to put credits in my script to you all.
I am hoping to release this ranking system after its run for a month or so. Eventscripts is free and so are the scripts that get released. I am sure a lot of Counter Strike Source game server admins will love the speed SQLite had given this ranking system. :jumping: Thanks again. Dennis Cote wrote: > > Pejayuk wrote: >> Thanks Dennis, >> I get the gist of it. >> >> I am getting duplicate records though using insert or replace. >> I was using update before. >> >>>From the TOP. >> >> When my script loads it does the following fine. >> //Open static database. >> es_xsql open stats_static |zmstats >> //Open memory database. >> es_xsql open stats_memory ":memory:" > > There is no need to open the memory database. SQLite will implicitly > open the memory database when you attach to it. In fact your > stats_memory database is a second independent memory database which is > unused by the rest of your processing. > >> //Attach memory database to static. >> es_xsql query stats_static "ATTACH ':memory:' as stats_memory;" >> //Create table in stats_static attached memory database. >> es_xsql query stats_static "CREATE TABLE stats_memory.stats (rank int(6), >> steamid varchar(25), playername varchar(80), zkills int(6), hkills >> int(6), >> deaths int(6), degrade int(6), prounds int(6), lastvisit int(20), points >> NUMERIC(12));" >> >> >> When my script unloads it does the following fine. >> //unload all >> es_xsql query stats_static "DROP TABLE stats_memory.stats;" > > There is no need to drop tables in memory databases. They simply > disappear when the memory is released. > >> es_xsql query stats_static "DETACH stats_memory" >> es_xsql close stats_memory > > Also, no need to close the memory database. It will be close > automatically when it is detached. > >> es_xsql close stats_static >> >> >> As the players play on the server the fields in stats_memory.stats are >> updated with kills and deaths etc. >> After the map finishes my script needs to update the stats_static stats >> table with the records in stats_memory.stats, but instead of updating the >> records it creates new records. >> >> The unique thing about each row is the steamid of the player both in >> stats_static stats and stats_memory.stats. This is specific to the >> players >> account. > > Then this should be the primary key in both tables. Try this instead. > > CREATE TABLE stats_memory.stats ( > rank int(6), > steamid varchar(25) primary key, > playername varchar(80), > zkills int(6), > hkills int(6), > deaths int(6), > degrade int(6), > prounds int(6), > lastvisit int(20), > points NUMERIC(12) > ); > >> I need these records to update or replace the existing record in >> stats_static stats table with the corresponding record in >> stats_memory.stats >> or if the records doesn't exist, it needs to insert a new record. >> > > When doing and INSERT OR REPLACE, the record is replaced if the primary > key already exists in the table, otherwise it does an insert with the > new primary key. > >> >> This is the query that I believe is causing me the problem. >> >> //Update stats_static stats table with records from stats_memory.stats >> table. >> es_xsql query stats_static "INSERT OR REPLACE into main.stats select * >> from >> stats_memory.stats;" >> >> >> I believe the problem is with the sql statement part as follows. >> >> INSERT OR REPLACE into main.stats select * from stats_memory.stats; >> >> This should somehow using the steamid field in both tables as a way to >> choose if it should insert or replace. >> If the steamid from stats_memory.stats isn't in stats_static stats table >> then it should insert. >> If the steamid from stats_memory.stats is in stats_static stats table >> then >> it should replace or update it. > > This will all happen automatically if you make steamid the primary key > because a primary key must be unique in each record of a table, and the > replace option happens when a new record would violate a uniqueness > constraint. See http://www.sqlite.org/lang_insert.html and > http://www.sqlite.org/lang_conflict.html for additional details. > >> >> >> I hope this is a better explanation than before. > > Much clearer. :-) > > HTH > Dennis Cote > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Update-static-database-with-records-from-memory-database.-tp18187288p18268001.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
