Using database storage in the app: restoring values of spinners, labels, etc.

[Pal Csanyi]

Hello,

I am developing in App Inventor this application. So far all texts is in Hungarian.

These two images below are taken in Designer. There is a DataPicker Keltezés: , label Keltezés?, three spinners ( for each spinner there is a label for it out there ), three buttons: Ment, Betölt, Tanuló, then more labels and below each label a textBox, and one Image component out there.

In Blocks I have these block ( see below ).

When Ment.click then should save in the database the following values: label 'Keltezes_', the selected Items of spinners 'Orarend', 'Osztaly', 'Ora', label 'het_sorszama', the text in textBoxes 'HianyzokNevsora', 'TanitasiEgysegSzovege', 'OktatasiCelokSzovege', 'NevelesiCelokSzovege', 'AzOravazlatSzovegeBeirva', the Image in 'MegjelenitettKepek', and the text in textBox 'NaploAzOrarolSzovege'.

What I want is following. When the button 'Betölt' clicked, to get back stored data from the database and to put these datas in to corresponding labels, spinners, textBoxes.

Is this possible? If yes, how can I do it?

I attached here the exported project to my computer.

Best, from Pali

[Tim Carter]

You could do it like this but it is over complicated in my view.

I usually create a procedure block (purple) for this with all the default values, then call the procedure from a button/other procedure when required. This could also be used in the Screen.initialize block.

Can't see any need in using TinyDB for this, unless your defaults change across sessions based upon a users previous inputs?

EDIT

Just re-read your post and seems you do want to retrieve user entered data, in which case using the TinyDB is the correct approach.

From what you have done you just need to call back each tag and set the label/spinner value to the value stored.

It might be easier to save all the values as 1 tag stored as a list, then restore the values using  the list indexes

[Pal Csanyi]

Hello Tim,

Thank you for the answer.

This app shall be a Lesson Plan application wich allows for teachers to enter it's lesson planes for every Lesson in a school year.
So there could be eg. 36 Lessons in the school year out there.
The app should allow editing every Lesson by entering text into it and determining with labels and spinners what is the date, timetable, class in which must the teacher takes that Lesson, the number of that Lesson, the number of the week in which is that Lesson taked, names of missing students, teaching unit, educational purposes, text of Plan and images for that Lesson, and finally the diary ( what happened in the classroom on that Lesson ).
These informations must be stored in the database, right?
And teacher should have the ability to change these information for any of those Lessons.
So, is the way I am implementing these tasks the right way?

--
Best, from Pali

[Tim Carter]

Hi Pali

Yes, the information will need to be stored in a persistent database.

Will teachers need to share resources and information? If so the tinydb is not the right way to go, because it only stores data on the app itself, on the single device. You may need to use Firebase or another external database system for this, and everyone using the app would need an internet connection.

If they are not sharing information then you can use lists, lists of lists and a tinydb, but you may also need to maintain some external lists as csv files or google sheets (e.g. names of students) so they remain up to date.

Your app looks like it will become quite complex in parts ( I could be wrong?) so to get further help if you converted to English it would make things easier for others to help you.

Tim  

[Pal Csanyi]

Hi Tim,

Teachers will not need to share resources and informations for now.

So I shall use lists, lists of lists and a tinydb. How can I do that?

If I understand well, one Lesson Plan should be saved in tinydb as one list, right?
But there will be eg. 36 Lesson Plans out there so how many lists should to be stored in tinydb out there? 36?
And in one list there should be informations of that particular Lesson Plan?

I just converted to English my app and attached it's TechnicalLessonPlans.aia file here.

Pali

[Abraham Getzler]

I looked at your blocks.

You are saving the component blocks into TinyDB, a mistake.

The data is in the .Text values of most component types,

never in the component block, which is used only with

the advanced Any Component blocks.

You need a way to structure and organize the various

lesson plans, their contents, and their classroom experiences.

I suggest looking through these wildly different sample apps, to

see different ways to structure and present information ...

https://docs.google.com/document/d/1S_aCm1ZyKq3riZwv7rQibr5tZatggLLkiSzneCl9Yl4/edit?usp=sharing

https://docs.google.com/document/d/1AuhZjdMP55Cl9uOrrPG6x_FpZHlPInTaUfEhiwzDUh8/edit?usp=sharing

https://docs.google.com/document/d/1S8eZBKBvtFzafH51a3bKnw_iBVHRnjHaEY1Zbjzscb0/edit?usp=sharing

Give thought to how to organize your lesson plan data into either multiple Fusion Tables

or JSON schemas.

JSON sample: see the JSON section of this FAQ:

https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/mitappinventortest/2cd6Uz__xA0/cPQSiZpcDAAJ

ABG

[Tim Carter]

Hi Pali

ABG is right of course.

I think you need to take a step back and consider what one of your database records will contain. To start with there will need to be a unique reference or ID to use as the "tag".

From what I can see in your design, and from what you have said, there will be 36 weeks, and a Lesson plan for each week. in each Lesson plan there will be many lessons, each requiring the full detail of the record.

So is it something like this:

Lesson:      << Unique ID / Reference

Lesson Plan:  << (choose 1-36)

Date:  <<  presumably the date will be from within the time frame of the Lesson plan, so do you need to identify when the Lesson Plan will be?

Timetable:

Class:   << can this be used to pull in a list of students for each class?

Teaching Unit:

Teaching goals:

and so on

Your database record would then be the text values of:

Lesson, Lesson Plan, Date, Timetable, Class, Teaching Unit, Teaching Goals, etc.

Using the value of Lesson as your tag to save in the TinyDB (because it is unique) and save all the other values as a list (as above)

[Abraham Getzler]

Building on Tim's post, you might also need extra tables for:

• Lesson Parts explosion
  
• Lesson User Experiences (one row per lesson and user experience, assuming multiple users per shared lesson.)

Also look for "PDF" in this FAQ to avoid reinventing the wheel for

presenting images and text.

