Re: [Dorset] Database with GUI Front End for Non-Database Experts
On Thursday, 15 November 2018 10:25:17 GMT Ralph Corderoy wrote: > > When you talk about rows, do you mean rows in the spreadsheet > > You can assume that, and the question still stands. :-) I hadn't envisage multiple rows in the spreadsheet since we are currently simply typing in the value free-form. Obviously when the system is developed we would probably end up with the equivalent of multiple rows. > Here are three ways of thinking about today's typical `relational > database', each with their own vocabulary. There's quite a bit to digest there and I won't try to do it until I have more time. Many thanks for taking the time to explain it. > The `card' approach to a database, with tables, records, fields, and > forms, has each record being a spreadsheet or relational-database row, > or algebraic tuple, and a field being a column or attribute. Again, it > discards some of the purity of the algebra. From what I've seen it might be all we need though. > Mapping it back onto your stock control, a Location relation might have > `description' and `lon/lat' attribute names, and your Stock relation > would have a `location' attribute name with a value that matched a > Location.description. A UI could present a drop-down of those > Location.description's. Exactly. Though rather than Lat and Long, it's more likely to say 'Terry's Workshop'. :-) > > > Have you considered Google Sheets instead of your LibreOffice > > > spreadsheet? > > It has a Query function that allows SQL-like expressions. > https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/ Worth a look. Thanks. > Django's advantage is more readily defining the web form for data entry > from the description of the database table that you've already told > Django about. But overall it's a bigger beast and might be harder to > grasp what's going on. Whatever, we do, our limited application and the workload that we have would preclude any major development. At the end of the say if the benefit gained is significantly less than the effort expended, then it probably isn't worth it (except for fun maybe). As it happens, Tristan Hill responded off list with the following: Perhaps https://en.wikipedia.org/wiki/Odoo or https://en.wikipedia.org/wiki/Tryton. These look as if they might be a jolly good starting point, but again, I haven't had time to follow this up yet. > Not entirely, it seems. Windows and Mac only. > https://en.wikipedia.org/wiki/The_Fourth_Dimension_(company) Your ability to make Google work for you never ceases to amaze me :-) I searched for 4th Dimension with all sorts of additional terms and never found it. Having said that, you've linked to the wrong Wikipedia page: https://en.wikipedia.org/wiki/4th_Dimension_(software) -- Terry Coles -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
Hi Terry, > > So if you've five widgets, two of them Condition=rusty, is that two > > rows? What columns come together to make a unique row rather than > > one that should be merged into another? I'd group those together > > rather than slot Quantity in between, say. > > I had to think about this to understand what you meant by your > question. At the moment Condition could have several entries; New or > Used is the most common, but Needs Assembly also appears (for example, > Pis can come bare or with pre-assembled headers). > > When you talk about rows, do you mean rows in the spreadsheet You can assume that, and the question still stands. :-) > or rows in the database (is there such a thing)? Here are three ways of thinking about today's typical `relational database', each with their own vocabulary. `Relational calculus' is a set of operations over `relations'. A relation is a set of `tuples'. (A reminder at this point that in maths a set can't have duplicate members, and the members have no order.) A tuple is a set of attribute names each with a value, i.e. a `name: value' pair, e.g. (show: "Swap Shop", postcode: "W12 8QT"). All tuples in a relation have the same attributes, e.g. they all have `show' and `postcode'. Because a set can't have duplicates, attribute names in a relation have to be distinct so you couldn't have (show: "Swap Shop", presenter: "Noel", presenter: "Cheggers"). Similarly, because a relation is a set of those tuples, you can't have two the same. The operations on a relation include... Selection, AKA `restriction', that plucks zero or more of a relation's tuples to form a new relation, e.g. selecting those with an attribute `show' that's `"Why Don't You?". Projection picks just particular attributes, e.g. `postcode', again giving a new relation. Note, since tuples in a relation must be unique, because it's a set, multiple shows with the same post code will be represented by just one tuple. Join takes two relations and an attribute name from each and produces a new relation with one tuple for every pair of tuples in the two inputs where the attributes have the same value. TV shows and their post codes could be joined to post codes and their lon/lats. If a post code has multiple lon/lats then the show appears multiple times in the result. If the post code doesn't have a coordinate, some don't exist on the ground, then the show doesn't appear at all. And then in addition there's the normal set operations of union, intersection, and difference. Union with two relations that have the same attribute names just merges the tuples; no duplicates of course as it's a set. Intersection is those tuples in both relations. Difference is those in one that aren't in the other. Codd at IBM came up with all this and there's lots more that can be built on them that he also covered. Anyway, if you take a relation and write each tuple on a line, with the attributes in the same order, then it looks a lot like a filled rectangle of columns and rows on a spreadsheet. And given the attributes are now ordered, you can remove their name from each spreadsheet cell and make it a column heading. A sheet could have multiple relations as different rectangles. A `relational database' is based on Codd's theory, but quite warped away from it, and likewise SQL is a crude contortion of his algebra. A relation becomes a table. The tuples are rows within it, and columns define the attribute names. Some things remain: a table's rows aren't in any defined order, just as tuples aren't in a set. Other things deviate: multiple rows can exist in a table with the same values for all attributes. SQL's `SELECT...JOIN...WHERE' does the algebra's `select', `project', and `join' in one, with all the clunkiness one would expect compared to orthogonal operators. The `card' approach to a database, with tables, records, fields, and forms, has each record being a spreadsheet or relational-database row, or algebraic tuple, and a field being a column or attribute. Again, it discards some of the purity of the algebra. Mapping it back onto your stock control, a Location relation might have `description' and `lon/lat' attribute names, and your Stock relation would have a `location' attribute name with a value that matched a Location.description. A UI could present a drop-down of those Location.description's. > > Have you considered Google Sheets instead of your LibreOffice > > spreadsheet? It has a Query function that allows SQL-like expressions. https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/ > > Otherwise, like Keith, I was going to suggest > > http://flask.pocoo.org/ given your acquaintance with Python. Django's advantage is more readily defining the web form for data entry from the description of the database table that you've already told Django about. But overall it's a bigger beast and might be harder to grasp what's going on. > They did kill off 4th Dimension though. Not
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On 14/11/2018 13:25, Terry Coles wrote: On Wednesday, 14 November 2018 12:40:46 GMT Ralph Corderoy wrote: So if you've five widgets, two of them Condition=rusty, is that two rows? What columns come together to make a unique row rather than one that should be merged into another? I'd group those together rather than slot Quantity in between, say. I had to think about this to understand what you meant by your question. At the moment Condition could have several entries; New or Used is the most common, but Needs Assembly also appears (for example, Pis can come bare or with pre-assembled headers). When you talk about rows, do you mean rows in the spreadsheet or rows in the database (is there such a thing)? In terms of the GUI, we would want some kind of dropbox to select the condition I expect. If not it could be simply a plain text entry. (There won't be many Users (at least at first).) I take 'rows' to mean fields in the database Record. Is Location brief enough to be entered consistently? Or should there be a table of locations with precise descriptions, each with a unique ID, either numeric or mnemonic, with the database or code enforcing each Item row uses a valid Location ID? Now, if you has a second table of locations, then if done correctly, adding data to the location field in the first table would bring down a dropbox of items from the second table. Similar for condition? For this one the answer is much the same as for Condition. Peter -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On Wednesday, 14 November 2018 12:40:46 GMT Ralph Corderoy wrote: > So if you've five widgets, two of them Condition=rusty, is that two > rows? What columns come together to make a unique row rather than one > that should be merged into another? I'd group those together rather > than slot Quantity in between, say. I had to think about this to understand what you meant by your question. At the moment Condition could have several entries; New or Used is the most common, but Needs Assembly also appears (for example, Pis can come bare or with pre-assembled headers). When you talk about rows, do you mean rows in the spreadsheet or rows in the database (is there such a thing)? In terms of the GUI, we would want some kind of dropbox to select the condition I expect. If not it could be simply a plain text entry. (There won't be many Users (at least at first).) > Is Location brief enough to be entered consistently? Or should there be > a table of locations with precise descriptions, each with a unique ID, > either numeric or mnemonic, with the database or code enforcing each > Item row uses a valid Location ID? For this one the answer is much the same as for Condition. > Do you want an audit trail so history can be examined to know who made > what changes and when? Handy when something's gone awry between theory > and reality due to the Pilferers of Wimborne. If the system you're > using doesn't provide it, then you can implement it yourself with tables > recording the changes. Wouldn't it be nice :-) Maybe, but we certainly won't need it at first. > Have you considered Google Sheets instead of your LibreOffice > spreadsheet? Access from a web browser. Data stored centrally, > handling multiple editors. Not sure about its history tracking. Google > Apps Script allows custom menu items, etc., by writing Javascript that > runs on Google's machines. Never used it, mind. > https://en.wikipedia.org/wiki/Google_Apps_Script > https://developers.google.com/apps-script/ > And there's quite a few articles about using Google Sheets as a simple > database. Yes we have discussed this and might fall back on it (initially at least) if creating the full-blown database proves too time consuming. We could also transfer the data in Google Spreadsheets into a proper database later. > Otherwise, like Keith, I was going to suggest http://flask.pocoo.org/ > given your acquaintance with Python. For your small needs, SQLite would > be adequate and that's what Flask's tutorial uses. > http://flask.pocoo.org/docs/1.0/tutorial/database/ Flask is probably the most attractive route for us because ultimately we want our Visitor GUI to display current and historical results, stored in a database for the Visitors to marvel at :-) We had decided some time ago (after you suggested its affinity with Python for another application (which is still not started)) to use it for that purpose. > Your type of need keeps occurring. Clive had something similar a year > or two ago. It does seem like a *good* web implementation of > https://en.wikipedia.org/wiki/HyperCard would do, from what I've read > about it, but I don't know of one. Yes there have been a number of quite powerful tools over the years and I have some nostalgia for 4th Dimension on a Macintosh in the early 90s. I don't think it was ever made online capable, but the user interface was superb. Microsoft tried to copy it with Access, but they never achieved the same performance and ease of use. They did kill off 4th Dimension though. My team colleague, who suggested the use of a database for the Inventory, also used this tool during the same period that I did. At the end of the day, this idea is probably for the 'Wouldn't it be nice if' book. I'm sure it's doable, but there is a limit to what a couple of volunteers can achieve (especially when one was a hardware engineer and one a systems engineer and both are heavily tied up with hardware development and its associated control software. However, the Inventory would probably be most useful now rather than later, whereas the Visitor GUI isn't much use until we have a working system! The Inventory could be brought to the fore sooner in the end. Currently we are working on a new Level Sensor with I2C interfacing and a Gate Valve with positional feedback. These are looking pretty good at the moment. -- Terry Coles -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
Hi Terry, > The current spreadsheet has a total of five columns; Item, Condition, > Quantity, Location and Comment. We would probably want to add a > Supplier record and maybe two or three others too. So if you've five widgets, two of them Condition=rusty, is that two rows? What columns come together to make a unique row rather than one that should be merged into another? I'd group those together rather than slot Quantity in between, say. Is Location brief enough to be entered consistently? Or should there be a table of locations with precise descriptions, each with a unique ID, either numeric or mnemonic, with the database or code enforcing each Item row uses a valid Location ID? Do you want an audit trail so history can be examined to know who made what changes and when? Handy when something's gone awry between theory and reality due to the Pilferers of Wimborne. If the system you're using doesn't provide it, then you can implement it yourself with tables recording the changes. Have you considered Google Sheets instead of your LibreOffice spreadsheet? Access from a web browser. Data stored centrally, handling multiple editors. Not sure about its history tracking. Google Apps Script allows custom menu items, etc., by writing Javascript that runs on Google's machines. Never used it, mind. https://en.wikipedia.org/wiki/Google_Apps_Script https://developers.google.com/apps-script/ And there's quite a few articles about using Google Sheets as a simple database. Otherwise, like Keith, I was going to suggest http://flask.pocoo.org/ given your acquaintance with Python. For your small needs, SQLite would be adequate and that's what Flask's tutorial uses. http://flask.pocoo.org/docs/1.0/tutorial/database/ Your type of need keeps occurring. Clive had something similar a year or two ago. It does seem like a *good* web implementation of https://en.wikipedia.org/wiki/HyperCard would do, from what I've read about it, but I don't know of one. Cheers, Ralph. -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On 13/11/2018 09:27, Stephen Wolff wrote: Hi, Once you've written the requirements, you can look at how you could implement them. From what you've said so far, I'd be looking at a Flask app (or, if you'd prefer to spend nine months learning the platform, Django). Shouldn’t take 9 months with Django unless you had absolutely no knowledge of web development, python or linux. I’d be happy to help if you went down that path. As mentioned earlier, my hosting provider includes MySQL as part of my package, and Stephen Wolff has suggested off list that the hosting provider might also provide access to PHPMyAdmin. It does; so I am looking into how suitable that is for our purpose. If there are non-technical users, it probably isn’t much use. It’s more a database administration tool. On that subject, can anyone comment on that? I can see that PHPMyAdmin allows the database to be developed, (and the records can be imported from LibreOffice Calc) but I'm not sure how the users would then access it. As I said at the beginning, I have very little knowledge of database development (yet). But start with the requirements. Well I thought I had; albeit a bit informal. -- In my teaching at the university, I got thrown in to teaching a Database course to 2nd year. I learned a lot in a hurry. The students used Dreamweaver to build a web page that accessed a database. Here is a link to FOSS equivalents: https://www.ionos.co.uk/digitalguide/websites/website-creation/dreamweaver-alternatives-open-source-solutions/ Caveat: I have not tried any. But I may do one day. Peter -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On Tuesday, 13 November 2018 09:27:57 GMT Stephen Wolff wrote: > > As mentioned earlier, my hosting provider includes MySQL as part of my > > package, and Stephen Wolff has suggested off list that the hosting > > provider > > might also provide access to PHPMyAdmin. It does; so I am looking > > into how > > suitable that is for our purpose. > > If there are non-technical users, it probably isn’t much use. It’s > more a database administration tool. I had a suspicion it might be, which is why I asked the question. So if I want to take advantage of the MySQL offering from my provider (it is free as it's part of the contract that I have with them), I only use PHPMyAdmin to initially populate, configure and structure the database? So I would probably have to use something else for the users so are there any suggestions? Or is this going to need a bespoke development, necessitating the use of Flask, Django or similar? My database experience is quite limited, being confined to being a User of the CMS I mentioned earlier, simple tasks using MS Access and even simpler ones using an early Mac tool that I think was called 4th Dimension (that was about 25 years ago). Hence my rather naive questions. > Shouldn’t take 9 months with Django unless you had absolutely no > knowledge of web development, python or linux. I’d be happy to help if > you went down that path. I might take you up on that. My experience of web development is definitely beginner level, of Python only slightly more (as some of my posts on this list will testify) and I've never used Django. Having said that, at the moment I'm just exploring all the options and don't expect to do much active development on this until we've completed some hardware development that is currently ongoing (or at least reach a slack point, while I wait for some dependency to come through. -- Terry Coles -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
Hi, Once you've written the requirements, you can look at how you could implement them. From what you've said so far, I'd be looking at a Flask app (or, if you'd prefer to spend nine months learning the platform, Django). Shouldn’t take 9 months with Django unless you had absolutely no knowledge of web development, python or linux. I’d be happy to help if you went down that path. As mentioned earlier, my hosting provider includes MySQL as part of my package, and Stephen Wolff has suggested off list that the hosting provider might also provide access to PHPMyAdmin. It does; so I am looking into how suitable that is for our purpose. If there are non-technical users, it probably isn’t much use. It’s more a database administration tool. On that subject, can anyone comment on that? I can see that PHPMyAdmin allows the database to be developed, (and the records can be imported from LibreOffice Calc) but I'm not sure how the users would then access it. As I said at the beginning, I have very little knowledge of database development (yet). But start with the requirements. Well I thought I had; albeit a bit informal. -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On Monday, 12 November 2018 19:37:17 GMT Keith Edmunds wrote: > Start by writing your requirements. The hard part: do NOT include any > implementation details in the requirements. Focus on what, not how. Before I say anything else, I feel that I should point out that before I retired, a large part of my job was writing Requirement Specifications for very large Aerospace companies. I am well aware of the need for Requirements to state the what and not the how. > For example, "The database should be hosted on a website somewhere" is not > a requirement. It is a way of achieving an unstated requirement; there may > be other, even better, ways. Not really. All Requirements have to start from somewhere. For example, a stakeholder (for that read 'Customer') has some internal requirements and will already have made some design choices. So instead of starting at rock bottom and saying that this piece of software has to do x, y and z. he might say that this piece of software has to run on an off-the-shelf PC and be hosted by Linux. (Sadly it was often Windows and the product must use Sharepoint. (Urgh) ) That 'how' for the stakeholder now becomes 'what' for the supplier. In my case, the WMT is the stakeholder and members of the WMT team had already decided that the solution should be accessible from multiple devices. This drove the need for my reference to a website (which I have since clarified to mean not necessarily a web page). Yes. I could have written a full blown Requirement Specification for this, in which case the top-level requirement would have been accessible from various devices. However, left like that; the current solution would have fulfilled that requirement because it is possible to open a LibreOffice spreadsheet on Windows, Linux or MacOS (and Android I believe). However, that wouldn't have allowed the access capabilities that we wanted. So in this case, the requirement is 'hosted on a website somewhere' because we have access to a website that we can develop and we had already decided that was what we wanted. > Once you've written the requirements, you can look at how you could > implement them. From what you've said so far, I'd be looking at a Flask > app (or, if you'd prefer to spend nine months learning the platform, > Django). As mentioned earlier, my hosting provider includes MySQL as part of my package, and Stephen Wolff has suggested off list that the hosting provider might also provide access to PHPMyAdmin. It does; so I am looking into how suitable that is for our purpose. On that subject, can anyone comment on that? I can see that PHPMyAdmin allows the database to be developed, (and the records can be imported from LibreOffice Calc) but I'm not sure how the users would then access it. As I said at the beginning, I have very little knowledge of database development (yet). > But start with the requirements. Well I thought I had; albeit a bit informal. -- Terry Coles -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
Start by writing your requirements. The hard part: do NOT include any implementation details in the requirements. Focus on what, not how. For example, "The database should be hosted on a website somewhere" is not a requirement. It is a way of achieving an unstated requirement; there may be other, even better, ways. Once you've written the requirements, you can look at how you could implement them. From what you've said so far, I'd be looking at a Flask app (or, if you'd prefer to spend nine months learning the platform, Django). But start with the requirements. -- Linux Tips: https://www.tiger-computing.co.uk/category/techtips/ -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
On Monday, 12 November 2018 14:24:05 GMT Patrick Wigmore wrote: > Last year, I used [Kexi](http://kexi-project.org/) to record an > inventory of my personal hoard of electronic components. So far so good. > But Kexi does not fulfil your requirements, in that the database would > not be hosted on a website somewhere, and that it would not run on Mac > or Windows (support for those platforms being "in development"). The > database itself can either be a local Sqlite file, or a database > server (MySQL or PostgreSQL). It is, however, open source and it is a > GUI-based tool for the development and use of the database. A clarification. When I said website, I didn't mean the database had to run on a web page. We just want to access it from various devices. For example, my hosting provider includes MySQL as part of my package and I have just created one called 'WMT River System Inventory'The MySQL Control Panel currently says that I have '1 of 10 used', although it is currently 'Setting Up', whatever that means. Presumably, once this is done, I'll be able add records for each item in our Inventory and then all need is a GUI tool to access it from Linux or a Mac. > I suppose you are probably looking either for an off-the-shelf > inventory database solution, or something broadly equivalent to > Oracle's "APEX" software, except open source and perhaps a little > easier to use. I don't think we need the functionality of a full-blown inventory solution; presumably that would have to be paid for. Our idea is to have a database which allows us to hold records for each component and then link the records to the build data for each assembly. I think we should be able to cobble something like that together, as long as the interface is user friendly. -- Terry Coles -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
Re: [Dorset] Database with GUI Front End for Non-Database Experts
Hi Terry, Last year, I used [Kexi](http://kexi-project.org/) to record an inventory of my personal hoard of electronic components. But Kexi does not fulfil your requirements, in that the database would not be hosted on a website somewhere, and that it would not run on Mac or Windows (support for those platforms being "in development"). The database itself can either be a local Sqlite file, or a database server (MySQL or PostgreSQL). It is, however, open source and it is a GUI-based tool for the development and use of the database. Kexi is limited in terms of the types of constraints you can apply to the tables, and I found I had to write some of the queries in SQL instead of using the graphical query designer. It's also somewhat limited in terms of what kinds of forms you can create, and scriptability. I found Kexi to be at least one step above a spreadsheet and entirely satisfactory for my use case, but for a multi-user environment I would want more enforcement of constraints built into the database application, and more flexible and scriptable form design to improve usability. I don't doubt that there might be a nice, open source, web GUI for designing and using database applications, but since I have not used such a thing I will refrain from recommending anything! I suppose you are probably looking either for an off-the-shelf inventory database solution, or something broadly equivalent to Oracle's "APEX" software, except open source and perhaps a little easier to use. Patrick. -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
