Keskustelu: Nettisivujen teko: tiedostosta tietokantaan

Eli kuinka saan helpoiten luettua / suoritettua tiedoston sisällön tietokantaan. Rivit sisältää tiedot, jotka pitäisi saada vain tietokantaan tallennettua.

Lataan ulkoiselta palvelimelta tiedoston jokna sisältö näyttää tältä (rivejä saattaa olla n. 10 000):

INSERT INTO x_table VALUES (1,325,400,1,283,'Tieto1');
INSERT INTO x_table VALUES (2,325,400,1,283,'Tieto2');
INSERT INTO x_table VALUES (3,325,400,1,283,'Tieto3');

Ilmeisesti pitäisi kyhätä koodi joka lukee rivin kerrallaan, ja liittää rivi aina @mysql_query($rivi ,$con):n sisällöksi, vai löytyykö tähän muita ratkaisuja?

Testasinkin tuota tapaa, mutta se tallensi tietokantaan vain osan tieodoston sisällöstä (5 372/11 837), eli ei oikein hyvä.. Parempia ehdotuksia otetaan vastaan :)

(Mod. korjasi oikeat kooditagit.)

Pentu kirjoitti:

Eli kuinka saan helpoiten luettua / suoritettua tiedoston sisällön tietokantaan. Rivit sisältää tiedot, jotka pitäisi saada vain tietokantaan tallennettua.

Mikäli palvelimella on phpMyAdmin, onnistuu tuo sillä kätevästi.

Pentu kirjoitti:

meisesti pitäisi kyhätä koodi joka lukee rivin kerrallaan, ja liittää rivi aina @mysql_query($rivi ,$con):n sisällöksi, vai löytyykö tähän muita ratkaisuja?

Sekin on yksi vaihtoehto.

LOAD DATA INFILE "xxx.csv" INTO TABLE taulu FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" (col1, col2, col3)

Jotenkin näin. http://dev.mysql.com/doc/refman/5.1/en/load-data.html

-tossu- kirjoitti:

Mikäli palvelimella on phpMyAdmin, onnistuu tuo sillä kätevästi.

Käytinkin sitä jo testaillessani tuota luku osaa koodistani, mutta kun myöhemmin sekin on hieman työlästä, sillä kerran päivässä haen tuon tiedoston useammalta palvelimelta. Ja en varmaankaan ole joka päivä tuota operaatiota suorittamassa, niin senkin kannalta olisi hyvä saada koodilla tuo suoritettua, luotettavasti.

Olisihan se ikävä tarjota "asiakkaalle" vanhentunutta tietoa.

Macron vastaus meni taas ihan aiheen ohi, tämähän ei ole CSV-tiedosto vaan ilmeisesti SQL-dumppi.

Silmukka on ihan toimiva ratkaisu, kunhan varmistat, että tiedosto tulee aina käsiteltyä oikein (esim. monirivisistä teksteistä riippumatta). Lisäksi voi olla ongelmana, että PHP:n aikaraja on asetettu niin matalaksi, ettei skripti ehdi laittaa kaikkia rivejä. Muuta siis tarvittaessa PHP:n rajoja. Muistirajan ylityksen välttämiseksi tiedostoa kannattaa luultavasti lukea rivi kerrallaan esim. funktiolla fgets.

Jos palvelimella voi ajaa ohjelmia, yksi mahdollisuus on syöttää tiedosto suoraan komentorivillä toimivalle MySQL-asiakasohjelmalle.

Metabolix kirjoitti:

Macron vastaus meni taas ihan aiheen ohi, tämähän ei ole CSV-tiedosto vaan ilmeisesti SQL-dumppi.

Sen siitä saa kun ei osaa lukea. Ymmärsin, että tuollaisia arvoja pitäisi lisätä kantaan, ei suorittaa noita kyselyitä. Sama se.

Eikös tuota aika rajaa pysty muuttamaan väliaikaisesti koodia ajaessakin? Jos, niin mitenkäs se onnistui? Ja mitenkäs pystyn tarkistamaan, että kaikki rivit tuli varmasti suoritettua?

Näillä koodin pätkillä testasin tätä juttua :P ..

$files = file("data/tmp.sql");
for ($i = 0; $i < count($files); $i++) {
     @mysql_query("$files[$i]");
}

