A Server Database that has more Power and is more affordable than a Desktop database
User vs Multiuser
Internal, 'Casual User'
External, Web user
Web and/or Local Version
Create unlimited PostgreSQL Tables
Fifteen 'base' datatypes + Composite Types (supported within the Editor)
Unique, Check & Foreign key constraints
Import data, export data
Web Manager Graphical Query Builder
Tab 1: Dataset Design (visual & WYSIWYG GUI)
Tab 2: Expressions Editor
Tab 3: Where Clause
Tab 4: Group by Expressions
Tab 5: Having Clause
Web Manager SQL Table Editor & data GUI
Tab 6: Window Clause
The most common Desktop Databases (Filemaker, Access) are single-user. Each additional user requires a license key.- The Web Manager is Multiuser and it's built-in. Each user can be further designated as multi-user.-- You will reduce your licensing fees and application costs with the Web Manager System. To get The latest version of a Desktop Database, you must upgrade and pay more fees.- The Web Manager upgrades all the time, without charge.-- You won't need to pay for - or install upgrades. Desktop Databases live on the desktop. To get to the web requires Server software + Hardware.- The Web Manager lives on the web. -- No additional hardware, software or complex administrative tasks are required. Desktop Databases may strain under a modest load. - The Web Manager is an enterprise class, transactional database. -- The Web Manager uses PostgreSQL which is a workhorse built for a heavy load.
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. You cannot insert, update, or delete on a View but it is useful as it is treated as a Table when writing a Query, allowing you to use the result of 1 query (the View) in an entire different, new Query. This additional flexibility is often helpful. You can also create a Table from a View, capturing the data state at that moment.
The Web Manager gives you the ability to create your own Private Functions, which automatically load into the Expressions Editor of the Query Builder. These Private Functions may be peculiar to your business or simply a means of simplifying a complex but oft-used algorithm. An additional benefit is simplicity. A typical Knowledge Worker would be stymied if required to type a 20 line set of code. A Private Function may be able to reduce it to two or three simple items. The procedural language is PGSQL and is built into every PG install, so any Function you write, is also portable.
Last, though certainly not least are Datasources. Datasources are (simply put), a floating layer of Data that are built off of an original set of data. So a Table could have one or more Datasources, as could a Query or a View. This additional layer allows you to safely get data to webpages without worrying about SQL injection & other evil gnomes. In addition, it allows you to manipulate the 'floating layer', without damaging the original layer. As a result, PHP can be safely written specifically for the floating datasource layer, giving you complete control your data environment. A datasource allows you to put your pseudo data safely onto a web page. A very simple example is above (Preloaded functions list). The list is in a Table -> a simple Select Query sorts the output -> a Datasource was made on the Query and a field placed on the web page. A Datasource is a part of what makes the Web Manager different. It's an important helper that assists in allowing the integration of Data, Content, Print and Cloud via their respective Managers.A Datasource (enormous, learned articles describe how to set one up) is created with the Web Manager with One Click. Just one. It's all you'll need.
The Web Manager Table Editor was built to allow you to create your own tables and design those tables into a database according to your own business needs. This ability to create SQL Tables is much deeper than the 'web norm' and does not require you to set up a web server or hire an Admin. If you can understand a desktop system such as Filemaker or MS Access, you will be able to master the Web Manager's Table Editor in short-order.
We do not impose a specific limit on the number of SQL Tables you may create or the size of those tables, nor do we charge 'per table' or 'per row'. Your Web Manager total account, standard storage space (50 gig) can be increased on demand. However, very large Tables may require specific performance tuning in order to maximize query performance. If this becomes a need, it is possible you will need your own Web Manager Server software on your own box. We can assist in server setup and tuning. Colocation with our data center is also possible.
Foreign Key Constraints- name constraint- referenced table, - columns, - local field, - referenced field, - on delete
- on delete, - on update (no action, restrict, cascade, set null) - set default- full match, simple match- drop- add
You can choose from datatypes that are most appropriate for your Tables.
You can create constraints on your data columns to reduce data entry errors and help to automate tasks.
You can get data into and out of your Tables with pinpoint controls + comprehensive Google integration.
Export data- column selection / removal- xml, xls, csv ( , ; | tab)- local Download- Copy to Files and Open in Web File Manager- Copy to Files and Open in Google Spreadsheet
Import data- bulk import- small file copy paste + delimiter- field mapping- column selection / removal- xml, xls, xlsx, csv ( , ; | tab), user defined- data starts on Row x- ignore Unique constraints- 10 row data preview- skip column
- name columns - drop, add, columns(B-Tree only)
Indexes may speed up query performance for large datasets. PostgreSQL's excellent query planner usually makes Indexes unnecessary. Nevertheless, they are available (B-Tree only).
Table Triggers 'make something happen if something else happens.' Said differently, automatic execution of an event.Table triggers can significantly reduce repetitive tasks. The Web Manger even includes Conditional Triggers, 'make this happen but only if .x.y.z.'.
- name, - before, after- insert, update (on columns), delete
- for each statement, row- edit event conditions- run procedure- parameters
Composite Types- add, define- drop- auto-loads into Table Editor
The Web Manager System also includes a Table Data GUI, that allows you to manually insert rows, delete rows, update rows and their data, per column. In addition, you can sort your view and do a 'quick find' for data in a particular Table. This is helpful for relatively small datasets. The SQL for each change is done automatically in the background and does not commit until you 'Save'. Modifying larger datasets would be done more efficiently by using the Query Builder (below).
The Basic Web Manager (the Web Cruiser) includes three Users, one of which must be your internal Administrator.The other two Users can be 'single users' or Multiuser. If a User is designated as a Multiuser, then several Knowledge Workers can login with the same name and work concurrently. Works quite well. What if you want to add one more 'single user'? You can, at the price of $12.05 per year. How does that compare to getting an additional license key for your current Desktop Database? Pretty good, we suspect.
The Web Manager is set up with a comprehensive Access Control List and Role Based Access Controls (see Content Management). A set of Knowledge Workers can be designated multiuser with a Role that excludes database privileges, yet still do simple data entry tasks via an internal web form.
The Web Manager is set up for Private (log in only) or Public pages where each page and site can be individually marked as Public or Private. External Web users do not count as Users. You can have 539 people viewing your external web pages but they do not count as Users.
The Web Manager creates and uses real SQL. Although there may be slight differences between the Web Manager's PostgreSQL output vs DB2, Oracle, MySQL, etc - the differences (if any) can usually be adapted to a different system. However, the output for desktop databases is often 'unknown', hidden and / or proprietary. 'View the SQL'buttons are available throughout the Web Manager System.
The Web Manager allows you to 'Export a Table Snapshot', which includes all the Table information + Data. This snapshot can be loaded on any PostgreSQL system, anywhere. Same idea for Queries. Try doing that with a desktop database such as Filemaker.
The Web Manager can be set up on your local, server hardware - allowing you greater freedom and flexibility than can be set up here, on the Web. As an example, we will not set up Explicit casts on the web, but with a local version, you could do it yourself. If you'd like your own, local version, let us know. We do not charge for the System - but we do require that we set it up (install). Installation is complex and not simple, point and click. Use Support for a Quote.
The Web Manager's Data Manager component includes a Graphical Query Builder that auto-generates SQL via menu selections, drag and drop and a reduced level of keystroking. This does not mean that the generated SQL will be 'smart', that it will make sense or return your desired results. That's often a function of your own experience. Knowledge Workers can expand their abilities by learning new skills - but - no one wants to learn new skills if it causes damage. That's why we included a special Tab: Test Query. Test Query shows you what would happen if you actually went ahead and applied (committed) the query, which we think is much more prudent than 'Rolling back' (undoing), a truly awful result. We learn from awful results, but it sure is better to learn when they are safe, yet instructive tests. After you are fully satisfied with your Safe Testing - then Save the Query. Early on, use the Query Builder to learn SQL by using unimportant test Tables. Later, use it to apply (commit) SQL on your Production Database. In the future, you may become such an Expert that you won't even need the Query Builder. You'll use Direct SQL. A Knowledge Worker with experience in desktop applications such as Filemaker or MS Access will be able to work effectively within two weeks. PostgreSQL has much deeper capabilities than a proprietary, desktop database so your skills and knowledge will grow. What is below is but a brief summary and list of features. On Load of a new query, a user is given the choices of query 'types' (Select, Update, Insert, Delete or Edit SQL). This 'split-method' makes digesting SQL simpler for Knowledge Workers. A sufficiently well-versed Developer can use 'direct SQL' and simply type (ask Support for more info if this capability is required).
How you pull and combine data from your Tables is largely dependent on the decisions you make from within this Tab. Experiment and safely test your results by using the Test Query Tab.
The Expressions Tab has multiple capabilities. You can be simple and select a specific data columns or you canapply math, string and other calculations to expand on the original selections. The functions are very similar to those of a desktop database such as Filemaker or that you'd use on a spreadsheet. Remember - that a Select query (by itself)does not change the Table data. Once again, experiment.
Preloaded functions (see full list - right)-- Aggregate, Arrays, Conditional, Formatting, Date / Time, Financial, Math,Query builder (Prompt) Statistics, String, Trigonometric, Save/get User Expressions, User functions, Window functions
Preloaded Functions List
Drag & drop tables, Views & Set returning functions output- Joins; Left, Right, Inner, Full, Self- Drag (reassign) Joined columns- Edit Execution order
Click the WHERE Tab and the Expressions Editor will open. You'd then click the item of interest and proceed.If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output. Conceptually (not sql): 'Show me a list of all employees NAMES WHERE their AGE is GREATER THAN 45.'Each of those that are 45 or younger would be omitted 1 x 1 from the displayed list. WHERE is an optional clause.
GROUP BY is also optional and once again, opens the Expressions editor.GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. If one of your Tables has 'gender' and 'birth date', you could calculate an AVERAGE (an aggregate function) AGE per gender. Ideally, only two rows would be displayed, each with the average age.
The Having clause is also optional and once again, opens the Expressions editor.The Having clause is similar to the WHERE clause, but instead, it eliminates group rows that do not satisfy the condition. So, HAVING filters group rows created by GROUP BY.
The Window clause is also optional, though it opens an entirely different editor. The Window clause allows you to dig much deeper into data analysis. Unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. An excellent usage example and Tutorial is here: Postgresql docs
Order by allows you to sort the result set. Although it is optional, your results may appear very random without using Order by. Once again, the expression editor opens for selecting and can be set to either Ascending or descending. In addition, NULLS can be sorted to be FIRST or LAST.
The Query Builder allows you to combine Queries (UNION, INTERSECT & EXCEPT) via manual edit in the SQL Tab. UNION effectively appends query2 to the result of query1 and eliminates duplicates. UNION ALL includes duplicates.INTERSECT returns all rows that are in query1 and in query2 results. Use INTERSECT ALL if you want duplicates.EXCEPT returns all rows that are in the result of query1 but not in the result of query2. You can also manually edit other portions of the autogenerated SQL in this tab.
You can and should test your output in the Test Query Tab, which also allows you to drag columns, resort and resize. The Test Query Tab also allows you to test parameters for the specific query and Chart the results (Segment, Step, Reverse Step, Vertical, Horizontal & Curve).
If you are an advanced user and the Query Builder has become more nuisance than Tool, let us know. We'll show you an example of Direct SQL. An additional alternative is to install your own Web System - Contact Support.
The Query Data View has the many of same features as the Table Editor Data View, with the exception being that it is not possible to Update, Delete or Insert new rows. You'd need to adjust your Query to change your result set output. See the screenshots or video. Better yet, get a Free Trial account and test it.
The above is a very abbreviated Summary of the basic functionality of the Data Manager, the 2nd component of the Integrated Web Manager System.