Edit via SFTP
  1. <?php namespace ferret\data;
  2. /*
  3.   PURPOSE: revised table-class structure: database traits
  4.   ASSUMES: for now, all databases use SQL.
  5.   This may change later and we'll have to restructure a bit.
  6.   HISTORY:
  7.   2019-09-13 started
  8.   2019-10-11 GetString_Source_Safe(); for now, commenting out Readable/Writable Source methods
  9.   and tNamedTableSQL trait
  10.   2019-11-25 Decided that tNamedTable makes the class a *table* class,
  11.   so that's where we define GetTableName() (formerly TableName())
  12.   and alias it to GetString_Source().
  13.   2020-07-31 *all* rowsets now have the ability to store a native driver
  14.   See matching changes in db/base.php
  15. */
  16.  
  17. /*::::
  18.   PURPOSE: fcDataSource + this = table with db connection
  19.   REQUIREMENT: class constructor should include same argument as TraitConstructor()
  20.   and by default should call TraitConstructor(), unless you are doing something different.
  21.   USAGE: Could theoretically be used in any class that expects a direct link to a database,
  22.   but was intended for use in Table types.
  23.   HISTORY:
  24.   2020-01-11 Added FetchRecord() (is this duplicating functionality already written elsewhere?)
  25. */
  26. trait tRowsetFetcher {
  27.  
  28. //public $sql; // for debugging & error messages
  29.  
  30. // ++ READ DATA ++ //
  31.  
  32. public function FetchRecords($sql) : cSelectResult {
  33. $db = $this->GetDatabase();
  34. $os = $db->FetchRecordset($sql,$this);
  35. return $os;
  36. }
  37. public function FetchRecord($sql) : cRecordResult {
  38. $db = $this->GetDatabase();
  39. $osrs = $db->FetchRecordset($sql,$this);
  40. if ($osrs->GetOkay()) {
  41. $rs = $osrs->GetRows();
  42. $qr = $rs->RowCount();
  43. if ($qr == 1) {
  44. $osrc = $rs->NextRow();
  45. } elseif ($qr == 0) {
  46. $osrc = new cRecordResult();
  47. $osrc->ClearIt(FALSE);
  48. } else {
  49. throw new \exception("Ferreteria data error: expected no more than one record, but got $qr. <b>Query</b>: $sql");
  50. }
  51. } else {
  52. throw new \exception("Ferreteria internal error - query failed. SQL: $sql");
  53. }
  54. return $osrc;
  55. }
  56.  
  57. // -- READ DATA -- //
  58. }
  59. trait tSelectable {
  60. use tRowsetFetcher;
  61.  
  62. // ++ CONFIGURATION ++ //
  63.  
  64. abstract protected function GetString_Source() : string;
  65. // CEMENT
  66. protected function GetString_Source_Safe() { return '('.$this->GetString_Source().')'; }
  67. /*----
  68.   RETURNS: List of field names for SELECT to retrieve by default
  69.   OVERRIDEABLE
  70.   */
  71. protected function GetString_ReadableFields() : string { return '*'; }
  72.  
  73. // -- CONFIGURATION -- //
  74. // ++ READ DATA ++ //
  75.  
  76. /*----
  77.   REQUIRES: table name fx and data source
  78.   RETURNS: Select Result, which ideally will have a Rowset
  79.   but if there was an error, ->Rowset()->HasIt() will be FALSE.
  80.   HISTORY:
  81.   2019-12-10 simplified
  82.   */
  83. public function SelectRecords($sqlWhere=NULL,$sqlSort=NULL,$sqlOther=NULL) : cSelectResult {
  84. $sql = $this->FigureSelectSQL($sqlWhere,$sqlSort,$sqlOther);
  85. $os = $this->FetchRecords($sql);
  86. return $os;
  87. }
  88. /*----
  89.   PURPOSE: Like SelectRecords(), but expects no more than a single row and returns a RowStatus instead of a recordset
  90.   Throws an error if more than one row is returned.
  91.   HISTORY:
  92.   2019-12-10 created
  93.   2020-01-13 revised a bit to handle additional API changes
  94.   Note that single-row status doesn't currently handle SQL; might need to add later.
  95.   2020-12-11 was not properly handling case where FetchRecords() fails; fixed?
  96.   DEBUGPOINT
  97.   */
  98. public function SelectRecord($sqlWhere=NULL,$sqlOther=NULL) : cSelectResult {
  99. $sql = $this->FigureSelectSQL($sqlWhere,NULL,$sqlOther);
  100. $osSel = $this->FetchRecords($sql); // cSelectResult
  101. if ($osSel->Action()->GetOkay()) {
  102. $rs = $osSel->Rowset()->GetIt();
  103. $nRows = $rs->RowCount();
  104. if ($nRows > 1) {
  105. $sError = "Ferreteria data error: expected one row, got $nRows.";
  106. $e = new \ferret\except\cData($sError);
  107. $e->AddDiagnostic('SQL: '.$sql);
  108. throw $e;
  109. }
  110. // load first/only row (2021-04-20 this may not be the right way...)
  111. $osRow = $rs->NextRow(); // RecordStatus
  112. $osSel->Record()->CopyIt($osRow);
  113. }
  114. return $osSel; // return single-row status
  115. }
  116.  
  117. // -- READ DATA -- //
  118. // ++ CALCULATION ++ //
  119.  
  120. protected function FigureSelectSQL($sqlWhere=NULL,$sqlSort=NULL,$sqlOther=NULL,$sqlFields=NULL) {
  121. if (is_null($sqlFields)) {
  122. $sqlFields = $this->GetString_ReadableFields();
  123. }
  124. $sqlSource = $this->GetString_Source_Safe();
  125. $sql = "SELECT $sqlFields FROM $sqlSource";
  126. if (!is_null($sqlWhere)) {
  127. $sql .= ' WHERE '.$sqlWhere;
  128. }
  129. if (!is_null($sqlSort)) {
  130. $sql .= ' ORDER BY '.$sqlSort;
  131. }
  132. if (!is_null($sqlOther)) {
  133. $sql .= ' '.$sqlOther;
  134. }
  135. return $sql;
  136. }
  137.  
  138. // -- CALCULATION -- //
  139.  
  140. }
  141. /*----
  142.   HISTORY:
  143.   2020-07-12 Realized that a "named table" should be a sub-type of "selectable",
  144.   since we can also select from a query. Changing inheritance structure...
  145.   REQUIRES: tSelectable
  146. */
  147. trait tNamedTable {
  148.  
  149. abstract protected function GetTableName();
  150. protected function GetString_Source() : string { return $this->GetTableName(); }
  151.  
  152. /*----
  153.   REPLACES TableName_Cooked()
  154.   TODO:
  155.   * also escape any backticks in source (SECURITY issue)
  156.   * clarify usage cases for this
  157.  
  158.   */
  159. protected function GetString_Source_Safe() { return '`'.$this->GetString_Source().'`'; }
  160. }
  161. // REQUIRES: tNamedTable
  162. trait tInsertable {
  163.  
  164. /*----
  165.   RETURNS: ID of new record, or FALSE if it could not be created
  166.   HISTORY:
  167.   2010-11-16 Added "array" requirement for iData
  168.   2010-11-20 Calculation now takes place in SQL_forInsert()
  169.   2019-11-26 Created DoInsert() and deprecated Insert()
  170.   */
  171. public function DoInsert(array $arData) : cInsertResult {
  172. $sql = $this->FigureInsertSQL($arData);
  173. $db = $this->GetDatabase();
  174. $ok = $db->ExecuteAction($sql);
  175.  
  176. $os = new cInsertResult();
  177. $os->SetResults($sql,$ok,$this);
  178. if ($ok) {
  179. $id = $db->CreatedID();
  180. $osid = $os->ID();
  181. $osid->SetIt($id);
  182. } else {
  183. $os->SetMessage($db->ErrorString());
  184. }
  185. return $os;
  186. }
  187. // DEPRECATED
  188. public function Insert(array $arData) {
  189. throw new \exception('2021-04-19 is anything still calling this?');
  190. $oStat = $this->DoInsert($arData);
  191. if ($oStat->GetOkay()) {
  192. return $oStat->GetID();
  193. } else {
  194. return FALSE;
  195. }
  196. }
  197. /*----
  198.   RETURNS: SQL for creating a new record for the given data
  199.   HISTORY:
  200.   2010-11-20 Created.
  201.   2016-10-27 Adapted from db.v1 to db.v2.
  202.   */
  203. protected function FigureInsertSQL(array $arData) : string {
  204. $sqlNames = '';
  205. $sqlVals = '';
  206. foreach($arData as $key=>$val) {
  207. if ($sqlNames != '') {
  208. $sqlNames .= ',';
  209. $sqlVals .= ',';
  210. }
  211. if (!(is_string($val) || is_numeric($val))) {
  212. if (is_object($val)) {
  213. $sDescr = 'class '.get_class($val);
  214. } else {
  215. $sDescr = 'type '.gettype($val);
  216. }
  217. throw new exception("Internal Error: The INSERT value of [$key] is of $sDescr.");
  218. }
  219. $sqlNames .= "`$key`";
  220. $sqlVals .= $val;
  221. }
  222. $sqlTable = $this->GetString_Source_Safe();
  223. return "INSERT INTO $sqlTable ($sqlNames) VALUES($sqlVals);";
  224. }
  225. }
  226. /*----
  227.   REQUIRES: tNamedTable
  228.   HOSTED BY: cTabloid family
  229.   NOTE: Although updating on a key-value is the most common form of update,
  230.   this trait does not require the existence of a key. It instead expects
  231.   the caller to provide a filter for the row(s) to be modified.
  232. */
  233. trait tUpdatable {
  234.  
  235. /*----
  236.   HISTORY:
  237.   2019-06-11 Removing $isNativeData parameter -- use $db->SanitizeValueArray() instead
  238.   2019-11-26 Created DoUpdate() and deprecated Update()
  239.   */
  240. public function DoUpdate(array $arChg, string $sqlWhere) : cUpdateStatus {
  241. $sql = $this->FigureUpdateSQL($arChg,$sqlWhere);
  242. $db = $this->GetDatabase();
  243. $ok = $db->ExecuteAction($sql);
  244.  
  245. $oStat = new cUpdateStatus($sql,$ok,$this);
  246. return $oStat;
  247. }
  248. /*----
  249.   INPUT:
  250.   $arChg = key=>value array of fields to be written
  251.   $sqlWhere = SQL for WHERE clause
  252.   HISTORY:
  253.   2010-11-20 Created
  254.   2013-07-14 Adapted from clsTable_key_single to static method in clsTable_abstract
  255.   2016-10-30 tweaked very slightly for db.v2
  256.   2017-07-29 added optional $isNativeData parameter
  257.   2019-06-11 removed $isNativeData -- use $db->SanitizeValueArray() to prepare values
  258.   2019-10-11 moved from old db.table.php trait ftName_forTable to trait tUpdateable
  259.   and renamed from FigureSQL_forUpdate() to FigureUpdateSQL().
  260.   */
  261. public function FigureUpdateSQL(array $arChg,$sqlWhere) : string {
  262. $sqlSet = '';
  263. foreach($arChg as $key=>$val) {
  264. if (is_scalar($val)) {
  265. if ($sqlSet != '') {
  266. $sqlSet .= ',';
  267. }
  268. $sqlSet .= ' `'.$key.'`='.$val;
  269. } else {
  270. throw new exception('Ferreteria parameter error: value for "'
  271. .$key
  272. .'" is "'
  273. .gettype($val)
  274. .'", which is not a scalar type.'
  275. );
  276. }
  277. }
  278. $sqlName = $this->GetString_Source_Safe();
  279. return "UPDATE $sqlName SET$sqlSet WHERE $sqlWhere";
  280. }
  281. }
  282. /*::::
  283.   PURPOSE: This could just be part of tSelectable, but I'm keeping it separate because
  284.   of how these two functions work together and aren't really used by anything else.
  285. */
  286. trait tSelectableUnique {
  287. use tSelectable;
  288.  
  289. /*----
  290.   RETURNS: SQL filter string for record that matches the given array data
  291.   INPUT: $arData = array of raw field values to match
  292.   NOTES:
  293. * This could just as easily be in the Database class.
  294. * $arVals needs to be converted to a slightly different format for use with cSQLt_Filt.
  295. That's what ValueArray_to_ConditionArray() does:
  296. $arVals[field name] = value
  297. $arCond[index] = "`field name` = value"
  298.   */
  299. protected function FigureSQL_toMatchValues(array $arVals) {
  300. if (is_array($arVals)) {
  301. $arValsSQL = $this->GetDatabase()->SanitizeValueArray($arVals);
  302. $arCond = cSQLt_Filt::ValueArray_to_ConditionArray($arValsSQL);
  303. $oFilt = new cSQLt_Filt('AND',$arCond);
  304. return $oFilt->RenderValue();
  305. } else {
  306. throw new InvalidArgumentException('Internal error: expecting an array, got this: '.print_r($arVals,TRUE));
  307. }
  308. }
  309. /*----
  310.   NOTE: If we ever want to allow more than one match, call it FetchRecords_thatMatchValues() -- but it probably
  311.   should not go in this trait. (Not sure what that would actually be useful for, really.)
  312.   */
  313. protected function FetchRecord_toMatchValues(array $ar) {
  314. $sqlFilt = $this->FigureSQL_toMatchValues($ar);
  315. $rs = $this->SelectRecords($sqlFilt);
  316. if ($rs->HasRows()) {
  317. if ($rs->RowCount() > 1) {
  318. // TODO: change this so it doesn't show up on the screen, but only logs/emails an error
  319. throw new exception('Ferreteria Data Warning: more than one row found for query: '.$this->sql);
  320. }
  321. return $rs;
  322. } else {
  323. return NULL; // no matches found
  324. }
  325. }
  326. }
  327. /*::::
  328.   PURPOSE: base for Tabloid classes that work with a Database
  329.   ADDS: requires Database on construction
  330.   ABSTRACT: row class names
  331.   HISTORY:
  332.   2020-07-31 Removed tDrivenTable
  333.   2021-09-06 moved parent, constructor, [G|S]etDatabase() from cDataTable into new cDataTabloid class
  334. */
  335. abstract class cDataTabloid extends cTabloid {
  336. public function __construct(cDatabase $db) {
  337. parent::__construct(); // creates the Workspace
  338. $this->SetDatabase($db);
  339. }
  340. protected function SetDatabase(cDatabase $db) { $this->Space()->Database()->SetIt($db); }
  341. protected function GetDatabase() : cDatabase { return $this->Space()->Database()->GetIt(); }
  342. }
  343. /*::::
  344.   PURPOSE: standart Table I/O functions
  345.   ADDS: data manipulation methods from traits (select, insert, update)
  346.   HISTORY:
  347.   2020-07-31 Removed tDrivenTable
  348.   2021-09-06
  349.   * moved parent, constructor, [G|S]etDatabase() from cDataTable into new cDataTabloid class
  350.   * renamed from cDataTable to cTableBase
  351. */
  352. abstract class cTableBase extends cDataTabloid { use tSelectableUnique, tInsertable, tUpdatable; }
  353.  
  354. trait tDefaultKeylessRows {
  355. protected function MultiRowClass() : string { return cRecordRowsKeyless::class; }
  356. }
  357. /*----
  358.   PURPOSE: DB table
  359.   ABSTRACT: SingularName(), GetAllRows()
  360.   HISTORY:
  361.   2020-08-01 added tNamedTable, because if it's really a Table, then it has a name.
  362.   Might cause clash with child-classes that also use tNamedTable?
  363. */
  364. abstract class cStandardTableGeneric extends cTableBase {
  365. use tDefaultKeylessRows;
  366. use tNamedTable;
  367. }
  368.  
  369. /*----
  370.   TODO: we need both a query type that *does* have a standard source, and another one that doesn't
  371.   (and just knows what kind of recordset to spit out)
  372.   ...mainly the second one, really.
  373. */
  374. /*----
  375.   PURPOSE: database query that doesn't have a specific source
  376.   It just needs the ability to fetch from a database.
  377.   tRowsetFetcher provides the fetching
  378.   HISTORY:
  379.   2020-01-06 added tDrivenTable
  380.   2021-09-06
  381.   * changed parent from cTabloid to cDataTabloid
  382.   * renamed from cSourcelessQuery to cQueryBase
  383. */
  384. abstract class cQueryBase extends cDataTabloid {
  385. use tRowsetFetcher;
  386. use tDefaultKeylessRows;
  387. }
  388. /*----
  389.   PURPOSE: database query that defaults to a specific source (table, query, or select statement
  390.   NOTE 2019-11-13: does anything actually use this? It doesn't have a driver.
  391. */
  392. /* 2021-09-06 disabling, because I'm renaming things and I can't include this if I don't know what it's for.
  393. abstract class cSourcedQuery extends cSourcelessQuery {
  394.   use tSelectable;
  395. }
  396. */
  397.