Suoritettuja rivejä (5 372 / 11 837)

$tiedot = fopen("data/tmp.sql","r");
$ln = 0;
while(!feof($tiedot)){
    $rivi = fgets ($tiedot);
    ++$ln;
   @mysql_query($rivi, $db);
}
fclose($tiedot);
echo "$ln <BR>\n";

Suoritettuja rivejä (5 490 / 11 837)

Mistä tarkistat suoritettujen rivien määrän? Tietokannastako? Pääseekö skripti tuonne loppuun asti? Hieman vaihteleva rivimäärä viittaisi minusta juurikin ajan loppumiseen.

Ota turhat lainausmerkit pois ensimmäisestä koodista. Molemmissa koodeissa tarkista, että mysql_query onnistuu; jos ei onnistu, selvitä syy. Aikarajaa voi muuttaa funktiolla set_time_limit, jos palvelin sallii.

Juu tietokannasta katson tuon tallennettujen tietojen määrän, tyhjennän tietokannan tietenkin ennen kuin yritän ajaa koodini uudelleen. Asetin väliaikaisesti aikarajaksi 120sec, johan onnistui...

viimeisellä rivillä tulosti virheen, mutta se johtuu tyhjästä rivistä, joten täytyy varmaan tarkistaa myös rivin sisältö ennen kuin ajan sen.

Jos löytyy MySQL-komentorivi, niin komennolla:

source path/to/file.extension

Kyllähän tuo konsoli löytyy testi palvelimelta ainakin.. Se kysyy ensimmäisenä passua, ja kun syötän sen sille, niin koko ikkuna katoaa. Ja kun sen avaa uudelleen, niin käy samoin.

Ja vaikka saisinkin tuon pelittämään, niin tuo tarttis silti saada suoritettua koodissa, jotta projektin valmistuessa saisin tarjottua asiakkaalle (sivulla vierailevalle) aina tuoreet tiedot. Ja onko tuo mun tekemä while:kaan kovin luotettava, jos aikaraja ylittyy syystä tai toisesta, niin ohjelman ajaminen jää kesken, ja tiedot jää siten vajaaksi.

Pentu kirjoitti:

Ja onko tuo mun tekemä while:kaan kovin luotettava, jos aikaraja ylittyy syystä tai toisesta, niin ohjelman ajaminen jää kesken, ja tiedot jää siten vajaaksi.

Eikös transactionit ole tätä varten?

Tarvinnee yrittää tutustua pdo:n maailmaan ilmeisesti..

Kyllä ne näyttäisivät toimivan myös ilman PDO:ta, mutta mielestäni PDO on paljon parempi.

Transaktio on sitä varten, että jos homma jää kesken, perutaan ne alkupuolen asiatkin. Tuskinpa siitä on mitään iloa tässä.

Kaiken järjen mukaan homman pitäisi olla tällä selvä:

set_time_limit(0);
ignore_user_abort(true);

Jotenkin kuitenkin koko juttu kuulostaa oudolta purkkaratkaisulta. Mistä nuo tiedot tulevat ja miten ne ajetaan järjestelmään, käsinkö vai jollain ajastetulla skriptillä? Onko oikeasti tarpeen poistaa koko vanha tietokanta ja täyttää alusta asti uudestaan?

Kokeilinkin tuota tapaa. Ohjelman ajo kesti 47.369218111 sekuntia, mikä on kylläkin aika pitkä aika odottaa sivulla surffaavalle...

Hienoa on kuitenkin että scripti toimii jotenkin... :D tiedoston käsittely aika pitenee varmastikkin aika roimasti, kun lisään tuohon scriptiin vielä tyhjän rivin tarkistuksen... ja vaihan lennosta tietokannan nimen...

Ei sitä kannatakkaan joka kerta suorittaa, vaan esimerkiksi pari kertaa päivässä ja ei niin, että se tapahtuu käyttäjän aktivoimana.

Se tarvii suorittaa vain kerran päivässä, jokaisen palvelimen kohdalla erikseen tietenkin... Tuo ladattava .sql tiedosto ilmestyy tosiaan vain kerran päivässä, klo 7:00 palvelimen aikaa..

Melkein pakkohan se on käyttäjän aktivoimana suorittaa, jollen itse ole joka päivä kukkoa ennen suorittamassa tuota..

