prototype for wiki tutorial lazarus / 16-Databases ---------------------------------------------------------------------- codifies · 2012-09-15 06:17 please find attached a prototype for a tutorial entitled Using a second form to edit a grid of data it includes the following features syncing data cursors between formsshowing / hiding forms rather than creating and destroyingusing a data modulecustom rendering in a data gridI'd be grateful for feedback and also could you let me know on which indexes (where) should the tutorial be linked to once I turn it into a wiki page. ---------------------------------------------------------------------- BigChimp · 2012-09-15 07:09 Hi Chris, Looks good! Nice and easy style, nice screenshots! Going to show some of my ignorance below, hope you don't mind I would just include the "SQL dump" you're talking about in the tutorial itself rather than on another site or something, to make it self-contained. "Notice also that as there is only a single transaction and connection component in the project I've just put them off to one side and lazily not even bothered to rename them." I'd rather rename them and be done with it rather than encourage people to stick with default names... Perhaps a naive question: "replace all the line breaks (ASCII 10 (0x0A)) with an ellipsis." Is this cross platform - i.e. will this work on Linux+Windows? Custom drawn memo: perhaps some more explanation of what custom drawn is and that it is used to modify normal drawing would be nice. A screenshot of the plain vanilla memo with your data may help here? Assuming peopleid is an integer field: if (DataModule1.f2peopleQuery.FieldByName('peopleid').AsString='') Could you use .IsNull here rather? Also, you're using locate with DataModule1.f2peopleQuery.FieldByName('peopleid').AsString. No idea how this works with locate expecting variants etc, but would it be better to use .AsInteger? I would link to it from the Database Overview page, and any other relevant page that has a link to DB Tutorial1. I'd also link from the grids page (dbgrids are mentioned there). FYI: I intend to split up DB Tutorial1 into 2: a read-only part and an editing part, in order to reduce its length (and apparent complexity). I'd like to add at least database export (using xmlxsdexport etc) and Lazreport tutorials, using the same datasets. I was thinking about it being a good idea to have the same SQL data for all db tutorials, but the Firebird EMPLOYEE database SQLDB Tutorial1 is based on probably won't lend itself to your tut. Perhaps we can figure something out.... or we can leave it be for the moment - much more important that there are tutorials rather than that they all have the same data. ---------------------------------------------------------------------- codifies · 2012-09-15 07:58 A common data source would be a good idea, at least one table should include memo's image or at least thumb nail images of people would be nice for more advanced tutorials relationships like one to many and also a junction table so we can have an example of records that are linked to multiple records another table... I didn't include the dump because the main table has 100 records - the minimum you really want for messing around with a table FYI http://www.generatedata.com/#generator is WELL worth a look! I fortunately don't own an OS that uses none standard tables and it's only a guess that memo fields might contain spurious end of line characters for all I know it might depend on what the client posts or even might be a stipulation of the server software that it use unix line ending regardless of what OS the server is running on - I really don't know.  Good catch with the .asString on the ID's I'll look again at that code, I believe I copied it from some code of mine that was doing something completely different !! I look forward to hearing your views on DB structures for the wiki examples/tutorials and I'm more than happy to work with you so together we can make one simple cross server SQL statement that can be dumped into a number of servers, or at least something we can translate into a dump for each server that Lazarus can handle.... ---------------------------------------------------------------------- codifies · 2012-09-15 08:08 how about this for a scenario for the data set a marketing company has say 100 employee's each employee works on multiple projects each project has at least one and sometimes more project leaders employees has a thumbnail photo (need not be real pics could even just be images of playing cards - thinking copy right issues here) each project has a description field which is a memo, and a data started and completed date time stamp each project has multiple notes attached these are date / time stamped memo fields with an author field Might this type of structure provide almost all of what we need for all the tutorials ? ---------------------------------------------------------------------- BigChimp · 2012-09-15 08:31 Structure looks quite good at first glance. Alternatively we could create something that would be useful for our users (as not everybody presumably has a marketing company), but I think the age of the self-designed adddress database is over... and not everybody is interested in say Fish Facts or marketing companies. Really thinking out loud here: perhaps we could do something with Lazarus projects: e.g. record where they are on disk, how many lines of code they have, store hashes of the files to see if there are exactly the same file. We could add images for each project (bit tortuous, but well, if we need a picture I really don't mind either way. That generatedata site is very nice Regarding your not including a dump... well to each his own. As you noticed, the sqldbtutorial1 and 2 were very beginner-friendly, hands on but at some point you'll have to move past explaining everything. Presumably loading in some data into an RDBMS shouldn't be too difficult Having a Lazarus loader application that creates the tables if they don't exist and load the data may even be the simplest option QuoteI fortunately don't own an OS that uses none standard tablesThe nice thing about standards is that there are so many to choose from We'll let the users sort it out then ---------------------------------------------------------------------- codifies · 2012-09-15 09:28 I picked the marketing company example out of thin air, just seemed better to peg it onto some kind of vaguely real world example I only didn't include the sql as text because it was going to be too long, I was going to upload it (wiki articles can have attachments right?) an sql dump should handle all the drop tables if exists etc kind of stuff for example mysqldump creates a single textual sql file that will completely generate a complete database from scratch dropping any existing tables etc as needed Most other databases should have similar tools to do this so some custom app to ram the data in via lazarus is probably over kill... I'd like to include thumb nail images as they are great for thinks like club membership db's etc I'm also keep to have an excuse for using at least one junction table as it takes simple relational structures just that little step further than most tutorials do, and its a *very* useful technique. Do you have any scenarios in mind how about a squash club ? - not sure of a none convoluted use for a junction table there mind! Whatever the data it would be nice if the structure were sufficiently complex where all the tutorials could use it but also usable for basic tutorials, ie a basic tutorial might only use the title name and surname of the members table, where as an advanced tutorial might use a junction table with the members and court bookings table to let members book courts... ---------------------------------------------------------------------- BigChimp · 2012-09-15 09:59 Marketing company, squash club, airline reservations... it's all the same to me Plus side: those are real world examples which is useful for people to visualize. Not meant as disparaging, but it's meh because it isn't inherently linked to something "Lazarusy/FPCy", if you understand what I mean. Something like a project management/registration & billing, however bureaucratic that may be, may be something that links to the way some, hopefully many people would use Lazarus. It could link to Laz projects on disk, lines of codes etc, with some history tables. If we add people/employees: Multiple people could work on multiple projects=> presumably gives a nice junction table. Employee pictures are once again a possibility. Memo/multiline/long text fields could be part of e.g. a project description, comments on billable hour registration etc Re the dump file: the wiki allows at least image attachments. Not sure about other types. Agreed about custom app being probably overkill, HOWEVER : it could serve as an example application in Lazarus itself (if we cater to the various databases out there). Until it gets distributed with all Laz versions, it may find a home in the Lazarus CCR SVN repo or another repo (I've got a bitbucket one) that allows downloadsThis application could be full on with bells and whistles (error handling, etc). Much to complex indeed for a simple tutorial, but: it would relieve newbies from having to delve into their specific RDBMS tools... and more importantly for me, it would relieve me from having to spell out instructions a la SQLDB tutorial1 which only distract from the tutorial per se.Linked to the previous point: you're going to have to provide multiple dump files anyway, because autoincrement/autonumber type fields are implemented differently in every db (although I think it should be in the SQL2012? draft? standard - but of course complying that is another story, especially if it's draft) and we'll need this... Still, I'm not dead set on any of this, it's nice having an open discussion with somebody who's knowledgeable ---------------------------------------------------------------------- codifies · 2012-09-15 10:30 so who's the knowledgeable person you found to talk to then I suggested using dump files as there is usually at least a simple gui tool to execute a query... you can even do it on the command line with some Code: [Select]cat dumpfile.sql | sqlite3 my_database.sqlite actually tho the tool could easily be turned into a generic data pump too, which could be useful for us to make dumps for other servers - and if made generic enough would be a tool worth having as part of the standard Lazarus "kit" ie you could make tables in mySql then data pump into Postgrsql then execute postgres sql export.... there may even be a remote way to request a dump or even have our own module per server type to build an sql dump file. A kind of generic DBMS swiss army knife if you want to make a start I'll be only too happy to pitch in. Well my votes for some kind of club rather than zzzz business scenario I'm *really* not so bothered about squash but a squash club would be a good excuse to have member pic's (for their membership cards) and booking / reservation of courts would be a great tutorial showing a number of different DB techniques... Like you say you do need something to visualise to aid the learning process and preferable something a little more stimulating than some dry aspect of business. Trying to tie the example to something inherently Lazarusy seems a little tenuous to me...? I'm just hoping that some party is going to jump in here with some great tutorial scenario that gonna blow us all away with exclamations of wow amazing why didn't I think of that.... ---------------------------------------------------------------------- BigChimp · 2012-09-15 11:00 Quote from: chris_c on September 15, 2012, 12:30:59 pmso who's the knowledgeable person you found to talk to then Quote from: chris_c on September 15, 2012, 12:30:59 pmactually tho the tool could easily be turned into a generic data pump too, which could be useful for us to make dumps for other servers - and if made generic enough would be a tool worth having as part of the standard Lazarus "kit" ie you could make tables in mySql then data pump into Postgrsql then execute postgres sql export.... there may even be a remote way to request a dump or even have our own module per server type to build an sql dump file. A kind of generic DBMS swiss army knife Yep. Have a look at LazdataDesktop which does at least part of the exporting stuff... Quote from: chris_c on September 15, 2012, 12:30:59 pmif you want to make a start I'll be only too happy to pitch in. Mm yes, not sure when though Quote from: chris_c on September 15, 2012, 12:30:59 pmWell my votes for some kind of club rather than zzzz business scenario I'm *really* not so bothered about squash but a squash club would be a good excuse to have member pic's (for their membership cards) and booking / reservation of courts would be a great tutorial showing a number of different DB techniques... Like you say you do need something to visualise to aid the learning process and preferable something a little more stimulating than some dry aspect of business. Trying to tie the example to something inherently Lazarusy seems a little tenuous to me...? I'm just hoping that some party is going to jump in here with some great tutorial scenario that gonna blow us all away with exclamations of wow amazing why didn't I think of that.... Agreed, can see your point re anything non-businessy. Let's wait for that bright forum post that'll surprise us