Форум Сайтостроение Веб-строительство

Обработка большого текстового файла

G
На сайте с 24.10.2009
Offline
51
Gaaarfild
1030

Есть такая опция, когда пользователи обновляют Базу данных с помощью CVS файла.

Но файлы достаточно большие. 1-2 мегабайта.

Каждая строчка содержит госномер машины, VIN и компанию, в которой застрахована машина, а так же периоды страховки ее и другую информацию.

При загрузке в БД идет сначала проверка, нет ли такого авто уже в БД. Если есть, то его необходимо просто обновить. Если нет, то добавить новую запись.

У меня данная конструкция получается очень сильно громоздкой.

И скрипт есть очень много ресурсов, потому что очень много запросов в БД. Подскажите пожалуйста, как можно максимально оптимизированно обрабатывать файл?

Вот пример CSV файла.


SkID;nomZvl;vidStr;VIN;gosNom;model;dts;dtPo;dtSl
HTN;803р-08АлФ;1;XTA21102020541895;;ВАЗ 2110;2008-7-31;2009-7-30;2008-11-20
HTN;805р-08АлФ;1;X8927851B50BE3893;;БАГЕМ 278510;2008-6-24;2009-6-23;2008-11-20
HTN;806р-08АлФ;1;KLAJF19W1VB201104;Х468РР;DAEWOO ESPERO;2008-8-4;2009-8-3;2008-11-21
HTN;807р-08АлФ;1;XTA21074011442260;Х250РН;ВАЗ 2107;2008-9-2;2009-9-1;2008-11-24
HTN;808р-08АлФ;1;XTA210990T1820120;T006ЕУ;ВАЗ 21099;2008-4-30;2009-4-29;2008-11-24
HTN;809р-08АлФ;1;JMBSNCS3A6U017800;Х606АУ;MITSUBISHI LANCER;2008-5-2;2009-5-1;2008-11-25

Вот код обработки