Voit luoda oman sivun tuolle päivityskoodille, ja sitten käsket palvelinkoneen vierailla siellä kello seitsemän.

Linuxsille on crontab, ja Windowsista löytyy scheduled tasks.

Pentu kirjoitti:

Ohjelman ajo kesti 47.369218111 sekuntia, mikä on kylläkin aika pitkä aika odottaa sivulla surffaavalle...

Voi hyvänen aika sentään. >_> Siis tietokantojen tekemiseen on hyviä tapoja, huonoja tapoja ja aivan tolkuttoman huonoja tapoja – arvaapa, mihin ryhmään tämä kuuluu.

Voit helposti laittaa sivulle tarkistuksen, että jos on ilmestynyt uusi tiedosto, ajetaan se kantaan. Muista tehdä homma pitävästi niin, että edes samanaikaiset sivunlataukset eivät aiheuta tiedoston ajamista tuplana.

Pentu kirjoitti:

tiedoston käsittely aika pitenee varmastikkin aika roimasti, kun lisään tuohon scriptiin vielä tyhjän rivin tarkistuksen

No se tuskin hidastaa koodia edes sekunnin sadasosaa. Muista trimmata rivit. Jos kuitenkin käytät file-funktiota, voit myös vain antaa sille toiseksi parametriksi FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES.

Arvaas, tiedän kyllä, että koodini, varsinkin tämä kuuluu tolkuttoman huonoihin... En parempaankaan pysty. Siksipä täällä neuvoja kyselenkin. Ehkä nappasin turhan vaikean osan heti alkuun, mutta jossain kohtaa tämäkin scripti on koodattava. Yritän löytää hyvän ja tehokkaan ratkaisun tämän asian hoitamiseksi.

voisin tietenkin suorittaa tuon system() -funtiolla, kuten ko. pelin tuto scriptissä asia on hoidettu... a) se ei pelitä ainakaan tässä testi palvelimellani ja b) jätän mielelläni system() funtion väliin, koska en tiedä siitä juuri mitään.

lainaus:

En parempaankaan pysty.

Mutta vähän tuntuu, ettet edes yritä, kun et ollenkaan kysynyt, olisiko tuolle minuutin odottelulle jokin vaihtoehto, etkä ole kertonut koko kikkailun perimmäistä syytä, jotta joku voisi ehkä ehdottaa täysin erilaista lähestymistapaa.

Erittäin yksinkertainen ratkaisu on tarkistaa joka sivunlatauksen lopussa, onko uutta tiedostoa, ja jos on, kutsua erillistä skriptiä, joka hoitaa sen kopioinnin:

// *.php
// Jos on ilmestynyt uusi tiedosto...
if (file_exists("uusi.sql")) {
  // ... ladataan toinen sivu, joka hoitaa itse siirron,
  // mutta ei jäädä odottamaan sitä.
  $ctx = stream_context_create(array("http" => array("timeout" => 0.1)));
  @file_get_contents("http://.../kantaan.php", 0, $ctx);
}

// kantaan.php
ignore_user_abort(false);
set_time_limit(0);
if (rename("uusi.sql", "kesken.sql")) {
  // while (...) mysql_query(...);
  rename("kesken.sql", "vanha.sql");
}

Toki asia vähän mutkistuu, jos tiedosto pitää hakea netistä. Silloin kannattaa ensin tarkistaa kellonajan perusteella, että uusi versio edes voisi olla olemassa, ja latauksen jälkeen kannattaa vielä tarkistaa, että tiedoston sisältö ei ole sama kuin viimeksi.

Tuo tiedostonlataus toiselta palvelimelta ei ole mikään ongelma. Olen siihen löytänyt jopa ratkaisun, ja homma toimiikin ihan kelvollisesti. Toki voisin tiedustella, että jos siihen löytyisi parempi ratkaisu. Mutta jätettäköön se myöhemmälle, mikäli koen ko. asiaa tarpeelliseksi.

Enkös tuossa tiedustele, että josko löytyisi parempiakin keinoja.

Pentu kirjoitti:

Ilmeisesti pitäisi kyhätä koodi joka lukee rivin kerrallaan, ja liittää rivi aina @mysql_query($rivi ,$con):n sisällöksi, vai löytyykö tähän muita ratkaisuja?

