public static function prepareEnvironment(ConnectionManagement $connection)
{
$database = $connection->getDatabase();
$newConnection = new ConnectionManagement(str_replace("/{$database}", "/", $connection->getDbConnectionString()));
$dbDataset = new DBDataset($newConnection->getDbConnectionString());
$dbDataset->execSQL("CREATE SCHEMA IF NOT EXISTS `{$database}` DEFAULT CHARACTER SET utf8 ;");
}
/**
* Remove a specific site from all users
* Return True or false
*
* @param string $propertyName Property name
* @param string $value Property value with a site
* @return bool
* */
public function removeAllProperties($propertyName, $value)
{
$param = array();
$param["name"] = $propertyName;
$param["value"] = $value;
$sql = $this->_sqlHelper->createSafeSQL($this->sqlRemoveAllProperties(), array("@@Table" => $this->getCustomTable()->table, "@@Name" => $this->getCustomTable()->name, "@@Value" => $this->getCustomTable()->value));
$this->_db->execSQL($sql, $param);
}
public function setUp()
{
$this->prefix = "";
$db = new DBDataset('sqlite:///tmp/teste.db');
$db->execSQL('create table users (
userid integer primary key autoincrement,
name varchar(45),
email varchar(200),
username varchar(20),
password varchar(40),
created datetime,
admin char(1));');
$db->execSQL('create table users_property (
customid integer primary key autoincrement,
userid integer,
name varchar(45),
value varchar(45));');
$this->object = new UsersDBDataset('sqlite:///tmp/teste.db');
$this->object->addUser('User 1', 'user1', '*****@*****.**', 'pwd1');
$this->object->addUser('User 2', 'user2', '*****@*****.**', 'pwd2');
$this->object->addUser('User 3', 'user3', '*****@*****.**', 'pwd3');
}
/**
* @param SQLType $sqlType
*/
protected function ExecuteSQL($sqlType)
{
$fieldList = array();
if ($sqlType != SQLType::SQL_DELETE) {
// Get a SingleRow with all field values
$anyCurInfo = new AnyDataset();
$anyCurInfo->appendRow();
foreach ($this->_fields as $field) {
$anyCurInfo->addField($field->fieldName, $this->_context->get($field->fieldName));
}
$itCurInfo = $anyCurInfo->getIterator();
$srCurInfo = $itCurInfo->moveNext();
// Format and Adjust all field values
foreach ($this->_fields as $field) {
if ($field->editable) {
$value = $this->preProcessValue($field->fieldName, $field->dataType, $this->_context->get($field->fieldName));
if ($field->fieldXmlInput == XmlInputObjectType::FILEUPLOAD) {
$files = $this->_context->getUploadFileNames();
if ($files[$field->fieldName] == "") {
continue;
}
// Do nothing if none files are uploaded.
}
if ($field->beforeInsertFormatter != null) {
$value = $field->beforeInsertFormatter->Format($srCurInfo, $field->fieldName, $value);
}
$fieldList[$field->fieldName] = array(SQLFieldType::TEXT, $value);
}
}
}
$param = array();
if ($sqlType != SQLType::SQL_INSERT) {
$filter = $this->getWhereClause($param);
} else {
$filter = "";
}
$helper = new SQLHelper($this->_dbData);
$helper->setFieldDelimeters($this->getFieldDeliLeft(), $this->getFieldDeliRight());
$sql = $helper->generateSQL($this->_table, $fieldList, $param, $sqlType, $filter, $this->_decimalSeparator);
$this->DebugInfo($sql, $param);
$this->_dbData->execSQL($sql, $param);
}
/**
*
* @param DBDataset $dbdataset
* @param string $sql
* @param array $param
* @param string $sequence
* @return int
*/
function executeAndGetInsertedId($dbdataset, $sql, $param, $sequence = null)
{
$dbdataset->execSQL($sql, $param);
return -1;
}
/**
* Process Vote. Note that the system ONLY process the vote if there is no another equal IP.
*
* @param int $width
* @param int $height
*/
public function processVote($width = 450, $height = 400)
{
if ($this->_context->get("xcrt") == "") {
// Is The Post values needed to process vote exists?
if ($this->_context->get("xmlnuke_poll") != "" && $this->_context->get("xmlnuke_polllang") != "" && $this->_context->get("xmlnuke_pollanswer") != "") {
$this->_poll = $this->_context->get("xmlnuke_poll");
$this->_lang = $this->_context->get("xmlnuke_polllang");
$ok = true;
// Check if IP already voted -> Freeze IP for 5 days.
if ($this->_isdb) {
// Remove Old Entries
$dbdata = new DBDataset($this->_connection);
$sql = "delete from :table where register < now() - interval 5 day ";
$sql = \ByJG\AnyDataset\Database\SQLHelper::createSafeSQL($sql, array(':table' => $this->_tbllastip));
$dbdata->execSQL($sql);
// Check if exists
$sql = "select count(1) from :table where ip = [[ip]] and name = [[name]] ";
$sql = \ByJG\AnyDataset\Database\SQLHelper::createSafeSQL($sql, array(':table' => $this->_tbllastip));
$param = array("ip" => $this->_context->getClientIp(), "name" => $this->_poll);
$count = $dbdata->getScalar($sql, $param);
$ok = false;
if ($count == 0) {
$ok = true;
$sql = "insert into :table (ip, name, register) values ([[ip]], [[name]], now()) ";
$sql = \ByJG\AnyDataset\Database\SQLHelper::createSafeSQL($sql, array(':table' => $this->_tbllastip));
$param = array("ip" => $this->_context->getClientIp(), "name" => $this->_poll);
try {
$dbdata->execSQL($sql, $param);
} catch (\PDOException $ex) {
$ok = false;
}
}
}
// Is My IP Unique? If true I can process the vote.
// Note if the poll name, lang and code are wrong the system does not do anything.
if ($ok) {
// Get Data
$itf = new IteratorFilter();
$itf->addRelation("name", Relation::EQUAL, $this->_poll);
$itf->addRelation("lang", Relation::EQUAL, $this->_lang);
$itf->addRelation("code", Relation::EQUAL, $this->_context->get("xmlnuke_pollanswer"));
if ($this->_isdb) {
$dbdata = new DBDataset($this->_connection);
$param = array();
$sql = "update :table set votes = IFNULL(votes,0) + 1 where :filter ";
$sql = \ByJG\AnyDataset\Database\SQLHelper::createSafeSQL($sql, array(':table' => $this->_tblanswer, ':filter' => $itf->getFilter(IteratorFilter::SQL, $param)));
$dbdata->execSQL($sql, $param);
} else {
$this->getAnyData();
$itAnswer = $this->_anyAnswer->getIterator($itf);
if ($itAnswer->hasNext()) {
$sr = $itAnswer->moveNext();
$sr->setField("votes", intval($sr->getField("votes")) + 1);
$this->_anyAnswer->Save();
}
}
}
$this->_processed = true;
}
} else {
$this->_processed = true;
}
$this->_width = $width;
$this->_height = $height;
}
/**
* Edit DataBase
*
*/
protected function actionEditDB()
{
$blockCenter = new XmlBlockCollection($this->_myWords->Value("DATABASE"), BlockPosition::Center);
$breakline = new XmlnukeBreakLine();
$paragraph = new XmlParagraphCollection();
$paragraph->addXmlnukeObject(new XmlnukeText($this->_myWords->Value("DATABASETEXT")));
$paragraph->addXmlnukeObject($breakline);
$secop = $this->_context->get("secop");
// Menu
$form = new XmlFormCollection($this->_context, $this->_url . "?op=5", "Menu");
$optionlist = array();
$optionlist[""] = "-- Selecione --";
$optionlist["setup"] = "Configurar a Conexão";
$optionlist["test"] = "Testar a conexão";
$optionlist["create"] = "Create Sample Table";
$optionlist["edit"] = "Edit Sample Table";
$list = new XmlEasyList(EasyListType::SELECTLIST, "secop", "Selecione a Ação", $optionlist, $secop);
$form->addXmlnukeObject($list);
$btnmenu = new XmlInputButtons();
$btnmenu->addSubmit("Selecionar");
$form->addXmlnukeObject($btnmenu);
$blockCenter->addXmlnukeObject($form);
// Opções:
switch ($secop) {
case "setup":
$formsetup = new XmlFormCollection($this->_context, $this->_url . "?op=5", "Editar Conexão");
$formsetup->addXmlnukeObject(new XmlInputHidden("secop", "setupconf"));
$text = new XmlInputTextBox("Connection String", "connection", "adodriver://*****:*****@server/datasource");
$text->setRequired(true);
$formsetup->addXmlnukeObject($text);
$btn = new XmlInputButtons();
$btn->addSubmit("Salvar");
$formsetup->addXmlnukeObject($btn);
$blockCenter->addXmlnukeObject($formsetup);
break;
case "setupconf":
$filename = new AnydatasetFilenameProcessor("_db");
$anydata = new AnyDataset($filename->FullQualifiedNameAndPath());
$itf = new IteratorFilter();
$itf->addRelation("dbname", Relation::EQUAL, "sampledb");
$it = $anydata->getIterator($itf);
if ($it->hasNext()) {
$sr = $it->moveNext();
$sr->setField("dbtype", "dsn");
$sr->setField("dbconnectionstring", $this->_context->get("connection"));
} else {
$anydata->appendRow();
$anydata->addField("dbname", "sampledb");
$anydata->addField("dbtype", "dsn");
$anydata->addField("dbconnectionstring", $this->_context->get("connection"));
}
$anydata->Save();
$paragraph->addXmlnukeObject(new XmlnukeText("Updated!", true));
break;
case "test":
$db = new DBDataset("sampledb");
$db->TestConnection();
$paragraph->addXmlnukeObject(new XmlnukeText("I suppose it is fine the connection string!", true));
break;
case "create":
$db = new DBDataset("sampledb");
$sql = "create table sample (fieldkey integer, fieldname varchar(20))";
$db->execSQL($sql);
$db->TestConnection();
$paragraph->addXmlnukeObject(new XmlnukeText("Table Created!", true));
break;
case "edit":
// Cria um acesso a $crud
$pageFields = new CrudFieldCollection();
$fieldPage = new CrudField();
$fieldPage->fieldName = "fieldkey";
$fieldPage->key = true;
$fieldPage->dataType = INPUTTYPE::NUMBER;
$fieldPage->fieldCaption = "Código";
$fieldPage->fieldXmlInput = XmlInputObjectType::TEXTBOX;
$fieldPage->visibleInList = true;
$fieldPage->editable = true;
$fieldPage->required = true;
$fieldPage->rangeMin = "100";
$fieldPage->rangeMax = "999";
$pageFields->addCrudField($fieldPage);
$fieldPage = new CrudField();
$fieldPage->fieldName = "fieldname";
$fieldPage->key = false;
$fieldPage->dataType = INPUTTYPE::TEXT;
$fieldPage->fieldCaption = "Name";
$fieldPage->fieldXmlInput = XmlInputObjectType::TEXTBOX;
$fieldPage->visibleInList = true;
$fieldPage->editable = true;
$fieldPage->required = true;
$fieldPage->maxLength = 20;
$pageFields->addCrudField($fieldPage);
$crud = new XmlnukeCrudDB($this->_context, $pageFields, "Edição teste usando Banco de Dados", $this->_url . "?op=5", null, "sample", "sampledb");
$crud->setPageSize(3, 0);
$crud->addParameter("secop", "edit");
$paragraph->addXmlnukeObject($crud);
break;
}
$blockCenter->addXmlnukeObject($paragraph);
$this->_document->addXmlnukeObject($blockCenter);
}
