LilDb.js

{
    "type": "file",
    "namespace": {
        "type": "namespace",
        "name": "Tlf",
        "declaration": "namespace Tlf;",
        "class": [
            {
                "type": "class",
                "docblock": {
                    "type": "docblock",
                    "description": "A lil tiny database class"
                },
                "namespace": "Tlf",
                "fqn": "Tlf\\LilDb",
                "name": "LilDb",
                "declaration": "class LilDb",
                "properties": [
                    {
                        "type": "property",
                        "modifiers": [
                            "public",
                            "\\PDO"
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "a pdo instance"
                        },
                        "name": "pdo",
                        "declaration": "public \\PDO $pdo;"
                    }
                ],
                "methods": [
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "user",
                                "declaration": "string $user"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "password",
                                "declaration": "string $password"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "db",
                                "declaration": "string $db"
                            },
                            {
                                "type": "arg",
                                "name": "host",
                                "value": "'localhost'",
                                "declaration": "$host='localhost'"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Convenience method to initialize with pdo",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "Tlf\\LilDb"
                                }
                            ]
                        },
                        "modifiers": [
                            "static",
                            "public"
                        ],
                        "name": "new",
                        "body": "$pdo = new \\PDO(\"mysql:dbname=${db};host=${host}\", $user, $password);\n$pdo->setAttribute(\\PDO::ATTR_ERRMODE, \\PDO::ERRMODE_EXCEPTION);\n$ldb = new static($pdo);\nreturn $ldb;",
                        "declaration": "static public function new(string $user, string $password, string $db, $host='localhost')"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "dbName",
                                "value": "':memory:'",
                                "declaration": "string $dbName = ':memory:'"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Convenience method to initialize sqlite db in memory",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "Tlf\\LilDb"
                                }
                            ]
                        },
                        "modifiers": [
                            "static",
                            "public"
                        ],
                        "name": "sqlite",
                        "body": "$pdo = new \\PDO('sqlite:'.$dbName);\n$pdo->setAttribute(\\PDO::ATTR_ERRMODE, \\PDO::ERRMODE_EXCEPTION);\n$ldb = new static($pdo);\nreturn $ldb;",
                        "declaration": "static public function sqlite(string $dbName = ':memory:')"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "name": "dbName",
                                "value": "':memory:'",
                                "declaration": "$dbName = ':memory:'"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Convenience method to initialize mysql db in memory"
                        },
                        "modifiers": [
                            "static",
                            "public"
                        ],
                        "name": "mysql",
                        "body": "$pdo = new \\PDO('mysql:'.$dbName);\n$pdo->setAttribute(\\PDO::ATTR_ERRMODE, \\PDO::ERRMODE_EXCEPTION);\n$ldb = new static($pdo);\nreturn $ldb;",
                        "declaration": "static public function mysql($dbName = ':memory:')"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "\\PDO"
                                ],
                                "name": "pdo",
                                "declaration": "\\PDO $pdo"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Initialize with a db handle",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "param",
                                    "description": "$pdo a pdo instance"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "__construct",
                        "body": "$this->pdo = $pdo;",
                        "declaration": "public function __construct(\\PDO $pdo)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "tableName",
                                "declaration": "string $tableName"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "colDefinitions",
                                "declaration": "array $colDefinitions"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "bool"
                                ],
                                "name": "recreateIfExists",
                                "value": "false",
                                "declaration": "bool $recreateIfExists=false"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Create a new table if it doesn't exist.\n",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "param2",
                                    "description": "$colDefinitions array of columns like: `['col_name'=>'VARCHAR(80)', 'col_two'=> 'integer']`"
                                },
                                {
                                    "type": "attribute",
                                    "name": "param3",
                                    "description": "$recreateIfExists true\/false to include `DROP TABLE IF EXISTS table_name`"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "create",
                        "body": "$colStatements = [];\nforeach ($colDefinitions as $col => $definition){\n    $statement = '`'.$col.'` '. $definition;\n    $colStatements[] = $statement;\n}\n$colsSql = implode(\", \", $colStatements);\n$drop = $recreateIfExists ? \"DROP TABLE IF EXISTS `{$tableName}`;\\n\" : '';\n$sql =\n<<<SQL\n    {$drop}\n    CREATE TABLE IF NOT EXISTS `{$tableName}`\n    (\n    {$colsSql}\n    )\n    ;\nSQL;\n$this->exec($sql);",
                        "declaration": "public function create(string $tableName, array $colDefinitions, bool $recreateIfExists=false)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "sql",
                                "declaration": "string $sql"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "binds",
                                "value": "[]",
                                "declaration": "array $binds=[]"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Execute an Sql statement & get rows back",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "throws",
                                    "description": "if the statement fails to prepare"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "query",
                        "body": "$pdo = $this->pdo;\n$stmt = $pdo->prepare($sql);\nif ($stmt===false){\n    $error = var_export($pdo->errorInfo(),true);\n    throw new \\Exception(\"Sql problem: \\n\".$error.\"\\n\\n\");\n}\n$stmt->execute($binds);\n$rows = $stmt->fetchAll(\\PDO::FETCH_ASSOC);\nreturn $rows;",
                        "declaration": "public function query(string $sql, array $binds=[])"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "tableName",
                                "declaration": "string $tableName"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "whereCols",
                                "value": "[]",
                                "declaration": "array $whereCols=[]"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Get rows from a table with the given $whereCols"
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "select",
                        "body": "$sql = \"SELECT * FROM `${tableName}` \";\n$binds = static::keysToBinds($whereCols);\nif (count($whereCols)>0){\n    $whereStr = \"Where \".static::whereSqlFromCols($whereCols);\n    $sql .= $whereStr;\n}\n$rows = $this->query($sql, $binds);\nreturn $rows;",
                        "declaration": "public function select(string $tableName, array $whereCols=[])"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "table",
                                "declaration": "string $table"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "row",
                                "declaration": "array $row"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Insert a row into the database",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "throws",
                                    "description": "Exception if the insert fails"
                                },
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "the newly inserted id"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "insert",
                        "body": "$pdo = $this->pdo;\n$cols = [];\n$binds = [];\nforeach ($row as $key=>$value){\n    $cols[] = $key;\n    $binds[\":{$key}\"] = $value;\n}\n$colsStr = '`'.implode('`, `',$cols).'`';\n$bindsStr = implode(', ', array_keys($binds));\n$query = \"INSERT INTO `${table}`(${colsStr})\n        VALUES (${bindsStr})\n    \";\n$stmt = $pdo->prepare($query);\nif ($stmt===false){\n    throw new \\Exception(\"Could not insert values into databse.\". print_r($pdo->errorInfo(),true));\n}\n$stmt->execute($binds);\nif ($stmt->errorCode()!=='00000'){\n    print_r($stmt->errorInfo());\n    throw new \\Exception(\"There was an error inserting data\");\n}\nreturn $pdo->lastInsertId();",
                        "declaration": "public function insert(string $table, array $row)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "table",
                                "declaration": "string $table"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "rowSet",
                                "declaration": "array $rowSet"
                            }
                        ],
                        "modifiers": [
                            "public"
                        ],
                        "name": "insertAll",
                        "body": "foreach ($rowSet as $row){\n    $lastInsertId = $this->insert($table, (array)$row);\n}\nreturn $lastInsertId;",
                        "declaration": "public function insertAll(string $table, array $rowSet)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "table",
                                "declaration": "string $table"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "newRowValues",
                                "declaration": "array $newRowValues"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "idColumnName",
                                "value": "'id'",
                                "declaration": "string $idColumnName='id'"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Update an existing row. Shorthand for `updateWhere()` with the id column set as the where values."
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "update",
                        "body": "return $this->updateWhere($table, $newRowValues, [$idColumnName=>$newRowValues[$idColumnName]]);",
                        "declaration": "public function update(string $table, array $newRowValues, string $idColumnName='id')"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "table",
                                "declaration": "string $table"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "newRowValues",
                                "declaration": "array $newRowValues"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "whereVals",
                                "declaration": "array $whereVals"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "param",
                                    "description": "$whereVals To update ALL rows, pass `[]`"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "updateWhere",
                        "body": "$valueBinds = [];\n$setSql = [];\nforeach ($newRowValues as $col=>$value){\n    $valueBinds[$bindKey=':'.$col.'_value'] = $value;\n    $setSql[] = \"`$col` = $bindKey\";\n}\n$setSql = implode(\",\\n\", $setSql);\n$whereSql = static::whereSqlFromCols($whereVals);\nif (strlen(trim($whereSql))>0)$whereSql = \"WHERE\\n${whereSql}\";\n$sql = <<<SQL\n    UPDATE `${table}`\n    SET $setSql\n    ${whereSql}\nSQL;\n$binds = array_merge($valueBinds, $whereVals);\n$binds  = static::keysToBinds($binds);\n$this->execute($sql,$binds);",
                        "declaration": "public function updateWhere(string $table, array $newRowValues, array $whereVals)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "table",
                                "declaration": "string $table"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "whereCols",
                                "declaration": "array $whereCols"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Delete rows from a table",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "true if any rows were deleted. false otherwise"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "delete",
                        "body": "$sql = static::whereSqlFromCols($whereCols);\nif ($sql!=null)$sql = 'WHERE '.$sql;\n$sql = \"DELETE FROM `${table}` ${sql}\";\n$stmt = $this->execute($sql, $whereCols);\n\/\/ var_dump($stmt->errorCode());\n\/\/ exit;\n\/\/ var_dump($stmt->rowCount());\n\/\/ exit;\nif ($stmt->errorCode()=='00000'\n    &&$stmt->rowCount()>0)return true;\nreturn false;\n\/\/ return $stmt;",
                        "declaration": "public function delete(string $table, array $whereCols)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "sql",
                                "declaration": "string $sql"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "binds",
                                "value": "[]",
                                "declaration": "array $binds=[]"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Execute an Sql statement & get a PDOStatement back",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "throws",
                                    "description": "if the statement fails to prepare"
                                },
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "PDOStatement"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "execute",
                        "body": "$pdo = $this->pdo;\n$stmt = $pdo->prepare($sql);\nif ($stmt===false){\n    $error = var_export($pdo->errorInfo(),true);\n    throw new \\Exception(\"Sql problem: \\n\".$error.\"\\n\\n\");\n}\n$stmt->execute($binds);\nreturn $stmt;",
                        "declaration": "public function execute(string $sql, array $binds=[])"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "string"
                                ],
                                "name": "sql",
                                "declaration": "string $sql"
                            },
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "binds",
                                "value": "[]",
                                "declaration": "array $binds=[]"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Alias for `execute()`",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "PDOStatement"
                                }
                            ]
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "exec",
                        "body": "return $this->execute($sql, $binds);",
                        "declaration": "public function exec(string $sql, array $binds=[])"
                    },
                    {
                        "type": "method",
                        "args": [],
                        "docblock": {
                            "type": "docblock",
                            "description": "get the pdo object"
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "getPdo",
                        "body": "return $this->pdo;",
                        "declaration": "public function getPdo()"
                    },
                    {
                        "type": "method",
                        "args": [],
                        "docblock": {
                            "type": "docblock",
                            "description": "get the pdo object"
                        },
                        "modifiers": [
                            "public"
                        ],
                        "name": "pdo",
                        "body": "return $this->pdo;",
                        "declaration": "public function pdo()"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "columns",
                                "declaration": "array $columns"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Convert key=>value array into a 'WHERE' sql.\n",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "param",
                                    "description": "$columns `['key'=>$val, ':key2'=>$val]`. `$val` can be string, array, or numeric."
                                },
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "string sql for a WHERE statement. Does not include `WHERE`"
                                },
                                {
                                    "type": "attribute",
                                    "name": "exampleOutput",
                                    "description": ": `key = :val1 AND key2 LIKE :val2, AND key3 IN (:val3_1,:val3_2)`."
                                }
                            ]
                        },
                        "modifiers": [
                            "static",
                            "public"
                        ],
                        "name": "whereSqlFromCols",
                        "body": "$binds = static::keysToBinds($columns);\n\/\/generate sql\n$pieces = [];\n$copy = $binds;\nforeach ($copy as $k=>$v){\n    $col = substr($k,1);\n    if (is_string($v)){\n        $pieces[] = \"`$col` LIKE $k\";\n    } else if (is_array($v)){\n        unset($binds[$k]);\n        $inList = [];\n        foreach ($v as $index=>$inValue){\n            $inKey = $k.$index;\n            $binds[$inKey] = $inValue;\n            $inList[] = $inKey;\n        }\n        $pieces[] = \"`$col` IN (\".implode(', ',$inList).\")\";\n    } else {\n        $pieces[] = \"`$col` = $k\";\n    }\n}\n$sql = implode(' AND ', $pieces);\nreturn $sql;",
                        "declaration": "static public function whereSqlFromCols(array $columns)"
                    },
                    {
                        "type": "method",
                        "args": [
                            {
                                "type": "arg",
                                "arg_types": [
                                    "array"
                                ],
                                "name": "keyedValues",
                                "declaration": "array $keyedValues"
                            }
                        ],
                        "docblock": {
                            "type": "docblock",
                            "description": "Convert an array `['key'=>$val, ':key2'=>$val]` into binds: `[':key'=>$val, ':key2'=>$val]`.\n",
                            "attribute": [
                                {
                                    "type": "attribute",
                                    "name": "return",
                                    "description": "array where keys are prefixed with a colon (:)"
                                }
                            ]
                        },
                        "modifiers": [
                            "static",
                            "public"
                        ],
                        "name": "keysToBinds",
                        "body": "$binds = [];\nforeach ($keyedValues as $k=>$v){\n    if (!is_string($k)){\n        $binds[] = $v;\n    } else if (substr($k,0,1)==':'){\n        $binds[$k] = $v;\n    } else {\n        $binds[':'.$k] = $v;\n    }\n}\nreturn $binds;",
                        "declaration": "static public function keysToBinds(array $keyedValues)"
                    }
                ]
            }
        ]
    }
}