Autor Téma: Jak spravne pracovat s sql?  (Přečteno 552 krát)

Offline Andy

  • Mladík
  • **
  • Příspěvků: 51
  • Karma: 0
Jak spravne pracovat s sql?
« kdy: 13-04-2017, 15:38:46 »
Ahoj, mam projekt ve Firemonkey (iOS/Android/Windows) ktery v jednom vlakne zapisuje data do SQLite (cca 2500 zaznamu za hodinu) a v druhem je synchronizuje na jiny server. data se cca po mesici mazou (tzn okolo milionu zaznamu - synchronizuje se samozrejme jen ty co nebyly synchronizovany).

Obcas se mi ale stane ze to zacne hlasit takove veci jako ze "Database is encrypted or is not databse", nebo "Database is locked" ...

s databazi pracuji naprimo nejak takto

Kód: [Vybrat]
procedure TSQLCls.Query(jak: boolean; SQL:String; RCount: Integer = 30);
begin
  try
    if jak = true then begin
      SQLCls.SMInsert.SQL.Clear;
      SQLCls.SMInsert.SQL.Add(safeString(SQL));
      SQLCls.SMInsert.ExecSQL;
    end
    else begin
        SQLCls.SMSelect.Close;
        SQLCls.SMSelect.FetchOptions.RowsetSize:=RCount;
        SQLCls.SMSelect.SQL.Text:=safeString(SQL);
        SQLCls.SMSelect.Open;
        SQLCls.SMSelect.First;
    end;
  except on E:Exception do begin
      SQLCls.WriteToLogFile('Chyba [QRY01]: '+E.Message);
      SQLCls.WriteToLogFile('Chyba [QRY01]: '+SQL);
    end;
  end;
end;

function TSQLCls.QueryGetField(table,field,where:String):String; // vybere jen jednu hodnotu
begin
  Query(false,'SELECT '+field+' FROM '+table+' WHERE '+where+' LIMIT 1');
  Result:=getSQLString(field);
end;


function TSQLCls.getSQLString(field:String):String;
begin
  Result := SQLCls.SMSelect.Fields.FieldByName(field).AsString;
end;

zajima mne jestli je to takhle v poradku a problem je v tom ze proste SQLite to nezvlada nebo jestli je problem v tom ze pracuji naprim s DB a ne pomoci dalsich objektu ty Table atd ?

Offline Mi.Chal.

  • Guru
  • *****
  • Příspěvků: 566
  • Karma: 23
Re:Jak spravne pracovat s sql?
« Odpověď #1 kdy: 13-04-2017, 19:38:31 »
Excellent
Rated 1 time
Že se pracuje s SQL přímo problém asi nebude, ale asi to nemáš synchronizovaný. Musí to být buď na straně aplikace nebo zapnout v sqllite, podle toho, co člověk potřebuje. https://sqlite.org/threadsafe.html

A místo věcí jako safeString (nějaká doma vyrobená metoda?) by se měly používat spíš sql parametry. Místo Sql.Clear, Sql.Add snad stačí jenom přiřadit Sql.text nebo jak se to jmenuje.

Offline Andy

  • Mladík
  • **
  • Příspěvků: 51
  • Karma: 0
Re:Jak spravne pracovat s sql?
« Odpověď #2 kdy: 13-04-2017, 22:05:41 »
Connection definition pouzivam
Kód: [Vybrat]
  oParams := TStringList.Create;
  oParams.Add('Database=' + SYS_DATADIR + 'data.db');
  oParams.Add('Pooled=True');
  oParams.Add('JournalMode=WAL'); // Off
  oParams.Add('UpdateOptions.LockWait=True'); // cemmenter
  oParams.Add('SQLiteAdvanced=page_size=8192'); // 4096
  //oParams.Add('CacheSize=0');
  oParams.Add('Busytimeout=20000'); //10000
  oParams.Add('Synchronous=Full'); // Off
  oParams.Add('LockingMode=Normal');
  oParams.Add('SharedCache=False');
  oParams.Add('Encrypt=No');

db vytvari aplikace pri startu takze predpokladam ze Serialized
safeString jen nahrazuje uvozovky
Sql.Clear, Sql.Add mozna drobet spomali bykon ale asi nezpusobi chybu.

