> Use (Storage) > [MyNextNumber]NextNumber:=Storage.myNextNumber > End use
This is not necessary. For just reading you don’t need to use „use“. For generating sequence numbers, you have to use „auto increment“. Nothing else. For invoice numbers, you now can save the number to the database within transactions with SUSPEND/RESUME TRANSACTION (of course, locking and waiting if necessary…) -- Grüße/Regards, [heubach-media] | Christian Sakowski christian.sakow...@heubach-media.de iChat/AIM: SakowskiF Tel: +49/(0)40/52 10 59-23 > Am 25.08.2018 um 21:10 schrieb Tim Nevels via 4D_Tech <4d_tech@lists.4d.com>: > > On Aug 25, 2018, at 10:33 AM, Pat Bensky wrote: > >> Thanks John. >> But how would that work in a multi-user setting? Whatever method is used >> for assigning the new numbers, it must run on the server, otherwise we're >> back to the problem of using semaphores, checking record locking, etc. >> Also I should point out that there could be a large number of tables for >> which the record numbering system needs to be used. We have a RecordNumbers >> table which has one record for each table that requires this function. so >> we must have ONE process that accesses and updates that table. > > I like John’s idea of using the new shared object “Storage”. This has a > built-in, super efficient, locking mechanism using the “Use/End Use” system. > My first thought were that this could be the ultimate “next sequence number” > system if you have already used the “Sequence number” attribute for a table > (which is already the ultimate “next sequence number” system for a table in > terms of speed and reliability in a multi-user and transactional system. > > So here is an idea for a basic sequence number system based on shared > “Storage” object. The goals are: > > - Make it as fast as possible > - Make it work on single-user and client/server with the least amount of code > - Use built-in 4D features wherever you can > - Works anywhere and everywhere, no special cases to deal with > - any process cooperative or preemptive > - in triggers > - inside or outside transactions > - from a form or from a process with no UI > - Don’t care about “lost numbers” (holes in the sequence numbers are OK) > > Sorry Pat, you’ll have to complicate the system to make it work for your > specific needs, but that should be doable with a more complex storage object. > I just wanted to present the concept I have here in the simplest terms for > easy digestion. > > 1. Initialization - this needs to be done once for a database application. If > you are running single-user, do this in the “On Startup” method. If you are > running client/server do this in “On Server Startup” > > Use (Storage) > Storage.myNextNumber:=1 > End use > > 2. GetNextNumber - this is a method that will return the next number from the > shared storage. Set the method property for “Execute on server”. This will > make it work in single-user or client/server with no code changes. > > // Method: GetNextNumber > > C_LONGINT($0) > > Use (Storage) > $0:=Storage.myNextNumber > Storage.myNextNumber:=$0+1 // increment sequence > End use > > 3. Permanent storage of the next number - If you want this to maintain it’s > value across launches of the application, use a table in the database. So in > the “initialization” part above instead of setting "Storage.myNextNumber:=1” > you would query a table and get the next number value from a field in a > record. When the database quits you update that value so it is available for > the next “On Startup” or “On Server Startup” You would put this code in the > “On Exit” or “On Server Shutdown”. > > 4. What if my database crashes and my code does not get a chance to do a > proper save of the next number to the database? A valid consideration and one > I think should be addressed. We all know that 4D will crash sometimes, so > best to be prepared and handle it as best we can. > > How about in the “initialization” part you also start a new process whose job > is to save the next number from the storage object to a database record. Have > this process run as often as you are comfortable with. If you are journaling > your data file — and you should be — the record update will be in the journal > file so that if 4D crashes, on the next startup it will integrate the change > into the database and you’ve not lost anything. > > You could bypass the whole shared storage approach and just do database > operations on records, but I would speculate that using shared storage is > just a tad bit faster than doing a query, wait for record to be unlocked, > increment, save record, unload record operation. Might not be, I’ve not done > any testing to see which is faster. Just seems like less overhead to not call > the database engine every time you need a next number. But it does introduce > a "cache within a cache” situation. And the potential risk of losing that > last sequence number before your process “flushes” it to the database cache > and writes to the journal file. But one of my above stated goals was maximum > speed, so I think this will buy you a tiny bit of extra speed. > > Here is some example code for saving the next number storage value to the > database. Put this in a method that is called in either “On Startup” or “On > Server Startup”. To make is super simple, [MyNextNumber] table only contains > 1 record and 1 field. And no other process is ever allowed to do any write > operations to table [MyNextNumber]. The only process to modify it is > “SaveMyNextNumber” then you don’t have to worry about possible record locking > issues. > > $processID_l:=New Process(“SaveMyNextNumber”;0) > > // Method: SaveMyNextNumber > > ALL RECORDS([MyNextNumber]) > if (Records in selection([MyNextNumber])=0) > CREATE RECORD([MyNextNumber]) > End if > > Use (Storage) > [MyNextNumber]NextNumber:=Storage.myNextNumber > End use > > SAVE RECORD([MyNextNumber]) > UNLOAD RECORD([MyNextNumber]) > > Great topic for discussion Pat, glad you brought it up now that we have v17 > and all the new features. > > And great idea from John about using Storage that got me thinking about this > age old next sequence number issue and how to best implement it. > > I wonder what other developers think about this crazy idea? > > Tim > > ***************************************** > Tim Nevels > Innovative Solutions > 785-749-3444 > timnev...@mac.com > ***************************************** > > ********************************************************************** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ********************************************************************** -- heubach media Osterfeldstr. 12-14 | Haus 1 | Eingang Nord 22529 Hamburg tel: 040 / 52 10 59 - 10 | fax: -99 mail: i...@heubach-media.de home: www.heubach-media.de Geschäftsführer|CEO: Matthias Heubach Mieten Sie Ihre Computer, iPads & Drucker für Ihre Events bei: http://www.milo-rental.com Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ********************************************************************** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
