WWW SQL Designer

HEllo,

Can't save anything...

Thx

Hello,

please provide more information about version, used backend and error messages (if any).

How I save files : # check that you server have write permission in sqldesignor's directory # I use the method php-file

I hope it can help you

how to delete relations:

1. kick ondrej.zara on the butt for not implementing a method to delete relations
2. hug ondrej.zara for giving immense flexibility by allowing to edit XML
3. click on save/load
4. click on save xml
5. edit the resulting xml to delete the relation
6. load xml back

when i add a table and type in the description and then hit "enter" I get an "AW SNAP" error page in google chrome. if I click the "ok" button, i don't get the error

How do I import a schema from a ddl script ? Ie I want to have an ER diagram from a sql script file that contains all my table definitions. Thanks.

Please see http://code.google.com/p/wwwsqldesigner/issues/detail?id=4 - create an appropriate import-capable backend to achieve this.

I have the /php-file/data as 777 and still does not save.

Please help

Rick

it just doesn't work. I cant import a thing... no databases (mysql) no sql scripts nothing. what are you guys working on if your software just doesnt work ?

@csharppoet It's a work in progress, don't like it? Don't use it, simple as that.

@csharppoet To import a database schema, make sure you have a working php+mysql environment and setup your DB connection in backend/php-mysql/index.php. If this does not work, please provide us with an error report (for instance, the output of the relevant ajax call) instead of plain trolling. Thanks! :)

I have the /php-file/data as 777 and still does not save.

@laucode - if PHP is in safe_mode, your file must be owned by the process of web server. If this still does not help, you will need to provide some debugging output (for instance, the result of relevant ajax call(s)).

This is a brilliant tool (and it saves and loads perfectly for me without any issues).

Great piece of software.

hello ! how to delete a foreign key ? thanks

Click on the foreign row and select "Remove field" button. Removal of the connector line (without field deletion) is on the tasklist.

Amazing tool and code

Excelent. I use it for the begining.

The online demo doesn't create a new field and relation when clicking 'Create foreign key' button in sidebar and click target table's heading.

Yes, this is a bug. Will fix it asap.

when trying to import from DB (using php-mysql) it says: XML error: null document.

all the other functions (load, save, list) are working perfect.

What I'm trying to do is to import a database in the sql server.

very nice work!

The XML generated by importscript is probably not valid. What are the contents of /backend/php-mysql/?action=import&database=yourDBname ?

Realy great Work. Thank You. We are molding it to new needs and we are pleased with what we are seeing! We have 2 plans: .Enlarge designer-zone .Write a Oracle-Xe 10g connector. Keeping in touch.

ok. stage 1 done. We have a "Zone" 5000px by 5000px and an easy hundreads of tables... The system answer time like open. load, scrolls are visibly slow. It is not because of the cpu power (2.4 GHz and 1 GB Ram) but because of Video Card that seems manufactured by the Brothers Lumière. Changes: (for a static zone) - file ./styles/styles.css - paragraph : #area - lines : 6,7,8

Next News : The oracle connector-XE 10g.

Again, thank you guys. great work. Lorenzo.

Hello, How to create a foreing key when the primary key are two fields ?