if($_FILES['usercsv']['tmp_name']!="") {
				if($files_class->get_typefile($_FILES['usercsv']['name'])!='csv') {
					header('Location: index.php?mod=carbaseedit&do=Add&error=filetypeerror');
				}
				$che = 1;
				do {
					$filename = date('d-m-Y_H-i-s').'('.$che.').csv';
					//$filename = rand(1000000, 9000000).'.csv';
					$che++;
				} while (file_exists('uploads/tmp/'.$filename));
				
				if(!copy($_FILES['usercsv']['tmp_name'], 'uploads/tmp/'.$filename)) {
					header('Location: index.php?mod=carbaseedit&do=Add&error=uploaderror');
				} else {
					//setlocale(LC_ALL, 'ru_RU.UTF-8');
					$file_size = filesize('uploads/tmp/'.$filename)+100;
					$fo = fopen('uploads/tmp/'.$filename, "r");
					$row = 1;
					//$db->sql_query("TRUNCATE TABLE ".$prefix."_car_rollback");
					$records_sum = 0;
					$filecont = file_get_contents('uploads/tmp/'.$filename);
					while (($line = fgets($fo, $file_size)) !== false) {
						if($row!=1) {
							$data=explode(';', $line);
							//	[0] - Код компании
							//	[1] - Заявление
							//	[2] - Каско\Осаго
							//	[3] - VIN
							//	[4] - Госномер
							//	[5] - Модель машины
							//	[6] - Дата С
							//	[7] - Дата По
							//	[8] - Дата случая
							
							list($cid) = $db->sql_fetchrow($db->sql_query("SELECT id FROM ".$prefix."_ins_companies WHERE `tid` LIKE '".$data[0]."'"));
							$gosnom_sh = str_replace(' ', '', $data[4]);
							//$gosnom = iconv('cp1251', 'utf-8//IGNORE', $gosnom);
							$gosnom_sh = $texts_class->translit_toeng_repl($gosnom_sh);
							$dat_adate = explode('-', $data[8]);
							$region = mb_substr($gosnom_sh, 7);
							$vin = str_replace(' ', '', $data[3]);
							$dat_from = explode('-', $data[6]);
							$date_fr = mktime(3,0,0, $dat_from[1], $dat_from[2], $dat_from[0]);
							$dat_to = explode('-', $data[7]);
							$date_to = mktime(3,0,0, $dat_to[1], $dat_to[2], $dat_to[0]);
							$dat_adate = explode('-', $data[8]);
							$adate = mktime(3,0,0, $dat_adate[1], $dat_adate[2], $dat_adate[0]);
							$result = $db->sql_query("SELECT id FROM ".$prefix."_car WHERE `company`=".$cid." AND `gosnomer`='".$gosnom_sh."' AND `vin`='".$vin."' AND `adate`=".$adate."");
							 if($db->sql_numrows($result)==0) {
								$gosnom_sh = $texts_class->translit_toeng_repl($gosnom_sh);
								$result = $db->sql_query("SELECT id FROM ".$prefix."_car WHERE `company`=".$cid." AND `gosnomer`='".$gosnom_sh."' AND `vin`='".$vin."' AND `adate`=".$adate."");
							}
							//print_r($db->sql_error());
							if($db->sql_numrows($result)==0) {
								$gosnom_sh = $texts_class->translit_toeng_repl($gosnom_sh);
								$result1 = $db->sql_query("INSERT INTO ".$prefix."_car (`gosnomer`, `region`, `model`, `claim`, `company`, `company_code`, `ins_type`, `fr`, `fr_d`, `to`, `to_d`, `adate`, `adate_d`, `vin`, `add_time`, `uinput`) VALUES ('".$gosnom_sh."', '".$regison."', '".mysql_escape_string(str_replace('"', '', trim($data[5])))."', '".mysql_escape_string(trim($data[1]))."', ".$cid.", '".mysql_escape_string(trim($data[0]))."', ".intval($data[2]).", ".$date_fr.", '".mysql_escape_string(trim($data[6]))."', ".$date_to.", '".mysql_escape_string(trim($data[7]))."', ".$adate.", '".mysql_escape_string(trim($data[8]))."', '".$vin."', ".time().", ".intval($_SESSION['user_id']).")");
								$newcar_id = $db->sql_nextid($result1);
								$db->sql_query("INSERT INTO ".$prefix."_car_rollback (`car_id`, `time`, `company`) VALUES (".$newcar_id.", ".time().", ".intval($_SESSION['company_id']).")");
								$db->sql_query("INSERT INTO ".$prefix."_ins_period (`fr`, `to`, `car_id`, `add_time`, `uinput`) VALUES (".$date_fr.", ".$date_to.", ".$newcar_id.", ".time().", ".intval($_SESSION['user_id']).")"); 
								$db->sql_query("INSERT INTO ".$prefix."_accident (`adate`, `car_id`, `add_time`, `uinput`) VALUES (".$adate.", ".$newcar_id.", ".time().", ".intval($_SESSION['user_id']).")");
								//print_r($db->sql_error());
							} else {
								list($sid) = $db->sql_fetchrow($result);
								$gosnom_sh = $texts_class->translit_toeng_repl(trim($gosnom_sh));
								$db->sql_query("UPDATE ".$prefix."_car SET `gosnomer`='".$gosnom_sh."', `region`='".trim($region)."', `model`='".mysql_escape_string(trim($data[5]))."', `claim`='".mysql_escape_string(trim($data[1]))."', `company`=".$cid.", `company_code`='".mysql_escape_string(trim($data[0]))."', `ins_type`=".intval($data[2]).", `fr`=".$date_fr.", `fr_d`='".mysql_escape_string(trim($data[6]))."', `to`=".$date_to.", `to_d`='".mysql_escape_string(trim($data[7]))."', `adate`=".$adate.", `adate_d`='".mysql_escape_string(trim($data[8]))."', `vin`='".$vin."', `uinput`=".intval($_SESSION['user_id'])." WHERE id=".$sid."");
								$result3 = $db->sql_query("SELECT id FROM ".$prefix."_ins_period WHERE `car_id`=".$sid." AND `fr`=".$date_fr." AND `to`=".$date_to."");
								if($db->sql_numrows($result3)==0) {
									$db->sql_query("INSERT INTO ".$prefix."_ins_period (`fr`, `to`, `car_id`, `add_time`, `uinput`) VALUES (".$date_fr.", ".$date_to.", ".$sid.", ".time().", ".intval($_SESSION['user_id']).")");
								}
								$result4 = $db->sql_query("SELECT id FROM ".$prefix."_accident WHERE `car_id`=".$sid." AND `adate`=".$adate."");
								if($db->sql_numrows($result4)==0) {
									$db->sql_query("INSERT INTO ".$prefix."_accident (`adate`, `car_id`, `add_time`, `uinput`) VALUES (".$adate.", ".$newcar_id.", ".time().", ".intval($_SESSION['user_id']).")"); 
								}
								//print_r($db->sql_error());
							}
						}
						$records_sum = ($row!=1) ? ++$records_sum : $records_sum;
						$row++;
						
					}
					//unlink('uploads/tmp/'.$filename);
					$db->sql_query("INSERT INTO ".$prefix."_upload_stats (`date`, `company`, `uid`, `filename`, `filesize`, `records`) VALUES (".time().", ".intval($cid).", ".intval($_SESSION['user_id']).", '".$filename."', '".$_FILES['usercsv']['size']."', ".$records_sum.")");
					print_r($db->sql_error());
					// Логирование начало
						$core_class->log_text_db = 'filename: '.mysql_escape_string($_FILES['usercsv']['name']).' -> '.$filename.', filesize: '.intval($file_size).'b';
						$core_class->log_text_file = 'filename: '.mysql_escape_string($_FILES['usercsv']['name']).' -> '.$filename.', filesize: '.intval($file_size).'b';
						$core_class->log_to_file('carbase_load_csv');
						$core_class->log_to_db('carbase_load_csv');
					//Логирование конец
					header('Location: index.php?mod=carbaseedit&do=Add&error=noerror');
				}
			} else {
				header('Location: index.php?mod=carbaseedit&do=Add&error=nofile');
			}

