Project

General

Profile

Feature #3910

Single course database (in chamilo_main )

Added by Julio Montoya almost 8 years ago. Updated over 7 years ago.

Status:
Feature implemented
Priority:
Urgent
Category:
-
Target version:
Start date:
22/09/2011
Due date:
% Done:

90%

Estimated time:
Spent time:
Complexity:
Difficult
SCRUM pts - complexity:
40

Description

1. Add all course tables in the main database (1 time)
2. Add a new field called "c_id int" for those new course tables
3. Fix selects/inserts/update/delete queries
4. Fix the migration/installation process
5. test and test


Files

db.png (23.6 KB) db.png Julio Montoya, 22/09/2011 19:02

Associated revisions

Revision b020f1dc (diff)
Added by Julio Montoya almost 8 years ago

Changing lot of queries in order to use one real course database see #3910

Revision f8876b4a (diff)
Added by Julio Montoya almost 8 years ago

Adding migration from Chamilo 1.8.X (multiple dbs) to Chamilo 1.9 see #3910, adding "c_id"as a primary id component in the course tables

Revision c7b150b0 (diff)
Added by Julio Montoya almost 8 years ago

Fixing SQL queries due the nes DB changes (singe database) see #3910

Revision f4fbb920 (diff)
Added by Julio Montoya almost 8 years ago

Fixing group # of members due the recent DB changes (single database) see #3910

Revision 15f73cd1 (diff)
Added by Julio Montoya almost 8 years ago

Fixing the metadata table due the new database changes (single database) see #3910

Revision 98c3b8fe (diff)
Added by Julio Montoya almost 8 years ago

Fixing more queries due the new database changes (single database) see #3910

Revision 1c9ddb43 (diff)
Added by Julio Montoya almost 8 years ago

Fixing more queries due the new database changes (single database) see #3910

Revision 33e153c8 (diff)
Added by Julio Montoya almost 8 years ago

Adding course int id see #3910

Revision 295e32b2 (diff)
Added by Julio Montoya almost 8 years ago

Adding course int id see #3910

Revision 35835faa (diff)
Added by Julio Montoya almost 8 years ago

Adding course_id in queries see #3910

Revision 323d5542 (diff)
Added by Julio Montoya almost 8 years ago

Adding c_id in queries changes due the new single database changes see #3910

Revision e9e9aa14 (diff)
Added by Julio Montoya over 7 years ago

Some fixes in the document tool due the new single database see #3910

Revision 53f13021 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database see #3910

Revision 275dfc19 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database see #3910

Revision 4c1a60d7 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database see #3910

Revision 2dff2a39 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database see #3910

Revision d267cbc1 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database changes see #3910

Revision 309a6942 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database changes see #3910

Revision 76d8f816 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries due the single database see #3910

Revision 41ad44b8 (diff)
Added by Julio Montoya over 7 years ago

Fixing SQL queries adding c_id see #3910

Revision 4fd80e1f (diff)
Added by Julio Montoya over 7 years ago

Adding the c_id field in some course tables see #3910

Revision bb9f780d (diff)
Added by Julio Montoya over 7 years ago

Some fixes in the dashboard due the recent changes in the single database see #3910

Revision 8ede09f2 (diff)
Added by Julio Montoya over 7 years ago

SQL query fix due the recent changes in the single database see #3910

Revision dc99331f (diff)
Added by Hubert Borderiou over 7 years ago

Adding the c_id field in some lp request for scorm and aicc table - ref #3910

Revision 22d78033 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries adding c_id field see #3910

Revision ba95996c (diff)
Added by Julio Montoya over 7 years ago

Fixing query due the recent changes when adding the c_id see #3910

Revision 3c610fe4 (diff)
Added by Julio Montoya over 7 years ago

Adding c_id in queries see #3910

Revision 748ea61e (diff)
Added by Julio Montoya over 7 years ago

Fixing number of threads due the new DB changes see #3910

Revision 872d9197 (diff)
Added by Julio Montoya over 7 years ago

Fixing queries adding c_id see #3910

Revision 28544c79 (diff)
Added by Julio Montoya over 7 years ago

specific_field is not a problem see #3910

Revision 911ec5a9 (diff)
Added by Julio Montoya over 7 years ago

Adding c_id in query see #3910

Revision 1a8f10cd (diff)
Added by Julio Montoya over 7 years ago

Fixing the search xapian plugin due the recent changes in the DB see #3910

Revision dc0cf751 (diff)
Added by Julio Montoya over 7 years ago

Removing unused function get_course_table_from_code see #3910

Revision cf13a78c (diff)
Added by Julio Montoya over 7 years ago

Adding c_id when copy a course session into another course session see #3910

Revision 237957f1 (diff)
Added by Julio Montoya over 7 years ago

All databases (stat, user) are going to use the main DB see #3910

