Skip to forum content

You are not logged in. Please login or register.

forums.pvpgn.pro → [RU] Tech Support → из файлов в бд

Pages 1

You must login or register to post a reply

RSS topic feed

Posts: 7

1 Topic by kosil (edited by kosil 21.12.2011 16:59)

  • kosil
  • newbie
  • Offline
  • Registered: 25.03.2010
  • Posts: 13

Topic: из файлов в бд

Как перенести юзеров из папки /var/users в БД mysql ? Где-то видел похожую тему, но не могу найти, ни тут, ни в гугле). Есть какие нибудь способы переноса, кроме как "вбивания" ручками? Я слышал, что есть какой-то скрип(то ли на перле, то ли на пэашпэ), но не могу его найти.

Да, кстати, объясните плз, как pvpgn записывает отчеты игр в бд? Вот при первом запуске он создает таблицы. С hwstats - все понятно, там есть скрипт, и через него он делает записи в бд. А вот как дела обстоят с pvpgn stats (2.4.5) ? Как там производить импорт?

2 Reply by HarpyWar

  • HarpyWar
  • HarpyWar
  • Developer
  • Offline
  • Registered: 07.12.2007
  • Posts: 1,937

Re: из файлов в бд

kosil wrote:

Как перенести юзеров из папки /var/users в БД mysql ?

svn.berlios.de/svnroot/repos/pvp … s/storage/

kosil wrote:

как pvpgn записывает отчеты игр в бд?

Он записывает для каждой игры свой файл в папку /var/reports/. Другого способа нет. Pvpgn не пишет инфу о сыгранных играх для варкрафта 3.

kosil wrote:

как дела обстоят с pvpgn stats (2.4.5) ? Как там производить импорт?

Там нет страницы с историей игр.

Do not ask for support in PM.

HarpyWar's Website

3 Reply by kosil (edited by kosil 21.12.2011 19:00)

  • kosil
  • newbie
  • Offline
  • Registered: 25.03.2010
  • Posts: 13

Re: из файлов в бд

Спасибо! А можно поподробние как импортировать? Я делаю так:
1) Открываю консоль, ввожу xxx\plain2sql.pl yyy\users\
2) Идет перевод. копирую, что в консоли. открываю navicat, выбираю нужную бд, открываю консоль. вставляю. и ошибки:

INSERT INTO BNET (uid) VALUES (4);
INSERT INTO profile (uid) VALUES (4);
INSERT INTO Record (uid) VALUES (4);
INSERT INTO friend (uid) VALUES (4);
ALTER TABLE Record ADD COLUMN 'sexp__1__rating' VARCHAR(128);
UPDATE Record SET 'sexp__1__rating' = '937' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__rank' VARCHAR(128);
UPDATE Record SET 'sexp__1__rank' = '6' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__wins' VARCHAR(128);
UPDATE Record SET 'sexp__0__wins' = '7' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__email' VARCHAR(128);
UPDATE BNET SET 'acct__email' = '1221@dsa.com' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__high_rating' VARCHAR(128);
UPDATE Record SET 'sexp__1__high_rating' = '1033' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__ctime' VARCHAR(128);
UPDATE BNET SET 'acct__ctime' = '1307439693' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__userid' VARCHAR(128);
UPDATE BNET SET 'acct__userid' = '4' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_ip' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_ip' = '5.215.36.118' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__passhash1' VARCHAR(128);
UPDATE BNET SET 'acct__passhash1' = 'eada9930b0e6c6095718294835e6eb7da81ef936' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__last_game' VARCHAR(128);
UPDATE Record SET 'sexp__0__last_game' = '30189313 1606775349' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_clienttag' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_clienttag' = 'SEXP' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__losses' VARCHAR(128);
UPDATE Record SET 'sexp__1__losses' = '6' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__last_game_result' VARCHAR(128);
UPDATE Record SET 'sexp__0__last_game_result' = 'WIN' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__wins' VARCHAR(128);
UPDATE Record SET 'sexp__1__wins' = '2' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_owner' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_owner' = '3' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__high_rank' VARCHAR(128);
UPDATE Record SET 'sexp__1__high_rank' = '2' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__losses' VARCHAR(128);
UPDATE Record SET 'sexp__0__losses' = '5' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__username' VARCHAR(128);
UPDATE BNET SET 'acct__username' = 'pc2' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__last_game_result' VARCHAR(128);
UPDATE Record SET 'sexp__1__last_game_result' = 'LOSS' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__last_game' VARCHAR(128);
UPDATE Record SET 'sexp__1__last_game' = '30189895 2645591087' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__draws' VARCHAR(128);
UPDATE Record SET 'sexp__0__draws' = '3' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_time' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_time' = '1321986085' WHERE uid = 4;