No joo, toki jos asiaan löytyy parempikin ratkaisu kuin perinteinen whale tai for -lasue, niin miksipäs ei... Mitä tehokkaammin ja luotettavammin asian saa hoidettua, sen parempi... Mutta tähän viimeisimpään antamaasi vinkkiin tutustunen paremmin huomenna, kun pääsen takaisin kotikonnuille, ja nauttimaan php:n maailmasta.

Tässä osassa projektia on tarkoituksena hakea päivittäin useammalta palvelimelta (aluksi n. 8) tuo kyseinen .sql -tiedosto. Tämän jälkeen ajaa se kantaan, jotta saan tarjottua pelaajille ajan tasalla olevan tiedon. Tietoa tulee kuitenkin joka päivä lisää, joten joudun erottelemaan vielä eri päivinä tulleen datan, jotta pystyn tarjoamaan myös tilastoja aikaisemmasta... Tämä jääköön toistaiseksi myöhempään käsittelyyn, mikäli en itse keksi hyvää ratkaisua.

Tämä ei siis ole mikään "onkohan tällänen mahdollista" -kikkailu vaan ihan mielenkiinnosta tätä projektia teen ja neuvoa kyselen sitä tarvitessani. Ylensä yritän ensin itse hakea vastauksen googlen avulla + php oppaista ja php.netin sivuilta.

Miksi tämä pitää tehdä php:n alla? Miksei hyvin yksinkertainen crontab käy?

Eikö toimi tämäntapainen kaikki kertaheitolla tietokantaan:
https://www.ohjelmointiputka.net/keskustelu/20463-mysqldump-php-ja-selain/sivu-1#v162450

Olet määritellyt ongelmasi aivan väärin. Voit yksinkertaisella ajan mittauksella todeta, että 10000 rivin lukeminen tiedostosta ei kestä hetkeäkään. Ei ole mitään väliä, mitä silmukkaa tähän käytät. Ja vaikka ajaisit tiedoston kantaan jollain muulla tavalla, kyllä 10000 rivin lisääminen tietokantaan silti vie aikansa.

Sen sijaan kyseisen tiedoston lataus toiselta palvelimelta voi kestää kauankin, joten yritä välttää turhia latauksia. Optimitapauksessa tiedosto ladataan vain kerran jokaisen muutoksen jälkeen. Jos suuri osa tiedoista pysyy samana, vielä parempi olisi päästä tilanteeseen, jossa ladattaisiin vain tarvittavat muutokset, esim. pieni määrä DELETE-, UPDATE- ja INSERT-rivejä.

Eli suurin ongelma ei ole tietojen ajamisessa kantaan vaan siinä, milloin ne pitäisi ajaa ja miten tämä tunnistetaan. Sitä kukaan muu ei voi kertoa, kun kukaan muu ei vielä tiedä, mistä ne tiedot ihan oikeasti tulevat ja miten varmasti tuo "klo 7:00 palvelimen aikaa" pitää paikkansa. Toki jos se on varma tieto ja heittää vaikka enintään 10 minuuttia, kaikkein kätevintä olisi yksinkertaisesti ajastaa skripti toimimaan 7:10 tai ajaa se aina tätä seuraavalla sivunlatauksella.

Kyllä tuon varmasti saisi hoidettuakin tuolla system(); -funktiolla, mutta käsittääkseni sitä suositellaan vlälttämään jos ei ole varma toiminnasta, jota tuon avulla aikoo käyttää... Sitäpaitsi webhotellit eivät useinkaan salli käyttää sitä. Myös crontabi taitaa olla aika harvassa.

Jep, kyseessä on siis selainpohjainen peli, Travian... Tuosta kellon ajan tarkkuudesta en tiedä. Tuskin on suurtakaan heittoa tuosta luvatusta ajasta. Tuon sql- dumpin lataan erikseen jokaiselta travianin peli palvelimelta (toistaiseksi vain suomen palvelimilta), esimerkiksi "http://ts5.travain.fi/map.sql" -ositteesta... Luon jokaiselle palvelimelle oman tietokannan, jonne tiedot tallennan. Eli muokkaan "x_world" kohdtaan vain sen tablen nimen jonne tiedon haluan, str_replace():n avulla.