Revision 0ffffaf9 (diff)
Added by Julio Montoya over 7 years ago

Adding c_id in some queries see #3910

Revision 395f54b7 (diff)
Added by Julio Montoya over 7 years ago

Removing custom_tabes in install see #3910

Revision 2b951c1e (diff)
Added by Julio Montoya over 7 years ago

Fixing default and missing values for cas_add_user_activate see #3910

Revision a60a1220 (diff)
Added by Julio Montoya over 7 years ago

Removing unused vars cas_add_user_* see #3910

Revision 7bda51c5 (diff)
Added by Julio Montoya over 7 years ago

Adding missing settings see #3910

Revision 98936629 (diff)
Added by Julio Montoya over 7 years ago

Removing double insert see #3910

Revision 68c169a3 (diff)
Added by Julio Montoya over 7 years ago

Adding custom_pages config see #3910

Revision cb31bae0 (diff)
Added by Julio Montoya over 7 years ago

Adding c_id see #3910

History

#1

Updated by Julio Montoya almost 8 years ago

  • % Done changed from 0 to 30

I already did 1 and 2 I'm currently doing 3. which needs a lot of time and patience ...

#2

Updated by Julio Montoya almost 8 years ago

I'm not sending anything to the repo because it will break all your portals

#3

Updated by Julio Montoya almost 8 years ago

By default I'm addding a prefix for the course tables "c_" it's a constant so we can change it if you don't like it.
After talking with Yannick all those "c_" tables will have a field called "c_id" as I said in step 2

#4

Updated by Julio Montoya almost 8 years ago

  • Project changed from Courses to Chamilo LMS
  • SCRUM pts - complexity set to ?
#5

Updated by Julio Montoya almost 8 years ago

  • % Done changed from 30 to 40

Apparently I added this task in the Chamilo2 project I just moved it to 1.x
I'm still fixing inner joins, and left joins in the main/inc/lib files ...

#6

Updated by Julio Montoya almost 8 years ago

  • Subject changed from Use single database database by default (new way - no new table creation by course) to Single course database (in chamilo_main )
#8

Updated by Julio Montoya almost 8 years ago

  • % Done changed from 40 to 70

I just updated chamilodev.beeznest.com with this feature, requires testing

#10

Updated by Julio Montoya almost 8 years ago

  • Target version set to 1.9 Stable
  • Complexity changed from Normal to Difficult
  • SCRUM pts - complexity changed from ? to 40
#11

Updated by Yannick Warnier almost 8 years ago

chamilodev works great so far. It does seem a bit slower though... We should try and set a few benchmark scripts to execute some common chamilo operation with courses and see which is slower...

#12

Updated by Julio Montoya almost 8 years ago

  • Status changed from Assigned to Needs testing
  • Assignee deleted (Julio Montoya)
#13

Updated by Laurent Opprecht over 7 years ago

wondering why we have double key primary key (course id and id) instead of making id the primary key and having it auto incremented ?

The second option being usually more flexible.

#14

Updated by Julio Montoya over 7 years ago

Laurent Opprecht wrote:

wondering why we have double key primary key (course id and id) instead of making id the primary key and having it auto incremented ?

The second option being usually more flexible.

The problem here was the complexity of the migration process, because in different course databases we had the same id.

Example:

In course A:
we had a document with id = 1
We add this document in a learning path so we had in the lp_item.ref = 1

In course B:
We also have a document with id = 1
We add this document in a learning path so we had in the lp_item.ref = 1

We could use the id autonumeric but the migration will be very complex. So right now we're just "moving" data from 1 course database to a single database (we maintain the id = 1). Otherwise will be change document id everywhere in all learning paths for example.

#15

Updated by Yannick Warnier over 7 years ago

Julio Montoya wrote:

We could use the id autonumeric but the migration will be very complex. So right now we're just "moving" data from 1 course database to a single database (we maintain the id = 1). Otherwise will be change document id everywhere in all learning paths for example.

I would personally like to have the very complex migration now and not drag false unique IDs for some more years, but I agree that it would be too dangerous to apply now. We can schedule it for 2.0 (which should come shortly after 1.9).

Just to be on the same side, having one single primary key would imply updating records in:
  • the c_item_property table
  • the c_lp_item table
  • all the stats table that mention resources

...and that should be it. Am I correct?

#16

Updated by Julio Montoya over 7 years ago

all tables that use a "foreign key" must be updated to:

quiz_answer (field question_id)
student_publication (field parent_id)
survey_answer (field survey_id)
...

in fact all tables should be reviewed

Yannick Warnier wrote:

Julio Montoya wrote:

We could use the id autonumeric but the migration will be very complex. So right now we're just "moving" data from 1 course database to a single database (we maintain the id = 1). Otherwise will be change document id everywhere in all learning paths for example.

