correct DELETE syntax?

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

correct DELETE syntax?

Post by combuster » Fri Dec 26, 2014 2:40 am

Hi!

I use this to delete a row:

Code: Select all

$sql = 'DELETE t
 		FROM ' . $this->table_prefix . TABLES::TAGS . ' t
 		WHERE NOT EXISTS (
 			SELECT 1
 			FROM ' . $this->table_prefix . TABLES::TOPICTAGS . ' tt
 				WHERE tt.tag_id = t.id
 		)';

$this->db->sql_query($sql);
while mysql works fine, I get an error in postgres:

Code: Select all

SQL ERROR [ postgres ]
 
ERROR: syntax error at or near "t" LINE 1: DELETE t ^ []
 
SQL
 
DELETE t FROM phpbb_rh_topictags_tag t WHERE NOT EXISTS ( SELECT 1 FROM phpbb_rh_topictags tt WHERE tt.tag_id = t.id )
Postgres does not support the alias "t" directly after the "DELETE". However removing it works in postgres, but I get the mysql error:

Code: Select all

 SQL ERROR [ mysqli ]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't WHERE NOT EXISTS ( SELECT 1 FROM phpbb_rh_topictags tt WHERE t' at line 2 [1064]

SQL

DELETE FROM phpbb_rh_topictags_tag t WHERE NOT EXISTS ( SELECT 1 FROM phpbb_rh_topictags tt WHERE tt.tag_id = t.id )
What is a proper sql syntax to use within phpbb? using the database abstraction layer I wonder why I would ever run in such an issue, because the abstraction layer should map the query to the different DB-systems?!

postgres doc: http://www.postgresql.org/docs/9.0/stat ... elete.html
mysql doc: http://dev.mysql.com/doc/refman/5.7/en/delete.html

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster » Sat Jan 03, 2015 7:29 am

bump

User avatar
M.Gaetan89
Registered User
Posts: 39
Joined: Sun Apr 21, 2013 12:36 pm
Location: Divonne-les-Bains
Name: Gaëtan
Contact:

Re: correct DELETE syntax?

Post by M.Gaetan89 » Sat Jan 03, 2015 10:51 am

Use DELETE FROM instead of DELETE t FROM.

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster » Sat Jan 03, 2015 11:29 am

Please read the full question, because MySQL does not support that syntax in multidelete statements! In MySQL you need to specify from which table you want to delete if there are several mentiones in the from clause.

User avatar
M.Gaetan89
Registered User
Posts: 39
Joined: Sun Apr 21, 2013 12:36 pm
Location: Divonne-les-Bains
Name: Gaëtan
Contact:

Re: correct DELETE syntax?

Post by M.Gaetan89 » Sat Jan 03, 2015 11:59 am

Sorry, I missed the middle-part ...

Have you tried using USING? I am not sure about the syntax, as I never used it before.

Code: Select all

DELETE
FROM  ' . $this->table_prefix . TABLES::TAGS . ' t, ' . $this->table_prefix . TABLES::TOPICTAGS . ' tt
USING tt
...

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster » Sat Jan 03, 2015 4:01 pm

Hey yes using 'using' would be OK for MySQL and postgres, but phpbb does support more DBMS and I am no sure all oft them support it. I wonder why such an issue can arise? Its called a DB-abstraction layer and we should not have to deal with dbms-specific problems. I wonder how I as an ext.developer should use the DBAL for multi delete so that it runs in all suported dbms.

Actually i think its not even possible to use subqueries (even though in MySQL its fine). So I will probably split it up in 2 queries, one selecting the ids and one to delete the rows. Then it is single delete statement which seems to be fine across the different dbms.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4882
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: correct DELETE syntax?

Post by VSE » Sat Jan 03, 2015 7:20 pm

You should not use queries like in your OP. You have to remember that phpBB supports a lot of dbms, so use the dbal functions instead of writing out a query like you did, because it limits the compatability with various supported dbms, as you have realized.

Just run a query to get the applicable results first, then run a second query on those results.
Dictated but not read.
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster » Sat Jan 03, 2015 7:38 pm

@VSE: As far as I know, there are NO dbal-methods that explicitly deal with DELETE-Statements. Actually the documentation puts in mind that its ok to use any sql-statement for DELETE: https://wiki.phpbb.com/Queries_in_phpBB3#Delete

However, documentation says:
phpBB supports many different databases, in order to do this, you must use SQL statements that work with each one and that the $db object in the Database Abstraction Layer of phpBB can understand.
I think this is not enough for developers, because keeping up with 7 DBMS should not be left to each ext-developer, but solved by the framework (which is phpbb). DBAL is an ABSTRACTION layer and the phpbb's abstraction does not abstract from the concrete DBMS if people need to check if a statement syntax is supported by all (required) DBMS. Maybe phpbb can learn more from Symfony. =)

Anyway, thanks for commenting. I use - as I wrote - 2 queries now.

nicofuma
3.2 Release Manager
3.2 Release Manager
Posts: 546
Joined: Sun Apr 13, 2014 1:47 am
Location: Grenoble - France

Re: correct DELETE syntax?

Post by nicofuma » Sun Jan 04, 2015 12:21 pm

Actually Symfony doesn't do anything with the SQL, Doctrine does.

Anyway, phpBB do have an abstraction layer: if you use the right methods the queries are rewrote. It's just that our system is pretty old and doesn't support sub requests. Instead you should run the sub request manually and then use the result in your DELETE query.

To be precise, you can see the phpBB DBAL as a simple layer with a limited language (which is a part of the standard SQL language) but it let you the possibility to run a more complex query if you know what is the concrete DBMS and if you know what you are doing.

EDIT: But the main point of a DBAL is not to abstract the SQL language but to abstract the api of the DBMS driver (mysql_connect/mysqli_connect/pg_connect/etc...)
Member of phpBB Development-Team
No Support via PM

Post Reply

Return to “Extension Writers Discussion”