README

一个用于处理数据库连接的类。 一个通用的可互换 CRUD 系统。

这是 版本 5，它将破坏 版本 4 的用户。

主要变更：

• global 函数 ezFunctions.php 文件中使用了 namespace。使用全局函数将要求用户在 .php 文件中以某种形式开始，例如：

  use function ezsql\functions\where;
  // Or
  use function ezsql\functions\{
      getInstance,
      selecting,
      inserting,
  };

• 可以通过魔法方法 get/set 访问的类属性，现在采用 PSR 1 camelCase。

• 将 ez_mysqli 中的 select 重命名为 dbSelect。

• 将类方法和 selecting 的行为重命名为 select。

• selecting 和新的 inserting 方法可以不带表名调用，只需其他必要参数即可。

  • 带有 前缀 的表 名称 可以使用方法 tableSetup(name, prefix) 或 setTable(name), setPrefix(append) 预设/存储，如果没有预设，则返回 false。
  • 此 功能 将添加到 所有 数据库 CRUD 访问方法，每个方法名称都将添加 ing 结尾。

• 删除了传递 table 名称的全局函数，使用以 ing 结尾的预设表名函数。

• 将 cleanInput 重命名为 clean_string

• 将 createCertificate 重命名为 create_certificate

• 添加全局 get_results 以返回不同格式的结果集

版本 4 具有许多现代编程实践，这将破坏 版本 3 的用户。

版本 3 在一个主要方面破坏了 版本 2.1.7，它需要 PHP 5.6。这取消了 mysql 扩展支持，除此之外，在使用库方面没有任何变化，只是增加了新功能。

此库有一个 Database 类，它结合了 Factory 模式和 依赖注入 容器托管。此库现在遵循许多 OOP 原则，其中一个原则是移除了方法的公共访问属性。此库还遵循 PSR-2、PSR-4、PSR-11 规范，以及大部分 PSR-1，这仍是一个正在进行中的工作。

• 更多待办事项...

要全面了解，请参阅 文档 Wiki，它尚未完成。

安装

composer require ezsql/ezsql

使用方法

require 'vendor/autoload.php';

// **** is one of mysqli, pgsql, sqlsrv, sqlite3, or Pdo.
use ezsql\Database;

$db = Database::initialize('****', [$dsn_path_user, $password, $database, $other_settings], $optional_tag);

// Is same as:
use ezsql\Config;
use ezsql\Database\ez_****;

$settings = new Config('****', [$dsn_path_user, $password, $database, $other_settings]);

$db = new ez_****($settings);

此库将假设开发人员正在使用某种具有启用智能感知的 IDE。注释/文档块区域将包含任何缺失的文档。有关其他示例，请参阅 phpunit 测试，这些测试是完全功能性的集成测试，意味着它们是实时数据库测试，没有模拟。

以下自 2.1.7 版本以来已添加：

通用方法

to_string($arrays, $separation = ',');
clean($string);
create_cache(string $path = null);
secureSetup(string $key = 'certificate.key',
    string $cert = 'certificate.crt',
    string $ca = 'cacert.pem',
    string $path = '.'._DS
);
secureReset();
create_certificate(string $privatekeyFile = certificate.key,
    string $certificateFile = certificate.crt,
    string $signingFile = certificate.csr,
    string $ssl_path = null, array $details = [commonName => localhost]
);

快捷表方法

create(string $table = null, ...$schemas);// $schemas requires... column()
column(string $column = null, string $type = null, ...$args);
primary(string $primaryName, ...$primaryKeys);
index(string $indexName, ...$indexKeys);
drop(string $table);

示例

// Creates an database table
create('profile',
    // and with database column name, datatype
    // data types are global CONSTANTS
    // SEQUENCE|AUTO is placeholder tag, to be replaced with the proper SQL drivers auto number sequencer word.
    column('id', INTR, 11, AUTO, PRIMARY), // mysqli
    column('name', VARCHAR, 50, notNULL),
    column('email', CHAR, 25, NULLS),
    column('phone', TINYINT)
);

innerJoin(string $leftTable = null, string $rightTable = null,
    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);

leftJoin(string $leftTable = null, string $rightTable = null,
    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);

rightJoin(string $leftTable = null, string $rightTable = null,
    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);

fullJoin(string $leftTable = null, string $rightTable = null,
    string $leftColumn = null, string $rightColumn = null, string $tableAs = null, $condition = EQ);

prepareOn(); // When activated will use prepare statements for all shortcut SQL Methods calls.
prepareOff(); // When off shortcut SQL Methods calls will use vendors escape routine instead. This is the default behavior.

快捷 SQL 方法

• having(...$having);
• groupBy($groupBy);
• union(string $table = null, $columnFields = '*', ...$conditions);
• unionAll(string $table = null, $columnFields = '*', ...$conditions);
• orderBy($orderBy, $order);
• limit($numberOf, $offset = null)
• where( ...$whereConditions);
• select(string $table = null, $columnFields = '*', ...$conditions);
• create_select(string $newTable, $fromColumns, $oldTable = null, ...$conditions);
• select_into(string $newTable, $fromColumns, $oldTable = null, ...$conditions);
• 更新（string $table = null, $keyAndValue, ...$whereConditions）；
• 删除（string $table = null, ...$whereConditions）；
• 替换（string $table = null, $keyAndValue）；
• 插入（string $table = null, $keyAndValue）；
• 创建（string $table = null, ...$schemas）；
• 删除表（string $table = null）；
• 修改（string $table = null, ...$alteringSchema）；
• 插入选择（string $toTable = null, $toColumns = '*', $fromTable = null, $fromColumns = '*', ...$conditions）；

