CYBERTEC provides a set of extensions which can be used to simplify the process of building applications. In this section you’ll learn which extensions exist and what they are capable of doing.

Purpose:

Store telegram posts

ER model:

Description:

The extension consists of just one table. The content of the message is stored in the “payload” column.

Purpose:

This extension provides a generic extension to store log entries and events. The idea is to generalize event messages.

ER model:

Description:

The database user is stored as text and not as an object id. The reason is that we want to support “DROP USER” in PostgreSQL and allow for more generic usage.

The log_level is represented as enum type in PostgreSQL which allows for sorting. The following sort order is used:

• INFO
• NOTICE
• LOG
• WARNING
• ERROR
• FATAL

Note that PostgreSQL will provide this order automatically. It’s also possible to filter easily.

Purpose:

This module offers a simple way to manage blogs, posts and authors.

ER model:

Description:

Authors are identified by email address (unique field). The table is structured in a way it can be extended easily (fields for phone, etc.). Posts contain boolean field to identify the status (proofreading yes / no). Blogs have a title. The payload is intentionally not part of the table as a blog might need various fields to store the content (payload, images, etc.) - those are supposed to be added by the ER design person.

Sample data isn’t included.

Purpose:

This module can handle newsletter as well as blacklists.

ER model:

Description:

In email marketing a “Robinson list” is a list indicating who isn’t supposed to receive messages. It’s basically a “blacklist”. In the “email list” itself, the information stored indicates whether newsletters are allowed or whether or not an address is marked as spam. However, emails can still bounce. In this case the bounce message is stored in the newsletter recipient table.

Sample data isn’t included.

Purpose:

This is a basic module to store web server logs in a table.

ER model:

Description:

The module consists of just one table. It stores the typical data one would find in a web server log as a database entry. We are using PostgreSQL optimized data types to handle IPs. The http status is stored as a simple integer value. Note that the “tstamp” column represents the insert-time into PostgreSQL (default value = clock_timestamp() ). The timestamp as observed by the webserver is stored in “request_time”.

Sample data isn’t included.

Purpose:

An extension to store twitter posts.

ER model:

Description:

The module consists of just one table capable of storing twitter messages. This is basically a 1:1 copy of the Twitter API (which is also the foundation of the twitter_fdw).

Sample data isn’t included.

Purpose:

Handling extensions, campaigns, keywords and clicks.

ER model:

Description:

The model allows storing keywords (t_keyword) in various languages (t_language). Those keywords are associated with campaigns. For each campaign the extension analyzes how many keywords were clicked and how often on each day.

Purpose:

Storing the energy content of food.

ER model:

Description:

We store the energy content of food as measured in calories. By default the table is empty. However, when looking at the content of the extension in Git you’ll notice that sample data is available but it’s commented out. It should be easy to load this information if needed.

Purpose:

Storing elements in the periodic table.

ER model:

Description:

We store elements in the periodic table including a complete list. The name of the element is stored in English.

Purpose:

A sample app to store speeding tickets.

ER model:

Description:

We store information about speeding tickets. The purpose of the extension is more for educational purposes.

Purpose:

Sample data taken from the oil industry.

ER model:

Description:

This table contains data sets (644 records) from the oil industry. The purpose of this extension is mostly educational. It’s ideal to teach windowing functions, analytics and time series analysis.

Purpose:

Handling basic room reservations.

ER model:

Description:

This module stores hotel bookings. It associates hotel entries with bookings entries. Note that there is no foreign key relation between customers and bookings. We do so to ensure that customers can be deleted without destroying historic bookings.

Purpose:

An ER model to handle rental cars

ER model:

Description:

This extension helps to manage rental cars. It stores information about categories, models, locations as well as drivers and bookings. It’s a blueprint for helping people to get started quickly.

Note that exclusion operators are used to avoid overlapping bookings. Check out our blog posts dealing with exclusion operators to learn more.

Purpose:

Handle sensors and time series.

ER model:

Description:

The model consists of two tables. Sensors and sensor data. It’s a blueprint to getting started and to store more comprehensive information in a simple manner. Note that in case you want to store billions of rows, partitioning the sensor_data table is an option for scalability reasons. CYPEX is perfectly capable of handling partitioning.

Purpose:

A basic model to handle agents and sales orders.

ER model:

Description:

This module is mainly used for educational purposes. It stores information about agents, customers and customer orders. It’s a basic 3-table model which can be expanded upon.

