Hi,
there are really some differences between an actual and my kmymoney db.
Is there a documentation existing how to do the change?
Thanks
photomike
--
Michael Kaddik
Lerchenweg 9
78126 Königsfeld-Erdmannsweiler
+49 (0) 7725 482512
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;
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;
