GnuCash / SQL

[Christopher Browne]

> In a message dated: 03 Apr 2002 20:45:35 EST
> Derek Atkins said:
> >As for text export, I _DID_ say export and import.  Perhaps you missed
> >it (or perhaps it was in one of the personal replies instead of one on
> >the list).
> 
> I evidently missed that, and Rob Browning kindly pointed out that 
> 'import' would be a requirement.
> 
> >Note that with MySQL, you can always run the db_dump and db_restore
> >functions, even with an embedded database.

> If you say so, I know nothing of the internals of MySQL, so I'll have
> to trust you :)

I don't much like the smiley there.  It seems to imply that you are
humouring people that you basically basically figure are lying to you:

  "Oh, yes, they _tell_ me that there's a way to dump a database out...
   I don't really believe a word of it, but I'll humour them, for now..."

In the interests of getting rid of all such "non-trust," I'll inject a
dose of _what actually happens_.

The "mysqldump" command is documented here:
<http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#mysqldump>

Here's a sample of what it generates for a database I've got:

-- MySQL dump 8.21
--
-- Host: localhost    Database: questionnaire
---------------------------------------------------------
-- Server version	3.23.49-log

--
-- Table structure for table 'QAXES'
--

CREATE TABLE QAXES (
  OPTIONID int(11) NOT NULL auto_increment,
  TITLE varchar(48) NOT NULL default '',
  QUESTYPE varchar(4) NOT NULL default '',
  DESCR varchar(255) NOT NULL default '',
  PRIMARY KEY  (OPTIONID)
) TYPE=MyISAM;

--
-- Dumping data for table 'QAXES'
--


INSERT INTO QAXES VALUES (1,'Usefulness','NUM','Did you find this web page generally useful or interesting');
INSERT INTO QAXES VALUES (2,'Agedness','NUM','Does this web page seem reasonably up-to-date?');
INSERT INTO QAXES VALUES (3,'Miscellanea','MULT','Assorted selections that did not fit well anywhere else');

--
-- Table structure for table 'QGENSTATS'
--

CREATE TABLE QGENSTATS (
  STATID int(11) NOT NULL auto_increment,
  PAGE varchar(32) default NULL,
  HOST varchar(48) default NULL,
  AGENT varchar(48) default NULL,
  DATE date default NULL,
  PRIMARY KEY  (STATID)
) TYPE=MyISAM;

--
-- Dumping data for table 'QGENSTATS'
--


INSERT INTO QGENSTATS VALUES (89,'index','192.168.1.13','Mozilla/5.0 Galeon/1.0 (Linux i686; U;) Gecko/20','2001-12-11');
INSERT INTO QGENSTATS VALUES (88,'index','','','2001-12-11');

--
-- Table structure for table 'QMSTATS'

[and another 300-odd lines I'll omit as they're not more
illuminating...]

A very analagous command, pg_dump, does much the same thing with
PostgreSQL:

% pg_dump sqlledger | head -50

--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 55 (OID 46981)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: postgres
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';

--
-- TOC Entry ID 56 (OID 46982)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

\connect - cbbrowne
--
-- TOC Entry ID 2 (OID 127068)
--
-- Name: id Type: SEQUENCE Owner: cbbrowne
--

CREATE SEQUENCE "id" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

--
-- TOC Entry ID 4 (OID 127087)
--
-- Name: makemodel Type: TABLE Owner: cbbrowne
--

CREATE TABLE "makemodel" (
	"id" integer DEFAULT nextval('id'::text),
	"parts_id" integer,
	"name" text
);

--
-- TOC Entry ID 5 (OID 127115)
--
-- Name: gl Type: TABLE Owner: cbbrowne
--

CREATE TABLE "gl" (
	"id" integer DEFAULT nextval('id'::text),
	"source" text,
... and much more anon...

In case it's not Blatantly Obvious, the results of such dumps are
remarkably human-readable, human editable, and are done in Plain Text,
which would _appear_ to fit with your preferences.

I've not gone through upgrades of MySQL, but expect that the format that
it generates is something that could move data from older to newer
versions.

I _have_ gone through PostgreSQL upgrades; the way that an upgrade is
done is to take a dump produced using pg_dump, and load it into the new
version.  I haven't seen it _not_ work flawlessly.

Note that if Berkeley DB were chosen as "The Database," there is a
similar program called (surprise, surprise, surprise) db3_dump.

Does anybody have any guesses as to what db3_dump does?  Only in
particularly enfeebled imaginations would its purpose be to transform a
GnuCash database into a Microsoft Word document...
--
(concatenate 'string "cbbrowne" "@acm.org")
http://www3.sympatico.ca/cbbrowne/sgml.html
ITS is a hand-crafted RSUBR.