CREATE TABLE table1 (

sub_id1 int(11) NOT NULL, sub_id2 int(11) NOT NULL, field decimal(4,2) default NULL, PRIMARY KEY (sub_id1,sub_id2)

CREATE TABLE table2 (

sub_id1_table1 int(11) NOT NULL, sub_id2_table1 int(11) NOT NULL, field decimal(4,2) default NULL, PRIMARY KEY (sub_id_table1,sub_id_table2)

ALTER TABLE table2

ADD CONSTRAINT table2_ibfk_1 FOREIGN KEY (sub_id1_table1, sub_id2_table1) REFERENCES table1 (sub_id1, sub_id2);

I am afraid that this is currently not posible, because FK constraints are created between two columns (correct would be column and a key).

Great tool. Installs and saves/loads fine if you are equipped with a standard brain. Thank you. (Add the ability to remove a connector line and I'll thank you again :-) )

Great work! i just like it to have support for oracle sql.

I too am having problems with the import from DB in mysql-php. The action statement above gives: Index of /wwwsql/backend/php-mysql Parent Directory database.sql index.php but I see no XML! Also if I try to execute the query "SELECT FROM TABLES WHERE TABLE_SCHEMA = '".$db."'" This doesn't seem to return anything. I assume the information_schema db is used prior to this call but where? /steveA

If you see only the directory listing, you need to setup your webserver to use index.php as a directory index file.

Apparently, I do not have a standard brain. It's a great tool but I also am having prob's with Load/Save. I am running F'fox3.5 on a Mac 10.5, the files are in the 'Sites' directory and the backend dir is set to Read&Write for everyone. When I try to save I just get an endlessly spinning gearwheel..... Apologies for my denseness

To save and/or load, one must have a properly set up webserver with PHP support and MySQL database (if you choose to use the php+mysql backend). I do not have access to Mac platform right now, so I am unable to provide more detailed assistance :-(

Ondrej - thks for the swift feedback - i can have MAMP running in the background but do not see where to go to link the two elements (MAMP/SQLD) I would be content just to save as a file.... O'wise it's just marvellous

The php+mysql backend is realized via HTTP requests to /YOUR_WWWSQL_INSTALLATION/backend/php-mysql/index.php file. This file must be interpreted with PHP. Also, your MySQL must be correctly set up with a table - the definition is in backend/php-mysql/database.sql. Finally, in the index.php file, you must properly configure the database connection credentials.

Of course, you are free to use any other available backend, e.g. the php-file. In this case, MySQL is not necessary.

And then, there is no need to use DB backends at all - just export your design as XML and save it to file via clipboard. No configuration necessary :)

Ondrej - Thks again for the feedback

I have amended the index.php file to include the username & pwd for MAMP in several places (lines 5,6,12,13 & 17) - is this correct

re the mySQL part of MAMP - this needs to have a table with the definition shown in 'database.sql' - is this a standalone db or part of the db i am working on ??

Apologies for the denseness of my Q's. AD

The DB used for design storage is not related at all to the DB you are designing. No problem with questions, but I will be gone for 3 weeks starting from tomorrow :)

Sorry but what should "The DB used for design storage" be called

Enjoy your hols

So I now have

1) a DB called 'WSD' running under MAMP w a table 'wwwsqldesigner' as per the file 'database.sql' in the 'backend/php-mysql'dir.

2) the file 'index.php' in the same dir amended to show the correct u'name,pwd & DB. (changes on lines 5, 6, 12, 13, 17 & 19)

Q - is this file in the right place?? Q - DB save under 'wwwsqld..' should show up in 'WSD' as data - yes??

All files are in right places in default wwwsqldesigner installation. There is no need to move them around.

If you save your DB design via the "php-mysql" backend, your design will be added as a new data row to the 'wwwsqldesigner' table. If unsure, just use the Firebug tool to monitor the XHR traffic.

thks for that - hope you have a great hol - 3 wks! marvellous

meantime i am getting this errmsg from Firebug

URI denied" code: "1012 on this error? xhr.send(o.data || null);\noz.js (line 213)

Anyway I will fool around until I break something will talk in 3 wks - enjoy Really thought that i had the permissions thing sorted Its still a really cool tool!!

great work. How to start with a default design to show? And how not to let people overwrite it? (just like demo)

How to Import from DB (my database is Oracle xe) . Pls tell me the solution as early as possible, its urgent.

arthur.doohan - please send me a screenshot showing the issue

famiglia.invernizzi - default design via ?keyword=name_of_default

hydsea - you need an oracle-based server endpoint with import capabilities. There is no such endpoint at the moment; someone has to create it.

I have a problem with the import from MySQL-DB: Loacal is running XAMPP for Mac (with the defaults "root" and pw ""). The /backend/php-mysql/index.php beginn:

set_time_limit(0); function setup_saveloadlist() {

define("SERVER","localhost"); define("USER","root"); define("PASSWORD",""); define("DB","home"); define("TABLE","wwwsqldesigner");

} function setup_import() {

define("SERVER","localhost"); define("USER","root"); define("PASSWORD",""); define("DB","information_schema");

}

Please watch the traffic (via Firebug) and send me the info about what is happening in the background. Use e-mail/google groups for this.

great tool!!

Thanks. It's simple and easy to use. Good for initial proposal designs.

Hi, Its an amaizing tool. Howver, I came acroos a doubt? Ive mapped fields of 2 table using foreign key and obviously, I can see a black line connecting between the 2 fields. Now, suppose i dont want that foreign key mapping. How can i remove the mapping?

Hope i didnt make it confusing. Thanks.

Hi,

removing a relation (without removing the relevant field) is on a list of acknowledged tasks.