Purpose:

A model to handle music

ER model:

Description:

This module is mostly a way to demonstrate the capabilities of CYPEX in an educational setting. It stores information about bands, songs, playlists and a lot more. It’s one of the bigger extensions used for educational purposes.

Purpose:

A model to handle friendship.

ER model:

Description:

This module describes friendship relations. A list of people is used to connect friendship relations (m : n). It’s an ideal start to model all kinds of relationships.

Purpose:

A powerful module to handle unit conversions

ER model:

Description:

This module contains functions which can be used in CYPEX to perform all kinds of unit conversions (e.g. km -> meters and similar). It contains a handful of stored procedures as well as a config table holding information about conversion rules.

Converting a numeric value from one unit to some other unit:

CREATE OR REPLACE FUNCTION convert_units(
        value 		numeric,
        input_units 	varchar(50),
        output_units 	varchar(50)
)
RETURNS numeric …

The following listing shows, how meters can be converted to kilometers:

test=# SELECT convert_units(100, 'm', 'km');
 convert_units
---------------
         0.100
(1 row)

Check if a unit can be converted or not (km -> meter is ok, km -> gallons isn’t). The function will error out in case a conversion is impossible:

CREATE OR REPLACE FUNCTION check_units(
    input_units 	varchar(50),
        output_units 	varchar(50)
)
RETURNS void …

Convert a unit to its standard unit:

CREATE OR REPLACE FUNCTION convert_units_from_si(
    value 			numeric,
    output_units 		varchar(50)
    )
 	RETURNS numeric …

The following example shows how 100 meters can be converted to the standard unit (1 km units):

test=# SELECT convert_units_from_si(100, 'km');
 convert_units_from_si
-----------------------
                 0.100
(1 row)

The entire process is driven by configuration tables:

INSERT INTO t_units_conversion_list  VALUES
-- temperature
    ('F','farenheit','K','kelvin','temperature', .55555555, 255.37222222, 1.8, -459.67),
    ('C','celsius','K','kelvin','temperature', 1.0, 273.15, 1.0 , -273.15),
    ('R','rankine','K','kelvin','temperature', .55555555, 0.0, 1.8, 0.0);

-- insert values without offset
INSERT INTO t_units_conversion_list(nonsi_unit, nonsi_name, si_unit,
      si_name, class, factor_to_si, factor_to_nonsi)
VALUES
      ('km', 'kilometre', 'm', 'metre', 'length'  ,1000.,       0.001),
      ('hm', 'hectometre', 'm', 'metre', 'length' ,100.,        0.01 ),
      ('dam', 'decametre', 'm', 'metre', 'length' ,10.,         0.1  ),

…

If further conversions are needed, add entries to the config tables.

Purpose:

Storing addresses given ISO countries

ER model:

Description:

A module to handle address data. To make this module work, the CYPEX country_list extension must be installed.

Purpose:

Country lists and ISO codes

ER model:

Description:

This module provides country lists and ISO codes. All officially recognized countries are listed including various incarnations of ISO codes. It allows users to quickly fill up “drop-downs” containing country codes without having to load those lists manually. Country names are represented in English and German. However, other languages can be added easily.

Purpose:

Provide basic and commonly used data type abstractions

ER model:

No tables needed.

Description:

The following types are provided by the extension:

• color_code: Hex codes to store colors
• Format examples: #00ccff, #039, ffffcc
• alphanumeric_string: A string which only supports ASCII characters and numbers (no blanks, etc.)
• password_text: At least 1 lowercase, 1 uppercase, 1 number, 1 special character and at least 8 characters long
• url: Matches http and https URLs.
• domain: Match domain names
• credit_card: Match card numbers
  • Amex Card
  • BCGlobal
  • Carte Blance
  • Diners Club
  • Discover Card
  • Insta Payment Card
  • JCB Card
  • Korean Local Card
  • Laser Card
  • Maestro Card
  • Mastercard
  • Solo Card
  • Switch Card
  • Union Pay Card
  • Visa Card
  • Visa Master Card
• hex_value: Hex values such as #a3c113
• number_positive: Positive numbers (NULL allowed, 0 allowed)
• number_negative: Negative numbers (NULL allowed, 0 allowed)
• int8_positive: Positive 8 byte integer (NULL allowed, 0 allowed)
• int8_negative: Negative 8 byte integer (NULL allowed, 0 allowed)

Purpose:

