Katelynn's Report

Katelynn's Report

(US Market)

Help










Font Size:

XBRL-minisql

What is XBRL

XBRL represents eXtensible Business Reporting Language, which is a freely available global standard for exchanging business information. Starting 2009, the SEC of United States began to require publicly traded companies to file financial statements in XBRL format.

Each XBRL report is called an XBRL instance, which is intrinsically a collection of XML files that use elements and attributes to represents business facts, and their relationships. While XBRL has significant advantages over unstructured business report, it is flexbility is often overused by reporting companies. There are several known problems like the inconsistency of the elements used by different companies; missing element, not well-formed structure, or unresolvable external link. The efficient use of XBRL is further complicated by the complex, cross linked structure of XML files, and the overhead of importing XBRL into document-oriented, XML-friendly database (e.g. MongoDB, Sedna XML). All these disadvantages significantly limited XBRL’s usefulness to many common investors.

What is XBRL-minisql

XBRL-minisql is a MySQL-based database constructed in view of the difficulties in efficient access/query of financial information in XBRL reports. The complex structures and business facts hidden in XBRL filings are digested by our accounting/informatics specialists and stored in MySQL, the world's most popular open source database, using merely five tables. The tables are properly structured, indexed, compressed, and partitioned for highly efficient access of most, if not all, information in the original XBRL filings.

Why XBRL-minisql

XBRL-minisql saves investors the overhead of dealing with complex XBRL architectures, linkbases, schema definitions (often hidden in external links), as well as the learning curves for mastering XML-oriented database. Anyone with moderate experience in MySQL will be able to utilize XBRL-minisql to extract, analyze, and present information in XBRL filing.
XBRL-minisql validates and keeps track of all XBRL filings (both regular and inline-XBRL) to the SEC since 2013 and can virtually be a surrogate source of the original XBRL filings.
XBRL-minisql is designed for efficient retrieval of fundamental information about a company, which includes but not limited to shares outstanding, public float, sales, gross margin, net income, operating expenses, earnings per share, short-term debt, long-term debt, dividend. Majority of material information about a company can be retrieved through a single MySQL query that smartly joins four tables.
XBRL-minisql keeps the content tight by dropping obviously redundant, or less useful information (e.g. multiple labels for a single element, some never used) in the original filings.

What XBRL-minisql is not

XBRL-minisql is not designed for reconstructing the original XBRL filing, which is supposed to be supported by Arelle project that uses about 29 tables to capture original content in each XBRL instance.

Leveraging XBRL-minisql

While the advent of XBRL fundamentally improved the accessibility financial data, it is still an overwhelming task to fully utilize the raw, unnormalizaed information extracted from XBRL. Since usually different tag names are used to represent the same item by different reporting companies, or even by the same company in different reporting periods. For instance, revenue in an income statement can be represented as "Revenue", "Revenues", "SalesRevenue", "SalesRevenueNet", "SalesRevenueAndOtherIncome" and et ac. Similarly, cost can be specified as "CostOfSale", "CostOfRevenue", "CostOfGoodsSold", "CostOfServices" and et ac. Things are further complicated by the positive and negative sign of a value due to debit or credit nature of an item, as well as typos or plural forms in tag names. Katelynn's Report is fully aware all kinds of difficulties associated with the efficient access to business facts in XBRL-minisql, and has set up a group of financial natural language processing expert to tackle this issue. Majority of the key financial data in balance sheet, income statement, and cash flow are already extracted and stored in seperate tables. Our service team is also ready to provide free coding support to any client (with dataportal access) who needs to extract any information that is not available yet.



XBRL-minisql overall structure

XBRL-minisql is constructured in a way reasonably similar to the file structure of XBRL instance (in order to minimize the overhead of mastering a new structure), with slight changes to accommendate the efficient retrieval of longitudinal information from the same filer. XBRL-minisql is composed of five tables including xbrls, xbrlpres, xbrldts, xbrlxsds, and xbrlnspcs. xbrls contains the index information for all XBRL filings. xbrlpres contains the information parsed from presentation, calculation, and definition linkbases. xbrldts contains the actual data mentioned in architecture of each linkbase. xbrlxsds contains the schema definition for all elements, either company specific or common elements (e.g. us_gaap), that was ever used in any XBRL filings after 2013. xbrlnspcs contains abbreviations and fullname of namespaces, as well as the link to schema location, for namespaces appear in each xsd (both internal and external) file.

xbrls: XBRL index table

XBRL index table contains basic information about each XBRL filing. Each XBRL instance filed to the SEC has an unique record in xbrls table. The table is constructed as shown below

CREATE TABLE `xbrls` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fdate` date NOT NULL,
`zipfilename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`symbol` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`cik` bigint(20) NOT NULL,
`cikarr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`periodenddate` date DEFAULT NULL,
`form` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_xbrls_on_zipfilename` (`zipfilename`),
KEY `index_xbrls_on_fdate` (`fdate`),
KEY `index_xbrls_on_symbol` (`symbol`),
KEY `index_xbrls_on_periodenddate` (`periodenddate`),
KEY `index_xbrls_on_cik` (`cik`),
KEY `index_xbrls_on_form` (`form`)
) ENGINE=InnoDB AUTO_INCREMENT=899655 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

fdate: XBRL filing date. Example: "2014-01-28".

zipfilename: A string uniquely identifies each XBRL filing. The format is ${filercik}_${form_type}#${xbrl zip filename}. Example: "320193_10-Q#0001193125-14-024487-xbrl.zip". Note that ${xbrl zip filename} does not uniquely identify XBRL filing, since exactly the same XBRL filing can be filed by different subsidiaries of the same company using different CIK.

symbol: The content of the first <dei:TradingSymbol> element of the data xml in an XBRL instance. Example: "AAPL". Note that companies may or may not present all classes of trading symbol in the <dei:TradingSymbol> element. Different companies may also have the same symbol in <dei:TradingSymbol> element. Thus the symbol field alone is not accurate enough to identify a company.

cik: The central index key used for the XBRL filing. Example: "320193". While cik can be reused for different companies over time, it can uniquely identify a company when combined with symbol or fdate field.

cikarr: A string containing "|" delimited cik(s) appeared in <dei:EntityCentralIndexKey> element(s) of an XBRL instance. Example: "788784|1158659|81033". 2%~3% of XBRL filings have multiple ciks in a single XBRL instance. Multiple ciks usually happen when a company has multilple different subsidiaries. Under most circumstances exactly the same XBRL filing will also be filed on the same day under other ciks appeared in the cikarr. In this example, there are three exactly the same copies of XBRL filings filed under cik 788784, 1158659, and 81033 respectively. All the three different ciks refer to Public Service Enterprise Group and its different subsidiaries. All of the three copies are parsed and stored by XBRL-minisql.

periodenddate: The date in <dei:DocumentPeriodEndDate> element of an XBRL instance. Example: "2014-12-28". This date tells what accounting period the XBRL filing covers. Depends on the form type, a period could be a quarter or a year and reflects all of the financial activity occurred during that time.

form: The type of the form submitted to the SEC. Possible values, in descending popularity, are 10-Q, 10-K, 485BPOS, S-1, 20-F, S-4, 497, and et ac.



xbrlpres: XBRL presentation, calculation, definition linkbases

xbrlpres table contains the information parsed from presentation, calculation, and definition linkbases, which define the architecture and mathematical relationships between elements. The table is constructed as shown below. Note that the table is partitioned by year, so include date limitation in the where clause (e.g. "xbrlpres.fdate >= '2015-01-01'" will speed up the search.

CREATE TABLE `xbrlpres` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`fdate` date NOT NULL,
`zipfilename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`xmlpart` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`role` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`roletitle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`fromlabel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`tolabel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`fromtext` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`totext` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`weight` decimal(10,3) DEFAULT NULL,
`odr` decimal(10,5) DEFAULT NULL,
`usefield` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`priority` smallint(6) DEFAULT NULL,
`arcrole` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`hasdata` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`ID`,`fdate`),
KEY `index_xbrlpres_on_fdate` (`fdate`),
KEY `index_xbrlpres_on_tolabel` (`tolabel`),
KEY `index_xbrlpres_on_fromlabel` (`fromlabel`),
KEY `index_xbrlpres_on_role` (`role`),
KEY `index_xbrlpres_on_xmlpart` (`xmlpart`),
KEY `index_xbrlpres_on_zipfilename_and_tolabel` (`zipfilename`,`tolabel`)
) ENGINE=InnoDB AUTO_INCREMENT=916601705 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (YEAR(fdate))
(PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION p2023 VALUES LESS THAN (2024) ENGINE = InnoDB,
PARTITION p2024 VALUES LESS THAN (2025) ENGINE = InnoDB,
PARTITION p2025 VALUES LESS THAN (2026) ENGINE = InnoDB,
PARTITION p2026 VALUES LESS THAN (2027) ENGINE = InnoDB,
PARTITION p2027 VALUES LESS THAN (2028) ENGINE = InnoDB,
PARTITION p2028 VALUES LESS THAN (2029) ENGINE = InnoDB,
PARTITION p2029 VALUES LESS THAN (2030) ENGINE = InnoDB,
PARTITION p2030 VALUES LESS THAN (2031) ENGINE = InnoDB,
PARTITION p2031 VALUES LESS THAN (2032) ENGINE = InnoDB,
PARTITION p2032 VALUES LESS THAN (2033) ENGINE = InnoDB,
PARTITION p2033 VALUES LESS THAN (2034) ENGINE = InnoDB,
PARTITION p2034 VALUES LESS THAN (2035) ENGINE = InnoDB,
PARTITION p2035 VALUES LESS THAN (2036) ENGINE = InnoDB,
PARTITION p2036 VALUES LESS THAN (2037) ENGINE = InnoDB,
PARTITION p2037 VALUES LESS THAN (2038) ENGINE = InnoDB) */

Field definition

fdate: XBRL filing date. Example: "2014-01-28". This is the same as the fdate field in xbrls table

zipfilename: A string identifying the origin of XBRL filing for the current record. It is the same as the zipfilename field in xbrls. This field serves as the key to join between xbrls and xbrlpres tables.

xmlpart: Which linkbase the record came from. Possible values are "PRE" for presentation linkbase, "CAL" for calculation linkbase, and "DEF" for definition linkbase.

role: For presentation linkbase, it is the string appeared after last "/" of the @role attribute of each <presentationLink> element.
For example, the role is "DocumentandEntityInformation" for the following element

<presentationLink xlink:type="extended" xlink:role="http://www.apple.com/taxonomy/role/DocumentandEntityInformation">

For calculation linkbase, it is the string appeared after last "/" of the @role attribute of each <calculationLink> element.
For definition linkbase, it is the string appeared after last "/" of the @role attribute of each <definitionLink> element.

roletitle: The @title attribute of corresponding <presentationLink>, <calculationLink> or <definitionLink> element or, if not available, the content in <definition> element that is a child of corresponding <roleType> element in schema definition file.

fromlabel: The string after "#" of @href attribute of the <loc> element that points to the @from attribute of Arc element.
For example, the fromlabel field for the following <presentationArc> element is "us-gaap_EarningsPerShareAbstract"

<loc xlink:type="locator" xlink:href="http://xbrl.fasb.org/us-gaap/2013/elts/us-gaap-2013-01-31.xsd#us-gaap_EarningsPerShareAbstract" xlink:label="us-gaap_EarningsPerShareAbstract"/>
<presentationArc xlink:type="arc" xlink:arcrole="http://www.xbrl.org/2003/arcrole/parent-child" xlink:from="us-gaap_EarningsPerShareAbstract" xlink:to="us-gaap_EarningsPerShareBasic" order="1.0200" preferredLabel="http://www.xbrl.org/2003/role/terseLabel" priority="2" use="optional"/>

tolabel: Similar to fromlabel field, but for @to attribute of Arc element.

fromtext: For presentation linkbase, this is the content of the <label> element (in label linkbase) that belongs to the correctly mapped locator, and has @role attribute matches the @preferredLabel attribute of <presentationArc> element in corresponding role.
For calculation and definition linkbase, this is the content of the <label> element that belongs to the correctly mapped locator, and has the shortest @role attribute.

totext: Similar to fromtext field, but for @to attribute of Arc element.

weight: The @weight attribute of corresponding <calculationArc> element. This field must be NULL for presentation and definition linkbases, and must be NOT NULL for calculation linkbase.

odr: The @order attribute of Arc element. The default value is 1.0.

usefield: The @use attribute of Arc element. Possible values are "opt" for optional, "pro" for prohibited.

priority: The @priority attribute of Arc element. The default value is 0.

arcrole: The string after last "/" of @arcrole attribute of Arc element. It defines the relationship between @from attribute and @to attribute. For calculation linkbase, this field should be "summation-item". For presentation linkbase, this field should be "parent-child":

hasdata: Whether the @to attribute has associated data. Possible values are "NO_DATA" and "HAS_DATA". If "HAS_DATA", a record with matched zipfilename and tolabel fields must be available in xbrldts table.



xbrldts: XBRL data

xbrldts table contains data parsed from the data xml file, which contains the actual data for each XBRL instance. Note that the table is partitioned by year, so include date limitation in the where clause (e.g. "xbrldts.fdate >= '2015-01-01'" will speed up the search. The table is constructed as shown below.

CREATE TABLE `xbrldts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fdate` date NOT NULL,
`zipfilename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`tolabel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`xsdid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`unit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`decimals` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`metadata` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`datesta` date DEFAULT NULL,
`dateend` date DEFAULT NULL,
`numericvalue` decimal(30,5) DEFAULT NULL,
`stringvalue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`textvalue` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`,`fdate`),
KEY `index_xbrldts_on_fdate` (`fdate`),
KEY `index_xbrldts_on_zipfilename_and_tolabel` (`zipfilename`,`tolabel`)
) ENGINE=InnoDB AUTO_INCREMENT=543178826 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (YEAR(fdate))
(PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION p2023 VALUES LESS THAN (2024) ENGINE = InnoDB,
PARTITION p2024 VALUES LESS THAN (2025) ENGINE = InnoDB,
PARTITION p2025 VALUES LESS THAN (2026) ENGINE = InnoDB,
PARTITION p2026 VALUES LESS THAN (2027) ENGINE = InnoDB,
PARTITION p2027 VALUES LESS THAN (2028) ENGINE = InnoDB,
PARTITION p2028 VALUES LESS THAN (2029) ENGINE = InnoDB,
PARTITION p2029 VALUES LESS THAN (2030) ENGINE = InnoDB,
PARTITION p2030 VALUES LESS THAN (2031) ENGINE = InnoDB,
PARTITION p2031 VALUES LESS THAN (2032) ENGINE = InnoDB,
PARTITION p2032 VALUES LESS THAN (2033) ENGINE = InnoDB,
PARTITION p2033 VALUES LESS THAN (2034) ENGINE = InnoDB,
PARTITION p2034 VALUES LESS THAN (2035) ENGINE = InnoDB,
PARTITION p2035 VALUES LESS THAN (2036) ENGINE = InnoDB,
PARTITION p2036 VALUES LESS THAN (2037) ENGINE = InnoDB,
PARTITION p2037 VALUES LESS THAN (2038) ENGINE = InnoDB) */

Field definition

fdate: XBRL filing date. Example: "2014-01-28". This is the same as the fdate field in xbrls table.

zipfilename: A string identifying the origin of XBRL filing for the current record. It is the same as the zipfilename field in xbrls and xbrlpres. This field serves as the key to join between xbrlpres and xbrldts tables.

tolabel: Same as the tolabel field in xbrlpres table. zipfilename and tolabel fields in xbrldts together identify one or more than one (differ in metadata, datesta, or dateend fields) data record(s) in xbrlpres.

xsdid: The schema definition identification string for the tolabel field. It is formated like ${xsd link}#${elementid}. For each xsdid in xbrldts, there must be a corresponding record in xbrlxsds table that use this xsdid as an unique key.

unit: The <unit> element that the @unitRef attribute points to. The unit field can contain single unit or complex unit. The most commonly used single units are "iso4217:USD", "iso4217:EUR", "iso4217:BGP", "iso4217:CNY". For complex unit, " / " stands for division (e.g. "iso4217:USD / xbrli:shares", "iso4217:USD / compsci:item"), and " | " stands for multiplication (e.g. "feet | feet" for squarefeet). Multiplication is extremely rare.

decimals: The @decimals attribute of numeric data record. Must be an integer or the value "INF" that specifies the number of decimal places to which the value of the fact represented may be considered accurate. For example, decimals equal to 4 means the value is accurate to the fourth digit right of decimal point (e.g. 123.5678). Decimals equal to -4 means the value is accurate to the fourth digit left of decimal point (e.g. 1230000).

metadata: A string containing " || " delimited array of contents in <segment> element(s) inside the <context> that @contextRef attribute points to. Each element in the array contains two strings delimited by " | ". The left string is the original content in <segment> element. The right string is the corresponding description text in label linkbase. Example: "ptrbfi_S000043499Member | Prudential Short Duration Multi-Sector Bond Fund || ptrbfi_C000134895Member | Class A". Note that while <context> may contain both <segment> and <scenario> elements, the latter is extremely rare and not captured by XBRL-minisql.

datesta: The date in <startDate> element, if any, of the <context> that @contextRef attribute points to.

dateend: The date in <endDate> or <instant> element, if any, of the <context> that @contextRef attribute points to.

numericvalue: This field stores content in data element which is recognized as numeric by regular experession "^(\+|-)?\d?\.?\d+(e(\+|-)?\d+)?$".

stringvalue: This field stores any content in data element that is non-numeric and have length <= 255.

textvalue: This field stores any content in data element that is non-numeric and have length > 255.



xbrlxsds: XBRL schema definition

Schema definition is an abstract representation of an object's characteristics and relationship to other objects. The xbrlxsds table contains the characteristics parsed from internal and external xsd files. Internal xsd file is the .xsd file submitted within each XBRL instance. External xsd files are the xsd files referenced anywhere in an XBRL instance. The relationship to other objects can be obtained through joined query of xsd, type fields in xbrlxsds and xsd, ns fields in xbrlnspcs table ( non MySQL scripting required). The table is constructed as shown below.

CREATE TABLE `xbrlxsds` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xsd` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`xsdid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`elementid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nillable` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`balance` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`periodtype` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`substitutiongroup` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`abstract` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_xbrlxsds_on_xsdid` (`xsdid`),
KEY `index_xbrlxsds_on_xsd` (`xsd`),
KEY `index_xbrlxsds_on_elementid` (`elementid`)
) ENGINE=InnoDB AUTO_INCREMENT=59392736 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Field definition

xsd: Link to schema definition file. For external xsd file it is a working link like "http://xbrl.sec.gov/dei/2013/dei-2013-01-31.xsd". For internal xsd file it is a pseudo link like "http://www.apple.com/20131228", which mostly does not link to anything.

xsdid: Same as the xsdid field in xbrldts table. It serves as the key for joining between xbrldts and xbrlxsds.

elementid: The @id attribute of each element. Example: "dei_AccountingAddressMember".

name: The @name attribute of each element. Example: "AccountingAddressMember".

nillable: The @nillable attribute of each element. Possible values are "true" and NULL.

balance: The @balance attribute of each element. Possible values are "credit", "debit", and NULL.

type: The @type attribute of each element. Examples: "nonnum:domainItemType", "xbrli:stringItemType". The "nonnum" and "xbrli" before colon are namespaces, whose http locations are documented in xbrlnspcs table.

periodtype: The @periodType attribute of each element. Possible values are "instant", "duration", and NULL. "instant" data type should be NOT NULL in dateend field and NULL in datesta field of xbrldts table. "duration" data type should be NOT NULL in both datesta and dateend fields of xbrldts table.

substitutiongroup: The @substitutionGroup attribute of each element.

abstract: The @abstract attribute of each element. Possible values are "true", "false", and NULL.



xbrlnspcs: XBRL namespace

Namespaces in XML are used to provide unique element and attribute identifier, thus avoid conflicts when element/attribute from different sources have the same name. xbrlnspcs documents the full namespace, abbreviation, and schema location (source link) presented in both internal and external xsd files. Note that under most circumstances xbrlnspcs table is not needed to extract key ratios and material information from an XBRL instance. The table is constructed as shown below.

CREATE TABLE `xbrlnspcs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xsd` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`ns` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`namespace` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`schemalocation` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_xbrlnspcs_on_xsd_and_ns` (`xsd`,`ns`),
KEY `index_xbrlnspcs_on_xsd` (`xsd`),
KEY `index_xbrlnspcs_on_ns` (`ns`),
KEY `index_xbrlnspcs_on_namespace` (`namespace`)
) ENGINE=InnoDB AUTO_INCREMENT=9445505 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Field definition

xsd: Same as the xsd field in xbrlxsds table. It serves as the key to join between xbrlxsds and xbrlnspcs tables.

ns: Abbreviation of full namespace. Example: "nonnum". This field corresponds to the namespace in type and periodtype fields of xbrlxsds table.

namespace: Full namespace. Example: "http://www.xbrl.org/dtr/type/non-numeric".

shemalocation: Http link to the namespace file. Example: "http://www.xbrl.org/dtr/type/nonNumeric-2009-12-16.xsd".



XBRL validation

XBRL-minisql only stores validated XBRL instances in database. The validation process is composed of two major parts including XML validation that checks for the integrity of each XML file in an XBRL instance, and XBRL specific validation which enforces a valid XBRL instance be conformed to crucial components of XBRL 2.1 specification, or inline XBRL 1.1 specification.


XML validation

XML validation is performed by using Perl module XML::Validate, which uses libxml2 backend to validate the integrity and well-formedness of XML file. XML validation is performed before any XBRL specific validation, and any syntax or structure errors in xsd file, presentation, calculation, definition, or data xml file result in abortion of the parsing of an XBRL instance. Check here for more readings on libxml2 specifications. Note that the XML validation here is not against xsd file, which is performed in XBRL specific validation.


XBRL specific validation

XBRL specific validation makes sure an XBRL instance conforms to major specifications of XBRL 2.1 or inline XBRL 1.1 that are crucial to the proper extraction of material business facts (e.g. key ratios). Specifically, the following primary rules are enforced.

There MUST be presence of a xsd file, a data xml file, and a label xml file in an XBRL instance. The presence of presentation, calculation, and definition XML files are optional.
Each namespace abbreviation appeared in either element or attribute name MUST have a mapping full namespace that can be found elsewhere in an XBRL instance.
<roleType> element in xsd file MUST have @roleURI attribute.
<roleRef> element in presentation, calculation, and definition files MUST have both @roleURI and @href attributes.
<presentationLink> element in presentation file MUST have @role attribute.
<presentationArc> element in presentation file MUST have both "from" and "to" abbributes.
<calculationLink> element in calculation file MUST have @role attribute.
<calculationArc> element in calculation file MUST have @from, @to, and @weight abbributes.
<definitionLink> element in definition file MUST have @role attribute.
<definitionArc> element in definition file MUST have both @from and @to abbributes.
All elements in data xml file MUST have mapping records in xsd file (this is rule is no longer applied starting from 2018-01-01, since we noticed that an increasing number of XBRL filings failed to provide xsd definition for certain elements in data xml file).

Any error(s) violating above listed rules result(s) in abortion of parsing process. There are also other rules which generate warning message (e.g. @contextRef attribute without <context> with mapping id), but does not abort the parsing.

select sum(numericvalue) from xbrls left join xbrlpres on xbrls.zipfilename = xbrlpres.zipfilename left join xbrldts on xbrlpres.zipfilename = xbrldts.zipfilename and xbrlpres.tolabel = xbrldts.tolabel where symbol="AAPL" and xbrls.fdate='2016-04-27' and xbrlpres.xmlpart = 'CAL' and dateend='2016-03-26' and xbrlpres.roletitle like "%consolidated balance sheet%" and xbrlpres.fromlabel like "%asset%cu rrent%" and (xbrldts.metadata = '' or xbrldts.metadata is null);