Well. After searching a lot here's the way todo a "RENAME" for a table. It's a bit tricky...
Renaming table in ESP SQLite is very interesting because SQLite has very limited features for editing table. So you can't add field, remove and so on. The only way is to create a new tmp table, copy the content of the orginal one into this tmp table, drop the original one and rename the tmp with the name of the new one.
DROP TABLE is OK with the ESP version of SQLite, but as said in the question, ALTER RENAME don't work.
So here is the way to do:
1-What tables do we have?
When we init the ESP, we call
sqlite3_initialize();
and then we give SQLite the file it will use for saving data with a call to sqlite3_open() giving the name of a previously created file.
All our own tables will be saved into this file but also definitions of our tables. For that, SQLite create system table. So in the database we have our own tables but also the SQLlite system tables
2 - The data base schema table
One of the most important table is the database schema one. Its name depend on the version of SQLite. With the version we use on ESP8266 or ESP32, its name is sqlite_master .
This table has 4 fields:
- type. Value can be 'table', 'index', 'view', or 'trigger'
- name. Value is the name of the data. If type='table', name is the name of
the table defined in this record
- tbl_name. Name of the table. So if type='name', tbl_name = name
- rootpage. An int which is the index in the Btree used by SQLite to
access the data
- sql. The code to create the table
3-Check the schema
To check the content of this specific table, just make a CREATE TABLE then SELECT * FROM sqlite_master
Here is what I've done and the result:
The CREATE TABLE CALL:
create TABLE IF NOT EXISTS TAB_JOUEUR ('tjoueur_id' INTEGER,"tjoueur_prenom" TEXT , "tjoueur_nom" TEXT, "tjoueur_ville" TEXT , PRIMARY KEY("tjoueur_id" AUTOINCREMENT))
The result of the SELECT * FROM sqlite_master
- type = table
- name = TAB_JOUEUR
- tbl_name = TAB_JOUEUR
- rootpage = 2
- sql = CREATE TABLE TAB_JOUEUR ('tjoueur_id'
INTEGER,"tjoueur_prenom" TEXT , "tjoueur_nom" TEXT,"tjoueur_ville" TEXT , PRIMARY KEY("tjoueur_id" AUTOINCREMENT))
and then
- type = table
- name = sqlite_sequence
- tbl_name = sqlite_sequence
- rootpage = 3
- sql = CREATE TABLE sqlite_sequence(name,seq)
As you can see the sql field relative to the table "TAB_JOUEUR" as the CREATE call but it's not a pure copy of the original CREATE call. The original one as "create" in lower case, had extra spaces and has a "IF NOT EXISTS" which we don't have in the sql field of sqlite_master. But the part between the () is exactly the same (single quote for tjoueur_id, double for tjoueur_nom, extra spaces...)
4 - The trick
In order to rename a table, you just have to make a SELECT on sqlite_master in order to get the record of the table you want to rename. Then, change the content of the sql field to replace the "old name" by the "new one", and perform an update, using the rootpage as id.
So like that:
void alter_table(String old_name, String new_name){
int rc ;
String data_rootpage;
String data_sql;
int pos_start;
char *err_msg = 0;
String query_sql = "SELECT rootpage,sql FROM sqlite_master WHERE type='table' AND name = '"+old_name+"'";
const char* sql = query_sql.c_str();
rc = sqlite3_prepare_v2(db_handle, sql, -1, &stmt, NULL);
rc = sqlite3_step(stmt);
data_rootpage = (const char *)sqlite3_column_text(stmt, 0);
data_sql = (const char *)sqlite3_column_text(stmt, 1);
// Find first ( in the string
pos_start = data_sql.indexOf ('(');
String tmp = "CREATE TABLE "+new_name+" "+data_sql.substring(pos_start);
query_sql = "UPDATE sqlite_master SET sql='"+tmp+"', name='"+new_name+"', tbl_name='"+new_name+"' WHERE rootpage = "+data_rootpage;
sql = query_sql.c_str();
rc = sqlite3_exec(db_handle, sql, 0, 0, &err_msg);
sqlite3_close(db_handle);
sqlite3_initialize();
sql_connect("/littlefs/transcom.db", &db_handle);}
It's basic and don't have test (you must add some!). I start by searching for the record concerning the "old_name" table. In the sql field I search for the "(" and then I replace the first part of the sql field data with the new name of the table.
Then I update. Notice I don't escape the quote because I've previously make a CREATE call without any ' or " (easier!)
After that, I perform a sqlite3_close() then re-open with sqlite3_initialize(); If you don't do that, SQLite don't take change in account (maybe there is a cache for data).
5 - Great but... not enought!
If you try this, you'll get an error on the update call. That's because for you, "user", sqlite_master is a read only table. So you can't update it. In order to prevent writing on some table (like the sqlite_master one), when SQLite create it, it sets a "read only" flag. The trick is to edit the source code in order to give to this flag, the value of "read and write".
In the sqlite3.c of the LIB, it's a line 17420. You'll see:
#define TF_Readonly 0x0001 /* Read-only system table */
Just change it by
#define TF_Readonly 0x0000 /* Read-only system table */
So when SQLite will create a system table, it will set a read+write flag and so, you would be able to update the sqlite_master table. You need to compile again de lib (on Arduino IDE, just select a wrong board, try to compile, get an error, select ESP32 and compile again and the IDE will perform a full compilation of the lib)
If you have trigger, and some other specific feature, maybe it would be nice to check the result of the select * from sqlite_master in order to see if some records need also to be updated. But for basic table, this will do the job.
Hope this will help.
Edit
If you add an index (or more than one) to your table, you'll have also to update 'index" records from sqlite_master. They are like that:
- type = index
- name = index_ttest_nom
- tbl_name = TAB_TEST
- rootpage = 6
- sql = CREATE UNIQUE INDEX index_ttest_nom ON TAB_TEST (ttest_nom)
So just update the tbl_name value giving the new name of the table, and update the sql field the same way.
