Open bart-jaskulski opened 1 year ago
Notes: UPSERT a/k/a ON DUPLICATE KEY UPDATE
This has different syntax on SQLite, and doesn't exist at all prior to version 3.24.
SELECT DISTINCT meta_key FROM wp_postmeta WHERE metakey NOT BETWEEN '' AND '_z' HAVING metakey NOT LIKE '%' ORDER BY meta_key LIMIT 30
SQLlite's complaint for this is " a GROUP BY clause is required before HAVING"
It's `NOT LIKE '_%' . That is, it's NOT LIKE any string that begins with an underscore.
It's `NOT LIKE '_%' . That is, it's NOT LIKE any string that begins with an underscore.
We've discussed this on WP.org slack, here's the summary:
$this->last_reserved_keyword
could be useful to process expressions more complex than just a string literal.I believe this has now been fixed? 🤔
woocommerce payment not work
Are there any plans to support versions prior to sqlite3.24? In version 3.16, there seem to be many problems with the operations of the wp_options table.
INSERT INTO wp_options
(option_name
, option_value
, autoload
) VALUES ('widget_links', 'a:1:{s:12:\"_multiwidget\";i:1;}', 'yes') ON DUPLICATE KEY UPDATE option_name
= VALUES(option_name
), option_value
= VALUES(option_value
), autoload
= VALUES(autoload
)
INSERT INTO wp_options
(option_name
, option_value
, autoload
) VALUES (:param0 , :param1 , :param2 ) ON CONFLICT ("option_name") DO UPDATE SET option_name
= excluded.option_name
, option_value
= excluded.option_value
, autoload
= excluded.autoload
| parameters: widget_links, a:1:{s:12:"_multiwidget";i:1;}, yes
SQLSTATE[HY000]: General error: 1 near "ON": syntax error.
@ianzhi tell me more about your use-case – is there anything blocking you from upgrading to SQLite 3.24? Also, what would that query would look like on SQLite 3.16?
@ianzhi tell me more about your use-case – is there anything blocking you from upgrading to SQLite 3.24? Also, what would that query would look like on SQLite 3.16?
https://www.sqlite.org/lang_upsert.html I don't know what should be provided, I checked the documentation of SQLite and found that the INSERT INTO ... ON CONFLICT ... syntax seems to be part of UPSERT and is only provided after SQLite version 3.24.0, so if using SQLite version earlier than 3.24.0, should all encounter this problem.
Thank you for this additional context @ianzhi! This could be refactored into an UPDATE query and an INSERT query on earlier SQLite versions. However, before this plugin may work with multiple SQLite versions, it needs to work reliably with a single version – and there are still missing parts that will take time to implement. Is there anything stopping you from upgrading to SQLite 3.24?
Thank you for this additional context @ianzhi! This could be refactored into an UPDATE query and an INSERT query on earlier SQLite versions. However, before this plugin may work with multiple SQLite versions, it needs to work reliably with a single version – and there are still missing parts that will take time to implement. Is there anything stopping you from upgrading to SQLite 3.24?
Thank you very much for your reply. I'm running WordPress using an environment like Amazon Lambda. If I want to update the version of SQLite, I may have to use a self-built image. I suspect this will affect the cold start time. Of course, it is not a big problem. I just want to know if there is Plans to support older versions of SQLite. I used Google Translate for my reply. I'm not sure it expresses my meaning correctly. Please don't mind if it offends you.
Of course, it is not a big problem. I just want to know if there is Plans to support older versions of SQLite.
I'm not aware of such plans at the moment, perhaps @aristath would have more context to offer.
Upgrading the version of SQLite used in the php extension is difficult or impossible for the customers of must hosting providers. The most likely way to make it happen is to push forward to a more recent php version.
A BEGIN / UPDATE / if ( 0 === $sqlite->changes() ) { INSERT } / COMMIT sequence is surprisingly efficient for the older versions, for what it's worth.
I've been testing new implementation with WooCommerce, as it's both useful to me and quite elaborate when it comes to database operations.
So far, so good!
I've stumbled upon some minor errors, which seeming doesn't affect overall user experience, but this may lead to further problems.
At first, after WooCommerce activation I've been welcomed (already with SQLite database installed) by white screen with error. I truncated the output a bit, as it's illegible in its full form, but attached it also as gist)
Another error found at order edit page (after successful submission):