PHPackages chippyash/db-plantuml - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. chippyash/db-plantuml ActiveProject[Database & ORM](/categories/database) chippyash/db-plantuml ===================== Support for creating DB logical and physical diagrams using the awesome PlantUml 1.1.2(3y ago)2142[1 PRs](https://github.com/chippyash/db-plantuml/pulls)BSD-3-ClausePHPPHP ^8.0CI failing Since Jan 22Pushed 3y ago1 watchersCompare [ Source](https://github.com/chippyash/db-plantuml)[ Packagist](https://packagist.org/packages/chippyash/db-plantuml)[ RSS](/packages/chippyash-db-plantuml/feed)WikiDiscussions master Synced 3w ago READMEChangelogDependencies (3)Versions (11)Used By (0) PlantUML Database Support ========================= [](#plantuml-database-support) chippyash/db-plantuml --------------------- [](#chippyashdb-plantuml) ### What [](#what) 1. Provides Plantuml !include files that you can use to create logical and physical database diagrams 2. Provides a (PHP) utility to turn the diagrams into DDL files to create your database. PlantUml Support ---------------- [](#plantuml-support) - Tested with PlantUML V1.2021.12 - Graphviz version 2.43.0 2 definition files - DatabaseLogical.iuml - DatabasePhysical.iuml ### Goal [](#goal) To quickly create logical db designs that end users might understand and convert them to initial physical designs with additional functionality that can be turned into something developers will understand, i.e. a SQL Schema ### Demo [](#demo) - Open `examples\User-Logical.puml` and display the drawing - Open `examples\User-Physical.puml` and display the drawing Look at both files. The only difference between them is - a/ the included file defined at top of definition - b/ physical drawing has additional features - View - Trigger & trigger() - (Stored) Proc and uses() The original logical definition was copy-pasta'd from the logical file to the physical file and renders automatically in physical form. Take a look at the `dist\*.iuml` files. It's the subtle differences between the function declations that allows the transform to happen. ### Installation [](#installation) There is no real need to install this code base. You can access the required files remotely using the `!includeurl` directive. *You do however, need to install [PlantUml](http://plantuml.com/)*! ### Usage [](#usage) 1. Create your logical model to represent customer/user view 2. Copy logical model file to physical model file and change the !include statement. Amend as necessary. 3. (Optional) Generate the SQL DDL file to create your database #### Logical Models [](#logical-models) `!include ../dist/DatabaseLogical.iuml` or `!includeurl https://raw.githubusercontent.com/chippyash/db-plantuml/master/dist/DatabaseLogical.iuml` [![examples/User-Logical.puml](examples/User_Logical.png)](examples/User_Logical.png)Logical model will display the following differently to their representation in a Physical model ##### Components [](#components) ``` Table(alias, name="", description="") //preferred or Entity(alias, name="", description="") //psuedonym for Table Type(alias, name="", description="") //data type (enum) Set(alias, name="", description="") //data type (set) ``` ##### Data types [](#data-types) ``` string(l=30) char(l=12) text() date() time() datetime() int(l=8) real() bool() ``` NB string and int lengths do not display in logical models, but specify them if you know them as they will be displayed in physical models. You can display other data types directly in your entity classes e.g. ``` Table(t1, foo) { bin_data blob } ``` ##### Type modifiers [](#type-modifiers) ``` not_null(name, type) unsigned(name, l=8) //unsigned integer with a name _unsigned(l=8) //unsigned integer, use with primary() etc ``` ##### Indexes and keys [](#indexes-and-keys) ``` primary(name = 'id', type=_unsigned(8), auto=1) //primary key idx(name, type=int()) //non unique index - one member idx2(name1, name2) //non unique index - two members idx3(name1, name2, name3) //non unique index - three members ``` ##### Relationships [](#relationships) ``` zeromany(from, to, verb, tNum='n') onemany(from, to, verb, tNum='n') manymany(from, to, verbFrom, verbTo) oneone(from, to, verb, keyname='id', type=int()) depends(from, to, colname) //enum and set dependencies ``` There is an internal relationship `_join` ``` _join(from, to, verb, fNum, tNum) e.g. _join(a, b, has, 0, n) ``` You can use this to fine tune relationships as required. fNum & tNum accept an integer or 'n'. #### Physicals Models [](#physicals-models) `!include ../dist/DatabasePhysical.iuml` or `!includeurl https://raw.githubusercontent.com/chippyash/db-plantuml/master/dist/DatabasePhysical.iuml` [![examples/User-Physical.puml](examples/User_Physical.png)](examples/User_Physical.png) Use the same statements as per Logical models. In addition there are: ##### Components [](#components-1) ``` Trigger(alias, name) e.g. Trigger(t1, UserUpdate) { beforeUpdate() afterUpdate() beforeInsert() afterInsert() beforeDelete() afterDelete() } Proc(alias, name) e.g. Proc(p1, StoredProcs) { addUser(uid, guid) } View(alias, name) e.g. View(v1, sessions) { select(user_id, data\nfrom user, session\njoin id on user_id) } ``` ##### Indexes and keys [](#indexes-and-keys-1) ``` foreign_key(tableName, to, type=int(), suffix='_id') ``` Foreign keys are automatically generated where appropriate between Tables in your model. You may need to explicitly declare them for Tables that are off model. ##### Relationships [](#relationships-1) ``` function triggers(from, to) //table actions trigger function uses(from, to) //proc uses table ``` These `uses` relationships is purely informational. ##### User defined data types [](#user-defined-data-types) Whilst many SQL RDMS natively support user defined data types, many do not, including the ever popular MySql/MariaDb. You can achieve this functionality in PlantUml yourself. ``` !define guid() char(36) !unquoted procedure orgId() orgId int(8) UNSIGNED NOT NULL index(orgId) !endprocedure ``` and then use them in your Table definitions: ``` Table(invpayee, cr_invoice_payee, Invoice Payee) { primary(entityId, guid(), 0) orgId() } ``` See (PlantUml Preprocessing)\[\] for more ideas. Diagram to SQL conversion ------------------------- [](#diagram-to-sql-conversion) A PHP utility CLI program that will convert your physical diagram to SQL DDL. MySql is supported at this release. - Installation - "See Installation - production use" below ### Basic usage [](#basic-usage) `bin/pumldbconv g ./examples/User-Physical.puml ./out.sql` Which will convert the example physical diagram into SQL looking thus: ``` CREATE TABLE `user` ( `id` INT(8) PRIMARY KEY AUTO_INCREMENT, `tag` VARCHAR(30), `username` VARCHAR(30), `bar` TEXT NOT NULL, `password` VARCHAR(30) NOT NULL ); CREATE TABLE `session` ( `id` INT(8) PRIMARY KEY AUTO_INCREMENT, `data` text NOT NULL, `user_id` INT(8) ); CREATE TABLE `account` ( `logon` VARCHAR(30), `user_id` INT(8) ); CREATE TABLE `profile` ( `age` SMALLINT, `birthday` DATETIME NOT NULL, `id` INT(8) PRIMARY KEY, `gender` enum('MALE','FEMALE') NOT NULL, `fav_colours` set('RED','BLUE','GREEN') NOT NULL ); CREATE TABLE `group` ( `id` INT(8) PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL ); CREATE TABLE `user_group` ( `user_id` INT(8), `group_id` INT(8) ); CREATE INDEX idx_att34 ON user (`tag`); CREATE UNIQUE INDEX idx_att35 ON user (`username`); CREATE INDEX idx_att36 ON user (`username`,`password`); ALTER TABLE `session` ADD FOREIGN KEY fk_att40 (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; CREATE INDEX idx_att43 ON account (`logon`); ALTER TABLE `account` ADD FOREIGN KEY fk_att44 (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; ALTER TABLE `user_group` ADD FOREIGN KEY fk_att54 (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; ALTER TABLE `user_group` ADD FOREIGN KEY fk_att55 (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; CREATE VIEW `sessions` AS SELECT user_id, data from user join session on (user.id = session.user_id); DELIMITER // CREATE PROCEDURE sp_StoredProcs_addUser(IN uid INT, IN guid INT) BEGIN # complete proc body and parameter typing END; DELIMITER ; CREATE DEFINER=`root`@`localhost` TRIGGER UserUpdate_beforeUpdate BEFORE UPDATE ON `user` FOR EACH ROW BEGIN # complete trigger body and declaration END; CREATE DEFINER=`root`@`localhost` TRIGGER UserUpdate_afterUpdate AFTER UPDATE ON `user` FOR EACH ROW BEGIN # complete trigger body and declaration END; ``` The program assumes that your plantuml.jar is located at: - /usr/share/plantuml/plantuml.jar for Linux - "C:/Program Files/Java/jars/plantuml.jar" for Windows If this is not the case, you can specify the folder location with the `-p` flag e.g.: `bin/pumldbconv g -p /usr/local/javajars ./examples/User-Physical.puml ./out.sql` ### Installation - production use [](#installation---production-use) You will need PHP8.0+ with the xsl and xml extensions installed to use this program. - Clone/Fork this repo or grab an archive and unzip it - Move the bin/pumldbconv file into a directory in your path, perhaps `/usr/local/bin` - Check that you can execute it with `pumldbconv -V` - Remove the source files if no longer required ### Installation - development [](#installation---development) Caveat: These instructions assume a Linux OS. (If you are a Windows/Mac user, please consider adding installation and usage instructions to this repo by way of a pull request.) - Clone/Fork this repo or grab an archive and unzip it - Install [Composer](https://getcomposer.org/) - Install the PHP XSL extension e.g. For Debian based Linux ``` sudo apt install php-xsl ``` PHP normally has the XML extension built-in, but you may need to install it manually. ``` sudo apt install php-xml ``` - run `composer install` ### Building [](#building) ``` make build ``` Will build a new PHAR executable in the bin directory. You will need [Box](https://github.com/humbug/box) installed and your `php.ini` settings modified to build phar files (off by default). ### Changing the library [](#changing-the-library) 1. fork it 2. write the test 3. amend it 4. do a pull request Found a bug you can't figure out? 1. fork it 2. write the test 3. do a pull request NB. Make sure you rebase to HEAD before your pull request Or log an issue ticket in Github. Where? ------ [](#where) The library is hosted at [Github](https://github.com/chippyash/db-plantuml). It is available at [Packagist.org](https://packagist.org/packages/chippyash/db-plantuml) License ------- [](#license) This software is licensed under the [BSD-3 Clause license](LICENSE.md). History ------- [](#history) V0.0.0 Initial alpha release V0.0.1 Alpha release with DDL generator V1.0.0 Upgrade to use PHP 8 and latest version of PlantUML V1.1.0 Add UNSIGNED attribute support ### Health Score 31 — LowBetter than 66% of packages Maintenance20 Infrequent updates — may be unmaintained Popularity13 Limited adoption so far Community12 Small or concentrated contributor base Maturity69 Established project with proven stability Bus Factor1 Top contributor holds 60% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~102 days Recently: every ~229 days Total 10 Last Release 1436d ago Major Versions 0.0.6 → 1.0.02021-10-26 PHP version history (2 changes)0.0.1PHP ^7.2 1.0.0PHP ^8.0 ### Community Maintainers ![](https://www.gravatar.com/avatar/198575568597b367c8b285de16d278018c8cf292c6c75c535270135c1eea0561?d=identicon)[chippyash](/maintainers/chippyash) --- Top Contributors [![chippyash](https://avatars.githubusercontent.com/u/983560?v=4)](https://github.com/chippyash "chippyash (18 commits)")[![akzincsystems](https://avatars.githubusercontent.com/u/73334506?v=4)](https://github.com/akzincsystems "akzincsystems (6 commits)")[![zinc-dev](https://avatars.githubusercontent.com/u/13366930?v=4)](https://github.com/zinc-dev "zinc-dev (6 commits)") --- Tags databaseplantumldiagrams ### Code Quality TestsPHPUnit ### Embed Badge ![Health badge](/badges/chippyash-db-plantuml/health.svg) ``` [![Health](https://phpackages.com/badges/chippyash-db-plantuml/health.svg)](https://phpackages.com/packages/chippyash-db-plantuml) ``` ### Alternatives [perplorm/perpl Perpl is an improved and still maintained fork of Propel2, an open-source Object-Relational Mapping (ORM) for PHP. 2411.8k](/packages/perplorm-perpl) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)