https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/mitappinventortest/2cd6Uz__xA0/cPQSiZpcDAAJ

ABG

[Pal Csanyi]

Hi Tim,

I agree with ABG and with you of course.

I think you need to take a step back and consider what one of your database records will contain. To start with there will need to be a unique reference or ID to use as the "tag".

I think that that such an unique reference ID could be if I can concatenate the date of a day with the name of a class in which the teacher take a lesson, like this:
the date is eg. '01.09.2017.' and a class name is eg. '5b' then the unique ID would be ' 01.09.2017.5b' because in one day teacher does not take a lesson in one class a period twice time. Or if so, then I should insert one textBox in which can teacher enter the number of period on that day so the unique ID should be then 'date.class.numberofperiod', eg. '01.09.2017.5b.3'.
I hope so you understand this because my first language is not English and for me it is hard to find the exact words for what is in school class, lesson and period.

Also I add some explanation about my subject: I am a teacher of technics and informatics science in a primary school. In one week I take lessons in ten classes. Eg. on Monday I take one Lesson in 5a Class during two period ( 2 times of 45 minutes), another Lesson in class 6c Class during two period.

So to get such an unique ID for the database record, can I concatenate information as mentioned abowe? If yes then how would be the database record has text values?

Best, from Pali

[Tim Carter]

Hi Pali

Use whatever you need to get a unique ID for each record.

Your English is very good!

szép munka  !!

[Abraham Getzler]

I would recommend using one of the free online data modelling tools to lay out

the design of your tables and their relationships.

https://www.red-gate.com/simple-talk/sql/database-administration/five-online-database-modelling-services/

I personally like vertabelo.com for its free web basis and its no-install sharing capability,

but I notice they are free now only for teaching or learning a data modelling class.

I expect you would need to keep separate tables for the Lessons and the

Class Calendars and the assignments of Lessons into slots in the Class Calendars.

The Lessons table is needed to hold information about the contents of each Lesson,

like time estimates and prerequisites, and facilitates reuse across Class Sections

and school years.

The Class Calendars are built by the school administration, to balance seats, pupils,

class meetings, room sizes, and the academic calendar.

The  Class Lesson Schedule (classs syllabus) maps the Lessons onto Class sessions,

so it would need compound keys including Lesson keys, Class Caldar keys, etc.

The text JOIN block is good for making compound keys.

I favor year-month-day as a date format to facilitate chronological sorting.

ABG

[Pal Csanyi]

Honored Abraham,

one should follow your advices about how to organize the database for this application if it intend to be a serious application.
But I am a newbie here and I just want to create the simplest application as possible so I can it use in the incoming school year that is beginning at september this year.
Maybe when I get skills enough I can create such versatile application.

But for now I just want to store my Lesson Plans into database so I can get back those  when I am browsing those Lesson Plans after, or when I am taking that Lesson in a Class and want to enter a diary about that period, and nothing more. Eventually I intend to add another activity where I can view or edit informations for my pupils. grades, etc.

In my case for now I think I want to store these informations ( without datas for pupils for now ) in one table called Plans. I am using WWW SQL Designer to plan this database.
I design the Table of this very simple database:

-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'Plans'
--
-- ---

DROP TABLE IF EXISTS `Plans`;
       
CREATE TABLE `Plans` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `date` DATE NULL DEFAULT NULL COMMENT 'Date when Lesson is taken.',
  `timetable` CHAR(3) NULL DEFAULT NULL COMMENT 'Mon, Tue, Wed, Thu,Fri.',
  `class` CHAR(2) NULL DEFAULT NULL,
  `no_of_lesson` CHAR(3) NULL DEFAULT NULL,
  `no_of_week` INTEGER(2) NULL DEFAULT NULL,
  `no_of_period` CHAR(3) NULL DEFAULT NULL,
  `missing_ones` MEDIUMTEXT NULL DEFAULT NULL,
  `teaching_unit` MEDIUMTEXT NULL DEFAULT NULL,
  `teaching_goals` MEDIUMTEXT NULL DEFAULT NULL,
  `educational_goals` MEDIUMTEXT NULL DEFAULT NULL,
  `lesson_plan` MEDIUMTEXT NULL DEFAULT NULL,
  `images_for_lesson_plan` MEDIUMTEXT NULL DEFAULT NULL,
  `activities_diary` MEDIUMTEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `date`, `timetable`, `class`, `no_of_lesson`, `no_of_week`, `no_of_period`)
);

-- ---
-- Foreign Keys
-- ---


-- ---
-- Table Properties
-- ---

-- ALTER TABLE `Plans` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ---
-- Test Data
-- ---

-- INSERT INTO `Plans` (`id`,`date`,`timetable`,`class`,`no_of_lesson`,`no_of_week`,`no_of_period`,`missing_ones`,`teaching_unit`,`teaching_goals`,`educational_goals`,`lesson_plan`,`images_for_lesson_plan`,`activities_diary`) VALUES
-- ('','','','','','','','','','','','','','');

What do you think about it?

Best, from Pali

[Taifun]

it's sufficient to have the id as primary key...

but which database are you planning to use? a server database like a fusiontable or a MySQL database?

or local  database, i.e. TinyDB? btw. there also exists a local SQlite database, see the SQlite extension https://puravidaapps.com/sqlite.php, which would make things easier in case you plan to use TinyDB...

Taifun

[Abraham Getzler]

Watch out for the columns whose names are plural.

Supplying a list of items for a single column value will break most SQl INSERT statements,

unless wrapped well with ' characters.

If not broken out into separate tables, they will be hard to filter too.

If all you want to do is label and bundle the fields together,

define a JSON schema at http://www.jsoneditoronline.org

to get the ability to keep sublists as elements, and try FireBase

from the experimental drawer.

ABG

[Pal Csanyi]

Which database are you planning to use?

I am planning to use TinyDB. Just do not know which free online data modelling tools to use for the TinyDB?

[Taifun]

I am planning to use TinyDB. 