Thanks for the reply. Does that means I can only remove the black-line via deleting the field and re-creating it? Am i correct?

Currently, this is the only way.

It seems that it does not work in IE7 when moving the boxes around. The connector did not adjust automatcailly. Is there any reason? Or Am I missing something? Thanks in advance. Anyway, it is a awesome tool.

Moving boxes around works for me fine in IE6, IE7 and IE8. Can you please try also in IE8 in its IE7-mode?

very nice tool! keep up the good work.

E7 mode in IE8 is working fine after installing IE8. However, it's running fine on the online version. When I downloaded the wwwsqldesigner-2.3.3.zip, then open the index.html locally in IE8 or IE7 mode. It gave me the error saying that "Access is denied. oz.js Line:195 Char:3". And the menu to add the tables did not show up. Anyone is having the same problem??? Thanks again.Since I am actively investigating this app.

Did anyone perform "import from DB" successfully??? I have changed the connect config in php-mysql/index.php...but still did not work...any advice will be greatly appreciated. Thanks.

To solve the DB import problem, please let us know how does the AJAX communication relevant to the import request looks like. Use some tool like Firebug to see what exactly is going on, if the import.php is executed and what is its output.

Hi all, First this is a very nice tool. I used the Firebug to step thru the demo. I am wondering how you decide the X and Y position in the first place for all the boxes(i.e. table entities). I checked the value of the varible "data" after this line "xhr.send(o.data || null); " ..in line 212 of oz.js....the XML returns from the response already contains the e.g. <table x="389" y="357" name="PERSONAS">.. i'd like to see how you calculate those values and where ?? Any explanatiaon will be helpful...thank you very much...

Hi,

table positions are stored in saved xml file. When creating new tables, the user controls (using the mouse) where tables are placed.

but how about the diagram created "import from DB"...just like the web demo. I will assume, once the DB schema is loaded. (I can see the XML file is created in perl withouit X and Y info based on the schema)...then somewhere/somehow in the wwwsqldesinger will calculate the X/Y position and added it to the XML. This is the part I couldnt' figure out...Thanks again.

Hi,

imported schemas are repositioned the same way the "Align tables" button works. It is a trivial grid alignment code, see http://code.google.com/p/wwwsqldesigner/source/browse/trunk/js/wwwsqldesigner.js line #2182 (function alignTables).

ondrej, I really appreciated for your quick response. Ok. 'alignTables'. I got it. However, in your online demo, it seems that the tables are not aling using 'alignTables' function..as I can easily see the positions when first loading your web demo. The boxes' position are more "random". But it turns out very good looking(no overlap). That's basically was my 1st question in place that where/how you align up so nice when loading your web demo. Thanks.

Becuase what I am trying to do it somehow feed the XML markup to your page then ER diagram will be displayed with nice alignment automatically. Thanks again for your excellent work.

Vincent,

the demo page (http://ondras.zarovi.cz/sql/demo/?keyword=default) is not an import, it is a saved design - with tables already placed (by its creator). Therefore, the design contains coordinates for all the tables.

oic. I thought the demo data is retrieved everytime from the DB. I totally understood now. Thanks.

I'm getting some javascript errors when I host the page (version 2.3.3) on my local machine. I can play with the GUI just fine, and the save client stuff works just fine, but when I try to play with the server stuff using the perl-file backend (I don't have PHP installed on my local machine, so...), I get:

Error: uncaught exception: "Access to restricted URI denied" code: "1012" nsresult: "0x805303f4 (NS_ERROR_DOM_BAD_URI)" location: "file:///C:/Program%20Files/wwwsqldesigner/wwwsqldesigner-2.3.3/js/oz.js Line: 213"?

That's under Firefox 3.5.3. I don't think there are any addons installed.

Under Chrome, the input/output window has, after running the list command (just copy and pasted here:) <!DOCTYPE html> <html> <head>

