My favorites | Sign in
Project Home Downloads Wiki Issues Source
Search
for
Installation  
How to install WWW SQL Designer
Featured
Updated Aug 29, 2012 by ondrej.zara

Installation

You will need to install WWW SQL Designer if you want to:

  • Use custom locale
  • Use custom CSS file
  • Use custom DB definition
  • Use custom server backend

Just download and unpack latest application archive. There are generally no requirements, but to use server-side storage, one should have a working webserver.

To enable the Load and Save features using a database

Ensure that you have a working webserver (Such as WAMP for Windows, MAMP on Mac etc) enabled on your desktop or the PC you will be working off.

You will then need to create a database and enable a user on it:

For PHP-MySQL:

Once logged into the console, create a database that the application will use:

mysql> create database sqlDesignerExample;
Query OK, 1 row affected (0.03 sec)

Ensure that you switch to the database you just created:

mysql> use sqlDesignerExample;
Database changed
mysql> DROP TABLE IF EXISTS `wwwsqldesigner`;
Query OK, 0 rows affected, 1 warning (0.03 sec)

Run the SQL in the sqlDesigner\backend\php-mysql folder:

mysql> CREATE TABLE `wwwsqldesigner` (
    ->   `keyword` varchar(30) NOT NULL default '',
    ->   `data` mediumtext,
    ->   `dt` timestamp,
    ->   PRIMARY KEY  (`keyword`)
    -> );
Query OK, 0 rows affected (0.03 sec)

Now, create a user with the permissions as needed:

mysql> create user 'sqlExample'@'localhost' identified by
'someSuperSecretPassword';
Query OK, 0 rows affected (0.38 sec)

mysql> grant all privileges on sqlDesignerExample.* to
'sqlExample'@'localhost'
with grant option;
Query OK, 0 rows affected (0.03 sec)

Finally, update the PHP file in the same folder. You will need to make changes to the first function as follows:

function setup_saveloadlist() {
    define("SERVER", "localhost");
    define("USER", "sqlExample");
    define("PASSWORD", "someSuperSecretPassword");
    define("DB", "sqlDesignerExample");
    define("TABLE", "wwwsqldesigner");
}

Save the file after you make the changes and you will now be able to load and save using Server Backend: php-mysql

Comment by pattyk...@gmail.com, Apr 2, 2009

How do you install this. I'm only able to unzip it.

Comment by jhol...@gmail.com, Apr 3, 2009

You need to do a little digging... I struggled at first, as well. For starters, you need to make sure the entire zip file is unpacked in your server's document root. Access the folder/index.html file to see if it displays the grid and if you are able to create tables. Next, create a database and add the table located at "sqldesigner/backend/php-mysql/database.sql" After creating the database and table make adjustments to the first function in the index.php file located in the same directory.

I haven't played with the Import from DB option yet but I suspect you would need to make changes to the second function and possibly the fourth (by changing "information_schema" to the database you want to import) but you are on your own with that one.

By the way, ondrej.zara, excellent tool! Thanks for the time you've spent on this! Looking forward to seeing where you go with it!

Comment by rap...@free.fr, Apr 20, 2009

If you want to use import feature, here is how I did it :

To make the save and load feature working follow this steps : 1. Open backend/php-mysql/index.php 2. In setup_saveloadlist function change SERVER, USER, PASSWORD and DB to fit your configuration 3. Import in DB the file backend/php-mysql/database.sql

To make the import from db feature working follow this step : 1. Open backend/php-mysql/index.php 2. In setup_import function change SERVER, USER, PASSWORD

Before any of this step, I suggest that you create through phpmyadmin or mysql client the sqldesigner mysql account.

The process for other server import should be similar.

Comment by jwpr...@gmail.com, May 5, 2010

Note that in php.ini turning on only the php_mysqli extension won't work; you must also turn on the php_mysql extension.

It's also worth noting that (at least with Apache) if you change php.ini you must restart the web server as the php module only reads php.ini once. Which is certainly sensible, but can be annoying when you forget and then waste time trying to figure out why something isn't working.

Comment by meade.d...@gmail.com, Jul 13, 2010

My installation worked and it seems to be working fine except for one thing - trying to add a comment to a table crashes my browser. (Safari crashes and google gives the "aw snap" crash page). Anyone have any suggestions?

Comment by project member ondrej.zara, Jul 13, 2010

This is probably related to http://code.google.com/p/wwwsqldesigner/issues/detail?id=64 . Stay tuned - it looks like the problem is apparently nearly fixed in Chrome :)

