DOCSEARCHbyAlgolia
Submit Docs

WWW SQL Designer Documentation

repository·master·Indexed 25 days ago

https://github.com/ondras/wwwsqldesigner

A browser-based tool for designing database schemas and E-R diagrams using JavaScript. It supports visualizing, saving, and exporting designs to SQL scripts, as well as importing schemas from existing databases. The project includes multiple PHP backend implementations for CUBRID, MySQL, PDO, and file-based storage to manage design persistence and schema imports.

Tokens
4.9K
Snippets
3
Records
36
Agent score
84%

What's inside WWW SQL Designer

  1. Overview of WWW SQL Designer features

    master

    WWW SQL Designer is a browser-based tool for creating database schemas (E-R diagrams) using JavaScript. It does not require external programs like Flash.

    Key Capabilities:

    • Design: Draw and create database schemas directly in the browser.
    • Features: Supports keys, foreign key constraints, comments, and indexes.
    • Persistence: Save designs for later modification, print designs, or export them as SQL scripts.
    • Import: Retrieve (import) schemas from existing databases.
    • Compatibility: Works in Chrome, Firefox, SeaMonkey, Internet Explorer, MS Edge, Safari, and Opera.

  2. Basic usage of WwwSQLDesigner

    master
    1. Start: Open Index.html to launch the application.
    2. Warning: Never use Page Refresh (F5), as all unsaved data will be lost.
    3. Exporting FoxPro Scripts: Use the Save button to generate a FoxPro script containing CREATE TABLE commands.
    4. Saving/Loading Work:
      • To save your work to a text file, use the Save XML button to generate XML, then copy-paste it into a text file.
      • To resume work, use the Read XML button to load your saved XML data.

  3. Install VFP9 support in WwwSQLDesigner

    master

    VFP9 support is included as a standard part of WwwSQLDesigner. To enable it, navigate to the Settings menu and select vfp9 as your database type.

    If you need to customize the generated SQL script, you can patch or replace the XSL stylesheet located at db\vfp9\output.xsl.

  4. Install WWW SQL Designer using Docker

    master

    You can containerize WWW SQL Designer using Docker.

    1. Build the Docker image from the repository root.
    2. Run the container in detached mode, mapping port 8080.
    3. Access the application in your browser at http://127.0.0.1:8080.
    docker build -t wwwsqldesigner .
docker run -d -p 8080:8080 wwwsqldesigner
# Visit http://127.0.0.1:8080

  5. Install WWW SQL Designer locally via npm

    master

    To run WWW SQL Designer on your local machine, you can use http-server to serve the files.

    1. Install the http-server package globally using npm.
    2. Navigate to the root of the repository.
    3. Start the server.
    4. Access the application in your browser at http://127.0.0.1:8080.
    npm install http-server -g
http-server
# Visit http://127.0.0.1:8080

  6. Configure VFP9 fields and data types

    master

    When adding fields for VFP9 support, note the following specific behaviors:

    Using the 'Default' Textbox

    In the Field adding interface, the Default textbox is used to enter FoxPro keywords that follow the field definition.

    • The keyword DEFAULT is not automatically added. To include it, you must explicitly write DEFAULT value (e.g., DEFAULT 0).
    • To set a null default, write DEFAULT .NULL. instead of just NULL to avoid FoxPro errors.

    Handling Binary Data

    Binary data types are not explicitly listed. To simulate binary behavior, use standard types and add the NOCPTRANS keyword in the Default textbox:

    • Use Varchar, Character, or Memo.
    • Set Default to NOCPTRANS.

    Integer Types

    The Integer (not key) type differs from primary/foreign keys only in its visual color in the designer; it generates a standard Integer in the script.

  8. Configure MySQL backend connection settings

    master

    The PHP backend uses two global configuration functions to define database connection constants. You must call these before instantiating the mysqlDB class to set the SERVER, USER, PASSWORD, DB, and TABLE constants.

    • setup_saveloadlist(): Configures connection details for saving and loading database designs. It defines the TABLE constant which specifies where design keywords and data are stored.
    • setup_import(): Configures connection details for importing existing database schemas (typically targeting information_schema).

  9. Configure PostgreSQL backend for WWW SQL Designer

    master

    To use this backend, you must configure the database connection parameters within the PHP script. There are two distinct configuration contexts:

    1. Application Database (Save/Load): Used for storing and retrieving saved database designs. Configure setup_saveloadlist() with the credentials for the table where design XML data is stored.
    2. Import Database: Used when importing an existing PostgreSQL database into the designer. Configure setup_import() with the credentials for the target database you wish to scan.

    Important PostgreSQL Notes:

    • When prompted for a database name in the UI, enter any value.
    • PostgreSQL does not have an 'auto increment' checkbox; instead, use the serial type for columns. When importing, look for an Integer type with a default value like nextval(...).
    • User-defined types may not be captured if they are not defined in ../../db/postgresql/datatypes.xml.
    // Example configuration for the application's storage
function setup_saveloadlist() {
    Define("HOST_ADDR", "localhost");
    Define("PORT_NO", "5432");
    Define("DATABASE_NAME", "wwwsqldesigner");
    Define("USER_NAME", "wwwsqldesigner");
    Define("PASSWORD", "xxx");
    Define("TABLE", "wwwsqldesigner");
}

// Example configuration for importing an existing database
function setup_import() {
    Define("HOST_ADDR", "localhost");
    Define("PORT_NO", "5432");
    Define("DATABASE_NAME", "testdb");
    Define("USER_NAME", "username");
    Define("PASSWORD", "??????");
}

  10. Set up the PostgreSQL backend for wwwsqldesigner

    master

    To use a PostgreSQL server to save and load your database diagrams, follow these steps to configure the database and the application:

    1. Database Setup:

      • Create a login role named wwwsqldesigner with the password xxx.
      • Create a database named wwwsqldesigner with the following properties:
        • Owner: wwwsqldesigner
        • Encoding: UTF-8
        • Template: template0
      • Connect to the wwwsqldesigner database and execute the database.sql script found in the backend/php-postgresql/ directory.

    2. Application Configuration:

      • Locate and edit js/config.js.
      • Change the default database to postgresql.
      • Add postgresql to the list of available backends.

  11. Configure CUBRID PHP backend connection settings

    master

    The CUBRID backend implementation uses internal functions to define connection parameters. To use this backend, you must ensure the cubrid_mysql_compat.php library is available and that the following constants are correctly configured within the setup_saveloadlist() or setup_import() functions:

    • SERVER: The database server address and port (e.g., localhost:30000).
    • USER: The database username (e.g., dba).
    • PASSWORD: The database password.
    • DB: The database name.
    • TABLE: (For save/load operations) The table name used to store design data (e.g., wwwsqldesigner).

  12. Customize VFP9 generated script parameters

    master

    The generated VFP script supports optional parameters that modify the behavior of CREATE TABLE commands. These parameters allow you to control keywords, execution flow, and file paths.

    ParameterDescription
    teLongNameAllows generating FREE or NAME keywords.
    tcCommandA command that can be fired immediately after each CREATE TABLE (with the new table selected).
    tcPathControls the path where tables are created.

    Handling Comments via tcCommand

    Comments are not copied directly into the target database. Instead, they are stored in the lcTableComment variable (for table comments) and the lacComments array (for field comments) before the tcCommand is executed. You can access these in your custom FoxPro procedure.

    Example tcCommand usage:

    = MyProc( ALIAS(), m.lcTableComment, @lacComments )

    This allows your MyProc() procedure to access the current alias, the table comment, and the array of field comments.