Re: [sqlite] intial database creation
I was thinking a bit on your question...let's make some terminology clear.
Database file -- a user file on your hard disk that may contain tables
Table -- a user table entry which may contain records
Records -- a specific user data record in a table containing fields of user data
#1 Create "a database file" as you asked -- when you first open a database file
with the shell or the default C API it will automatically create it. It does
not need to be "pre created". For the C APIthere are options when opening
the database file which can mandate that it already exist.
Example with the shell.
sqlite3 myfile.db
sqlite> .quit
Database file "myfile.db" now exists on your system but has no tables in it
(ergo no records).
#2 Create a table -- note that myfile.db does not need to exist for this
example:
sqlite3 myfile.db
sqlite> create table mytable(mydata);
sqlite> .quit
Database file "myfile.db" now exists and has one table with no data
#3 Create a record -- Now we'll re-use the databasefile that already has
"mytable" in it.
sqlite3 myfile.db
sqlite> .dump mytable
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable(mydata);
COMMIT;
sqlite> insert into mytable values('value1');
sqlite> select * from mytable;
value1
sqlite> .quit
Your database file "myfile.db" now has one table with on record in it.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of McSparin, Brett E [brett.e.mcspa...@boeing.com]
Sent: Thursday, May 10, 2012 3:26 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] intial database creation
How does one go about creating a database initially in SQLite?
Do you create a schema file for it to read or similar?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] intial database creation
Hello Brett, All of my DB's are generated in code. When I version control the code, I'm also version controlling the schema. If my users end up with bad DB's (it's windows, it happens fairly frequently). They just delete them and they're then re-created as needed by the program. I have specific unit tests built as part of the class itself to test access to the DB and test all the queries and inserts. I just use the command line tool for trouble-shooting. C Thursday, May 10, 2012, 4:26:29 PM, you wrote: MBE> How does one go about creating a database initially in SQLite? MBE> Do you create a schema file for it to read or similar? MBE> ___ MBE> sqlite-users mailing list MBE> sqlite-users@sqlite.org MBE> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] intial database creation
Same basic advice as Michael. If I may add however, you make a separate SQL file with just the database schema and put it under version control same way you would do with any other source file. Other test cases and sample queries can also be version controlled but would probably be separate from the main schema. Mircea On 10/05/2012 4:26 PM, McSparin, Brett E wrote: How does one go about creating a database initially in SQLite? Do you create a schema file for it to read or similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] intial database creation
My preference is to create a file with an editor to spell out the tables and some example inserts and selects to test. Then you can easily just make some changes and re-read the file to test it again. Then you can run the sqlite shell and do this (example file is myfile.sql that you create) containing: drop table if exists mytable; create table mytable(field1,field2); insert into mytable values(1,2); insert into mytable values(3,4); select * from mytable; Then run this to create database named "my.db" sqlite3 my.db sqlite> .read myfile.sql 1|2 3|4 sqlite> Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of McSparin, Brett E [brett.e.mcspa...@boeing.com] Sent: Thursday, May 10, 2012 3:26 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] intial database creation How does one go about creating a database initially in SQLite? Do you create a schema file for it to read or similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] intial database creation
How does one go about creating a database initially in SQLite? Do you create a schema file for it to read or similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
