Saving a dababase
Hello,
I'm currently working with databases following dbviewer & dbcuelist examples from the sdk.
I'm trying to save datas from the database to a file.
And before implementing my own read/write methods using "SELECT *" queries, I'm wondering if there isn't an easier option.
I can see that db_open from ext_database.c has an option to read from a file on disk but can't manage to make it work.
An empty file only rejects any table creation/modification.
I also tried to discover methods from the sqlite object (using its o_messlist) but couldn't find something usefull.
It seems there's a "create_backup" method but I'm not sure what it is doing and how to use it.
Any idea ?
Thanks
Léo
Hi Léo,
Unless you've set the attribute to make the database a memory-resident database, then the database is automatically saved to disk and updated after each transaction. No manual saving is required.
Cheers,
Tim
Hi Tim,
I've followed the dbcuelist example, so I'm using "db_open".
Right now I'm passing NULL as second argument in "db_open", which, if I understand it correctly in "ext_database.c", forces the database to be "rambased".
I've tried entering a filename instead, I've tried with an existing file and a non-existing file. But then all queries return errors, so I don't understand what I'm doing wrong.
Thanks
Léo
In audiounit~ there is a cache of plug-in information stored in a SQLite database in the preferences folder. Here is the code used to create/open that database:
short au_db_path = 0;
char au_db_pathstr[MAX_PATH_CHARS];
t_max_err err = MAX_ERR_NONE;
t_db_result *result = NULL;
// Open the cache of AU info (if it exists) from the last time or create it anew
err = preferences_path("", true, &au_db_path);
if (!err)
path_topotentialname(au_db_path, "aucache.db3", au_db_pathstr, 0);
else
strncpy_zero(au_db_pathstr, "aucache.db3", MAX_PATH_CHARS);
err = db_open(ps___max_audiounit_db__, au_db_pathstr, &s_db);
if (err)
error("audiounit~ could not open cache from %s", au_db_pathstr);
Hope this helps,
Tim
Thanks Tim,
I'll take a look.
Anyway, I need to be able to read and write different files for my system (something like a complicated preset system).
I only use the database for the powerful data manipulation SQL it provides.
So I implemented my own read/write methods.
Thanks
Léo
Four years later, I have the very same issue: I don't seem to be able to save a database on my hard drive.
Especially, what bugs me is that in SQlite C api creation function, if I give as database file path a non-existing file, such file is created, whereas this doesn't seem to be the case with db_open().
Timothy, do you have some other suggestion? I was hoping in some undocumented db_write_to_file() function...
Léo: may I ask you how did you implement the read/write methods? The sqlite ".dump" message doesn't seem to work for me...
Daniele
Hi Daniele,
I don't have my computer right now, but if I remember correctly, I just parse my tables using "select *" queries and write the result to a file using standard read/write methods. Pretty inefficient for sure but it gives me the opportunity to edit the file in a standard file editor afterwards.
I can send you some parts of code later if you want.
Hope this helps.
Léo
Thanks Léo for your quick answer - that'd be great!
I admit that I was hoping of having db_open() to create and update the file... Let's see if Timothy has some suggestions...
Daniele
Hi Daniele,
sorry for the delay busy week.
my save method looks like that
hope this helps
léo
void luce_db_dosave(t_luce_db *x)
{
string fn = string(x->d_filename->s_name);
#ifndef WIN_VERSION
short pp = fn.find(':');
if(pp != -1)
{
string volname = fn.substr(0,pp);
string filename = fn.substr(pp+1);
fn = "/Volumes/"+volname+"/"+filename;
}
#endif
if((short)fn.find(".luceshow") == -1)
{
fn += ".luceshow";
}
x->d_filename = gensym(fn.c_str());
cpost("DB \"%s\" - Saving \"%s\"\n", x->d_name->s_name, x->d_filename->s_name);
if(!x->d_db)
return;
#ifdef WIN_VERSION
stringstream ofs(ios::out);
#else
ofstream ofs(fn.c_str(), ios::out);
if(!ofs)
return;
#endif
t_max_err err = MAX_ERR_NONE;
t_db_result *result = NULL;
long numrecords, numfields;
int i, j;
err = db_query(x->d_db, &result, "SELECT * FROM cues ORDER BY cue_id");
if(err)
object_error((t_object *)x, "error while reading table cues");
else {
numrecords = db_result_numrecords(result);
numfields = db_result_numfields(result);
for(i = 0 ; i < numrecords ; i++)
{
ofs << "cue ";
for(j = 0 ; j < numfields ; j++)
{
ofs << db_result_string(result, i, j);
if(j < numfields - 1)
ofs << ' ';
}
ofs << endl;
}
}
object_free(result);
#ifdef WIN_VERSION
char ps[MAX_PATH_CHARS];
char fin[MAX_PATH_CHARS];
short vol;
short err3;
t_max_err err2;
t_filehandle fh;
t_ptr_size size;
char * txt;
std::strcpy(ps,x->d_filename->s_name);
path_frompotentialpathname(ps, &vol, fin);
err3 = path_createsysfile(fin,vol,FOUR_CHAR_CODE('TEXT'),&fh);
if (err3) {
object_error((t_object *)x, "%s: error %d creating file",fin,err3);
return;
}
size = ofs.str().size();
txt = sysmem_newptr(size*sizeof(char));
strcpy(txt,ofs.str().c_str());
err2 = sysfile_write(fh, &size, txt);
if (err2)
object_error((t_object *)x,"Error writing to file: %d", err2);
sysfile_getpos(fh, &size);
sysfile_seteof(fh, size);
if (err2 = sysfile_close(fh)) {
object_error((t_object *)x,"Error closing file: %d", err2);
}
sysmem_freeptr(txt);
#else
ofs.close();
#endif
}