Понимаю, что все это дико неоптимизированно. Но где можно поправить?

C
На сайте с 04.02.2005
Offline
291
Chukcha
#1

как вариант закачать файл во временную таблицу

Затем update существующих с пометкой, что запись обработана

Затем инсерт необработанных записей.

malls
На сайте с 08.08.2005
Offline
255
malls
#2

REPLACE осчастливит автоматическим апдейтом существующих и добавлением отсутствующих.

G
На сайте с 24.10.2009
Offline
51
Gaaarfild
#3

Просто Replace будет ли работать?

Не меняются только номера машин VIN. А вот некоторые остальные данные в линии меняться могут.

E
На сайте с 29.08.2010
Offline
0
eutm
#4
Gaaarfild:
Просто Replace будет ли работать?
Не меняются только номера машин VIN. А вот некоторые остальные данные в линии меняться могут.

Будет.

Маленький нюанс. Если REPLACE понимает, что запись с таким VIN уже есть, то сначала удалит старую, а потом вставит новую. Как бы если данных много, то лучше использовать INSERT ON DUPLICATE KEY UPDATE, в своих нагруженных проектах я делаю так.

WordPress.com интегрировалась с Google Google добавил новые возможности Google Сайты получили обновлённый
S
На сайте с 23.05.2004
Offline
315
Stek
#5

У MySQL на этот счет есть свои средства:


LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]
Это просто подпись.
G
На сайте с 24.10.2009
Offline
51
Gaaarfild
#6
Stek:
У MySQL на этот счет есть свои средства:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

Отличная команда. Спасибо. Тогда еще один вопрос. Как вы видите из кода, он обычную дату превращает в unix timestamp, И часть полей он дублирует в другие таблицы с указанием внешнего ключа. Так же, часть данных преобразовывает на основании других таблиц.

Как решить этот вопрос?

В Яндекс Вебмастере появился Яндекс.Маркет обновил функционал для В анализ сайта от
S
На сайте с 23.05.2004
Offline
315
Stek
#7

если некоторые поля не соответствуют, то сделайте в таблице временные поля под это дело.

А потом после импорта, парой других запросов приведите все в соответствие.

К примеру обработку даты как то так :

UPDATE my_table
    SET 
        timestamp_filed = UNIX_TIMESTAMP(import_date_filed)
    WHERE
        timestamp_filed = 0;

В общем часть работы вполне можно переложить на базу, а уж обрабатывает она данные гораздо шустрее :)

Статика => Движок = Ошибка 1054 в MySQL. Огромные базы данных
G
На сайте с 24.10.2009
Offline
51
Gaaarfild
#8

Спасибо большое. =)

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий