Nice! But no admin UI?

by **Tim_Myth** » Tue Jun 23, 2009 6:25 am

Ok, I played with Xataface yesterday, and I must say I am impressed. Very nicely done. I have just one small problem: there is no adminstrator back end. I realize that phpMyAdmin does an awesome job of alowing me, the developer, manage databases. Dammit Jim! I'm a developer, not a database administrator! That's a job to foist off on someone without my mad skillz. Hahahaha. Sorry, playful mood this morning. Seriously though, this is great...scratch that...freaking awesome for a smaller organization that doesn't need people with such seperate roles, but in my case I need something that I can develop, hand over to a less technical user, and move on to the next nut that needs cracking. Giving Joe Mid-Level Manager access to phpMyAdmin is asking for trouble, not to mention that I can't expect Mr. Manager to be able to code a delegate class, properly edit an ini file, or understand the file structure of the tables folder. Frankly, I'm impressed when Joe knows what FTP is much less how to use it. Are there any plugins/modules that replicate a few of the features of phpMyAdmin using prepared sql statements (for security) without being as complex (for simplicity) while also editing the various ini files?

by **shannah** » Tue Jun 23, 2009 7:13 am

What specific phpmyadmin functionality were you interested in?

by **Tim_Myth** » Tue Jun 23, 2009 7:32 am

Well, I've been working on an "admin" page that gets stuck in a sub folder which is password protected. It would/should allow the db admin to create a table, drop a table, and modify a table. It should also edit the conf.ini file to include the table as a nav tab and work with the individual settings for that table. For example, it should allow the db admin to set a "friendly name" ad description for the tabl columns. I haven't quite reached the user pemissions part of the docs, but that would also be huge. Ideally, the db admin page should perform all the file writing operations Iwould normally do by hand. Less ideally woul be to make a text box that loads and allows me to edit the various files.

Here's what I cludged up last night:
Admin.php

Code: Select all: <html> <head> <title>Create Table Demo</title> <script> //Gets the browser specific XmlHttpRequest Object function getXmlHttpRequestObject() { if (window.XMLHttpRequest) { return new XMLHttpRequest(); //Not IE } else if(window.ActiveXObject) { return new ActiveXObject("Microsoft.XMLHTTP"); //IE } else { //Display an error message and inform the user they might want to upgrade their browser. alert("Your browser doesn't support the XmlHttpRequest object. Better upgrade to Firefox."); } } //Get our browser specific XmlHttpRequest object. var receiveReq = getXmlHttpRequestObject(); //Initiate the asyncronous request. function Create_Table() { //If our XmlHttpRequest object is not in the middle of a request, start the new asyncronous call. if (receiveReq.readyState == 4 || receiveReq.readyState == 0) { //Setup the connection as a GET call to create_table.php. //True explicity sets the request to asyncronous (default). var $TN = encodeURI(document.getElementById('tablename').value); var $FN = encodeURI(document.getElementById('friendlyname').value); receiveReq.open("GET", 'create_table.php?tablename='+$TN+'&friendlyname='+$FN, true); //Set the function that will be called when the XmlHttpRequest objects state changes. receiveReq.onreadystatechange = handleCreate_Table; //Make the actual request. receiveReq.send(null); //Clear our text boxes document.getElementById('tablename').value=""; document.getElementById('friendlyname').value=""; } } //Called every time our XmlHttpRequest objects state changes. function handleCreate_Table() { //Check to see if the XmlHttpRequests state is finished. if (receiveReq.readyState == 4) { //Set the contents of our span element to the result of the asyncronous call. document.getElementById('span_result').innerHTML = receiveReq.responseText; } } </script> </head> <body> <p> Example of using AJAX to create a new table. This file would/should be placed in a folder that is password protected.<br> <input> Enter a name for the new table. <span>*REQUIRED</span><br> <input> Enter a human-readable (i.e. "Friendly") name for the table<br> (Leaving out a bunch of code here that would allow you to specify the various columns and their data types here.)<br> <button>Create Table</button> </p> <p>Result: <span> </span></p> <p>At this point I have not coded the functions to allow columns to be named/created so a default set is used. There are numerous other function that would have to be created such as the ability to modify existing tables, set reationships between tables, or drop tables entirely. We can also add a link to each table view in Xataface to allow the table to be modified (assuming appropriate permissions of course!).</p> </body> </html>

create_table.php

