Refactoring de consultas de SQL embebidas aProcedimientos almacenadosM.C. Juan Carlos Olivares RojasPRESEN!"Joa#u$n rejo Soniaoledo %avala Joa#u$nINDICEPlanteamiento del &roblema''''''''''''''''''''.....(Objetivo general'''''''''''''''''''''''''..')Objetivo es&ec$fico'''''''''''''''''''''''''..)Marco te*rico'''''''''''''''''''''''''.'.+,-.Estado de arte'''''''''''''''''''''''''..--,()Planteamiento de la soluci*n'''''''''''''''''''.-/,-0Conclusi*n''''''''''''''''''''''''''...'...(11Planteamiento del problema:Siendo SQLun S234 mu5 usado &ara la creaci*n de bases de datos se tiene ungran dilema #ue es el &unto a resolver en esta investigaci*n. !ctualmente no e6isten 7erramientas #ue &ermitan migrar consultas embebidas a&rocedimientos almacenados es &or ello #ue lo #ue se &retende es investigar o7allarla maneradedesarrollaruncom&ilador #ue&ermitareali8aresteti&odeconsultas de manera m9s efica8 5 eficiente cum&liendo con los re#uerimientos #ueconsiste en migrarlos desde distintos sistemas de gesti*n de datos: de S5base aPostgres#l. El objetivo &rinci&al es automati8ar las consultas #ue se reali8an en la base dedatosconvirti;ndolasa&rocedimientosalmacenadosdebidoa#ueunadelasgrandes ventajas #ue esto nos ofrece es #ue aumentar9 el rendimiento de nuestrosistema: es decir: nos a5udara a la automati8aci*n de los &rocesos o consultas#ue se reali8an en la base de datos al no tenerse #ue mandar datos directamenteal servidor.2OBJETIVO GENERAL:Migrar una consulta embebida a un &rocedimiento almacenado: &ara automati8arel rendimiento de la base de datos.OBJETIVO ESPECIFICO: !utomati8ar el tiem&o en #ue se reali8an los &rocesos en la base de datos. 4etectar &osibles errores #ue ocurren al momento de migrar la consulta.ar!o te"ri!o 3!ntesdecomen8ar connuestro&ro5ectovamosanecesitar de&rogramas57erramientas #ue son con las #ue vamos a trabajar en esta investigaci*n.Para el desarrollo de este &ro5ecto se 7an indagado varios temas #ue sedesconoc$an &ara &oder darle continuidad al mismo: uno de los cuales es comoreali8ar consultas 5 &rocedimientos almacenados en S243 #ue anteriormente no7ab$amos utili8ado los cuales son s5base 5 &ostgres#l.Para&oder &oner en&r9cticaloinvestigado: seinstalaron&rinci&almentelosSistemas 2estores de 3ase de 4atos Sare 5 sistemas o&erativosest9ndar: enfocado de forma es&ecial a la inteligencia de negocios. La combinaci*n de la fuer8a de S!P con el e6celente desem&e?o en consultas 5eficienciaenel almacenamientodedatosestructurados5no,estructuradosdeS5base =Q &ermiten a las em&resas lograr una significativa reducci*n de tiem&o 5costos: adem9s de o&timi8ar la ca&acidad de almacenamiento. .4Enel siguiente@i&erv$nculosecitaunP4Aenel cual see6&licademaneraentendible como funciona este sistema gestor de base de datos.S5base Sinta6isEl otro S234 a utili8ar es PostgreSQLPostgreSQL PostgreSQLPostgreSQL es un &otente sistema de base de datos objeto,relacional de c*digoabierto. Cuentaconm9sde-1a?osdedesarrolloactivo5unaar#uitectura&robada#uese7aganadounas*lidare&utaci*ndefiabilidadeintegridaddedatos. Se ejecuta en los &rinci&ales sistemas o&erativos #ue e6isten en laactualidad como" Linu6 BN=C D!=C: 3S4: @P,BC: S2= =R=C: Mac OS C: Solaris: ruE+F Gindo>sEn nuestro caso lo ejecutaremos con Gindo>s /.Es totalmente com&atible con !C=4: tiene so&orte com&leto &ara claves for9neas:uniones: vistas: dis&aradores 5 &rocedimientos almacenados Den varioslenguajesF.=nclu5ela ma5or$a delosti&osdedatosdel SQL (../: inclu5endo=NE2ER:num;rico: 3OOLE!N: C@!R: H!RC@!R: 4!E: =NERH!L: 5 =MES!MP.5ambi;nso&orta almacenamientode objetosbinarios grandes:comoim9genes:sonidos o v$deo. Cuenta con interfaces nativas de &rogramaci*n &ara C I C J J:Java: Net:Perl:P5t7on: Rub5: cl: O43C: entreotros:5la documentaci*n#ueactualmente e6iste es realmente e6ce&cional.El siguiente7i&erv$nculoesunmanual b9sico&araPostgrSQL #ueesmu5 Ktil&ara &oder mani&ular tus 344.Manual b9sico &ara PostgrSQLOtro &unto a tratar en el desarrollo de este &ro5ecto 5 #ui89 el m9s im&ortante: escomo reali8ar estas consultas embebidas 5 &rocedimientos almacenados en &7& 5de esta manera com&arar tiem&os: es decir: #ue nos conviene m9s : sireali8arconsultas o &rocedimientos almacenados.Para ello lo &rimero #ue tenemos #ue reali8ar es la cone6i*n de PostgreSQL Con&7&En el video #ue se cita a continuaci*n e6&lica c*mo 7acer esta cone6i*n. 7tt&s"II>>>.5outube.comI>atc7LvMP2CE(sl%N> Para ello tuvimos #ue instalar la 7erramienta Macromedia Dream$eaberLa cual nos a5udara a reali8ar la cone6i*n.Parareali8ar lacone6i*n: tambi;nutili8amos6am&&enel cual 7icimosunasconfiguraciones: una de ella fue: des comentar la l$nea: e6tensi*n &7&N&gs#l: laotra fue cambiar los &uertos debido a #ue los marcaba ocu&ados.=niciamos el 6am&& 5 corremos el servidor a&ac7e &ara &oder 7acer la cone6i*n con la base de datos en &ostgres#l.6 Para &oder enla8ar la base de datos con &ostgres#l debemos tomar en cuenta varios &asos &ara #ue no nos mar#ue un error con la cone6i*n.-. Co&iar el arc7ivo" libp%&dll #ue se encuentra en " 6am&&I&7& 5 lo vamos a &egar en " 6am&&IIa&ac7eIbin7Posteriormente lo &egamos.(. Luego vamos nos dirigimos ala l$nea del arc7ivoc"I6am&&I&7&I&7&.ini: la e6tensi*n #ue vamos a des comentar es Mp'p(p)*%l&dll8Por ultimo buscamos el &uerto /. en !&ac7e D7tt&d.confF. 9< lo cambiamos &or /./..10ESTADO DE ARTE Crearemos una nueva base de datos en &ostgres#l sobre la #ue trabajaremos.Entramos a &ostgres#l 5 damos doble clic en la &arte del servidor de local 7ost5 luego &onemos la contrase?a #ue 7ab$amos &uesta a la 7ora de la instalaci*n de &ostgres#l &ara &oder acceder a nuestra base de datos.Como nosotros 5a ten$amos una base de datos Dtec del valleF #ue fue con la #ue trabajamos solo es necesario &oner la base en nuestra com&utadora 5 esto se 7i8o con los siguientes &asos. Es necesario tener el bacOu& de la base de datos Creamos una base de datos nueva 5 le asignamos el nombre #ue #ueramos.11 Para restaurar la base de datos del tec del valle solo damos un restore.12Seleccionamos la ruta donde se encuentra nuestra base 5 damos restore nuevamente.13< a7ora 5a tenemos todos los datos en nuestra base llamada Pte!+14=NSER!7ora comen8aremos con la migraci*n de una consulta embebida aun&rocedimiento almacenado describiendo los &asos #ue se llevan a cabo. Lo &rimero es identificar si la consulta viene embebida en &osgres#l os5base.En este caso es una consulta de un insert 7ec7o en &ostgres#l.--insert into causa_superavit(rfc,periodo,horas_plaa,horas_!rupo,horas_super,causa,id_causa" values(#$rfc#,#$periodo#,$horas_p,$horas_!,$horas_s,#$au%#,&'((") Posteriormente comen8amos a convertir nuestra consulta a un&rocedimiento almacenado. Comen8amos a formar el es#ueleto del &rocedimiento con un CRE!Eel ORREPL!CEes o&cional &ara verificar #ue no e6iste otro&rocedimiento con el mismo nombre.*+,-., /+ +,0(-*, 1'&*.2/& Posteriormente colocamos el nombre #ue deseemos. El siguiente&asoescolocarlos&ar9metros#uedebederecibirdeacuerdo a la consulta con su res&ectivo ti&o de dato.CRE!E OR REPL!CE ABNC=ON insertar-DvNrfc c7aracter:vN&eriodo QN&eriodoQ:vN7orasN& integer:vN7orasNg integer:vN7orasNs integer:vNau6 c7aracterF15 El Return se coloca &ara los datos #ue va a devolver o bien &uede serun ti&o de dato vac$o DHoidF. En este caso no va a devolver nada esta consulta : &ero #ueremos #uenosmandeunmensajedete6to&araverificar #uesereali8*onocorrectamente REBRNS SEOA te6t !SR3O47ere noNdeNcontrol M TRnoNdeNcontrolTS Comen8amos a formar el es#ueleto del &rocedimiento con un CRE!Eel ORREPL!CEes o&cional &ara verificar #ue no e6iste otro&rocedimiento con el mismo nombre.*+,-., /+ +,0(-*, 1'&*.2/& Enseguida le damos el nombre #ue deseemos 5 adelante del nombrecolocamoslos&ar9metrosoel &ar9metrodeentrada#uedebederecibir de acuerdo a la consulta #ue tengamos con su res&ectivo ti&o dedato.&roc-D=N vNnoNdeNcontrol c7aracterF El Return se coloca &ara los datos #ue va a devolver o bien &uede serde ti&o vac$o DHoidF. En este caso nos va a devolver un ti&o de datos del dominioQNnoNdeNcontrolQ.REBRNS SEOA QNnoNdeNcontrolQ !S Posteriormente dentro del 3E2=N 5 EN4 deber9 ir la consulta #ue ten$amos desde un inicio.select !.noNdeNcontrol from alumnos ! >7ere !.noNdeNcontrol M vNnoNdeNcontrolS Por ultimo alteramos la funci*n #ue se esta reali8ando colocandodes&u;s del nombre los ti&os de dato de nuestros &ar9metros.L!N2B!2E &l&gs#l HOL!=LE18COS -..ROGS -...S!LER ABNC=ON &roc-Dc7aracterFOGNER O &ostgresSBP4!E19enemos la siguiente consulta BP4!E #ue ser9 migrada &osteriormente a un&rocedimiento almacenado.,,u&date &la8asN&ersonal set unidadMTRunidadT:subunidadMTRsubunidadT:categoriaMTRcategoriasT:7orasMTR7orasT:diagonalMTRdiagonalT:ti&oNmovimientoMRti&oNmovimiento >7ere rfcMTRrfcTS Bbicamos La tabla P&la8asN&ersonalU 5 vemos los datos #ue vamos amodificar as$ como su ti&o de dato. Lo &rimero #ue 7acemos es crear la funci*n 5 &onerle el nombre #uedeseemos en este caso PProc-(U 5 delante de el colocamos los&ar9metrosdeentrada5 a estos datos les ante&onemosuna v5 unguion bajo vN 5 des&u;s del dato &onemos su res&ectivo ti&o de dato. Como se muestra en el c*digo.create or re&lace ABNC=ON Proc-( DvNunidad c7aracterD(F:vNsubunidad c7aracterD(F: vNcategoria c7aracterDEF:vN7oras c7aracterD(F:vNdiagonal c7aracterDEF:vNti&oNmovimiento integer:vNrfc c7aracterD-)FFREBRNS HO=4 L!N2B!2E &l&gs#l !S RR 4es&u;s de esto vamos a abrir un 3egin 5 un end #ue es donde va estar todo lo #ue vamos a necesitar &ara nuestro &rocedimiento Luego del begin &ondremos la consulta #ue #ueremos BP4!E seguido la tabla #ue vamos a modificar. 4es&u;s del set 5 vamos 7acer las indicaciones #ue dec$a nuestro &rocedimiento 20 Pero 5a &onemos a7ora los datos con vN 5 no con el signo de&esos 5 7acemos la igualaci*n.3E2=N ,, begin transactionu&date &la8asN&ersonal set unidadMvNunidad:subunidadMvNsubunidad:categoriaMvNcategoria:7orasMvN7oras:diagonalMvNdiagonal:ti&oNmovimientoM vNti&oNmovimiento >7ere rfcMvNrfcS ,, commitreturnSPor ultimo alteramos la funci*n #ue estamos reali8ando: colocando des&u;s del nombre los ti&os de dato de los &ar9metros.!LER ABNC=ON u&d-Dc7aracter: c7aracter: c7aracter: c7aracter: c7aracter:integer: c7aracterFOGNER O &ostgresS 4ELEEenemos la siguiente consulta 4ELEE #ue ser9 migrada &osteriormente a un21&rocedimiento almacenado.,, delete from &la8as >7ere unidad MTRunidadT and subunidad MTRsubunidadT and categoria MTRcategoriaT and diagonal MTRdiagonalTSS Bbicamos La tabla P&la8asU 5 vemos los datos #ue vamos a modificaras$ como su ti&o de dato. Lo &rimero #ue 7acemos es crear la funci*n 5 &onerle el nombre #uedeseemos en este caso Pdel-U 5 delante de el colocamos los&ar9metrosdeentrada5 a estos datos les ante&onemosuna v5 unguion bajo vN 5 des&u;s del dato &onemos su res&ectivo ti&o de dato. Como se muestra en el c*digo.CRE!E OR REPL!CE ABNC=ON del- DvNunidad c7aracter:vNsubunidad c7aracter: vNcategoria c7aracter:vNdiagonal c7aracter:FREBRNS HO=4 L!N2B!2E &l&gs#l !S RR 4es&u;s de esto vamos a abrir un 3egin 5 un end #ue es donde va estar todo lo #ue vamos a necesitar &ara nuestro &rocedimiento Luego del begin &ondremos la consulta #ue #ueremos 4ELEE seguido la tabla #ue vamos a modificar. 4es&u;s del set 5 vamos 7acer las indicaciones #ue dec$a nuestro &rocedimiento Pero 5a &onemos a7ora los datos con vN 5 no con el signo de&esos 5 7acemos la igualaci*n.223E2=N ,, begin transactiondelete from &la8as >7ere unidad MvNunidadand subunidad MvNsubunidadand categoria MvNcategoria and diagonal MvNdiagonalS ,, commitreturnSPor ultimo alteramos la funci*n #ue estamos reali8ando: colocando des&u;s del nombre los ti&os de dato de los &ar9metros.!LER ABNC=ON u&d-Dc7aracter: c7aracter: c7aracter: c7aracterF OGNER O &ostgresS . PLANTEAIENTO DE LA SOL,CI-N Para dar soluci*n a la &roblem9tica #ue se &resenta: se buscaron7erramienta#uenos&ermitieranreali8ar lamigraci*ndeunaconsultaembebida a un &rocedimiento almacenado : debido a #ue no encontramosninguna #ue nos &udiese a5udar: reali8amos la migraci*n a mano:23describiendo los &asos #ue se llevan a cabo..CONCL,SI-N& Ainalmente concluimos #ue es mejor utili8ar &rocedimientos almacenados244ebido a #ue nos ofrece ma5ores ventajas #ue utili8ar consultasembebidasdirectamente5el tiem&odeejecuci*n#ueestosllevanesmenor: el cual es el objetivo de esta investigaci*n: aun#ue se reali8aron&rocedimientos no mu5 e6tensos &ero nos &ercatamos #ue en el sistemasetieneunasecuenciadeactuali8acioneso&rocesosenlabasededatos basada en un mismo conjunto de &ar9metros.Si utili89ramosconsultasembebidasestos&rocesos&robablementelotendr$amos #ue 7acer en varias consultas: lo cual lleva m9s tiem&o: 5 enve8dereali8ar tantasllamadasal servidor: creamosun&rocedimientoalmacenadoenel #ue&asemostodoslos&ar9metros anidados5lollamamos desde nuestra a&licaci*n. 4e esta manera reducimos el tr9ficoen la red.Otros beneficio #ue un &rocedimiento almacenado nos ofrece: es #ue alosusuarios#uenotengan&ermisos&araacceder directamentealastablas&uedenobtenersucontenidooactuali8arlas. 4etal maneranosofrecen un mejor control en cuanto a la seguridad.Por lo general: es mejor utili8ar &rocedimientos almacenados en todos loscasos.25