• Welcome, Guest. Please login.
 
September 17, 2019, 10:17:47 am

News:

Welcome to the SQLitening support forums!


Question on creating a new DB

Started by Mark Strickland, July 03, 2008, 12:38:59 am

Previous topic - Next topic

Mark Strickland

What would be the best way to create a new "empty" DB file?

Paul's old code would simply create the DB if it did not exist.  Since SQLitening, in the server mode, cannot create a DB file and the DB is in the "home" directory of the server what would you consider a good practice to create a new DB file?

I could create it in a "direct connect" mode and copy it to the server "home" directory but I don't see an easy way to know where the "home" directory exists.

Does the QUERY_SERVICE_CONFIG return the path in the lpBinaryPathName data item where the DB file would be stored?

Would it make sense to allow the slOpen to have the Create flag?

If you have a better idea I am open to what ever will work.

I wrote a Cheetah to SQLite converter tool based on Paul's old code and it had an option to create the DB if desired.  I have converted this code to SQLitening and this is the last thing to fix.  Once I fix this last "feature" I will post the updated code to the forum for all to use.

Thanks,

Mark

Mark Strickland, CISSP
www.SimplyBASICsecurity.com

Fred Meier

July 03, 2008, 05:47:44 pm #1 Last Edit: July 03, 2008, 06:05:50 pm by Fred Meier
Mark - Yes, the QUERY_SERVICE_CONFIG returns the path in the
lpBinaryPathName parm.  It also appends the program name and
any command line parms.  For SQLitening that would be
           "SQLiteningServer.Exe" & " " & chr$(&HFF)
There is an example in SQLiteningServerAdmin.Bas.

There are any number of ways to get the files on to the server.  I would
create them on my local computer and then copy them to a shared folder on
the server.  Then going to the server, I would copy them to either the
same folder as the service or create a new subfolder and copy them there. 

Another way is to run a local mode SQLitening application on the server
which could create the database into either the service folder or a
subfolder.  This small local app could remain on the server for any future
database create requirements. 

Both of these methods require you to have physical access to the server. 
If I did not have that access then I would create on my local computer and
email to the server admin.  All of these assume very little create activity.

Allowing the slOpen to have the Create flag is valid question.  It would
be very easy to change and allow it.  The concern would be for a
misbehaved program to create many unwanted files.  It only takes a typo
error and you have created a file on some remote server!  If Create is
allowed should delete also be allowed?  Maybe a Cfg option?  The apps I
have done to date never needed the ability to create "master" files or
databases on the fly.  SQLite has both the Temp and :Memory: databases
which can be very useful.  I think if I was a server admin I would want
some kind of protection against files being created on my server without
my permission.  The subject is certainly open for more discussion and I will
be happy to make any agreed upon changes. 


Mark Strickland

Fred,

After reading your responses I agree with you that the server version should NOT be allowed to create a physical DB file.  That makes sense.  Typically the creation of a physical DB is NOT something done on the fly by an application program.

My intial "need" to create the physical DB came from converting the Cheetah 2 SQLite utility that I previously posted to the FF Source Code Forum.  It had the option to create the file if desired.  I kept the option in the program but it only now works for a direct connect DB.

If needed the programmer could create a DB using the direct connect mode then copy it to the server data path (from the QUERY_SERVICE_CONFIG of the running server) if the server file permissions allowed that access.

As far as I am concerned, at this point, I think you are right on.  Unless the concensus is at create option is needed then don't change it.

I believe SQLitening is one of the most elegant DB solutions on the planet.  Couple that with FireFly and PB ... nothing is better.   ;D

Mark
Mark Strickland, CISSP
www.SimplyBASICsecurity.com

JoeByrne

QuoteAfter reading your responses I agree with you that the server version should NOT be allowed to create a physical DB file.  That makes sense.  Typically the creation of a physical DB is NOT something done on the fly by an application program.


I have to strongly disagree.  I understand the potential security issues, but who should have the "authority" to decide whether a process is a security risk or not.... the programmer, or the tool the programmer uses?

Should hammers be built in such a way that they can't be used to hit anything other than a nail?

I have a number of C/S applications that create db's "on the fly".  Its not an unusual situation.  There are plenty of "in-house" programmers who don't need to be impeded, but empowered, by their tools.

Security is really the responsibly of the programmer(s) and the network administrators, not the software tools.

Just my 0.02 worth

Paul Squires

I think I tend to agree with Joe on this one. Give the programmer the option to allow the creation and him/her decide whether or not to implement it in the application.

:)


Mark Strickland

I can go either way but I had already figured out my "work around".