Code: Select all: <?php // import xataface // ** THIS MUST CHANGE WHEN WE MOVE IT TO A SUBFOLDER ** require_once 'dataface-public-api.php'; df_init(__FILE__, 'http://10.240.16.81/OIMS/'); // Make sure a tablename was given or die if (isset($_GET['tablename']) and $_GET['tablename']<table_name> $ini_section_value) { fwrite($fh, "[$ini_section]\n"); foreach($ini_section_value as $key => $value) { fwrite($fh, "$key=\"$value\"\n"); } } // Check for the presence of a friendly name. If no friendly name, use the table name $new_table = urldecode(strip_tags($_GET['tablename'])); if (isset($_GET['friendlyname']) and $_GET['friendlyname']<>""){ $new_table .= '="' . urldecode(strip_tags($_GET['friendlyname'])) . "\"\n"; } else { $new_table .= '="' . urldecode(strip_tags($_GET['tablename'])) . "\"\n"; } // Write the new table settings to conf.ini fwrite($fh, $new_table); fclose($fh); // Return success echo "Success!"; } else { // It was NOT successful so return the error echo "FAILURE:" . mysql_error(); } ?>

What those two files do:
Enter a name in the table name field and click the ceate table button. It creates a table in your db (make sure to set the $db variable in create_table.php!), then edits the conf.ini file to add that table as a nav tab.

Much easier for a non-technical user than using phpMyAdmin and ftp to download, edit, then upload conf.ini. Also much quicker.

by **Tim_Myth** » Tue Jun 23, 2009 7:36 am

hmm. For some reason the forum is stripping a bunch of my code. probably because it is sql_query stuff.

by **Tim_Myth** » Tue Jun 23, 2009 7:38 am

Maybe this will work
[code]<?php

// import xataface
// ** THIS MUST CHANGE WHEN WE MOVE IT TO A SUBFOLDER **
require_once 'dataface-public-api.php';
df_init(__FILE__, 'http://10.240.16.81/OIMS/');

// Make sure a tablename was given or die
if (isset($_GET['tablename']) and $_GET['tablename']<>""){
$table_name=mysql_real_escape_string(urldecode($_GET['tablename']));
} else {
die('No table name specified. Exiting...');
}

// select the database
// ** THIS SHOULD BE A CONFIGURATION SETTING SET DURING INSTALL. MAYBE A CONSTANT?
$selected_db = 'oims'
mysql_select_db($selected_db) or die('Cannot select database');

// Build the query.
$query = 'CREATE TABLE '.$table_name.'( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'cname VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'csubject VARCHAR(30) NOT NULL, '.
'cmessage TEXT NOT NULL, '.
'PRIMARY KEY(cid)) '.
'ENGINE=MYISAM';

// Execute the query
$result = mysql_query($query);

// Was it successful?
if ($result){

// It was successfull, so read in conf.ini and parse it to an array. This may be redundant.
$conf_ini = parse_ini_file("conf.ini", true);
$myFile = "conf.ini";
$fh = fopen($myFile, 'w') or die("can't open file");

// Write conf.ini back
foreach ($conf_ini as $ini_section => $ini_section_value) {
fwrite($fh, "[$ini_section]\n");
foreach($ini_section_value as $key => $value) {
fwrite($fh, "$key=\"$value\"\n");
}
}

// Check for the presence of a friendly name. If no friendly name, use the table name
$new_table = urldecode(strip_tags($_GET['tablename']));
if (isset($_GET['friendlyname']) and $_GET['friendlyname']<>""){
$new_table .= '="' . urldecode(strip_tags($_GET['friendlyname'])) . "\"\n";
} else {
$new_table .= '="' . urldecode(strip_tags($_GET['tablename'])) . "\"\n";
}

// Write the new table settings to conf.ini
fwrite($fh, $new_table);
fclose($fh);

// Return success
echo "Success!";
} else {

// It was NOT successful so return the error
echo "FAILURE:" . mysql_error();
}
?>[/code]

by **shannah** » Tue Jun 23, 2009 7:41 am

I see what you're try to build. I have started and stopped working on an admin type module many times. Just haven't had any clients that needed it.

Your module sounds useful. Please consider releasing it to the community once it is complete as I'm sure that some others will find it helpful too.

You must take care now that you are connecting the bridge between HTTP access and filesystem access. Since INI files reside on the filesystem, Xataface considers them "trusted". I.e. it isn't worried that a valuelists __sql__ query might contain a "drop tables" statement. However if you write a module that allows updating of the ini files, take care that your module isn't exploitable by a hacker to start writing malicious things in the INI files.

Best regards

Steve

by **shannah** » Tue Jun 23, 2009 7:45 am

Tim_Myth wrote:hmm. For some reason the forum is stripping a bunch of my code. probably because it is sql_query stuff.

Check the "Disable HTML in this post" box. This will tell phpbb to behave.

Nice! But no admin UI?

Nice! But no admin UI?

Who is online