Comment by meade.d...@gmail.com, Jul 15, 2010

ah, yes that sounds similar. Thanks :)

Comment by rlape...@gmail.com, Aug 17, 2010

very good tool, zara. Congratulations.

Comment by digital....@gmail.com, Aug 20, 2010

You state: "There are generally no requirements"

A Web server is not a requirement? One should be able to just open the index.html file directly into the browser and work away? I can't even add a table in Safari 4 or Firefox 3.5 (OS X 10.5 PPC). So it seems to me I have to install in my web server?

My objective would be to design a data model and export it as a SQL creation script.

Comment by project member ondrej.zara, Aug 22, 2010

Web server is definitely not a requirement for a basic functionality. It is necessary only for save/load/export/import tasks.

Comment by digital....@gmail.com, Aug 24, 2010

I got it now. Suggestion: Make a paint selection – mouse held down + making a selection - create a table just like just clicking. For some reason I made a paint move instead of a click. For me that was the most intuitive move.

This looks like something I'd like to install on my web server. Will try it in a live project right now.

Big hand!

Comment by john.o.n...@gmail.com, Sep 9, 2010

I can't seem to get this working. I've extracted the zip file OK and brought the index page up in my web browser. But I just get the following error message:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022) Timestamp: Thu, 9 Sep 2010 12:12:52 UTC

Message: Access is denied.

Line: 201 Char: 3 Code: 0 URI: file:///E:/Program%20Files/SQL%20Designer%202.5/js/oz.js

I'm running it on IE8 on XP SP3 inside VMWare. Any ideas what line 201 is doing that IE/XP is objecting to?

Puzzled?

John

Comment by project member ondrej.zara, Sep 9, 2010

Maybe some silly security restrictions in IE and/or Windows prevent local files (served with file:/// protocol) to use JavaScript?? Are you sure there was no confirmation/allowance dialogue prior to this error?

Comment by juan.la...@gmail.com, Sep 28, 2010

I have an old IIS5 running in my Windows XP. After downloading the .zip I extracted it´s content (a folder maned wwwsqldesigner-2.5) under C:\Inetpub\wwwroot (the default root folder). After, I created an alias "wwwsqldesigner" (no version number) pointing to the new folder using the IIS management applet in the control panel. In the browser entered:

http://localhost/wwwsqldesigner/
and the application displayed. I was able to add two tables with a column each and set an FK relationship. It all took about 5 minutes. Excellent, Ondrej!

Comment by abdull...@gmail.com, Oct 5, 2010

is there any way to actually run the SQL file that is generated directly from the system? anyone extended it?

Comment by project member ondrej.zara, Oct 5, 2010

Just transfer it to phpMyAdmin via clipboard :)

Comment by kadish...@gmail.com, Nov 28, 2010

CUBRID Database project has added WWW SQL Designer to the list of Web applications supporting their database. See http://www.cubrid.org/wwwsqldesigner.

Besides the DEMO site has been created at http://sqldesigner.cubrid.org/. You might want to add the DEMO site link to either your Manual or Installation Wiki page. Otherwise, I could not find one. In fact, I saw somewhere here the link for the demo site, which loaded some demo schema, but could not find it again. Perhaps, it would be good to place this demo site at some visible spot as well.

Comment by project member ondrej.zara, Nov 29, 2010

There already is a canonical demo site, located at http://ondras.zarovi.cz/sql/demo/?keyword=default and linked directly from project's homepage at http://code.google.com/p/wwwsqldesigner/ .

Comment by kadish...@gmail.com, Dec 1, 2010

Right. I remember I saw somewhere, but the links are so tiny I could not find again. Anyways, the http://sqldesigner.cubrid.org/ demo site is backed by the CUBRID Database. That should be the difference.

Comment by mdk...@gmail.com, Dec 8, 2010

If you guys want an easy way to run on your local machine without a lot of hassle, you can always use Bitnami's AMP Stacks (available in Linux and Windows varieties - LAMP and WAMP respectively).

This will install apache and mysql on your local machine, and give you a control script.

http://bitnami.org/stack/wampstack

Comment by stefano....@gmail.com, Mar 9, 2011

installation is a 2min job. the graphical aspect is stunning, but the time needed to import the structure of one of my smallest database was deadly long. The problem was, seen from my processlist, the queries on the information_schema. tables limiting column per column for every table found in the database. SQL AND information_schema were not created to be used like that.

