sqlite 데이터베이스에서 열을 삭제하거나 추가하고 싶습니다
다음 쿼리를 사용하여 열을 삭제하고 있습니다.
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME그러나 그것은 오류를 준다
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error답변
SQLite는 ALTER TABLE의 제한된 하위 집합을 지원합니다. SQLite의 ALTER TABLE 명령을 사용하면 테이블 이름을 바꾸거나 기존 테이블에 새 열을 추가 할 수 있습니다. 열 이름을 바꾸거나 열을 제거하거나 테이블에서 제약 조건을 추가하거나 제거 할 수 없습니다.
당신은 할 수 있습니다 :
- 변경하려는 테이블로 새 테이블을 만들고
- 모든 데이터를 복사
- 오래된 테이블을 떨어 뜨리고
- 새 이름을 바꿉니다.
답변
Sqlite의 권장 방법에 따라 Java 구현을 작성했습니다.
private void dropColumn(SQLiteDatabase db,
ConnectionSource connectionSource,
String createTableCmd,
String tableName,
String[] colsToRemove) throws java.sql.SQLException {
List<String> updatedTableColumns = getTableColumns(tableName);
// Remove the columns we don't want anymore from the table's list of columns
updatedTableColumns.removeAll(Arrays.asList(colsToRemove));
String columnsSeperated = TextUtils.join(",", updatedTableColumns);
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
// Creating the table on its new format (no redundant columns)
db.execSQL(createTableCmd);
// Populating the table with the data
db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
+ columnsSeperated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
}테이블의 열을 얻기 위해 “PRAGMA table_info”를 사용했습니다.
public List<String> getTableColumns(String tableName) {
ArrayList<String> columns = new ArrayList<String>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = getDB().rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}나는 실제로 내 블로그에 그것에 대해 썼습니다. 더 많은 설명을 볼 수 있습니다 :
http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/
답변
다른 사람들이 지적했듯이
열 이름을 바꾸거나 열을 제거하거나 테이블에서 제약 조건을 추가하거나 제거 할 수 없습니다.
출처 : http://www.sqlite.org/lang_altertable.html
항상 새 테이블을 만든 다음 이전 테이블을 삭제할 수 있습니다. 이 해결 방법 을 예를 들어 설명하려고합니다 .
sqlite> .schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
height INTEGER
);
sqlite> select * from person ;
id first_name last_name age height
---------- ---------- ---------- ---------- ----------
0 john doe 20 170
1 foo bar 25 171 이제이 height테이블 에서 열을 제거하려고 합니다.
라는 다른 테이블을 만듭니다. new_person
sqlite> CREATE TABLE new_person(
...> id INTEGER PRIMARY KEY,
...> first_name TEXT,
...> last_name TEXT,
...> age INTEGER
...> ) ;
sqlite> 이제 이전 테이블의 데이터를 복사하십시오.
sqlite> INSERT INTO new_person
...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id first_name last_name age
---------- ---------- ---------- ----------
0 john doe 20
1 foo bar 25
sqlite>이제 person테이블을 삭제 하고 이름 new_person을 바꿉니다 .person
sqlite> DROP TABLE IF EXISTS person ;
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>그래서 지금 당신이 할 경우 .schema, 당신은 볼 것이다
sqlite>.schema
CREATE TABLE "person"(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER
);답변
http://www.sqlite.org/lang_altertable.html
다이어그램에서 볼 수 있듯이 ADD COLUMN 만 지원됩니다. 그러나 (kinda heavy) 해결 방법이 있습니다 : http://www.sqlite.org/faq.html#q11
답변
SQLite 3에서 특정 열을 삭제할 수 없습니다 . FAQ를 참조하십시오 .
답변
다른 사람들이 지적했듯이, sqlite의 ALTER TABLE진술은 을 지원 하지 않으며DROP COLUMN , 이것을 수행하는 표준 레시피는 제약 및 지수를 보존하지 않습니다.
다음 은 모든 주요 제약 조건과 색인 을 유지 하면서 일반적 으로이 작업을 수행하는 Python 코드 입니다.
사용하기 전에 데이터베이스를 백업하십시오! 이 함수는 원래 CREATE TABLE 문을 의사 결정하는 데 약간 위험하며 잠재적으로 약간 안전하지 않습니다. 예를 들어 식별자에 쉼표 또는 괄호가 포함되어 있으면 잘못된 작업이 수행됩니다.
누구든지 SQL을 구문 분석하는 더 나은 방법을 제공하고자한다면 좋을 것입니다!
업데이트 오픈 소스sqlparse패키지를사용하여 구문 분석하는 더 좋은 방법을 찾았습니다. 관심이 있으시면 여기에 게시 할 것입니다.
import re
import random
def DROP_COLUMN(db, table, column):
columns = [ c[1] for c in db.execute("PRAGMA table_info(%s)" % table) ]
columns = [ c for c in columns if c != column ]
sql = db.execute("SELECT sql from sqlite_master where name = '%s'"
% table).fetchone()[0]
sql = format(sql)
lines = sql.splitlines()
findcol = r'\b%s\b' % column
keeplines = [ line for line in lines if not re.search(findcol, line) ]
create = '\n'.join(keeplines)
create = re.sub(r',(\s*\))', r'\1', create)
temp = 'tmp%d' % random.randint(1e8, 1e9)
db.execute("ALTER TABLE %(old)s RENAME TO %(new)s" % {
'old': table, 'new': temp })
db.execute(create)
db.execute("""
INSERT INTO %(new)s ( %(columns)s )
SELECT %(columns)s FROM %(old)s
""" % {
'old': temp,
'new': table,
'columns': ', '.join(columns)
})
db.execute("DROP TABLE %s" % temp)
def format(sql):
sql = sql.replace(",", ",\n")
sql = sql.replace("(", "(\n")
sql = sql.replace(")", "\n)")
return sql답변
코드가 테이블 생성 쿼리를 자동으로 생성하도록 @Udinic 응답을 다시 작성했습니다 . 또한 필요하지 않습니다 . 또한 트랜잭션 내에서이 작업을 수행해야 합니다 .ConnectionSource
public static String getOneTableDbSchema(SQLiteDatabase db, String tableName) {
Cursor c = db.rawQuery(
"SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '" + tableName + "'", null);
String result = null;
if (c.moveToFirst()) {
result = c.getString(c.getColumnIndex("sql"));
}
c.close();
return result;
}
public List<String> getTableColumns(SQLiteDatabase db, String tableName) {
ArrayList<String> columns = new ArrayList<>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = db.rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}
private void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) {
db.beginTransaction();
try {
List<String> columnNamesWithoutRemovedOnes = getTableColumns(db, tableName);
// Remove the columns we don't want anymore from the table's list of columns
columnNamesWithoutRemovedOnes.removeAll(Arrays.asList(columnsToRemove));
String newColumnNamesSeparated = TextUtils.join(" , ", columnNamesWithoutRemovedOnes);
String sql = getOneTableDbSchema(db, tableName);
// Extract the SQL query that contains only columns
String oldColumnsSql = sql.substring(sql.indexOf("(")+1, sql.lastIndexOf(")"));
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
db.execSQL("CREATE TABLE `" + tableName + "` (" + getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove)+ ");");
db.execSQL("INSERT INTO " + tableName + "(" + newColumnNamesSeparated + ") SELECT " + newColumnNamesSeparated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
db.setTransactionSuccessful();
} catch {
//Error in between database transaction
} finally {
db.endTransaction();
}
}