Re: [Wikitech-l] SQL: Stored Procedures and MediaWiki
Hi Robert As far as I understand, MediaWiki does not use stored procedures, for compatibility reasons. MediaWiki only requires mysql 4.0, which iirc does not support stored procedures at all. Also, stored procedures can not (easily) be ported to other database systems like sqlight. If you want to use stored procedures in your extension, I don't see anything mediawiki specific to observe. It should works simply as you would expect it from mysql. The only question is how the procedures can be created cleanly when the extension is being installed, or changed when the extension is updated. Perhaps someone here knows about this? A quick search through the extensions on mediawiki.org indicates that Extension:Geoserver uses stored procedures, but with postgres, not mysql. hth daniel On 05.08.2011 11:37, Robert Vogel wrote: Hi Brion, thanks for the hint. Is there a tutorial or documentation on how to use stored procedures in MediaWiki? Do you - or anybody else - know an extension that makes use of this feature? Something we can take a look at? Greetings, Robert Vogel Hallo Welt! - Medienwerkstatt GmbH ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Re: [Wikitech-l] SQL: Stored Procedures and MediaWiki
Hi Brion, thanks for the hint. Is there a tutorial or documentation on how to use stored procedures in MediaWiki? Do you - or anybody else - know an extension that makes use of this feature? Something we can take a look at? Greetings, Robert Vogel Hallo Welt! - Medienwerkstatt GmbH ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Re: [Wikitech-l] SQL: Stored Procedures and MediaWiki
Hi Roan, thanks for the quick reply. This is (one of) the statement(s) we've got a problem with. -- DROP PROCEDURE IF EXISTS `insertfile_getFilePosition`; DELIMITER $$ CREATE PROCEDURE `insertfile_getFilePosition`(filename VARCHAR(255)) BEGIN SELECT tmp.rank FROM (SELECT @row:=@row+1 rank, i.img_name FROM /*$wgDBprefix*/image i, (SELECT @row:=0) r WHERE (i.img_major_mime != 'image' AND i.img_minor_mime != 'tiff') ORDER BY i.img_name ASC) tmp WHERE tmp.img_name = filename; END $$ DELIMITER ; -- If provided to the SQL Database directly, it works. But the update.php throws a syntax error. In general, is it possible to provide stored procedures this way? Could there be a problem with the way the sql-file is read? Greetings, Robert Vogel Social Web Technologien Softwareentwicklung Hallo Welt! - Medienwerkstatt GmbH __ Untere Bachgasse 15 93047 Regensburg Tel. +49 (0) 941 - 56 95 94 98 Fax +49 (0) 941 - 50 27 58 13 www.hallowelt.biz vo...@hallowelt.biz Sitz: Regensburg Amtsgericht: Regensburg Handelsregister: HRB 10467 E.USt.Nr.: DE 253050833 Geschäftsführer: Anja Ebersbach, Markus Glaser, Dr. Richard Heigl, Radovan Kubani On Wed, 27 Jul 2011 at 22:16 PM, Roan Kattouw roan.katt...@gmail.com wrote: On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel vo...@hallowelt.biz wrote: Hello everybody! At my company we develop extensions for MediaWiki. We use the LoadExtensionSchemaUpdates hook to create tables with the maintenance/update.php script. Recently we faced the question whether it is possible to have stored procedures/functions in an extensions SQL-File, or not. We tried it out and it didn't work for us. The update.php says we've got an error in the SQL syntax, but there isn't one. Can anybody help us? Is it possible to provide stored procedures to the database using the update.php? Is there an example anywhere? Thx. The SQL syntax error message comes from the database engine, not from MediaWiki. So if it tells you there's an SQL syntax error, there's a syntax error for sure. What you should look at: 1. does the DB backend you connect to support the syntax you're using? Infamously, MySQL 4.0 will reject anything containing subqueries as a syntax error, because subquery support wasn't introduced until 4.1 if memory serves 2. is MediaWiki connecting to the DB that you think it's connecting to? There might be a version-triggered error like #1 above, but you might not notice if you're connecting to a different version than MediaWiki is 3. are you using magic phrases like /*_*/, /*$wgDBTablePrefix*/, /*i*/ or /*$wgDBTableOptions*/ ? MediaWiki substitutes these before sending the SQL to the DB backend, so make sure you test your queries with these substitutions applied Roan Kattouw (Catrope) ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Re: [Wikitech-l] SQL: Stored Procedures and MediaWiki
I suspect the delimiter switch is not being handled by the code that splits the file into statements, so the procedure definition gets split at the first semicolon. -- brion On Jul 28, 2011 3:55 AM, Robert Vogel vo...@hallowelt.biz wrote: Hi Roan, thanks for the quick reply. This is (one of) the statement(s) we've got a problem with. -- DROP PROCEDURE IF EXISTS `insertfile_getFilePosition`; DELIMITER $$ CREATE PROCEDURE `insertfile_getFilePosition`(filename VARCHAR(255)) BEGIN SELECT tmp.rank FROM (SELECT @row:=@row+1 rank, i.img_name FROM /*$wgDBprefix*/image i, (SELECT @row:=0) r WHERE (i.img_major_mime != 'image' AND i.img_minor_mime != 'tiff') ORDER BY i.img_name ASC) tmp WHERE tmp.img_name = filename; END $$ DELIMITER ; -- If provided to the SQL Database directly, it works. But the update.php throws a syntax error. In general, is it possible to provide stored procedures this way? Could there be a problem with the way the sql-file is read? Greetings, Robert Vogel Social Web Technologien Softwareentwicklung Hallo Welt! - Medienwerkstatt GmbH __ Untere Bachgasse 15 93047 Regensburg Tel. +49 (0) 941 - 56 95 94 98 Fax +49 (0) 941 - 50 27 58 13 www.hallowelt.biz vo...@hallowelt.biz Sitz: Regensburg Amtsgericht: Regensburg Handelsregister: HRB 10467 E.USt.Nr.: DE 253050833 Geschäftsführer: Anja Ebersbach, Markus Glaser, Dr. Richard Heigl, Radovan Kubani On Wed, 27 Jul 2011 at 22:16 PM, Roan Kattouw roan.katt...@gmail.com wrote: On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel vo...@hallowelt.biz wrote: Hello everybody! At my company we develop extensions for MediaWiki. We use the LoadExtensionSchemaUpdates hook to create tables with the maintenance/update.php script. Recently we faced the question whether it is possible to have stored procedures/functions in an extensions SQL-File, or not. We tried it out and it didn't work for us. The update.php says we've got an error in the SQL syntax, but there isn't one. Can anybody help us? Is it possible to provide stored procedures to the database using the update.php? Is there an example anywhere? Thx. The SQL syntax error message comes from the database engine, not from MediaWiki. So if it tells you there's an SQL syntax error, there's a syntax error for sure. What you should look at: 1. does the DB backend you connect to support the syntax you're using? Infamously, MySQL 4.0 will reject anything containing subqueries as a syntax error, because subquery support wasn't introduced until 4.1 if memory serves 2. is MediaWiki connecting to the DB that you think it's connecting to? There might be a version-triggered error like #1 above, but you might not notice if you're connecting to a different version than MediaWiki is 3. are you using magic phrases like /*_*/, /*$wgDBTablePrefix*/, /*i*/ or /*$wgDBTableOptions*/ ? MediaWiki substitutes these before sending the SQL to the DB backend, so make sure you test your queries with these substitutions applied Roan Kattouw (Catrope) ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
[Wikitech-l] SQL: Stored Procedures and MediaWiki update.php
Hello everybody! At my company we develop extensions for MediaWiki. We use the LoadExtensionSchemaUpdates hook to create tables with the maintenance/update.php script. Recently we faced the question whether it is possible to have stored procedures/functions in an extensions SQL-File, or not. We tried it out and it didn't work for us. The update.php says we've got an error in the SQL syntax, but there isn't one. Can anybody help us? Is it possible to provide stored procedures to the database using the update.php? Is there an example anywhere? Thx. Greetings, Robert Vogel Social Web Technologien Softwareentwicklung Hallo Welt! - Medienwerkstatt GmbH ___ Untere Bachgasse 15 93047 Regensburg Tel. +49 (0) 941 - 56 95 94 98 Fax +49 (0) 941 - 50 27 58 13 www.hallowelt.biz vo...@hallowelt.biz Sitz: Regensburg Amtsgericht: Regensburg Handelsregister: HRB 10467 E.USt.Nr.: DE 253050833 Geschäftsführer: Anja Ebersbach, Markus Glaser, Dr. Richard Heigl, Radovan Kubani ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Re: [Wikitech-l] SQL: Stored Procedures and MediaWiki update.php
On Wed, Jul 27, 2011 at 12:47 PM, Robert Vogel vo...@hallowelt.biz wrote: Hello everybody! At my company we develop extensions for MediaWiki. We use the LoadExtensionSchemaUpdates hook to create tables with the maintenance/update.php script. Recently we faced the question whether it is possible to have stored procedures/functions in an extensions SQL-File, or not. We tried it out and it didn't work for us. The update.php says we've got an error in the SQL syntax, but there isn't one. Can anybody help us? Is it possible to provide stored procedures to the database using the update.php? Is there an example anywhere? Thx. The SQL syntax error message comes from the database engine, not from MediaWiki. So if it tells you there's an SQL syntax error, there's a syntax error for sure. What you should look at: 1. does the DB backend you connect to support the syntax you're using? Infamously, MySQL 4.0 will reject anything containing subqueries as a syntax error, because subquery support wasn't introduced until 4.1 if memory serves 2. is MediaWiki connecting to the DB that you think it's connecting to? There might be a version-triggered error like #1 above, but you might not notice if you're connecting to a different version than MediaWiki is 3. are you using magic phrases like /*_*/, /*$wgDBTablePrefix*/, /*i*/ or /*$wgDBTableOptions*/ ? MediaWiki substitutes these before sending the SQL to the DB backend, so make sure you test your queries with these substitutions applied Roan Kattouw (Catrope) ___ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