It took 5 mins to extract info from 21 tables (without notice! I'd expect 99.9% of users re-trying to reimport data almost twice. I did it, I manually killed the duplicate queries on the db)

Given this, it's really nice

Comment by mattpla...@gmail.com, Aug 1, 2011

The front end of this is fantastic! I had to rewrite the import function to read table definitions from DESC [table] over the tables in the database instead of the information_schema etc. Now it is loading a database of 200 large tables in a few seconds. This is a seriously useful piece of software, something like this has been needed for a long time have been contemplating getting something written to do this exact thing in fact, I just don't really understand the javascript so much, getting the lines between the divs moving and recalculating as they were dragged about was always the stumbling block. Well done!

Comment by Mirodil...@gmail.com, Oct 31, 2011

How do you install this? I got the latest version and unzip it. I opened the file named "index.html" and only appear empty gird without any controls.

Comment by Justin.V...@gmail.com, Oct 31, 2011

I had the same problem index.html appears as an empty grid without any controls.

Comment by project member ondrej.zara, Nov 1, 2011

Use a (local) webserver to view this application, i.e. download, unzip to webserver's root, goto http://localhost/path/to/extracted/archive

Comment by wbucha...@astadia.com, Nov 16, 2011

NOTE - opening the index file in Chrome you will not see the controls to create a table, etc. Use Firefox and everything works.

Comment by kn_d...@yahoo.com, Nov 27, 2011

I can't use 'save" function. Get a message : 'Server response: Service Unavailable' I'm using Google Chrome and WAMP server. I've checked the server is online.

Thumbs up for this very easy-to use tool!!

Comment by project member ondrej.zara, Nov 27, 2011

To save data, you must properly configure the desired backend to suit your setup. For instance, if you use php-mysql backend to store designs, you have to:

1) create a storage table according to backend/php-mysql/database.sql 2) setup connection and credentials in backend/php-mysql/index.php

:)

Comment by kn_d...@yahoo.com, Nov 28, 2011

Thanks for the help. I'm not a techie at all and struggled a bit but I finally succeeded in saving my tables!!

I'm not a DBA and only have SQL basics but I happen to write SELECT scripts and always have trouble remembering tables names and the links betweend each other.

I was really eager to have the tool working on my comp and get rid of my awful drawings in Excel :p

Thanks a bunch!

Comment by rwonem...@gmail.com, Mar 14, 2012

these are steps if you would like to add it to your website, are using MySQL and phpMyAdmin, and encoding is OK as english: #1 download zip file, #2 extract, #3 upload contents to a directory on your website, #4 create a database (in cPanel for example), #5 create a database table in phpMyadmin (3 columns - col 1 called keyword, varchar(255), Default is 'None', utf8_unicode_ci; col 2 called data, mediumtext, Default is 'NULL', utf8_unicode_ci, null checkbox checked; col 3 called dt, timestamp, default is CURRENT_TIMESTAMP, attributes 'on update CURRENT_TIMESTAMP'). #6 Set the 'keyword' column as the index - not sure if this is necessary but phpMyAdmin was prompting me to set an index. (You can import the database at backend/php-mysql/database.sql into your new database if you would like to see what the database table should look like). #7 edit the file at backend/php-mysql/index.php so that the first four (4) functions match your new database settings. You should now be able to Save and Load schemas by their keyword.

Comment by rwonem...@gmail.com, Mar 14, 2012

you could also then password protect that directory by following this tutorial: http://css-tricks.com/easily-password-protect-a-website-or-subdirectory/

Comment by rwonem...@gmail.com, Mar 14, 2012

can anyone please elaborate on how i can import a database? i have managed to enable 'Save' and 'Load' as above but haven't been able to figure out how to import from a database. Say i have 'main_site_database' and 'sqldesigner_database' - can i import a database from 'main_site_database'? Which function should i be modifying in backend/php-mysql/index.php and should the connection function be defined as the one to 'sqldesigner_database' or the one to 'main_site_database'? Thank You!

Comment by project member ondrej.zara, Mar 15, 2012

Hi,

to import stuff, adjust those DEFINE fields in setup_import() in backend/php-mysql/index.php.

IMPORTANT! Do NOT change the DEFINE("DB","information_schema"); this is NOT the place for the name of database to be imported.

Comment by rwonem...@gmail.com, Mar 15, 2012

thank you for your reply, in chrome i get XML error: null document. in firefox, a blank grid loads, although with firebug > console on it shows '200 OK 512ms' but then also shows 'Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ttt/public_html/ttt/backend/php-mysql/index.php on line 38'. is there any other information i can provide to assist with troubleshooting? thank you.

