Saving a dababase


    Jun 20 2011 | 10:58 pm
    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

    • Jun 21 2011 | 6:06 pm
      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
    • Jun 21 2011 | 6:40 pm
      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
    • Jun 23 2011 | 5:51 pm
      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
    • Jun 23 2011 | 6:44 pm
      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
    • Oct 09 2015 | 3:21 pm
      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
    • Oct 09 2015 | 3:37 pm
      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
    • Oct 09 2015 | 4:07 pm
      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
    • Oct 16 2015 | 8:05 pm
      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
      }