I would personally like to have the very complex migration now and not drag false unique IDs for some more years, but I agree that it would be too dangerous to apply now. We can schedule it for 2.0 (which should come shortly after 1.9).

Just to be on the same side, having one single primary key would imply updating records in:
  • the c_item_property table
  • the c_lp_item table
  • all the stats table that mention resources

...and that should be it. Am I correct?

#17

Updated by Laurent Opprecht over 7 years ago

Fine since we have several thing to do now.
Now one of the advantage of using Symphony is the ability to autogenerate the code model from the database structure - i.e. scaffolding - but for that we need to have a database as clean as possible.

So I would check if we can go with real unique id before moving to Symphony.

#18

Updated by Yannick Warnier over 7 years ago

  • Target version changed from 1.9 Stable to 1.9 Beta
#19

Updated by Yannick Warnier over 7 years ago

  • Status changed from Needs testing to Assigned
  • Assignee set to Julio Montoya
  • Priority changed from Normal to Urgent
Julio, I won't be able to do it, so before the beta release I need you to re-check the coherency of the database changes since 1.8.8.4 and that they are all in db_main.sql, update-db-1.8.8-1.9.0.inc.php and add_course.lib.inc.php to check if the three following cases generate the same databases:
  • installing a new 1.9 system from scratch and creating one course
  • installing a 1.8.8.4 in multi DB, creating one course and migrating to 1.9
  • installing a 1.8.8.4 in single DB, creating one course and migrating to 1.9

If you can find someone to do that for you, that's fine by me, but reviewing the migration code is a top priority, in any case.

#20

Updated by Yannick Warnier over 7 years ago

There's a strange EnableWebCamClipTitle in my settings_current (without anything in settings_options). It appears at the end of the "tools" section in admin params.

#21

Updated by Julio Montoya over 7 years ago

tabcustom1, etc was removed

#22

Updated by Julio Montoya over 7 years ago

Yannick Warnier wrote:

There's a strange EnableWebCamClipTitle in my settings_current (without anything in settings_options). It appears at the end of the "tools" section in admin params.

I found the error here #4596 and I just fixed

#23

Updated by Julio Montoya over 7 years ago

cas_add_user_activate seems buggy:

dans migrate:

INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES;
INSERT INTO settings_options (variable, value, display_text) values ('cas_add_user_activate', 'true', 'Yes');
INSERT INTO settings_options (variable, value, display_text) values ('cas_add_user_activate', 'false', 'No');

dans db_main

('cas_add_user_activate', NULL, 'radio', 'CAS', '', 'CasUserAddActivateTitle', 'CasUserAddActivateComment', NULL, NULL, 0),
'cas_add_user_activate', 'false', 'No'),
('cas_add_user_activate', 'platform', 'casAddUserActivatePlatform'),
('cas_add_user_activate', 'extldap', 'casAddUserActivateLDAP'),

asking to Hubert

#24

Updated by Julio Montoya over 7 years ago

  • Status changed from Assigned to Needs more info
  • Assignee changed from Julio Montoya to Yannick Warnier

Julio Montoya wrote:

cas_add_user_activate seems buggy:

dans migrate:

INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES;
INSERT INTO settings_options (variable, value, display_text) values ('cas_add_user_activate', 'true', 'Yes');
INSERT INTO settings_options (variable, value, display_text) values ('cas_add_user_activate', 'false', 'No');

dans db_main

('cas_add_user_activate', NULL, 'radio', 'CAS', '', 'CasUserAddActivateTitle', 'CasUserAddActivateComment', NULL, NULL, 0),
'cas_add_user_activate', 'false', 'No'),
('cas_add_user_activate', 'platform', 'casAddUserActivatePlatform'),
('cas_add_user_activate', 'extldap', 'casAddUserActivateLDAP'),

asking to Hubert

I already sent some fixes

The "custom pages" will be enabled? I didn't test it yet ...

#25

Updated by Yannick Warnier over 7 years ago

  • Status changed from Needs more info to Assigned
  • Assignee changed from Yannick Warnier to Julio Montoya

Yes, just leave it disabled in the admin section for now. Even if it doesn't work. We'll just put it so it's easier to implement with a little patch later on.
Once you've made sure it's there but not enabled by default, move this task to 1.9.2 so we can check it at this point.

#26

Updated by Julio Montoya over 7 years ago

  • Status changed from Assigned to Needs more info
  • Assignee changed from Julio Montoya to Yannick Warnier

"custom_pages" added; disabled by default

#27

Updated by Yannick Warnier over 7 years ago

  • Status changed from Needs more info to Needs testing
  • % Done changed from 70 to 90
#28

Updated by Yannick Warnier over 7 years ago

  • Status changed from Needs testing to Feature implemented

Bah, OK, let's close this task and cross our fingers. Let's hope it all gets good between now and the RC1.

Also available in: Atom PDF