Léopold Frey

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.

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.

saving-a-dababase

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.

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.

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.

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);


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);


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.

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.

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...

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...

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.

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...

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...

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
}



sorry for the delay busy week.
my save method looks like that

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
}



Saving a dababase