in TinyDB you can only store tag/value pairs... for TInyDB there is no need for data modelling imho...

see also the TinyDB tutorial by Stephen

as already said, . there also exists a local SQlite database, see the SQlite extension https://puravidaapps.com/sqlite.php, which would make things easier...

Taifun

[Pal Csanyi]

Uh, indeed. I just imported the SQlite extension and added a SQLdb to my application. It is easy to use, it seems so.
However, I am using now the Vertabelo online data modelin tool to model my database. Work in progress! :)
I have now the SQL database model, I attached it here. It is just Tables so far. What do you think about it?

[Abraham Getzler]

The Table Design reads like a bowl of marbles.

Each table has only one column, and there are no

relations between any tables?

Here's a good starter tutorial ...

http://www.vertabelo.com/blog/technical-articles/how-to-create-a-database-model-from-scratch

ABG

[Tim Carter]

Also, have we mentioned the Pencil project ?

http://pencil.evolus.vn/

[Pal Csanyi]

Hello Abraham,

thank you to pointing me in to the right direction, to the Vertabelo tutorial. It is grait!

Unfortunately my Vertabelo free account that was trial just expired. So I model my database in Umbrello UML Modeling Tool.
See the image bellow.

What do you say about it now?

Well, it is not enough good. I shall follow your advices:

I expect you would need to keep separate tables for the Lessons and the
Class Calendars and the assignments of Lessons into slots in the Class Calendars.

I am trying to understand this advice abowe. So, there should be
one table named Lessons
one table named ClassCalendar
one table named ClassSyllabus
right?
But what do you mean when say and the assignments of Lessons into slots in the Class Calendars ?

The Lessons table is needed to hold information about the contents of each Lesson,
like time estimates and prerequisites, and facilitates reuse across Class Sections
and school years.

The Class Calendars are built by the school administration, to balance seats, pupils,
class meetings, room sizes, and the academic calendar.

The  Class Lesson Schedule (classs syllabus) maps the Lessons onto Class sessions,
so it would need compound keys including Lesson keys, Class Caldar keys, etc.

The text JOIN block is good for making compound keys.

I favor year-month-day as a date format to facilitate chronological sorting.

I am trying to use this too in parallel too: http://dbdesigner.net/designer as a tool to design database.

And I will use SqLite database.

Best, Pali

[Ghica]

What you can/should do is run the SQLite manager plugin for the FireFox browser.
Create the tables in there, create sample data and try queries to see if you can retrieve the information that you require.

If this is all fine, then you can export your DB from FrieFox and import it into the SQLite on your phone. The SQLite extension has an import function.
It is much easier to experiment using the SQLite manager then trying to do that on the phone.
Cheers, Ghica.

[Pal Csanyi]

SQLite Manager just installed on my FireFox browser.
Before trying it out, I want to finish my database UML model, by using Dia and http://dbdesigner.net/designer.
Thanks!
So far in Dia I created again tables of database, without associations. I shall finish it soon.

[Abraham Getzler]

It's premature to do physical data base design without a logical Entity/Relation design.

UML tools are better for process design, showing the interplay of

people and processes.  They fail at E/R design.

I'm trying a free creately.com account (no expiration, 5 free diagrams)

with online sharing and .png export .

See attached for a sample Entity Relation diagram,

from their templates, for a different problem in a related 

subject area.

AT this level, it's all about the entities and relations,

before any physical table design or choice of storage system.

ABG

[Ghica]

Actually I agree and disagree with Abraham.
You cannot make entity-relationship diagrams of you do not know what they are.
And, I think Pal has no experience with SQL either.

So, Pal, look if you can find an SQL tutorial, start making some databases and do not be afraid to restart 20-times over until you understand what one-many, many-many relations are and how to represent them in a database.
Also the concept of keys, foreign keys etc. should be something you should look at. Maybe there is someone in your school that can help you with this?

But for your final design, you better know what you are doing, because once made, it will be hard to change.
Cheers, Ghica.

[Abraham Getzler]

See attached for my impression of your data model.

I sent edit invitations to both of you.

I recommend a SaveAs before making changes,

to avoid the Too Many Cooks problem.

ABG

[Pal Csanyi]

Thank you Abraham!!

I saved it as under other name and just started study it.

Best, Pali

[Ghica]

To get this right, you (Pal) may want to provide more descriptions. Or maybe Abraham too.

There seem to be 36 LessonPlans. Or 36 Courses? Or the Curriculum? Is curriculumName unique? And LessonNumber? Or only together?
Are there then single lessons which are given at a specific time in a specific location?
Is the ClassMeeting a specific time and place?
What is a ClassSection?

Courses, classes, students, classrooms, schedules, absence/presence, grades - modeling this is a can of worms. Believe me, I have done this for three years professionally.
It would be good to start with a very restricted definition of what you want to do, and I really very much doubt that you would like to make just a phone app.

So, this is a model for 1 teacher, who teaches a number of courses? Each course consist of a number of lessons?
Then, there are classrooms and date/times where a specific lesson is tought?
For each course there is a list of students? How many students are there?
You are going to keep presence/absence records?
Are you going to keep grades?

Give me some definitions and I will make a model.
@Abraham: Nice tool!!

Cheers, Ghica.

[Pal Csanyi]

Hi Ghica,

thank you for willing me help.
I will try to give a very restricted description about what I want to do with this phone app.
I am a teacher of technics and informatic science in a primary school.
I am teaching pupils aging between 11-15 years.
There are ten school Classes in which I am teaching. The Classes have names, like: 5a, 6a, 6b, 6c, 7a, 7b, 7c, 8a, 8b, 8c. In a Class there are from ten to 25 pupils out there.
So I shall now describe how am I teaching in one such Class and I do this exactly in the same way in other Classes too. ( However, my English knowledge is very poor so I shall make mistakes for sure. )

In the school we have cabinet system that is mean teacher of technics has it's own classroom for teaching technics science, teacher of informatics has it's own classroom where are computers, teacher of English has it's own Classroom for teaching English language, etc.

