4941: Database cursor support - Composr CMS feature tracker

ID	Project	Category	View Status	Date Submitted	Last Update

4941	Composr	core_database_drivers	public	2022-09-17 00:05	2022-09-27 17:22

Reporter	Chris Graham	Assigned To	Guest
Priority	normal	Severity	feature
Status	new	Resolution	open

Summary	4941: Database cursor support
Description	We do not currently have any support for database cursors. A query will return as many results as we ask for in one go, and then we're done with it. Consider this query we were considering doing... SELECT recipient_id,SUM(amount_points)+SUM(gift_points)-(SELECT SUM(amount_points)+SUM(gift_points) FROM cms11_points_ledger refund WHERE refund.status='refund' AND refund.date_and_time>=START AND refund.date_and_time<END AND refund.recipient_id=normal.recipient_id) AS points FROM cms11_points_ledger normal WHERE normal.status='normal' AND normal.date_and_time>=START AND normal.date_and_time<END GROUP BY recipient_id ORDER BY points DESC LIMIT 10; This is finding the top members by points within a date range. However, if we were to filter some of the results out in PHP, we'd want to not have a LIMIT clause, and we'd not want to paginate the results (very slow when aggregate functions are involved) - we'd want to just keep cursoring through. I'm somewhat ambivalent on this change. It's taken until now to find a use case, and the solution to our use case is just to turn off the PHP memory limit as we are running in a Cron hook anyway (and if we're concerned about memory, the cursoring query would still use lots too, just in MySQL rather than in PHP).
Tags	No tags attached.
Attach Tags	(Separate by ",")

Time estimation (hours)	24
Sponsorship open

Date Added	Member	Amount Sponsored

View Status	Private (for security issues or disclosing of private information; if you submit as a guest, you will not be able to see your submission)
Note
Upload Files Maximum size: 32,768 KiB	Attach files by dragging & dropping, selecting or pasting them.
You are not logged in	You are not logged in. This means you will not get any e-mail notifications. And if you reply, we will not know for sure you are the original poster of the issue.

Date Modified	Username	Field	Change
2022-09-17 00:05	Chris Graham	New Issue
2022-09-17 00:05	Chris Graham	Tag Attached: 24
2022-09-27 17:22	Chris Graham	Time estimation (hours)	=> 24
2022-09-27 17:22	Chris Graham	Tag Detached: 24