in reply to Re: Re: To DBI or not to DBI
in thread To DBI or not to DBI

Thank you for your advice. It's making me think hard about this.

As it turns out, I am actually quite familiar with SQL. However, my familiarity with SQL is only partly relevant.

What I'm implementing is a script that can be configured by people who use my software to connect to whatever database they have on their Web server. What kind of configuration options do they see in my software? I am pretty sure none of my users know SQL, so I will try to avoid having them type in any SQL statements. Instead, I will likely have them choose from a list of databases, and have them type in the name, and other distinguishing information for the database. If their database is not in the list, I plan to provide some way for them to create a custom configuration.

At the outset, I am planning to have SQL statements inside my script for creating tables, and retrieving, inserting and editing records - should be very simple stuff. If, for example, it turns out that there isn't one SQL statement that will create a table in whatever database the user selects, then I will need to make the table-creating SQL statement part of the parameters that come with the selected database. I'm already betting that I will need to do this. It may turn out that all SQL statements will be parameters that come with the selected database. This is a paranoid approach that says I don't know everything about all databases.

This means that if a user doesn't see her kind of database in the list, she will create a new set of parameters and type in the SQL statement for creating a table and any other SQL statement that I cannot make work for all databases. I can of course help her on the telephone. If having another abstraction layer avoids my users from having to type SQL statements for custom databases it is worth investigating.

Also, I'm considering SQLite only as an alternative like any of the other databases (Oracle, SQL Server, MS Access, mySQL, etc.), where a user can choose to use a SQLite database to store the information for the script. SQLite would be just another database as far as the script is concerned. CSV might also be that way, but since CSV is already supported by the script, I haven't decided if the CSV implementation will be replaced.

Thank you again,

Richard

Replies are listed 'Best First'.
Re: Re: Re: Re: To DBI or not to DBI
by hmerrill (Friar) on Jan 29, 2003 at 16:39 UTC
    Wow, sorry - now, reading what your intent is I may stand corrected. Maybe SQLite does make sense.

    That is quite a project you are undertaking! I'm sure you realize that there are enough differences between the databases to make your job a hard one. For example all(?) the databases handle these things differently:
      * Date/Time handling
  * Sequences (unique identifiers)

    [download]
    Those are the 2 glaring ones off the top off my head, and I'm sure there are more. I try to stick with standard SQL, and try to stay away from things that each database does which are specific only to that database. But some things(like Date/Time, Sequences, etc.) you just have to deal with in each database. I haven't worked with triggers and constraints(and other stuff), so if you use those you may encounter issues there. Just thinking about how you would handle the differences in date/time handling makes my head hurt ;-)

    Good luck with your project!
[reply]
[d/l]
      Thank you for your follow-up.

      Fortunately (for now anyway), the scripts needs very little from a database, so I won't initially encounter the many incompatibilities that I'm sure exist. Hopefully, I won't design myself into a corner that will cause a lot of rewriting later!

      Richard

[reply]

In Section Seekers of Perl Wisdom