Ready-to-use currency lists

ER model:

Description:

This extension provides a ready-to-use list of commonly used currencies (EUR, USD, GBP, CHF) which can easily be extended. v_currency_list provides a list of those currencies given your default CYPEX language determined by cypex.current_language(). The default language of CYPEX can be changed in the config table of CYPEX.

Purpose:

Basic functions to calculate loan-related information

ER model:

No tables needed

Description:

The following function is provided to calculate monthly payments:

CREATE OR REPLACE FUNCTION loan_calculate_rate(
    v_sum 		numeric,
    v_interest_rate 	numeric,
    v_months 		int
)
 	RETURNS numeric …

Here’s a sample:

SELECT interest_rates.loan_calculate_rate(1000.0, 5.0, 12);
 loan_calculate_rate
---------------------
             85.6075
(1 row)

Purpose:

Basic functions to manage hotel room bookings

ER model:

Description:

The model describes a basic hotel including bookings. It’s designed as a starting point for more comprehensive models.

Purpose:

Basic functions to manage inventory

ER model:

Description:

The inventory model describes brands, products, product types as well as inventory in an easy-to-use way. It allows users to various products and provides generic ways to handle product attributes.

Purpose:

Manage trainers, training courses as well as customers.

ER model:

Description:

This model handles training related issues. Trainers can speak various languages and are assigned to different types of training. Training can take place in different locations, which are assigned to customers. Again this is a blueprint to develop things into more complex models.

Purpose:

Manage GPS tracks

ER model:

Description:

Used to store vehicles (which can be a special category of vehicles). Those vehicles are then assigned to GPS tracks. CYPEX can then visualize those tracks using GeoJSON documents.

Purpose:

Handle exchange rates

ER model:

Description:

This module offers an easy way to store currencies as well as exchange rates. Currency names can be translated to ensure multi-language support. The price is stored for any point in time.

The currency_list extension is required for this module.

Purpose:

Manage team lists

ER model:

Description:

The idea of this module is to give users the ability to store team lists. Employees are assigned to a list of departments. Column lists can easily be extended.

Purpose:

Handle TODO items and jour fix meetings

ER model:

Description:

The core idea is to give users the ability to handle TODO items coming out of team meetings related to many different topics.

Purpose:

Manage sponsors and logistics for a conference

ER model:

Description:

Conferences can be challenging. This is especially relevant in case it’s necessary to coordinate sponsoring as well as conference logistics. This model handles conference sponsorship-related tasks and helps to store information about conference logistics. Which items have been sent to which conference? What is the tracking data? etc.

Purpose:

Manage simple TODO items.

ER model:

Description:

TODO items are assigned to TODO types as well as to users who are supposed to handle those items. It’s a simple yet efficient model to store tasks.

Purpose:

Manage stock prices

ER model:

Description:

The stock ticker module has been modeled after the Yahoo Finance API. It can be used directly to store data coming from this API.

Purpose:

Manage prices for engineers, depending on the region

ER model:

Description:

Often prices depend on regions, type of service and so on. The consulting_prices extension contains an ER-model which reflects those aspects of pricing and allows you to store prices depending on service types and region.

Purpose:

A basic model to handle rating agencies

ER model:

Description:

A basic data model capable of storing information about rating agencies.

Purpose:

Store bank accounts

ER model:

Description:

This model is a comprehensive module which is capable of managing bank accounts as well as many aspects of infrastructure. It can handle:

• User logins
• Security questions
• Account types
• Interest rates
• Account status
• Failed transactions
• Error logs
• Employees
• Transaction types
• Transactions
• Accounts
• Customers

Sample data is available.

Purpose:

Accounting and invoicing

ER model:

Description:

This model contains a simple bookkeeping infrastructure which consists of addresses, customers, invoice payments, invoices as well as invoice components (“lines”). The presence of the PostgreSQL contrib package is needed to satisfy the dependency on the citext extension (= “case insensitive text”).

Purpose:

Managing support customers

ER model:

Description:

This module offers users the capability to handle support customers and contracts.

Purpose:

Storing products and product categories

ER model:

Description:

Products can have various categories and can be assigned to attributes. Prices can be in varying currencies and can be valid for different periods of time.

Purpose:

Ready-made salutations

ER model:

Description:

The “salutations” module will provide a list of ready-to-use salutations (e.g. “Mr”, “Mrs”, etc.). It helps to reduce the effort to store addresses and other person- related data.