Re: Overriding one database's content with another

[Brian Shields <bl...@gmail.com>]

Rick Hillegas <Ri...@...> writes:
> > 1) Using two schemas in one database, how does Derby store the filesystem?
> > Currently, I've got a Data directory, containing a couple files, a log
> > folder, and a seg0 folder with many data files. If the two schemas are
> > stored separately (or in separate subfolders), adding database updates to
> > my program should be trivial. If the data from the two schemas is merged
> > into the same data files, updating the non-user data would be more tricky.
> >
> You'll see one file per table in the seg0 folder. The schemas will not
> share data files.

So I've successfully got two schemas in my database: data and user_data. Both
have 19 tables, and each corresponding table has the same columns, with the
exception that the tables in user_data have a column 'delete_row' at the end.
All 38 tables have a single index. Aside from the two schemas I've created, the
following exist in my database, according to RazorSQL
<http://www.razorsql.com/>:
Schema SQLJ contains 3 procedures: install_jar, remove_jar, and replace_jar.
Schema SYS contains 19 system tables and 42 indices.
Schema SYSCS_UTIL contains 26 procedures.
Schema SYSIBM contains 1 system table (sysdummy1) and 22 procedures.

All told, that's 58 tables (20 are system tables), 51 procedures, and 80
indices. My seg0 folder contains 139 data files, which neither matches the total
number of tables in my database, nor the total number of items (189). So, I'm
not exactly sure which files represent what, and there's no noticeable naming
scheme to the DAT files I can go off of.

Most importantly, I don't want to overwrite the files representing user_data
tables when I push an update, so if worst comes to worst, I could
programatically delete the user_data tables from *my* copy before pushing an
update, and I would be overwriting the data and system
tables/procedures/whatever.

Ideally, I would want to only overwrite the data schema tables, as that would be
a smaller download, but it also means I need a way of identifying which files
correspond to what, but I can't exactly read them, so it's a problem.

In short: Is there any way to identify what data files in the seg0 folder
correspond to what tables, and if so what is it? Or do I just have to suck it up
and lop off my user_data before pushing an update?

Re: Overriding one database's content with another

[Brian Shields <bl...@gmail.com>]

Kristian Waagan <Kr...@...> writes:
> The conglomerate number is represented as an integer in the system 
> tables. To get the corresponding file, convert the integer to 
> hexadecimal, prefix a 'c' and append '.dat'.
> 
> Remember that indexes are stored in their own separate file (also 
> recorded in the system tables).

Thank you, this is exactly what I was looking for!

As for the indices, I don't think they're ever changing, but my plan was to grab
all of the conglomerates corresponding to the data schema, and the indices would
be a part of that. I don't mind 38 vs. 19 files, it was the 139 vs. 19 that was
problematic. I'd rather not needlessly overwrite a bunch of system information
for the database, and I'd rather not have to programatically drop the user_data
tables before pushing out a build of the program just to ensure the users don't
get their settings changed.

Re: Overriding one database's content with another

[Kristian Waagan <Kr...@Sun.COM>]

Brian Shields wrote:
> Bryan Pendleton <bp...@...> writes:
>   
>>> not exactly sure which files represent what, and there's no noticeable naming
>>> scheme to the DAT files I can go off of.
>>>       
>> The naming scheme is based off the conglomerate ID, which can be used to
>> cross-reference back to the system catalogs.
>>
>> This should help you get the hang of figuring out which file goes with
>> which conglomerate, and hence with which table/index:
>> http://db.apache.org/derby/javadoc/engine/org/apache/derby/diag/SpaceTable.html
>>     
>
> The vast majority of the CONGLOMERATEIDs for my tables contain either c16f or
> c20e, and end in c00 or c30. My filenames range from c1a1 to cf0, so unless you
> can tell me how the filenames are generated from the CONGLOMERATEID, I don't see
> how that solves my problem...
Hi Brian,

I think what Bryan meant was the conglomerate number. See in 
SYS.SYSCONGLOMERATES.
You may also want to filter based on the schema id etc, and you have to 
join data from SYS.SYSTABLES if you want to target a specific table/index.
The conglomerate number is represented as an integer in the system 
tables. To get the corresponding file, convert the integer to 
hexadecimal, prefix a 'c' and append '.dat'.

Remember that indexes are stored in their own separate file (also 
recorded in the system tables).
I've never tried to update parts of a database in this way, let us know 
how it works out :)


Regards,
-- 
Kristian

Re: Overriding one database's content with another

[Brian Shields <bl...@gmail.com>]

Bryan Pendleton <bp...@...> writes:
> > not exactly sure which files represent what, and there's no noticeable naming
> > scheme to the DAT files I can go off of.
> 
> The naming scheme is based off the conglomerate ID, which can be used to
> cross-reference back to the system catalogs.
> 
> This should help you get the hang of figuring out which file goes with
> which conglomerate, and hence with which table/index:
> http://db.apache.org/derby/javadoc/engine/org/apache/derby/diag/SpaceTable.html

The vast majority of the CONGLOMERATEIDs for my tables contain either c16f or
c20e, and end in c00 or c30. My filenames range from c1a1 to cf0, so unless you
can tell me how the filenames are generated from the CONGLOMERATEID, I don't see
how that solves my problem...?

Re: Overriding one database's content with another

[Bryan Pendleton <bp...@amberpoint.com>]

> not exactly sure which files represent what, and there's no noticeable naming
> scheme to the DAT files I can go off of.

The naming scheme is based off the conglomerate ID, which can be used to
cross-reference back to the system catalogs.

This should help you get the hang of figuring out which file goes with
which conglomerate, and hence with which table/index:
http://db.apache.org/derby/javadoc/engine/org/apache/derby/diag/SpaceTable.html

thanks,

bryan