In 5a Class I have in one school year exactly 72 school periods to teach.
In one week I have to take one block of school periods in this Class. One block take two school period, one after other, say the 1. period start at 7:25 and end at 8:10 ( take 45 minutes ) and the 2. period start at 8:15 to 9:00 hour. So, my period-block start at 7:25 and end at 9:00 hour.
In 5a Class I must to use Serbian language only. But In Class eg. 6c I am using Hungarian language only. So the Lesson Plans must be in these two languages written: in Hungarian and in Serbian.

In one week in 5a Class I have this one period-block only, for each one must be exactly one Lesson Plan. Because there are 36 school weeks in one school year, there must be 36 Lesson Plans out there for the 5a Class. Likewise there must be 36 Lesson Plans for 6b, 36 LP for 7b, 36 LP for 8b. I can use Lesson Plans written in one language for eg. 6b in 6c Class too.

I must write a Lesson Plan for exactly one block of periods. So there must be 36 Lesson Plans for 5a Class out there, written in Serbian language. However, in 6c Class there must be 36 Lesson Plans written in Hungarian language out there. Say I can use a Lesson Plan number 1. written in Hungarian for a Lesson in all these Classes: 6b, 6c, because in 6b, and 6c we are speaking just in Hungarian. But the Lesson Plan number 1. can be used in 6b and 6c only on the 1. period-block. The Lesson Plan 1. can be used on the 1. school week only, the Lesson Plan 2. on week 2., etc.

I wish to create and have this 'TechnicalLessonPlan' phone application to record, browse and modifying these information ( see bellow this note ):

NOTE!
When I made the GUI for this app by using MIT AppInventor2 on the net ( the .aia file is attached bellow ), I made it to get a GUI that I want to use for these purposes just mentioned abowe.

A lesson Plan should contains the following informations:
- must be written in one of the languages ( Hungarian or Serbian ? )
- on which date is taken this Lesson Plan to teach pupils in 5a Class ?
- which timetable is used on that day ( Mon,Tue,Wed,Thu,Fri ? )
- in which class is taken this Lesson Plan (5a, 6a, 6b, 6c, 7a, 7b, 7c, 8a, 8b, 8c ?)
- what is the number of Lesson Plan ( for the technics these can be: 1-2,3-4,5-6,..,71-72 ? ) ( the Lesson Plan 1-2 is for the week 1., the LP 3-4 is for the week 2., etc. )
- what is the number of school week ( 1,2,3, .., 36 ? ) NOTE: In my app I managed that that when one select from the spinner the number of the Lesson Plan, the number of week is set automatically.
- what is the nmber of period when this Lesson Plan was taken ( 1-2, 2-3, 3-4, .. 6-7 ? ) ( these are the period-blocks ) ( eg. I can take one Lesson in 5a on Monday on 2-3 period-block then have hole hour than must take another Lesson in 6b on 5-6 period-block, etc. )
- which pupils are missing from this period-block ( nobody, or name 1, name 2, .. ?) This is the presence/absence, right?
- what is the Teaching Unit for this Lesson Plan ( a sentence ) ? ( describes what will be pupils study )
- what are Teaching Goals for this Lesson ( a sentence ) ? ( eg. teaching pupils how to use the jigsaw )
- what are Educational Goals for this Lesson ( a sentence ) ? ( eg. teaching pupils to accurate work )
- what is the Outline for this Lesson ( few sentences, which describes what will pupils do on this period-block ) ? ( eg. pupils will create components of the napkin holder )
- on ore more images which represents eg. what should be on the school table, say a sketch of napkin holder parts
- I want to write at the and of the period-block what are pupils do on that period-block - this should be the Activity Diary.

Before the start of a school year we get from the Ministry what must we teaching in 5., 6., 7. and 8. Classes in Technics science school subject. This is called Teaching Program.
From this Program ( which conatins duties not for one subject only, but for all school subjects ) I and my colleges take out duties only for our subject - which is technics science. Then we distribute over several school hours duties - what should study pupils in this school year. For this we create the so called Class Syllabus.

Class Syllabus is an Excel - or Calc table in which are following informations.

One raw of the table contains one school week with these columns, from left to right:

the month, teching theme, the number of school period ( not period-block! ), goals of this teaching theme, the number of period-block, the teaching unit, which quarter of school year is this, type of the class ( here the class not mean eg. 5a, 6a Classes, but a school period, and can be eg. new curriculum, practical work, etc. ), Educational form ( eg. head-on, individual ), educational method ( drawing, dialog, demonstration ), educational tools ( eg. jigsaw, wood ), workpiece ( eg. Cartoon technical drawing ).
In Class Syllabus the are 36 raws out there and the heading raw abowe these raws.

That would be good if I could make from such Class Syllabus a table or a SqLite database and could load it in to my phone app to use it.

In my app I want to keep students grades and want to get an average grade for every student at the end of the quarter of school year, notes about whay get a student such grade for a work, moreover my notes for every student about diligence of a student, did the student bring the equipment up or not?

In our school initially I should be the first who use such a phone app for this purpose. I expect that that when other see what am I using and whether this app makes my work easier, then may be happen that that other want to use it too. So this is a model for one teacher.

That is, this is my description of my system for teaching pupils. I hope I was clear, but if not, go a head, ask more questions.

Best, Pali

[Ghica]

I will need some time to look at this. Maybe Abraham has an opinion too.
Cheers, Ghica.

[Pal Csanyi]

I forgot to mention the Timetable. Here is my Timetable for the past, 2016/2017 school year:

I shall to try again and again to create database for my app, by using http://dbdesigner.net .

Best, Pali

[Ghica]

Hi Pali,
A few questions:

1. At the start of the school year you get information about lesson plans. How? On paper, or maybe a spreadsheet, or, maybe from a website?
2. The same question for students. Do you have a list of names for each student, per lesson? On paper, spreadsheet or website?
3. Assuming we figure out the right way to save lesson plans, what should you know to be able to load one?
4. What should the button Student do?
5. Missing students is a text field, should it not be a list you could add names to?
6. Where do you want to store the images?
7. There is a diary at the end, that means that you would create a new lessonplan for each day and each class?