Comment by rwonem...@gmail.com, Mar 15, 2012

ps to above, as this could have something to do with my index.php and how i have set it up - a commented version of those first four functions would be really helpful eg with comments above each DEFINE field saying what should be changed and how and what shouldn't be changed - perhaps this is something that could be added to this wiki? thank you!

Comment by rwonem...@gmail.com, Mar 15, 2012

pps to above:

for example, the comments could be (and i don't know if these are correct, please correct if they are not):

// define your server, usually localhost

DEFINE SERVER

// define the user who has access to your wwwsqldesigner database

DEFINE USER

//define their password

DEFINE PASSWORD

// define the wwwsqldesigner database where your keywords will be saved and loaded from

DEFINE DB

// define the wwwsqldesigner database table where you keywords will be saved and loaded from

DEFINE TABLE

// define the server where you will be importing databases from, usually localhost

DEFINE SERVER

// define the user who has access to the database you wish to import

DEFINE USER

// define their password

DEFINE PASSWORD

// leave this as is - do NOT adjust this

DEFINE DB

// ================== generally you do not need to edit below this line ==================

FUNCTION CONNECT

Comment by rwonem...@gmail.com, Mar 15, 2012

and in case i buried my own request hehe :) i am still getting this error when trying to import db:

in chrome i get XML error: null document. in firefox, a blank grid loads, although with firebug > console on it shows '200 OK 512ms' but then also shows 'Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ttt/public_html/ttt/backend/php-mysql/index.php on line 38'. is there any other information i can provide to assist with troubleshooting? thank you.

Comment by ge...@sergiolourenco.com, Mar 30, 2012

Ok, so i was able to install, run and save to backend "php-mysql" in a windows 7 machine. However, running on a hosting server with Linux i could not save (no error, nothing). What i did? On the "oz.js" file i commented the line 217 - "if (o.method == "POST") { xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded"); }".

Voilá!! Its working! Hope it helps for those having problems.

Comment by biksworl...@gmail.com, May 15, 2012

nice stuff. took almost 1 hour to understand how to work with. but finally succeeded. gr8 job

Comment by junxu...@gmail.com, Jun 6, 2012

Very very good tools!

Comment by muhammad...@gmail.com, Jul 2, 2012

I was googling some web2py stuff and I have reached your fabulous app at http://designer.bojanka.net/designer/. I have downloaded and tried the app both locally or through a web2py local web server. But still I can not figure out how to enable web2py as a server backend option which is already enabled at http://designer.bojanka.net/designer/. any suggestions ?

Comment by project member ondrej.zara, Jul 5, 2012

Just checked it and it works; I presume you already solved this?

Comment by jtl...@yahoo.com, Jul 13, 2012

This tool has got my boss WAY TOO EXCITED! Now I regret setting it up! One install tip - make sure your server running this tool is in the EXCEPTION list for your proxy server! This is not proxy server friendly! Great work Ondrej Zara - YOU RULE!!!

Comment by 73as...@googlemail.com, Aug 9, 2012

Just discovered this and it's absolutely brilliant. If only everything was as easy to use & configure as this. Brilliant stuff. Much appreciated! Kudos to the first few commenters too for outlining the steps to install on AMP.

Comment by swati4...@gmail.com, Dec 8, 2012

I set it up and everything works fine - except for when I try to load or save using the backend/php-mysql. Here is what I get.

"You don't have permission to access /sqldesigner/backend/php-mysql/"

I have given all the necessary permissions to Apache web server process to access the folder.Any clue?

Comment by project member ondrej.zara, Dec 8, 2012

1) OS / Browser / version?

2) Screenshot (from the debugging tool which reports the error)

3) Do you use local webserver, or file:/// protocol? AJAX requests are not allowed for file:/// in certain browsers.

Comment by swati4...@gmail.com, Dec 9, 2012

1. OS: Ubuntu 12.04 Browser: Firefox Version: 16.0.2

2. Not sure how do I attach the screen shot. I get this message when I click on "List items" button after a failed "Import from DB" attempt. The content displayed is as follows.


<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <html><head> <title>403 Forbidden</title> </head><body> <h1>Forbidden</h1> <p>You don't have permission to access /sqldesigner/backend/php-mysql/ on this server.</p> <hr> <address>Apache/2.2.22 (Ubuntu) Server at localhost Port 80</address> </body></html>


3. I use local server "http://localhost/sqldesigner" - that is how I have configured. "sqldesigner" is an alias pointing to the folder (inside "/home/user_name/blah/blah").

