MIME-Version: 1.0
From: Aditya Toshniwal <aditya.toshniwal@enterprisedb.com>
Date: Mon, 16 Dec 2019 17:39:37 +0530
Message-ID: 
 <CAM9w-_mgjt9ju6057rQi87XpaA5CXSnwkYa=pgLf7wvW4nCs-g@mail.gmail.com>
Subject: [pgAdmin][RM4988] Refactoring of all the SQL files used by pgAdmin4
To: pgadmin-hackers <pgadmin-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000fa31230599d11749"
Precedence: bulk

--000000000000fa31230599d11749
Content-Type: multipart/alternative; boundary="000000000000fa31210599d11747"

--000000000000fa31210599d11747
Content-Type: text/plain; charset="UTF-8"

Hi Hackers,

Attached patch which is related to RM4988, reduces the number of hits
to edit_mode_types.sql
(an expensive one).
Currently, this is query is executed for every column one by one when table
properties dialog is opened. I've added edit_mode_types_bulk.sql, which
will fetch the edit types for all the distinct column types used in the
table in a single DB trip and use that data for each column.
Also, while generating RE-SQL for columns, edit_mode_types.sql will not be
executed.
This will definitely reduce time.

Kindly review.

-- 
Thanks and Regards,
Aditya Toshniwal
Sr. Software Engineer | EnterpriseDB India | Pune
"Don't Complain about Heat, Plant a TREE"

--000000000000fa31210599d11747
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:verdana,=
sans-serif">Hi Hackers,</div><div class=3D"gmail_default" style=3D"font-fam=
ily:verdana,sans-serif"><br></div><div class=3D"gmail_default" style=3D""><=
font face=3D"verdana, sans-serif" color=3D"#000000">Attached patch which is=
 related to RM4988, reduces the number of hits to=C2=A0<span style=3D"font-=
variant-ligatures:no-common-ligatures">edit_mode_types.sql (an expensive on=
e).</span></font></div><div class=3D"gmail_default" style=3D""><font face=
=3D"verdana, sans-serif" color=3D"#000000"><span style=3D"font-variant-liga=
tures:no-common-ligatures">Currently, this is query is executed for every c=
olumn one by one when table properties dialog is opened. I&#39;ve added=C2=
=A0</span><span style=3D"font-variant-ligatures:no-common-ligatures">edit_m=
ode_types_bulk.sql, which will fetch the edit types for all the distinct co=
lumn types used in the table in a single DB trip and use that data for each=
 column.</span></font></div><div class=3D"gmail_default" style=3D""><font c=
olor=3D"#000000" face=3D"verdana, sans-serif"><span style=3D"font-variant-l=
igatures:no-common-ligatures">Also, while generating RE-SQL for columns,=C2=
=A0</span></font><font face=3D"verdana, sans-serif">edit_mode_types.sql wil=
l not be executed.</font></div><div class=3D"gmail_default" style=3D""><spa=
n style=3D"font-variant-ligatures:no-common-ligatures"><font face=3D"verdan=
a, sans-serif" color=3D"#000000">This will definitely reduce time.</font></=
span></div><div class=3D"gmail_default" style=3D""><span style=3D"font-vari=
ant-ligatures:no-common-ligatures"><font face=3D"verdana, sans-serif" color=
=3D"#000000"><br></font></span></div><div class=3D"gmail_default" style=3D"=
"><span style=3D"font-variant-ligatures:no-common-ligatures"><font face=3D"=
verdana, sans-serif" style=3D"" color=3D"#000000">Kindly review.</font></sp=
an></div>