Mark

Mark Strickland, CISSP
www.SimplyBASICsecurity.com

Fred Meier

I agree with Joe's comment that security is really the responsibly of the
programmer and the network administrator, not the software tools.  I
also believe that the tools should allow for proper controls so I will
make the following changes to Version 2 which I plan to send to Paul the
first of next week. 

1. Add a new entry to the General section of the .Cfg file as follows:

      CreateDatabaseAllosed = Yes or No  ---  Controls the creation of new databases.  If yes clients are allowed to
                                              create new databases on the server.  If omitted or value is empty then
                                              will default to No

2. Change the slOpen to work the same way in both Local and Remote mode.
   If a database does not exist and ModChar "C" is passed then will allow the
   open to occur and a new database is created.  If a database does not exist
   and no ModChar "C" then will raise error -9 (File does not exist).


JoeByrne

Fred,

It occurred to me that I left out one very important point:

Thank you for the excellent work (and to Paul as well!).

I didn't want to come across sounding ungrateful :)  I'm in a DB transition at the moment.  Having recently finished a huge app using Tsunami, and prior making many apps with Cheetah, I've decided its time to move on to SQL.  I'm still having difficulties with some of the details of the code, but I have a good grasp of the general concept.  Been doing database stuff most of my life, but it seems that each engine has its own personality and its necessary to learn to "think" in that personality.

I plan to document my understandings as I go.  I wish to contribute back to this project as much as I can and documentation is a strong point of mine.  I'd hope to encourage anyone else using this tool to post lots of questions and/or suggestions.  IMO, seeing others work through issues is the best way to learn :)

Mark Strickland

Joe,

Yes some SQL DB's do have different "personalities".  Oracle and other "brand name" engines tend to have their own extensions.  In general (beyond the administration of the engine itself) SQL can be fairly standard if you stay away from the extensions added by various "brands".  SQLite is fairly "standard" from what I have seen.

One of the other differences with SQL and indexed data file managers like Cheetah and Tsunami is how you have to think about getting to the data.

JOINs are very powerful and let you create a "connected" set of info that involve multiple tables (files).  There are ways to summarize the data from a SQL statement using GROUP BY and your application code does not have to do any summing or other logic.  Sub Selects let you do some other interesting things (a SELECT within a SELECT).

I have even JOINed a table to itself in one application.  I got that tip from a SQL guru and it was very much counter intuitive as I was trying to make the transition from things like Cheetah to SQL.

One key thing is you must remember that "standard" SQL statements are NON procedural and you cannot think of the data retrieval as you did in Cheetah (i.e. Read a header record for an order, read the customer record for that header, have a sub-loop to read the detail for the order lines, read the part master for each part number of each line, etc).  In SQL you can do this whole thing in one single SQL statement and the simply step through a linear list of records one at a time with everything you need already assembled into one logical record.

It is a bit of a mental "change of gears" but once you see how it can work you won't ever want to go back.

Find a good SQL tutorial (print or Web) and look at lots of examples.  If you can get the "hang" of JOINs then you will have "won about 75% of the battle".

Get a good SQL query tool and play with your DB constructing the SQL statements on the fly and see the instant results.  I wrote a Cheetah2SQL tool (posted here) that will help you convert some of your old Cheetah files to SQLite.  If you understand the data in the Cheetah form then it will help you make the transition to SQL.  Once you have an SQL statement working the way you want then put it in some PB code via SQLitening.

I use SQL Maestro (not free except the ODBC version).  I think Paul has found a free one for SQLite (SQLite3explorer - http://www.singular.gr/sqlite/).  Here is another one (http://www.sqliteexpert.com/) but it is not free.

ALSO -- Most SQL DB's have the EXPLAIN verb.  It will return how the server approaches finding and returning data.  It is very helpful if you have speed issues.  Because JOIN's all happen "under the hood" you may get things running slow on some queries.  EXPLAIN will show you how the "engine" optimizes the query and uses index files.

Good luck.

Mark Strickland
Mark Strickland, CISSP
www.SimplyBASICsecurity.com

JoeByrne

Thank you Mark! 

This is very useful.  I wasn't quite grasping the 'non-procedural' concept, but now I think I see where my thinking was getting skewed.

I've read a couple of good SQL books, but alas have not really played with the live data much.  That's one reason I was so excited about SQLite, it seemed like the perfect starting point to nail down the basics.  I made a leap-frog discovery last night while playing with the sample code in SQLighting and a few necessary light bulbs went off!  YEA!

Now I'm on to more playing..... kinda feels a bit like Christmas :)