Topic review: Open sqlite database under kmymoney versions >= 5.0

[michael at kaddik.de]

An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/kmymoney-devel/attachments/20190720/4faeec76/attachment.html>
-------------- next part --------------
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `kmmTransactions` (
	`id`	varchar ( 32 ) NOT NULL,
	`txType`	char ( 1 ),
	`postDate`	timestamp,
	`memo`	mediumtext,
	`entryDate`	timestamp,
	`currencyId`	char ( 3 ),
	`bankId`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmTags` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	mediumtext,
	`closed`	char ( 1 ),
	`notes`	longtext,
	`tagColor`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmTagSplits` (
	`transactionId`	varchar ( 32 ) NOT NULL,
	`tagId`	varchar ( 32 ) NOT NULL,
	`splitId`	smallint unsigned NOT NULL,
	PRIMARY KEY(`transactionId`,`tagId`,`splitId`)
);
CREATE TABLE IF NOT EXISTS `kmmSplits` (
	`transactionId`	varchar ( 32 ) NOT NULL,
	`txType`	char ( 1 ),
	`splitId`	smallint unsigned NOT NULL,
	`payeeId`	varchar ( 32 ),
	`reconcileDate`	timestamp,
	`action`	varchar ( 16 ),
	`reconcileFlag`	char ( 1 ),
	`value`	text NOT NULL,
	`valueFormatted`	text,
	`shares`	text NOT NULL,
	`sharesFormatted`	mediumtext,
	`price`	text,
	`priceFormatted`	mediumtext,
	`memo`	mediumtext,
	`accountId`	varchar ( 32 ) NOT NULL,
	`costCenterId`	varchar ( 32 ),
	`checkNumber`	varchar ( 32 ),
	`postDate`	timestamp,
	`bankId`	mediumtext,
	PRIMARY KEY(`transactionId`,`splitId`)
);
CREATE TABLE IF NOT EXISTS `kmmSecurities` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`symbol`	mediumtext,
	`type`	smallint unsigned NOT NULL,
	`typeString`	mediumtext,
	`smallestAccountFraction`	varchar ( 24 ),
	`pricePrecision`	smallint unsigned NOT NULL DEFAULT 4,
	`tradingMarket`	mediumtext,
	`tradingCurrency`	char ( 3 ),
	`roundingMethod`	smallint unsigned NOT NULL DEFAULT 7,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmSchedules` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`type`	tinyint unsigned NOT NULL,
	`typeString`	mediumtext,
	`occurence`	smallint unsigned NOT NULL,
	`occurenceMultiplier`	smallint unsigned NOT NULL,
	`occurenceString`	mediumtext,
	`paymentType`	tinyint unsigned,
	`paymentTypeString`	longtext,
	`startDate`	date NOT NULL,
	`endDate`	date,
	`fixed`	char ( 1 ) NOT NULL,
	`lastDayInMonth`	char ( 1 ) NOT NULL DEFAULT 'N',
	`autoEnter`	char ( 1 ) NOT NULL,
	`lastPayment`	date,
	`nextPaymentDue`	date,
	`weekendOption`	tinyint unsigned NOT NULL,
	`weekendOptionString`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmSchedulePaymentHistory` (
	`schedId`	varchar ( 32 ) NOT NULL,
	`payDate`	date NOT NULL,
	PRIMARY KEY(`schedId`,`payDate`)
);
CREATE TABLE IF NOT EXISTS `kmmReportConfig` (
	`name`	varchar ( 255 ) NOT NULL,
	`XML`	longtext,
	`id`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmPrices` (
	`fromId`	varchar ( 32 ) NOT NULL,
	`toId`	varchar ( 32 ) NOT NULL,
	`priceDate`	date NOT NULL,
	`price`	text NOT NULL,
	`priceFormatted`	mediumtext,
	`priceSource`	mediumtext,
	PRIMARY KEY(`fromId`,`toId`,`priceDate`)
);
CREATE TABLE IF NOT EXISTS `kmmPluginInfo` (
	`iid`	varchar ( 255 ) NOT NULL,
	`versionMajor`	tinyint unsigned NOT NULL,
	`versionMinor`	tinyint unsigned,
	`uninstallQuery`	longtext,
	PRIMARY KEY(`iid`)
);
CREATE TABLE IF NOT EXISTS `kmmPayeesPayeeIdentifier` (
	`payeeId`	varchar ( 32 ) NOT NULL,
	`userOrder`	smallint unsigned NOT NULL,
	`identifierId`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`payeeId`,`userOrder`)
);
CREATE TABLE IF NOT EXISTS `kmmPayees` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	mediumtext,
	`reference`	mediumtext,
	`email`	mediumtext,
	`addressStreet`	mediumtext,
	`addressCity`	mediumtext,
	`addressZipcode`	mediumtext,
	`addressState`	mediumtext,
	`telephone`	mediumtext,
	`notes`	longtext,
	`defaultAccountId`	varchar ( 32 ),
	`matchData`	tinyint unsigned,
	`matchIgnoreCase`	char ( 1 ),
	`matchKeys`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmPayeeIdentifier` (
	`id`	varchar ( 32 ) NOT NULL,
	`type`	varchar ( 255 ),
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmOnlineJobs` (
	`id`	varchar ( 32 ) NOT NULL,
	`type`	varchar ( 255 ) NOT NULL,
	`jobSend`	timestamp,
	`bankAnswerDate`	timestamp,
	`state`	varchar ( 15 ) NOT NULL,
	`locked`	char ( 1 ) NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmKeyValuePairs` (
	`kvpType`	varchar ( 16 ) NOT NULL,
	`kvpId`	varchar ( 32 ),
	`kvpKey`	varchar ( 255 ) NOT NULL,
	`kvpData`	mediumtext
);
CREATE TABLE IF NOT EXISTS `kmmInstitutions` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`manager`	mediumtext,
	`routingCode`	mediumtext,
	`addressStreet`	mediumtext,
	`addressCity`	mediumtext,
	`addressZipcode`	mediumtext,
	`telephone`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmFileInfo` (
	`version`	varchar ( 16 ),
	`created`	date,
	`lastModified`	date,
	`baseCurrency`	char ( 3 ),
	`institutions`	bigint unsigned,
	`accounts`	bigint unsigned,
	`payees`	bigint unsigned,
	`tags`	bigint unsigned,
	`transactions`	bigint unsigned,
	`splits`	bigint unsigned,
	`securities`	bigint unsigned,
	`prices`	bigint unsigned,
	`currencies`	bigint unsigned,
	`schedules`	bigint unsigned,
	`reports`	bigint unsigned,
	`kvps`	bigint unsigned,
	`dateRangeStart`	date,
	`dateRangeEnd`	date,
	`hiInstitutionId`	bigint unsigned,
	`hiPayeeId`	bigint unsigned,
	`hiTagId`	bigint unsigned,
	`hiAccountId`	bigint unsigned,
	`hiTransactionId`	bigint unsigned,
	`hiScheduleId`	bigint unsigned,
	`hiSecurityId`	bigint unsigned,
	`hiReportId`	bigint unsigned,
	`encryptData`	varchar ( 255 ),
	`updateInProgress`	char ( 1 ),
	`budgets`	bigint unsigned,
	`hiBudgetId`	bigint unsigned,
	`hiOnlineJobId`	bigint unsigned,
	`hiPayeeIdentifierId`	bigint unsigned,
	`logonUser`	varchar ( 255 ),
	`logonAt`	timestamp,
	`fixLevel`	int unsigned
);
CREATE TABLE IF NOT EXISTS `kmmCurrencies` (
	`ISOcode`	char ( 3 ) NOT NULL,
	`name`	text NOT NULL,
	`type`	smallint unsigned,
	`typeString`	mediumtext,
	`symbol1`	smallint unsigned,
	`symbol2`	smallint unsigned,
	`symbol3`	smallint unsigned,
	`symbolString`	varchar ( 255 ),
	`smallestCashFraction`	varchar ( 24 ),
	`smallestAccountFraction`	varchar ( 24 ),
	`pricePrecision`	smallint unsigned NOT NULL DEFAULT 4,
	PRIMARY KEY(`ISOcode`)
);
CREATE TABLE IF NOT EXISTS `kmmCostCenter` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmBudgetConfig` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`start`	date NOT NULL,
	`XML`	longtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmAccountsPayeeIdentifier` (
	`accountId`	varchar ( 32 ) NOT NULL,
	`userOrder`	smallint unsigned NOT NULL,
	`identifierId`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`accountId`,`userOrder`)
);
CREATE TABLE IF NOT EXISTS `kmmAccounts` (
	`id`	varchar ( 32 ) NOT NULL,
	`institutionId`	varchar ( 32 ),
	`parentId`	varchar ( 32 ),
	`lastReconciled`	timestamp,
	`lastModified`	timestamp,
	`openingDate`	date,
	`accountNumber`	mediumtext,
	`accountType`	varchar ( 16 ) NOT NULL,
	`accountTypeString`	mediumtext,
	`isStockAccount`	char ( 1 ),
	`accountName`	mediumtext,
	`description`	mediumtext,
	`currencyId`	varchar ( 32 ),
	`balance`	mediumtext,
	`balanceFormatted`	mediumtext,
	`transactionCount`	bigint unsigned,
	PRIMARY KEY(`id`)
);
CREATE INDEX IF NOT EXISTS `kmmSplits_kmmSplitsaccount_type_idx` ON `kmmSplits` (
	`accountId`,
	`txType`
);
CREATE INDEX IF NOT EXISTS `kmmKeyValuePairs_type_id_idx` ON `kmmKeyValuePairs` (
	`kvpType`,
	`kvpId`
);
CREATE VIEW kmmBalances AS SELECT kmmAccounts.id AS id, kmmAccounts.currencyId, kmmSplits.txType, kmmSplits.value, kmmSplits.shares, kmmSplits.postDate AS balDate, kmmTransactions.currencyId AS txCurrencyId FROM kmmAccounts, kmmSplits, kmmTransactions WHERE kmmSplits.txType = 'N' AND kmmSplits.accountId = kmmAccounts.id AND kmmSplits.transactionId = kmmTransactions.id;
COMMIT;
-------------- next part --------------
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `kmmTransactions` (
	`id`	varchar ( 32 ) NOT NULL,
	`txType`	char ( 1 ),
	`postDate`	timestamp,
	`memo`	mediumtext,
	`entryDate`	timestamp,
	`currencyId`	char ( 3 ),
	`bankId`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmTags` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	mediumtext,
	`closed`	char ( 1 ),
	`notes`	longtext,
	`tagColor`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmTagSplits` (
	`transactionId`	varchar ( 32 ) NOT NULL,
	`tagId`	varchar ( 32 ) NOT NULL,
	`splitId`	smallint unsigned NOT NULL,
	PRIMARY KEY(`transactionId`,`tagId`,`splitId`)
);
CREATE TABLE IF NOT EXISTS `kmmSplits` (
	`transactionId`	varchar ( 32 ) NOT NULL,
	`txType`	char ( 1 ),
	`splitId`	smallint unsigned NOT NULL,
	`payeeId`	varchar ( 32 ),
	`reconcileDate`	timestamp,
	`action`	varchar ( 16 ),
	`reconcileFlag`	char ( 1 ),
	`value`	text NOT NULL,
	`valueFormatted`	text,
	`shares`	text NOT NULL,
	`sharesFormatted`	mediumtext,
	`price`	text,
	`priceFormatted`	mediumtext,
	`memo`	mediumtext,
	`accountId`	varchar ( 32 ) NOT NULL,
	`checkNumber`	varchar ( 32 ),
	`postDate`	timestamp,
	`bankId`	mediumtext,
	PRIMARY KEY(`transactionId`,`splitId`)
);
CREATE TABLE IF NOT EXISTS `kmmSecurities` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`symbol`	mediumtext,
	`type`	smallint unsigned NOT NULL,
	`typeString`	mediumtext,
	`smallestAccountFraction`	varchar ( 24 ),
	`tradingMarket`	mediumtext,
	`tradingCurrency`	char ( 3 ),
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmSchedules` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`type`	tinyint unsigned NOT NULL,
	`typeString`	mediumtext,
	`occurence`	smallint unsigned NOT NULL,
	`occurenceMultiplier`	smallint unsigned NOT NULL,
	`occurenceString`	mediumtext,
	`paymentType`	tinyint unsigned,
	`paymentTypeString`	longtext,
	`startDate`	date NOT NULL,
	`endDate`	date,
	`fixed`	char ( 1 ) NOT NULL,
	`autoEnter`	char ( 1 ) NOT NULL,
	`lastPayment`	date,
	`nextPaymentDue`	date,
	`weekendOption`	tinyint unsigned NOT NULL,
	`weekendOptionString`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmSchedulePaymentHistory` (
	`schedId`	varchar ( 32 ) NOT NULL,
	`payDate`	date NOT NULL,
	PRIMARY KEY(`schedId`,`payDate`)
);
CREATE TABLE IF NOT EXISTS `kmmReportConfig` (
	`name`	varchar ( 255 ) NOT NULL,
	`XML`	longtext,
	`id`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmPrices` (
	`fromId`	varchar ( 32 ) NOT NULL,
	`toId`	varchar ( 32 ) NOT NULL,
	`priceDate`	date NOT NULL,
	`price`	text NOT NULL,
	`priceFormatted`	mediumtext,
	`priceSource`	mediumtext,
	PRIMARY KEY(`fromId`,`toId`,`priceDate`)
);
CREATE TABLE IF NOT EXISTS `kmmPluginInfo` (
	`iid`	varchar ( 255 ) NOT NULL,
	`versionMajor`	tinyint unsigned NOT NULL,
	`versionMinor`	tinyint unsigned,
	`uninstallQuery`	longtext,
	PRIMARY KEY(`iid`)
);
CREATE TABLE IF NOT EXISTS `kmmPayeesPayeeIdentifier` (
	`payeeId`	varchar ( 32 ) NOT NULL,
	`order`	smallint unsigned NOT NULL,
	`identifierId`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`payeeId`,`order`)
);
CREATE TABLE IF NOT EXISTS `kmmPayees` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	mediumtext,
	`reference`	mediumtext,
	`email`	mediumtext,
	`addressStreet`	mediumtext,
	`addressCity`	mediumtext,
	`addressZipcode`	mediumtext,
	`addressState`	mediumtext,
	`telephone`	mediumtext,
	`notes`	longtext,
	`defaultAccountId`	varchar ( 32 ),
	`matchData`	tinyint unsigned,
	`matchIgnoreCase`	char ( 1 ),
	`matchKeys`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmPayeeIdentifier` (
	`id`	varchar ( 32 ) NOT NULL,
	`type`	varchar ( 255 ),
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmOnlineJobs` (
	`id`	varchar ( 32 ) NOT NULL,
	`type`	varchar ( 255 ) NOT NULL,
	`jobSend`	timestamp,
	`bankAnswerDate`	timestamp,
	`state`	varchar ( 15 ) NOT NULL,
	`locked`	char ( 1 ) NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmKeyValuePairs` (
	`kvpType`	varchar ( 16 ) NOT NULL,
	`kvpId`	varchar ( 32 ),
	`kvpKey`	varchar ( 255 ) NOT NULL,
	`kvpData`	mediumtext
);
CREATE TABLE IF NOT EXISTS `kmmInstitutions` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`manager`	mediumtext,
	`routingCode`	mediumtext,
	`addressStreet`	mediumtext,
	`addressCity`	mediumtext,
	`addressZipcode`	mediumtext,
	`telephone`	mediumtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmFileInfo` (
	`version`	varchar ( 16 ),
	`created`	date,
	`lastModified`	date,
	`baseCurrency`	char ( 3 ),
	`institutions`	bigint unsigned,
	`accounts`	bigint unsigned,
	`payees`	bigint unsigned,
	`tags`	bigint unsigned,
	`transactions`	bigint unsigned,
	`splits`	bigint unsigned,
	`securities`	bigint unsigned,
	`prices`	bigint unsigned,
	`currencies`	bigint unsigned,
	`schedules`	bigint unsigned,
	`reports`	bigint unsigned,
	`kvps`	bigint unsigned,
	`dateRangeStart`	date,
	`dateRangeEnd`	date,
	`hiInstitutionId`	bigint unsigned,
	`hiPayeeId`	bigint unsigned,
	`hiTagId`	bigint unsigned,
	`hiAccountId`	bigint unsigned,
	`hiTransactionId`	bigint unsigned,
	`hiScheduleId`	bigint unsigned,
	`hiSecurityId`	bigint unsigned,
	`hiReportId`	bigint unsigned,
	`encryptData`	varchar ( 255 ),
	`updateInProgress`	char ( 1 ),
	`budgets`	bigint unsigned,
	`hiBudgetId`	bigint unsigned,
	`hiOnlineJobId`	bigint unsigned,
	`hiPayeeIdentifierId`	bigint unsigned,
	`logonUser`	varchar ( 255 ),
	`logonAt`	timestamp,
	`fixLevel`	int unsigned
);
CREATE TABLE IF NOT EXISTS `kmmCurrencies` (
	`ISOcode`	char ( 3 ) NOT NULL,
	`name`	text NOT NULL,
	`type`	smallint unsigned,
	`typeString`	mediumtext,
	`symbol1`	smallint unsigned,
	`symbol2`	smallint unsigned,
	`symbol3`	smallint unsigned,
	`symbolString`	varchar ( 255 ),
	`partsPerUnit`	varchar ( 24 ),
	`smallestCashFraction`	varchar ( 24 ),
	`smallestAccountFraction`	varchar ( 24 ),
	PRIMARY KEY(`ISOcode`)
);
CREATE TABLE IF NOT EXISTS `kmmCostCenter` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmBudgetConfig` (
	`id`	varchar ( 32 ) NOT NULL,
	`name`	text NOT NULL,
	`start`	date NOT NULL,
	`XML`	longtext,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `kmmAccountsPayeeIdentifier` (
	`accountId`	varchar ( 32 ) NOT NULL,
	`order`	smallint unsigned NOT NULL,
	`identifierId`	varchar ( 32 ) NOT NULL,
	PRIMARY KEY(`accountId`,`order`)
);
CREATE TABLE IF NOT EXISTS `kmmAccounts` (
	`id`	varchar ( 32 ) NOT NULL,
	`institutionId`	varchar ( 32 ),
	`parentId`	varchar ( 32 ),
	`lastReconciled`	timestamp,
	`lastModified`	timestamp,
	`openingDate`	date,
	`accountNumber`	mediumtext,
	`accountType`	varchar ( 16 ) NOT NULL,
	`accountTypeString`	mediumtext,
	`isStockAccount`	char ( 1 ),
	`accountName`	mediumtext,
	`description`	mediumtext,
	`currencyId`	varchar ( 32 ),
	`balance`	mediumtext,
	`balanceFormatted`	mediumtext,
	`transactionCount`	bigint unsigned,
	PRIMARY KEY(`id`)
);
CREATE INDEX IF NOT EXISTS `kmmSplits_kmmSplitsaccount_type_idx` ON `kmmSplits` (
	`accountId`,
	`txType`
);
CREATE INDEX IF NOT EXISTS `kmmKeyValuePairs_type_id_idx` ON `kmmKeyValuePairs` (
	`kvpType`,
	`kvpId`
);
COMMIT;