PHP jDb类代码示例

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $group = $input->getArgument('group');
     $labelkey = $input->getArgument('labelkey');
     $label = $input->getArgument('label');
     $cnx = \jDb::getConnection('jacl2_profile');
     $sql = "SELECT id_aclsbjgrp FROM " . $cnx->prefixTable('jacl2_subject_group') . " WHERE id_aclsbjgrp=" . $cnx->quote($group);
     $rs = $cnx->query($sql);
     if ($rs->fetch()) {
         throw new \Exception("This subject group already exists");
     }
     $sql = "INSERT into " . $cnx->prefixTable('jacl2_subject_group') . " (id_aclsbjgrp, label_key) VALUES (";
     $sql .= $cnx->quote($group) . ',';
     $sql .= $cnx->quote($labelkey);
     $sql .= ')';
     $cnx->exec($sql);
     if ($this->verbose()) {
         $ouput->writeln("Rights: group of subjects '" . $group . "' is created");
     }
     if ($label && preg_match("/^([a-zA-Z0-9_\\.]+)~([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_\\.]+)\$/", $labelkey, $m)) {
         $localestring = "\n" . $m[3] . '=' . $label;
         $path = $this->getModulePath($m[1]);
         $file = $path . 'locales/' . \jApp::config()->locale . '/' . $m[2] . '.' . \jApp::config()->charset . '.properties';
         if (file_exists($file)) {
             $localestring = file_get_contents($file) . $localestring;
         }
         file_put_contents($file, $localestring);
         if ($output->isVerbose()) {
             $output->writeln("locale string " . $m[3] . " is created into " . $file);
         }
     }
 }

 /**
  * action to process the page after the submit
  * @return 0
  */
 function process()
 {
     $errors = array();
     $login = $_SESSION['adminaccount']['login'] = trim($_POST['login']);
     if ($login == '') {
         $errors[] = $this->locales['error.missing.login'];
     }
     $password = $_SESSION['adminaccount']['password'] = trim($_POST['password']);
     if ($password == '') {
         $errors[] = $this->locales['error.missing.password'];
     }
     $passwordconf = $_SESSION['adminaccount']['password_confirm'] = trim($_POST['password_confirm']);
     if ($password != $passwordconf) {
         $errors[] = $this->locales['error.confirm.password'];
     }
     $email = $_SESSION['adminaccount']['email'] = trim($_POST['email']);
     if ($email == '') {
         $errors[] = $this->locales['error.missing.email'];
     }
     if (count($errors)) {
         $_SESSION['adminaccount']['errors'] = $errors;
         return false;
     }
     jApp::loadConfig('havefnubb/config.ini.php');
     $db = jDb::getConnection();
     $db->exec('INSERT INTO ' . $db->encloseName($db->prefixTable('community_users')) . ' (login, password, email, nickname, status, create_date) VALUES (' . $db->quote($login) . ',' . $db->quote(md5($password)) . ',' . $db->quote($email) . ',' . $db->quote($login) . ',1,' . "'" . date('Y-m-d H:i:s') . "')");
     $idu = $db->lastInsertId();
     $db->exec('INSERT INTO ' . $db->encloseName($db->prefixTable('jacl2_group')) . ' (id_aclgrp, name, grouptype, ownerlogin) ' . 'VALUES (' . $db->quote('__priv_' . $login) . ',' . $db->quote($login) . ',2,' . $db->quote($login) . ')');
     $db->exec('INSERT INTO ' . $db->encloseName($db->prefixTable('jacl2_user_group')) . ' (login, id_aclgrp) VALUES (' . $db->quote($login) . ',\'admins\')');
     $db->exec('INSERT INTO ' . $db->encloseName($db->prefixTable('jacl2_user_group')) . ' (login, id_aclgrp) VALUES (' . $db->quote($login) . ',' . $db->quote('__priv_' . $login) . ')');
     unset($_SESSION['adminaccount']);
     return 0;
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $group = $input->getArgument('group');
     $name = $input->getArgument('name');
     $isDefault = $input->getOption('default');
     if (!$name) {
         $name = $group;
     }
     $cnx = \jDb::getConnection('jacl2_profile');
     try {
         $sql = "INSERT into " . $cnx->prefixTable('jacl2_group') . " (id_aclgrp, name, grouptype, ownerlogin) VALUES (";
         $sql .= $cnx->quote($group) . ',';
         $sql .= $cnx->quote($name) . ',';
         if ($isDefault) {
             $sql .= '1, NULL)';
         } else {
             $sql .= '0, NULL)';
         }
         $cnx->exec($sql);
     } catch (\Exception $e) {
         throw new \Exception("this group already exists");
     }
     if ($output->isVerbose()) {
         $output->writeln("Group '" . $group . "' is created");
     }
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $subject = $input->getArgument('subject');
     $cnx = \jDb::getConnection('jacl2_profile');
     if (!$input->getOption('confirm')) {
         $helper = $this->getHelper('question');
         $question = new ConfirmationQuestion('are you sure you want to delete subject ' . $subject . ' (y/N)?', false);
         if (!$helper->ask($input, $output, $question)) {
             $output->writeln('command canceled');
             return;
         }
     }
     $sql = "SELECT id_aclsbj FROM " . $cnx->prefixTable('jacl2_subject') . " WHERE id_aclsbj=" . $cnx->quote($subject);
     $rs = $cnx->query($sql);
     if (!$rs->fetch()) {
         throw new \Exception("This subject does not exist");
     }
     $sql = "DELETE FROM " . $cnx->prefixTable('jacl2_rights') . " WHERE id_aclsbj=";
     $sql .= $cnx->quote($subject);
     $cnx->exec($sql);
     $sql = "DELETE FROM " . $cnx->prefixTable('jacl2_subject') . " WHERE id_aclsbj=";
     $sql .= $cnx->quote($subject);
     $cnx->exec($sql);
     if ($output->isVerbose()) {
         $output->writeln("Rights: subject " . $subject . " is deleted");
     }
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $cnx = \jDb::getConnection('jacl2_profile');
     $table = new Table($output);
     $groupFiler = false;
     if ($input->getArgument('group')) {
         $id = $this->_getGrpId($input, true);
         $sql = "SELECT login FROM " . $cnx->prefixTable('jacl2_user_group') . " WHERE id_aclgrp =" . $cnx->quote($id);
         $table->setHeaders(array('Login'));
         $groupFiler = true;
     } else {
         $sql = "SELECT login, g.id_aclgrp, name FROM " . $cnx->prefixTable('jacl2_user_group') . " AS u " . " LEFT JOIN " . $cnx->prefixTable('jacl2_group') . " AS g\n                ON (u.id_aclgrp = g.id_aclgrp AND g.grouptype < 2)\n                ORDER BY login";
         $table->setHeaders(array('Login', 'group', 'group id'));
     }
     $cnx = \jDb::getConnection('jacl2_profile');
     $rs = $cnx->query($sql);
     foreach ($rs as $rec) {
         if ($groupFiler) {
             $table->addRow(array($rec->login));
         } else {
             $table->addRow(array($rec->login, $rec->name, $rec->id_aclgrp));
         }
     }
     $table->render();
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $cnx = \jDb::getConnection('jacl2_profile');
     $group = $cnx->quote($this->_getGrpId($input));
     $subject = $cnx->quote($input->getArgument('subject'));
     $resource = $cnx->quote($input->getArgument('resource'));
     $sql = "SELECT * FROM " . $cnx->prefixTable('jacl2_rights') . "\n                WHERE id_aclgrp=" . $group . "\n                AND id_aclsbj=" . $subject . "\n                AND id_aclres=" . $resource;
     $rs = $cnx->query($sql);
     if ($rs->fetch()) {
         throw new \Exception("right already set");
     }
     $sql = "SELECT * FROM " . $cnx->prefixTable('jacl2_subject') . " WHERE id_aclsbj=" . $subject;
     $rs = $cnx->query($sql);
     if (!($sbj = $rs->fetch())) {
         throw new \Exception("subject is unknown");
     }
     $sql = "INSERT into " . $cnx->prefixTable('jacl2_rights') . " (id_aclgrp, id_aclsbj, id_aclres) VALUES (";
     $sql .= $group . ',';
     $sql .= $subject . ',';
     $sql .= $resource . ')';
     $cnx->exec($sql);
     if ($output->isVerbose()) {
         $output->writeln("Right is added on subject {$subject} with group {$group} and resource {$resource}");
     }
 }

 /**
  * get the Rate of a given source and ID
  * @param integer $id_source the id to link to the source
  * @param string $source the linked source
  * @return integer $total the global rate
  */
 function getTotalRatesBySource($id_source, $source)
 {
     $cnx = jDb::getConnection();
     $strQuery = 'SELECT COUNT(*) as total_rates, SUM(level) as total_level, AVG(level) as avg_level ' . ' FROM ' . $cnx->prefixTable('hfnu_rates') . " WHERE id_source = '" . $id_source . "' AND source='" . addslashes($source) . "' GROUP BY id_source";
     $rs = $cnx->query($strQuery);
     $total = $rs->fetch();
     return $total;
 }

 /**
  *
  */
 function index()
 {
     $rep = $this->getResponse('html');
     $groups = array();
     $o = new StdClass();
     $o->id_aclgrp = '-2';
     $o->name = jLocale::get('jacl2_admin~acl2.all.users.option');
     $o->grouptype = 0;
     $groups[] = $o;
     $o = new StdClass();
     $o->id_aclgrp = '-1';
     $o->name = jLocale::get('jacl2_admin~acl2.without.groups.option');
     $o->grouptype = 0;
     $groups[] = $o;
     foreach (jAcl2DbUserGroup::getGroupList() as $grp) {
         $groups[] = $grp;
     }
     $listPageSize = 15;
     $offset = $this->intParam('idx', 0, true);
     $grpid = $this->intParam('grpid', -2, true);
     $p = jAcl2Db::getProfil();
     if ($grpid == -2) {
         //all users
         $dao = jDao::get('jelix~jacl2groupsofuser', $p);
         $cond = jDao::createConditions();
         $cond->addCondition('grouptype', '=', 2);
         $rs = $dao->findBy($cond, $offset, $listPageSize);
         $usersCount = $dao->countBy($cond);
     } elseif ($grpid == -1) {
         //only those who have no groups
         $sql = 'SELECT login, count(id_aclgrp) as nbgrp FROM jacl2_user_group 
                 GROUP BY login HAVING nbgrp < 2 ORDER BY login';
         $cnx = jDb::getConnection($p);
         $rs = $cnx->query($sql);
         $usersCount = -1;
     } else {
         //in a specific group
         $dao = jDao::get('jelix~jacl2usergroup', $p);
         $rs = $dao->getUsersGroupLimit($grpid, $offset, $listPageSize);
         $usersCount = $dao->getUsersGroupCount($grpid);
     }
     $users = array();
     $dao2 = jDao::get('jelix~jacl2groupsofuser', $p);
     foreach ($rs as $u) {
         $u->groups = array();
         $gl = $dao2->getGroupsUser($u->login);
         foreach ($gl as $g) {
             if ($g->grouptype != 2) {
                 $u->groups[] = $g;
             }
         }
         $users[] = $u;
     }
     $tpl = new jTpl();
     $tpl->assign(compact('offset', 'grpid', 'listPageSize', 'groups', 'users', 'usersCount'));
     $rep->body->assign('MAIN', $tpl->fetch('users_list'));
     return $rep;
 }

 public function run()
 {
     $this->loadAppConfig();
     $module = $this->_parameters['module'];
     $path = $this->getModulePath($module);
     $filename = $path . 'daos/';
     $this->createDir($filename);
     $daofile = strtolower($this->_parameters['name']) . '.dao.xml';
     $filename .= $daofile;
     $profile = $this->getOption('-profile');
     $param = array('name' => $this->_parameters['name'], 'table' => $this->getParam('table'));
     if ($param['table'] == null) {
         $param['table'] = $param['name'];
     }
     if ($this->getOption('-empty')) {
         $this->createFile($filename, 'module/dao_empty.xml.tpl', $param, "Empty DAO");
     } else {
         $sequence = $this->getParam('sequence', '');
         $tools = jDb::getConnection($profile)->tools();
         $fields = $tools->getFieldList($param['table'], $sequence);
         $properties = '';
         $primarykeys = '';
         foreach ($fields as $fieldname => $prop) {
             $name = str_replace('-', '_', $fieldname);
             $properties .= "\n        <property name=\"{$name}\" fieldname=\"{$fieldname}\"";
             $properties .= ' datatype="' . $prop->type . '"';
             if ($prop->primary) {
                 if ($primarykeys != '') {
                     $primarykeys .= ',' . $fieldname;
                 } else {
                     $primarykeys .= $fieldname;
                 }
             }
             if ($prop->notNull && !$prop->autoIncrement) {
                 $properties .= ' required="true"';
             }
             if ($prop->autoIncrement) {
                 $properties .= ' autoincrement="true"';
             }
             if ($prop->hasDefault) {
                 $properties .= ' default="' . htmlspecialchars($prop->default) . '"';
             }
             if ($prop->length) {
                 $properties .= ' maxlength="' . $prop->length . '"';
             }
             if ($prop->sequence) {
                 $properties .= ' sequence="' . $prop->sequence . '"';
             }
             $properties .= '/>';
         }
         if ($primarykeys == '') {
             throw new Exception("The table " . $param['table'] . " has no primary keys. A dao needs a primary key on the table to be defined.");
         }
         $param['properties'] = $properties;
         $param['primarykeys'] = $primarykeys;
         $this->createFile($filename, 'module/dao.xml.tpl', $param, "DAO");
     }
 }

 protected function _connect()
 {
     if (!isset($this->_profile['table']) || !isset($this->_profile['dbprofile'])) {
         throw new Exception("table and dbprofile is missing for the db kvdb driver");
     }
     $this->table = $this->_profile['table'];
     $cnx = jDb::getConnection($this->_profile['dbprofile']);
     return $cnx;
 }

 function testTableList()
 {
     $db = jDb::getConnection();
     $tools = new testmysqlDbTools($db);
     $goodList = array('jacl_group', 'jacl_right_values', 'jacl_right_values_group', 'jacl_rights', 'jacl_subject', 'jacl_user_group', 'jacl2_group', 'jacl2_user_group', 'jacl2_subject', 'jacl2_subject_group', 'jacl2_rights', 'jlx_user', 'myconfig', 'product_test', 'product_tags_test', 'labels_test', 'labels1_test', 'products', 'jlx_cache', 'jsessions', 'testkvdb');
     $list = $tools->getTableList();
     sort($goodList);
     sort($list);
     $this->assertEqual($list, $goodList);
 }

 /**
  * import a sql script into the given profile.
  *
  * The name of the script should be store in install/sql/$name.databasetype.sql
  * in the directory of the component. (replace databasetype by mysql, pgsql etc.)
  * 
  * @param string $name the name of the script, without suffixes
  */
 public function execSQLScript($name, $profile = '')
 {
     $tools = jDb::getTools($profile);
     $p = jDb::getProfile($profile);
     $driver = $p['driver'];
     if ($driver == 'pdo') {
         preg_match('/^(\\w+)\\:.*$/', $p['dsn'], $m);
         $driver = $m[1];
     }
     $tools->execSQLScript($this->path . 'install/sql/' . $name . '.' . $driver . '.sql');
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $group = $input->getArgument('group');
     $login = $input->getArgument('login');
     $cnx = \jDb::getConnection('jacl2_profile');
     $groupid = $this->_getGrpId($input);
     $sql = "DELETE FROM " . $cnx->prefixTable('jacl2_user_group') . " WHERE login=" . $cnx->quote($login) . " AND id_aclgrp=" . $cnx->quote($groupid);
     $cnx->exec($sql);
     if ($output->isVerbose()) {
         $output->writeln("User '" . $login . "' is removed from group '" . $group . "'");
     }
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $table = new Table($output);
     $table->setHeaders(array('id', 'label key'));
     $cnx = \jDb::getConnection('jacl2_profile');
     $sql = "SELECT id_aclsbjgrp, label_key FROM " . $cnx->prefixTable('jacl2_subject_group') . " ORDER BY id_aclsbjgrp";
     $rs = $cnx->query($sql);
     foreach ($rs as $rec) {
         $table->addRow(array($rec->id_aclsbjgrp, $rec->label_key));
     }
     $table->render();
 }

 protected function _execute(InputInterface $input, OutputInterface $output)
 {
     $group = $input->getArgument('group');
     $def = $input->getOption('no-default') ? 0 : 1;
     $id = $this->_getGrpId($input, true);
     $cnx = \jDb::getConnection('jacl2_profile');
     $sql = "UPDATE " . $cnx->prefixTable('jacl2_group') . " SET grouptype={$def}  WHERE id_aclgrp=" . $cnx->quote($id);
     $cnx->exec($sql);
     if ($output->isVerbose()) {
         $output->writeln("Group '" . $group . "' is " . ($def ? ' now a default group' : ' no more a default group'));
     }
 }