java - 如何在使用 SQLite 操作期间添加行？

Question

这是我的第一个 Android/sql 项目，我正在创建一个带有两个数据库的考勤应用程序。 DatabaseHelper 有一张包含学生/教授列表的表格。另一个名为 coursesDatabase 的数据库有一个表，用于存储类(class)名称及其信息。我遇到的问题是我想为每个记录该特定类(class)出勤率的类(class)制作表格，从 COL1=STUDENT_NAME 开始，然后添加每个 COL2=LAST_NAME、COL3=DATE1、COL4=DATE2 等。 . 每次教授创建出勤部分时都会创建每个日期列。这是我的代码，不包括不相关的已完成方法:

public class CoursesDatabase extends SQLiteOpenHelper {
private static CoursesDatabase cd = null;

public static final int DATABASE_VERSION = 2;
public static final String DATABASE_NAME = "course_manager.db";
public static final String COURSES_TABLE_NAME = "COURSES";
public static final String KEY_ID = "ID";                         //column index 0
public static final String KEY_NAME= "COURSE_NAME";               //column index 1
public static final String KEY_CODE = "COURSE_CODE";              //column index 2
public static final String KEY_HOUR = "COURSE_HOUR";              //column index 3
public static final String KEY_MINUTE = "COURSE_MINUTE";          //column index 4
public static final String KEY_INSTRUCTOR = "COURSE_INSTRUCTOR";  //column index 5
public static final String IS_ON = "IS_ON";                       //column index 6

private CoursesDatabase(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    deleteAll();
    addCourse(new Course("354", "Software Engineering", "12345", 10, 30, "doctor doc"));
    addCourse(new Course("237", "CompSci 2", "13346", 11, 0, "john doe"));
}

public static CoursesDatabase getInstance(Context context){
    if (cd == null){
        cd = new CoursesDatabase(context);
    }
    return cd;
}

@Override
public void onCreate(SQLiteDatabase cd) {
    final String SQL_CREATE_USER_TABLE = "CREATE TABLE " + COURSES_TABLE_NAME + "(" +
            KEY_ID          +   " TEXT NOT NULL, "  +   // column 0
            KEY_NAME        +   " TEXT NOT NULL, "  +   // column 1
            KEY_CODE        +   " TEXT NOT NULL, "  +   // column 2
            KEY_HOUR        +   " INT, "            +   // column 3
            KEY_MINUTE      +   " INT, "            +   // column 4
            KEY_INSTRUCTOR  +   " TEXT,"            +   // column 5
            IS_ON           +   "INT DEFAULT 0);";      // column 6

    cd.execSQL(SQL_CREATE_USER_TABLE);
}

//Creates a table to log attendance for each class
String KEY_STUDENT_FIRST_NAME = "First_Name";
String KEY_STUDENT_LAST_NAME = "Last_Name";
public void newClassTable(SQLiteDatabase cd, Course course){
    String CLASS_TABLE_NAME = course.getId() + "-" + course.getName() + "-" + course.getHour() + ":" + course.getMinute();

    final String SQL_CREATE_CLASS_TABLE = "CREATE TABLE " + CLASS_TABLE_NAME + "(" +
            KEY_STUDENT_FIRST_NAME     +   " STRING NOT NULL, " +  // column 0
            KEY_STUDENT_LAST_NAME      +   " STRING NOT NULL);";   // column 1
    cd.execSQL(SQL_CREATE_CLASS_TABLE);
}

//takes the attendance of a student in course table. If the student does not exist in the course table, the student is then added.
public boolean attendStudentInCourse(Student student, Course course) {
    SQLiteDatabase cd = this.getWritableDatabase();

    //if professor has not turned on the course yet exit
    if (!course.isAvailable()) {
        return false;
    }

    return true;

}

所以我的问题是，当教授点击该类(class)的“新类(class)”时，我应该使用什么方法在类(class)表的末尾创建一个新列？当学生尝试 attendStudentInCourse() 方法时，我如何能够更新特定的最后一行？

Answer 1

So my question is what methods do I use in order to create a new column at the end of the class table when the professor clicks on "new session" for that class?

您可以使用

向表中添加一列(在某些条件下)

ALTER TABLE <your_table_name> ADD COLUMN <you_column_definition(e)>

• 请注意，您将使用 execSQL 方法，因为很少会使用 ALTER，因此没有方便的方法。

SQL As Understood By SQLite - ALTER TABLE 阅读此内容了解条件/限制。

And how would I be able to update that specific last row when a student attempts the attendStudentInCourse() method?

假设您指的是列而不是行

注意到您可以使用 DEFAULT <default_value>设置将应用于所有现有行的默认值。

如果设置默认值(我猜测新 session 最初在类(class)中有 0 个学生)不适合，那么您可以使用 UPDATE 查询。

如果你的意思是行那么:-

但是，添加列不会添加行，添加的列将应用于所有行。

---

总而言之，设计可能会改进为:-

• a) 拥有一个利用关系的单一数据库，因为 SQLite 是一种关系数据库管理系统 (RDMS)，旨在很好地处理表之间的关系。
• b) 建立关系以排除列中不同数量的列或列表的复杂性(添加列并因此具有难以阅读的动态架构)。
• c) 标准化一些因素，例如讲师，因为这似乎必须每行输入并且很可能会重复并且效率低下。
• 例如 Ihavea Longname 教授每次存储时都需要超过 25 个字节，并且在对该行进行搜索/比较时可能需要检查多达 25 个字节。
  • 如果您有一个 instructor 表，那么您只需要存储对 instructor 的引用(rowid 的别名很可能是非常适合引用的列，这就是通常隐藏的 rowid 列存在的原因)，那么 25_ 字节将只存储一次，而引用(最多约 8 个字节(如果您有数十亿名讲师)大约 1 个字节，最多 127 个讲师)将仅存储节省空间，减少 IO，增加可以缓存的内容.