Then, for my understanding, for example for Monday you would have two lesson plans, one for class 6a and another for class 8d.
Class 6a would span period 2 and 3 and in time it would be from 8:15 - 10:00, Class 8d would span period 4 and 5 and in time it would be from 10:15 - 11:00, but what do the red lines mean?
What does 6a is a period block mean?

Cheers, Ghica

[Pal Csanyi]

Hi Ghica,

I insert her another image of my Timetable. This is the whole timetable, previous is edited to show only when and where have my Classes in the Week.
It is difficult to express myself in English, because do not know how to say when Class means the organisation unit in which 10-25 students belongs, and when Class means the place ( Class room ) where a teacher must take shes / his Lesson? For as the Class means mainly the organisation unit where a number of student belong. Because we have in the school Cabinet system, such a Class have English during one school period in the Cabinet of English. After that this Class - students - are going into the Cabinet of say Mathematics where will the teacher of mathematics hold shes / his Class. You see in English Class means many things.

My answers follows.
Q.: 1. At the start of the school year you get information about lesson plans. How? On paper, or maybe a spreadsheet, or, maybe from a website?
A.: We get these informations on paper. These are not Lesson Plans, but a Program in textual form only ( not in spreadsheet ). Actually this Program does not changes for several years so we can use the same Class Syllabus for several years.
Q.: 2. The same question for students. Do you have a list of names for each student, per lesson? On paper, spreadsheet or website?
A.: Students belong to a Class ( see abowe my explanation ). Every such Class has it's own form-master, a head teacher. We get a list of students names for each such Class ( which has a head teacher ) in paper format, but I then create in Calc for my self such a spreadsheet for every such Class.
Q.: 3. Assuming we figure out the right way to save lesson plans, what should you know to be able to load one?
A.: If we have the right way to save lesson plans, I should know followings: 1. the language in which was lesson plan written, 2. the number of Lesson. Say after I 1a. load from the database such a Lesson Plan number 9-10, I could then 1b. set the date, timetable ( Mon, or Tue, or ... ) and Class ( 5a, or 6a, or 6b, etc. ), eventually clear names in Missing ones List and save back this Lesson Plan in another record of the database. So next time, if I set Language, Date, Timetable, Class, Number of Lesson in the applications GUI and I click on the Load button, then should I see the stored Lesson Plan in step 1b. for the Date, Timetable, Class, Number of Lesson.
Q.: 4. What should the button Student do?
A.: If I have load a Lesson Plan, when I begin the Lesson in a say Class 6b, then I could load a Student name with student's informations about it's grades and want to get an average grade for every student at the end of the quarter of school year, notes about whay get a student such grade for a work, moreover my notes for every student about diligence of a student, did the student bring the equipment up or not so I can add a new grade for that Student, or write down notes about it's diligence, etc. This should be in my app an another Screen, an another Activity.
Q.: 5. Missing students is a text field, should it not be a list you could add names to?
A.: Indeed. I attached the modified GUI to include List View instead of TextBox. In the ListView then should be names of Students who are belong to that Class ( say 6b ) so I can choose from that list just that ones who are missing from that Lesson, or choose nobody because every body are there, right?
Q.: 6. Where do you want to store the images?
A.: My idea is that, that if I drow a sketch on the school table, the I use my phone to take a picture of that school table. Then at home I can download it to my computer, convert it to a vactor graphic format ( by using Inkscape for example ), and after that to load it back to my phone. So, when this is done, I should load it somehow in to my TechnicalLessonPlans application at the approppriate Lesson Plan number, so I can Save it to the Database as a vector graphic image, or images, if I have more images for that Lesson Plan.
Q.: 7. There is a diary at the end, that means that you would create a new lessonplan for each day and each class?
A.: The diary is to write a log of that Lesson, eg. what happened on that Class ( during that period-block when I hold the Class using that Lesson Plan), whether was student accomplished teaching and educational goals of that Lesson Plan? This should be a text only.

Yes, for Monday I would have two Lessons to hold in the Class 6a ( one period-block 2-3) and in Class 8d ( another period-block 4-5 ).
Yes, Class 6a would span period 2 and 3 and in time it would be from 8:15 - 10:00, Class 8d would span period 4 and 5 and in time it would be from 10:15 - 11:00.
The meaning of the red lines is the following. Every teacher has it's own duties at big break in a school day, or at the beginning of such day. With red lines I just mark the two big brake which are between 2. and 3. period, and between 3. and 4. period. On the image that I attached bellow you can see where I have to be on Monday during the 1. and 2. big break.

The 6a is a period block is ther because I was trying to explain to you wht does it mean. In tha image bellow ther is no such explanation out there.

On this image there are more informations out there, eg. when has Teacher2 Classes in Cabinet for Informatics, or Classes in Cabinet of Technics science, when I have Reception clock when I must be there to accept the parents, and when I have groups where my students can create some technical things. :)

Best, Pali

[Abraham Getzler]

I see you are making great progress with Ghica.

Here are some ideas for nouns ...

Cohort for the group of students under a Head Master

Session for a meeting in time and space for a group of students to learn something

Department for the English equivalent of Cabinet  ?  (As opposed to the Cabinet of Dr Caligari)  ;)

Can you store the lesson plans as Google Docs or some other web based document?

That would eliminate the need to get too detailed about content types in AI2.

The app could track meta data about the document, like language, and its name and sequence,

and URL.

Also, the teaching goals that come from the Ministry ...

Is it a small list that you would select from in describing each

lesson plan?  If so, keeping them in a separate table to

populate a List Picker would be helpful when setting up lesson plans.

ABG

[Pal Csanyi]

Hi Abraham,

thank you very much for your appreciation and thank you for the nouns. I will replace in my description of the system approppriate nouns with these for Ghica.

