database limitations and compatibility

[bertram]

I need to do some internal number crunching within a flat database which is 315,000 records x 40 fields (315000 lines by 330 chars as a fortran textfile). Calc goes to 64k lines (just use more columns), quattropro to 1M, but the unwieldy memory loads are a waste of time, since I have a slow computer and I don't need to have all the data instantly available for all of the objects all the time -- my queries will be pretty much, What does the distribution (report and chart) of the accumulated geometric and/or harmonic relationships between a particular field of a given line (focus) and other fields of other lines look like?

I'm a newbie, but it seems like OOo_Base might be more suitable than Calc, and that this project might fit nicely with the science-oriented origins of the OpenOffice package?

Can Base handle a db with say 500k records?

Where can I find more about the design boundaries of OOo_Base? -- Max tables, and subtables (I don't know what a subtable is?), in a base? Max records in a table? Max fields in a record? Max len of a record? Other design limitations, of tables and records? (I must have poked inefficiently on this question, as my poking was to no avail. My searches returned various examples, and some very helpful esp on this forum, but they are oriented for tables with say x-100 lines.)

Also, where can I find more on the limiitations of field data types as treated by OOo_Base? (the Base help file has no information on the boundaries of the available field types per the OOo_Base Table Design applet

[DrewJensen]

whew..

Always start, at the source for info...hsqldb is the embedded database driver

http://hsqldb.org/web/hsqlDocsFrame.html

is the on-line users manual. It covers just about all of your questions.

Once you see the max sizes at the source, there are a few limitations that Base adds to the mix - mostly that your in memory cache can not exceed 50 megs for the table data, so depending on how wide those 315,000 rows are (40 columns, if mostly numerics will [should] be no problem )

However, the data all gets wrapped in Java and that expands the storage quite a bit, so you can eat up that 50 meg pretty quickly. I have worked with a few large sets of records, you may beed to bring them in, in chunks...if you have problems ask. You may also need to use the hsqldb engine in server mode, where you can allocate more memory to the table space.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/

[bertram]

thanks for that link, Drew ... that got me started. Chapters 2 and 9 were especially helpful in defining datatypes.

That led to the following additional sources, which were helpful --

http://java.sun.com/j2se/1.5.0/docs/api/
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

The result is that I have edited the form in my question above with the results of some of my "findings."

HSQLDB recommends defining tables with the DOUBLE datatype, instead of FLOAT or REAL because for internal use the java engine converts them to double anyway.

The maximul lengths of strings (text and binary) is determined by a signed Integer, thus 2 gig (theoretical) lengths are possible, lengths up to 1 meg were reported as workable.

For the Date and Time group, I didn't have much success in nailing down the range in years (say 1900 to 2064, or whatever) or the granularity. HSQLDB mentioned granularity of time of in 10^-6 seconds, or SS.ssssss, but the java.sql.Time and Timestamp types talked in terms of milliseconds. Apparently, Sun has been changing their API definitions (I don't know what an API is) for the sql datatypes, and they may have made that change after HSQL wrote their manpages, so there is flux happening in the 1st definitions of things.

The reported "unlimited" range of DECIMAL-integer type sounds a little unrealistic for me, so I'm staying away from it. IMHO, the API definition of the internally used java.math.BigDecimal type on Sun's java developers webpage, http://java.sun.com/j2se/1.5.0/docs/api/java/math/BigDecimal.html , is unintellible for the case of a negatively signed "Immutable, arbitrary-precision signed decimal number." Consider the 4-parts of the BigDecimal value +999.1234. The "IAPSDN," e.g. the number itself, is 999.1234. The "unscaledValue" is digitstring 9991234. The " precision" or length of digitstring, is 7, which is apparently an integer of *undefined type, as according to the definition there is no limit to its length. The "scale" or decimalplaces is 4, held as a 32-bit Integer type. However, I could not figure out how to apply the API definition (1st paragraph=4 sentences) for the case of a negative value, for example, say -999.1234! So, I don't think I'm quite ready for that datatype just yet.

For limitations on naming of columns in a table, I found nothing, but am going to use max length of 31 (taken from thin air), and restrict myself to [a-z, A-Z, 0-9, _, &] (excluding the commas)

For limitations on naming of tables in a base, I found nothing, but assume that the name is restricted to characters which are available for naming of files, and its length will be limited by the total length of the table's file name with its full path, which is both system dependent and dependent on the length of the filename (directory name) for the odb basefile.