Sqlquerymanager Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require beeflow/sqlquerymanager:dev-master
   

   For Laravel (non-Symfony), manually register the service provider in config/app.php:

   'providers' => [
       // ...
       Beeflow\SQLQueryManager\SQLQueryServiceProvider::class,
   ],
   

2. First Use Case: Define a SQL file (e.g., sql_example.sql) in a directory (e.g., database/sql):

   SELECT * FROM users WHERE email = {value->email}
   

   Call it from a controller:

   use Beeflow\SQLQueryManager\Facades\SQLQueryManager;
   
   $results = SQLQueryManager::sqlExample(['value' => 'user@example.com']);
   

3. Directory Configuration: Set the default SQL directory in config/services.php (if using Laravel’s service container):

   'sql_query_manager' => [
       'sql_directory' => database_path('sql'),
   ],
   

   Or dynamically:

   SQLQueryManager::setSqlDirectory(database_path('sql'));
   

Implementation Patterns

Core Workflows

1. Parameterized Queries: Use {value->vartype} syntax in SQL files for type-safe binding:

   INSERT INTO users (name, email) VALUES ({name->secureString}, {email->email})
   

   Pass values as an associative array:

   SQLQueryManager::createUser([
       'name'  => 'John Doe',
       'email' => 'john@example.com',
   ]);
   

2. Custom Value Types: Extend functionality by registering new vartypes (e.g., phone, password):

   // In a service provider's boot method
   $this->app->bind('app.phone', function() {
       return new \App\Vartypes\Phone();
   });
   

   Tag the service in config/services.php:

   'tags' => [
       'beeflow.sql_manager.vartype' => ['alias' => 'phone'],
   ],
   

   Use in SQL:

   UPDATE users SET phone = {phone->phone} WHERE id = {id->integer}
   

3. Dynamic Query Execution: Override the SQL directory temporarily:

   SQLQueryManager::setSqlDirectory(storage_path('temp_sql'));
   $results = SQLQueryManager::dynamicQuery(['param' => 'value']);
   

4. Conditional Logic: Use placeholder names with conditions (e.g., notEmptyValue):

   SELECT * FROM orders WHERE status = {status->string} AND {notEmptyValue->integer} IS NOT NULL
   

   Pass null or a value:

   SQLQueryManager::filterOrders(['status' => 'shipped', 'notEmptyValue' => 1]);
   

5. Array Handling: For arrays without vartypes, use valueArrayWithoutAtype:

   SELECT * FROM tags WHERE id IN ({valueArrayWithoutAtype->array})
   

   Pass as an array:

   SQLQueryManager::findTags(['valueArrayWithoutAtype' => [1, 2, 3]]);
   

Integration Tips

• Laravel Query Builder: Combine with Laravel’s query builder for complex logic:

  $query = DB::select(SQLQueryManager::getQuery('complex_query', ['param' => 'value']));
  

• Validation: Validate custom vartypes before binding (e.g., email format, VAT number).
• Logging: Log generated queries for debugging:

  \Log::debug('Generated Query:', ['query' => SQLQueryManager::getQuery('query_name', $params)]);
  

Gotchas and Tips

Pitfalls

1. Type Casting Issues:

   • Passing a string to an integer vartype defaults to 0. Validate types explicitly:

     if (!is_int($request->input('age'))) {
         throw new \InvalidArgumentException('Age must be an integer.');
     }
     

   • Use secureString for strings to avoid implicit casting.

2. SQL File Parsing:

   • Ensure placeholders ({value->vartype}) are exact matches to array keys. Typos will cause silent failures.
   • Avoid spaces or special characters in placeholder names.

3. Deprecated SQLQuery Class: The SQLQuery class is deprecated. Use the service facade (SQLQueryManager) instead.

4. Directory Permissions: Ensure the SQL directory is readable by the web server. Use storage_path('sql') for private files.

5. Array Syntax: For arrays, use ->array vartype only if the SQL supports IN clauses. Raw arrays may break queries:

   -- Works:
   SELECT * FROM users WHERE id IN ({ids->array})
   
   -- Fails (unless manually formatted):
   SELECT * FROM users WHERE id = {ids->array}
   

Debugging

1. Query Dumping: Use getQuery() to inspect the final SQL before execution:

   $sql = SQLQueryManager::getQuery('query_name', $params);
   \Log::debug($sql);
   

2. Exception Handling: Catch Beeflow\SQLQueryManager\Exception\SQLQueryException for invalid vartypes or missing files:

   try {
       SQLQueryManager::execute('invalid_query', ['param' => 'value']);
   } catch (\Exception $e) {
       \Log::error($e->getMessage());
   }
   

3. Vartype Validation: Override validate() in custom vartypes to enforce rules:

   class Phone extends AbstractVarType {
       public function validate($value) {
           if (!preg_match('/^\+?[0-9]{10,15}$/', $value)) {
               throw new \InvalidArgumentException('Invalid phone number.');
           }
           return $value;
       }
   }
   

Extension Points

1. Custom Vartypes: Extend Beeflow\SQLQueryManager\VarType\AbstractVarType to create reusable types:

   namespace App\Vartypes;
   
   use Beeflow\SQLQueryManager\VarType\AbstractVarType;
   
   class Password extends AbstractVarType {
       public function __construct() {
           $this->type = 'password';
       }
   
       public function validate($value) {
           if (strlen($value) < 8) {
               throw new \InvalidArgumentException('Password too short.');
           }
           return password_hash($value, PASSWORD_DEFAULT);
       }
   }
   

2. Query Preprocessing: Hook into the query generation process by extending the service provider:

   public function boot() {
       SQLQueryManager::extend(function ($query, $params) {
           // Modify $query or $params before execution
           return $query;
       });
   }
   

3. Database Abstraction: Use the generated SQL with Laravel’s DB::select() or DB::statement() for cross-database compatibility:

   $results = DB::select(SQLQueryManager::getQuery('query_name', $params));
   

4. Caching: Cache compiled SQL queries for performance:

   $cacheKey = md5(serialize($params));
   if (cache()->has($cacheKey)) {
       return cache()->get($cacheKey);
   }
   $results = SQLQueryManager::execute('query_name', $params);
   cache()->put($cacheKey, $results, now()->addHours(1));