示例(使用原始 SQL)

作为一个例子，也许考虑以下使用:-

• role角色表(教师学生)(即一个人有一个角色)所以有
• person 表，用于所有人员(他们的关系(一个角色对许多人)决定他们做什么(指导(指导者)，被教导(学生)，帮助指导(指导者援助)或管理)
  • 如果需要，可以动态添加新角色
• 一个类(class)表，其中包含有关类(class)、名称、代码时间的静态信息
• 和attendance表是一个映射表，存储所有考勤信息(所有考勤登记在一个)。

-- 请注意具有多个表的单个数据库，这些表具有关系映射也称为引用

-- 请注意具有多个表的单个数据库，这些表具有关系映射也称为引用

DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS attendance;
DROP VIEW IF EXISTS proposed_attendance_list;
DROP VIEW IF EXISTS non_attendance_list;

-- Create the role table
CREATE TABLE IF NOT EXISTS role (_id INTEGER PRIMARY KEY, role);
-- Add some roles
-- NOTE _id column is not specified so SQLite will assign a unqiue value 
-- assigned value will likely be 1, then likely 2, then likely 3........9,223,372,036,854,775,807 (highest)
-- NOTE no need for AUTOINCREMENT keyword and the resultant performance loss
-- NOTE _id is the column name that best suits Android (e.g. Cursor Adapters need this column name)
INSERT INTO ROLE (role) VALUES 
    ('Instructor'), -- id 1
    ('Student'), -- id 2
    ('Instructor Aid '), -- id 3
    ('Administrator') -- id 4
    -- !!!NOTE!!! you would not typically assume specific id
;

-- Create the Person table (Person will have a role - for simplicity of demo single role assumed)
CREATE TABLE IF NOT EXISTS person (_id INTEGER PRIMARY KEY, name TEXT, role INTEGER);

INSERT INTO person (name,role) VALUES
    ('Professor Plum',1), -- Instructor id = 1
    ('Tom Brown',2), -- Student id = 2
    ('Mary Smith',2), -- Student  id = 3
    ('Sue Barnes',2), -- Student id = 4
    ('Ty Pit',4), -- Admin id = 5
    ('Dr. J M Hardy',1), -- Instructor id = 6
    ('Matilda Dance',2), -- Student id = 7
    ('Fred Bloggs',3) -- Aid id = 8

;

CREATE TABLE IF NOT EXISTS course (
    _id INTEGER PRIMARY KEY, 
    course_name TEXT NOT NULL, 
    course_code TEXT NOT NULL, 
    course_hour TEXT NOT NULL, 
    course_minute TEXT, 
    course_time, 
    course_instructor INTEGER
);
INSERT INTO course (course_name, course_code, course_hour, course_minute, course_instructor) VALUES
    ('MATHS BASIC','M001','10','30',6), -- Maths with instructor Dr. J M Hardy id = 1
  ('MATHS APPLIED', 'M101','12','30',6), -- Applied Maths instructor Dr. J M Hardy id =2 
  ('MATHS ADVANCED','M011','11','00',1) -- Advanced Maths instructor Professor Plum id =3
;

CREATE TABLE IF NOT EXISTS attendance (course_reference INTEGER, person_reference INTEGER, attended INTEGER, PRIMARY KEY (course_reference, person_reference));
INSERT INTO attendance VALUES
    (1,6,0), -- Dr J M Hardy should attend (attended 0 = should attend, -1 if did not attend, 1 if did attend) course MATCHS BASIC
    (1,3,0), -- Mary Should attent Basic maths
    (1,7,0), -- Matilda should attend basic maths
    (1,8,0), -- Aid Fred should attend basic maths
    (3,1,1), -- Advanced maths Plum attended (taught it)
    (3,2,-1), -- Advanced maths Tom Brown missed
    (3,3,1), -- Advanced maths mary attended
    (3,8,1), -- Aid Fred attended
    (2,4,1), -- Applied Maths Sue attended
    -- (2,4,0) WOULD BE DUPLICATE SO NOT ALLOWED (INSERT OR IGNORE would skip, Note android SQliteDatabase insert method uses INSERT OR IGNORE)
    (2,3,0), -- Mary didn't attend as such but not flagged as did not attend (could just use 0 to indicate did not attend)
    (2,7,1), -- Applied Maths Matilda attended
    (2,6,0), -- Dr Hardy should attend
    (2,1,0), -- Prof Plum should attend
    (2,8,0) -- Fred Bloggs Admin should attend
;

-- Course attendace example listing the course details, the number of attendees and the list of attendees
-- Create as a view so that it can be resued (only need to type it out once)
CREATE VIEW IF NOT EXISTS proposed_attendance_list AS 
    SELECT 
        course.course_name, 
        course.course_code, 
        course.course_hour||':'||course.course_minute AS CourseTime, 
        count(attended) AS proposed_attendance, 
        group_concat(Person.name,' ,') AS peeople 
    FROM Course 
        JOIN attendance ON course._id = attendance.course_reference
        JOIN person ON attendance.person_reference = person._id
        JOIN role ON person.role = role._id
    -- GROUP BY course_hour, course_minute
    -- HAVING
    GROUP BY course_code
    ORDER BY course.course_code,role._id
;
-- Initial proposed course attendance
SELECT * FROM proposed_attendance_list;
-- Change some course codes
UPDATE course SET course_code = 'M201' WHERE course_code = 'M101';
UPDATE course SET course_code = 'M111' WHERE course_code = 'M011';
-- Modifed proposed course attendance
SELECT * FROM proposed_attendance_list;

• 注意评论(-- )

结果

1。最初提议的类(class)出勤率

2。类(class)名称更改后

关于更改出勤

先换个角度写一封关于旷课的信作为demo

CREATE VIEW IF NOT EXISTS non_attendance_list AS
    SELECT course_name, 
        course_code, 
        Person.name, 
        Person.role, 
        'Dear '||person.name||' our records show that you did''t attend Course '||course_code||' '||course_name||' rest of letter' AS letter_to_send
    FROM attendance
        JOIN course ON course_reference = course._id
        JOIN Person ON person_reference = Person._id
        JOIN Role ON Person.role = role._id
    WHERE attendance.attended < 1
    ORDER BY course_code
;

-- Initial letters
SELECT letter_to_send FROM non_attendance_list;

结果

更改出勤示例

-- Set Mary as having attended M001 Basic maths
UPDATE attendance SET attended = 1 
    WHERE course_reference = (SELECT _id FROM course WHERE course_code = 
        'M001' -- value that would be changed to suit
    )
        AND person_reference = (SELECT _id FROM person WHERE name =
        'Mary Smith' -- value that would be changed to suit
        )
;
-- Letters after update
SELECT letter_to_send FROM non_attendance_list;

结果(没有给玛丽的信 M001)