Jump to content
Ketarin forum

Question about online database server


leeonell
 Share

Recommended Posts

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

 


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

--

-- 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

  • 3 months later...

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.