correct DELETE syntax?

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

correct DELETE syntax?

Post by combuster »

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: 351
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster »

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 »

Use DELETE FROM instead of DELETE t FROM.
combuster
Registered User
Posts: 351
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster »

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 »

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: 351
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster »

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
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5164
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: correct DELETE syntax?

Post by MattF »

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.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
combuster
Registered User
Posts: 351
Joined: Sun Oct 26, 2008 11:58 pm

Re: correct DELETE syntax?

Post by combuster »

@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 »

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”