Re: String lengths in the SQL backend
Having said that, we shouldn't rule out users accessing the data out from under GnuCash, at least in a read-only means. I still believe that external tools should not be writing data into the GnuCash database. Have you thought about how allow other applications could use GnuCash engine to write data into the GC's database? -- Trabajar, la mejor arma para tu superación de grano en grano, se hace la arena (R) (en trámite, pero para los cuates: LIBRE) ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Quoting Daniel Espinosa [EMAIL PROTECTED]: Having said that, we shouldn't rule out users accessing the data out from under GnuCash, at least in a read-only means. I still believe that external tools should not be writing data into the GnuCash database. Have you thought about how allow other applications could use GnuCash engine to write data into the GC's database? Absolutely. They should use the GnuCash API. However the GnuCash API is currently not exported in a way to make this easy. However in the long term that should get made easier. In the short term it's not important. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 20, 2008 11:27:03 pm Daniel Espinosa wrote: If you look at libqof's code you'll find that all GC's QOF implementation is based in store *all* data object in memory using a GHashTable using a string for the object type and GUID for identification, any search for any object is a foreach routine. This implementation is good for XML data store, but for DB is quite poore feature, becouse hides all DB's data manipulation and data integrity checks. If GC could allow any other to access the data from the DB in a way of SQL queries or DataModels, taking care about Accounts, Transactions, and other objects for data consistency and integrity, could allows others to take lot advantages. Hi Daniel, I *completely* agree with you that I would like to see gnucash become a true database app. At this point, however, it isn't, and will take a lot of work to get there. It is a change, not only in the back end storage, but also in how the data is accessed. Perhaps, once the db back end is released, work can happen to make those other changes. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 20, 2008 06:38:14 pm Phil Longstaff wrote: On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. OK. I have modified code (not committed yet) which doesn't read tx/splits at startup. At startup, it executes: SELECT account_guid, reconcile_state, sum(quantity_num) AS quantity_num, quantity_denom FROM splits GROUP BY account_guid, reconcile_state, quantity_denom This gives me 1 row per guid/reconcile_state/quantity_denom triplet. From these, I set the start balance, start cleared balance and start reconciled balance for each account. When the backend receives a query to load splits for an account, it uses this algorithm: 1: save start and end balance, cleared balance and reconciled balance for each account 2: stop qof events 3: load all transactions which contains splits for the target account. This loads all splits for those transactions, even those in other accounts. 4: for each account, get new end balances. If end balance doesn't match end balance from step 1, calculate old_start_balance-(new_end_balance- old_end_balance) and set this as the new start balance. 5: resume qof events IOW, if the end balance for an account increases by X because of newly loaded splits, step 4 decreases the start balance by X to make the end balance match what is should be. Eventually, once all tx/splits are loaded, all start balances are 0. Each account that is the target of a query for all splits is marked so that it's splits are never reloaded. Done as a performance improvement but would need to be revisited for a multi-user back end. I need a bit more testing, but once I'm happy with this, I'll commit it. It does lead to a delay when opening an account with a lot of splits. I may look at adding a progress bar or other indication that the app is busy. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reading whole db at startup (was Re: String lengths in the SQL backend)
On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reading whole db at startup (was Re: String lengths in the SQL backend)
If you look at libqof's code you'll find that all GC's QOF implementation is based in store *all* data object in memory using a GHashTable using a string for the object type and GUID for identification, any search for any object is a foreach routine. This implementation is good for XML data store, but for DB is quite poore feature, becouse hides all DB's data manipulation and data integrity checks. If GC could allow any other to access the data from the DB in a way of SQL queries or DataModels, taking care about Accounts, Transactions, and other objects for data consistency and integrity, could allows others to take lot advantages. 2008/11/20 Phil Longstaff [EMAIL PROTECTED] On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I was just looking at the pg backend code again. In single user mode, it *did* read all of the transaction data at startup time. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- Trabajar, la mejor arma para tu superación de grano en grano, se hace la arena (R) (en trámite, pero para los cuates: LIBRE) ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Marcus Wolschon [EMAIL PROTECTED] writes: 2008/11/18 Derek Atkins [EMAIL PROTECTED]: Marcus Wolschon [EMAIL PROTECTED] writes: Or using stored procedures to create transactions uppon changes being made in other databases on the same DBMS (like a webshop-database). We cannot depend on Stored Procedures because not all the supported DBs support them. No but an individual user with a specific database can code some for his own system. Such specific stuff would be of no use for others so it does not need to be general. I was just pointing out what new posibilities computer-literate users will have. Sure, but personally I think we need to focus on what GnuCash itself needs, not what an industrious user could do outside of the GnuCash application. The latter set is very large but just derails the constructive discussion of what GnuCash itself needs to operate cleanly and effectively. Having said that, we shouldn't rule out users accessing the data out from under GnuCash, at least in a read-only means. I still believe that external tools should not be writing data into the GnuCash database. Marcus -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Derek Atkins wrote: If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. For me, the biggest benefit is accessability of the data. XML query tools don't match their SQL counterparts even remotely. I'd go as far as saying that XML access to the data is a virtual impossibility. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
2008/11/18 Rolf Leggewie [EMAIL PROTECTED]: Derek Atkins wrote: If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. For me, the biggest benefit is accessability of the data. XML query tools don't match their SQL counterparts even remotely. I'd go as far as saying that XML access to the data is a virtual impossibility. You mean like Jasper Reports or Crystal? Or using stored procedures to create transactions uppon changes being made in other databases on the same DBMS (like a webshop-database). Both would be a huge benefit. Marcus Wolschon ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Rolf Leggewie wrote: While it will complicate things a little bit, I may probably use the guid space as key which is inherently larger than the code space. Not only does it complicate things quite a bit (I have yet to find a tool that allows to link SQL tables and update the data in them - https://bugzilla.novell.com/show_bug.cgi?id=444576), I think it also breaks down when the code field has duplicate entries in the original gnucash data. For the german SKR, this is not yet the case, but it may happen eventually. I've gone back to using code as the key to link the tables. The shortcomings that imposes are currently more tolerable than having it the other way round. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Marcus Wolschon [EMAIL PROTECTED] writes: Or using stored procedures to create transactions uppon changes being made in other databases on the same DBMS (like a webshop-database). We cannot depend on Stored Procedures because not all the supported DBs support them. Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Rolf Leggewie [EMAIL PROTECTED] writes: One potentially VERY huge advantage of the SQL backend that I forgot is of course simultaneous access by more than one user. Multi-user access is not a goal in the first release, but it is certainly something that would be nice to add. And yes, a SQL Backend is necessary but not sufficient for multi-user. Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Derek Atkins wrote: Multi-user access is not a goal in the first release, but it is certainly something that would be nice to add. And yes, a SQL Backend is necessary but not sufficient for multi-user. Not addressing the multi user issue now will cause a significant number of bugs to be reported down the line, as there is nothing stopping two people from accessing the same database. Regards, Graham -- smime.p7s Description: S/MIME Cryptographic Signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
2008/11/18 Derek Atkins [EMAIL PROTECTED]: Marcus Wolschon [EMAIL PROTECTED] writes: Or using stored procedures to create transactions uppon changes being made in other databases on the same DBMS (like a webshop-database). We cannot depend on Stored Procedures because not all the supported DBs support them. No but an individual user with a specific database can code some for his own system. Such specific stuff would be of no use for others so it does not need to be general. I was just pointing out what new posibilities computer-literate users will have. Marcus ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Loading whole db at startup (was Re: String lengths in the SQL backend)
On November 16, 2008 02:01:05 pm Derek Atkins wrote: Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. I've removed gnucash-user because this is getting into development topics. I thought I was doing what the pg backend was doing, and it wasn't working. I just looked again and found a difference - the pg backend was setting the starting balances, and I was setting the ending balances. I'll try again to not load the transactions and set just the starting balances (balance/cleared balance/reconciled balance). Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Re: String lengths in the SQL backend
Ian Smith-Heisters wrote: On Wed, Nov 12, 2008 at 6:11 PM, Derek Atkins [EMAIL PROTECTED] wrote: Hi, Quoting Eric Anopolsky [EMAIL PROTECTED]: I'm not familiar with gnucash's data structures so I'm not 100% sure what a code is, but would it be possible to identify each account by an int or a bigint instead of a code? Then this secondary table could have the account int as its foreign primary key, and you could have another table that maps account int to code with the code as a varchar(2048) as desired. Or put the account int and the code in the same table and make the int the primary key instead. Totally unreasonable? Uhh, yeah. Sorry. Totally unreasonable. The code in question is a user-input field. Historically accountants used codes instead of names to keep track of accounts. So GnuCash provides a place for you to enter in an Account Code. But it's a string, not a number. Granted, most users probably do only use numbers, but there is no requirement that it be a number. Cheers, Eric -derek As a GnuCash-naive, but MySQL-adept user, I would have recommended the same thing as Eric. The point being: never use user-input fields for keys. Sure, code needs to be a string because users want to be able to enter anything into it. So don't use it as a key. I doubt that's *totally* unreasonable, though I suspect it's practically unreasonable if you're already using code (and similar fields) as a key all over the place. Moreover, I, for one, see no reason code could not be truncated to 32 characters, except that it might point to a problematic usage of fields for both user input and application logic. But, hey, I'm not even sure how I would fill out the code field, so don't listen to me ;) Is there a functional reason for fields to be used for both user input and keying? Should a user be able to create two accounts, enter code on both, and have them magically relate? I haven't seen any functionality like this in my GnuCash journeys, so I doubt it, but it's the only reason I can fathom to use code both as user input and as a key. -ISH I agree with Ian in the context of what I would call a surrogate key, i.e., a system-generated value used in a relational database to maintain relational identity and integrity between related tables. Such a surrogate key should be meaningless to anyone/anything but the database. It does not replace, however, user-entered, user-identified values that uniquely identify a record in human-language/business-context sense. As a dba and developer, I'll use user-entered values as keys when they are (unique, of course) unchanging - i.e., names (persons, or account names) are easily changed, and so are bad candidates for keys (apart from their non-uniqueness). I use surrogate keys when it requires a combination of fields to make a unique value (a composite key), and the number of fields numbers more than three (an arbitrary number). I do this mainly for ease in writing SQL statements that join tables containing the composite key - the single-field surrogate key can replace the composite key for that join purpose. That surrogate key is still meaninless, though, and I still crate a unique constraint in the [parent] table containing the composite fields. This is probably way more technical information that an average gnucash user wants, by the main reason i would like to see a rdbms backend is so that I could write SQL queries against it. Mark Cochran ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Phil Longstaff [EMAIL PROTECTED] writes: Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. There should not be any dependence on reading in all the data from the DB. The old PG Backend certainly did not, and it worked fine. I think it got around it by having checkpoints for things like running account balances, so you only need to load current transactions, not all of them. If you're loading all data all the time then the only benefit to the DB backend over the XML backend is save-on-commit. Phil -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Phil, Phil Longstaff [EMAIL PROTECTED] writes: On November 13, 2008 10:10:53 am Derek Atkins wrote: What exactly is the use-case for using codes as a primary key? Keep in mind that the SQL Backend data store is just that, a data store. GnuCash does NOT use database constraints, and for Accounts it doesn't even need to use an Index, because GnuCash does (should!) load in all the accounts at start time. So... All searches on accounts are done in core in the gnucash app without looking at the DB backend. The use case is that one person wants to have another table which stores information based on the account code. I gather there are standard German account codes and he wants to key off of them. Possible alternatives are to key off of the account guid, or have a code key but don't require the code key in his table to be 2048 long. I must have missed this, because I don't remember reading the original request. I don't understand what additional table is required. I'm trying to think about it from the GnuCash use standpoint, not the backend storage aspect. Where in GnuCash is this extra table supposed to be used? And how? BTW, Derek is right that the SQL backend is simply a data store. When you open an SQL db, the entire contents is read in the same way the current XML file is read. Any time you add/delete/edit anything, the change is written to the db immediately, so it should help prevent data loss. Gnucash is not written as a database app. It is written assuming that all data is immediately available. Having an SQL db will open up possibilities, but the app will need a lot of work to make use of those possibilities. Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Phil Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Hello everybody, Herbert Thoma wrote: What exactly is the use-case for using codes as a primary key? I'm not following this list daily. I just joined this discussion which also concerns me to a great extent. Phil already gave a bit of explanation. I'll try to give my perspective, too. First, my motivation stems from the frustrations I have experienced over the last 18 months or so trying to get gnucash into a shape to support German business accounting. I finally had to realize that almost all components of gnucash are too inflexible (and of course my capability to change that is also limited, but I knew that before). Phil's SQL backend now gives me the opportunity to evade the rigidities of gnucash in areas where this is needed. I can immediately access the data I entered into gnucash and get results quickly. SQL allows me to slice and dice the data in any way I see fit (gnucash reporting is an absolute nightmare). While obviously most data will be supplied by gnucash, there are some additional data repositories that need to be linked in. One example is the relation from account to tax categories (no, the stuff offered by gnucash is not sufficient, those following gnucash closely will know I tried very hard). I link in data by adding tables to the MySQL database and relating that to gnucash data with the help of keys. In my opinion the mapping of account to code is a one-to-one relationship and I see no reason for account code being used as a primary key, one could (and should) use the account key. For the plain DATEV SKR04 that is true. For the way that it is being applied in gnucash, it is not. I guess, this should already be clear by the code field being available. If there was a one-to-mapping (eineindeutig), one could just use the user entered data from the code field and have that as the guid. The randomly-generated GUID itself would have become redundant data. Just one real-live example for further clarification. Gnucash offers placeholder accounts. The gnucash SKR04 makes liberal use of that to present the user with a hierarchy of accounts. The placeholder accounts don't have any entry in the code field. There is no 1:1 relation. With guid being a primary key, it naturally is a 1:n-relation, though, and this should help. That being said. What I want is a relation between the code field of an account (interpreted as DATEV code in the gnucash SKR04) and the tax category (this will be outside of gnucash). That lead me naturally to use the code field as the foreign key. While it will complicate things a little bit, I may probably use the guid space as key which is inherently larger than the code space. Regards Rolf ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On November 14, 2008 08:30:11 am Derek Atkins wrote: Phil, I must have missed this, because I don't remember reading the original request. I don't understand what additional table is required. I'm trying to think about it from the GnuCash use standpoint, not the backend storage aspect. Where in GnuCash is this extra table supposed to be used? And how? Rolf has just replied to this. Wait, the ENTIRE contents are read in? Historically only necessary data was read in. That would be the Accounts and Commodities from the main CoA. The transactions were all loaded on demand. Yes. I wanted to only read necessary data. However, my (admittedly incomplete) knowledge of the engine led me to the conclusion that parts of th engine assume that all data is present. I couldn't get the account tree to show correct values, for example, unless all splits for an account were present. I ended up just loading the whole database into memory. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
--- On Thu, 11/13/08, Derek Atkins [EMAIL PROTECTED] wrote: From: Derek Atkins [EMAIL PROTECTED] Subject: Re: String lengths in the SQL backend To: [EMAIL PROTECTED] Cc: Gnucash list [EMAIL PROTECTED], [EMAIL PROTECTED], Phil Longstaff [EMAIL PROTECTED] Date: Thursday, November 13, 2008, 8:11 AM Paul Schwartz [EMAIL PROTECTED] writes: I've worked with several accounting packages, and I would find it very strange if someone could not adapt their procedures to live with 32 for the length of the account code. Except that the UI makes no limitation on the field size... So people who convert over could lose data. Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek True. Introduction of this feature should come with sufficient warnings so that the user could adjust his account tree appropriately for this one time event. Is anything ever perfect? Paul ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Derek Atkins wrote: Uhh, yeah. Sorry. Totally unreasonable. The code in question is a user-input field. Historically accountants used codes instead of names to keep track of accounts. So GnuCash provides a place for you to enter in an Account Code. But it's a string, not a number. Granted, most users probably do only use numbers, but there is no requirement that it be a number. It is for this exact reason that in standard database schema design, codes should not be used as keys. The end user (or an administrator, or an auditor) should have the power to choose the code as they see fit and change the code if they see fit. If you have used the code as a key, changing that code becomes difficult from a sql perspective. The XML file already uses a separation between codes and keys: the user might enter a code, but internally inside the XML file GUIDs are used as keys. Because of this, the code can be changed easily and safely, and the end user doesn't have to know or care what a GUID is or even that a GUID exists. It is perfectly reasonable (and recommended) for the primary key to be something private to the application (an integer, a GUID, whatever), and the code to be just-another-field in the table. Regards, Graham -- smime.p7s Description: S/MIME Cryptographic Signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Phil Longstaff [EMAIL PROTECTED] schrieb am Wed, 12 Nov 2008 10:24:03 -0500: Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? I can't, but I'm sure sooner or later a user will reach that limit. 128 should be much saver. Would that be a problem? Manfred -- Manfred Usselmann[EMAIL PROTECTED] ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
I agree that this would be the best course of action. Use a separate key for the codes. Internally, link the tables using the keys, and only where user interaction is expected, present the code. My 2c. Geert On Thursday 13 November 2008, Graham Leggett wrote: Derek Atkins wrote: Uhh, yeah. Sorry. Totally unreasonable. The code in question is a user-input field. Historically accountants used codes instead of names to keep track of accounts. So GnuCash provides a place for you to enter in an Account Code. But it's a string, not a number. Granted, most users probably do only use numbers, but there is no requirement that it be a number. It is for this exact reason that in standard database schema design, codes should not be used as keys. The end user (or an administrator, or an auditor) should have the power to choose the code as they see fit and change the code if they see fit. If you have used the code as a key, changing that code becomes difficult from a sql perspective. The XML file already uses a separation between codes and keys: the user might enter a code, but internally inside the XML file GUIDs are used as keys. Because of this, the code can be changed easily and safely, and the end user doesn't have to know or care what a GUID is or even that a GUID exists. It is perfectly reasonable (and recommended) for the primary key to be something private to the application (an integer, a GUID, whatever), and the code to be just-another-field in the table. Regards, Graham -- ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Herbert Thoma [EMAIL PROTECTED] writes: Ah well, I have a 30, 2560 pixel wide screen, 256 chars on a single line is possible, if the font is not too large ... Rolf wants german SKR4 account codes and these are 6 or 8 digit numerical codes, but that fact does not help since other users may have (miss-) used the account code field with longer codes. It is not a mis-use of the field to have longer codes. I agree, 32 is too long. What exactly is the use-case for using codes as a primary key? Keep in mind that the SQL Backend data store is just that, a data store. GnuCash does NOT use database constraints, and for Accounts it doesn't even need to use an Index, because GnuCash does (should!) load in all the accounts at start time. So... All searches on accounts are done in core in the gnucash app without looking at the DB backend. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Paul Schwartz [EMAIL PROTECTED] writes: I've worked with several accounting packages, and I would find it very strange if someone could not adapt their procedures to live with 32 for the length of the account code. Except that the UI makes no limitation on the field size... So people who convert over could lose data. Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Roland Roberts [EMAIL PROTECTED] writes: I confess I haven't been paying attention to the SQL backend in a while. I'm one who would very much like to have this working since my wife and I often run into conflicts having to serialize our data entry (neither of us has much time to do data entry until the kids are asleep and then we both rush to get everything done) The first release of the new SQL backend wont solve this issue It will still be single user. However it will hopefully open the door for future multi-user use. Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On November 13, 2008 10:12:32 am Derek Atkins wrote: Roland Roberts [EMAIL PROTECTED] writes: I confess I haven't been paying attention to the SQL backend in a while. I'm one who would very much like to have this working since my wife and I often run into conflicts having to serialize our data entry (neither of us has much time to do data entry until the kids are asleep and then we both rush to get everything done) The first release of the new SQL backend wont solve this issue It will still be single user. However it will hopefully open the door for future multi-user use. Rolf, can you key off the account guid? That's only 32 chars, fixed length. The account code could stay as is. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Derek Atkins schrieb: Herbert Thoma [EMAIL PROTECTED] writes: Ah well, I have a 30, 2560 pixel wide screen, 256 chars on a single line is possible, if the font is not too large ... Rolf wants german SKR4 account codes and these are 6 or 8 digit numerical codes, but that fact does not help since other users may have (miss-) used the account code field with longer codes. It is not a mis-use of the field to have longer codes. I agree, 32 is too long. What exactly is the use-case for using codes as a primary key? I don't know and http://bugzilla.gnome.org/show_bug.cgi?id=560165 doesn't tell either. In my opinion the mapping of account to code is a one-to-one relationship and I see no reason for account code being used as a primary key, one could (and should) use the account key. Keep in mind that the SQL Backend data store is just that, a data store. GnuCash does NOT use database constraints, and for Accounts it doesn't even need to use an Index, because GnuCash does (should!) load in all the accounts at start time. So... All searches on accounts are done in core in the gnucash app without looking at the DB backend. I agree. Herbert. -derek -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: [EMAIL PROTECTED] www: http://www.iis.fhg.de/ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On November 13, 2008 10:10:53 am Derek Atkins wrote: What exactly is the use-case for using codes as a primary key? Keep in mind that the SQL Backend data store is just that, a data store. GnuCash does NOT use database constraints, and for Accounts it doesn't even need to use an Index, because GnuCash does (should!) load in all the accounts at start time. So... All searches on accounts are done in core in the gnucash app without looking at the DB backend. The use case is that one person wants to have another table which stores information based on the account code. I gather there are standard German account codes and he wants to key off of them. Possible alternatives are to key off of the account guid, or have a code key but don't require the code key in his table to be 2048 long. BTW, Derek is right that the SQL backend is simply a data store. When you open an SQL db, the entire contents is read in the same way the current XML file is read. Any time you add/delete/edit anything, the change is written to the db immediately, so it should help prevent data loss. Gnucash is not written as a database app. It is written assuming that all data is immediately available. Having an SQL db will open up possibilities, but the app will need a lot of work to make use of those possibilities. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
. I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? Phil ROFLOL --- as soon as you set a limit that a user could bump into, somebody will. Best to look at this from a human engineering perspective. There's no obvious reason why somebody someday couldn't possibly want to use account codes larger than 32. But there are reasons to decide that nobody would ever use ones as large 256 -- because no screen wide enough to enter something like that on a single line. Michael ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On Wed, Nov 12, 2008 at 10:24 AM, Phil Longstaff [EMAIL PROTECTED] wrote: For those on the gnucash user list, a new SQL backend is in development. An issue has arisen, and I need to get input from users, not just developers. Rolf Leggewie has run into some problems with the SQL backend (http://bugzilla.gnome.org/show_bug.cgi?id=560165) - he wants to add another SQL table to key off the account 'code' field. However, the 'code' field is varchar(2048) which is too big to be a primary key in mysql. I responded that earlier discussion on this mailing list (mainly/only by Derek) had been that strings should be unlimited, and since SQL requires *some* limit, I chose 2048. For those who don't know SQL, for a variable length string (varchar), the database engine will only allocate enough space to hold the value, so allowing 2048 and storing only 10 chars uses about the same disk space as allowing 128 and storing the same 10. The problem in this case is that one user wants to be able to index some more information by the account code, but the fields used as the index is too large. I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? What's the maximum length primary key in mysql? Is that where the 32 came from? Why is mysql an issue? I thought the backend was going to be sqlite3? Perhaps you are doing a generic sql layer, designed to talk any one of many target databases, or the specific target database has changed? /Don Phil ___ gnucash-user mailing list [EMAIL PROTECTED] https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
I've worked with several accounting packages, and I would find it very strange if someone could not adapt their procedures to live with 32 for the length of the account code. Paul --- On Wed, 11/12/08, Phil Longstaff [EMAIL PROTECTED] wrote: From: Phil Longstaff [EMAIL PROTECTED] Subject: String lengths in the SQL backend To: Gnucash list [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Wednesday, November 12, 2008, 8:24 AM For those on the gnucash user list, a new SQL backend is in development. An issue has arisen, and I need to get input from users, not just developers. Rolf Leggewie has run into some problems with the SQL backend (http://bugzilla.gnome.org/show_bug.cgi?id=560165) - he wants to add another SQL table to key off the account 'code' field. However, the 'code' field is varchar(2048) which is too big to be a primary key in mysql. I responded that earlier discussion on this mailing list (mainly/only by Derek) had been that strings should be unlimited, and since SQL requires *some* limit, I chose 2048. For those who don't know SQL, for a variable length string (varchar), the database engine will only allocate enough space to hold the value, so allowing 2048 and storing only 10 chars uses about the same disk space as allowing 128 and storing the same 10. The problem in this case is that one user wants to be able to index some more information by the account code, but the fields used as the index is too large. I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? Phil ___ gnucash-user mailing list [EMAIL PROTECTED] https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Mike or Penny Novack schrieb: . I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? Phil ROFLOL --- as soon as you set a limit that a user could bump into, somebody will. Best to look at this from a human engineering perspective. There's no obvious reason why somebody someday couldn't possibly want to use account codes larger than 32. But there are reasons to decide that nobody would ever use ones as large 256 -- because no screen wide enough to enter something like that on a single line. Ah well, I have a 30, 2560 pixel wide screen, 256 chars on a single line is possible, if the font is not too large ... Rolf wants german SKR4 account codes and these are 6 or 8 digit numerical codes, but that fact does not help since other users may have (miss-) used the account code field with longer codes. Herbert. Michael ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel -- Herbert Thoma Dipl.-Ing., MBA Head of Video Group Multimedia Realtime Systems Department Fraunhofer IIS Am Wolfsmantel 33, 91058 Erlangen, Germany Phone: +49-9131-776-6130 Fax: +49-9131-776-6099 email: [EMAIL PROTECTED] www: http://www.iis.fhg.de/ ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On November 12, 2008 11:22:34 am Donald Allen wrote: Why is mysql an issue? I thought the backend was going to be sqlite3? Perhaps you are doing a generic sql layer, designed to talk any one of many target databases, or the specific target database has changed? It uses a library which supports multiple databases. Sqlite3 is the default, but mysql and postgresql will also be supported. With not too much extra work, other databases could also be supported, but they are not on the list for the first release. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On November 12, 2008 12:11:13 pm Donald Allen wrote: On Wed, Nov 12, 2008 at 11:48 AM, Phil Longstaff [EMAIL PROTECTED] wrote: On November 12, 2008 11:22:34 am Donald Allen wrote: Why is mysql an issue? I thought the backend was going to be sqlite3? Perhaps you are doing a generic sql layer, designed to talk any one of many target databases, or the specific target database has changed? It uses a library which supports multiple databases. Sqlite3 is the default, but mysql and postgresql will also be supported. With not too much extra work, other databases could also be supported, but they are not on the list for the first release. Ok, makes sense. Where did the 32 come from? Is that the actual maximum length of a varchar primary key in mysql? Or was that a number you just made up as an example? I ask because I actually do use the account code field to relate dividends to commodities for use by an external reporting package I wrote and a few of them are greater than 32 characters. If that field were limited to 32 characters, I'd work around it -- not a big deal -- but I'd like to be sure that the choice of 32 is not arbitrary. Said another way, if it is arbitrary, I'd vote for making it min(max(primary_key_length[i])) where i ranges over the supported databases. The value '32' is just made-up. I picked a not-too-large, not-too-small power of 2. What you suggest (min(max(primary_key_length[i])) makes sense. I've seen an old post (circa 2004) which has 500 bytes as max key length for mysql. If this field is typed, it'll need to be utf8 which needs 3 bytes/char, so we're looking at 166 chars for mysql. The max key length might be longer now, but I might round down to 150 chars. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Phil Longstaff wrote: I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? No matter what you do, any limit that you set will always eventually be too small for somebody. Ideally gnucash should not impose any limit at all - it should be up to the entity that creates the database to decide how wide the columns should be. Doing it this way means that database column sizes become the user's problem. If the user needs a wider column, the user can make the column wider. If the user chooses a database that supports varchars of large size efficiently (like postgres), then gnucash should step out the way and not impose a limit at all. In terms of the UI, if gnucash could query the database and say how wide is this column? and then use that to limit the widths, again it means that widths are the user's problem. Regards, Graham -- smime.p7s Description: S/MIME Cryptographic Signature ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On Wed, Nov 12, 2008 at 11:48 AM, Phil Longstaff [EMAIL PROTECTED] wrote: On November 12, 2008 11:22:34 am Donald Allen wrote: Why is mysql an issue? I thought the backend was going to be sqlite3? Perhaps you are doing a generic sql layer, designed to talk any one of many target databases, or the specific target database has changed? It uses a library which supports multiple databases. Sqlite3 is the default, but mysql and postgresql will also be supported. With not too much extra work, other databases could also be supported, but they are not on the list for the first release. Ok, makes sense. Where did the 32 come from? Is that the actual maximum length of a varchar primary key in mysql? Or was that a number you just made up as an example? I ask because I actually do use the account code field to relate dividends to commodities for use by an external reporting package I wrote and a few of them are greater than 32 characters. If that field were limited to 32 characters, I'd work around it -- not a big deal -- but I'd like to be sure that the choice of 32 is not arbitrary. Said another way, if it is arbitrary, I'd vote for making it min(max(primary_key_length[i])) where i ranges over the supported databases. /Don Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Phil Longstaff wrote: For those on the gnucash user list, a new SQL backend is in development. An issue has arisen, and I need to get input from users, not just developers. Rolf Leggewie has run into some problems with the SQL backend (http://bugzilla.gnome.org/show_bug.cgi?id=560165) - he wants to add another SQL table to key off the account 'code' field. However, the 'code' field is varchar(2048) which is too big to be a primary key in mysql. I responded that earlier discussion on this mailing list (mainly/only by Derek) had been that strings should be unlimited, and since SQL requires *some* limit, I chose 2048. For those who don't know SQL, for a variable length string (varchar), the database engine will only allocate enough space to hold the value, so allowing 2048 and storing only 10 chars uses about the same disk space as allowing 128 and storing the same 10. The problem in this case is that one user wants to be able to index some more information by the account code, but the fields used as the index is too large. I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? I confess I haven't been paying attention to the SQL backend in a while. I'm one who would very much like to have this working since my wife and I often run into conflicts having to serialize our data entry (neither of us has much time to do data entry until the kids are asleep and then we both rush to get everything done) Anyway, I have to agree with Mike Novack, at some point I might well bump into the 32 character limit although its not likely. And then I would end up annoyed if I can't bump it up somehow. I have often bumped into the 32-character limit on Oracle entity names (which is really a 30-character limit...). While Paul Schwartz is correct that I probably could work around the limit, if a larger number is still doable I would just pick something larger. 32 characters is just a bit short for my comfort zone, but once you get above 50-60, I can't imagine typing that much. regards, roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED]6818 Madeline Court [EMAIL PROTECTED] Brooklyn, NY 11220 ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On Wed, 2008-11-12 at 10:24 -0500, Phil Longstaff wrote: For those on the gnucash user list, a new SQL backend is in development. An issue has arisen, and I need to get input from users, not just developers. Rolf Leggewie has run into some problems with the SQL backend (http://bugzilla.gnome.org/show_bug.cgi?id=560165) - he wants to add another SQL table to key off the account 'code' field. However, the 'code' field is varchar(2048) which is too big to be a primary key in mysql. I responded that earlier discussion on this mailing list (mainly/only by Derek) had been that strings should be unlimited, and since SQL requires *some* limit, I chose 2048. For those who don't know SQL, for a variable length string (varchar), the database engine will only allocate enough space to hold the value, so allowing 2048 and storing only 10 chars uses about the same disk space as allowing 128 and storing the same 10. The problem in this case is that one user wants to be able to index some more information by the account code, but the fields used as the index is too large. I'd have to look back, but I think Derek's reply was the only one. I'd like to open the topic again, because of Rolf's problem. Can anyone think of a reason that account code size limit cannot be reduced to a smaller value (e.g. 32)? Will anyone ever enter an account code longer than that? Phil I'm not familiar with gnucash's data structures so I'm not 100% sure what a code is, but would it be possible to identify each account by an int or a bigint instead of a code? Then this secondary table could have the account int as its foreign primary key, and you could have another table that maps account int to code with the code as a varchar(2048) as desired. Or put the account int and the code in the same table and make the int the primary key instead. Totally unreasonable? Cheers, Eric signature.asc Description: This is a digitally signed message part ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Hi, Quoting Eric Anopolsky [EMAIL PROTECTED]: I'm not familiar with gnucash's data structures so I'm not 100% sure what a code is, but would it be possible to identify each account by an int or a bigint instead of a code? Then this secondary table could have the account int as its foreign primary key, and you could have another table that maps account int to code with the code as a varchar(2048) as desired. Or put the account int and the code in the same table and make the int the primary key instead. Totally unreasonable? Uhh, yeah. Sorry. Totally unreasonable. The code in question is a user-input field. Historically accountants used codes instead of names to keep track of accounts. So GnuCash provides a place for you to enter in an Account Code. But it's a string, not a number. Granted, most users probably do only use numbers, but there is no requirement that it be a number. Cheers, Eric -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH [EMAIL PROTECTED]PGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
On Wed, Nov 12, 2008 at 6:11 PM, Derek Atkins [EMAIL PROTECTED] wrote: Hi, Quoting Eric Anopolsky [EMAIL PROTECTED]: I'm not familiar with gnucash's data structures so I'm not 100% sure what a code is, but would it be possible to identify each account by an int or a bigint instead of a code? Then this secondary table could have the account int as its foreign primary key, and you could have another table that maps account int to code with the code as a varchar(2048) as desired. Or put the account int and the code in the same table and make the int the primary key instead. Totally unreasonable? Uhh, yeah. Sorry. Totally unreasonable. The code in question is a user-input field. Historically accountants used codes instead of names to keep track of accounts. So GnuCash provides a place for you to enter in an Account Code. But it's a string, not a number. Granted, most users probably do only use numbers, but there is no requirement that it be a number. Cheers, Eric -derek As a GnuCash-naive, but MySQL-adept user, I would have recommended the same thing as Eric. The point being: never use user-input fields for keys. Sure, code needs to be a string because users want to be able to enter anything into it. So don't use it as a key. I doubt that's *totally* unreasonable, though I suspect it's practically unreasonable if you're already using code (and similar fields) as a key all over the place. Moreover, I, for one, see no reason code could not be truncated to 32 characters, except that it might point to a problematic usage of fields for both user input and application logic. But, hey, I'm not even sure how I would fill out the code field, so don't listen to me ;) Is there a functional reason for fields to be used for both user input and keying? Should a user be able to create two accounts, enter code on both, and have them magically relate? I haven't seen any functionality like this in my GnuCash journeys, so I doubt it, but it's the only reason I can fathom to use code both as user input and as a key. -ISH ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: String lengths in the SQL backend
Well, my 2ct here: I am using the account-code to store item-numbers/item-barcodes (assets) or account-numbers (bank-accounts). Item-numbers can get quite long (think the serial-number identifying a laptop) but 32 characters should be okay, as long as it's not 32 byte (think Unicode). How far has the sql-schema matured and what databases are supported atm? After a few years the SQL-file does get a bit slow to load. Marcus ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
