{"id":99469,"date":"2021-03-18T10:23:26","date_gmt":"2021-03-18T08:23:26","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/vypusk-subd-sqlite-3-35"},"modified":"2021-03-18T10:23:26","modified_gmt":"2021-03-18T08:23:26","slug":"vypusk-subd-sqlite-3-35","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/novosti-interneta\/vypusk-subd-sqlite-3-35","title":{"rendered":"L\u00ebshimi i DBMS SQLite 3.35","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
\u00cbsht\u00eb publikuar l\u00ebshimi SQLite 3.35, nj\u00eb DBMS i leht\u00eb, i dizajnuar si nj\u00eb bibliotek\u00eb e lidhur. Kodi SQLite shp\u00ebrndahet si pron\u00eb publike, q\u00eb do t\u00eb thot\u00eb se mund t\u00eb p\u00ebrdoret pa kufizime dhe falas p\u00ebr \u00e7do q\u00ebllim. Mb\u00ebshtetja financiare p\u00ebr zhvilluesit e SQLite ofrohet nga nj\u00eb konsorcium i krijuar posa\u00e7\u00ebrisht, q\u00eb p\u00ebrfshin kompani si Adobe, Oracle, Mozilla, Bentley dhe Bloomberg. <\/p>\n
Ndryshimet kryesore: <\/p>\n
\n
Jan\u00eb shtuar funksione matematikore t\u00eb inkorporuara (log2(), cos(), tg(), exp(), ln(), pow() etj.), t\u00eb cilat mund t\u00eb p\u00ebrdoren n\u00eb SQL. P\u00ebrfshirja e funksioneve t\u00eb inkorporuara k\u00ebrkon nd\u00ebrtimin me opsionin «-DSQLITE_ENABLE_MATH_FUNCTIONS».\n
\u00cbsht\u00eb realizuar mb\u00ebshtetje p\u00ebr shprehjen «ALTER TABLE DROP COLUMN» p\u00ebr t\u00eb hequr kolona nga tabela dhe p\u00ebr t\u00eb pastruar t\u00eb dh\u00ebnat e ruajtura m\u00eb par\u00eb n\u00eb k\u00ebt\u00eb kolon\u00eb.\n
\u00cbsht\u00eb zgjeruar implementimi i operacionit UPSERT (shtoni-o-modifikoni), i cili lejon, p\u00ebrmes shprehjeve si «INSERT … ON CONFLICT DO NOTHING\/UPDATE», t\u00eb injoroheni gabimin ose t\u00eb kryeni nj\u00eb p\u00ebrdit\u00ebsim n\u00eb vend t\u00eb shtimit n\u00eb rast se shtimi i t\u00eb dh\u00ebnave p\u00ebrmes «INSERT» nuk \u00ebsht\u00eb i mundur (p.sh., n\u00ebse sh\u00ebnimi tashm\u00eb ekziston, mund t\u00eb kryhet nj\u00eb UPDATE n\u00eb vend t\u00eb INSERT). N\u00eb versionin e ri \u00ebsht\u00eb e lejuar t\u00eb specifikoni disa blloqe «ON CONFLICT», t\u00eb cilat do t\u00eb p\u00ebrpunohen n\u00eb rendin e ndjekjes. N\u00eb bllokun e fundit «ON CONFLICT» \u00ebsht\u00eb e lejuar t\u00eb mos specifikoni parametrin e p\u00ebrcaktimit t\u00eb konfliktit p\u00ebr t\u00eb p\u00ebrdorur «DO UPDATE».\n
N\u00eb operacionet DELETE, INSERT dhe UPDATE \u00ebsht\u00eb implementuar mb\u00ebshtetje p\u00ebr shprehjen RETURNING, me an\u00eb t\u00eb s\u00eb cil\u00ebs mund t\u00eb nxirret p\u00ebrmbajtja e sh\u00ebnimit t\u00eb fshir\u00eb, t\u00eb shtuar ose t\u00eb ndryshuar. P\u00ebr shembull, shprehja «insert into … returning id» do t\u00eb kthej\u00eb identifikuesin e rreshtit t\u00eb shtuar, nd\u00ebrsa «update … set price = price * 1.10 returning price» do t\u00eb kthej\u00eb vler\u00ebn e ndryshuar t\u00eb \u00e7mimit.\n
P\u00ebr shprehjet e tabelave t\u00eb p\u00ebrgjithshme (Common Table Expression, CTE), t\u00eb cilat lejojn\u00eb p\u00ebrdorimin e grupeve rezultatore t\u00eb p\u00ebrkohshme me emra, t\u00eb definuara p\u00ebrmes operatorit WITH, \u00ebsht\u00eb lejuar zgjedhja e modit «MATERIALIZED» dhe «NOT MATERIALIZED». «MATERIALIZED» n\u00ebnkupton ruajtjen e t\u00eb dh\u00ebnave t\u00eb k\u00ebrkes\u00ebs s\u00eb caktuar n\u00eb nj\u00eb tabel\u00eb fizike t\u00eb ve\u00e7ant\u00eb, duke pasuar k\u00ebrkesat p\u00ebr nxjerrjen e t\u00eb dh\u00ebnave nga kjo tabel\u00eb, nd\u00ebrsa me «NOT MATERIALIZED» p\u00ebr \u00e7do k\u00ebrkes\u00eb n\u00eb pamje do t\u00eb realizohen k\u00ebrkesa t\u00eb p\u00ebrs\u00ebritura. Fillimisht, n\u00eb SQLite, me default \u00ebsht\u00eb p\u00ebrdorur moda «NOT MATERIALIZED», por tani p\u00ebr CTE-t\u00eb e p\u00ebrdorura m\u00eb shum\u00eb se nj\u00eb her\u00eb \u00ebsht\u00eb ndryshuar n\u00eb «MATERIALIZED».\n
\u00cbsht\u00eb zvog\u00ebluar konsumimi i memories gjat\u00eb kryerjes s\u00eb operacionit VACUUM p\u00ebr DB t\u00eb p\u00ebrfshir\u00eb me vlera shum\u00eb t\u00eb m\u00ebdha me tipe TEXT ose BLOB.\n
Jan\u00eb b\u00ebr\u00eb pun\u00eb p\u00ebr t\u00eb rritur performanc\u00ebn e optimizuesit dhe planifikuesit t\u00eb pyetjeve:\n
\n
Jan\u00eb shtuar optimizime kur p\u00ebrdoren funksionet min dhe max me shprehjen «IN».\n
\u00cbsht\u00eb p\u00ebrshpejtuar ekzekutimi i operatorit EXISTS.\n
\u00cbsht\u00eb implementuar shtrirja e n\u00ebnpyetjeve nga shprehjet UNION ALL, t\u00eb p\u00ebrdorura n\u00eb p\u00ebrb\u00ebrje t\u00eb JOIN.\n
\u00cbsht\u00eb angazhuar indeksi p\u00ebr shprehjet IS NOT NULL.\n
\u00cbsht\u00eb siguruar konvertimi i «x IS NULL» dhe «x IS NOT NULL» n\u00eb FALSE ose TRUE p\u00ebr kolona q\u00eb kan\u00eb shenj\u00ebn «NOT NULL».\n
Kontrolli i \u00e7el\u00ebsave t\u00eb jasht\u00ebm \u00ebsht\u00eb injoruar n\u00eb UPDATE, n\u00ebse operacioni nuk ndryshon kolonat q\u00eb lidhen me \u00e7el\u00ebsin e jasht\u00ebm.\n
\u00cbsht\u00eb lejuar l\u00ebvizja e pjes\u00ebve t\u00eb bllokut WHERE n\u00eb n\u00ebnk\u00ebrkesa q\u00eb p\u00ebrmbajn\u00eb funksione dritare, n\u00ebse k\u00ebto pjes\u00eb jan\u00eb t\u00eb kufizuara nga puna me constante dhe kopje t\u00eb shprehjeve nga blloqet «PARTITION BY», t\u00eb p\u00ebrdorura n\u00eb funksionet dritare. <\/ul>\n
Ndryshimet n\u00eb interfejsin e komand\u00ebs n\u00eb linj\u00eb:\n
\n
\u00cbsht\u00eb shtuar komanda «.filectrl data_version».\n
N\u00eb komandat «.once» dhe «.output» \u00ebsht\u00eb realizuar mb\u00ebshtetje p\u00ebr transmetimin e daljes n\u00eb nj\u00eb p\u00ebrpunues t\u00eb thirrur me kanale t\u00eb paem\u00ebruara («|»).\n
N\u00eb komand\u00ebn «.stats» jan\u00eb shtuar argumentet «stmt» dhe «vmstep» p\u00ebr t\u00eb nxjerr\u00eb statistika mbi shprehjet dhe num\u00ebruesit e makineris\u00eb virtuale. <\/ul>\n<\/ul>\n