FYI, everything else works fine. What I mean by this is - I am able to model database, save the content to the browser and able to re-open later on etc...

Thanks for your help.

Comment by project member ondrej.zara, Dec 10, 2012

Right, so this seems to be a permission issue. You can verify that by simply pointing your browser at http://localhost/sqldesigner/backend/php-mysql/ (you shall see 403, until you properly set the permissions).

Comment by joell...@gmail.com, Feb 12, 2013

Doesn't work for me, I try Import from DB and I get an empty grid (no errors). The DB I'm importing does have tables defined (I've tried several different DBs).

Comment by project member ondrej.zara, Feb 12, 2013

Try to watch the "import" request in firebug and then open it in new tab. Post the screenshot of the resulting page...

Comment by pekhota....@gmail.com, May 16, 2013

Thanks

Comment by hh0...@gmail.com, Jun 18, 2013

anyone trying to use nginx as web server to run on this application?

Comment by project member ondrej.zara, Jun 19, 2013

What problems have you encountered?

Comment by jesus.mi...@gmail.com, Sep 11, 2013

I just tested design a little database and I like it very much. I would like to enable Load/Save feature using a SQLite database. What do I have todo? I'm testing lighttp + php-cgi + pdo-sqlite. Thanks.

Comment by project member ondrej.zara, Sep 11, 2013

You have to:

1) download the www sql designer and make it run using your local webserver (i.e. http://localhost, NOT file:///...)

2) switch to the php-sqlite backend in Save/Load dialog

3) everything should work. If not, please check the relevant AJAX requests with your favorite browser debugging tool.

Note that the built-in sqlite table already contains one demo design (labeled "default"), so the "list" and "load" commands can be tested without actually saving anything.

Comment by jesus.mi...@gmail.com, Sep 17, 2013

backend php-sqlite support sqlite 2 sintaxis. I modified the backend using sqlite3 sintax, right now I can create the db file but i can't save the schema. Output error: Internal Server Error.

Comment by project member ondrej.zara, Sep 18, 2013

Well you will have to check your webserver's log then. I have no idea how you changed the backend code, so I cannot provide any assistance here...

Comment by project member ondrej.zara, Sep 18, 2013

News: I just reimplemented the php-sqlite backend so it uses PDO+SQLite3. Please update your installation (use the latest mercurial revision).

Comment by darius...@gmail.com, Feb 3, 2014

Trying to save to php-mysql gives me this :

POST backend/php-mysql/?action=save&keyword=sgsdg 406 Not Acceptable 144ms

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <html><head> <title>406 Not Acceptable</title> </head><body> <h1>Not Acceptable</h1> <p>An appropriate representation of the requested resource /backend/php-mysql/ could not be found on this server.</p> <p>Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument? to handle the request.</p> </body></html>

Comment by darius...@gmail.com, Feb 3, 2014

SOLVED :

REPLACED //if (o.method == "POST") { xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");}

WITH if (o.method == "POST") { xhr.setRequestHeader("Content-Type","application/x-httpd-php");}

Comment by project member ondrej.zara, Feb 3, 2014

Wow, this solution is strange. I would say this should not work, but if it solved your issue... :-)

Comment by anacruzm...@gmail.com, Jul 23, 2014

Hi:

First of all, thanks and congrats for this really nice and useful tool :)

This is my doubt: I have managed to import a posgis DB into wwwsqldesigner without problem. However, when I make some changes on it using wwwsqldesigner and then try to save them to the DB in the server, I get a "503 Service Unavailable" error. Firebug reports that the network command is "http://localhost/wwwsqldesigner-2.7/backend/php-postgresql/?action=save&keyword=mykeyword"

Am I missing something? Thanks in advance for your help.

Comment by project member ondrej.zara, Jul 23, 2014

Hi,

the error message (503) is generated by your webserver. I suggest looking into its log files to see why your webserver+php+postgresql combination refuses to accept your data.

Also, please note that WWW SQL Designer does not allow you to actually modify your database: the "save" command just saves your design into a dedicated table (have you actually created it) for further loading and/or adjustments. To really modify your DB schema, export your design as a SQL and execute it against your database.

Comment by anacruzm...@gmail.com, Aug 13, 2014

Hi:

Thanks a lot for your answer. In case it is useful, here it is a link (in Spanish and English) to a post I have written in order to explain my experience with WWW SQL Designer: http://jafma.net/ana/theweekendarchaeologist/?p=71


Sign in to add a comment
Powered by Google Project Hosting