// The variadic ...$whereConditions, and ...$conditions parameters,
//  represent the following global functions.
// They are comparison expressions returning an array with the given arguments,
//  the last arguments of _AND, _OR, _NOT, _andNOT will combine expressions
eq('column', $value, _AND), // combine next expression
neq('column', $value, _OR), // will combine next expression again
ne('column', $value), // the default is _AND so will combine next expression
lt('column', $value)
lte('column', $value)
gt('column', $value)
gte('column', $value)
isNull('column')
isNotNull('column')
like('column', '_%?')
notLike('column', '_%?')
in('column', ...$value)
notIn('column', ...$value)
between('column', $value, $value2)
notBetween('column', $value, $value2)
// The above should be used within the where( ...$whereConditions) clause
// $value will protected by either using escape or prepare statement

// To allow simple grouping of basic $whereConditions,
// wrap the following around a group of the above comparison
// expressions within the where( ...$whereConditions) clause
grouping( eq(key, value, combiner ), eq(key, value, combiner ) )
// The above will wrap beginning and end grouping in a where statement
// where required to break down your where clause.

// Note: The usage of this method will require the user/developer to check
// if `query_string` or `param_array` is valid.
//
// This is really an `private` internal method for other shortcut methods,
// it's made public for `class development` usage only.
//
//
// Supply the the whole `query` string, and placing '?' within, with the same number of arguments in an array.
// It will then determine arguments type, execute, and return results.
query_prepared(string $query_string, array $param_array);
// You will need to call this method to get last successful query result.
// It wll return an object array.
queryResult();

使用上述快捷SQL方法间接使用预处理语句的示例

// To get all shortcut SQL methods calls to use prepare statements
$db->prepareOn(); // This needs to be called at least once at instance creation

$values = [];
$values['name'] = $user;
$values['email'] = $address;
$values['phone'] = $number;
$db->insert('profile', $values);
$db->insert('profile', ['name' => 'john john', 'email' => 'john@email', 'phone' => 123456]);

// returns result set given the table name, column fields, and ...conditions
$result = $db->select('profile', 'phone', eq('email', $email), between('id', 1, $values));

foreach ($result as $row) {
    echo $row->phone;
}

$result = $db->select('profile', 'name, email',
    // Conditionals can also be called, stacked with other functions like:
    //  innerJoin(), leftJoin(), rightJoin(), fullJoin()
    //      as (leftTable, rightTable, leftColumn, rightColumn, tableAs, equal condition),
    //  where( eq( columns, values, _AND ), like( columns, _d ) ),
    //  groupBy( columns ),
    //  having( between( columns, values1, values2 ) ),
    //  orderBy( columns, desc ),
    //  limit( numberOfRecords, offset ),
    //  union(table, columnFields, conditions),
    //  unionAll(table, columnFields, conditions)
    $db->where( eq('phone', $number, _OR), neq('id', 5) ),
    //  another way: where( array(key, operator, value, combine, combineShifted) );
    //  or as strings double spaced: where( "key  operator  value  combine  combineShifted" );
    $db->orderBy('name'),
    $db->limit(1)
);

foreach ($result as $row) {
    echo $row->name.' '.$row->email;
}

// To get results in `JSON` format
$json = get_results(JSON, $db);

直接使用预处理语句的示例，未使用快捷SQL方法

$db->query_prepared('INSERT INTO profile( name, email, phone) VALUES( ?, ?, ? );', [$user, $address, $number]);

$db->query_prepared('SELECT name, email FROM profile WHERE phone = ? OR id != ?', [$number, 5]);
$result = $db->queryResult(); // the last query that has results are stored in `lastResult` protected property
// Or for results in other formats use the global function, will use global database instance if no `$db` supplied
$result = get_results(/* OBJECT|ARRAY_A|ARRAY_N|JSON */, $db); // Defaults to `OBJECT`

foreach ($result as $row) {
    echo $row->name.' '.$row->email;
}

大多数快捷方法都有对应的 全局 函数。它们只能通过在 .php 文件开头进行访问

use function ezsql\functions\functionBelow;
// Or as here, a complete list.
use function ezsql\functions\{
    database,
    mysqlInstance,
    pgsqlInstance,
    mssqlInstance,
    sqliteInstance,
    pdoInstance,
    tagInstance,
    setInstance,
    getInstance,
    clearInstance,
    get_vendor,
///
    to_string,
    clean_string,
    is_traversal,
    sanitize_path,
    create_certificate,
///
    column,
    primary,
    foreign,
    unique,
    index,
    addColumn,
    dropColumn,
    changingColumn,
///
    eq,
    neq,
    ne,
    lt,
    lte,
    gt,
    gte,
    isNull,
    isNotNull,
    like,
    in,
    notLike,
    notIn,
    between,
    notBetween,
///
    where,
    grouping,
    groupBy,
    having,
    orderBy,
    limit,
    innerJoin,
    leftJoin,
    rightJoin,
    fullJoin,
    union,
    unionAll,
///
    creating,
    deleting,
    dropping,
    replacing,
    selecting,
    inserting,
    altering,
    get_results,
    table_setup,
    set_table,
    set_prefix,
    select_into,
    insert_select,
    create_select,
};

有关函数 用法/文档，请参阅 ezFunctions.php。

关于作者和 贡献者

贡献

鼓励和欢迎贡献；我总是很高兴在Github上收到反馈或拉取请求 :) 为错误和新的功能创建 Github Issues，并评论你感兴趣的问题。

许可协议

ezsql 是开源软件，最初根据 (LGPL-3.0) 许可，附加部分根据 (MIT) 许可。