Can you store the lesson plans as Google Docs or some other web based document?

I have Google account and can have google docs out there: textual docs, spreadsheets, drawings for images that could be used (?) as images for my Lesson Plans.

The app could track meta data about the document, like language, and its name and sequence, and URL.

Do you mean that that all those informations in my Lesson Plans, like: language in which was lesson plan written, date, timetable, cohort, number of lesson, session, missing ones, teaching -unit, -goal, educational goal, outline of lesson plan, activity diary and the vector graphics images as drawings also can be stored in google docs and used by my application?

Also, the teaching goals that come from the Ministry ...
Is it a small list that you would select from in describing each lesson plan?  If so, keeping them in a separate table to populate a List Picker would be helpful when setting up lesson plans.

Teaching goals that come from the Ministry are described textually in a PDF file.
Eg. in the fifth Cohorts ( 5a, 5b, 5c with 10-25 students in each Cohort ) there are 26 teaching goals listed in that document given to as from Ministry.
But these are given generally and these are not specific to a Lesson. However, I can write a list of teaching units for the whole school year so so those goals would be specific to specific Lessons. These goals could I store then in a database table to use with the List Picker in my app.

Best, Pali

[Pal Csanyi]

The first step in modeling the database was to describe the system of Lesson Plans. This is done with Abraham's and Ghica's help. Thank you Ghica and thank you Abraham. Now I am doing Data Analysis for the database. Then I shall create E-R Diagram. This is a task for me in which I have not skills so far. I am studying it now how to do it well. I find good tutorial's for this.

[Pal Csanyi]

Hi,

I decide that that first I want create a simplest application with using a database: TinyDB or SQLite.

I am now creating the first version of the E-R D of the database.

Best, Pál

[Abraham Getzler]

https://www.google.com/search?q=how+to+do+ER+modelling

ABG

[Pal Csanyi]

Hi Abraham,

thank you for the google search results.

I find some of these already, but shall look the others too.

Moreover, I find scripts for students of universities about ER modeling in Hungarian. I'm just studying these.

My favorite ER modeling applications is now the Dia. I find tutorial on Youtube how can one create ER diagram with it.

It start here:
https://www.youtube.com/watch?v=JoVwansiTkM&t=4s


best, Pál

[Pal Csanyi]

Hi,

finally I created the ER diagram of this simple model by using Chen's notation in the Dia diagram editor. The model follows.

The diary about school class contains: date of school day, timetable on that school day, number of lesson, name of cohort, missing student(s), overall which is a textual description about that school class.
I attach here the ER Diagram that I make so far.
Do you find any mistakes in it?

Best, Pál

[Ghica]

Hi Pal,
This is very unfortunate. There was a good E-R model in the making. I am attaching the PDF here for Abraham if he wants to look.
What is missing is that you did not make a final version, including my comments you agreed on and changing Syllabus to Curriculum.
If you do that, you have all the entity descripions, their attributes and their relationships to other entities.
You could have easily put this in Abrahams tool or another tool with this type of notation. The notation you used in the Dia tool is cumbersome and does it really create SQL for you?
Cheers, Ghica.

[Pal Csanyi]

Hi Ghica,

The notation you used in the Dia tool is cumbersome and does it really create SQL for you?

Chen's notation is another notation beside Crow’s Foot notation. Why would it be cumbersome?

Dia is a graphical tool to draw diagrams, like ER Diagram, Class Diagram, etc.
There is a command line tool to generate SQL from Class diagrams created with Dia.
It is called dia2code. http://dia2code.sourceforge.net/features.html

So when I finished my ER Diagram, then I must it convert into UML Class Diagram in Dia.
Then can I convert it to SQL.

There was a good E-R model in the making.

Indeed, but this is not yet an ER Diagram, just a description of datas. It is very complicated to me to create from it my first ER Diagram.
So I decide to simplify my model.
First I want to make this very simple ER model, then implement it into my application.
If it works, then I want to improve it to be better, by adding more entities, like Curriculum, etc.

Best, Pál

[Ghica]

Hi Pal,
It is a disappointment to me that you made something completely different from what you had before, which does not look like an E-R model at all.
The notation is cumbersome, because you apparently lost track of what the entities are (Student, Cohort, LessonPlan, Missing, Grades, TeachingUnit, Curriculum). These should go into the rectangles and nothing else.
So, diary about school class may be an attribute of LessonPlan indicated by a balloon, but it is not an entity in your model.

Your final product from this modeling excersize should be a set of SQL statements for SQLite, going back to UML is putting the horse behind the cart. It would be useful to have UML if you were going to implement your app in Java or some other object-oriented language, but you are going to use App Inventor, which does not support objects or inheritance (alas!). Database models are like object models without inheritance and E-R models are OK for that.
(Diaries could be entities if you wanted them to be structured things, having a title, summary, and content as attributes for example, but that would be over-engineering here).

This is a forum for using App Inventor and not for teaching database theory, therefore I am trying to shortcut this now. I made a data model using a tool called Pony, which I found after some googling. https://editor.ponyorm.com/
Pony is intended for using with a Python ORM, but it can generate perfectly allright SQL for SQLite (as you should know, SQL has many dialects). I attach it here as a txt file. Just as an example, here is de DDL for LessonPlan:

CREATE TABLE "lessonplan" (
  "id" INTEGER CONSTRAINT "pk_lessonplan" PRIMARY KEY AUTOINCREMENT,
  "cohort" INTEGER NOT NULL REFERENCES "cohort" ("id"),
  "classroom" TEXT NOT NULL,
  "lessondate" DATETIME,
  "timetable" TEXT NOT NULL,
  "schoolweekno" INTEGER,
  "sessionblock" TEXT NOT NULL,
  "teaching_unit" INTEGER NOT NULL REFERENCES "teachingunit" ("id"),
  "diarynotes" TEXT NOT NULL
);

If you want to change something, I can send you a share invitation for the model. If you have questions about the model, you know how to find me.
If you have questions how to continue with this in App Inventor, ask here.
Cheers, Ghica.