<script> function addRow(name, url, isdir, size, date_modified) { ... lots more code... </script><script>i18nTemplate.process(document, templateData);</script><script>start("C:\\Program Files\\wwwsqldesigner\\wwwsqldesigner-2.3.3\\backend\\python-file\\");</script> <script>addRow("..","..",1,"0 B","10/28/09 6:58:34 AM");</script> <script>addRow("index.py","index.py",0,"440 B","10/28/09 8:49:34 AM");</script>

I'm not sure how to check for errors with chrome.

Internet explorer won't even load the GUI part, I get this error as soon as I load the page and enable dynamic content:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.21022; .NET CLR 3.5.30729; .NET CLR 3.0.30618) Timestamp: Wed, 28 Oct 2009 23:29:24 UTC

Message: Access is denied.

Line: 195 Char: 3 Code: 0 URI: file:///C:/Program%20Files/wwwsqldesigner/wwwsqldesigner-2.3.3/js/oz.js

...

Again, this is just running on my local machine. It's not built to be a web server or anything, so maybe there's just something not installed? PHP certainly isn't installed, for instance. I installed perl just to try out the perl-file backend.

Any insights would be appreciated.

You are probably hitting the cross-domain request restriction. All requests from a webpage MUST be directed to the same domain.

So something with the backends accessing the file system is causing the problem? I'm guessing that this isn't something that would happen on a server, though?

Let me explain what I want to do and maybe you can help...

I'd like to somehow use wwwsqldesigner to allow other programmers on my team to update the database schema. Which means I want to be able to save (and load) a .xml file of the schema on the different programmers' machines, and then each of them can check in that .xml to our versioning system (perforce) if they make changes.

Then, in a table creation script, I want to convert that xml to an actual SQL query and run it on some given MySQL database.

Ideally the xml and the database couldn't get out of sync, so I was thinking of gutting the server save stuff so that instead it saves a file to the "server" (really a place on the client's machine where they've checked out our project) and also sends the SQL query to our database, which I can just hard code the login details for inside whatever script.

There would still be a danger of someone updating the database and not checking in the xml, but the database's data can be regenerated from scratch in a few days if we need to, so it wouldn't be that big of a problem.

I'm not a javascript, perl, python, PHP, or html wizard at all, and I really don't understand web technologies. So forgive any obtuse questions I might ask :) In your opinion, what's the best way to approach this? I can install wwwsqldesigner on our local intranet web server if need be, but I really need to be able to save a .xml file to the local machine of users so that they can easily check it in to perforce.

I've thought of copy+paste from the input/output window, but I have to sell this to the other designers so it needs to be as frictionless as possible.

http://cakeapp.com uses WWW SQL Designer. It is a community to create and save db schemas. It allows also to create a website out of the schema.

I set up the IIS on my local machine and copied wwwsqldesigner to C:\inetpub\wwwroot\ (which apparently gets created with you install IIS?), and I can sort of get it working but I'm still getting some errors.

I can use php-file to load and list just fine. However, when I try to save, it says "Server response: Saved" but the file does not show up in either windows explorer or the "list" button. I can load the default schema, though.

Any ideas how to debug this?

Hmm, seems to work now, so I'm not sure what the problem was before.

Anyway, for the curious, I made this little batch script to automatically register the current directory as a virtual directory on the IIS server (7.0):

@ECHO ON

%systemroot%/system32/inetsrv/appcmd delete vdir "Default Web Site/sqlDesigner"

%systemroot%/system32/inetsrv/appcmd add vdir /app.name:"Default Web Site/" /path:/sqlDesigner /physicalPath:"%CD%"

if ERRORLEVEL 1 GOTO error

goto success

:error

ECHO ERROR!

ECHO Could not mount sql designer as a virtual directory.

ECHO Make sure that you have IIS 7.0 installed properly (you'll need it to debug data fusion anyway)

ECHO Make sure that your IIS 7 install's "app name" is "Default Web Site" (it should be the default)

ECHO And that you're running this script from inside the wwwsqldesigner-2.3.3 directory

pause

goto end

:success

ECHO SUCCESS! Starting web page...

redirect.html

goto end

:end

And then redirect.html simply points to the proper place on the local host:

<html>

<head>

<body><META HTTP-EQUIV="Refresh" CONTENT="0; URL=http://localhost/sqlDesigner/">

<a href = "http://localhost/wwwsqldesigner-2.3.3/">Redirect</a>

</body>

</html>

Muy interezante aplic, muchas gracias

Is there any way to specify the set of valid values for an ENUM?

Just enter them to the "size" field (also known as "stuff in the round brackets after column definition")

Hey, great tool. i was wondering in terms of displaying the ERD diagram via widget on another webpage. it is possible to embed the existing canvas? or has any thought been given to exporting the disgram as a SVG diagram, which then could be embedded on a page. Regards, P

Export to SVG sounds nice, but is far more complicated that it may seem. Only the lines (connectors) are realized via vector graphics; tables and their content are traditional HTML elements without any canonical SVG representation.

I am trying to 'import from db' a php-sqlite db and am getting nowhere. Can anyone help? It just 'hourglasses' forever. Let me know what I can supply you with to help me. I read this document and most of it is regarding MySQL.

I believe that the php-sqlite backend does not support importing. If I remember correctly, the only backend with implemented import is php-mysql.

Hi, Thanks for this useful tool. I'm very new to DB design and all has worked fine (except that it took a while before I realize that "save" doesn't mean a dialog will appear). Just had a little difficulty with quoted words inside quotes; I have to put "\" like: "plaisir d\'amour", otherwise phpMyAdmin doesn't accept it.

