Examples - jazd/Business GitHub Wiki
Simple Examples
Individuals
People
People View
SELECT fullname, goesBy, birthday, in_days, death
FROM People
WHERE individual IN (3, 14, 16)
;
fullname | goesby | birthday | in_days | death |
---|---|---|---|---|
Stephen Arthur Jazdzewski | Steve | 2014-11-22 | 64 | |
Candace Lauren Fish | Candy | 2015-8-27 | 342 | 2008-10-23 00:00:00 |
Franklin Patrick Herbert Jr | Frank | 2014-10-8 | 19 | 1986-02-11 00:00:00 |
Actual Individual table records.
SELECT id, name, suffix, goesBy,
DATE(birth) AS birth, DATE(nameChange) AS nameChange,
DATE(death) AS death
FROM Individual
WHERE id IN (3, 14, 16)
ORDER BY id, nameChange
;
id | name | suffix | goesby | birth | namechange | death |
---|---|---|---|---|---|---|
3 | 1 | 22 | 1963-11-22 | |||
14 | 11 | 1942-08-27 | 1953-01-01 | |||
14 | 11 | 28 | 1942-08-27 | 1961-01-01 | ||
14 | 12 | 28 | 1942-08-27 | 1978-01-01 | ||
14 | 13 | 28 | 1942-08-27 | 1985-01-01 | ||
14 | 14 | 28 | 1942-08-27 | 2008-10-23 | ||
16 | 16 | 140 | 30 | 1920-10-08 | 1986-02-11 |
Id 3, Active entry, single record name change and death fields are NULL.
Id 14, Inactive entry, all records have non-NULL name change and death fields. Notice that all the name changes, have been preserved.
When Id 14's name changed in 1953, the current entry name change field was updated from NULL to '1953-01-01' and a new entry record was inserted with the new name (12) and the initial name change and death fields set to NULL.
Entities
Entity View
SELECT individual, entity, name, goesBy, DATE(formed) AS formed, aged
FROM Entities
WHERE individual IN (10,1002)
ORDER BY individual
;
individual | entity | name | goesby | formed | aged |
---|---|---|---|---|---|
10 | 1 | International Business Machines, Inc. | IBM | 1911-06-16 | 110 years 9 mons 4 days |
1002 | 5 | Oracle Corporation | Oracle | 1977-06-16 | 44 years 9 mons 4 days |
Actual Individual table records.
SELECT Individual.id,
DATE(birth) AS birth,
DATE(nameChange) AS nameChange,
GoesBy.value AS goesBy,
Entity.name
FROM Individual
JOIN Entity ON Entity.id = Individual.entity
LEFT JOIN Given AS GoesBy ON GoesBy.id = Individual.goesBy
WHERE Individual.id IN (10,1002)
ORDER BY Individual.id, nameChange
;
id | birth | namechange | goesby | name |
---|---|---|---|---|
10 | 1911-06-16 | IBM | International Business Machines, Inc. | |
1002 | 1977-06-16 | 1979-01-01 | Software Development Labs Incorporated | |
1002 | 1977-06-16 | 1983-01-01 | Relational Software, Inc | |
1002 | 1977-06-16 | 1995-01-01 | Oracle Systems Corporation | |
1002 | 1977-06-16 | Oracle | Oracle Corporation |
Tables used in People and Entities VIEWs
Entity Name Change
To change Individual 10's name to 'International Business Machines Corporation'
Mark the current entry for Id 10 as changed.
UPDATE Individual SET namechange = DATE(NOW())
WHERE id = 10 AND namechange IS NULL AND death IS NULL
;
Insert a new entry for Id 10.
INSERT INTO Individual (id, entity, goesBy, birth)
SELECT 10,
GetEntityName('International Business Machines Corporation'),
Individual.goesBy,
Individual.birth
FROM Individual WHERE id = 10 AND namechange = DATE(NOW()) LIMIT 1
;
The Entities VIEW shows the latest namem for Individual 10.
SELECT name, goesBy FROM Entities WHERE individual = 10
;
name | goesby |
---|---|
International Business Machines Corporation | IBM |
Actual Individual table records.
SELECT id, name, suffix, goesBy,
DATE(birth) AS birth, DATE(nameChange) AS nameChange,
DATE(death) AS death
FROM Individual
WHERE id = 10
ORDER BY nameChange
;
id | name | suffix | goesby | birth | namechange | death |
---|---|---|---|---|---|---|
10 | 100000 | 1911-06-16 | 2019-11-17 | |||
10 | 100000 | 1911-06-16 |
Email addresses are split into their component parts using a stored
procedure. The email address [email protected]
is actually stored in
the three Email table fields username, plus, host.
SELECT username, plus, host FROM Email WHERE id = 2
;
username | plus | host |
---|---|---|
Steve | NoCRUD | ADb.Net |
The VIEW EmailAddress combines the fields into a usable email address.
SELECT email, value FROM EmailAddress WHERE email IN (1,2)
;
value | |
---|---|
1 | [email protected] |
2 | [email protected] |
Assign an default email to a specific individual
SELECT SetIndividualEmail (10,
GetEmail('[email protected]'),
NULL
) FROM DUAL;
The new Email table entry
SELECT id, username, plus, host
FROM Email
WHERE Email.id = GetEmail('[email protected]')
;
id | username | plus | host |
---|---|---|---|
2000011 | Support | IBM.com |
The new record associating this email with Individual.id 10 as the default type NULL
SELECT individual, type, email, stop
FROM IndividualEmail
WHERE individual = 10
AND type IS NULL
;
individual | type | stop | |
---|---|---|---|
10 | 2000011 |
All IBM.com email addresses and the associated individuals
SELECT IndividualEmail.individual, EmailAddress.value
FROM IndividualEmail
JOIN Email ON Email.id = IndividualEmail.email
JOIN EmailAddress ON EmailAddress.email = Email.id
WHERE UPPER(Email.host) = UPPER('IBM.com')
AND IndividualEmail.stop IS NULL
;
individual | value |
---|---|
10 | [email protected] |
Tables used for Individual emails
Path
URL
URL Path table record for the Web page www.IBM.com
id | protocol | secure | host | value | get |
---|---|---|---|---|---|
10 | http | 0 | www.IBM.com |
Path 10 record as a URL
SELECT value FROM URL WHERE path = 10
;
value |
---|
http://www.IBM.com/ |
Display a link to Individual 10 on a NoCRUD HTML page
IndividualPath record
individual | type | path | track | stop |
---|---|---|---|---|
10 | 10 | 10 | NoCRUD |
SELECT individual, path, value AS anchor
FROM IndividualURL
WHERE individual = 10
;
individual | path | anchor |
---|---|---|
10 | 10 | http://www.IBM.com/?NoCRUD |
File
Files are stored using host, path and actual file name. These values are used differently depending on the context they are needed. For example, as a relative path and file used in a application.
Insert a file that will located using relative path 'assets/text'
SELECT GetFile('localhost', 'assets/text', 'myFile.txt') FROM DUAL
;
Use this file path in an application query
SELECT '/var/app/' || file AS filePath
FROM Path
JOIN File ON File.path = Path.id
WHERE Path.get = 'myFile.txt'
;
filepath |
---|
/var/app/assets/text/myFile.txt |
Tables used for IndividualURL and URL VIEWs
Addresses
Addresses are made up of several tables.
Tables used for Addresses VIEW
Country
id | name | code | idd | ndd |
---|---|---|---|---|
1 | 50000 | USA | 011 | 1 |
Code is the Long ISO Country code.
The International Dialing Prefix (idd) National Direct Dialing (ndd) fields are used for composing the numbers actually to dial when calling a phone number in another country.
Postal
id | country | code | state | stateabbreviation | county | city |
---|---|---|---|---|---|---|
110 | 1 | 10504 | 60018 | 60017 | 61054 | 61056 |
111 | 1 | 20500 | 61055 | 60027 | 61055 | 60026 |
De-normalized view of the Postal records
SELECT Postal.id AS postal,
State.value AS stateFullName,
City.value AS city,
StateAbbreviation.value AS state,
Postal.code AS zipCode,
Country.code AS countryCode
FROM Postal
JOIN Word AS State ON State.id = Postal.state
AND State.culture = 1033
JOIN Word AS StateAbbreviation ON StateAbbreviation.id = Postal.stateabbreviation
JOIN Word AS City ON City.id = Postal.city
AND City.culture = 1033
JOIN Country ON Country.id = Postal.country
WHERE Postal.id IN (110,111)
;
postal | statefullname | city | state | zipcode | countrycode |
---|---|---|---|---|---|
111 | District of Columbia | Washington | DC | 20500 | USA |
110 | New York | Armonk | NY | 10504 | USA |
Address
An address record consists of an address line, postal id and a postal plus 4. Technically for addresses in the USA, only a postal and plus4 is needed for any street address.
SELECT id, line1, postal, postalPlus FROM Address WHERE id in (1,2)
;
id | line1 | postal | postalplus |
---|---|---|---|
1 | 1 New Orchard Road | 110 | 1716 |
2 | 1600 Pennsylvania Avenue NW | 111 | 0005 |
Using the Addresses VIEW
SELECT address, line1, city, state, zipcode, countryCode
FROM Addresses WHERE address IN (1,2)
;
address | line1 | city | state | zipcode | countrycode |
---|---|---|---|---|---|
1 | 1 New Orchard Road | Armonk | NY | 10504-1716 | USA |
2 | 1600 Pennsylvania Avenue NW | Washington | DC | 20500-0005 | USA |
Phones
Phone numbers are stored without any formatting. Adding back the formatting is left up to the actual application or VIEW.
Phone table records. Note that dashes have been stripped out of number field.
id | country | area | number |
---|---|---|---|
1 | 1 | 914 | 4991900 |
2 | 1 | 202 | 4561111 |
The Phones VIEW puts all the formatting back in. This includes the idd and ndd fields from the Country table for the international version of the phone number.
SELECT phone, local, international
FROM Phones
WHERE phone IN (1,2)
;
phone | local | international |
---|---|---|
1 | 914-499-1900 | 011-1-914-499-1900 |
2 | 202-456-1111 | 011-1-202-456-1111 |
Tables used for Phones VIEW
List
Tables used for List VIEW
List tables are complicated due to the ListIndividual.id field being a foreign key for the ListIndividualName table's listIndividual field. They are further complicated by the ListIndividual table id field not being unique.
The ListIndividual table is a series of entry and removal records for and named list in the ListIndividualName table.
Subscribe an Individual to 4 lists by email address
SELECT ListSubscribeEmail('Clothing','Hats','[email protected]') FROM DUAL;
SELECT ListSubscribeEmail('Clothing','Socks','[email protected]') FROM DUAL;
SELECT ListSubscribeEmail('Job','Clown','[email protected]') FROM DUAL;
SELECT ListSubscribeEmail('Job','Santa','[email protected]') FROM DUAL;
Show lists this individual is now subscribed to
SELECT id, listNameValue AS listName, listSetValue AS listSet, send
FROM List
WHERE individual = GetIndividualEmail('[email protected]')
;
id | listname | listset | send |
---|---|---|---|
2000001 | Clothing | Hats | to |
2000002 | Clothing | Socks | to |
2000003 | Job | Clown | to |
2000004 | Job | Santa | to |
Unsubscribe from a list.
SELECT ListUnSubscribe('Job', 'Clown', GetIndividualEmail('[email protected]')) FROM DUAL
;
id | listname | listset | send |
---|---|---|---|
2000001 | Clothing | Hats | to |
2000002 | Clothing | Socks | to |
2000004 | Job | Santa | to |
Unsubscribe from all lists by inserting the individual with id (List id) field set to null.
INSERT INTO ListIndividual (individual) VALUES (GetIndividualEmail('[email protected]'))
;
No longer on any list.
id | listname | listset | send |
---|
Re-subscribe to a list
SELECT ListSubscribeEmail('Job','Clown','[email protected]') FROM DUAL
;
Still not on any list. An individual can still maintain their subscriptions, even if they no longer show up in the List view due to the un-subscribe from all flag is set.
id | listname | listset | send |
---|
Set the unList field for the unsubscribe all lists entry.
UPDATE ListIndividual SET unList = DATE(NOW())
WHERE id IS NULL
AND individual = GetIndividualEmail('[email protected]')
;
Back on all lists, even the list subscribed to when unsubscribed from all lists.
id | listname | listset | send |
---|---|---|---|
2000001 | Clothing | Hats | to |
2000002 | Clothing | Socks | to |
2000004 | Job | Santa | to |
2000003 | Job | Clown | to |
Actual ListIndividual Table records
id | unlist | individual |
---|---|---|
2000001 | 4000006 | |
2000002 | 4000006 | |
2000003 | 2019-11-17 16:45:54.822776 | 4000006 |
2000004 | 4000006 | |
2019-11-17 00:00:00 | 4000006 | |
2000003 | 4000006 |
Period
Time Periods can be made up of one or more entries in the Period tables Month, MonthDay, DayOfWeek, and TimeOfDay tables.
Each of these tables share an id sequence. If the Month table has a record with id 5, then none of the other Period tables can have an entry for id 5. This allows a simplified Period table where the span field can point to a record in any of the Period tables.
What time period is it right now?
SELECT TimePeriod.period, periodName
FROM TimePeriod
JOIN Periods ON Periods.period = TimePeriod.period
WHERE TimePeriod.open = true
;
period | periodname |
---|---|
23 | Holiday Dinner |
Complicated Holidays
Period 9, Labor Day, first Monday in September
SELECT period, periodName, span, dayofweekstart, dayofmonth, month FROM PeriodSpans WHERE period = 9
;
period | periodname | span | dayofweekstart | dayofmonth | month |
---|---|---|---|---|---|
9 | Labor Day | 9 | 9 | ||
9 | Labor Day | 20 | 1 | 1 |
Period 4, Memorial Day, last Monday of May
SELECT period, periodName, span, dayofweekstart, dayofmonth, month FROM PeriodSpans WHERE period = 4
;
period | periodname | span | dayofweekstart | dayofmonth | month |
---|---|---|---|---|---|
4 | Memorial Day | 5 | 5 | ||
4 | Memorial Day | 16 | 1 | -1 |
Tables used for Periods and TimePeriod VIEWs
Parts
Parts can be individual things or assemblies of multiple parts.
Each Part can be part of a hierarchy. For example, the top of the hierarchy can be the kind of part, all the way down to a part version with a serial number.
In this example, we have a Demo Printed Circuit Board called Alarm Demo version 1.0.0. It has several discrete components.
Let's start by creating the discrete components.
SELECT GetPart('Module');
SELECT GetPartWithParent('Arduino Nano', 'v3.x', 'Module', 'Micro Controller');
SELECT GetPart('Capacitor');
SELECT GetPartWithParent('470pF', '50v', 'Capacitor', 'Ceramic Disc');
SELECT GetPartWithParent('1uF', '50v', 'Capacitor', 'Ceramic Disc');
SELECT GetPartWithParent('22uF', '450v', 'Capacitor', 'Electrolytic');
SELECT GetPart('Schottky Diode');
SELECT GetPartWithParent('SR250', '15', 'Schottky Diode', 'DO');
SELECT GetPart('MOSFET');
SELECT GetPartWithParent('IRF540NPBF', '220', 'MOSFET', 'TO');
SELECT GetPartWithParent('2N7000', '92', 'MOSFET', 'TO');
SELECT GetPart('Resistor');
SELECT GetPartWithParent('33k', '1/4w', 'Resistor', 'Axial Lead');
SELECT GetPartWithParent('1k', '1/4w', 'Resistor', 'Axial Lead');
After executing the above, the database Part table will have these entries
SELECT part, parent, name, version
FROM Parts
ORDER BY parent nulls first, part
;
part | parent | name | version |
---|---|---|---|
10000 | Module | ||
10003 | Capacitor | ||
10009 | Schottky Diode | ||
10012 | MOSFET | ||
10016 | Resistor | ||
10001 | 10000 | Module | Micro Controller |
10002 | 10001 | Arduino Nano | v3.x |
10004 | 10003 | Capacitor | Ceramic Disc |
10007 | 10003 | Capacitor | Electrolytic |
10005 | 10004 | 470pF | 50v |
10006 | 10004 | 1uF | 50v |
10008 | 10007 | 22uF | 450v |
10010 | 10009 | Schottky Diode | DO |
10011 | 10010 | SR250 | 15 |
10013 | 10012 | MOSFET | TO |
10014 | 10013 | IRF540NPBF | 220 |
10015 | 10013 | 2N7000 | 92 |
10017 | 10016 | Resistor | Axial Lead |
10018 | 10017 | 33k | 1/4w |
10019 | 10017 | 1k | 1/4w |
Another way to list the parts
SELECT parent.name AS part,
parts.name,
parent.version || parts.version AS type
FROM Parts, Parts AS Parent
WHERE Parent.part = Parts.parent
AND Parent.version != ''
ORDER BY Parent.name
;
part | name | type |
---|---|---|
Capacitor | 1uF | Ceramic Disc 50v |
Capacitor | 470pF | Ceramic Disc 50v |
Capacitor | 22uF | Electrolytic 450v |
Module | Arduino Nano | Micro Controller v3.x |
MOSFET | 2N7000 | TO 92 |
MOSFET | IRF540NPBF | TO 220 |
Resistor | 33k | Axial Lead 1/4w |
Resistor | 1k | Axial Lead 1/4w |
Schottky Diode | SR250 | DO 15 |
Create an Assembly and add the parts to it
-- Assembly
SELECT GetPart('PCB');
SELECT GetPartWithParentVersion('Alarm', GetVersionName('Demo', '1', '0', '0'), 'PBC', GetVersionName('Demo'));
-- Parts
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('IRF540NPBF', GetVersionName('220')), 'Q1', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('2N7000', GetVersionName('92')), 'Q2', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('33k', GetVersionName('1/4w')), 'R9', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('1k', GetVersionName('1/4w')), 'R4', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('1k', GetVersionName('1/4w')), 'R7', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('470pF', GetVersionName('50v')), 'C1', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('22uF', GetVersionName('450v')), 'C2', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('1uF', GetVersionName('50v')), 'C3', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('1uF', GetVersionName('50v')), 'C5', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('1uF', GetVersionName('50v')), 'C6', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('SR250', GetVersionName('15')), 'D2', NULL);
SELECT PutAssemblyPart(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), GetPart('Arduino Nano', GetVersionName('v3.x')), 'A1', NULL);
Show the Assembly
SELECT parentname, name, version, versionname
FROM Assemblies
;
parentname | name | version | versionname |
---|---|---|---|
PBC | Alarm | 10013 | Demo 1.0.0 |
Show the parts for that Assembly
SELECT assemblyname, assemblyversionname, designator, partname, versionname
FROM AssemblyParts
ORDER BY designator
;
assemblyname | assemblyversionname | designator | partname | versionname |
---|---|---|---|---|
Alarm | Demo 1.0.0 | A1 | Arduino Nano | v3.x |
Alarm | Demo 1.0.0 | C1 | 470pF | 50v |
Alarm | Demo 1.0.0 | C2 | 22uF | 450v |
Alarm | Demo 1.0.0 | C3 | 1uF | 50v |
Alarm | Demo 1.0.0 | C5 | 1uF | 50v |
Alarm | Demo 1.0.0 | C6 | 1uF | 50v |
Alarm | Demo 1.0.0 | D2 | SR250 | 15 |
Alarm | Demo 1.0.0 | Q1 | IRF540NPBF | 220 |
Alarm | Demo 1.0.0 | Q2 | 2N7000 | 92 |
Alarm | Demo 1.0.0 | R4 | 1k | 1/4w |
Alarm | Demo 1.0.0 | R7 | 1k | 1/4w |
Alarm | Demo 1.0.0 | R9 | 33k | 1/4w |
Each assembled PCB, of a specific version can be inserted into the database for tracking.
First assembled Alarm Demo 1.0.0 serial number 10001.
SELECT GetPartbySerial(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), '10001')
;
SELECT name, version, serial FROM Parts WHERE part = GetPartbySerial(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), '10001')
;
name | version | serial |
---|---|---|
Alarm | Demo 1.0.0 | 10001 |
Tables used for Parts VIEW
Accounting
Wikipedia Example of debits and credits.
Book Entries
SELECT Book('Rent', 100) FROM DUAL; -- A business pays rent with cash
SELECT Book('Sale', 50) FROM DUAL; -- A business receives cash for a sale
SELECT Book('Equipment', 5200) FROM DUAL; -- A business buys equipment with cash
SELECT Book('Loan', 11000) FROM DUAL; -- A business borrows cash with loan
SELECT Book('Salary', 5000) FROM DUAL; -- A business pays salaries with cash
Booked business is automatically entered into the proper Journals.
Generated Journal Entries
The table from Wikipedia Debits and Credits Further examples
SELECT entry, account, type,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM JournalReport
WHERE ledger = 1 -- General Ledger
ORDER BY created, entry, rightSide
;
entry | account | type | debit | credit |
---|---|---|---|---|
1 | Rent | Expense | 100 | |
1 | Cash | Asset | 100 | |
2 | Cash | Asset | 50 | |
2 | Sales | Income | 50 | |
3 | Equipment | Asset | 5200 | |
3 | Cash | Asset | 5200 | |
4 | Cash | Asset | 11000 | |
4 | Loan | Liability | 11000 | |
5 | Salary | Expense | 5000 | |
5 | Cash | Asset | 5000 | |
Total | 21350 | 21350 |
Ledger Balances
The Ledger T Accounts are also automatically updated.
SELECT ledgerName AS ledger,
accountName AS account,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM LedgerReport
ORDER BY ledger,
sequence
;
ledger | account | debit | credit |
---|---|---|---|
General | Asset | 16250 | 10300 |
General | Liability | 11000 | |
General | Income | 50 | |
General | Expenses | 5100 | |
General | Total | 21350 | 21350 |
More Complex Book Entries
Effects more than two accounts.
SELECT Book('Sale Jane Doe', 1000) FROM DUAL; -- Jane Doe makes a commission sale
SELECT Book('Sale John Doe', 1000) FROM DUAL; -- John Doe makes a commission sale
Generated Journal Entries
SELECT entry, account, type,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM JournalReport
ORDER BY created, entry, rightSide
;
entry | account | type | debit | credit |
---|---|---|---|---|
1 | Rent | Expense | 100 | |
1 | Cash | Asset | 100 | |
2 | Cash | Asset | 50 | |
2 | Sales | Income | 50 | |
3 | Equipment | Asset | 5200 | |
3 | Cash | Asset | 5200 | |
4 | Cash | Asset | 11000 | |
4 | Loan | Liability | 11000 | |
5 | Salary | Expense | 5000 | |
5 | Cash | Asset | 5000 | |
6 | Cash | Asset | 1000 | |
6 | Commissions Payable | Liability | 200 | |
6 | Sales | Income | 800 | |
7 | Cash | Asset | 1000 | |
7 | Commissions Payable | Liability | 150 | |
7 | Sales | Income | 850 | |
Total | 23350 | 23350 |
Ledger Balances
SELECT ledgerName AS ledger,
accountName AS account,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM LedgerReport
ORDER BY ledger,
sequence
;
ledger | account | debit | credit |
---|---|---|---|
General | Asset | 18250 | 10300 |
General | Liability | 11350 | |
General | Income | 1700 | |
General | Expenses | 5100 | |
General | Total | 23350 | 23350 |
Using I18N feature for Accounting VIEWs
The above accounting VIEW results using different cultures.
These examples where created using PostgreSQL.
es-MX
SELECT ClientCulture();
DELETE FROM inject_culture;
INSERT INTO inject_culture values (2058)
;
SELECT entry, account, type,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM JournalReport
ORDER BY created, entry, rightSide
;
entry | account | type | debit | credit |
---|---|---|---|---|
1 | Alquiler | las expensas | 100 | |
1 | Dinero en efectivo | la posesión capital | 100 | |
2 | Dinero en efectivo | la posesión capital | 50 | |
2 | Ventas | Ingresos | 50 | |
3 | Equipo | la posesión capital | 5200 | |
3 | Dinero en efectivo | la posesión capital | 5200 | |
4 | Dinero en efectivo | la posesión capital | 11000 | |
4 | el empréstito | la obligación | 11000 | |
5 | Salario | las expensas | 5000 | |
5 | Dinero en efectivo | la posesión capital | 5000 | |
6 | Dinero en efectivo | la posesión capital | 1000 | |
6 | Comisiones a pagar | la obligación | 200 | |
6 | Ventas | Ingresos | 800 | |
7 | Dinero en efectivo | la posesión capital | 1000 | |
7 | Comisiones a pagar | la obligación | 150 | |
7 | Ventas | Ingresos | 850 | |
Total | 23350 | 23350 |
SELECT ledgerName AS ledger,
accountName AS account,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM LedgerReport
ORDER BY ledger,
sequence
;
ledger | account | debit | credit |
---|---|---|---|
General | la posesión capital | 18250 | 10300 |
General | la obligación | 11350 | |
General | Ingreso | 1700 | |
General | las expensas | 5100 | |
General | Total | 23350 | 23350 |
fr-FR
SELECT ClientCulture();
DELETE FROM inject_culture;
INSERT INTO inject_culture values (1036)
;
SELECT entry, account, type,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM JournalReport
ORDER BY created, entry, rightSide
;
entry | account | type | debit | credit |
---|---|---|---|---|
1 | Louer | Dépenses | 100 | |
1 | encaisser | le capital | 100 | |
2 | encaisser | le capital | 50 | |
2 | Ventes | Revenu | 50 | |
3 | Équipement | le capital | 5200 | |
3 | encaisser | le capital | 5200 | |
4 | encaisser | le capital | 11000 | |
4 | la dette | les dettes | 11000 | |
5 | le salaire | Dépenses | 5000 | |
5 | encaisser | le capital | 5000 | |
6 | encaisser | le capital | 1000 | |
6 | Commissions à payer | les dettes | 200 | |
6 | Ventes | Revenu | 800 | |
7 | encaisser | le capital | 1000 | |
7 | Commissions à payer | les dettes | 150 | |
7 | Ventes | Revenu | 850 | |
Total | 23350 | 23350 |
SELECT ledgerName AS ledger,
accountName AS account,
CAST(debit AS NUMERIC(19,0)),
CAST(credit AS NUMERIC(19,0))
FROM LedgerReport
ORDER BY ledger,
sequence
;
ledger | account | debit | credit |
---|---|---|---|
Général | le capital | 18250 | 10300 |
Général | les dettes | 11350 | |
Général | Revenu | 1700 | |
Général | Dépenses | 5100 | |
Général | Total | 23350 | 23350 |
Accounting Table Diagram
Tables used for Accounting
Inventory Movement
Simple example
- Put a stuffed Bunny into a shopping cart.
- Turn that cart into an order.
- Ship the order to the customer.
Create Wish list
-- Create bill of type wish list
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Wish' -- Wish list type bill
)
FROM DUAL
;
Add Bunny to wish list
-- Add 1 Stuffed Animal Bunny to Wish list
SELECT AddCargo(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Wish'), -- Wish List
GetPart('Bunny'), -- Item
1 -- Quantity
) FROM DUAL
;
Line items in Wish list
SELECT typeName AS type, consigneeName AS consignee, count, line, item, outstanding
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Wish')
;
type | consignee | count | line | item | outstanding |
---|---|---|---|---|---|
Wish | Toys for Tots | 1 | 100 | Bunny | 1 |
Create Cart
-- Create Cart as immediate child of Wish list
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Cart', -- Cart type bill
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Wish') -- Wish List as parent
) FROM DUAL
;
Add Bunny (all cargo) to Cart
-- Move all wish list line items (cargo) to Cart
SELECT MoveCargoToChild(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Wish'), -- Wish List
NULL, -- All cargo items
NULL
) FROM DUAL
;
View Cart Contents
Line items in Wish list
SELECT typeName AS type, consigneeName AS consignee, count, line, item, outstanding
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Wish')
;
type | consignee | count | line | item | outstanding |
---|---|---|---|---|---|
Wish | Toys for Tots | 1 | 100 | Bunny | 0 |
Notice that outstanding is 0
Line items in Cart
SELECT typeName AS type, consigneeName AS consignee, count, line, item, outstanding
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Cart')
;
type | consignee | count | line | item | outstanding |
---|---|---|---|---|---|
Cart | Toys for Tots | 1 | 101 | Bunny | 1 |
Create Quote
Create a price schedule
Default schedule for purchaser
-- Setup Default price schedule for Toys for Tots.
SELECT GetIndividualJobSchedule(GetIndividualEntity('Toys for Tots'),
GetJob('Default'), GetSchedule('Default'))
;
Add prices to schedule
INSERT INTO Schedule (schedule, fromCount, toCount, rate)
VALUES
(GetSchedule('Default'), 0, 99, 100),
(GetSchedule('Default'), 100, NULL, 80)
;
INSERT INTO AssemblyIndividualJobPrice (assembly, individualJob, price)
VALUES (GetPart('Bunny'),
GetIndividualJobSchedule(GetIndividualEntity('Toys for Tots'),
GetJob('Default'), GetSchedule('Default')), 14.99)
;
Display price expanded line items for Cart
-- Display current line item unit price and line total
SELECT typeName AS type, consigneeName AS consignee, count, line, item,
currentUnitPrice, outstanding, (currentUnitPrice * outstanding) as lineTotal
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Cart')
;
type | consignee | count | line | item | currentunitprice | outstanding | linetotal |
---|---|---|---|---|---|---|---|
Cart | Toys for Tots | 1 | 101 | Bunny | 14.99 | 1 | 14.99 |
Child Quote from Cart
-- Create Quote as immediate child of Cart
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Quote', -- Quote type bill
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Cart') -- Cart as parent
) FROM DUAL
;
Add Bunny (all cargo) to Quote
Fix quote prices by fixing IndividualJob in new cargo records
-- Move all cart line items (cargo) to Quote
SELECT MoveCargoToChild(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Cart'), -- Cart as parent
NULL, -- All cargo items
NULL,
GetIndividualJobSchedule(GetIndividualEntity('Toys for Tots'),
GetJob('Default'), GetSchedule('Default'))
) FROM DUAL
;
Line items in Quote
SELECT typeName AS type,
consigneeName AS consignee,
count,
line,
item,
currentUnitPrice,
unitprice,
totalprice,
outstanding
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Quote')
;
type | consignee | count | line | item | currentunitprice | unitprice | totalprice | outstanding |
---|---|---|---|---|---|---|---|---|
Quote | Toys for Tots | 1 | 102 | Bunny | 14.99 | 14.99 | 14.99 | 1 |
Increase the current default price of the Bunny for 'Toys for Tots'
-- Stop current default IndividualJob price on Bunny
UPDATE IndividualJob SET stop = NOW() WHERE individual = GetIndividualEntity('Toys for Tots')
;
-- Get new IndividualJob
SELECT GetIndividualJobSchedule(GetIndividualEntity('Toys for Tots'),
GetJob('Default'),
GetSchedule('Default'))
;
-- Insert new price
INSERT INTO AssemblyIndividualJobPrice (assembly, individualJob, price)
VALUES (GetPart('Bunny'),
GetIndividualJobSchedule(GetIndividualEntity('Toys for Tots'),
GetJob('Default'), GetSchedule('Default')), 23.44)
;
Line items in Quote again, showing new current price and quoted price
SELECT typeName AS type,
consigneeName AS consignee,
count,
line,
item,
currentUnitPrice,
unitprice,
totalprice,
outstanding
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Quote')
;
type | consignee | count | line | item | currentunitprice | unitprice | totalprice | outstanding |
---|---|---|---|---|---|---|---|---|
Quote | Toys for Tots | 1 | 102 | Bunny | 23.44 | 14.99 | 14.99 | 1 |
Create Order
Child Order from Quote
-- Create Order as immediate child of Quote
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Order', -- Order type bill
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Quote') -- Quote as parent
) FROM DUAL
;
Add Bunny (all cargo) to Order
-- Move all cart line items (cargo) to Order
SELECT MoveCargoToChild(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Quote'), -- Quote as parent
NULL, -- All cargo items
NULL, -- Default Job
NULL, -- Default Schedual
'AR Sale' -- Accounting Book to used
) FROM DUAL
;
Display price expanded line items from Order
These current price and line total come from journal entries
-- Display current line item unit price and line total
SELECT typeName AS type, consigneeName AS consignee, count, line, item,
currentUnitPrice,
unitprice, outstanding,
(currentUnitPrice * outstanding) as lineTotal
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Order')
;
type | consignee | count | line | item | currentunitprice | unitprice | outstanding | linetotal |
---|---|---|---|---|---|---|---|---|
Order | Toys for Tots | 1 | 103 | Bunny | 23.44 | 14.99 | 1 | 23.44 |
Create Invoice
-- Create Invoice as immediate child of Order
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Invoice', -- Invoice type bill
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Order') -- Order as parent
) FROM DUAL
;
Add Bunny (all cargo) to Invoice
-- Move all cart line items (cargo) to Invoice
SELECT MoveCargoToChild(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Order'), -- Order as parent
NULL, -- All cargo items
NULL, -- Default Job
NULL -- Default Schedual
) FROM DUAL
;
Display price expanded line items for Invoice
-- Display current line item unit price and line total
SELECT typeName AS type, consigneeName AS consignee, count, line, item,
unitprice, outstanding,
(currentUnitPrice * outstanding) as lineTotal
FROM LineItems
WHERE bill = GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Invoice')
;
type | consignee | count | line | item | unitprice | outstanding | linetotal |
---|---|---|---|---|---|---|---|
Invoice | Toys for Tots | 1 | 104 | Bunny | 14.99 | 1 | 23.44 |
Create Payment
Payment receipt
-- Create Receipt as immediate child of Invoice
SELECT CreateBill(
GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'),
'Receipt', -- Invoice type bill
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Invoice') -- Invoice as parent
) FROM DUAL
;
Add Bunny (all cargo) to Receipt
Toys for Tots has and account with Bunnies-R-Us, so purchase on credit (Accounts Receivable).
-- Move all cart line items (cargo) to Receipt
SELECT MoveCargoToChild(
GetOutstandingBill(GetIndividualEntity('Bunnies-R-Us'),
GetIndividualEntity('Toys for Tots'), 'Invoice'), -- Invoice as parent
NULL, -- All cargo items
NULL, -- Default Job
NULL, -- Default Schedual
'AR Payment' -- Accounting Book to used
) FROM DUAL
;
Show Accounts for this transaction
SELECT entry, account, type, debit, credit
FROM JournalReport
ORDER BY created, entry, rightSide
;
entry | account | type | debit | credit |
---|---|---|---|---|
1 | Receivable | Asset | 14.99 | |
1 | Sales | Income | 14.99 | |
2 | Cash | Asset | 14.99 | |
2 | Receivable | Asset | 14.99 | |
Total | 29.98 | 29.98 |
Ship Order
Received Order
Mark clean
Flow
Inventory Bin -> Shipper (received) -> Carrier (loaded), (delivered)
More complicated example
- Put a few stuffed animals into a shopping cart.
- Turn most of the cart into an order.
- Ship the order to the customer in multiple shipments.
- Customer returns one of the items.
- A replacement item is shipped to the customer.
- The customer is credited for the returned and not billed for the replacement
Inventory Table Diagram
Tables used for Inventory
Application Release
A use of Application Release is to record a version and build of firmware that is written (embedded) into a serial numbered physical device.
In the above Parts section, a physical device was defined and an assembled, tested and serial numbered
unit was inserted into the database. Demo Alarm board version 1.0.0, serial number 10001
.
SELECT GetPartbySerial(GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')), '10001')
;
If every Demo Alarm board version 1.0.0 of any serial number gets embedded software called 'Alarm Embeded' version 0.0.3 build 42.
SELECT GetAssemblyApplicationRelease(
GetPart('Alarm', GetVersionName('Demo', '1', '0', '0')),
GetApplicationRelease(
GetApplication('Alarm Embedded'),
GetRelease(GetVersion('0','0','3'), '42')))
;
SELECT assemblyName,
assemblyVersion,
applicationName,
applicationVersionName,
buildName
FROM AssemblyApplicationReleases
;
assemblyname | assemblyversion | applicationname | applicationversionname | buildname |
---|---|---|---|---|
Alarm | Demo 1.0.0 | Alarm Embedded | 0.0.3 | 42 |
Web Sessions
It is assumed that the Web server that gives an application access to the fields in NCSA Common Combined Log Format.
The web logs can also be post-processed for historical analysis.
Create Session
- A client hits the server without a session token
- Parse Client(Browser) User-Agent string.
- Insert the parsed Client Agent string, referring URL and source IP address into an Anonymous Session.
- Client requests to create a user
- Assign a session token to the anonymous session
- Client generates a valid password
- Create a user credential (account) associated with the new password
- Associate current session with new user credential
A client hits the server without a session token
Parse User-Agent string Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.130 Safari/537.36
for arguments to AnonymouseSession.
SELECT AnonymousSession('Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.130 Safari/537.36',
'Chrome',
'43',
'0',
'2357',
'130',
'Linux',
NULL,NULL,NULL,NULL,NULL,
'Other',
0,
'www.ibm.com',
NULL,NULL,
'107.77.97.52') FROM DUAL
;
anonymoussession |
---|
1 |
The AnonymousSession result can be used by the application going forward for the client web session. AnonymousSession() should be called on every anonymous page load to keep session alive and track the client.
Associate the session id with a user
Assign a session token to the anonymous session id 1.
INSERT INTO SessionToken (session, token, initialized, timeout, items)
SELECT 1,
'BKrB9cYbZYcP1xKbKBOeXsAxDmoybyHn',
1,
NULL, NULL
;
No password generator entity or generator application release, so just store in the clear.
INSERT INTO password (provider, generator, value)
VALUES (NULL, NULL, '0000')
;
Assume Password.id is 1.
Create a user credential (account) associated with the new password id 1.
INSERT INTO credential (username, password, culture)
VALUES ('helmet', 1, 1033)
;
Assume Credential.id is 1.
Finally, associate current session.id 1 to credential.id 1. Use the balance of the web session information.
SELECT SetSession('BKrB9cYbZYcP1xKbKBOeXsAxDmoybyHn', NULL, 1000, 1, 10, '107.77.97.52', NULL);
SetSession should be called on every page load to keep session alive and track the user and client.
Report the initial anonymous page hit with the later association of the session to a user via a credential.
SELECT session, token, os, agent, credential, username
FROM Sessions
ORDER BY token, credential NULLS FIRST, touched
;
session | token | os | agent | credential | username |
---|---|---|---|---|---|
1 | BKrB9cYbZYcP1xKbKBOeXsAxDmoybyHn | Linux | Chrome | ||
1 | BKrB9cYbZYcP1xKbKBOeXsAxDmoybyHn | Linux | Chrome | 1 | helmet |
Tables used to record Wed sessions
DAG
The implementation is based heavily on A Model to Represent Directed Acyclic Graphs (DAG) on SQL Databases
How to Use the Edge Table
Company Structure
Will duplicate figure 5 from a Kemal Erdogan codeproject article.
SELECT AddEdgeName('HelpDesk', 'Admins');
SELECT AddEdgeName('Ali', 'Admins');
SELECT AddEdgeName('Ali', 'Users');
SELECT AddEdgeName('Burcu', 'Users');
SELECT AddEdgeName('Can', 'Users');
SELECT AddEdgeName('Managers', 'Users');
SELECT AddEdgeName('Technicians', 'Users');
SELECT AddEdgeName('Demet', 'HelpDesk');
SELECT AddEdgeName('Engin', 'HelpDesk');
SELECT AddEdgeName('Engin', 'Users');
SELECT AddEdgeName('Fuat', 'Managers');
SELECT AddEdgeName('G l', 'Managers');
SELECT AddEdgeName('Hakan', 'Technicians');
SELECT AddEdgeName('Irmak', 'Technicians');
SELECT AddEdgeName('ABCTechnicians', 'Technicians');
SELECT AddEdgeName('Jale', 'ABCTechnicians');
SELECT startName, hops
FROM Edges
WHERE stop = GetVertex('Admins')
ORDER BY hops
;
startname | hops |
---|---|
HelpDesk | 0 |
Ali | 0 |
Demet | 1 |
Engin | 1 |
SELECT stopName, hops
FROM Edges
WHERE start = GetVertex('Jale')
ORDER BY hops
;
stopname | hops |
---|---|
ABCTechnicians | 0 |
Technicians | 1 |
Users | 2 |
Family Tree
Simple Jazdzewski family tree
SELECT AddEdgeName('Family Tree', 'Jazdzewski') FROM DUAL;
SELECT AddEdge(GetVertex('Jazdzewski'), SetIndividualVertex(1)) FROM DUAL;
SELECT AddEdge(GetIndividualVertex(1), SetIndividualVertex(2)) FROM DUAL;
SELECT AddEdge(GetIndividualVertex(1), SetIndividualVertex(3)) FROM DUAL;
SELECT AddEdge(GetIndividualVertex(1), SetIndividualVertex(4)) FROM DUAL;
SELECT AddEdge(GetIndividualVertex(3), SetIndividualVertex(5)) FROM DUAL;
SELECT AddEdge(GetIndividualVertex(3), SetIndividualVertex(6)) FROM DUAL;
SELECT startName, StopIndividualname, hops
FROM EdgeIndividuals
WHERE start = GetVertex('Jazdzewski')
ORDER BY hops
;
startname | stopindividualname | hops |
---|---|---|
Jazdzewski | Terrence Edward Jazdzewski Sr | 0 |
Jazdzewski | Terrence Edward Jazdzewski Jr | 1 |
Jazdzewski | Stephen Arthur Jazdzewski | 1 |
Jazdzewski | Charles Paul Jazdzewski | 1 |
Jazdzewski | Alec Xavier Jazdzewski | 2 |
Jazdzewski | Devin Kyle Jazdzewski | 2 |
SELECT stopIndividualname, hops
FROM EdgeIndividuals
WHERE start = GetIndividualVertex(3)
ORDER BY hops
;
stopindividualname | hops |
---|---|
Alec Xavier Jazdzewski | 0 |
Devin Kyle Jazdzewski | 0 |
Add grandmother
SELECT AddEdge(GetIndividualVertex(1), SetIndividualVertex(14, 'spouse')) FROM DUAL
;
SELECT stopIndividualname, stopType, hops
FROM EdgeIndividuals
WHERE start = GetIndividualVertex(1)
ORDER BY hops
;
stopindividualname | stoptype | hops |
---|---|---|
Terrence Edward Jazdzewski Jr | 0 | |
Stephen Arthur Jazdzewski | 0 | |
Charles Paul Jazdzewski | 0 | |
Candace Lauren Fish | spouse | 0 |
Alec Xavier Jazdzewski | 1 | |
Devin Kyle Jazdzewski | 1 |
Tables used for DAG