I am trying to teach myself how to make a database program that will open or create a database file at run time in either sqlexpress or postgresql..
I have never really worked with databases except for basic access stuff so I have very little knowledge of sql or postgresql..
Basically where I am stuck is at the file IO part.. whether the database is on a server or on the users machine the database is contained in a file (or multiple files) correct? I can open a file with connectionstring using ADO.NET and VB2k5expr, but I can't figure out how to create the actual files at run time without having another program do it for me outside of my program (or having microsoft make me one of the wrong type automatically with a click of a button...).. How do I go about actually creating a file at runtime that I can open with connectionstring? Seems like every book I get on it gives a worthless overview of relational databases then jumps straight into the sql code and concepts which dont seem to be what I need since I am still trying to figure out how the data is actually stored in the file itself.. I've only had C programming experience in college manipulating low level data with stdio file IO, and I guess I'm missing how the 'file' itself actually works..
Anyway.. if anyone can can help me out or point me in a direction it would be greatly appreciated. (for example an exercise that would teach me how to create a simple program that creates a 1 table, 1 column database file with the ability to enter data at runtime.. then I think I could jump into the rest of the sql code with ease since I would actually be able to test the stuff I would be reading..)
I can help you with some of your questions - and I recall having some similar "dumb" questions when I first encountered PostgreSQL.
PostgreSQL is based on a client-server model. The PostgreSQL program is one one machine and it stores the data in files that are contained in specific directories. You usually don't have to worry about this as PostgreSQL takes care of it internally. Your data files are stored alongside several PostgreSQL system files that the program needs to work with your data.
You don't directly access your data files but rather you do so through PostgreSQL. An interface language is provided (psql). You can also use an administrative tool. pgAdmin III ships with PostgreSQL and is pretty good. If you spend a few bucks, you can get pgLightning Admin (from AM Software) and I like it quite a bit. A third option would be be EMS PostgreSQL Manager. With these tools, you can create and manage your databases. There is quite a bit to this, as PostgreSQL is very powerful and a lot of functions are done internally. Fortunately, the PostgreSQL manual, which also comes with the program, is pretty thorough (over 1400 pages!, how's that for open-source documentation).
Now, you will also need a client application that allows users to interact with the database. This will need to connect with the database through something like ODBC or JDBC (I'm not sure how ADO.Net works, as I've never used it). The client can be on the same machine as PostgreSQL or it can be on another machine, connected via LAN or Internet. The client application can be written in a number of languages, such as Java, Delphi, PHP, tcl/tk, or even Access (I've heard). The client application interacts with the database via issuing SQL commands.