Thanks again

Philippe

Excellent tool, thank you very much!!!

I still have one problem. Import/load/save from DB are working fine. XML Save is also working.

The problem is in generating SQL code for the table definitions. I get the error

XML error: Component returned failure code: 0x80004003 (NS_ERROR_INVALID_POINTER) nsIXSLTProcessor.importStylesheet?

Not really sure where to begin with this one.

Am I missing something or is it possible to use the XML definitions to create the tables in the DB ?

I'm using firefox 3.5, MySql? 5.0.67 and Suse 11.1

Thanks for any help

Hi, please tell us what DB datatypes do you use (Options -> Database for new designs). Also, does the problem happen on the sample database view, the http://ondras.zarovi.cz/sql/demo/?keyword=default ?

Thanks for the quick response.

The options is set to mysql. The problem does NOT occur with the sample database. In that case I get the SQL output.

Not sure if this plays a role, but i've tried to set all my collations to be unicode. UTF-8 charset and utf-unicode-ci for the tables and fields.

The problem occurred in the latest version 2.4. I've also checked out the most recent SVN read-only version and the problem still occurs.

Ok, please use Firebug to see if the .xsl template necessary for SQL statement creation is fetched properly. The requested file should be "db/mysql/output.xsl".

I've test a couple variations and determined that the XSL that my browser recieves has one difference. On line 96

<xsl:text> COMMENT='</xsl:text> 

the '=' character is missing.

The file "db/mysql/output.xsl" on my local server contains the '=' character.

When I compare the response from the demo application with my local server copy of "db/mysql/output.xsl" they are identical and the SQL generation works. Only when I get the response from my local server is the '=' character missing in the response.

I thought it may have something to do with PHP short tags which I have activated but I changed this and the error still occurs.

This is a strange problem.

Here are the request and response headers for the request as seen in firebug

Response Headers Date Tue, 01 Dec 2009 08:09:02 GMT Server Apache/2.2.10 (Linux/SUSE) Last-Modified Thu, 05 Nov 2009 08:03:54 GMT Etag "64371-df8-4779b2b200e80" Accept-Ranges bytes Content-Length 3576 Content-Type text/x-xslt Request Headers Host ahp3 User-Agent Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.0) Gecko/20090623 SUSE/3.5.0-2.1 Firefox/3.5 Accept text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8 Accept-Language en-us,en;q=0.5 Accept-Encoding gzip,deflate Accept-Charset ISO-8859-1,utf-8;q=0.7,;q=0.7 Keep-Alive 300 Connection keep-alive Referer http://ahp3/sqldesigner/ Cookie fe_typo_user=41bfbb95d9

I've also just compared the headers I get from the demo with the headers I get from my server and discovered that the response header content type differs. The length of the response differs also by one byte but this is likely due to a new line at the end of the file.

From Demo: Content-Type application/xml From my server: Content-Type text/x-xslt

Any idea what's going on here ?

The missing '=' character is no longer a problem. This was caused by my browser using presumably an older cached version of the file. Now the only difference seems to be the content type.

Problem solved. :)

After reading http://www.se.eecs.uni-kassel.de/~thm/Linux/mozilla-xml-xsl.html and http://www.ibm.com/developerworks/xml/library/x-tipapachexhtml/index.html

I wrote a small .htaccess file and placed it in the "db/mysql" directory.

RewriteEngine on
RewriteCond %{REQUEST_URI} \.xsl$
RewriteRule .* - [T=application/xml]

Now the SQL generation works.

Not sure if this is the best way to solve this problem but is a functioning workaround.

The missing "=" is just some fixed stuff present in latest SVN revisions.

JS unfortunately needs all XML files server with text/xml or application/xml, so your workaround is certainly the best you can do, given these circumstances. Congratulations :)