Zajimave je ze to muze bezet klidne tyden v poradku (nezavisle od zateze) a pak (treba i pri male zatezi) se najednou sekne.
Kdyz jsem to nepotreboval na mobilnich zarizenich a pouzival MySQL misto SQLite tak se nic podobneho nedelo :-(

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 1743
  • Karma: 75
    • Verze Delphi: D2007, XE3, DX10
Re:Jak spravne pracovat s sql?
« Odpověď #3 kdy: 13-04-2017, 22:48:29 »
Zajimave je ze to muze bezet klidne tyden v poradku (nezavisle od zateze) a pak (treba i pri male zatezi) se najednou sekne.
Ja na tom nic zajimaveho nevidim, takhle se projevuje vetsina race-condition problemu...
Prepodkladam, ze jsi slysel, ze SQLlite DB muze menit pouze jeden proces v jeden okamzik, tak snad ti tam nebezi zapisovaci a nejaky odmazavajici thread soucasne apod. tj ze mas max. jeden proces, ktery si DB otevrel s flagem SQLITE_OPEN_READWRITE a ostatni oteviras jako SQLITE_OPEN_READONLY

A ze ten soubor s DB mas na lokalnim mediu a ne nekde na siti, protoze ta chyba "Database is encrypted or is not databse se typicky vyskytuje, kdyz soubor, ktery jsi predhodil volani sqlite3_open v nem nepoznava SQLite databazi. A jestli to hlasi zamky, tak je treba revidovat zivotni cykly pristupu k DB, praces s transakcemi atd. Ve vlastnich DML prikazech problem nebude.

Ale hodne zalezi, cim a jak na tu SQLite lezes tj. jak ta DB connectivita s tou DB zachazi. V tom ti neporadim, protoze kdysi davno jsem si napsal vlastni connectivitu a s tou nam SQLite bezi v podstate 15 let v 7x24 rezimu...

Nejake zamykani ve FireDAC uz jsme tu taky resili

Offline Andy

  • Mladík
  • **
  • Příspěvků: 51
  • Karma: 0
Re:Jak spravne pracovat s sql?
« Odpověď #4 kdy: 13-04-2017, 23:21:37 »
Prepodkladam, ze jsi slysel, ze SQLlite DB muze menit pouze jeden proces v jeden okamzik, tak snad ti tam nebezi zapisovaci a nejaky odmazavajici thread soucasne apod. tj ze mas max. jeden proces, ktery si DB otevrel s flagem SQLITE_OPEN_READWRITE a ostatni oteviras jako SQLITE_OPEN_READONLY

To bude mozna ten problem  :-[ jelikoz v jednom vlakne porad pridavam nove radky a  v druhem ktere to synchronizuje tak ty pridane radky oznacuji jako synchronizovane...

Otazka tim padem je zda bude pro mne jednodussi(rozumej rychlejsi) nastudovat si poradne SQLite nebo zkusit to prehodit na IBToGO (nikdy jsem s ni nepracoval a vlastne ani nevim zda by to muj problem vyresilo)

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 1743
  • Karma: 75
    • Verze Delphi: D2007, XE3, DX10
Re:Jak spravne pracovat s sql?
« Odpověď #5 kdy: 14-04-2017, 09:39:44 »
Otazka tim padem je zda bude pro mne jednodussi(rozumej rychlejsi) nastudovat si poradne SQLite
Protoze tvuj problem se da vyresit pomerne jednoduse:
- nekde na zacatku si jednou otevres DB pro RW pristup
- kazdy, kdo bude chtit v DB neco menit bude muset nejprve ziskat handle (connection) te otevrene  DB
- kdyz ho ziska, pouzije se pro vykonani DML prikazu tj. BEGINTRANSACTION -> SQL commands -> COMMIT/ROLLBACKTRANSACTION
- uvolni connection pro dalsi pouziti

Jinymi slovy, vsechny thready menici obsah DB serializujes napr. pomoci mutexu u handleru k otevrene SQLite.

Ale otazka zustava: cim na ni lezes, protoze mam dojem, ze zrovna FireDAC nejakou podporu
zamykani DB maji a jak jsem psal, uz jsme to tady diskutovali, ale s Delphi uz leta nedelam
a navic HOSIP.

K te vymene za neco jineho: je treba si uvedomit, ze vic threadu nemuze bezstarostne psat najednou ani
do obycejneho souboru na disku a musi se to resit pres file sharing ziskavanim pristupu k souboru apod.

Kdyz se pouziva velky RDBMS, tak tyhle zalezistosti resi server, kdyz se pouzije embedded DB, tak se dostavas
na uroven prace se soubory a je otazka, kdo a jak tim v tom (ne)pomohl.



Offline Mi.Chal.

  • Guru
  • *****
  • Příspěvků: 566
  • Karma: 23
Re:Jak spravne pracovat s sql?
« Odpověď #6 kdy: 14-04-2017, 11:56:32 »
Otazka tim padem je zda bude pro mne jednodussi(rozumej rychlejsi) nastudovat si poradne SQLite
Protoze tvuj problem se da vyresit pomerne jednoduse:
- nekde na zacatku si jednou otevres DB pro RW pristup
- kazdy, kdo bude chtit v DB neco menit bude muset nejprve ziskat handle (connection) te otevrene  DB
- kdyz ho ziska, pouzije se pro vykonani DML prikazu tj. BEGINTRANSACTION -> SQL commands -> COMMIT/ROLLBACKTRANSACTION
- uvolni connection pro dalsi pouziti

Jinymi slovy, vsechny thready menici obsah DB serializujes napr. pomoci mutexu u handleru k otevrene SQLite.

Co jsem pochopil z toho odkazu, co jsem sem dával, tak by to měla umět i ta sqllite, pokud se správně nastaví. Buď si tu synchronizaci řeší člověk sám nebo to dělá ta db. Pokud to používá jeden thread, tak se dá na synchronizace vykašlat a bude to o něco rychlejší, jinak se to někde synchronizovat musí vždycky.

Offline Mi.Chal.

  • Guru
  • *****
  • Příspěvků: 566
  • Karma: 23
Re:Jak spravne pracovat s sql?
« Odpověď #7 kdy: 14-04-2017, 11:59:26 »
safeString jen nahrazuje uvozovky

To není jediný problém, častým zdrojem problémů může být třeba formát datumu, čísel, času atd. A u chytřejších db to může mít i vliv na výkon (pokud se posílá stejný string, tak se nemusí pokaždé znovu analyzovat. Pokud se místo parametrů posílají přímo hodnoty ve stringu, tak to je většinou jiný string a musí se znovu analyzovat).

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2731
  • Karma: 26
    • Verze Delphi: XE7 professional
Re:Jak spravne pracovat s sql?
« Odpověď #8 kdy: 14-04-2017, 13:11:33 »
Excellent
Rated 1 time
Citace
Pokud se místo parametrů posílají přímo hodnoty ve stringu, tak to je většinou jiný string a musí se znovu analyzovat
A ešte k tomu prisupuje SQL injection. Veľká bezpečnostná medzera/chyba.
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 1743
  • Karma: 75
    • Verze Delphi: D2007, XE3, DX10
Re:Jak spravne pracovat s sql?
« Odpověď #9 kdy: 14-04-2017, 17:21:01 »
Co jsem pochopil z toho odkazu, co jsem sem dával, tak by to měla umět i ta sqllite, pokud se správně nastaví.
Vypada to, ze mas pravdu.

My s SQLite zacinali jeste ve verzich 2.x a tohle zavedli nekdy ve verzi 3.3.1 Sice jsme taky prubezne upgradovali na 3.x, ale na tohle uz nikdo nesahal. A od verze 3.4.2 z roku 2017 uz tu SQLite nikdo neupgradoval vubec a bezi porad, takze jsem ponekud obsolete

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 1743
  • Karma: 75
    • Verze Delphi: D2007, XE3, DX10
Re:Jak spravne pracovat s sql?
« Odpověď #10 kdy: 14-04-2017, 20:23:18 »
To bude mozna ten problem  :-[ jelikoz v jednom vlakne porad pridavam nove radky a  v druhem ktere to synchronizuje tak ty pridane radky oznacuji jako synchronizovane...
Takze pokud SQLite ma jakode fault rezim SERIALIZABLE, tak pak ta namitka pada a jedine co musis dodrzet (a to plati obecne pro kazdou DB connectivitu), aby kazdy thread mel vlastni connection tj. kazdy thread si otevrel svuj pristup k DB.
« Poslední změna: 14-04-2017, 20:29:23 od pf1957 »

Offline Andy

  • Mladík
  • **
  • Příspěvků: 51
  • Karma: 0
Re:Jak spravne pracovat s sql?
« Odpověď #11 kdy: 16-04-2017, 16:51:22 »
pro kazde vlakno sve spojeni samozrejme mam ale asi to vypada ze skutecne jsem spis nekde neuhledel a bude tam kolize pristupu. Prvotne jsem myslel ze mam spatne ConnectionDefinition nebo ze je problem v primem pristupu k datum, no alespon mne to konecne donuti napsat si class pro praci s db a napsat to poradne se vsim co k tomu patri :-)

Za cenne rady vsem velice dekuji

Offline geby

  • Plnoletý
  • ***
  • Příspěvků: 163
  • Karma: 13
    • Verze Delphi: 7, 2007, XE2, 10.2
    • Synapse
Re:Jak spravne pracovat s sql?
« Odpověď #12 kdy: 20-06-2017, 22:22:03 »
Ve chvili, kdy jeden thread narazi na zamek z jineho threadu, tak to skonci chybou "database is busy". Muzes si ale nastavit svoji rutinu, ktera se misto teto chyby zavola. Nebo si muze zapnou tzv. busytimeout, coz je predpripravena rutina, ktera tu kolizni operaci zkousi nekolikrat znova, dokud neubehne timeout.

Da se to nastavit i pomoci pragma SQL prikazu, viz. https://sqlite.org/pragma.html#pragma_busy_timeout

Muj lehky sqlite klient SqliteWrap podporuje, tak tam muzes nacerpat inspiraci.

 

S rychlou odpovědí můžete používat BB kódy a emotikony jako v běžném okně pro odpověď, ale daleko rychleji.

Jméno: E-mail:
Ověření:
Datový typ v Delphi, který má True a False: