def loadXbrlFromDB(self, loadDBsaveToFile):
# load from database
modelXbrl = self.modelXbrl
# find instance in DB
instanceURI = os.path.basename(loadDBsaveToFile)
results = self.execute("SELECT InstanceID, ModuleID, EntityScheme, EntityIdentifier, PeriodEndDateOrInstant"
" FROM dInstance WHERE FileName = '{}'"
.format(instanceURI))
instanceId = moduleId = None
for instanceId, moduleId, entScheme, entId, datePerEnd in results:
break
# find module in DB
results = self.execute("SELECT XbrlSchemaRef FROM mModule WHERE ModuleID = {}".format(moduleId))
xbrlSchemaRef = None
for result in results:
xbrlSchemaRef = result[0]
break
if not instanceId or not xbrlSchemaRef:
raise XPDBException("sqlDB:MissingDTS",
_("The instance and module were not found for %(instanceURI)"),
instanceURI = instanceURI)
if modelXbrl.skipDTS:
# find prefixes and namespaces in DB
results = self.execute("SELECT * FROM [vwGetNamespacesPrefixes]")
dpmPrefixedNamespaces = dict((prefix, namespace)
for owner, prefix, namespace in results)
# create the instance document and resulting filing
modelXbrl.blockDpmDBrecursion = True
modelXbrl.modelDocument = createModelDocument(modelXbrl,
Type.INSTANCE,
loadDBsaveToFile,
schemaRefs=[xbrlSchemaRef],
isEntry=True)
ValidateXbrlDimensions.loadDimensionDefaults(modelXbrl) # needs dimension defaults
addProcessingInstruction(modelXbrl.modelDocument.xmlRootElement,
'xbrl-streamable-instance',
'version="1.0" contextBuffer="1"')
# add roleRef and arcroleRef (e.g. for footnotes, if any, see inlineXbrlDocue)
# filing indicator code IDs
# get filing indicators
results = self.execute("SELECT mToT.TemplateOrTableCode "
" FROM dFilingIndicator dFI, mTemplateOrTable mToT "
" WHERE dFI.InstanceID = {} AND mTot.TemplateOrTableID = dFI.BusinessTemplateID"
.format(instanceId))
filingIndicatorCodes = [code[0] for code in results]
if filingIndicatorCodes:
modelXbrl.createContext(entScheme,
entId,
'instant',
None,
datePerEnd,
None, # no dimensional validity checking (like formula does)
{}, [], [],
id='c')
filingIndicatorsTuple = modelXbrl.createFact(qnFindFilingIndicators)
for filingIndicatorCode in filingIndicatorCodes:
modelXbrl.createFact(qnFindFilingIndicator,
parent=filingIndicatorsTuple,
attributes={"contextRef": "c"},
text=filingIndicatorCode)
# facts in this instance
factsTbl = self.execute("SELECT DataPointSignature, DataPointSignatureWithValuesForWildcards,"
" Unit, Decimals, NumericValue, DateTimeValue, BooleanValue, TextValue "
"FROM dFact WHERE InstanceID = {} "
"ORDER BY substr(CASE WHEN DataPointSignatureWithValuesForWildcards IS NULL "
" THEN DataPointSignature"
" ELSE DataPointSignatureWithValuesForWildcards"
" END, instr(DataPointSignature,'|') + 1)"
.format(instanceId))
# results tuple: factId, dec, varId, dpKey, entId, datePerEnd, unit, numVal, dateVal, boolVal, textVal
# get typed dimension values
prefixedNamespaces = modelXbrl.prefixedNamespaces
prefixedNamespaces["iso4217"] = XbrlConst.iso4217
if modelXbrl.skipDTS:
prefixedNamespaces.update(dpmPrefixedNamespaces) # for skipDTS this is always needed
cntxTbl = {} # index by d
unitTbl = {}
def typedDimElt(s):
# add xmlns into s for known qnames
tag, angleBrkt, rest = s[1:].partition('>')
text, angleBrkt, rest = rest.partition("<")
qn = qname(tag, prefixedNamespaces)
# a modelObject xml element is needed for all of the instance functions to manage the typed dim
return addChild(modelXbrl.modelDocument, qn, text=text, appendChild=False)
# contexts and facts
for dpSig, dpSigTypedDims, unit, dec, numVal, dateVal, boolVal, textVal in factsTbl:
metric, sep, dims = (dpSigTypedDims or dpSig).partition('|')
conceptQn = qname(metric.partition('(')[2][:-1], prefixedNamespaces)
concept = modelXbrl.qnameConcepts.get(conceptQn)
isNumeric = isBool = isDateTime = isQName = isText = False
if concept is not None:
if concept.isNumeric:
isNumeric = True
else:
baseXbrliType = concept.baseXbrliType
if baseXbrliType == "booleanItemType":
isBool = True
elif baseXbrliType == "dateTimeItemType": # also is dateItemType?
isDateTime = True
elif baseXbrliType == "QNameItemType":
isQName = True
else:
c = conceptQn.localName[0]
if c == 'm':
isNumeric = True
elif c == 'd':
isDateTime = True
elif c == 'b':
isBool = True
elif c == 'e':
isQName = True
isText = not (isNumeric or isBool or isDateTime or isQName)
if isinstance(datePerEnd, _STR_BASE):
datePerEnd = datetimeValue(datePerEnd, addOneDay=True)
cntxKey = (dims, entId, datePerEnd)
if cntxKey in cntxTbl:
cntxId = cntxTbl[cntxKey]
else:
cntxId = 'c-{:02}'.format(len(cntxTbl) + 1)
cntxTbl[cntxKey] = cntxId
qnameDims = {}
if dims:
for dim in dims.split('|'):
dQn, sep, dVal = dim[:-1].partition('(')
dimQname = qname(dQn, prefixedNamespaces)
if dVal.startswith('<'):
mem = typedDimElt(dVal) # typed dim
else:
mem = qname(dVal, prefixedNamespaces) # explicit dim (even if treat-as-typed)
qnameDims[dimQname] = DimValuePrototype(modelXbrl, None, dimQname, mem, "scenario")
modelXbrl.createContext(entScheme,
entId,
'instant',
None,
datePerEnd,
None, # no dimensional validity checking (like formula does)
qnameDims, [], [],
id=cntxId)
if unit:
if unit in unitTbl:
unitId = unitTbl[unit]
else:
unitQn = qname(unit, prefixedNamespaces)
unitId = 'u{}'.format(unitQn.localName)
unitTbl[unit] = unitId
modelXbrl.createUnit([unitQn], [], id=unitId)
else:
unitId = None
attrs = {"contextRef": cntxId}
if unitId:
attrs["unitRef"] = unitId
if dec is not None:
if isinstance(dec, float): # must be an integer
dec = int(dec)
elif isinstance(dec, _STR_BASE) and '.' in dec:
dec = dec.partition('.')[0] # drop .0 from any SQLite string
attrs["decimals"] = str(dec) # somehow it is float from the database
if False: # fact.isNil:
attrs[XbrlConst.qnXsiNil] = "true"
text = None
elif numVal is not None:
num = roundValue(numVal, None, dec) # round using reported decimals
if dec is None or dec == "INF": # show using decimals or reported format
dec = len(numVal.partition(".")[2])
else: # max decimals at 28
dec = max( min(int(float(dec)), 28), -28) # 2.7 wants short int, 3.2 takes regular int, don't use _INT here
text = Locale.format(self.modelXbrl.locale, "%.*f", (dec, num))
elif dateVal is not None:
text = dateVal
elif boolVal is not None:
text = 'true' if boolVal.lower() in ('t', 'true', '1') else 'false'
else:
if isQName: # declare namespace
addQnameValue(modelXbrl.modelDocument, qname(textVal, prefixedNamespaces))
text = textVal
modelXbrl.createFact(conceptQn, attributes=attrs, text=text)
# add footnotes if any
# save to file
modelXbrl.saveInstance(overrideFilepath=loadDBsaveToFile)
modelXbrl.modelManager.showStatus(_("Saved extracted instance"), 5000)
return modelXbrl.modelDocument
def loadXbrlFromDB(self, loadDBsaveToFile):
# load from database
modelXbrl = self.modelXbrl
# find instance in DB
instanceURI = os.path.basename(loadDBsaveToFile)
results = self.execute("SELECT InstanceID, ModuleID, EntityScheme, EntityIdentifier, PeriodEndDateOrInstant"
" FROM dInstance WHERE FileName = '{}'"
.format(instanceURI))
instanceId = moduleId = None
for instanceId, moduleId, entScheme, entId, datePerEnd in results:
break
# find module in DB
results = self.execute("SELECT XbrlSchemaRef FROM mModule WHERE ModuleID = {}".format(moduleId))
xbrlSchemaRef = None
for result in results:
xbrlSchemaRef = result[0]
break
if not instanceId or not xbrlSchemaRef:
raise XPDBException("sqlDB:MissingDTS",
_("The instance and module were not found for %(instanceURI)"),
instanceURI = instanceURI)
if modelXbrl.skipDTS:
# find prefixes and namespaces in DB
results = self.execute("SELECT * FROM [vwGetNamespacesPrefixes]")
dpmPrefixedNamespaces = dict((prefix, namespace)
for owner, prefix, namespace in results)
# create the instance document and resulting filing
modelXbrl.blockDpmDBrecursion = True
modelXbrl.modelDocument = createModelDocument(modelXbrl,
Type.INSTANCE,
loadDBsaveToFile,
schemaRefs=[xbrlSchemaRef],
isEntry=True)
ValidateXbrlDimensions.loadDimensionDefaults(modelXbrl) # needs dimension defaults
addProcessingInstruction(modelXbrl.modelDocument.xmlRootElement,
'xbrl-streamable-instance',
'version="1.0" contextBuffer="1"')
# add roleRef and arcroleRef (e.g. for footnotes, if any, see inlineXbrlDocue)
# filing indicator code IDs
# get filing indicators
results = self.execute("SELECT mToT.TemplateOrTableCode "
" FROM dFilingIndicator dFI, mTemplateOrTable mToT "
" WHERE dFI.InstanceID = {} AND mTot.TemplateOrTableID = dFI.BusinessTemplateID"
.format(instanceId))
filingIndicatorCodes = [code[0] for code in results]
if filingIndicatorCodes:
modelXbrl.createContext(entScheme,
entId,
'instant',
None,
datePerEnd,
None, # no dimensional validity checking (like formula does)
{}, [], [],
id='c')
filingIndicatorsTuple = modelXbrl.createFact(qnFindFilingIndicators)
for filingIndicatorCode in filingIndicatorCodes:
modelXbrl.createFact(qnFindFilingIndicator,
parent=filingIndicatorsTuple,
attributes={"contextRef": "c"},
text=filingIndicatorCode)
# facts in this instance
factsTbl = self.execute("SELECT DataPointSignature, DataPointSignatureWithValuesForWildcards,"
" Unit, Decimals, NumericValue, DateTimeValue, BooleanValue, TextValue "
"FROM dFact WHERE InstanceID = {} "
"ORDER BY substr(CASE WHEN DataPointSignatureWithValuesForWildcards IS NULL "
" THEN DataPointSignature"
" ELSE DataPointSignatureWithValuesForWildcards"
" END, instr(DataPointSignature,'|') + 1)"
.format(instanceId))
# results tuple: factId, dec, varId, dpKey, entId, datePerEnd, unit, numVal, dateVal, boolVal, textVal
# get typed dimension values
prefixedNamespaces = modelXbrl.prefixedNamespaces
prefixedNamespaces["iso4217"] = XbrlConst.iso4217
if modelXbrl.skipDTS:
prefixedNamespaces.update(dpmPrefixedNamespaces) # for skipDTS this is always needed
cntxTbl = {} # index by d
unitTbl = {}
def typedDimElt(s):
# add xmlns into s for known qnames
tag, angleBrkt, rest = s[1:].partition('>')
text, angleBrkt, rest = rest.partition("<")
qn = qname(tag, prefixedNamespaces)
# a modelObject xml element is needed for all of the instance functions to manage the typed dim
return addChild(modelXbrl.modelDocument, qn, text=text, appendChild=False)
# contexts and facts
for dpSig, dpSigTypedDims, unit, dec, numVal, dateVal, boolVal, textVal in factsTbl:
metric, sep, dims = (dpSigTypedDims or dpSig).partition('|')
conceptQn = qname(metric.partition('(')[2][:-1], prefixedNamespaces)
concept = modelXbrl.qnameConcepts.get(conceptQn)
isNumeric = isBool = isDateTime = isQName = isText = False
if concept is not None:
if concept.isNumeric:
isNumeric = True
else:
baseXbrliType = concept.baseXbrliType
if baseXbrliType == "booleanItemType":
isBool = True
elif baseXbrliType == "dateTimeItemType": # also is dateItemType?
isDateTime = True
elif baseXbrliType == "QNameItemType":
isQName = True
else:
c = conceptQn.localName[0]
if c == 'm':
isNumeric = True
elif c == 'd':
isDateTime = True
elif c == 'b':
isBool = True
elif c == 'e':
isQName = True
isText = not (isNumeric or isBool or isDateTime or isQName)
if isinstance(datePerEnd, _STR_BASE):
datePerEnd = datetimeValue(datePerEnd, addOneDay=True)
cntxKey = (dims, entId, datePerEnd)
if cntxKey in cntxTbl:
cntxId = cntxTbl[cntxKey]
else:
cntxId = 'c-{:02}'.format(len(cntxTbl) + 1)
cntxTbl[cntxKey] = cntxId
qnameDims = {}
if dims:
for dim in dims.split('|'):
dQn, sep, dVal = dim[:-1].partition('(')
dimQname = qname(dQn, prefixedNamespaces)
if dVal.startswith('<'): # typed dim
mem = typedDimElt(dVal)
else:
mem = qname(dVal, prefixedNamespaces)
qnameDims[dimQname] = DimValuePrototype(modelXbrl, None, dimQname, mem, "scenario")
modelXbrl.createContext(entScheme,
entId,
'instant',
None,
datePerEnd,
None, # no dimensional validity checking (like formula does)
qnameDims, [], [],
id=cntxId)
if unit:
if unit in unitTbl:
unitId = unitTbl[unit]
else:
unitQn = qname(unit, prefixedNamespaces)
unitId = 'u{}'.format(unitQn.localName)
unitTbl[unit] = unitId
modelXbrl.createUnit([unitQn], [], id=unitId)
else:
unitId = None
attrs = {"contextRef": cntxId}
if unitId:
attrs["unitRef"] = unitId
if dec is not None:
if isinstance(dec, float): # must be an integer
dec = int(dec)
elif isinstance(dec, _STR_BASE) and '.' in dec:
dec = dec.partition('.')[0] # drop .0 from any SQLite string
attrs["decimals"] = str(dec) # somehow it is float from the database
if False: # fact.isNil:
attrs[XbrlConst.qnXsiNil] = "true"
text = None
elif numVal is not None:
num = roundValue(numVal, None, dec) # round using reported decimals
if dec is None or dec == "INF": # show using decimals or reported format
dec = len(numVal.partition(".")[2])
else: # max decimals at 28
dec = max( min(int(float(dec)), 28), -28) # 2.7 wants short int, 3.2 takes regular int, don't use _INT here
text = Locale.format(self.modelXbrl.locale, "%.*f", (dec, num))
elif dateVal is not None:
text = dateVal
elif boolVal is not None:
text = 'true' if boolVal.lower() in ('t', 'true', '1') else 'false'
else:
if isQName: # declare namespace
addQnameValue(modelXbrl.modelDocument, qname(textVal, prefixedNamespaces))
text = textVal
modelXbrl.createFact(conceptQn, attributes=attrs, text=text)
# add footnotes if any
# save to file
modelXbrl.saveInstance(overrideFilepath=loadDBsaveToFile)
modelXbrl.modelManager.showStatus(_("Saved extracted instance"), 5000)
return modelXbrl.modelDocument