leeonell Posted April 20, 2016 Report Share Posted April 20, 2016 Hello, Just by curiosity, maybe a silly question, ketarin is open source but the online database server not? If I want my self-hosted database server to synchronise specific config there is no source for this part ? Instead to exchange xml file between computers. thanks for your lights. Link to comment Share on other sites More sharing options...
Etz Posted April 22, 2016 Report Share Posted April 22, 2016 You could use Network share or cloud service for synchronization, there is no need for Database server. Link to comment Share on other sites More sharing options...
leeonell Posted April 23, 2016 Author Report Share Posted April 23, 2016 Thanks Etz, you are right it's enough for simple usage. It was also to know if the server part is or can be open source like the Client. Link to comment Share on other sites More sharing options...
floele Posted April 24, 2016 Report Share Posted April 24, 2016 It's not open source at this point, but I could provide the source if anyone is interested. Link to comment Share on other sites More sharing options...
leeonell Posted May 1, 2016 Author Report Share Posted May 1, 2016 It's not open source at this point, but I could provide the source if anyone is interested. Hello floele, Cool personally, I'm interested by the source. How do you want provide it ? under what conditions ? Link to comment Share on other sites More sharing options...
floele Posted May 1, 2016 Report Share Posted May 1, 2016 span { font-family: 'Courier New'; font-size: 10pt; color: #000000; } .sc18 { color: #FF0000; } .sc118 { } .sc119 { color: #808080; } .sc120 { color: #808080; } .sc121 { color: #0000FF; } .sc122 { color: #FF8000; } .sc123 { color: #000080; } .sc124 { color: #008000; } .sc125 { color: #008000; } .sc127 { color: #8000FF; } <?php use Doctrine\Common\ClassLoader; use Doctrine\DBAL\DriverManager; require_once 'XML/RPC2/Server.php'; require_once 'XML/RPC2/Server/Input/PhpInput.php'; require 'Doctrine/Common/ClassLoader.php'; // RPC server does not emit useful error messages // (on purpose) if this option is not set. ini_set('display_errors', 1); // Set up database connection $classLoader = new ClassLoader('Doctrine'); $classLoader->register(); $config = new \Doctrine\DBAL\Configuration(); $connectionParams = array( 'dbname' => '...', 'user' => '...', 'password' => '...', 'host' => 'localhost', 'driver' => 'pdo_mysql', 'unix_socket' => '/var/run/mysql/mysql.sock' ); try { $conn = DriverManager::getConnection($connectionParams); } catch(Exception $e) { die("Error connecting to the database!!!"); } // Utility class class Url { /** * Check if an url exists * * @param string $url * @access static * @return bool True if the url is accessible and false if the url is unaccessible or does not exist * @throws Exception An exception will be thrown when Curl session fails to start */ public static function exists($url) { $handle = curl_init($url); if (false === $handle) { throw new Exception('Fail to start Curl session'); } curl_setopt($handle, CURLOPT_HEADER, true); curl_setopt($handle, CURLOPT_NOBODY, true); curl_setopt($handle, CURLOPT_RETURNTRANSFER, true); curl_exec($handle); $result = (curl_getinfo($handle, CURLINFO_HTTP_CODE) == '200'); // close Curl resource, and free up system resources curl_close($handle); return $result; } } // Do the fancy XML stuff class KetarinRpc { /** * Returns a list of the most often downloaded * applications. The number of apps is currently 50. * * @return array of (applicationname, updatedat, shareid, usecount) */ public static function GetMostDownloadedApplications() { global $conn; $result = $conn->fetchAll('SELECT ApplicationName, UpdatedAt, ShareId, DownloadCount FROM ApplicationJobs ORDER BY DownloadCount DESC LIMIT 50'); for ($i = 0; $i < count($result); $i++) { $result[$i]['UpdatedAt'] = (int)$result[$i]['UpdatedAt']; $result[$i]['ShareId'] = (int)$result[$i]['ShareId']; $result[$i]['UseCount'] = (int)$result[$i]['DownloadCount']; } return $result; } /** * Gets all applications matching a * search criteria (application name or GUID). * * @param string searchSubject * @return array of (applicationname, updatedat, shareid, downloadcount) */ public static function GetApplications($searchSubject = '') { global $conn; if ($searchSubject) { $result = $conn->fetchAll('SELECT ApplicationName, UpdatedAt, ShareId, DownloadCount FROM ApplicationJobs WHERE ApplicationName LIKE ? OR ApplicationGuid = ? ORDER BY ApplicationName', array('%' . $searchSubject . '%', $searchSubject)); } else { $result = $conn->fetchAll('SELECT ApplicationName, UpdatedAt, ShareId, DownloadCount FROM ApplicationJobs ORDER BY ApplicationName'); } for ($i = 0; $i < count($result); $i++) { $result[$i]['UpdatedAt'] = (int)$result[$i]['UpdatedAt']; $result[$i]['ShareId'] = (int)$result[$i]['ShareId']; $result[$i]['UseCount'] = (int)$result[$i]['DownloadCount']; } return $result; } /** * Gets all applications matching a * search criteria (application name) * except for the application given in the * second parameter. * * @param string searchSubject * @param string GUID of the application to ignore * @return array */ public static function GetSimilarApplications($searchSubject = '', $appGuid) { global $conn; $appGuid = '{' . trim($appGuid, '{}') . '}'; if ($searchSubject) { $result = $conn->fetchAll('SELECT ApplicationName, UpdatedAt, ShareId, DownloadCount, ApplicationGuid FROM ApplicationJobs WHERE ApplicationName LIKE ? ORDER BY ApplicationName', array('%' . $searchSubject . '%')); } else { $result = $conn->fetchAll('SELECT ApplicationName, UpdatedAt, ShareId, DownloadCount FROM ApplicationJobs ORDER BY ApplicationName'); } // If the application is among them, it can be overwritten (alreay in DB) foreach ($result as $application) { if ($application['ApplicationGuid'] == $appGuid) { return array(); } } for ($i = 0; $i < count($result); $i++) { $result[$i]['UpdatedAt'] = (int)$result[$i]['UpdatedAt']; $result[$i]['ShareId'] = (int)$result[$i]['ShareId']; $result[$i]['UseCount'] = (int)$result[$i]['DownloadCount']; } return $result; } /** * Gets the XML of all applications that have * been passed as argument and have been updated * (according to the UpdatedAt information of the arguments). * * @param array applications (applicationguid, updatedat) * @return array An array of XML strings */ public static function GetUpdatedApplications($applications) { global $conn; $updatedApps = array(); foreach ($applications as $application) { $appGuid = '{' . trim($application['applicationguid'], '{}') . '}'; $res = $conn->fetchArray('SELECT ApplicationXml FROM ApplicationJobs WHERE ApplicationGuid LIKE ? AND UpdatedAt > ?', array($appGuid, $application['updatedat'])); $res = $res[0]; if ($res) { $updatedApps[] = $res; } } return $updatedApps; } /** * Returns the data of a particular * application by its server-ID. * * @param integer shareId * @return string The application serialised as XML */ public static function GetApplication($shareId) { global $conn; $res = $conn->fetchArray('SELECT ApplicationXml FROM ApplicationJobs WHERE ShareId = ?', array($shareId)); $res = $res[0]; // Keep statistics $conn->executeQuery('UPDATE ApplicationJobs SET DownloadCount = DownloadCount + 1 WHERE ShareId = ?', array($shareId)); return $res; } /** * Adds an application to the database * or updates it. * * @param string xml Serialized application as XML * @param string authorGuid Guid of the author. When updating applications, it must be identical to the one used when adding the application. * @return integer 1 if the application has been updated, 2 if the application has been added */ public static function SaveApplication($xml, $authorGuid) { global $conn; $guidRegex = '/^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$/'; if (!$xml) { throw new XML_RPC2_Exception('Paramater "xml" is missing.', 1); } if (!preg_match($guidRegex, $authorGuid)) { throw new XML_RPC2_Exception('"authorGuid" is not a valid GUID.', 2); } /* LOG $handle = fopen("xml.log", "a"); fwrite($handle, $xml . "\r\n\r\n"); fwrite($handle, $GLOBALS['HTTP_RAW_POST_DATA'] . "\r\n\r\n"); fclose($handle); //*/ // Determine the application GUID. Required. $xmlDoc = new SimpleXMLElement($xml); $appGuid = $xmlDoc->ApplicationJob[0]['Guid']; if (!preg_match($guidRegex, $appGuid)) { throw new XML_RPC2_Exception('No valid application GUID has been found in the XML string.', 3); } $authorGuid = '{' . trim($authorGuid, '{}') . '}'; // We have a valid app GUID now. Check if it has been // deleted by moderators and prevent re-upoad: $res = $conn->fetchArray('SELECT DeleteId FROM app_deleted WHERE ApplicationGuid LIKE ?', array($appGuid)); if (count($res) && $res[0] != 0) { throw new XML_RPC2_Exception('Application has been deleted by moderators.', 99); } $conn->beginTransaction(); // Do we need to update an existing application? If so, // make sure that the author GUID is valid. $validAuthorGuid = $conn->fetchArray('SELECT AuthorGuid FROM ApplicationJobs WHERE ApplicationGuid LIKE ?', array('%' . $appGuid . '%')); $validAuthorGuid = $validAuthorGuid[0]; if ($validAuthorGuid && $authorGuid != $validAuthorGuid) { throw new XML_RPC2_Exception('Invalid author GUID.', 4); } $appGuid = '{' . trim($appGuid, '{}') . '}'; // Next, do we have a valid application name? $appName = $xmlDoc->ApplicationJob[0]->Name; if ($appName == '') { throw new XML_RPC2_Exception('The application name is missing.', 5); } // Delete possibly private data $xmlDoc->ApplicationJob[0]->PreviousLocation = ''; $xmlDoc->ApplicationJob[0]->ExecutePreCommand = ''; $xmlDoc->ApplicationJob[0]->ExecuteCommand = ''; // Check if FileHippo-ID exists $type = $xmlDoc->ApplicationJob[0]->SourceType[0]; $fhId = $xmlDoc->ApplicationJob[0]->FileHippoId[0]; if ($type == 'FileHippo' && !Url::exists('http://www.filehippo.com/download_' . $fhId . '/')) { throw new XML_RPC2_Exception('The FileHippo ID is not valid.', 6); } $externalId = $fhId ? 'FileHippo:' . $fhId : ''; if ($externalId) { // Check that there is no app which already has the same external ID $sameExternal = $conn->fetchArray('SELECT ShareId FROM ApplicationJobs WHERE ExternalId LIKE ? AND ApplicationGuid NOT LIKE ? ', array($externalId, $appGuid)); $sameExternal = $sameExternal[0]; if ($sameExternal) { throw new XML_RPC2_Exception('An application with the same external (for example, FileHippo) ID already exists.', 8); } } $category = $xmlDoc->ApplicationJob[0]->Category[0]; if ($validAuthorGuid) { // UPDATE $conn->executeUpdate('UPDATE ApplicationJobs SET ApplicationName = ?, ApplicationXml = ?, ExternalId = ?, Category = ? WHERE ApplicationGuid LIKE ?', array($appName, $xmlDoc->asXml(), $externalId, $category, $appGuid)); } else { // INSERT $conn->executeQuery('INSERT INTO ApplicationJobs (ApplicationName, Category, ApplicationXml, ApplicationGuid, AuthorGuid, ExternalId) VALUES (?, ?, ?, ?, ?, ?)', array($appName, $category, $xmlDoc->asXml(), $appGuid, $authorGuid, $externalId)); ; } $conn->commit(); return ($validAuthorGuid) ? 1 : 2; } } $options = array('prefix' => 'ketarin.', 'encoding' => 'utf-8', 'input' => new XML_RPC2_Server_Input_PhpInput(), ); try { $server = XML_RPC2_Server::create('KetarinRpc', $options); $server->handleCall(); } catch (Exception $e) { die('Exception : ' . $e->getMessage()); } Link to comment Share on other sites More sharing options...
floele Posted May 1, 2016 Report Share Posted May 1, 2016 -- -- Table structure for table `ApplicationJobs` -- CREATE TABLE `ApplicationJobs` ( `ShareId` int(10) UNSIGNED NOT NULL, `ApplicationName` varchar(255) NOT NULL, `ApplicationGuid` varchar(38) NOT NULL, `Category` varchar(255) DEFAULT NULL, `AuthorGuid` varchar(38) NOT NULL, `CreatedAt` int(10) UNSIGNED NOT NULL, `UpdatedAt` int(10) UNSIGNED NOT NULL, `ApplicationXml` text NOT NULL, `DownloadCount` int(11) NOT NULL DEFAULT '0', `ExternalId` varchar(255) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `app_deleted` ( `DeleteId` int(10) NOT NULL, `ApplicationGuid` varchar(38) DEFAULT NULL, `DeletionDate` int(10) NOT NULL, `UserID` int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Triggers `ApplicationJobs` -- DELIMITER $$ CREATE TRIGGER `JobsCreatedAt` BEFORE INSERT ON `ApplicationJobs` FOR EACH ROW BEGIN SET new.CreatedAt = UNIX_TIMESTAMP(), new.UpdatedAt = UNIX_TIMESTAMP(); END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER `JobsUpdatedAt` BEFORE UPDATE ON `ApplicationJobs` FOR EACH ROW BEGIN IF NEW.ApplicationXml != OLD.ApplicationXml THEN SET new.UpdatedAt = UNIX_TIMESTAMP(); END IF; END $$ DELIMITER ; -- -- Indexes for dumped tables -- -- -- Indexes for table `ApplicationJobs` -- ALTER TABLE `ApplicationJobs` ADD PRIMARY KEY (`ShareId`), ADD UNIQUE KEY `ApplicationGuid` (`ApplicationGuid`), ADD UNIQUE KEY `UniqueNamePerAuthor` (`ApplicationName`,`AuthorGuid`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `ApplicationJobs` -- ALTER TABLE `ApplicationJobs` MODIFY `ShareId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; Link to comment Share on other sites More sharing options...
leeonell Posted May 1, 2016 Author Report Share Posted May 1, 2016 Thanks a lot ! Link to comment Share on other sites More sharing options...
leeonell Posted August 4, 2016 Author Report Share Posted August 4, 2016 Hello Floele, I worked on your source code and it seems that lack the structure (maybe index, trigger too?) for the table app_deleted, could you provide it them too ? Thanks in advance. There is a moderator part on your system ? according to the comment in the code. Link to comment Share on other sites More sharing options...
floele Posted August 6, 2016 Report Share Posted August 6, 2016 Missing table added. Moderating happens within this forum (IPB), we have a custom module which allows deleting apps from the database. Link to comment Share on other sites More sharing options...
leeonell Posted August 8, 2016 Author Report Share Posted August 8, 2016 Thanks Again Floele Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now