Eli olet/te sitä mieltä, että tuo silmukka on pätevä ratkaisu tällaiseen hommaan?

Loin tablen ohjeiden mukaisesti seuraavalla tavalla:

CREATE TABLE `x_world` (
  `id` int(9) unsigned NOT NULL default '0',
  `x` smallint(3) NOT NULL default '0',
  `y` smallint(3) NOT NULL default '0',
  `tid` tinyint(1) unsigned NOT NULL default '0',
  `vid` int(9) unsigned NOT NULL default '0',
  `village` varchar(20) NOT NULL default '',
  `uid` int(9) NOT NULL default '0',
  `player` varchar(20) NOT NULL default '',
  `aid` int(9) unsigned NOT NULL default '0',
  `alliance` varchar(8) NOT NULL default '',
  `population` smallint(5) unsigned NOT NULL default '0',
  UNIQUE KEY `id` (`id`)
);

Tuota joudun kaiketi muuttamaan jonkun verran jotta saan tallennettua tauluun dataa useammilta päiviltä...

Testasinkin tuota system() -funktion avulla, kun kokeilin alkuperäisen tuton mukaan, eepä toiminut..

system('mysql --host='.$mysqlhost.' --user='.$mysqluser.' --password='.$mysqlpass.' --default-character-set=utf8 '.$mysqldb.' < data/'.$s_pvm.'.sql');

Mod. korjasi kooditagit, SQL ei ole PHP!

crontab harvassa? Mitä? Sehän on aivan perusjuttuja, jonka pitäisi löytyä jokaisesta webhotellista, joka pyörii linuxin päällä.

Ite en ainakaan voisi ikinä kuvitella ostavani tavuakaan tilaa mistään, missä ei tuollaisia perusjuttuja sallittaisi. Kerro toki mikä webhotelli, niin osaan varautua.

Ota ssh-yhteys siihen palvelimeesi ja käytä tuota mysql-komentoa yhdessä wgetin kanssa. Kun saat sen kerran toimimaan käsin, tunget sitten samat jutut crontabiin.

Tässä sinun ongelmassa on jotakin tuttua jota minullakin oli noin pari vuotta sitten. Onko mysql-palvelin jonne tallennat, samalla palvelimella kuin sinun for-silmukkasi?
Noin pari vuotta sitten minulla oli ongelmia omalta koneelta lisätä noin 25 000 riviä sisätävää taulua tietokantaan...kesti tosi kauan...ehkä jopa kauemmin kuin sinulla. Kun siirsin skriptin samalle palvelimelle, homma hoitui silmänräpäyksessä. Sekin kävi koko homman läpi for-silmukassa.

Tuota insertoimista voinee nopeuttaa parilla eri tavalla. Yksi tapa on muuttaa jokainen erillinen insertti monen alkio kertainsertiksi. MySQL tukee monen rivin asettamista kerralla.

INSERT INTO mytable VALUES (1, 'row 1'), (2, 'row 2'), (3, 'row 3');

Toinen ja kenties tehokkaampi, mutta vaikeammin toteutettava, tapa olisi käyttää esivalmisteltuja lausekkeita. Se onnistuu PDO:lla. Tämä nopeuttaa eritoten kyselyn parsimista mutta myös rivien lisäämistä.

$db = new PDO(...);
$q = $db->prepare(
  'INSERT INTO x_table VALUES (?, ?, ?, ?, ?, ?)',
  array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)
);

foreach ($rows as $row) {
  $q->bindParam(1, $row['data1'], PDO::PARAM_INT);
  ...
  $q->bindParam(6, $row['data6'], PDO::PARAM_STR);
  $q->execute();
}

Itse olin luullut, että tuo PDO tekisi loppujen lopuksi palvelimelle ihan vaan samanlaista SQL:ää kuin muutenkin. Täytyykin joskus tutustua mitä muita tapoja komentojen lähettämiseen MySQL:lle on olemassa kuin "SQL-kieliset" komennot ja mahdollisesti sniffailla mitä tapahtuu kun tuon esimerkkisi mukaisen kyselyn ajaa PDO:lla.

Kyse ei sinällään ole PDO:n omasta jutusta vaan jokseenkin standardista tietokantojen ominaisuudesta.