jyoungblood / dbkit Goto Github PK

The current way to do raw queries feels kinda hacky. Would love to have an abstraction function for db::find() that just accepts an arbitrary query.

Current way to do this:

$members = db::find("", "
	SELECT 
		distinct m.member_number, m.last_name, m.first_name
		, coalesce(mus.username, mem.email_address, '') as email
		, ma.city
		, st.name as state
		, co.name as country
	FROM member m
	  left join user mus on m.user_id = mus.id
	  left join member_email mem on mem.member_id = m.id
	  left join member_address ma on ma.member_id = m.id and ma.active = 1 and ma.principal = 1
	  left join state st on st.id = ma.state_id
	  left join country co on co.id = ma.country_id
	where m.id = '123456789'        
	GROUP BY m.member_number
	ORDER BY country DESC, state ASC, last_name ASC
", [
  'raw' => true
]);

Desired usage:

$members = db::raw("
	SELECT 
		distinct m.member_number, m.last_name, m.first_name
		, coalesce(mus.username, mem.email_address, '') as email
		, ma.city
		, st.name as state
		, co.name as country
	FROM member m
	  left join user mus on m.user_id = mus.id
	  left join member_email mem on mem.member_id = m.id
	  left join member_address ma on ma.member_id = m.id and ma.active = 1 and ma.principal = 1
	  left join state st on st.id = ma.state_id
	  left join country co on co.id = ma.country_id
	where m.id = '123456789'        
	GROUP BY m.member_number
	ORDER BY country DESC, state ASC, last_name ASC
");

All db::find() queries are SELECT * by default. It's possible to select only specific fields with raw queries, but it might be helpful to have the ability to do this with parameter options?

I'm not sure how it would look, maybe something like this:

$planets = db::find("celestial_bodies", "classification = 'planet' ORDER BY title ASC LIMIT 8", [
  'select' => [
    'id', 'title', 'classification', [
      'distance_from_sun' => 'distance'
    ]
]);

Which would render the query:

SELECT id, title, classification, distance_from_sun as distance FROM celestial_bodies WHERE classification = 'planet' ORDER BY title ASC LIMIT 8

The old version of this package had an option for caching a given query with APC. I haven't done a lot of research on the object-level caching options available these days, but if it's easy enough I would love to provide this option again.

Desired usage:

$space_objects = db::find("celestial_bodies", "id IS NOT NULL", [
  'cached' => true
]);

Shouldn't require any 3rd-party libraries or be a database-specific solution.

I think APCu sounds like what we'd want, but I need to do some more research.

Also for inspo, I like the api/concept of the Kirby3 Extended APCu Cache-Driver.

Pagination is a PITA, how can we make it easier? Would like to either modify db::find() or have new functions you can use in conjunction with find().

I know I've written abstractions to help with this at some point in my career (maybe for OKH? OB? HI?), but it's been a while. Haven't really thought it through yet, but I know it's a problem that can be solved.

misc thoughts:

• ?? abstraction - helpers for pagination (best practices, make it super easy)
• https://developer.wordpress.com/2014/02/14/an-efficient-alternative-to-paging-with-sql-offsets/
• send params to manage pagination offsets
• append to the end of queries (/insert in the appropriate spots)

Postgres, SQLite, et al should be supported with PDO, but I haven't tested with anything other than mysql yet.

Want to make sure there are no problems with connections, query writing, error display, etc.

• docs verbiage - “Dbkit = dbal (db abstraction layer) (convenient abstractions for safely performing common operations with db)”
  • specify that it’s not an orm, just handlers for more easily performing common CRUD operations on PDO-compatible databases
• docs - readme update w/ purpose/impetus - encourage best practices for data interaction (we make the decisions for, making the right choices easy) (use pdo, sanitize input, etc)
• docs - add notes for how to do raw queries (you can just use normal pdo functions)
  $GLOBALS['database']->query()
  $GLOBALS['database']->exec()
  $GLOBALS['database']->execute()
• docs - examples of how to support other dbs? (esp sqlite?) examples of 3rd-party dbs on a separate server (ex supabase, directus)

Current way to do joins is to just write a raw query, but I'm sure we could do something to make the process easier.

Maybe adding parameter options to db::find()?

Maybe a new function that's specifically designed to do joins?

Maybe something else?

I'm intrigued by how NotORM handles repetitive queries, which is similar to a patten I use in many of my applications to preclude the usage of joins altogether ... should something like this be a "best practice" that we encourage instead?

It's not a big deal to define fields like date_created and date_updated, especially if using time() for the current unix timestamp, but it gets more involved if using other formats like YYYY-MM-DDTHH:mm:ss.sssZ

Maybe we could have a global config option to define the names of fields to be automatically populated w/ the current timestamp and the format. For example:

$GLOBALS['settings']['database']['auto_write_timestamps'] = [
  [
     'name' => 'date_created',
     'format' => 'YYYY-MM-DDTHH:mm:ss.sssZ'
  ],
  [
     'name' => 'date_updated',
     'format' => 'YYYY-MM-DDTHH:mm:ss.sssZ'
  ],
];

I've always loved how libraries like Mongoose let you compose queries with chained methods or key/value parameters. Closer to our use case (PDO w/ PHP), NotORM has a similar API that I love...it's very easy to understand.

My concern is that this concept (specifically the method chaining) feels like a divergence from the spirit of our library, and I'm wary of doing anything that unnecessarily complicates either the library itself or the way people have to use it.

However, it would be beneficial to have the ability to compose queries programmatically. I think key/val parameters would provide both flexibility and reliability, using common data structures to build potentially complex queries.

There are a lot of ways this could be done, but I'm imagining something like this:

db::query([
  'select' => ['name', 'occupation', 'age'],
  'from' => 'people',
  'where' => [
    ['occupation', 'like', 'host'],
    ['age', '>', 17],
    ['age', '<', 66],
    ['last_name', '=', 'Ghost'],
    ['likes', 'in', ['vaporizing', 'talking']]
  ],
  'order_by' => ['occupation', 'DESC']
  'limit' => 10
]);

We're currently just echoing some errors, and some aren't being handled at all. Without going overboard, I would like to have more robust handling of error messages.

Errors should display on screen and shouldn't stop rendering. Would be cool to show errors in a separated "view" prepended to the page content (much like the console_log method in the x-utilities package).

Writing to a log might also be a good option if the implementation isn't too complex.

REF - check out how NotORM uses standard PDO error reporting.

Maybe want to give it a different name (or use snake case?), but it's similar to the Mongoose findOne method: finds one record that matches the criteria and returns an array with just the results of that record.

Current method of doing this:

$_user = db::find("users", "_id='123456789'");
$user = $_user['data'][0];
echo $user['email'];

Desired usage:

$user = db::findOne("users", "_id='123456789'");
echo $user['email'];

• behaves like update, but
• inserts record into table if there's no "where" param
  • and returns the id of the record just created

I'm wary of overcomplicating, but I've been in enough situations where this would be convenient...is it worth it?

I feel pretty confident about how this is all put together, but I want to do a deeper dive and make sure we’re doing everything “the right way” … mostly concerned about security and efficiency.

• connecting, querying, returning

  • https://phptherightway.com/#databases
  • https://www.php.net/manual/en/pdo.connections.php

• data filtering - https://phptherightway.com/#data_filtering

• better sql injection protection?

  • we’re using prepared statements, is there more we can do?
  • https://phpdelusions.net/pdo/sql_injection_example
  • https://stackoverflow.com/questions/10129514/should-i-sanitize-filter-user-input-and-output-when-using-php-pdo
  • https://stackoverflow.com/questions/1299182/prepared-parameterized-query-with-pdo (proper way to show errors?)
  • https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection

• audit the construction and make sure we’re doing this the best way possible

  • https://phpdelusions.net/pdo

• ?? are there cues we can take from NotORM regarding architecture, security, performance, error reporting, etc