common dataset for wiki tutorials lazarus / 16-Databases ---------------------------------------------------------------------- codifies · 2012-09-19 02:50 I've looked at a number of different data sets or even example schema's we could populate... Having an existing example data set that's bundled with a data base server probably means its been thrashed and its short comings hopefully addressed... (good idea BigChimp) To that end I'd like to propose we use the mysql sakila example data set ( http://downloads.mysql.com/docs/sakila-db.tar.gz ) I looked at employees data set but is 40 odd mb where sakila's a more reasonable 3-4 mb I'm prepared to hand craft the schema for at least postgres and data pump the data base and produce a postgres compatible sql 'dump' The idea being that all the tutorials would link to a page "Installing the tutorial data set" this would include sections for each database server that Lazarus support "If you have ACME data base server download file and run the following command...." the example data covers the following scenario there is a company renting video's it has a number of physical shops, maintains a membership list, and has an inventory which contains multiple copies of the same film, it also tracks for example which specific copy is rented by which member... Be sure I'm NOT saying we should build ONE monolithic application as an example, but rather this would be a common data set for simpler tutorials illustrating specific Lazarus database techniques... odd's on most tutorials will only need 2-3 tables at most but the rest of the structure AND data is there if needed in future... ---------------------------------------------------------------------- BigChimp · 2012-09-19 08:53 Good idea, and sakila looks good! The included license does ask us to repeat some... how are they called... "don't use MySQL AB in your marketing texts" clauses but seems to be normal BSD/MIT like. If we put the license text on the tutorial data set install set article, I think we'll be fine. Regarding that 40MB in employee.fdb: you do realize you don't have to recreate all data, do you? Edit: just checked my employee.fdb (package firebird2.5-examples on Debian), it's nowhere near 40MB, about 1 MB. ---------------------------------------------------------------------- ludob · 2012-09-19 10:15 Quoteseems to be normal BSD/MIT likeOnly sakila-schema.sql are sakila- data.sql are BSD licensed. All the rest, including the documentation is not open source. Something to watch out for when writing the tutorials. No copy and paste of text or drawings http://dev.mysql.com/doc/sakila/en/sakila-license.html ---------------------------------------------------------------------- codifies · 2012-09-19 12:13 I created the image myself using mysql workbench... ---------------------------------------------------------------------- codifies · 2012-09-19 12:17 if any one wants a larger data set to stress their app for testing with.... https://launchpad.net/test-db/+download could be adapted and probably better than writing a random generator! ---------------------------------------------------------------------- BigChimp · 2012-09-19 13:46 Quote from: chris_c on September 19, 2012, 02:17:47 pmcould be adapted and probably better than writing a random generator! However, having your own test data generator/random generator in LazDataDesktop would allow use with any sqldb-supported database It's an ill wind that blows nobody any good Edit: typo ---------------------------------------------------------------------- codifies · 2012-09-19 19:51 now that is an idea... if it could be driven by a small xml file which could included with tutorials... all the better ---------------------------------------------------------------------- jamhitz · 2012-09-21 18:10 Brilliant idea!! Count me in if you need any help. ---------------------------------------------------------------------- codifies · 2012-09-24 05:18 While busy I've been thinking about the best way to define table descriptions and realised we'd probably have to define data for some look up tables and randomly creating relationship data between tables could be quite complex Its probably unlikely that people can agree on a common data set for various reasons might we not all be better off not just using TSQL3Connection and having out own tiny sqlite db's for each tutorial? Yes a common source would be nice but how do you please all the people all the time... ---------------------------------------------------------------------- BigChimp · 2012-09-24 05:22 I'm having trouble understanding your reasoning. What do you mean by "randomly creating relationship data"? You can just fill the database with test data, right? You don't need hundreds of movies and clients in your DVD database to demonstrate how to show the overdue DVDs. If you want to make the data random data, ok, that could be. But it doesn't have to be. Why should we use sqlite dbs? Why not use PostgreSQL, MSSQL, etc? Or leave it up to the user? (Edit: note: you can also use the general sqlconnection and allow your user (or the code) to dynamically choose a db in order to make your application more db agnostic) Edit: I'd think the database type is a bit orthogonal to the database schema discussion. ---------------------------------------------------------------------- codifies · 2012-09-24 05:33 it occurred to me that sqlite was a single small self contained blob. I wasn't sure how structure could be defined and in defining it also define rules to ensure the data population made sense with integrity of the structure ---------------------------------------------------------------------- BigChimp · 2012-09-24 05:59 I must be missing the drift of your post... Here goes in the hope that is what you meant. The integrity of the structure would be enforced by the relational model, I suppose: referential integrity by primary/foreign key constraints; you can if needed add check constraints... The field content by the field types (varchar etc). That of course leaves a wide amount of leeway in what data you can enter. Yes, you can enter a zip code as a movie title. Actually, there may be a movie with a zip code as title. Ok, less likely that a renter's name is a zip code then. Some of this could be alleviated by triggers/stored procs in the db or business logic in an n-tier layer. However, for the purposes of demonstrating how databases work in Laz/FPC, I don't think that is needed. If it is, for an advanced tutorial, you can add the necessary SQL DDL to go from the basic schema to the advanced one in that tutorial, I'd think? As for sqlite being a self contained blob: yes, but you still need a dll. Choosing Firebird embedded or any C/S db (obviously you should have the server installed for those though) would be equivalent. Agreed though that setting up sqlite is very easy. That's why sqldb tutorial1 has been adapted to specifically teach users how to use it with sqlite... ---------------------------------------------------------------------- codifies · 2012-09-24 06:12 well you could have different random generators like random name - random date etc more what I was meaning was suppose you some how define a table customers and orders, you need then to define the type if relationship for the random generator should it create foreign key values to records that are already chosen for example and thats just a simple example. I can see it all getting out of hand really quickly... I suggested sqlite because despite needing a single library file, it is still simpler to set up than other databases, the data is all self contained in a single blob and without other settings needed other than the path to the blob... ---------------------------------------------------------------------- BigChimp · 2012-09-24 14:48 Still don't see the relevance of the random generator discussion with regards to a data model for tutorials. However I do understand you think the Sakila schema is no longer a good idea. No problem, in that case I'll continue with the Firebird employee.fdb schema as that is already in use. ---------------------------------------------------------------------- codifies · 2012-09-24 15:56 the idea was maybe that people could create data for the tutorials themselves I got the impression you were less than happy re the licencing of sakila I'd be happy to use employee.fdb myself but it's just a single table afaik... for a data set to be any use for more than just your tutorials we'd need ideally at least two tables with some kind of relationship. ---------------------------------------------------------------------- BigChimp · 2012-09-24 15:57 Quote from: chris_c on September 24, 2012, 05:56:25 pmI'd be happy to use employee.fdb myself but it's just a single table You're wrong. Told you before. ---------------------------------------------------------------------- taazz · 2012-09-24 16:58 Quote from: chris_c on September 24, 2012, 05:56:25 pmI'd be happy to use employee.fdb myself but it's just a single table afaik... You are wrong, Why don't you download and install firebirdSQL your self. it has installation for a lot of OSes including macs (no mobile platforms). For the record the employee.fdb has 10 tables including Job, Project, Country, Customer,  Employee, etc.. Personally I don't mind any schema be it sakila or Employee as long as it can be create by the end user in any database he wants to.