mysql> INSERT INTO BNET (uid) VALUES (4);
INSERT INTO profile (uid) VALUES (4);
INSERT INTO Record (uid) VALUES (4);
INSERT INTO friend (uid) VALUES (4);
ALTER TABLE Record ADD COLUMN 'sexp__1__rating' VARCHAR(128);
UPDATE Record SET 'sexp__1__rating' = '937' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__rank' VARCHAR(128);
UPDATE Record SET 'sexp__1__rank' = '6' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__wins' VARCHAR(128);
UPDATE Record SET 'sexp__0__wins' = '7' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__email' VARCHAR(128);
UPDATE BNET SET 'acct__email' = '1221@dsa.com' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__high_rating' VARCHAR(128);
UPDATE Record SET 'sexp__1__high_rating' = '1033' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__ctime' VARCHAR(128);
UPDATE BNET SET 'acct__ctime' = '1307439693' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__userid' VARCHAR(128);
UPDATE BNET SET 'acct__userid' = '4' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_ip' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_ip' = '5.215.36.118' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__passhash1' VARCHAR(128);
UPDATE BNET SET 'acct__passhash1' = 'eada9930b0e6c6095718294835e6eb7da81ef936' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__last_game' VARCHAR(128);
UPDATE Record SET 'sexp__0__last_game' = '30189313 1606775349' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_clienttag' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_clienttag' = 'SEXP' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__losses' VARCHAR(128);
UPDATE Record SET 'sexp__1__losses' = '6' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__last_game_result' VARCHAR(128);
UPDATE Record SET 'sexp__0__last_game_result' = 'WIN' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__wins' VARCHAR(128);
UPDATE Record SET 'sexp__1__wins' = '2' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_owner' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_owner' = '3' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__high_rank' VARCHAR(128);
UPDATE Record SET 'sexp__1__high_rank' = '2' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__losses' VARCHAR(128);
UPDATE Record SET 'sexp__0__losses' = '5' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__username' VARCHAR(128);
UPDATE BNET SET 'acct__username' = 'pc2' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__last_game_result' VARCHAR(128);
UPDATE Record SET 'sexp__1__last_game_result' = 'LOSS' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__1__last_game' VARCHAR(128);
UPDATE Record SET 'sexp__1__last_game' = '30189895 2645591087' WHERE uid = 4;
ALTER TABLE Record ADD COLUMN 'sexp__0__draws' VARCHAR(128);
UPDATE Record SET 'sexp__0__draws' = '3' WHERE uid = 4;
ALTER TABLE BNET ADD COLUMN 'acct__lastlogin_time' VARCHAR(128);
UPDATE BNET SET 'acct__lastlogin_time' = '1321986085' WHERE uid = 4;
Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__rating' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__rating' = '937' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__rank' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__rank' = '6' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__wins' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__wins' = '7' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__email' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__email' = '1221@dsa.com' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__high_rating' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__high_rating' = '1033' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__ctime' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__ctime' = '1307439693' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__userid' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__userid' = '4' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_ip' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_ip' = '5.215.36.118' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__passhash1' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__passhash1' = 'eada9930b0e6c6095718294835e6eb7da81ef936' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__last_game' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__last_game' = '30189313 1606775349' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_clienttag' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_clienttag' = 'SEXP' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__losses' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__losses' = '6' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__last_game_result' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__last_game_result' = 'WIN' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__wins' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__wins' = '2' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_owner' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_owner' = '3' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__high_rank' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__high_rank' = '2' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__losses' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__losses' = '5' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__username' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__username' = 'pc2' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__last_game_result' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__last_game_result' = 'LOSS' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__last_game' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__1__last_game' = '30189895 2645591087' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__draws' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sexp__0__draws' = '3' WHERE uid = 4' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_time' VARCHAR(128)' at line 1
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''acct__lastlogin_time' = '1321986085' WHERE uid = 4' at line 1
mysql>

Что я не так делаю? MySQL 5.0.67

4 Reply by HarpyWar

  • HarpyWar
  • HarpyWar
  • Developer
  • Offline
  • Registered: 07.12.2007
  • Posts: 1,937

Re: из файлов в бд

Попробуй заменить __ на _

Do not ask for support in PM.

HarpyWar's Website

5 Reply by kosil

  • kosil
  • newbie
  • Offline
  • Registered: 25.03.2010
  • Posts: 13

Re: из файлов в бд

все тоже самое

6 Reply by HarpyWar

  • HarpyWar
  • HarpyWar
  • Developer
  • Offline
  • Registered: 07.12.2007
  • Posts: 1,937

Re: из файлов в бд

Имена таблиц и полей могут обрамляться только наклонными кавычками `, но не '. А значения полей как раз должны заключаться в одиночные кавычки ' (или не обязательно, если это число).

То есть
UPDATE Record SET 'sexp__1__rating' = '937' WHERE uid = 4;
замени на
UPDATE Record SET `sexp_1_rating` = '937' WHERE uid = 4;

По аналогии и другие строки.

Do not ask for support in PM.
+1 Zikoi5

HarpyWar's Website

7 Reply by kosil

  • kosil
  • newbie
  • Offline
  • Registered: 25.03.2010
  • Posts: 13

Re: из файлов в бд

Отлично, спасибо!
Поправил скрипт на 107 и 108 строчке

print("ALTER TABLE $tab ADD COLUMN '$col' VARCHAR(128);\n");
print("UPDATE $tab SET '$col' = '$nval' WHERE uid = $userid;\n");

на

print("ALTER TABLE $tab ADD COLUMN `$col` VARCHAR(128);\n");
print("UPDATE $tab SET `$col` = '$nval' WHERE uid = $userid;\n");

Заметил, что поле username в таблице BNET не заполняеться при добавлении в бд данных. Без этого не авторизуешься в игре(сервер будет твердить, что акка нету). Вбивал имя каждого юзера руками.

Posts: 7

Pages 1

You must login or register to post a reply

Who now at forum

Currently view post: 1 guest, 0 registered users

forums.pvpgn.pro → [RU] Tech Support → из файлов в бд