如何使动态列使用Android SQLite数据库 [英] How to make dynamic column using Android SQLite Database

问题描述

使用SQLite数据库Android上的即时通讯书面申请和IM。
我希望能够由用户选择列添加到我的餐桌。
这样用户就可以补充说，他希望到餐桌的任何列。例如，用户有动物的表，他想为狗，猫和鱼。

Im writing application on Android and im using SQlite database. I want to be able to add columns to my table by the user choice. so the user can add any column that he wants the to table. For example the user have "animal" table and he want to add column for "dog", "cat" and "fish".

我看过一些解决方案和我没有看见一个人，可以帮助我。
我读了简单的方式来增加列使用：

I have read about some solutions and i didnt saw one that can help me. I read that the simple way to add column is using:

        @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // If you need to add a column
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + ColumnName);
        }
}

但我的这个，我不能选择什么是将用户最佳的选择被添加到表中的列名的问题，对于字符串没有参数。
所以我试图用这样的事情，并直接调用它。

But my problem with this that i cant choose what is the name of the column that will be added to the table by the user choise, there is no parameter for string. So i tried using something like this, and to call it directly.

        @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion, String newColumnName) {

        // If you need to add a column
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + newColumnName);
        }
}

但我得到的错误此方法。

But i got error for this method.

我对数据库版本的另一个问题。
该onUpgrade方法得到全自动调用时的onCreate被调用。
在onUpgrade有数据库版本oldVersion和静态网页参数。我什么时候设置oldVersion和静态网页参数？我如何我NEWVERSION参数设置为2,3,4 ...？

I have another question about the database version. The onUpgrade method get automaticly called when onCreate get called. In onUpgrade there is oldVersion and newVersion parameters for the database version. when do i set the oldVersion and newVersion parameters? How i set my newVersion parameter to 2,3,4...?

推荐答案

您可以创建一个辅助表来保存额外的列数据。查询到你的主表可以转换成上查询一个新的视角。

You can create an auxiliary table to hold the extra column data. Queries to your primary table can be converted into queries on a new view.

create table if not exists animal (pk integer primary key, name);

create table if not exists animal_aux (animal_pk, col_name, col_val);

create view if not exists animal_view
    as select animal.name as name,
              ct.col_val as cat,
              dt.col_val as dog
        from animal, animal_aux as ct, animal_aux as dt
        where animal.pk = ct.animal_pk
          and animal.pk = dt.animal_pk
          and ct.col_name = 'cat'
          and dt.col_name = 'dog'
;

此架构应得到加强，以使 animal_pk，COL_NAME 主键，或者至少唯一在 animal_aux 。您可能还需要触发添加或辅助表中删除条目时插入或在动物删除条目表。

This schema should be enhanced to make animal_pk, col_name a primary key, or at least unique in animal_aux. You may also need triggers to add or remove entries in the aux table when you insert or delete entries in the animal table.

例如：

sqlite> select * from animal_view;
sqlite> insert into animal values (NULL, 'fred');
sqlite> select * from animal_view;
sqlite> select * from animal;
1|fred
sqlite> insert into animal_aux values (1, "dog", "beagle");
sqlite> insert into animal_aux values (1, "cat", "siamese");
sqlite> select * from animal_view;
fred|siamese|beagle
sqlite> 

每次添加一个虚拟列，你需要

Each time you add a virtual column, you'll need to

drop view animal_view;

，然后用适当的额​​外的列重新创建和，其中条款。

这篇关于如何使动态列使用Android SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！