<div><br></div>-- <br><div dir=3D"ltr" class=3D"gmail_signature" data-smart=
mail=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div d=
ir=3D"ltr"><div><div dir=3D"ltr"><font size=3D"2" color=3D"#000000" face=3D=
"verdana, sans-serif">Thanks and Regards,</font><div><span style=3D"backgro=
und-color:rgb(255,255,255)"><font size=3D"2" color=3D"#000000" face=3D"verd=
ana, sans-serif">Aditya Toshniwal</font></span></div><div><font face=3D"ver=
dana, sans-serif"><span style=3D"background-color:rgb(255,255,255)"><font s=
ize=3D"2" color=3D"#000000">Sr. Software Engineer |=C2=A0EnterpriseDB India=
 |=C2=A0</font></span><span style=3D"color:rgb(0,0,0)">Pune</span></font></=
div><div><font color=3D"#38761d" face=3D"verdana, sans-serif" size=3D"1">&q=
uot;Don&#39;t Complain about Heat, Plant a TREE&quot;</font></div></div></d=
iv></div></div></div></div></div></div></div>

--000000000000fa31210599d11747--

--000000000000fa31230599d11749
Content-Type: application/octet-stream; name="RM4988.edit_types.patch"
Content-Disposition: attachment; filename="RM4988.edit_types.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_k48e9uzi0>
X-Attachment-Id: f_k48e9uzi0

