Problem with sqlite to mysql pointshop converters

I’ve tried 3 different methods now, but I’ve gotten nothing. Well, not quite nothing, but definitely not what’s needed. I am trying to convert my server’s sv.db to output a convert.sql file or whatever you would like to name it, with each row ready to be imported to a mysql database like this. Now, all methods have successfully converted, however each one gives up around half way through.
So far, the farthest I’ve gotten is with this method:
GitHub - Svenskunganka/GDBC: GDBC (GMod Database Converter) is a tool written in PHP that converts some GMod addons from SQLite to MySQL. - Converted about 5000 of the 9000 rows

Second farthest:
Converts sqlite database for pointshop to mysql (outputs sql file) · GitHub - Converted about 600 of the 9000 rows

And last:
Custom script I received from a friend that apparently Tommy228 made. You place it on your server, and type a console command, it states that its running its process, server freezes, its done, etc. The script looks pretty straight forward and looks like it could get the job done, sadly it stops and says complete at around 400 of the 9000 rows.

I’m at a loss here, and I don’t know what other tools to try. I have to convert all 9000 rows of data, as it’d probably be preferred that I don’t end up dropping anyone’s points/data/items. Does anyone have any ideas or tools that can be used for larger-scale conversions from sqlite->mysql?

Take a look at this: https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua.html

The way the data is stored is annoying, however, I’d recommend firefox addon: SQLite Manager… It should have no trouble exporting data ( but I’d recommend re formatting how pdata is stored, which is the table I explain in my tutorial )

I’m not sure about the addon for firefox as I’ve never seen it, but I do have a program that I don’t remember the exact name for other than “sqlite db manager” its a great free program and lets you do really anything to it. It only exports to csv, though. The main problem here is that sqlite pointshop uses a much different layout from mysql pointshop, so that is why this process is strange and I can’t just export it. I’ll look into your script although it looks slightly confusing. I’ll let you know how that goes later. Thanks.

There’s some old code here for taking points from the pdata provider into a mysql database:

https://github.com/adamdburton/pointshop-mysql/blob/210d616fb0f06545cb671480255cbc5c00e61377/lua/autorun/server/pointshop_mysql.lua#L172-L209

You could print out each query instead of running them and then paste them into a db admin tool like phpmyadmin.

I believe this is using an older version of sqlite syntax for pointshop or no? Or is it me thats using an older version? My pointshop sqlite tables use simply just uniqueid/points/items under no specific table other than playerpdata. The script says in console that its unable to select from pointshop_items and points of course since they dont exist. What would be a way around this?

If you’re only using _p:SetPData and _p:GetPData then my example will grab all of the information and output it to SQL insert statements. It’ll change the columns though ( because storing data like that isn’t great ) so you’d need to use UniqueID to query the table and populate the rest of the fields until all are full but then you can use SteamID without needing to CRC SteamId to get UniqueID…

Well, I’ve been going at this for a while now and so far the closest I’ve gotten is importing an .sql with all the "INSERT INTO"s for pointshop. It successfully moved about half of the people’s points, but dropped all items. I also tried exporting an sql file straight from my entire sv.db. I deleted anything not to do with playerpdata, and I replaced all lines that say playerpdata with pointshop_data. When importing this of course, it does not work. It’s missing an entire column. Items and points are separated in sqlite, but together in mysql. I am still at a loss of how to convert this. Is there any further help I can get?

With the backwards way of how pdata stores data ( as outlined in my tut ), you have to rewrite how you store data and repopulate it slowly but surely as clients join…

I wrote a prepared statement generator / sql query builder if you have any issues with writing SQL…

Example:


local _q = query:New( );
_q:SetType( QUERY_INSERT || QUERY_UPDATE || QUERY_SELECT ); -- select is default so this function only needs to be called if using insert or update...
_q:AddTable( _table_string, _column_as_string_or_columns_as_table, _column_data_for_insert_or_update ); -- where arg 1 is simply the table name, 2 can be string or table, and 3 is only used for insert or update whereby the data MUST MATCH 2 so if 2 is table then 3 is table, if 2 is string 3 is just data...
_q:AddWhere( _table, _column, _value ); -- Adds WHERE statement...
_q:AddLimit( 1 ); -- typically used when updating data so a mistake won't update all rows if you forget a WHERE...	

then you call it…


DATABASE:Query( _q:Build( ), function( _data ) ... end );	

It also supports joins ( add both tables, then use _q:AddJoin( ENUM_Type_of_join, _table1, _table2, _column, _column_for_table2 ); – where _column_for_table2 only needs to be used if _column isn’t the same for both tables )…

This makes queries super easy for someone just starting out with SQL ( hopefully; the new system will allow you to generate a full database with tables, columns, and associative data by creating a Lua table )…

Examples in my game-mode:


			// Character Loading
			local _q = query:New( );
			_q:AddTable( DB_ACCOUNTS_TABLE, DB_ACCOUNTS_COLUMNS );
			_q:AddTable( DB_CHARACTERS_TABLE, DB_CHARACTERS_COLUMNS );
			_q:AddJoin( QUERY_JOIN_INNER, DB_ACCOUNTS_TABLE, DB_CHARACTERS_TABLE, "account_id" )
			_q:AddWhere( DB_ACCOUNTS_TABLE, "account_steamid", _p:SteamID( ) );
			DATABASE:Query( _q:Build( ), function( _row )
				local _count = table.Count( _row );
				if ( _count > 0 ) then
					debug.print( "DatabasePlayerInfo", "Characters Loaded: ", _row );
					networking:SendToClient( "AccountCharacters", _p, _count, _row );
				else
					debug.print( "DatabasePlayerInfo", "No Characters: ", _p );
					networking:SendToClient( "AccountCharacters", _p, 0, { } );
				end
			end );
	

