Re: [sqlite] Newbie question on Data Source
I'll note a few things here... #1 If you want to learn about database design or how to code in language X get a book on those and not on a particular product (you may want the product book too if you need it but those tend to be less helpful). #2 Your first language will be hard to get out of. I've done over 60 languages now and C# is one of only a few that I was impressed with and worth the trouble to learn. Ada was the other one (but for completely different reasons). I still prefer C though C++ can be more helpful at times (advanced data structures). #3 This list is probably one of the best learning things you can do that doesn't take study time. When somebody poses a problem try to solve it yourself without seeing the answer that inevitably comes up here. One of the better ways of learning is doing. 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 Dave [thesche...@cox.net] Sent: Monday, November 28, 2011 5:24 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Newbie question on Data Source On 11/28/2011 4:59 PM, Simon Slavin wrote: On 28 Nov 2011, at 10:44pm, Dave wrote: On 11/28/2011 4:37 PM, Simon Slavin wrote: Just to stress that the filename includes the bit after the '.'. You can have any number of files with the same part before the '.' but different extensions. To tell the operating system which one you want you must include the bit after the '.'. Actually, I knew that. Just having a bit of a multiple crash course...all at once. I just recently built this pc and have been on XP until recently too. I do some graphic stuff and have many same named pics with the various .jpg, .bmp, .gif extensions. My job involves dealing with lots of people who aren't good at computers and the fact that, by default, the OS hides file extensions confuses the hell out of them. I'm not taking a shot at Windows here: the Macintosh OS does the same thing. It also means you see files with names like 'paper.doc.docx.docx.PDF'. Simon. I hear you... I once had a shareware app that I bought that the guy that coded it had a weird way of saving progress by nesting folders all named the same. I was watching a backup app once and thought, wtf, when I saw \Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\. I am pretty good with computers actually but still can get caught looking like a nob here and there. :-) I am one of the guys that was a diehard VB6 Classic user and when I upgraded to .NET 2002 I was instantly mad at the so called VB7 as it was nothing like the upgrade we were expecting or wanting. I got the $20 update for .NET 2003 and looked at it and stayed with VB6 Classic. I looked at Delphi, RealBASIC, and PowerBasic, and realized I like VB. Now I thought I better get caught up before Win8 starts messing with legacy apps. And as far as data base programming, I am still a noob. I read all the thick Access books years ago and spent a lot of time trying to learn but always reached a point where I got totally confused when it came to design and primary and foreign keys and relationship designer views. So here I go again. I don't give up easy. Thanks, Dave ___ 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] Newbie question on Data Source
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/29/2011 12:17 PM, Black, Michael (IS) wrote: I'll note a few things here... [snip] Much good advice. Seconded! ABS - -- Alaric Snell-Pym http://www.snell-pym.org.uk/alaric/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk7UzwwACgkQRgz/WHNxCGo8YQCfbuHuGn+TswVZvoaBdKvvUTfc LuIAnRklSLS0P/KgE29lF6GHuvlnnonq =U2zn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Michael, #1 Thanks for the comments. I have the book, The Definitive Guide to SQLite from Apress by Michael Owens, but it is the 2006 first edition. Do you, or anyone, know if the new or 2nd edition is worth buying or does it just cover 1 new item? I am not afraid to buy books. You should see my VB6 collection. :) Can you recommend a must have well written database design book? #2 Believe me, I know it is hard to get out of my VB6 and if I knew it would work forever, I probably wouldn't. But Microsoft dropped support years ago. I started with a TI994A and moved up to a Commodore C64 and actually ran a BBS with SCSI hard drives attached to it. Did the same with a Commodore C128. Still have the equipment and it still works. I build all my computers. I have the Visual Studio Pro so I can look at C# but I had C++ in my Classic Studio and stayed with Visual Basic. For my needs it is powerful enough. #3 This list has helped me get set up as I found the old ADO provider (1.0.66) and had some questions on the correct package to download from the System.Data.SQLite. Then the designer was missing but now I see it is almost ready and will come out next release. That is good. My database needs or wants are simple at the present time. When I wrote my main app that is shareware I couldn't quite figure out the data base stuff and I also was worried someone would just steal my data base and use it to form a competing product. The password with Access seemed easy to break if you Googled it. So I hard coded my data into the app but that made it a bunch of extra work to add new items in the proper order that was necessary as there were multiple fields. As I rewrite the app, the main reason for all this upgrading, I will surely come up with new situations and challenges. Simple things come to mind like allowing the end user to change the data, but if they muck it up, allow them to get back to the default data base. My app deals with formulas that require accuracy and hard coding data preserves that. The another reason I stayed away from .NET as I read how easy it was for people to steal your hard work. I read about Obfuscation and it seemed like it may or may not work all that good, but it looks like that is all we have so I have to trust it. Visual Studio Pro comes with a standard or basis Obfuscator but I have found other ones for free and some costing in the thousands. Dave On 11/29/2011 6:17 AM, Black, Michael (IS) wrote: I'll note a few things here... #1 If you want to learn about database design or how to code in language X get a book on those and not on a particular product (you may want the product book too if you need it but those tend to be less helpful). #2 Your first language will be hard to get out of. I've done over 60 languages now and C# is one of only a few that I was impressed with and worth the trouble to learn. Ada was the other one (but for completely different reasons). I still prefer C though C++ can be more helpful at times (advanced data structures). #3 This list is probably one of the best learning things you can do that doesn't take study time. When somebody poses a problem try to solve it yourself without seeing the answer that inevitably comes up here. One of the better ways of learning is doing. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question on Data Source
I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. My SQLconnect.ConnectionString = data source =C:\Users\Dave\Documents\Visual Studio 2010\Projects\WindowsApplication5\MyDatabase.db3 Anything to help me get off to a good start will be much appreciated. I am using the latest 1.0.77.0 SQLite dll. Thanks, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
I think this is a bug in SQL Lite I just came across this today and found that there is an update pending that claims to resolve it Not sure if this is the link but it is the site... http://system.data.sqlite.org/index.html/timeline M -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Sent: Monday, November 28, 2011 4:53 PM To: General Discussion of SQLite Database Subject: [sqlite] Newbie question on Data Source I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. My SQLconnect.ConnectionString = data source =C:\Users\Dave\Documents\Visual Studio 2010\Projects\WindowsApplication5\MyDatabase.db3 Anything to help me get off to a good start will be much appreciated. I am using the latest 1.0.77.0 SQLite dll. Thanks, Dave ___ 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] Newbie question on Data Source
On 11/28/2011 4:52 PM, Dave wrote: I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. What do you mean, of the same name? It's impossible to have two files with the exact same name in the same folder. The two names must be different in some way. Figure out what this difference is, then it probably will become clear why it happens. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 3:59 PM, Igor Tandetnik wrote: On 11/28/2011 4:52 PM, Dave wrote: I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. What do you mean, of the same name? It's impossible to have two files with the exact same name in the same folder. The two names must be different in some way. Figure out what this difference is, then it probably will become clear why it happens. I just checked and the 0K file that is created is the same name except it has .db3 added to it's name. Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 5:08 PM, Dave wrote: I just checked and the 0K file that is created is the same name except it has .db3 added to it's name. Why is this suprising? Your connection string requests a file named MyDatabase.db3. Is this not the file you want to connect to? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 3:59 PM, Igor Tandetnik wrote: On 11/28/2011 4:52 PM, Dave wrote: I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. What do you mean, of the same name? It's impossible to have two files with the exact same name in the same folder. The two names must be different in some way. Figure out what this difference is, then it probably will become clear why it happens. I just looked again and renamed my original database file back to add the .db3 to it and it remains in the same folder as the other file with one exception. The properties for my database shows: type: Data Base File Where the one generated at 0K with the same name is: type: DB3 File Thanks, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On Mon, Nov 28, 2011 at 5:13 PM, Dave thesche...@cox.net wrote: On 11/28/2011 3:59 PM, Igor Tandetnik wrote: On 11/28/2011 4:52 PM, Dave wrote: I am trying to learn VB.Net and SQLite at the same time. I have used VB6 Classic in the past. I have VB Studio 2010 Pro and am just trying to open a small simple database and have made some progress but hit a problem I cannot figure out after plenty of trying. I keep getting the error message: SQLite Error no such table: MyTableName I have the database in the project folder on my Win7 64 Pro system. The app, when run, creates an empty database file of the same name as my database file which is 13KB in size. What do you mean, of the same name? It's impossible to have two files with the exact same name in the same folder. The two names must be different in some way. Figure out what this difference is, then it probably will become clear why it happens. I just looked again and renamed my original database file back to add the .db3 to it and it remains in the same folder as the other file with one exception. The properties for my database shows: type: Data Base File Where the one generated at 0K with the same name is: type: DB3 File You probably have Hide extensions for known types enabled in windows When this is enabled windows does not display the extension for known types so that if you have more than 1 file with the same base name they will appear as the same but with a different icon in the explorer view. John M. Drescher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Yes, that is what I am trying to connect to. The problem is that it apparently doesn't connect to it as it cannot see the tables in it. So it creates another database file that has 0K in size. My database file has the correct icon next to it's name with a little key in the pic where the one that is created just has an icon that looks like a sheet of paper. Dave On 11/28/2011 4:09 PM, Igor Tandetnik wrote: On 11/28/2011 5:08 PM, Dave wrote: I just checked and the 0K file that is created is the same name except it has .db3 added to it's name. Why is this suprising? Your connection string requests a file named MyDatabase.db3. Is this not the file you want to connect to? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 5:13 PM, Dave wrote: I just looked again and renamed my original database file back to add the .db3 to it and it remains in the same folder as the other file with one exception. The properties for my database shows: type: Data Base File Where the one generated at 0K with the same name is: type: DB3 File Go to Control Panel | Folder Options | View. Uncheck Hide extensions for known file types setting, and click OK. Now you can see *real* names of your files. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Ok, I switched that and now my original database file has a .db added whereas the one created by my app stays the same. Thanks, Dave On 11/28/2011 4:14 PM, Igor Tandetnik wrote: On 11/28/2011 5:13 PM, Dave wrote: I just looked again and renamed my original database file back to add the .db3 to it and it remains in the same folder as the other file with one exception. The properties for my database shows: type: Data Base File Where the one generated at 0K with the same name is: type: DB3 File Go to Control Panel | Folder Options | View. Uncheck Hide extensions for known file types setting, and click OK. Now you can see *real* names of your files. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Thanks John. On 11/28/2011 4:18 PM, John Drescher wrote: You probably have Hide extensions for known types enabled in windows When this is enabled windows does not display the extension for known types so that if you have more than 1 file with the same base name they will appear as the same but with a different icon in the explorer view. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 5:23 PM, Dave wrote: Ok, I switched that and now my original database file has a .db added whereas the one created by my app stays the same. It doesn't have .db added. It had it all along, you just couldn't see it. Anyway, now you can name your file however you want. Make sure that, whichever name you choose, you use the same name in the connection string. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 28 Nov 2011, at 10:32pm, Igor Tandetnik wrote: On 11/28/2011 5:23 PM, Dave wrote: Ok, I switched that and now my original database file has a .db added whereas the one created by my app stays the same. It doesn't have .db added. It had it all along, you just couldn't see it. Anyway, now you can name your file however you want. Make sure that, whichever name you choose, you use the same name in the connection string. Just to stress that the filename includes the bit after the '.'. You can have any number of files with the same part before the '.' but different extensions. To tell the operating system which one you want you must include the bit after the '.'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Thanks Igor, I am just getting started so I am bound to get tripped up a little. :-) That was going to be my next question anyhow, about the database name. I was thinking we *had* to have the extension .db3 but you have answered that. I just have been using VB.Net less than 30 days and that doesn't help any. Cheers, Dave On 11/28/2011 4:32 PM, Igor Tandetnik wrote: On 11/28/2011 5:23 PM, Dave wrote: Ok, I switched that and now my original database file has a .db added whereas the one created by my app stays the same. It doesn't have .db added. It had it all along, you just couldn't see it. Anyway, now you can name your file however you want. Make sure that, whichever name you choose, you use the same name in the connection string. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
Thanks Simon, Actually, I knew that. Just having a bit of a multiple crash course...all at once. I just recently built this pc and have been on XP until recently too. I do some graphic stuff and have many same named pics with the various .jpg, .bmp, .gif extensions. Dave On 11/28/2011 4:37 PM, Simon Slavin wrote: On 28 Nov 2011, at 10:32pm, Igor Tandetnik wrote: On 11/28/2011 5:23 PM, Dave wrote: Ok, I switched that and now my original database file has a .db added whereas the one created by my app stays the same. It doesn't have .db added. It had it all along, you just couldn't see it. Anyway, now you can name your file however you want. Make sure that, whichever name you choose, you use the same name in the connection string. Just to stress that the filename includes the bit after the '.'. You can have any number of files with the same part before the '.' but different extensions. To tell the operating system which one you want you must include the bit after the '.'. Simon. ___ 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] Newbie question on Data Source
On 28 Nov 2011, at 10:44pm, Dave wrote: On 11/28/2011 4:37 PM, Simon Slavin wrote: Just to stress that the filename includes the bit after the '.'. You can have any number of files with the same part before the '.' but different extensions. To tell the operating system which one you want you must include the bit after the '.'. Actually, I knew that. Just having a bit of a multiple crash course...all at once. I just recently built this pc and have been on XP until recently too. I do some graphic stuff and have many same named pics with the various .jpg, .bmp, .gif extensions. My job involves dealing with lots of people who aren't good at computers and the fact that, by default, the OS hides file extensions confuses the hell out of them. I'm not taking a shot at Windows here: the Macintosh OS does the same thing. It also means you see files with names like 'paper.doc.docx.docx.PDF'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
On 11/28/2011 4:59 PM, Simon Slavin wrote: On 28 Nov 2011, at 10:44pm, Dave wrote: On 11/28/2011 4:37 PM, Simon Slavin wrote: Just to stress that the filename includes the bit after the '.'. You can have any number of files with the same part before the '.' but different extensions. To tell the operating system which one you want you must include the bit after the '.'. Actually, I knew that. Just having a bit of a multiple crash course...all at once. I just recently built this pc and have been on XP until recently too. I do some graphic stuff and have many same named pics with the various .jpg, .bmp, .gif extensions. My job involves dealing with lots of people who aren't good at computers and the fact that, by default, the OS hides file extensions confuses the hell out of them. I'm not taking a shot at Windows here: the Macintosh OS does the same thing. It also means you see files with names like 'paper.doc.docx.docx.PDF'. Simon. I hear you... I once had a shareware app that I bought that the guy that coded it had a weird way of saving progress by nesting folders all named the same. I was watching a backup app once and thought, wtf, when I saw \Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\\Folder1\. I am pretty good with computers actually but still can get caught looking like a nob here and there. :-) I am one of the guys that was a diehard VB6 Classic user and when I upgraded to .NET 2002 I was instantly mad at the so called VB7 as it was nothing like the upgrade we were expecting or wanting. I got the $20 update for .NET 2003 and looked at it and stayed with VB6 Classic. I looked at Delphi, RealBASIC, and PowerBasic, and realized I like VB. Now I thought I better get caught up before Win8 starts messing with legacy apps. And as far as data base programming, I am still a noob. I read all the thick Access books years ago and spent a lot of time trying to learn but always reached a point where I got totally confused when it came to design and primary and foreign keys and relationship designer views. So here I go again. I don't give up easy. Thanks, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On 19/06/2011 8:03 PM, Simon Slavin wrote:
On 20 Jun 2011, at 12:58am, Lucas Cotta wrote:
I understand that for a query with a two tables join, SQLite will do a
nested loop join with these two tables.
But what about a query joining 5 tables?
It would be like this?:
for(table1 lines){
for(table2 lines){
for(table3 lines){
You can use JOIN any number of times in a SELECT query. SQLite will work out
an optimal way to access each table. You can speed things up a great deal by
having useful INDEXes created.
IIRC sqlite does *not* do any join ordering optimizations and simply
runs them in whatever order the query specifies. This can have
unfortunate effects on runtime for some queries. Can anyone verify this?
Ryan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On Mon, Jun 20, 2011 at 6:55 AM, Ryan Johnson ryanj...@ece.cmu.edu wrote: IIRC sqlite does *not* do any join ordering optimizations and simply runs them in whatever order the query specifies. This can have unfortunate effects on runtime for some queries. Can anyone verify this? The SQLite query optimizer started reordering joins beginning with SQLite version 3.2.3 in August 2005. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On 20/06/2011 6:59 AM, Richard Hipp wrote: On Mon, Jun 20, 2011 at 6:55 AM, Ryan Johnsonryanj...@ece.cmu.edu wrote: IIRC sqlite does *not* do any join ordering optimizations and simply runs them in whatever order the query specifies. This can have unfortunate effects on runtime for some queries. Can anyone verify this? The SQLite query optimizer started reordering joins beginning with SQLite version 3.2.3 in August 2005. Oh, good. Searching the docs, I found where I was led astray: http://www.sqlite.org/vdbe.html (granted, the page does say it's many years out of date, but I forgot where I'd read it). Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question, how multiple joins are executed
Hi!
I understand that for a query with a two tables join, SQLite will do a
nested loop join with these two tables.
But what about a query joining 5 tables?
It would be like this?:
for(table1 lines){
for(table2 lines){
for(table3 lines){
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On 20 Jun 2011, at 12:58am, Lucas Cotta wrote:
I understand that for a query with a two tables join, SQLite will do a
nested loop join with these two tables.
But what about a query joining 5 tables?
It would be like this?:
for(table1 lines){
for(table2 lines){
for(table3 lines){
You can use JOIN any number of times in a SELECT query. SQLite will work out
an optimal way to access each table. You can speed things up a great deal by
having useful INDEXes created.
If you find your SELECT growing too complicated it's a sign that you need to
re-think your database structure.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question
Hi! Are primary keys and foreign keys indexes by default? Or do we have to set them as indexes? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
Lucas Cotta lucco...@gmail.com wrote: Are primary keys and foreign keys indexes by default? Or do we have to set them as indexes? An index is automatically created to implement PRIMARY KEY and UNIQUE constraints. No indexes are automatically created for foreign key constraints. Columns mentioned in REFERENCES clause must already have a unique index of them (whether explicitly or implicitly as a result of PRIMARY KEY or UNIQUE constraints). Indexes (not necessarily unique) on the columns of the child table subject to FOREIGN KEY constrained are recommended, but not required. For details, see http://sqlite.org/foreignkeys.html#fk_indexes -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie Question
My goal is to eventually become a contractor and work from home. It seems as if SQLite will optimally facilitate my goal—am I corect?? I will greatly appreciate any light that anyone can shed on this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie Question
On 16 Apr 2011, at 6:18pm, Db Dvlpr wrote: My goal is to eventually become a contractor and work from home. It seems as if SQLite will optimally facilitate my goal—am I corect?? I will greatly appreciate any light that anyone can shed on this. SQLite is a database tool, and allows a programmer to do some (not all and not in all situations) tasks which involve storing and retrieving data. If you want to become a programming contractor, learn a whole bunch of tools for a whole bunch of platforms as well as you can spare the time for. You won't get much work if you stick to Visual C# for Windows. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie Question
Thanks, Simon. I wasn't getting that it's a tool. Thank you for the advice because as you know, I will need to be as marketable as possible. From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Sat, April 16, 2011 1:24:09 PM Subject: Re: [sqlite] Newbie Question On 16 Apr 2011, at 6:18pm, Db Dvlpr wrote: My goal is to eventually become a contractor and work from home. It seems as if SQLite will optimally facilitate my goal—am I corect?? I will greatly appreciate any light that anyone can shed on this. SQLite is a database tool, and allows a programmer to do some (not all and not in all situations) tasks which involve storing and retrieving data. If you want to become a programming contractor, learn a whole bunch of tools for a whole bunch of platforms as well as you can spare the time for. You won't get much work if you stick to Visual C# for Windows. Simon. ___ 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] Newbie question
Thanks, Igor Michael - your help is much appreciated.
Cheers
Kai
On Mon, Mar 21, 2011 at 4:47 AM, Igor Tandetnik itandet...@mvps.org wrote:
Kai Peters kpet...@otaksoft.com wrote:
given a table with two columns (SaleDate, SaleVolume) is it possible in one
query to obtain
the following three column result set:
SalesCurrentYear, SalesLastYEar, SalesAllyears
select
sum(SaleVolume * (SaleDate = StartOfCurYear)) SalesCurrentYear,
sum(SaleVolume * (StartOfLastYear = SaleDate and SaleDate
StartOfCurYear)) SalesLastYear,
sum(SaleVolume) SalesAllYears
from Sales,
(select
date('now', 'start of year') StartOfCurYear,
date('now', 'start of year', '-1 years') StartOfLastYear);
--
Igor Tandetnik
___
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] Newbie question
Try this.
BEGIN TRANSACTION;
CREATE TABLE sales(SaleDate date,SaleVolume int);
INSERT INTO sales VALUES('2010-01-01', 10);
INSERT INTO sales VALUES('2010-01-02', 20);
INSERT INTO sales VALUES('2011-01-01', 15);
INSERT INTO sales VALUES('2011-01-02', 30);
INSERT INTO sales VALUES('2009-01-01', 5);
INSERT INTO sales VALUES('2009-01-02', 10);
COMMIT;
select strftime('%Y',SaleDate) as year,total(SaleVolume),coalesce((select
total(SaleVolume) from sales),'dummy') from sales where SaleDate =
date('now','start of year','-1 year') group by year;
2010|30.0|90.0
2011|45.0|90.0
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Kai Peters [kpet...@otaksoft.com]
Sent: Sunday, March 20, 2011 11:10 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Newbie question
Hi all,
given a table with two columns (SaleDate, SaleVolume) is it possible in one
query to obtain
the following three column result set:
SalesCurrentYear, SalesLastYEar, SalesAllyears
?
Thanks as always for any help,
Kai
___
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] Newbie question
Kai Peters kpet...@otaksoft.com wrote:
given a table with two columns (SaleDate, SaleVolume) is it possible in one
query to obtain
the following three column result set:
SalesCurrentYear, SalesLastYEar, SalesAllyears
select
sum(SaleVolume * (SaleDate = StartOfCurYear)) SalesCurrentYear,
sum(SaleVolume * (StartOfLastYear = SaleDate and SaleDate
StartOfCurYear)) SalesLastYear,
sum(SaleVolume) SalesAllYears
from Sales,
(select
date('now', 'start of year') StartOfCurYear,
date('now', 'start of year', '-1 years') StartOfLastYear);
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question
Hi all, given a table with two columns (SaleDate, SaleVolume) is it possible in one query to obtain the following three column result set: SalesCurrentYear, SalesLastYEar, SalesAllyears ? Thanks as always for any help, Kai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question - SQLite the best choice?
Hello! I think your structure is not good enough. You may use cookie-based database selection instead and doesn't store user information into your Master DB. The algorithm is like to: Username - user_id - check password by user_id database As example: User enter USERNAME and PASSWORD and we check PASSWORD in database file md5(USERNAME).db You may block brutforces in your application. For valid users performance is usually fine by this schema. P.S. For javascript auto-complete of user names is possible to build list of usernames periodically. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question - SQLite the best choice?
Hi Folks, Please forgive me if this isn't the proper forum to post this question but I am in need of some expert advice concerning if SQLite is the best choice for my application. Our application could have up to 10,000 users via a public facing web site. As a first stab at the schema, I thought I'd have one Master User database which will probably look like this: *Master DB *- (would contain contact info for all of the Users) user_ID - PRIMARY KEY last_name first_name email street_addr1 street_addr2 city state zip date_signed_up date_to_renew username password If I use SQLite, there would be another database that contains user setup data that describes the various different ways that their page would be displayed, each user could have up to 100 subjectsand then each subject could have 10-25 events. So this database could look like this: *User DB* - each user gets one of these that includes 3 tables (user_setup, subjects, events) TABLE_USER_SETUP user_id (FOREIGN KEY) setup_id data1 data2 data3 data4 TABLE_SUBJECTS subject_id PRIMARY KEY last_name first_name email street_addr1 street_addr2 city state zip password TABLE_EVENTS events_id PRIMARY KEY type data subject_id Can I use the user_id (as a FOREIGN KEY) that resides in another database to link the two DBs together? I'm a newbie to all of theis database design so I am using this project as a learning experience. From my limited knowledge of MySQL I think I could have everything in one database and just have a bunch of tables. If I use SQLite I'd have a bunch of files (or smaller databases). If there was a problem I can see just losing one ueser's data and not the whole ball of data with all the user's data. You folks are the experts and I'd like to hear what you recommend. Thanks, Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question - SQLite the best choice?
Richard Cooke wrote: Can I use the user_id (as a FOREIGN KEY) that resides in another database to link the two DBs together? I'm a newbie to all of theis database design so I am using this project as a learning experience. From my limited knowledge of MySQL I think I could have everything in one database and just have a bunch of tables. If I use SQLite I'd have a bunch of files (or smaller databases). If there was a problem I can see just losing one ueser's data and not the whole ball of data with all the user's data. This doesn't answer your main question exactly, but ... A general point of database design is that whenever you have a bunch of entities where some of those entities are defined in terms of others, including some being constrained in terms of others, such as with a FOREIGN KEY, then it only makes sense to have all of those entities collected into the same single database. With SQLite, that means put everything in a single file. The main reason for this is that you can then take any single database in isolation from others and it is self-describing enough that you can properly interpret what you see in that database, including what constraints it has. With SQLite, where its files can be located anywhere, there is no system restriction keeping your related files together, so you don't want to use them such that no part of your database can be used if you easily don't have all the parts. In your case, I would put everything, all your tables, in a single SQLite database file, if you use SQLite, same as you'd use a single database if you use PostgreSQL or other options instead. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question - SQLite the best choice?
On Sat, 6 Mar 2010, Richard Cooke wrote: Our application could have up to 10,000 users via a public facing web site. As a first stab at the schema, I thought I'd have one Master User database which will probably look like this: Richard, If I recall correctly, SQLite does not do well with multiple, simultaneous access. I suggest that you take a look here: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork and look at the multiuser/network client-server tools built on top of SQLite3. For our needs, we use SQLite as the embedded back end to single-user applications and postgres or mysql for multiuser and Web-based apps. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie Question about ASCII and UTF-16 strings
On 10/05/2009 3:29 PM, kalyan@aol.in wrote: Hi, I am a newbie to sqlite. I want to create a sqlite database in Linux OS. The data that I wish to put in the table contains both ASCII and UTF-16 encoded strings. For eg. File and directory names are UTF-16 where the URL, date time information about the files are in ASCII. How do I create a table that holds both ASCII and UTF-16 strings. The documentation says that TEXT datatype can hold both the encodings, if i do a sqlite3_open - TEXT is only ASCII, UTF-8 if i do a sqlite3_open16 - TEXT is only UTF-16 AFAICT ... and I am by no means omniscient :-) ... the story is something like this: There are THREE possibilities for how the data is declared to be stored. They are: UTF-8, UTF-16LE, UTF-16BE. You must choose ONE of the three. The choice is made when a database is created and is applied to the whole database. There is no way of making a choice per table. When you subsequently feed data into SQLite, you have two [1] choices: (a) you say that you are supplying bytes that are encoded in UTF-8 (b) you say that you are supplying wide chars that are encoded in the UTF-16xE that is appropriate to the endianness of the machine that you are running on. If the choice differs from that recorded in the database, SQLite will attempt to convert your data for you. If the declaration(s) that you make implicitly is/are untrue e.g. your data is encoded in ISO 8859-xx but you say that it is encoded in UTF-8, you may not get any error message. SQLite3 does little checking. Which choice you make for database storage would depend on two things: (1) the time required for any transcoding (1.1) by SQLite automatically as described above, and/or (1.2) by you to convert raw input into the encoding that you will use to feed your data to SQLite (2) the resultant size of the database: [following assumes all of your data is in the range U+ to U+] If stored in UTF-16xE, each character will occupy 2 bytes. If stored in UTF-8, the bytes per char is variable: U+ to U+007F (ASCII) - 1 byte per char U+0080 to U+07FF (covers e.g. accented-and-otherwise-adorned Latin letters, Greek, Cyrillic, Hebrew, Arabic) - 2 bytes per char U+0800 to U+ (covers e.g. Devanagari and other Indian languages, Chinese, Japanese, Korean) - 3 bytes per char So it's impossible to say whether UTF-8 would be better/same/worse without examining your actual data. You could write a script that loaded a typical data mix into a database, and run it with the database encoding specified as either UTF-8 or UTF-16xE and compare the resultant sizes (and time difference). [1]: If you are using a wrapper like e.g the Python sqlite3 module, your choices may be different. Possibilities include: you supply a unicode object or you supply a str object (8-bit characters) plus an encoding (which may default to UTF-8) and the wrapper will do whatever is necessary. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++)
Vinnie pisze: From: wiktor siri...@o2.pl Subject: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++) I'm trying to build a win form application that uses sqlite. I have problems with making it work. I would like to have the sqlite source included in my project (as .h file or dll) - sth similar to (but done by a function) http://www.sqlite.org/quickstart.html. As I have read on internet sources it shall be possible. I have never used Windows Forms but from what I understand it is a user interface toolkit for .NET. So you will need to access SQLite from .NET. There are a few ways of going about this. Here is one .NET wrapper for SQLite: http://www.phpguru.org/static/SQLite.NET.html The SQLite website has some instructions for building SQLite with Visual Studio .NET: http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Hope this helps! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hi Vinnie, thank you for your links. Unfortunately, I am not familiar with .NET and looking for sht more C++ like ;) Here is the solution which I was provided elsewhere: http://support.microsoft.com/kb/920770 and after installing the hotfix you shall use this link: http://news.thedigitalmachine.com/2007/03/28/set-up-sqlite-static-lib-and-a-c-wrapper-in-15-minutes/comment-page-1/#comment-12136 In my machine everything works fine. Since, I am a begginer in sqlite, can you tell me what is the role of wrappers? From what I have read at sqlite.org it is possible to fetch the data from database by executing sqlite_open, sqlite_exec and sqlite_close.Am I wrong? Regards, Wiktor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question about using SQLite with Window s Forms application (VS 2005 C++)
Hi, I'm trying to build a win form application that uses sqlite. I have problems with making it work. I would like to have the sqlite source included in my project (as .h file or dll) - sth similar to (but done by a function) http://www.sqlite.org/quickstart.html. As I have read on internet sources it shall be possible. Can anyone provide me with step by step guide on how to do it in Visual Studio C++ 2005? Regards, Wiktor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++)
From: wiktor siri...@o2.pl Subject: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++) I'm trying to build a win form application that uses sqlite. I have problems with making it work. I would like to have the sqlite source included in my project (as .h file or dll) - sth similar to (but done by a function) http://www.sqlite.org/quickstart.html. As I have read on internet sources it shall be possible. I have never used Windows Forms but from what I understand it is a user interface toolkit for .NET. So you will need to access SQLite from .NET. There are a few ways of going about this. Here is one .NET wrapper for SQLite: http://www.phpguru.org/static/SQLite.NET.html The SQLite website has some instructions for building SQLite with Visual Studio .NET: http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Hope this helps! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question about creating tables
My background is with SQL Server and Delphi, so the concepts in SQLite are new for me. I have a TDISQLite3Database component in my TDataModule with the DatabaseName set to Demo. Since I am familiar with the TDataset and other Delphi components, I would like to use those (at the expense of size and efficiency). What I need to do now is learn how to create Demo and add a table, Members. Ultimately, there will be tables in SQLite that will be synchronized with the tables on a server. So far I have not been able to find an example of creating the tables using DISQLite3 and the Delphi components. All suggestions are welcomed... Todd -- Ariste Software Petaluma, CA 94952 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
In the first query the (select id from tbl2 where name='Joe') is referred to as a SUBQUERY. The second query is uses a JOIN because you are joining together data from different rows. -Jeff -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: Wednesday, March 18, 2009 12:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Hoover, Jeffrey jhoo...@jcvi.org: Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id; if there may be many rows in tbl2 where name =- 'Joe' then SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id in (select id from tbl2 where name='Joe'); Both of the top 2 worked. Thank you. I'll try and stick to the less ambiguous form and bear in mind all the comments about single quotes. A bit more information though. Is there a term for that type of SELECT statement? Thanx, Dp. ___ 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] Newbie question
Hi, I am very green with SQL entirely so I apologise in advance for what might be a simple query. I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. I am not sure what the term is for such as statement, compound perhaps? TIA, Dp. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
Dermot paik...@googlemail.com wrote: I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. What's the text of the error message? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
2009/3/18 Igor Tandetnik itandet...@mvps.org: Dermot paik...@googlemail.com wrote: I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. What's the text of the error message? SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This attempt give the error: SQL error: near =: syntax error SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id='select id from tbl2 where name=Joe'; This return nothing, although it should. If I break the queries into 2 separate select statements they verify. This is SQLite 3.5.1. Thanx, Dp. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
I'm relatively newbie too, but just curious, is it anything to do with (select id from tbl2 where name=Joe) not being guarenteed to return a scaler? Ie only a single value? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: 18 March 2009 15:47 To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Igor Tandetnik itandet...@mvps.org: Dermot paik...@googlemail.com wrote: I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. What's the text of the error message? SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This attempt give the error: SQL error: near =: syntax error SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id='select id from tbl2 where name=Joe'; This return nothing, although it should. If I break the queries into 2 separate select statements they verify. This is SQLite 3.5.1. Thanx, Dp. ___ 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] Newbie question
Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id; if there may be many rows in tbl2 where name =- 'Joe' then SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id in (select id from tbl2 where name='Joe'); -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: Wednesday, March 18, 2009 11:47 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Igor Tandetnik itandet...@mvps.org: Dermot paik...@googlemail.com wrote: I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. What's the text of the error message? SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This attempt give the error: SQL error: near =: syntax error SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id='select id from tbl2 where name=Joe'; This return nothing, although it should. If I break the queries into 2 separate select statements they verify. This is SQLite 3.5.1. Thanx, Dp. ___ 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] Newbie question
I think its because sqlite (and most rdbms's) expect literal strings to be enclose in ' not . I think sybase is an except, accepting either. In SQLite you use to enclose table/column names that contain non0standard characters or where object id is case sensitive, such as select grant# from Current_Projects; -Jeff -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hardy, Andrew Sent: Wednesday, March 18, 2009 11:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question I'm relatively newbie too, but just curious, is it anything to do with (select id from tbl2 where name=Joe) not being guarenteed to return a scaler? Ie only a single value? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: 18 March 2009 15:47 To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Igor Tandetnik itandet...@mvps.org: Dermot paik...@googlemail.com wrote: I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. What's the text of the error message? SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This attempt give the error: SQL error: near =: syntax error SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id='select id from tbl2 where name=Joe'; This return nothing, although it should. If I break the queries into 2 separate select statements they verify. This is SQLite 3.5.1. Thanx, Dp. ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
SQLite treats double-quoted strings as column and table identifiers. Use single-quotes for literals. SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); -Clark - Original Message From: Dermot paik...@googlemail.com To: sqlite-users@sqlite.org Sent: Wednesday, March 18, 2009 8:35:52 AM Subject: [sqlite] Newbie question Hi, I am very green with SQL entirely so I apologise in advance for what might be a simple query. I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description=someval AND foreign_key_id=(select id from tbl2 where name=Joe); This gives me a syntax error and my other efforts are not yielding results. I am not sure what the term is for such as statement, compound perhaps? TIA, Dp. ___ 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] Newbie question
2009/3/18 Hoover, Jeffrey jhoo...@jcvi.org: Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id; if there may be many rows in tbl2 where name =- 'Joe' then SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id in (select id from tbl2 where name='Joe'); Both of the top 2 worked. Thank you. I'll try and stick to the less ambiguous form and bear in mind all the comments about single quotes. A bit more information though. Is there a term for that type of SELECT statement? Thanx, Dp. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
according to this http://www.sqlite.org/lang_keywords.html both the single and double quotes should have worked. (the single quotes are preferred) On Wed, Mar 18, 2009 at 9:04 AM, Dermot paik...@googlemail.com wrote: 2009/3/18 Hoover, Jeffrey jhoo...@jcvi.org: Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id; if there may be many rows in tbl2 where name =- 'Joe' then SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id in (select id from tbl2 where name='Joe'); Both of the top 2 worked. Thank you. I'll try and stick to the less ambiguous form and bear in mind all the comments about single quotes. A bit more information though. Is there a term for that type of SELECT statement? Thanx, Dp. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jim Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
On Wed, 18 Mar 2009 16:04:51 +, Dermot paik...@googlemail.com wrote: 2009/3/18 Hoover, Jeffrey jhoo...@jcvi.org: Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); subselect (If there's more than one 'Joe', only the first 'Joe' is used) this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and tbl2.id=tbl1.foreign_key_id; implicit join In this case you could also write: SELECT tbl1.* FROM tbl1 INNER JOIN tbl2 ON tbl2.id=tbl1.foreign_key_id WHERE tbl1.description='someval' AND tbl2.name='Joe'; which is an explicit join. if there may be many rows in tbl2 where name =- 'Joe' then SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id in (select id from tbl2 where name='Joe'); 'IN subselect set' (I'm making this one up). Both of the top 2 worked. Thank you. I'll try and stick to the less ambiguous form and bear in mind all the comments about single quotes. A bit more information though. Is there a term for that type of SELECT statement? See above. Thanx, Dp. HTH -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] newbie question regarding my sqlite code
Hello, I am a new sqilte user and I am learning sqlite code in my spare time.. I have always used sql code with Microsoft Access and Base (openoffice). Therefore, sorry to ask a question very simple . When I write the very simple code: select sum(età) from dipendenti everything works fine and the result is 100. When I try a bit longer query the result for the sum regarding the column età from the table dipendenti changes and it is wrong. That is 25200? (instead of the right value 100!). The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti In the above query the avg results for the column age (table acoda) and the column durata (table main) are right. The only value wrong is the third, that it, sum (for the table dipendenti, column age, 25200 instead of the right value 100). The column age in the table dipendenti is not present in the other two tables (acoda, main). What's wrong with the second query? Thanks in advance. Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question regarding my sqlite code
Try this: select avg(age) from acoda union all select avg(durata) from main union all select sum(età) from dipendenti RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of silvio grosso Sent: 10 January 2009 10:41 To: sqlite-users@sqlite.org Subject: [sqlite] newbie question regarding my sqlite code Hello, I am a new sqilte user and I am learning sqlite code in my spare time.. I have always used sql code with Microsoft Access and Base (openoffice). Therefore, sorry to ask a question very simple . When I write the very simple code: select sum(età) from dipendenti everything works fine and the result is 100. When I try a bit longer query the result for the sum regarding the column età from the table dipendenti changes and it is wrong. That is 25200? (instead of the right value 100!). The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti In the above query the avg results for the column age (table acoda) and the column durata (table main) are right. The only value wrong is the third, that it, sum (for the table dipendenti, column age, 25200 instead of the right value 100). The column age in the table dipendenti is not present in the other two tables (acoda, main). What's wrong with the second query? Thanks in advance. Best regards ___ 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] newbie question regarding my sqlite code
Am Samstag, 10. Januar 2009 schrieb silvio grosso: The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti This is cross join over all three tables. The result is a monster table, consisting of every possible combination of the records of those three tables. And in this monster table the sum most probably is correct. Please read your SQL-documention about different types of joins and what they are good for. For debugging purposes you can have a look at select * from acoda, main, dipendenti to see the raw result of your join. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] newbie question
hi, the SCM tool Subversion is going to start using sqlite in the upcoming 1.6 release to manage client metadata. would it be possible for us to also that sqlite install as part of some custom code we write around subversion? we'd like to be able to cache the list of managed object in the working copy with sqlite rather than require an svn ls or svn info every time we want to interact with that object. anyone see a problem with us piggy backing on the svn sqlite client install? TIA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Listman wrote: anyone see a problem with us piggy backing on the svn sqlite client install? You should be asking the Subversion folks. I'd assume they would want you to ensure your tables have their own namespace prefix. You will also want to find out if they ever wipe the database. Finally make sure your code doesn't leave open transactions for long periods of time as SQLite can only have one transaction open for writing at a time, blocking/queuing others wanting to write. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFI9C1emOOfHg372QQRAqI/AKDQy/vYYBGrIYHZYNuCH6SauBCbMQCgyOP6 aAq+aGisFbGOiLI3VNIeYG4= =Igho -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
Dennis Cote wrote: sqlfan wrote: I'm booking five resources, and right now I just use an excel sheet with all the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five columns are the five resources. When I book one, I just scroll to the appropriate date and change the color of the column for that resource for all the dates it's booked for, and then I put any information about the booking (who booked it, their e-mail address if it's someone I don't know, whether it has been paid) somewhere in the colored area. I'd like to start using a database instead, but I'm very very new and I don't know how I should structure it. I think if I do it correctly I think I could check much more easily whether anything is available for a date range, without having to scroll through my excel sheet to find that date and look, which is a pain because it's kind of long. It seems most databases are much more complicated than mine though -- for example, I don't even have any structure to the extra information I put about a booking, I sometimes just put a name like Joe for conference How should the database structure look. Am I even doing the right thing by wanting to use a database? I'm very very new and kind of lost, any help would be appreciated. Thank you!! Here is a sample database to do what you want. It uses date and time strings to store the begin and end time of each booking. You could change these to hold only dates if you don't need bookings that last less than a day. The first check constraint ensures that the bookings begin and end times are correctly ordered. I added a nice check constraint to ensure you couldn't add a booking that conflicted with an existing booking, but SQLite doesn't support subqueries in check constraints, so I had to remove it (by commenting it out). Your application will need to perform this check using a query before inserting new bookings. There is a sample query that will return true if there is an existing booking that conflicts with a proposed new booking. The last query shows how to find all the bookings at a given time. HTH Dennis Cote -- resources that can be booked create table resource ( resource_id integer primary key, resource_name text unique ); insert into resource values (1, 'resource 1'); insert into resource values (2, 'resource 2'); insert into resource values (3, 'resource 3'); insert into resource values (4, 'resource 4'); insert into resource values (5, 'resource 5'); -- bookings table create table booking ( booking_id integer primary key, resource_id integer references resource, begins datetime, ends datetime, info text, check (begins ends) /*, -- unsupported check constraint commented out since -- SQLite doesn't allow subqueries in check constraints check (not exists (select * from booking as b where resource_id = b.resource_id and ((begins b.ends and ends = b.ends) or (begins = b.begins and ends b.begins)) )) */ ); -- index to speed searches create index time_idx on booking(begins, ends); insert into booking values (null, 1, '2008-04-07 09:00:00', '2008-04-10 04:00:00', 'test 1'); insert into booking values (null, 2, '2008-04-08 09:00:00', '2008-04-09 04:00:00', 'test 2'); -- begins ends constraint violation insert into booking values (null, 2, '2008-04-14 09:00:00', '2008-04-13 04:00:00', 'test 3'); /* -- overlap with existing booking violations would be caught by unsupported constraint insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11 04:00:00', 'test 3'); insert into booking values (null, 2, '2008-04-07 09:00:00', '2008-04-09 10:00:00', 'test 4'); insert into booking values (null, 2, '2008-04-06 09:00:00', '2008-04-12 10:00:00', 'test 5'); */ -- query to check for conflict with existing bookings -- application must use this to check for conflicts prior to inserting new bookings select exists (select * from booking as b where :resource_id = b.resource_id and ((:begins b.ends and :ends = b.ends) or (:begins = b.begins and :ends b.begins)) ); -- report all bookings at a given time select * from booking where :some_time between begins and ends order by resource; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Thank you so so so so so so so much So much. Even when I have your code in front of me, it's still very very hard for me to understand it. This is hard stuff! So much harder than a spreadsheet, but hopefully it will work out in the end, because I'm programming in Python so I should have a lot of power... Here are some basic questions: what is the null that
Re: [sqlite] newbie question about laying out a simple database for bookings
sqlfan wrote:
Thank you so so so so so so so much So much. Even when I have your
code in front of me, it's still very very hard for me to understand it.
This is hard stuff! So much harder than a spreadsheet, but hopefully it
will work out in the end, because I'm programming in Python so I should have
a lot of power...
You're welcome. I think you will have fun with Python.
Here are some basic questions: what is the null that you insert into
Booking as the first column of the test records??? You don't insert it into
the resource records, even though it seems to me they have the same
structure!
(their first column is a unique integer resource_id and booking_id
respectively... but then you insert into the first by
insert into resource values (1, 'resource 1');
and the second by
insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11
04:00:00', 'test 3')
Null is an unspecified or unknown value. For columns declared as
integer primary key SQLite will assign a unique integer value when you
insert null.
Yes, I could have inserted null's into the resource table as well, and
SQLite would have assigned the same id numbers I inserted manually.
I tend to use manual id numbers when I have a known fixed number of
entries, and I want to refer to them directly by that id number. If I
let SQLite assign the numbers, then I would, in general, have to do a
lookup using the resource name to get the id number that had been
assigned. A typical insert into the booking table would then look like this:
insert into booking values (null,
(select resource_id from resource
where resource_name = 'resource 1'),
'2008-04-14 09:00:00', '2008-04-13 04:00:00', 'test 3');
where the resource id is replaced by a sub-select that retrieves the id
assigned to the resource.
2) I'd like to prepare an HTML table by dates instead of by resource...
How can I pivot the table so that each row I fetch to add to the table
starts with a date in the first column... This way, my presentation could be
similar to my current spreadsheet, which is really quite useful. I'd just
like it not to be so dumb. So, if I'm looking at June 12-18, for example,
I'd like
Resource1
2008-06-12 no booking
2008-06-13 no booking
2008-06-14 booking #72
2008-06-15 booking #72
2008-06-16 no booking
2008-06-17 booking #73
2008-06-18 no booking
I'm just showing 1 resource, because I don't know if my ascii art table
here will get mangled in transmission, but you can imagine I'd like the rest
of the columns to be the other resources.
How could I get this view?
This is a little trickier. :-)
First you need to generate a temporary table with the range of dates
that you want to report. This will create a temporary table with 32 day
starting from the current date.
create temp table date_range(
id integer primary key,
date datetime
);
insert into date_range values(1, date('now'));
insert into date_range select null, date from date_range; --x2
insert into date_range select null, date from date_range; --x4
insert into date_range select null, date from date_range; --x8
insert into date_range select null, date from date_range; --x16
insert into date_range select null, date from date_range; --x32
update date_range
set date = date(julianday(date) + id - 1);
Next you need to join this table to the booking table several times,
once for each column in the output.
select d.date as date,
r1.info as 'resource 1',
r2.info as 'resource 2',
r3.info as 'resource 3',
r4.info as 'resource 4',
r5.info as 'resource 5'
from date_range as d
left join booking as r1 on r1.resource_id = 1
and d.date between r1.begins and r1.ends
left join booking as r2 on r2.resource_id = 2
and d.date between r2.begins and r2.ends
left join booking as r3 on r3.resource_id = 3
and d.date between r3.begins and r3.ends
left join booking as r4 on r4.resource_id = 4
and d.date between r4.begins and r4.ends
left join booking as r5 on r5.resource_id = 5
and d.date between r5.begins and r5.ends
order by d.date;
If you execute this in the shell you can set the mode to column and turn
on the headers to get a nicely formatted table. The following trace
shows this query in action.
sqlite insert into booking values (null, 1,
... '2008-04-07', '2008-04-10', 'test 1');
sqlite insert into booking values (null, 2,
... '2008-04-08', '2008-04-09', 'test 2');
sqlite insert into booking values (null, 4,
... '2008-04-08', '2008-04-12', 'test 3');
sqlite insert into booking values (null, 5,
... '2008-04-15', '2008-04-20', 'test 4');
sqlite insert into booking values (null, 1,
... '2008-04-14', '2008-04-18', 'test
Re: [sqlite] newbie question about laying out a simple database for bookings
On 4/7/08, sqlfan [EMAIL PROTECTED] wrote: I'm booking five resources, and right now I just use an excel sheet with all the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five columns are the five resources. When I book one, I just scroll to the appropriate date and change the color of the column for that resource for all the dates it's booked for, and then I put any information about the booking (who booked it, their e-mail address if it's someone I don't know, whether it has been paid) somewhere in the colored area. I'd like to start using a database instead, but I'm very very new and I don't know how I should structure it. I think if I do it correctly I think I could check much more easily whether anything is available for a date range, without having to scroll through my excel sheet to find that date and look, which is a pain because it's kind of long. It seems most databases are much more complicated than mine though -- for example, I don't even have any structure to the extra information I put about a booking, I sometimes just put a name like Joe for conference How should the database structure look. Am I even doing the right thing by wanting to use a database? I'm very very new and kind of lost, any help would be appreciated. Thank you!! HI sqlfan, You may benefit from a database, but for something as simple as what you are wanting to do, you might simply get more done by using a simpler, readymade program. I work on a Mac, so I don't know about other programs, but there a countless journals and diaries type of programs that exist on the Mac. Check out macupdate.com and search for keywords like journal or personal information manager. On the Mac, most of these programs actually utilize SQLite as their datastore, but for the user, for you, the interface is really easy, readymade, attractive... best of all, most of these programs are very inexpensive once you decide to buy one of them... $20 to $40 for a license. You will support shareware, you will get your work done, and you will be using SQLite without even realizing it. On the other hand, if your future needs are very ambitious, and you are determined to roll your own db-based solution, I suggest you search for free SQL tutorials on the web... there are countless. Once you go through them, you will be better equipped to decide if you want to build your own. At that time you can use this SQLite list to ask SQLite-specific questions. Good luck, but I do recommend a readymade solution for the sake of getting work done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
sqlfan wrote: I'm booking five resources, and right now I just use an excel sheet with all the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five columns are the five resources. When I book one, I just scroll to the appropriate date and change the color of the column for that resource for all the dates it's booked for, and then I put any information about the booking (who booked it, their e-mail address if it's someone I don't know, whether it has been paid) somewhere in the colored area. I'd like to start using a database instead, but I'm very very new and I don't know how I should structure it. I think if I do it correctly I think I could check much more easily whether anything is available for a date range, without having to scroll through my excel sheet to find that date and look, which is a pain because it's kind of long. It seems most databases are much more complicated than mine though -- for example, I don't even have any structure to the extra information I put about a booking, I sometimes just put a name like Joe for conference How should the database structure look. Am I even doing the right thing by wanting to use a database? I'm very very new and kind of lost, any help would be appreciated. Thank you!! Here is a sample database to do what you want. It uses date and time strings to store the begin and end time of each booking. You could change these to hold only dates if you don't need bookings that last less than a day. The first check constraint ensures that the bookings begin and end times are correctly ordered. I added a nice check constraint to ensure you couldn't add a booking that conflicted with an existing booking, but SQLite doesn't support subqueries in check constraints, so I had to remove it (by commenting it out). Your application will need to perform this check using a query before inserting new bookings. There is a sample query that will return true if there is an existing booking that conflicts with a proposed new booking. The last query shows how to find all the bookings at a given time. HTH Dennis Cote -- resources that can be booked create table resource ( resource_id integer primary key, resource_name text unique ); insert into resource values (1, 'resource 1'); insert into resource values (2, 'resource 2'); insert into resource values (3, 'resource 3'); insert into resource values (4, 'resource 4'); insert into resource values (5, 'resource 5'); -- bookings table create table booking ( booking_id integer primary key, resource_id integer references resource, begins datetime, ends datetime, info text, check (begins ends) /*, -- unsupported check constraint commented out since -- SQLite doesn't allow subqueries in check constraints check (not exists (select * from booking as b where resource_id = b.resource_id and ((begins b.ends and ends = b.ends) or (begins = b.begins and ends b.begins)) )) */ ); -- index to speed searches create index time_idx on booking(begins, ends); insert into booking values (null, 1, '2008-04-07 09:00:00', '2008-04-10 04:00:00', 'test 1'); insert into booking values (null, 2, '2008-04-08 09:00:00', '2008-04-09 04:00:00', 'test 2'); -- begins ends constraint violation insert into booking values (null, 2, '2008-04-14 09:00:00', '2008-04-13 04:00:00', 'test 3'); /* -- overlap with existing booking violations would be caught by unsupported constraint insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11 04:00:00', 'test 3'); insert into booking values (null, 2, '2008-04-07 09:00:00', '2008-04-09 10:00:00', 'test 4'); insert into booking values (null, 2, '2008-04-06 09:00:00', '2008-04-12 10:00:00', 'test 5'); */ -- query to check for conflict with existing bookings -- application must use this to check for conflicts prior to inserting new bookings select exists (select * from booking as b where :resource_id = b.resource_id and ((:begins b.ends and :ends = b.ends) or (:begins = b.begins and :ends b.begins)) ); -- report all bookings at a given time select * from booking where :some_time between begins and ends order by resource; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
I guess I fit in the more ambitious category then. I hope my workload will increase at any rate. Anyway I've looked at some tutorials, but I'm a bit daunted. What do you think about this layout: [integer booking # (=unique ID)] - [integer start date] - [integer end date] - [text everything else about the booking] 1 - 2 - 3 - 4 - 5 - 6 - 7 - [...] then it seems I can derive the other views, such as a view that is by date 2008-04-06 [free] [free] [Joe for conference] [free] [free] Is that right? Do I need any other tables other than the one above that I derive the other views from? Thank you! P Kishor-3 wrote: On 4/7/08, sqlfan [EMAIL PROTECTED] wrote: I'm booking five resources, and right now I just use an excel sheet with all the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five columns are the five resources. When I book one, I just scroll to the appropriate date and change the color of the column for that resource for all the dates it's booked for, and then I put any information about the booking (who booked it, their e-mail address if it's someone I don't know, whether it has been paid) somewhere in the colored area. I'd like to start using a database instead, but I'm very very new and I don't know how I should structure it. I think if I do it correctly I think I could check much more easily whether anything is available for a date range, without having to scroll through my excel sheet to find that date and look, which is a pain because it's kind of long. It seems most databases are much more complicated than mine though -- for example, I don't even have any structure to the extra information I put about a booking, I sometimes just put a name like Joe for conference How should the database structure look. Am I even doing the right thing by wanting to use a database? I'm very very new and kind of lost, any help would be appreciated. Thank you!! HI sqlfan, You may benefit from a database, but for something as simple as what you are wanting to do, you might simply get more done by using a simpler, readymade program. I work on a Mac, so I don't know about other programs, but there a countless journals and diaries type of programs that exist on the Mac. Check out macupdate.com and search for keywords like journal or personal information manager. On the Mac, most of these programs actually utilize SQLite as their datastore, but for the user, for you, the interface is really easy, readymade, attractive... best of all, most of these programs are very inexpensive once you decide to buy one of them... $20 to $40 for a license. You will support shareware, you will get your work done, and you will be using SQLite without even realizing it. On the other hand, if your future needs are very ambitious, and you are determined to roll your own db-based solution, I suggest you search for free SQL tutorials on the web... there are countless. Once you go through them, you will be better equipped to decide if you want to build your own. At that time you can use this SQLite list to ask SQLite-specific questions. Good luck, but I do recommend a readymade solution for the sake of getting work done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/newbie-question-about-laying-out-a-simple-database-for-bookings-tp16537380p16543002.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
On 4/7/08, sqlfan [EMAIL PROTECTED] wrote: I guess I fit in the more ambitious category then. I hope my workload will increase at any rate. Anyway I've looked at some tutorials, but I'm a bit daunted. What do you think about this layout: [integer booking # (=unique ID)] - [integer start date] - [integer end date] - [text everything else about the booking] 1 - 2 - 3 - 4 - 5 - 6 - 7 - [...] then it seems I can derive the other views, such as a view that is by date 2008-04-06 [free] [free] [Joe for conference] [free] [free] Is that right? Do I need any other tables other than the one above that I derive the other views from? Thank you! Did you see the most incredibly detailed and helpful reply that Dennis Cote provided? You owe him a beer (or a free ticket to one of your bookings). He has done most of the work you want. You can take that as the db structure and run with it. Good luck. P Kishor-3 wrote: On 4/7/08, sqlfan [EMAIL PROTECTED] wrote: I'm booking five resources, and right now I just use an excel sheet with all the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five columns are the five resources. When I book one, I just scroll to the appropriate date and change the color of the column for that resource for all the dates it's booked for, and then I put any information about the booking (who booked it, their e-mail address if it's someone I don't know, whether it has been paid) somewhere in the colored area. I'd like to start using a database instead, but I'm very very new and I don't know how I should structure it. I think if I do it correctly I think I could check much more easily whether anything is available for a date range, without having to scroll through my excel sheet to find that date and look, which is a pain because it's kind of long. It seems most databases are much more complicated than mine though -- for example, I don't even have any structure to the extra information I put about a booking, I sometimes just put a name like Joe for conference How should the database structure look. Am I even doing the right thing by wanting to use a database? I'm very very new and kind of lost, any help would be appreciated. Thank you!! HI sqlfan, You may benefit from a database, but for something as simple as what you are wanting to do, you might simply get more done by using a simpler, readymade program. I work on a Mac, so I don't know about other programs, but there a countless journals and diaries type of programs that exist on the Mac. Check out macupdate.com and search for keywords like journal or personal information manager. On the Mac, most of these programs actually utilize SQLite as their datastore, but for the user, for you, the interface is really easy, readymade, attractive... best of all, most of these programs are very inexpensive once you decide to buy one of them... $20 to $40 for a license. You will support shareware, you will get your work done, and you will be using SQLite without even realizing it. On the other hand, if your future needs are very ambitious, and you are determined to roll your own db-based solution, I suggest you search for free SQL tutorials on the web... there are countless. Once you go through them, you will be better equipped to decide if you want to build your own. At that time you can use this SQLite list to ask SQLite-specific questions. Good luck, but I do recommend a readymade solution for the sake of getting work done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question re using SQLite in basic client/server situation
Hi all I'm looking at switching the database that I use for my XCode/Cocoa application from MySQL to SQLite. (I'm getting tired of all the incompatibilities with new versions of MySQL). But I need to be able to use it as a server with at most 10 clients that occasionally use the system. I noticed in the documentation that it said the following so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. From users' experience, is SQLite likely to be safe with so few clients on a local network. Also how do I login to the SQLite database from a client app? Cheers Jeff Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question re using SQLite in basic client/server situation
En/na Jeff Brown ha escrit: But I need to be able to use it as a server with at most 10 clients that occasionally use the system. Hi, I'm a newbie myself, so I didn't use the following, but did you check http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork ? Bye -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question re using SQLite in basic client/serversituation
Not sure on the locking issue, but some of it might have to do with whether the database file is being provided via a NFS or a Windows Network Filesystem. Sometimes file-level locking is not real robust on networked file systems. If you are using some kind of ODBC/ADO provider then the details for that would be what counts. Not sure what cocoa is, but it sounds fairly modern so you are probably able to go through an ODBC connection or provider of some sort, in which case youwould just configure the SQLITE connectivity layer you are using. As far as SQLITE itself goes, there is no database server to 'log into', all you are doing is providing a file name ( the file for which has to be visible to the client program ) to the connect function. If you want a more usual client/server situation, youd have to use ODBC or ADO, else you'd have to write it yourself. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Brown Sent: Friday, March 07, 2008 4:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] Newbie question re using SQLite in basic client/serversituation Hi all I'm looking at switching the database that I use for my XCode/Cocoa application from MySQL to SQLite. (I'm getting tired of all the incompatibilities with new versions of MySQL). But I need to be able to use it as a server with at most 10 clients that occasionally use the system. I noticed in the documentation that it said the following so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. From users' experience, is SQLite likely to be safe with so few clients on a local network. Also how do I login to the SQLite database from a client app? Cheers Jeff Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail ___ 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] Newbie question re using SQLite in basic client/server situation
On 3/7/08, Jeff Brown [EMAIL PROTECTED] wrote: Hi all I'm looking at switching the database that I use for my XCode/Cocoa application from MySQL to SQLite. (I'm getting tired of all the incompatibilities with new versions of MySQL). With a Cocoa application, all you have to do is use Core Data SQL as its persistent data store. Everything is stored automagically in SQLite format. But I need to be able to use it as a server with at most 10 clients that occasionally use the system. I noticed in the documentation that it said the following so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. There is no such thing as a SQLite server. SQLite is both the client and the server. Well, technically, your application is the client, and SQLite is embedded in it. Hence, when the user runs the application, there is a a SQLite dedicated just for that user. From users' experience, is SQLite likely to be safe with so few clients on a local network. Also how do I login to the SQLite database from a client app? The only analog I can think of is a web application where the data are stored in SQLite. This is a scenario that I use all the time. My web forms are the client, and the backend database is the server. I've never had a problem, but if I had more than a few users, I would likely upgrade to PostgreSql. Once again, keep in mind -- a typical use of SQLite is embedded inside an application, hence, each instantiation of the application has its own client (the application) and its own server (the SQLite embedded within it). Cheers Jeff Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question re using SQLite in basic client/serversituation
Sqlite is an embedded database, not a server. If you want to use it for multiple clinets you really need to add a server which embeds Sqlite. When you do that it works well and will handle many remote clients. If you have multiple users on one machine you can use the inbuilt Sqlite synchronization. [EMAIL PROTECTED] wrote: Not sure on the locking issue, but some of it might have to do with whether the database file is being provided via a NFS or a Windows Network Filesystem. Sometimes file-level locking is not real robust on networked file systems. If you are using some kind of ODBC/ADO provider then the details for that would be what counts. Not sure what cocoa is, but it sounds fairly modern so you are probably able to go through an ODBC connection or provider of some sort, in which case youwould just configure the SQLITE connectivity layer you are using. As far as SQLITE itself goes, there is no database server to 'log into', all you are doing is providing a file name ( the file for which has to be visible to the client program ) to the connect function. If you want a more usual client/server situation, youd have to use ODBC or ADO, else you'd have to write it yourself. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Brown Sent: Friday, March 07, 2008 4:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] Newbie question re using SQLite in basic client/serversituation Hi all I'm looking at switching the database that I use for my XCode/Cocoa application from MySQL to SQLite. (I'm getting tired of all the incompatibilities with new versions of MySQL). But I need to be able to use it as a server with at most 10 clients that occasionally use the system. I noticed in the documentation that it said the following so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. From users' experience, is SQLite likely to be safe with so few clients on a local network. Also how do I login to the SQLite database from a client app? Cheers Jeff Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question re using SQLite in basic client/server situation
On Fri, 7 Mar 2008 21:06:21 +1100 (EST), Jeff Brown [EMAIL PROTECTED] wrote: so, the file locking logic of many network filesystems implementation contains bugs (on both Unix and windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. If concurrent accesses are rare, you can probably get away with moving to a server-less DB like SQLite. To provide some kind of protection, you could - either write your own server process to which all clients should send their SQL queries, - or provide optimistic locking by keeping track of changes through a central database As extra protection, you could add some kind of semaphore to this table through either using the OS' locking mechanism while making changes to this table, or simply create/delete a file that will be a way to let other clients know that another process is busy updating the table. If performance is too slow on a 10Mbps LAN, it'll go away by switching to 1Gbps, which might be a viable alternative to moving to a C/S solution (rewriting, deploying, etc.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie question INSERT INTO on joined tables
Asume we have three tables:
CREATE TABLE ARTIST (Artistname TEXT UNIQUE)
CREATE TABLE SONG (SongTitle TEXT UNIQUE)
CREATE CHART(Artist INTEGER, Song INTEGER)
CHART.Artist should hold ARTIST.ROWID
CHART.Song should hold SONG.ROWID
Is it possible to do something like INSERT INTO CHART(Artist, Song)
Values('Abba', 'Dancing Queen') + something to obtain the result
1. Insert (if not exists) 'Abba' into ARTIST
2. Insert (if not exists) 'Dancing Queen' into SONG
3. Insert into CHART with corresponding ROWID’s
Michael from Sweden
--
View this message in context:
http://www.nabble.com/Newbie-question-%22INSERT-INTO%22-on-joined-tables-tf4781848.html#a13680160
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Newbie question about LIKE and ESCAPE
Hi all: I need a simple search, say: SELECT someField IN someTable WHERE name LIKE '%xyzetc%'; After some search in this list, I'm a bit more confused that before. For example after reading literally: The escape mechanism for LIKE has never been implemented in SQLite. The work-around is to use GLOB if your pattern contains '_' or '%' (unfortunately, GLOB is not std SQL). In case you're curious, the std SQL syntax is like 'ba_foo\_png' escape '\' Without the (unimplemented) escape clause, the '\' is just an ordinary character. Can someone unveil me the correct syntax for that query? Can I use some like: SELECT someField IN someTable WHERE name LIKE '?xyzetc?' ESCAPE ?; Thanks in advance. A.J.Millan ZATOR Systems - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Newbie question about LIKE and ESCAPE
On 3/3/07, A.J.Millan [EMAIL PROTECTED] wrote: Hi all: I need a simple search, say: SELECT someField IN someTable WHERE name LIKE '%xyzetc%'; This is standard SQL syntax, and works just fine in SQLite. It will match all rows where someField contains the string 'xyzetc' You can also anchor the string by using either '%xyzetc' or 'xyzetc%' if desired. .. Can I use some like: SELECT someField IN someTable WHERE name LIKE '?xyzetc?' ESCAPE ?; I don't have experience with the ESCAPE clause, but even if your syntax above were correct, you would find only those rows where someField is exactly like '?xyzetc?', and I think that is not what you want. Just use '%xyzetc%' and you will be fine. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Newbie Question
I am having a problem updating a table in one of two attached databases. What I want to do is indicate in one table whether a name is listed in a table in the other attached database. I am sure it is simple but I can't get the UPDATE criteria to work properly. Any help would be appreciated. Thanks, Roger /* Test ATTACH two Databases SOC and Options and set one column in a Table in SOC*/ DETACH Opt; DETACH SOC; ATTACH 'SOC.db' AS SOC; ATTACH 'Options.db' AS Opt; UPDATE Stocks SET bOption = 0; SELECT DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY Stocks.sStockSymbol; -- Why do I get 'SQL error: near INNER: syntax error' at the end of the previous output which is -- fine except for the error message -- Why doesn't the following UPDATE do anything? It has no effect and provides no error messages. UPDATE Stocks INNER JOIN Options ON Stocks.sStockSymbol = Options.sStockSymbol SET Stocks.bOption = 1; -- Note this is the same as the one above but does not provide an error message. SELECT DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY Stocks.sStockSymbol; SELECT 'Total Number of Stocks: ',COUNT(*) FROM Stocks; SELECT 'Number of Stocks with Options: ', COUNT(*) FROM Stocks WHERE bOption = 1; SELECT 'Number of Stocks without Options: ', COUNT(*) FROM Stocks WHERE bOption = 0; Also, .schema doesn't seem to do anything for ATTACHED databases, I assume this is intentional. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Newbie Question
Roger Miskowicz wrote: I am having a problem updating a table in one of two attached databases. What I want to do is indicate in one table whether a name is listed in a table in the other attached database. I am sure it is simple but I can't get the UPDATE criteria to work properly. Any help would be appreciated. Thanks, Roger /* Test ATTACH two Databases SOC and Options and set one column in a Table in SOC*/ DETACH Opt; DETACH SOC; ATTACH 'SOC.db' AS SOC; ATTACH 'Options.db' AS Opt; UPDATE Stocks SET bOption = 0; SELECT DISTINCT Stocks.sStockSymbol, bOption FROM Stocks INNER JOIN Options ON Stocks.sStockSymbol=Options.sStockSymbol ORDER BY Stocks.sStockSymbol; Where are the names Stocks and Options coming from? Should the ATTACH statements use AS Stocks and AS Options ? Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Newbie Question - Multiple Users, Multiple files
Hi Guys,
Re: [sqlite] Newbie Question
John, The configure script is looking for the location of a file named tclConfig.sh. On Fedora Core 4, that file is provided when you install the tcl-devel package. Here is the ../configure incantation (taken from ../configure --help): --with-tcl=DIR directory containing tcl configuration (tclConfig.sh) alternatively, you could compile with this: --disable-tcl You may want to recompile with the --disable-tcl option. If you decide to install the tcl-devel package, you can also recompile SQLite and point it at the directory that has tclConfig.sh. Bob JohnD wrote: Bob, I was able to compile on Linux after removing tclsqlite.c from the build. It appears as though that was causing my errors. Once I removed that, by changing HAVE_TCL = 1 to HAVE_TCL = It compiled without errors. I found this in another thread which indicated, to me at least, that it wouldn't be needed unless I wanted to use the TCL shell, which I don't. Thanks, John Robert L Cochran wrote: Post the error messages and someone will help you. I'll also check for messages after work tonight. There are others on this forum who are far more knowledgable than I.
Re: [sqlite] Newbie Question
Bob, Thanks for the information. Are the required dependencies for Sqlite listed anywhere? I'm unable to compile on Linux and it appears as though I'm missing a dependency or two. Any idea where I can check those? Thanks, John Robert L Cochran wrote: I think sqlite3.h is generated for you automatically as part of the build process from source code and it will be later installed for you in the include directory. You must actually compile sqlite from the source code. For Linux, why not just download sqlite-3.2.7.tar.gz (the linux source code), untar it, and build from that? wget http://www.sqlite.org/sqlite-3.2.7.tar.gz tar -xvzf sqlite-3.2.7.tar.gz cd sqlite-3.2.7 mkdir bld cd !$ ../configure --enable-utf8 --includedir=/usr/include [fix any errors...you might need to install the readline development package, etc...if you get an error, fix it, then rerun ./configure...] make [fix any errors if make doesn't finish successfully. Warnings about signedness differences under gcc4 are okay.] su make install exit su - ldconfig exit At this point you should be ready to build your own C/C++ programs that include sqlite3. You'll see the sqlite files in /usr/local/include if you compiled with the default installation path options. Bob Cochran Greenbelt, Maryland, USA JohnD wrote: Hello, I'm new to Sqlite and am trying to create a simple C/C++ program. I've downloaded both the binary and the CVS source but can't seem to locate the sqlite3.h header file. Is there any document that details what I need to do in order to set up an environment to be able to write and compile my own C/C++ programs on either Windows or Linux?
Re: [sqlite] Newbie Question
Post the error messages and someone will help you. I'll also check for messages after work tonight. There are others on this forum who are far more knowledgable than I. Bob JohnD wrote: Bob, Thanks for the information. Are the required dependencies for Sqlite listed anywhere? I'm unable to compile on Linux and it appears as though I'm missing a dependency or two. Any idea where I can check those? Thanks, John Robert L Cochran wrote: I think sqlite3.h is generated for you automatically as part of the build process from source code and it will be later installed for you in the include directory. You must actually compile sqlite from the source code. For Linux, why not just download sqlite-3.2.7.tar.gz (the linux source code), untar it, and build from that? wget http://www.sqlite.org/sqlite-3.2.7.tar.gz tar -xvzf sqlite-3.2.7.tar.gz cd sqlite-3.2.7 mkdir bld cd !$ ../configure --enable-utf8 --includedir=/usr/include [fix any errors...you might need to install the readline development package, etc...if you get an error, fix it, then rerun ./configure...] make [fix any errors if make doesn't finish successfully. Warnings about signedness differences under gcc4 are okay.] su make install exit su - ldconfig exit At this point you should be ready to build your own C/C++ programs that include sqlite3. You'll see the sqlite files in /usr/local/include if you compiled with the default installation path options. Bob Cochran Greenbelt, Maryland, USA JohnD wrote: Hello, I'm new to Sqlite and am trying to create a simple C/C++ program. I've downloaded both the binary and the CVS source but can't seem to locate the sqlite3.h header file. Is there any document that details what I need to do in order to set up an environment to be able to write and compile my own C/C++ programs on either Windows or Linux?
Re: [sqlite] Newbie Question
Bob, I was able to compile on Linux after removing tclsqlite.c from the build. It appears as though that was causing my errors. Once I removed that, by changing HAVE_TCL = 1 to HAVE_TCL = It compiled without errors. I found this in another thread which indicated, to me at least, that it wouldn't be needed unless I wanted to use the TCL shell, which I don't. Thanks, John Robert L Cochran wrote: Post the error messages and someone will help you. I'll also check for messages after work tonight. There are others on this forum who are far more knowledgable than I.
Re: [sqlite] Newbie Question
Compiling the CVS checkout is really the same -- just cd into the 'sqlite' directory and follow the instructions below from the 'mkdir' onwards. Bob Robert L Cochran wrote: I think sqlite3.h is generated for you automatically as part of the build process from source code and it will be later installed for you in the include directory. You must actually compile sqlite from the source code. For Linux, why not just download sqlite-3.2.7.tar.gz (the linux source code), untar it, and build from that? wget http://www.sqlite.org/sqlite-3.2.7.tar.gz tar -xvzf sqlite-3.2.7.tar.gz cd sqlite-3.2.7 mkdir bld cd !$ ../configure --enable-utf8 --includedir=/usr/include [fix any errors...you might need to install the readline development package, etc...if you get an error, fix it, then rerun ./configure...] make [fix any errors if make doesn't finish successfully. Warnings about signedness differences under gcc4 are okay.] su make install exit su - ldconfig exit At this point you should be ready to build your own C/C++ programs that include sqlite3. You'll see the sqlite files in /usr/local/include if you compiled with the default installation path options. Bob Cochran Greenbelt, Maryland, USA JohnD wrote: Hello, I'm new to Sqlite and am trying to create a simple C/C++ program. I've downloaded both the binary and the CVS source but can't seem to locate the sqlite3.h header file. Is there any document that details what I need to do in order to set up an environment to be able to write and compile my own C/C++ programs on either Windows or Linux? Thanks, John
[sqlite] newbie question re indexes
I'm very new to sqlite and have a question about indexing: are indexes used when doing range searches on integer columns? Suppose I have a table and index like the following: CREATE TABLE example (value INT); CREATE INDEX example_value_idx ON example (value); If I issue the following query, is sqlite going to narrow the search with the index, or scan the whole table? SELECT * FROM example WHERE value = 5 AND value 100;
[sqlite] Newbie question: sqlite.exe command usage?
I have read http://www.sqlite.org/sqlite.html
I want this to work:
[C:\test.js]
WS=new ActiveXObject('WScript.Shell')
WS.Run('C:\\sqlite.exe C:\\test.db .read C:\\query.txt .output
C:\\OUT.txt ')
[C:\query.txt] contains...
select * from sqlite_master;
What's up?
I have searched for links to sample command usage
and sample databases. please help.
[C:\whatever.js] If I try this, I lose the context of the previous settings...
WS=new ActiveXObject('WScript.Shell')
WS.Run('C:/sqlite.exe C:/test.db')
WS.Run('.read C:\\query.txt')
WS.Run('.output C:\\OUT.txt')
I would love it if all settings could be read from a single command file...
(settings=command line options, path to database file, SQL )
[C:\test.js]
WS=new ActiveXObject('WScript.Shell')
WS.Run('C:\\sqlite.exe -input C:\\commands.txt -output C:\\result.txt ')
joshdon
brisbane australia
-
Do you Yahoo!?
Meet the all-new My Yahoo! Try it today!
[sqlite] Newbie --question about multiple PCs accessing sqlite
Hello Shamil, [EMAIL PROTECTED] wrote: I do not have a C++ compiler If you are using Linux or some other Unix-like environment, you can get g++ (i.e., gcc) for free. If you are using Windows, there are several options for getting one (also for free): - Mingw or Cygwin (google for each) - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ - Borland also has a free command-line version of their C++ compiler on their website. HTH. Ulrik - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Newbie --question about multiple PCs accessing sqlite
Ulrik Petersen wrote: - Mingw or Cygwin (google for each) www.mingw.org, www.cygwin.com - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ Check the license! It is not really fair! Don't use this toolchain. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Newbie --question about multiple PCs accessing sqlite
-Original Message- From: Michael Roth [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 9:10 AM To: Ulrik Petersen Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Newbie --question about multiple PCs accessing sqlite Ulrik Petersen wrote: - Mingw or Cygwin (google for each) www.mingw.org, www.cygwin.com - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ Check the license! It is not really fair! Don't use this toolchain. Yes. Use Borland's, it is truly free :-) Don't want to risk Bill missing a house payment! http://community.borland.com/museum/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Newbie --question about multiple PCs accessing sqlite
Fred Williams wrote: -Original Message- From: Michael Roth [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 9:10 AM To: Ulrik Petersen Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Newbie --question about multiple PCs accessing sqlite Ulrik Petersen wrote: - Mingw or Cygwin (google for each) www.mingw.org, www.cygwin.com - Microsoft Visual C++ Toolkit 2003. Microsoft recently released their compiler and toolchain for free download: http://msdn.microsoft.com/visualc/vctoolkit2003/ Check the license! It is not really fair! Don't use this toolchain. Yes. Use Borland's, it is truly free :-) Don't want to risk Bill missing a house payment! http://community.borland.com/museum/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://www.openwatcom.org/ is also a possibility. It is an open source C/C++ compiler for several platforms. -- Daniel Lee Kruse Pursuant to U.S. code, title 47, Chapter 5, Subchapter II,Section 227, any and all unsolicited commercial E-mail sent,to this address is subject to a fee of $500.00 U.S. E-Mailing denotes, acceptance of these terms. Consult http://www.law.cornell.edu/uscode/47/227.html for details. War is an ugly thing, but it is not the ugliest of things. The decayed and degraded state of moral and patriotic feeling which thinks that nothing is worth war is much worse. A man who has nothing for which he is willing to fight, nothing he cares about more than his own personal safety, is a miserable creature who has no chance of being free, unless made so by the exertions of better men than himself. -- John Stuart Mill (1806-1873), British philosopher, economist. The Contest in America, Dissertations and Discussions (1859) Kill them all! God will recognize his own. -- Abbot Arnaud-Amalric in 1209, during the Albigensian Crusade on how to differentiate the Cathar heretics from the Catholics If Microsoft is ever going to produce something that does not suck, it is very likely a vacuum cleaner. Axel Hagedorn - Darmstadt/Germany - [EMAIL PROTECTED] Anonymous quotes: Pay attention to the political views of famous actors. After all, pretending to know things is their profession. (To minimize internet/virus/worm infections, I have a policy of not using Micro$oft products) Ein Volk, Ein Reich, Ein Fuehrer -- NSDAP (Nazi) slogan, 1930s One World, One Web, One Program -- Microsoft slogan, 1990s My two cents - Why would Micro$oft pattern a slogan after a Nazi slogan? Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Software is cheap, fast, and reliable ... (pick two). Windows (3.1, 95, 98, ME, NT, 2K, XP): n. 32 bit extensions and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit microprocessor, written by a 2 bit company, that can't stand 1 bit of competition. Friends don't let friends use Windows - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Newbie --question about multiple PCs accessing sqlite
I'm contemplating using the TCL binding of sqlite, but the database is to (B be located in a shared folder and about 100 users will be accessing it for (B read/write purposes. I would like to take some time to learn as much as (B possible to safeguard the database from getting corrupt due to several (B users accessing it at the same time. However, the sqlite website (B "http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: (B (B $B!H(BA good rule of thumb is that you should avoid using SQLite in situations (B where the same database will be accessed simultaneously from many computers (B over a network filesystem.$B!H(B (B (B (B I have 3 questions: (B (B 1.From the above statement, Am I right to assume that sqlite is not for (B me? (B (BProbably not unless you intend on implementing some application level (Bwrite locking on the SQLite database. I've done this with one (Bapplication but there probably aren't ever anywhere near 200 people (Baccessing it at the same time. If someone is writing the database it (Bwaits a few milliseconds and tries again (up to a finite number of (Btimes, at which time it reports a failure).. (B (BWith that many possible concurrent users you might look into a RDBMS (Bserver like PostgreSQL or MySQL. My personal preference is PostgreSQL, (Bbut many use MySQL too.. (B (B 2.If sqlite is feasible for my purpose, where can I find good (B information to avoid ending up with a corrupt database (B (BSQLite locks the *whole* database when writing. You simply cannot write (Bmore than one thing at a time to the database (INSERT, UPDATE, DELETE (Betc). I don't think there is any documentation that says anything more (Bthan that. (B (BI'm not sure if doing so will corrupt the data or just plain fail, but (Beither way you can't do it :-) (B (B 3.The TCL binding I downloaded says version 2.0 and I do not have a C++ (B compiler, where can I get the most recent binary of TCL sqlite? (B Thanks. (B (BNo idea on this one, check the SQLite webpage.. (B (B-- (B- Mitchell Vincent (B- kBilling - http://www.k-billing.com (B (B (B- (BTo unsubscribe, e-mail: [EMAIL PROTECTED] (BFor additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Newbie --question about multiple PCs accessing sqlite
I'm contemplating using the TCL binding of sqlite, but the database is to (Bbe located in a shared folder and about 100 users will be accessing it for (Bread/write purposes. I would like to take some time to learn as much as (Bpossible to safeguard the database from getting corrupt due to several (Busers accessing it at the same time. However, the sqlite website (B"http://www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite" says: (B $B!H(BA good rule of thumb is that you should avoid using SQLite in situations (Bwhere the same database will be accessed simultaneously from many computers (Bover a network filesystem.$B!H(B (B (B (BI have 3 questions: (B (B1.From the above statement, Am I right to assume that sqlite is not for (Bme? (B2.If sqlite is feasible for my purpose, where can I find good (Binformation to avoid ending up with a corrupt database (B3.The TCL binding I downloaded says version 2.0 and I do not have a C++ (Bcompiler, where can I get the most recent binary of TCL sqlite? (BThanks. (B (B (B (BThe information transmitted is intended only for the person(s)or entity (Bto which it is addressed and may contain confidential and/or legally (Bprivileged material. Delivery of this message to any person other than (Bthe intended recipient(s) is not intended in any way to waive privilege (Bor confidentiality. Any review, retransmission, dissemination or other (Buse of , or taking of any action in reliance upon, this information by (Bentities other than the intended recipient is prohibited. If you (Breceive this in error, please contact the sender and delete the (Bmaterial from any computer. (B (BFor Translation: (B (Bhttp://www.baxter.com/email_disclaimer (B (B (B- (BTo unsubscribe, e-mail: [EMAIL PROTECTED] (BFor additional commands, e-mail: [EMAIL PROTECTED]