ZGlmZiAtLWdpdCBhL3dlYi9wZ2FkbWluL2Jyb3dzZXIvc2VydmVyX2dyb3Vwcy9zZXJ2ZXJzL2Rh
dGFiYXNlcy9zY2hlbWFzL3RhYmxlcy9jb2x1bW5zL19faW5pdF9fLnB5IGIvd2ViL3BnYWRtaW4v
YnJvd3Nlci9zZXJ2ZXJfZ3JvdXBzL3NlcnZlcnMvZGF0YWJhc2VzL3NjaGVtYXMvdGFibGVzL2Nv
bHVtbnMvX19pbml0X18ucHkKaW5kZXggZWUwODI4MTBiLi5jMGM0MGU0MTcgMTAwNjQ0Ci0tLSBh
L3dlYi9wZ2FkbWluL2Jyb3dzZXIvc2VydmVyX2dyb3Vwcy9zZXJ2ZXJzL2RhdGFiYXNlcy9zY2hl
bWFzL3RhYmxlcy9jb2x1bW5zL19faW5pdF9fLnB5CisrKyBiL3dlYi9wZ2FkbWluL2Jyb3dzZXIv
c2VydmVyX2dyb3Vwcy9zZXJ2ZXJzL2RhdGFiYXNlcy9zY2hlbWFzL3RhYmxlcy9jb2x1bW5zL19f
aW5pdF9fLnB5CkBAIC03MTcsNyArNzE3LDEwIEBAIGNsYXNzIENvbHVtbnNWaWV3KFBHQ2hpbGRO
b2RlVmlldywgRGF0YVR5cGVSZWFkZXIpOgogICAgICAgICAgICAgICAgICAgICBjb2x1bW5fdXRp
bHMudHlwZV9mb3JtYXR0ZXIoZGF0YVsnY2x0eXBlJ10pCiAKICAgICAgICAgICAgICMgV2Ugd2ls
bCBhZGQgdGFibGUgJiBzY2hlbWEgYXMgd2VsbAotICAgICAgICAgICAgZGF0YSA9IGNvbHVtbl91
dGlscy5jb2x1bW5fZm9ybWF0dGVyKHNlbGYuY29ubiwgdGlkLCBjbGlkLCBkYXRhKQorICAgICAg
ICAgICAgIyBQYXNzaW5nIGVkaXRfdHlwZXNfbGlzdCBwYXJhbSBzbyB0aGF0IGl0IGRvZXMgbm90
IGZldGNoCisgICAgICAgICAgICAjIGVkaXQgdHlwZXMuIEl0IGlzIG5vdCByZXF1aXJlZCBoZXJl
LgorICAgICAgICAgICAgZGF0YSA9IGNvbHVtbl91dGlscy5jb2x1bW5fZm9ybWF0dGVyKHNlbGYu
Y29ubiwgdGlkLCBjbGlkLAorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgIGRhdGEsIFtdKQogCiAgICAgICAgICAgICBTUUwsIG5hbWUgPSBzZWxmLmdldF9z
cWwoc2NpZCwgdGlkLCBOb25lLCBkYXRhLCBpc19zcWw9VHJ1ZSkKICAgICAgICAgICAgIGlmIG5v
dCBpc2luc3RhbmNlKFNRTCwgKHN0ciwgdW5pY29kZSkpOgpkaWZmIC0tZ2l0IGEvd2ViL3BnYWRt
aW4vYnJvd3Nlci9zZXJ2ZXJfZ3JvdXBzL3NlcnZlcnMvZGF0YWJhc2VzL3NjaGVtYXMvdGFibGVz
L2NvbHVtbnMvdXRpbHMucHkgYi93ZWIvcGdhZG1pbi9icm93c2VyL3NlcnZlcl9ncm91cHMvc2Vy
dmVycy9kYXRhYmFzZXMvc2NoZW1hcy90YWJsZXMvY29sdW1ucy91dGlscy5weQppbmRleCBiYWQ5
MzgxNDIuLjE1YTMxNDNjNiAxMDA2NDQKLS0tIGEvd2ViL3BnYWRtaW4vYnJvd3Nlci9zZXJ2ZXJf
Z3JvdXBzL3NlcnZlcnMvZGF0YWJhc2VzL3NjaGVtYXMvdGFibGVzL2NvbHVtbnMvdXRpbHMucHkK
KysrIGIvd2ViL3BnYWRtaW4vYnJvd3Nlci9zZXJ2ZXJfZ3JvdXBzL3NlcnZlcnMvZGF0YWJhc2Vz
L3NjaGVtYXMvdGFibGVzL2NvbHVtbnMvdXRpbHMucHkKQEAgLTY0LDcgKzY0LDggQEAgZGVmIGdl
dF9wYXJlbnQoY29ubiwgdGlkLCB0ZW1wbGF0ZV9wYXRoPU5vbmUpOgogCiAKIEBnZXRfdGVtcGxh
dGVfcGF0aAotZGVmIGNvbHVtbl9mb3JtYXR0ZXIoY29ubiwgdGlkLCBjbGlkLCBkYXRhLCB0ZW1w
bGF0ZV9wYXRoPU5vbmUpOgorZGVmIGNvbHVtbl9mb3JtYXR0ZXIoY29ubiwgdGlkLCBjbGlkLCBk
YXRhLCBlZGl0X3R5cGVzX2xpc3Q9Tm9uZSwKKyAgICAgICAgICAgICAgICAgICAgIHRlbXBsYXRl
X3BhdGg9Tm9uZSk6CiAgICAgIiIiCiAgICAgVGhpcyBmdW5jdGlvbiB3aWxsIHJldHVybiBmb3Jt
YXR0ZWQgb3V0cHV0IG9mIHF1ZXJ5IHJlc3VsdAogICAgIGFzIHBlciBjbGllbnQgbW9kZWwgZm9y
bWF0IGZvciBjb2x1bW4gbm9kZQpAQCAtMTQ0LDI1ICsxNDUsMTcgQEAgZGVmIGNvbHVtbl9mb3Jt
YXR0ZXIoY29ubiwgdGlkLCBjbGlkLCBkYXRhLCB0ZW1wbGF0ZV9wYXRoPU5vbmUpOgogICAgICMg
d2Ugd2lsbCBzZW5kIGZpbHRlcmVkIHR5cGVzIHJlbGF0ZWQgdG8gY3VycmVudCB0eXBlCiAgICAg
dHlwZV9pZCA9IGRhdGFbJ2F0dHR5cGlkJ10KIAotICAgIFNRTCA9IHJlbmRlcl90ZW1wbGF0ZSgi
LyIuam9pbihbdGVtcGxhdGVfcGF0aCwgJ2lzX3JlZmVyZW5jZWQuc3FsJ10pLAotICAgICAgICAg
ICAgICAgICAgICAgICAgICB0aWQ9dGlkLCBjbGlkPWNsaWQpCi0KLSAgICBzdGF0dXMsIGlzX3Jl
ZmVyZW5jZSA9IGNvbm4uZXhlY3V0ZV9zY2FsYXIoU1FMKQotCi0gICAgZWRpdF90eXBlc19saXN0
ID0gbGlzdCgpCi0gICAgIyBXZSB3aWxsIG5lZWQgcHJlc2VudCB0eXBlIGluIGVkaXQgbW9kZQot
ICAgIGVkaXRfdHlwZXNfbGlzdC5hcHBlbmQoZGF0YVsnY2x0eXBlJ10pCi0KLSAgICBpZiBpbnQo
aXNfcmVmZXJlbmNlKSA9PSAwOgorICAgIGlmIGVkaXRfdHlwZXNfbGlzdCBpcyBOb25lOgorICAg
ICAgICBlZGl0X3R5cGVzX2xpc3QgPSBbXQogICAgICAgICBTUUwgPSByZW5kZXJfdGVtcGxhdGUo
Ii8iLmpvaW4oW3RlbXBsYXRlX3BhdGgsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgJ2VkaXRfbW9kZV90eXBlcy5zcWwnXSksCiAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICB0eXBlX2lkPXR5cGVfaWQpCiAgICAgICAgIHN0YXR1cywgcnNldCA9IGNvbm4uZXhl
Y3V0ZV8yZGFycmF5KFNRTCkKKyAgICAgICAgZWRpdF90eXBlc19saXN0ID0gW3Jvd1sndHlwbmFt
ZSddIGZvciByb3cgaW4gcnNldFsncm93cyddXQogCi0gICAgICAgIGZvciByb3cgaW4gcnNldFsn
cm93cyddOgotICAgICAgICAgICAgZWRpdF90eXBlc19saXN0LmFwcGVuZChyb3dbJ3R5cG5hbWUn
XSkKLQotICAgIGRhdGFbJ2VkaXRfdHlwZXMnXSA9IGVkaXRfdHlwZXNfbGlzdAorICAgICMgV2Ug
d2lsbCBuZWVkIHByZXNlbnQgdHlwZSBpbiBlZGl0IG1vZGUKKyAgICBlZGl0X3R5cGVzX2xpc3Qu
YXBwZW5kKGRhdGFbJ2NsdHlwZSddKQorICAgIGRhdGFbJ2VkaXRfdHlwZXNfbGlzdCddID0gZWRp
dF90eXBlc19saXN0CiAKICAgICBkYXRhWydjbHR5cGUnXSA9IERhdGFUeXBlUmVhZGVyLnBhcnNl
X3R5cGVfbmFtZShkYXRhWydjbHR5cGUnXSkKIApAQCAtMTkxLDkgKzE4NCwxMCBAQCBkZWYgZ2V0
X2Zvcm1hdHRlZF9jb2x1bW5zKGNvbm4sIHRpZCwgZGF0YSwgb3RoZXJfY29sdW1ucywKICAgICAg
ICAgcmFpc2UgRXhjZXB0aW9uKHJlcykKIAogICAgIGFsbF9jb2x1bW5zID0gcmVzWydyb3dzJ10K
LQorICAgIGVkaXRfdHlwZXMgPSB7fQogICAgICMgQWRkIGluaGVyaXRlZCBmcm9tIGRldGFpbHMg
ZnJvbSBvdGhlciBjb2x1bW5zIC0gdHlwZSwgdGFibGUKICAgICBmb3IgY29sIGluIGFsbF9jb2x1
bW5zOgorICAgICAgICBlZGl0X3R5cGVzW2NvbFsnYXR0dHlwaWQnXV0gPSBbXQogICAgICAgICBm
b3Igb3RoZXJfY29sIGluIG90aGVyX2NvbHVtbnM6CiAgICAgICAgICAgICBpZiBjb2xbJ25hbWUn
XSA9PSBvdGhlcl9jb2xbJ25hbWUnXToKICAgICAgICAgICAgICAgICBjb2xbJ2luaGVyaXRlZGZy
b20nICsgdGFibGVfb3JfdHlwZV0gPSBcCkBAIC0yMDIsOCArMTk2LDE3IEBAIGRlZiBnZXRfZm9y
bWF0dGVkX2NvbHVtbnMoY29ubiwgdGlkLCBkYXRhLCBvdGhlcl9jb2x1bW5zLAogICAgIGRhdGFb
J2NvbHVtbnMnXSA9IGFsbF9jb2x1bW5zCiAKICAgICBpZiAnY29sdW1ucycgaW4gZGF0YSBhbmQg
bGVuKGRhdGFbJ2NvbHVtbnMnXSkgPiAwOgorICAgICAgICBTUUwgPSByZW5kZXJfdGVtcGxhdGUo
Ii8iLmpvaW4oW3RlbXBsYXRlX3BhdGgsCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgJ2VkaXRfbW9kZV90eXBlc19idWxrLnNxbCddKSwKKyAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIHR5cGVfaWRzPSIsIi5qb2luKG1hcChsYW1iZGEgeDogc3RyKHgpLAorICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIGVkaXRfdHlw
ZXMua2V5cygpKSkpCisgICAgICAgIHN0YXR1cywgcmVzID0gY29ubi5leGVjdXRlXzJkYXJyYXko
U1FMKQorICAgICAgICBmb3Igcm93IGluIHJlc1sncm93cyddOgorICAgICAgICAgICAgZWRpdF90
eXBlc1tyb3dbJ21haW5fb2lkJ11dID0gcm93WydlZGl0X3R5cGVzJ10KKwogICAgICAgICBmb3Ig
Y29sdW1uIGluIGRhdGFbJ2NvbHVtbnMnXToKLSAgICAgICAgICAgIGNvbHVtbl9mb3JtYXR0ZXIo
Y29ubiwgdGlkLCBjb2x1bW5bJ2F0dG51bSddLCBjb2x1bW4pCisgICAgICAgICAgICBjb2x1bW5f
Zm9ybWF0dGVyKGNvbm4sIHRpZCwgY29sdW1uWydhdHRudW0nXSwgY29sdW1uLAorICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICBlZGl0X3R5cGVzW2NvbFsnYXR0dHlwaWQnXV0pCiAKICAgICBy
ZXR1cm4gZGF0YQogCmRpZmYgLS1naXQgYS93ZWIvcGdhZG1pbi9icm93c2VyL3NlcnZlcl9ncm91
cHMvc2VydmVycy9kYXRhYmFzZXMvc2NoZW1hcy90YWJsZXMvdGVtcGxhdGVzL2NvbHVtbnMvc3Fs
L2RlZmF1bHQvZWRpdF9tb2RlX3R5cGVzX2J1bGsuc3FsIGIvd2ViL3BnYWRtaW4vYnJvd3Nlci9z
ZXJ2ZXJfZ3JvdXBzL3NlcnZlcnMvZGF0YWJhc2VzL3NjaGVtYXMvdGFibGVzL3RlbXBsYXRlcy9j
b2x1bW5zL3NxbC9kZWZhdWx0L2VkaXRfbW9kZV90eXBlc19idWxrLnNxbApuZXcgZmlsZSBtb2Rl
IDEwMDY0NAppbmRleCAwMDAwMDAwMDAuLjJmZjE5MjE1NgotLS0gL2Rldi9udWxsCisrKyBiL3dl
Yi9wZ2FkbWluL2Jyb3dzZXIvc2VydmVyX2dyb3Vwcy9zZXJ2ZXJzL2RhdGFiYXNlcy9zY2hlbWFz
L3RhYmxlcy90ZW1wbGF0ZXMvY29sdW1ucy9zcWwvZGVmYXVsdC9lZGl0X21vZGVfdHlwZXNfYnVs
ay5zcWwKQEAgLTAsMCArMSwxMyBAQAorU0VMRUNUIHQubWFpbl9vaWQsIEFSUkFZX0FHRyh0LnR5
cG5hbWUpIGFzIGVkaXRfdHlwZXMKK0ZST00KKyhTRUxFQ1QgcGMuY2FzdHNvdXJjZSBhcyBtYWlu
X29pZCwgZm9ybWF0X3R5cGUodHQub2lkLE5VTEwpIEFTIHR5cG5hbWUKK0ZST00gcGdfdHlwZSB0
dAorICAgIEpPSU4gcGdfY2FzdCBwYyBPTiB0dC5vaWQ9cGMuY2FzdHRhcmdldAorICAgIFdIRVJF
IHBjLmNhc3Rzb3VyY2UgaW4gKHt7dHlwZV9pZHN9fSkKKyAgICBBTkQgcGMuY2FzdGNvbnRleHQg
SU4gKCdpJywgJ2EnKQorVU5JT04KK1NFTEVDVCB0dC50eXBiYXNldHlwZSBhcyBtYWluX29pZCwg
Zm9ybWF0X3R5cGUodHQub2lkLE5VTEwpIEFTIHR5cG5hbWUKK0ZST00gcGdfdHlwZSB0dAorV0hF
UkUgdHQudHlwYmFzZXR5cGUgIElOICh7e3R5cGVfaWRzfX0pCispIHQKK0dST1VQIEJZIHQubWFp
bl9vaWQ7CmRpZmYgLS1naXQgYS93ZWIvcGdhZG1pbi9icm93c2VyL3NlcnZlcl9ncm91cHMvc2Vy
dmVycy9kYXRhYmFzZXMvc2NoZW1hcy90YWJsZXMvdGVtcGxhdGVzL2NvbHVtbnMvc3FsL2RlZmF1
bHQvaXNfcmVmZXJlbmNlZC5zcWwgYi93ZWIvcGdhZG1pbi9icm93c2VyL3NlcnZlcl9ncm91cHMv
c2VydmVycy9kYXRhYmFzZXMvc2NoZW1hcy90YWJsZXMvdGVtcGxhdGVzL2NvbHVtbnMvc3FsL2Rl
ZmF1bHQvaXNfcmVmZXJlbmNlZC5zcWwKZGVsZXRlZCBmaWxlIG1vZGUgMTAwNjQ0CmluZGV4IDUy
YTRmZmE4Ni4uMDAwMDAwMDAwCi0tLSBhL3dlYi9wZ2FkbWluL2Jyb3dzZXIvc2VydmVyX2dyb3Vw
cy9zZXJ2ZXJzL2RhdGFiYXNlcy9zY2hlbWFzL3RhYmxlcy90ZW1wbGF0ZXMvY29sdW1ucy9zcWwv
ZGVmYXVsdC9pc19yZWZlcmVuY2VkLnNxbAorKysgL2Rldi9udWxsCkBAIC0xLDYgKzAsMCBAQAot
U0VMRUNUIENPVU5UKDEpCi1GUk9NIHBnX2RlcGVuZCBkZXAKLSAgICBKT0lOIHBnX2NsYXNzIGNs
IE9OIGRlcC5jbGFzc2lkPWNsLm9pZCBBTkQgcmVsbmFtZT0ncGdfcmV3cml0ZScKLSAgICBXSEVS
RSByZWZvYmppZD0ge3t0aWR9fTo6b2lkCi0gICAgQU5EIGNsYXNzaWQ9J3BnX2NsYXNzJzo6cmVn
Y2xhc3MKLSAgICBBTkQgcmVmb2Jqc3ViaWQ9IHt7Y2xpZHxxdExpdGVyYWx9fTsK
--000000000000fa31230599d11749--