[Pal Csanyi]

Hi Ghica,

you're right. I have to learn E-R modeling but not here. Sorry for the noise.

I appreciate your advices and I am trying to create this application by using MIT App Inventor 2.

I just signed up on Pony and tried it out. With Pony is very easy to work with. I can create your model based on the picture you provided. Thank you very much for the model!

This Forum is for questions about using App Inventor only, indeed.

Best, Pál

[Ghica]

Hi Pal,
It is not so black and white. Because App Inventor can touch on many area's, we try to answer questions in many area's too. There are always readers with an amazingly wide knowledge that you can tap into.
If you want to learn more about E-R modeling, by all means do so, and indeed not here.

More important is, to apply what you have learnt so far.

App Inventor is about trying things out, see what works and what not, start over if something does not work. The good thing is that you do not have to write a zillion lines of code before finding out that something is wrong with your approach. In app inventor you save what you had, put some blocks you can reuse in the backpack and off you go again.
Be happy: Invent!
Cheers, Ghica.

[Abraham Getzler]

@Ghica

Thanks for the Pony link.

Nice tool!

(Would never have found it without the link!)

And nice data model too!

ABG

[Pal Csanyi]

I created a modified version of database which was created by Ghica. I created it with Pony.
I exported it as SQL with two click of the mice. I copy and paste the SQL code in to TechnicalLessonPlans_v_001.sql .
Then I imported it in to Firefox Plug-in called sqlite-manager.
There I filled tables with data, tried some Searches and Views.
Now how can I import this database into App Inventor?

[Ghica]

Pal,
I assume you have bought the SQLite extension of Taifun: http://puravidaapps.com/sqlite.php
At his site he explains how to import a database. The database from the FF plugin can be found at some folder where you saved it. The easiest is to upload it as an asset and then import.
Cheers, Ghica.

[Pal Csanyi]

Ghica,

I did not bought the SQLite extension of Taifun and I am not planning to buy any extension at all.
I just realise that that without buying Taifun's extension I can't use it with MIT App Inventor, right?

Then I can't use the SQLiteAddons extension in MIT App Inventor, right?

So I can use TinyDB only in my application because then I do not must to buy any peace of software, right?

Best, Pál

[Ghica]

Pal,
Only the sun shines for free.
$10 is not a huge amount of money and allows Taifun to make a living, a great person, who contributes enormously to App Inventor with his knowledge and also with his extensions, where most are free.

You can use the TinyDB of course. It will make your app complicated.
Another option is to use Fusion tables. They use SQL too.
- Advantage: it is free and you can share your data with other teachers.
- Disadvantage: you need access to the internet when using your app. It is not so easy to set up.
Do the Pizza Part tutorial if you want to know more about it. http://appinventor.mit.edu/explore/ai2/pizzaparty.html
Cheers, Ghica

[Pal Csanyi]

Ghica,

I have bought the SQLite extension of Taifun.
I find the database created with FF plugin.
How to upload it as an asset?

Best, Pál

[Taifun]

same as uploading an image into App Inventor...

see also the example project here https://puravidaapps.com/sqlite.php

Taifun

[Ghica]

Hi Pal,
Very good decision!
Once you have loaded your DB into the phone, (follow Taifuns example), maybe it is time to close this thread and start a new one if needed.
Cheers, Ghica.

[Pal Csanyi]

Hi Ghica,

indeed, I have loaded mine database into the phone.
Just want to ask a question: whether must a SQLite database have the file .sqlite extension?
Can it be just .sql?

Best, Pál

[Ghica]

Apparently you have an asset now loaded in your app, but you must also load the database into the extension, look at Taifuns example how to do this. As long as you provide the right file name it should not matter what the extension is, but if yours is .sql, I am wondering what the format of your file is. You have the internal format which, using the plugin, has no extension at all. I changed my example to .db, to know what it is and because Taifun uses it in his example.
If you have .sql, is it exported sql statements? In that case look for the stored internal format and use that file instead.
Cheers, Ghica.

[Pal Csanyi]

I uploaded my database file which has name: TLPsD.sqlite in to the Media at right bottom of the MIT App Inventor GUI.
I can't find the Taifun's example how to load the database into the extension. Where it is?
Do you mean this on my attached image?

[Ghica]

Yes. Cheers, Ghica

[Pal Csanyi]

If you have .sql, is it exported sql statements? In that case look for the stored internal format and use that file instead.

Well, I do not understand what you say.
Bellow is the file that is exported by using Pony. I just copy the SQL from Pony to a file. Now I name it to TLPsD.db.
Now I am trying to open it in the Firefox SQLite Manager to fill it with data's, but get error message. After I successfully fill it with data, I will it upload again to the App Inventor to my project.
But can't because of the FF SQLite Manager error:

SQLiteManager: Error in opening file TLPsD.sqlite - either the file is encrypted or corrupt
Exception Name: NS_ERROR_FILE_CORRUPTED
Exception Message: Component returned failure code: 0x8052000b (NS_ERROR_FILE_CORRUPTED) [mozIStorageService.openUnsharedDatabase]

What can I do to solve this problem?