Showing this so you can see the data used… So you can see account_id is used to link accounts with characters. character_id is used throughout the rest of the db to link items, vehicles, etc… to characters. Bans are account-id based…


local _prefix = "cc_";

DB_ACCOUNTS_TABLE = _prefix .. "accounts";
DB_CHARACTERS_TABLE = _prefix .. "characters";

DB_ACCOUNTS_COLUMNS = { "account_id"; "account_steamid"; "account_steamid64"; "account_steamid3"; "account_name"; "account_level"; "account_permissions"; "account_rubies"; "account_connects"; "account_playtime"; "account_email"; "account_birthday"; "account_first_login"; "account_last_login"; };

// Old structure...
DB_CHARACTERS_COLUMNS = { "character_id"; "account_id"; "character_admin"; "character_first_name"; "character_last_name"; "character_gender"; "character_model"; "character_weight"; "character_hunger"; "character_bladder"; "character_bowel"; "character_cleanliness"; "character_health"; "character_stamina"; "character_appearance"; "character_cash"; "character_bank"; "character_deaths"; };


	

ALSO, all data is properly escaped. You just input it, and it handles it all… The uppercase vars are strings / tables containing the rows I may typically request or update data to…

After SQLite db is moved into a new db and restructured ( for pdata table at least) then this may help or it may make things harder…

But, add me on Steam and show me your sqlite db and I’ll see about maybe writing a script like my example to restructure and extract the data for you…

[editline]17th March 2015[/editline]

We got it done!

Based on: https://github.com/adamdburton/pointshop-mysql/blob/master/pointshop.sql

I finished: https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_pointshop_to_mysql.lua.html - https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_pointshop_to_mysql.lua from the original https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua.html - https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua

Basically, I removed the print statements ( OP had 60MB file with around 10k rows of pdata data to sift through ). I added a timer ( took 5, 6 and 7 seconds for 3 tries just to test it so average of 1 sec per 10mb of data ) for the process. I added an output_columns table which converted string.lower PS_Points and PS_Items to “points” and “items” to match “pointshop_data” table and I ignored any other data…

The way my converter works is by building a table where uniqueid is the key in the main table and generated a new table for any columns / data to be output, then each piece of data used column as key then data as value so there were rows with data from other mods OP didn’t want… So, 3 or 4 rows for 1 uniqueid player with data as: [] = became tab[ uid ] = { [ = ]+ };

The db was 60MB, 10k lines for pdata, the finished .txt was 600kb… ~6 seconds average on my pc ( after auto-refresh which may add time, should’ve used concommand, but whatever )…

Hopefully this tool helps anyone else that wants to convert pointshop from sv.db to MySQL…

Example output ( uids removed for meh sake ):


INSERT INTO pointshop_data SET uniqueid='0111111111', points='120';
INSERT INTO pointshop_data SET uniqueid='2222222222', points='630';
INSERT INTO pointshop_data SET uniqueid='3333333333', points='170';
INSERT INTO pointshop_data SET uniqueid='4444444444', items='{"duncehat":{"Equipped":true,"Modifiers":[]},"zombiefast":{"Equipped":true,"Modifiers":[]},"jumppack":{"Equipped":true,"Modifiers":[]}}', points='820';
INSERT INTO pointshop_data SET uniqueid='5555555555', points='530';
INSERT INTO pointshop_data SET uniqueid='6666666666', points='50';
INSERT INTO pointshop_data SET uniqueid='7777777777', items='{"jumppack":{"Equipped":true,"Modifiers":[]},"masterchief":{"Equipped":true,"Modifiers":[]},"texthat":{"Equipped":true,"Modifiers":{"text":"Traitor","color":{"r":255,"b":0,"a":255,"g":0}}},"kleinerglasses":{"Equipped":true,"Modifiers":[]}}', points='48619';
INSERT INTO pointshop_data SET uniqueid='8888888888', points='1440';
INSERT INTO pointshop_data SET uniqueid='9999999999', items='{"trollface":{"Equipped":true,"Modifiers":[]},"arctic":{"Equipped":true,"Modifiers":[]},"tophat":{"Equipped":true,"Modifiers":[]}}', points='5080';
INSERT INTO pointshop_data SET uniqueid='0000000000', points='30';
INSERT INTO pointshop_data SET uniqueid='1010101010', points='70';
INSERT INTO pointshop_data SET uniqueid='1111111111', points='1300';
INSERT INTO pointshop_data SET uniqueid='1212121212', items='{"threedeeglasses":{"Equipped":true,"Modifiers":[]}}', points='310';
INSERT INTO pointshop_data SET uniqueid='1313131313', items='{"herbert":{"Equipped":true,"Modifiers":[]}}', points='2290';
INSERT INTO pointshop_data SET uniqueid='1414141414', points='40';