← All changes | includes/Core/Database.php +363 -0 0.2.5.7 → trunk View file →

		@@ -1,0 +1,363 @@
	1	+<?php
	2	+
	3	+/**
	4	+ * Extension Factory
	5	+ *
	6	+ * @package NotificationX\Extensions
	7	+ */
	8	+
	9	+namespace NotificationX\Core;
	10	+
	11	+use NotificationX\GetInstance;
	12	+use WPDeveloper\QueryBuilder\Query as QueryBuilder;
	13	+
	14	+/**
	15	+ * Database Class
	16	+ * @method static Database get_instance($args = null)
	17	+ */
	18	+class Database {
	19	+ /**
	20	+ * Instance of Database
	21	+ *
	22	+ * @var Database
	23	+ */
	24	+ use GetInstance;
	25	+
	26	+ /**
	27	+ * WordPress database abstraction object.
	28	+ *
	29	+ * @var \wpdb
	30	+ */
	31	+ protected $wpdb;
	32	+ public static $version = '2.1';
	33	+ public static $table_entries;
	34	+ public static $table_posts;
	35	+ public static $table_stats;
	36	+ protected static $query;
	37	+
	38	+ /**
	39	+ * Initially Invoked when initialized.
	40	+ */
	41	+ public function __construct() {
	42	+ global $wpdb;
	43	+ $this->wpdb = $wpdb;
	44	+ self::$table_entries = $wpdb->prefix . 'nx_entries';
	45	+ self::$table_posts = $wpdb->prefix . 'nx_posts';
	46	+ self::$table_stats = $wpdb->prefix . 'nx_stats';
	47	+ }
	48	+
	49	+ public static function query() {
	50	+ if (!isset(self::$query)) {
	51	+ self::$query = QueryBuilder::init();
	52	+ }
	53	+ return self::$query;
	54	+ }
	55	+
	56	+ public function Create_DB() {
	57	+ $charset_collate = $this->wpdb->get_charset_collate();
	58	+ $table_posts = self::$table_posts;
	59	+ $table_entries = self::$table_entries;
	60	+ $table_stats = self::$table_stats;
	61	+
	62	+ $sql = "CREATE TABLE {$table_entries} (
	63	+ entry_id bigint(20) unsigned NOT NULL auto_increment,
	64	+ nx_id bigint(20) unsigned NULL,
	65	+ source varchar(55) default NULL,
	66	+ entry_key varchar(255) default NULL,
	67	+ data longtext,
	68	+ created_at TIMESTAMP NOT NULL,
	69	+ updated_at TIMESTAMP NOT NULL,
	70	+ PRIMARY KEY (entry_id),
	71	+ KEY source (source),
	72	+ KEY nx_id (nx_id)
	73	+ ) $charset_collate ;";
	74	+
	75	+ require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	76	+ $entries_db = dbDelta( $sql );
	77	+
	78	+ $sql = "CREATE TABLE {$table_posts} (
	79	+ nx_id bigint(20) unsigned NOT NULL auto_increment,
	80	+ title text default NULL,
	81	+ type varchar(55) default NULL,
	82	+ source varchar(55) default NULL,
	83	+ theme varchar(55) default NULL,
	84	+ is_inline varchar(255) default NULL,
	85	+ global_queue BOOLEAN default false,
	86	+ enabled BOOLEAN default false,
	87	+ data longtext,
	88	+ created_at TIMESTAMP NOT NULL,
	89	+ updated_at TIMESTAMP NOT NULL,
	90	+ PRIMARY KEY (nx_id),
	91	+ KEY type (type),
	92	+ KEY source (source),
	93	+ KEY theme (theme)
	94	+ ) $charset_collate ;";
	95	+ $post_db = dbDelta( $sql );
	96	+
	97	+ $sql = "CREATE TABLE {$table_stats} (
	98	+ stat_id bigint(20) unsigned NOT NULL auto_increment,
	99	+ nx_id bigint(20) unsigned default NULL,
	100	+ views varchar(55) default 0,
	101	+ clicks varchar(55) default 0,
	102	+ created_at DATE NOT NULL,
	103	+ PRIMARY KEY (stat_id),
	104	+ KEY nx_id (nx_id)
	105	+ ) $charset_collate ;";
	106	+ $stats_db = dbDelta( $sql );
	107	+
	108	+ }
	109	+
	110	+ public function update_analytics( $col, $id, $date, $data = null ) {
	111	+ $table_name = self::$table_stats;
	112	+ $_data = is_null( $data ) ? 1 : intval( $data );
	113	+
	114	+ // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	115	+ return $this->wpdb->query( $this->wpdb->prepare( '
	116	+ UPDATE %1$s
	117	+ SET `%2$s` = `%3$s` + %4$s
	118	+ WHERE nx_id = "%5$s"
	119	+ AND created_at = "%6$s"',
	120	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	121	+ $table_name, esc_sql( $col ), esc_sql( $col ), $_data, intval( $id ), $date
	122	+ )
	123	+ );
	124	+ // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder
	125	+ }
	126	+
	127	+ public function insert_post( $table_name, $post, $format = null ) {
	128	+ $post = $this->serialize_data( $post );
	129	+ $this->wpdb->insert( $table_name, $post, $this->get_format($format, $post) );
	130	+ return $this->wpdb->insert_id;
	131	+ }
	132	+
	133	+ public function insert_posts( $table_name, $posts, $format = null ) {
	134	+ if ( ! empty( $posts[0] ) ) {
	135	+ $values = array();
	136	+ $place_holders = array();
	137	+ $_column = array_keys( $posts[0] );
	138	+ /*
	139	+ * Column names are interpolated into the statement, so prepare() below
	140	+ * cannot protect them - it only binds the values. Callers such as the
	141	+ * /import REST route take these keys straight from a user-supplied JSON
	142	+ * payload, so accept identifiers only and quote them.
	143	+ */
	144	+ $_column = array_values(
	145	+ array_filter(
	146	+ $_column,
	147	+ function ( $col ) {
	148	+ return is_string( $col ) && preg_match( '/^[A-Za-z0-9_]+$/', $col );
	149	+ }
	150	+ )
	151	+ );
	152	+ if ( empty( $_column ) ) {
	153	+ return;
	154	+ }
	155	+ $columns = '`' . implode( '`, `', $_column ) . '`';
	156	+ $query = "INSERT INTO $table_name ($columns) VALUES ";
	157	+ foreach ( $posts as $key => $entry ) {
	158	+ $entry = $this->serialize_data( $entry );
	159	+ reset( $_column );
	160	+ $_place_holders = [];
	161	+ foreach ( $_column as $col ) {
	162	+ $values[] = isset( $entry[ $col ] ) ? $entry[ $col ] : '';
	163	+ if(!empty($format) && isset($format[$col])){
	164	+ $_place_holders[] = $format[$col];
	165	+ } else {
	166	+ $_place_holders[] = '%s';
	167	+ }
	168	+ }
	169	+ // $values = array_merge($values, array_values($entry));
	170	+ $place_holders[] = '(' . implode( ', ', $_place_holders ) . ')'; /* In my case, i know they will always be integers */
	171	+ }
	172	+ $query .= implode( ', ', $place_holders );
	173	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Values are bound by prepare() below; the column list is whitelisted to /^[A-Za-z0-9_]+$/ identifiers above, which is what stops the injection this used to allow.
	174	+ $query = $this->wpdb->prepare( "$query", $values );
	175	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared -- $query is the prepare()d statement built above, with the column list whitelisted to identifiers.
	176	+ $this->wpdb->query( $query );
	177	+ }
	178	+ }
	179	+
	180	+ public function update_post( $table_name, $post, $where__or_pid, $format = null ) {
	181	+ $post = $this->serialize_data( $post );
	182	+ if ( ! is_array( $where__or_pid ) ) {
	183	+ $id = $this->get_primary_col( $table_name );
	184	+ $where__or_pid = [ $id => $where__or_pid ];
	185	+ }
	186	+ return $this->wpdb->update( $table_name, $post, $where__or_pid, $this->get_format($format, $post) );
	187	+ }
	188	+
	189	+ public function get_post( $table_name, $where__or_pid, $select = '*' ) {
	190	+ if ( ! is_array( $where__or_pid ) ) {
	191	+ $id = $this->get_primary_col( $table_name );
	192	+ $where__or_pid = [ $id => absint( $where__or_pid ) ];
	193	+ }
	194	+ $posts = $this->get_posts( $table_name, $select, $where__or_pid );
	195	+ return ! empty( $posts[0] ) ? $posts[0] : null;
	196	+ }
	197	+
	198	+ public function get_posts( $table_name, $select = '*', $wheres = [], $join_table = '', $group_by_col = '', $join_type = 'LEFT JOIN', $extra_query = '' ) {
	199	+ $query = "SELECT $select FROM $table_name";
	200	+ if ( ! empty( $join_table ) ) {
	201	+ $query .= " AS a $join_type `$join_table` AS b ON a.nx_id = b.nx_id";
	202	+ }
	203	+ $query .= $this->get_where_query( $wheres );
	204	+ if ( ! empty( $group_by_col ) ) {
	205	+ $query .= " GROUP BY $group_by_col";
	206	+ }
	207	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	208	+ $posts = $this->wpdb->get_results( "$query $extra_query", ARRAY_A );
	209	+ $posts = array_map( [ $this, 'unserialize_data' ], $posts );
	210	+ return $posts;
	211	+ }
	212	+
	213	+ public function get_col( $table_name, $col, $wheres, $distinct = 'DISTINCT' ) {
	214	+ $col = esc_sql( $col );
	215	+ $posts = $this->get_posts( $table_name, "$distinct `$col`", $wheres );
	216	+ return array_column( $posts, $col );
	217	+ }
	218	+
	219	+ // public function get_count($table_name, $col, $wheres) {
	220	+ // $posts = $this->get_posts($table_name, "$col", $wheres);
	221	+ // return array_column($posts, $col, 0);
	222	+ // }
	223	+
	224	+ public function get_source_count( $table_name, $col, $wheres = [] ) {
	225	+ $results = [];
	226	+ $col = esc_sql( $col );
	227	+ $posts = $this->get_posts( $table_name, "`$col`, count(*)", $wheres, '', "`$col`" );
	228	+ foreach ( $posts as $key => $value ) {
	229	+ $results[ $value[ $col ] ] = $value['count(*)'];
	230	+ }
	231	+ return $results;
	232	+ }
	233	+
	234	+ public function delete_post( $table_name, $post_id ) {
	235	+ $id = $this->get_primary_col( $table_name );
	236	+ return $this->delete_posts( $table_name, [ $id => $post_id ] );
	237	+ }
	238	+
	239	+ public function delete_posts( $table_name, $wheres, $limit = 0 ) {
	240	+ if ( $limit ) {
	241	+ return $this->delete_posts_limit( $table_name, $wheres, $limit );
	242	+ }
	243	+ return $this->wpdb->delete( $table_name, $wheres );
	244	+ }
	245	+
	246	+ public function delete_posts_limit( $table_name, $wheres, $limit ) {
	247	+ $limit = absint( $limit );
	248	+ $query = "DELETE FROM `$table_name` ";
	249	+ $query .= $this->get_where_query( $wheres );
	250	+ $query .= " LIMIT $limit";
	251	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	252	+ return $this->wpdb->query( $query );
	253	+ }
	254	+
	255	+ public function serialize_data( $post ) {
	256	+ if ( ! empty( $post['data'] ) ) {
	257	+ $post['data'] = maybe_serialize( $post['data'] );
	258	+ }
	259	+ // created_at and updated_at if not empty convert to mysql date
	260	+ if ( ! empty( $post['created_at'] ) ) {
	261	+ $post['created_at'] = gmdate( 'Y-m-d H:i:s', strtotime( $post['created_at'] ) );
	262	+ }
	263	+ if ( ! empty( $post['updated_at'] ) ) {
	264	+ $post['updated_at'] = gmdate( 'Y-m-d H:i:s', strtotime( $post['updated_at'] ) );
	265	+ }
	266	+ return $post;
	267	+ }
	268	+
	269	+ public function unserialize_data( $post ) {
	270	+ if ( ! empty( $post['data'] ) ) {
	271	+ $post['data'] = maybe_unserialize( $post['data'] );
	272	+ }
	273	+ return $post;
	274	+ }
	275	+
	276	+ public function get_primary_col( $table_name ) {
	277	+ if ( $table_name == self::$table_posts ) {
	278	+ return 'nx_id';
	279	+ } elseif ( $table_name == self::$table_entries ) {
	280	+ return 'entry_id';
	281	+ } elseif ( $table_name == self::$table_stats ) {
	282	+ return 'stat_id';
	283	+ }
	284	+ }
	285	+
	286	+ public function get_where_query( $wheres ) {
	287	+ $query = '';
	288	+ if ( ! empty( $wheres ) && is_string( $wheres ) ) {
	289	+ return " $wheres ";
	290	+ } elseif ( ! empty( $wheres ) ) {
	291	+ $query .= ' WHERE true=true';
	292	+ foreach ( $wheres as $key => $value ) {
	293	+ $compare = '=';
	294	+ if ( is_array( $value ) ) {
	295	+ $compare = $value[0];
	296	+ // $value = $value[1];
	297	+ if ( 'IN' === strtoupper( $compare ) && is_array( $value[1] ) ) {
	298	+ $value = array_map( 'esc_sql', $value[1] );
	299	+ $value = "('" . implode( "', '", $value ) . "')";
	300	+ } elseif ( 'BETWEEN' === strtoupper( $compare ) && isset( $value[1], $value[2] ) ) {
	301	+ $value = $this->wpdb->prepare( '%s AND %s', $value[1], $value[2] );
	302	+ } elseif ( in_array( $compare, [ '<', '<=', '>', '>=' ], true ) ) {
	303	+ $value = "'" . esc_sql( $value[1] ) . "'";
	304	+ } else {
	305	+ throw new \Exception( esc_html( "Unknown parameter $compare." ), 1 );
	306	+ }
	307	+ } else {
	308	+ $value = "'" . esc_sql( $value ) . "'"; // is_bool($value) ? $value :.
	309	+ }
	310	+ $query .= " AND $key $compare $value";
	311	+ }
	312	+ }
	313	+ return $query;
	314	+ }
	315	+
	316	+ public function update_option( $key, $value, $autoload = 'no' ) {
	317	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	318	+ $is_exists = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->prefix}options WHERE option_name=%s LIMIT 1", $key ) );
	319	+ if ( $is_exists ) {
	320	+ if ( $is_exists->option_value == $value ) {
	321	+ return;
	322	+ }
	323	+ $this->wpdb->update( "{$this->wpdb->options}", [ 'option_value' => $value ], [ 'option_name' => $key ] );
	324	+ } else {
	325	+ $this->wpdb->insert( "{$this->wpdb->options}", [
	326	+ 'option_name' => $key,
	327	+ 'option_value' => $value,
	328	+ ]
	329	+ );
	330	+ }
	331	+ }
	332	+ public function get_option( $key, $default = false ) {
	333	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- False positive: the query is prepared via $this->wpdb->prepare(), which this sniff does not recognise, and only $wpdb->prefix table names are interpolated. Audited 2026-07-16.
	334	+ $results = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->wpdb->options} WHERE option_name=%s LIMIT 1", $key ) );
	335	+ if ( $results ) {
	336	+ return ! empty( $results->option_value ) ? $results->option_value : $default;
	337	+ }
	338	+ return $default;
	339	+ }
	340	+
	341	+ /**
	342	+ * Undocumented function
	343	+ *
	344	+ * @param array $format
	345	+ * @param array $data
	346	+ * @return array
	347	+ */
	348	+ public function get_format($format, $data) {
	349	+ if(empty($format)){
	350	+ return null;
	351	+ }
	352	+
	353	+ $result = array();
	354	+ foreach ($data as $key => $value) {
	355	+ if (isset($format[$key])) {
	356	+ $result[] = $format[$key];
	357	+ } else {
	358	+ $result[] = '%s';
	359	+ }
	360	+ }
	361	+ return $result;
	362	+ }
	363	+}