The file is:

    CREATE TABLE "cohort" (
      "id" INTEGER CONSTRAINT "pk_cohort" PRIMARY KEY AUTOINCREMENT,
      "cohort_id" TEXT NOT NULL,
      "gradelevel" TEXT NOT NULL,
      "headteacher" TEXT NOT NULL
    );

    CREATE TABLE "student" (
      "id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT,
      "studentid" TEXT NOT NULL,
      "firstname" TEXT NOT NULL,
      "lastname" TEXT NOT NULL,
      "cohort_id" INTEGER NOT NULL REFERENCES "cohort" ("id")
    );

    CREATE INDEX "idx_student__cohort_id" ON "student" ("cohort_id");

    CREATE TABLE "teachingunit" (
      "tunitno" TEXT NOT NULL CONSTRAINT "pk_teachingunit" PRIMARY KEY,
      "language" TEXT NOT NULL,
      "tutitle" TEXT NOT NULL,
      "gradelevel" TEXT NOT NULL,
      "teachinggoal" TEXT NOT NULL,
      "educationalgoal" TEXT NOT NULL
    );

    CREATE TABLE "lessonplan" (
      "id" INTEGER CONSTRAINT "pk_lessonplan" PRIMARY KEY AUTOINCREMENT,
      "cohort" INTEGER NOT NULL REFERENCES "cohort" ("id"),
      "classroom" TEXT NOT NULL,

      "lessondate" DATE NOT NULL,
      "timetable" TEXT NOT NULL,
      "schoolweekno" TINYINT NOT NULL,
      "sessionblock" TEXT NOT NULL,
      "teaching_unit" TEXT NOT NULL REFERENCES "teachingunit" ("tunitno"),

      "diarynotes" TEXT NOT NULL
    );

    CREATE INDEX "idx_lessonplan__cohort" ON "lessonplan" ("cohort");

    CREATE INDEX "idx_lessonplan__teaching_unit" ON "lessonplan" ("teaching_unit");

    CREATE TABLE "missingstudent" (
      "id" INTEGER CONSTRAINT "pk_missingstudent" PRIMARY KEY AUTOINCREMENT,
      "lesson_plan" INTEGER NOT NULL REFERENCES "lessonplan" ("id"),
      "student" INTEGER NOT NULL REFERENCES "student" ("id")
    );

    CREATE INDEX "idx_missingstudent__lesson_plan" ON "missingstudent" ("lesson_plan");

    CREATE INDEX "idx_missingstudent__student" ON "missingstudent" ("student");

    CREATE TABLE "studentgrade" (
      "id" INTEGER CONSTRAINT "pk_studentgrade" PRIMARY KEY AUTOINCREMENT,
      "grade" TEXT NOT NULL,
      "gradedate" TEXT NOT NULL,
      "note" TEXT NOT NULL,
      "diligence" TEXT NOT NULL,
      "workpiece" TEXT NOT NULL,
      "bringequipment" TEXT NOT NULL,
      "behavior" TEXT NOT NULL,
      "lesson_plan" INTEGER REFERENCES "lessonplan" ("id"),
      "student" INTEGER NOT NULL REFERENCES "student" ("id")
    );

    CREATE INDEX "idx_studentgrade__lesson_plan" ON "studentgrade" ("lesson_plan");

    CREATE INDEX "idx_studentgrade__student" ON "studentgrade" ("student")

[Ghica]

Pal,
I explained to you that there is SQL that you can have in a file and that there is the internal format the database management system (SQLite itself) uses to store its data. SQLite stores it in a simple file and when you used the FF plugin, you determined what its name was. This file is in an unreadable format. Taifuns example is based on importing a ready database, filled with example data, just like you are supposed to have made using the FF plugin.

But apparently you have a file with SQL (DDL) statements in it. Therefore the way to set up your database is to read the file using the file component and in the File.GotText block, use the SQLite execute sql block where the sql is the contents of your file. Now you should have your tables created, but no content.
Cheers, Ghica.

[Pal Csanyi]

Ghica,

I understand now.

In the FF SQLite Manager the file with SQL statements can be run if I create a new database, but FF SQLite Manager ( FF SQLM ) add the .sqlite eztension to that database file automatically.
So, when I created this new database in FF SQLM, then I can copy and paste the SQL code from Pony into FF SQLM and can run it.
That way I can get tables that we created in the Pony.
So I can fill data into database in FF SQLM now.
After I finish this job, I will upload this TLPsD.sqlite database file into APP Inventor and import it with SQLite extension's aproppriate block's.
Right?

Best, Pál

[Ghica]

Maybe. How you proceed depends on whether you would like to import an already filled database (with, for example, student names) that you made using the FF plugin, and continue developing your app from there,
or,
you can just execute the Pony SQL in your app (using the SQLite.execute block). The result will be a database with tables but no data in the tables.
Whatever works best for you.
Cheers, Ghica.

[Taifun]

Whatever works best for you.

see also Q6 here https://puravidaapps.com/sqlite.php#q

Taifun

[Pal Csanyi]

Hi Taifun,

on first run of your app just create your tables (CREATE TABLE statements) and use some INSERT TABLE statements to add some data

First run of an app. I do not understand this. First run after installation of the app? Can an app detect whether is it's first run or not?

Best, Pál

[Ghica]

Pal,
You do not want to create your tables twice. Anyway, you would get errors. Look at the Screen1.Initialize block in Taifun's example at https://puravidaapps.com/sqlite.php
It is a very commonly used trick he uses there.
Cheers, Ghica.

[Taifun]

 Can an app detect whether is it's first run or not?

you might want to do some tutorials to learn the basics...

A very good way to learn App Inventor is to read the free Inventor's Manual here in the AI2 free online eBook   http://www.appinventor.org/book2 ... the links are at the bottom of the Web page.  The book 'teaches' users how to program with AI2 blocks.

There is a free programming course here http://www.appinventor.org/content/CourseInABox/Intro  and the aia files for the projects in the book are here:  http://www.appinventor.org/bookFiles  

How to do a lot of basic things with App Inventor are described here:  http://www.appinventor.org/content/howDoYou/eventHandling  .

Also do the tutorials http://appinventor.mit.edu/explore/ai2/tutorials.html to learn the basics of App Inventor, then try something and follow the

 Top 5 Tips: How to learn  App Inventor

You will not find a tutorial, which does exactly what you are looking for. But doing the tutorials (not only reading a little bit) help you to understand, how things are working. This is important and this is the first step to do.

Taifun

---

Trying to push the limits of App Inventor! Snippets and Tutorials from Pura Vida Apps by  Taifun.         

[Pal Csanyi]

Taifun,

thank you very much for pointing me how to start with App Inventor!

Best, Pál

I think we can here close this thread now. ( How to close it? )

Pál