Re: [GENERAL] To BLOB Or Not To BLOB
How do I start this? There are so many different ways to look at this question. Do I save files inside the database, or do I simply use the database as a tool to query information about a given file, it's properties, etc. Instead of making general statements, let me pick specific examples. Let's say we want to create an MP3 repository. Our goals are to be able to query the information saved in ID3 tags, to locate, organize, manipulate, or play the MP3. To narrow the scope of discussion, let's say I will be accessing these files locally, on my filesystem or a NFS mounted directory. What would be the most efficient way for us to query the information found in the ID3 tags? We could parse each file in a number of directories and their subdirectories for the information we require each time we need it, or we could parse it once and save the information in a database structured around ID3 information. I would say the later is definitely more desireable and acceptable. Inside the database, the ID3 tags can be indexed and our searches optimized by the information we're requesting. But should we store the mp3's inside the database? I say no. A few observations: * Filesystems are generally better at allocating space for files than databases are. * Storing files inside databases renders shell access or direct access to files as impossible. If the database goes South, so does access to the files. Now, our mp3 decoders are shell programs that read mp3 input by opening a file, a URL connection, or reading it from standard input (that is if we have a good mp3 player). Yet, in order for these mp3 decoders to be able to play an mp3 found inside the database, we are forced to use a specialized database interface. It may return the mp3 file to the decoder via standard input, or it may export the file to a temp directory and feed the mp3 player the location of that temp file. Yet, why should we go through all of this trouble? And why should we tie the operation of two clearly separate applications into such inflexible relationships? We add overhead to the database to output this file, and we add another level of logic to get the file to the decoder. A level of logic that could prove the downfall of a would-be MP3 mobile disc jockey, who's worked so hard to please the Wicked Witch of the Wedding. He'd be forced to pull out the CD's and tapes he so dreads -- if he remembered to bring them along. If we keep file access decoupled from gleaning catagorizable, quantifiable data about/from the file, we allow two separate and efficient tools to do their jobs independently. If file access is your concern, then use a file system that will give you the level of control you desire to develop your security policies. If grabbing information from a data file is important, such as parsing text files for content, then build indexes to the data inside the file and store those indexes in the database. Internet directories are excellent examples of this! We don't neuter local file access by the applications that need the data files, and we still provide access to searchable information. I would argue that access to a data file and querying sets of data files for information are two separate layers of logic. Logical layers are best served when they are given tools to interact, but are not crippled by tight integration. Saving data files inside a database, IMHO, is an example of tight integration, and something to be avoided if possible -- at least in this case. -- Chad "^chewie, gunnarr" Walstrom [EMAIL PROTECTED] http://wookimus.net/chewie
Re: [GENERAL] To BLOB Or Not To BLOB
G'day. I do a lot of work with the BASIS textual/multi-media RDBMS package and run into this question all the time. There is one pretty basic answer: If you leave BLOBS lying around in the file system - particularly if it is a Novell etc file system - people move them and the links get broken. Just today I had an example where several thousand links broke because some dumb Novel administrator moved a directory holding files which were linked to from a database rather than stored in it. (Not my design decision) A database that can hold the BLOBS internally avoids this sort of snafu. (BASIS also has the advantage that it can automagically index almost any form of BLOB to the word/phrase/sentence level if it is stored in the database.) Cheers, Stephen Davies "John Henderson" [EMAIL PROTECTED] wrote: Hi, It seems that the issue with large objects is "Why do you want the info in a database?" It seems to me that the point of a database is its ability to order and relate data. If you want to retrieve the "large-ish text files" based on their content then I think you need to have the files in the database so they can be searched. However, if you are going to retrieve the files based on something about them that is stored as a separate attribute, such as their title, then I think that storing the files as large objects is unnecessary. If you have the option to "[do] as pointers t those files" then I think you are not intending to use any of Postgres's features beyond its ability to store and retrieve data. Surely the file system itself is 'better' at this regardless of how stable Postgres LO's are. I would sure like to hear someone else's opinion on this because it seems that quite a few folks are using large objects and I am often curious about why. John Henderson A while ago it was being held that the Postgres large object data type was too new and not sufficiently tested and mature to be used in a production environment. I am about to deploy a little database that involves storing large-ish text files (20-500k) which could be either done as large objects or as pointers to those files that would be then stored as ordinary files in the OS's filesystem. I am undecided as to this question. What are the pros and cons? What is the performance in either case vis-a-vis the other? It is a web app; the interface is done in PHP. Beginning from which version is the large object interface (if at all) to be considered stable and ready for production? cheers frank -- frank joerdens joerdens new media heinrich-roller str. 16/17 10405 berlin germany e: [EMAIL PROTECTED] t: +49 30 44055471 f: +49 30 44055475 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc Stephen Davies Consulting [EMAIL PROTECTED] Adelaide, South Australia.Voice: 08-8177 1595 Computing Network solutions.Fax: 08-8177 0133
RE: [GENERAL] excell to postgres
If you really want to save the user some work/confusion, investigate what you can do with VB under XL. I would think XL allows you to set some options when export as CSV, with VB, you could write your own Save function. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Hal Snyder Sent: Monday, April 17, 2000 4:41 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] excell to postgres Jeffrey [EMAIL PROTECTED] writes: I don't understand the need for such complicated solutions. Why not just create a table with your shcema, pg_dump the table and insert your delimited .xls file in the pg_dump file. Then re-install the pg_dump-ed table. The above step would save the MS user from converting his .XLS files to Access - all he does is Save As .CSV. Good idea.
Re: [GENERAL] excel to postgres
"Chris Carbaugh" [EMAIL PROTECTED] writes: If you really want to save the user some work/confusion, investigate what you can do with VB under XL. I would think XL allows you to set some options when export as CSV, with VB, you could write your own Save function. How messy to Save to a table via ODBC? Excel talking straight to PostgreSQL - nice idea.
Re: [GENERAL] Connecting website with SQL-database.....
Hello J.Post, On 17-Apr-00 12:26:41, you wrote: I've been looking on the internet for 2 hours for information how to connect my SQL database to my website. I didn't find what I was looking for, this is my problem I have got a form on my site, that can be filled in by visitors. I want the results automatically being written in my sql database, does anybody know how to do this??? Whatever platform you use, PHP is the solution for your problem. It is one of the most powerful Web programming languages that is able to interface natively with dozens of types databases including PostgreSQL. The good part is that it is not tied to any Web server and it is Open Source. http://www.php.net/ If you want to use a DBMS independent interface with database try Metabase for PHP. Metabase not only provides database independence in the access but also in the installation of your database schemas (tables, fields, indexes, sequences). With a Metabase schema description language defined in a custom XML format, you are able to install your schemas portably without having to wonder exactly how. The neat part is that if you want later to change your database schema by adding/removing/changing/renaming tables/fields/indexes/sequences, all you need to do is to make the changes in your database schema description file. Then you only need to ask Metabase to upgrade it and it will issue the necessary SQL commands to alter your database accordingly without affecting any data that was stored after the database was installed for the first time or upgraded for the last time. Here you may find all the source code for Metabase with all the classes, for the schema parser, database manager, driver classes for different DBMS including PostgreSQL, example schemas and installation scripts, user manual and tutorial documents. http://phpclasses.UpperDesign.com/browse.html/package/20 Enjoy, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --
Re: [GENERAL] excell to postgres
I'm a Linux user. I've been using xlHTML and a home-made HTML2DBF rather than ODBC with Perl's DBD::Proxy and DBD::ODBC because, to be able to use ODBC, you need people to *name* the table they create when using Excel. Well, they never do so. Sometimes, they will name a table with 256 columns and thousands of rows! and this, will *not* be handled by stupid ODBC. No name being given to define the table will make ODBC fail, as well. You can open the file, then save it as CSV, etc. But don't we want to make these kinds of things automatic? That's why I use xlHTML. And yes, even with it, I come across bad surprises, very often. So often that I've been in the obligation to refuse that people send me Excel tables. Access is the maximum I can tolerate (ODBC is ok at least). Fabrice Scemama Chris Carbaugh wrote: Can't you just export the excel sheets to a character delimited file and then import that into pgsql? With a little VB script in excel, you should be able to make pretty automated. Just create you own "save" method that would save the sheet as the delimited file is a certain location. Then have a cron job parse the location for new files. Just my 2 cents (and man, the bank is hurtin' :) Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Fabrice Scemama Sent: Monday, April 17, 2000 12:22 PM To: Martin A. Marques Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] excell to postgres "Martin A. Marques" wrote: Is there any utilitie (for Linux if it can be) to pass from excell tables to postgres database tables? Saludos... ;-) -- "And I'm happy, because you make me feel good, about me." - Melvin Udall - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar - You might use C OLE -- a C lib which was designed so as to import OLE files to other formats. An application is xlHTML (excel to HTML). I use xlHTML to import excel tables, then convert the HTML to DBF using a perl script. For some reason, xlHTML's author did not code a xlDBF file; and I'm not familiar with C. I'd be interested in something better than my solution (which I can send to you if you wish so). xlHTML: http://www.gate.net/~ddata/xlHtml/index.htm Regards Fabrice Scemama -- "How could this be a problem in a country where we have Intel and Microsoft?" (Al Gore, Vanity Fair, January 1999 issue, talking about Y2K)
Re: [GENERAL] excel to postgres
On 17 Apr 2000, Hal Snyder wrote: "Chris Carbaugh" [EMAIL PROTECTED] writes: If you really want to save the user some work/confusion, investigate what you can do with VB under XL. I would think XL allows you to set some options when export as CSV, with VB, you could write your own Save function. How messy to Save to a table via ODBC? Excel talking straight to PostgreSQL - nice idea. This would have to be doable in VB, wouldn't it? Doesn't it have some sort of ODBC functionality? I'm no expert here (never used the language) but I'd have thought it'd have to be in there... Cheers -- Tom Cook - Software Engineer "Christ died for sin, the righteous for the unrighteous, to bring you to God." - 1 Peter 3:18 LISAcorp - www.lisa.com.au -- 38 Greenhill Rd. Level 3, 228 Pitt Street Wayville, SA, 5034Sydney, NSW, 2000 Phone: +61 8 8272 1555 Phone: +61 2 9283 0877 Fax: +61 8 8271 1199 Fax: +61 2 9283 0866 --
Re: [GENERAL] excel to postgres
Sure, VB works, as would perl with DBD::ODBC module. I've been using that technique for porting a database from access to postgres, and it should work equally well with excel-based ODBC connections. Disadvantage to the original poster is that it does require a Windows machine to read the Excel file. - Andrew J. Perrin - [EMAIL PROTECTED] - NT/Unix Admin/Support Department of Demography-University of California at Berkeley 2232 Piedmont Avenue #2120 -Berkeley, California, 94720-2120 USA http://demog.berkeley.edu/~aperrin --SEIU1199 On Tue, 18 Apr 2000, Tom Cook wrote: On 17 Apr 2000, Hal Snyder wrote: "Chris Carbaugh" [EMAIL PROTECTED] writes: If you really want to save the user some work/confusion, investigate what you can do with VB under XL. I would think XL allows you to set some options when export as CSV, with VB, you could write your own Save function. How messy to Save to a table via ODBC? Excel talking straight to PostgreSQL - nice idea. This would have to be doable in VB, wouldn't it? Doesn't it have some sort of ODBC functionality? I'm no expert here (never used the language) but I'd have thought it'd have to be in there... Cheers -- Tom Cook - Software Engineer "Christ died for sin, the righteous for the unrighteous, to bring you to God." - 1 Peter 3:18 LISAcorp - www.lisa.com.au -- 38 Greenhill Rd. Level 3, 228 Pitt Street Wayville, SA, 5034Sydney, NSW, 2000 Phone: +61 8 8272 1555 Phone: +61 2 9283 0877 Fax: +61 8 8271 1199 Fax: +61 2 9283 0866 --
[ANNOUNCE] PostgreSQL book completed though chapter 14
I have completed the first draft of my book through chapter 14. New chapters include: Chapter 11, Performance: Indexes, CLUSTER, VACUUM, EXPLAIN Chapter 12, Controlling Results: LIMIT, Cursors Chapter 13, Table Management: Temporary tables, ALTER TABLE, GRANT, REVOKE, Inheritance, Views, Rules Chapter 14, Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK The section on FOREIGN KEYS is a new feature in 7.0beta. The books is accessible at: http://www.postgresql.org/docs/awbook.html Comments welcomed. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] excel to postgres
On Mon, 17 Apr 2000, Andrew Perrin - Demography wrote: Sure, VB works, as would perl with DBD::ODBC module. I've been using that technique for porting a database from access to postgres, and it should work equally well with excel-based ODBC connections. Disadvantage to the original poster is that it does require a Windows machine to read the Excel file. Probably if users are saving to an excel file to be put into access and then shipped of to a postgres instance then there is a windows machine around. So write a function to save to the database instance, and then alias it to a menu item 'Save to Database' or something like that. [snip] This would have to be doable in VB, wouldn't it? Doesn't it have some sort of ODBC functionality? I'm no expert here (never used the language) but I'd have thought it'd have to be in there... Cheers -- Tom Cook - Software Engineer "Christ died for sin, the righteous for the unrighteous, to bring you to God." - 1 Peter 3:18 LISAcorp - www.lisa.com.au -- 38 Greenhill Rd. Level 3, 228 Pitt Street Wayville, SA, 5034Sydney, NSW, 2000 Phone: +61 8 8272 1555 Phone: +61 2 9283 0877 Fax: +61 8 8271 1199 Fax: +61 2 9283 0866 --
