[sqlite] Tables as ASCII - is it possible?
Hi all, Is there a way of storing SQLite data (tables) as ASCII text rather than as binary data? I want to be able to run scripts against my data as well as use SQLite. If it's not available as a normal option, is there a patch on the interweb somewhere? If it does not exist, I respectfully suggest that it is worthy of consideration as a feature - maybe along the lines of MySQL storage engines? I respect the fact that this is an Open Source project with a Public Domain licence (i.e. volunteer coders) and I'm not demanding anything, merely suggesting that it might be a good idea. Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables as ASCII - is it possible?
2011/10/23 Simon Slavin slav...@bigfraud.org: I want to be able to run scripts against my data as well as use SQLite. I recommend you script the sqlite3 shell tool to pipe whatever data you want, or to make a text file of it which you can then read: Looks that this is the way to go alright! If it does not exist, I respectfully suggest that it is worthy of consideration as a feature - maybe along the lines of MySQL storage engines? My guess is that it would slow down SQLite by a factor of three or more. I don't doubt for a second that the ASCII option would be slower than the current one - only if the user chose it though. Feel free to write it, and contribute your code. I consider anything more sophisticated than Hello World to be advanced C! ;) Thanks for your input. Paul... Simon. lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables as ASCII - is it possible?
2011/10/23 Mr. Puneet Kishor punk.k...@gmail.com: I want to be able to run scripts against my data as well as use SQLite. Are you suggesting that you want to treat text data as a SQL data store? No, I'm suggesting that the SQLite engine be able to have table data available as text files - table1.txt, table2.txt c. I have to be able to send text files to a machine that doesn't have SQLite and won't (very very conservative manager! :-( ). You might want to look at DBD::CSV [http://search.cpan.org/ ~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm] Nope, Perl is out also - anyway, I wouldn't use it. If I could go with a scripting language, it would be Python - vastly superior IMHO to Perl - YMMV. It's shell scripting that I want to be able to do - remember my manager? Thanks for your input. Paul... Puneet Kishor -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski pat...@patpro.net: I can't see why you would want to do this more than once every minute - or do you? The granularity I'm looking for is between 1 second and 10 seconds. Cron is not an option here. I woke up this morning and there is a way that cron *_could_* do what you want. You appear to have figured out a way that suits you, but cron could be used. 10 second granularity. You have 6 cron jobs, each launched on the minute. The first launches iostat and puts data into SQLite. The second does a sleep 10, launches iostat and puts data into SQLite, the third sleep 20 c. I know it's an appalling hack, but could be useful to somebody? Sincères saluations. Paul... patpro -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski pat...@patpro.net: I'm facing a challenging problem. I want to log some data into an SQLite3 DB. Data come from a system command (iostat) in an endless steam, one row every X seconds: Take a look at a utility called dstat. I've twiddled with the source and have its output go to a .csv. Basically, I've done what you want to do, except with Oracle. The plan then is to cron (or within Oracle - use the db scheduler) a job that copies the data into the database - then at certain times delete down the .csv file so that you're not continually rejecting records already in the db. I have implemented this with Oracle - need to do a bit of work - but it's part of what I believe *_should_* be easy - i.e. put basic system metrics directly into a database so that such data can be analysed over a long period, rather than Oh, what did iostat say yesterday?. When I have it fully working with Oracle (XE 10), I plan to get it working with SQLite - it should be reasonably easy using .csv and cron jobs. Paul... -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski pat...@patpro.net: That's what I do, but I think using a loop is ugly, and I would like to find a way to feed data continuously into sqlite. cron Paul... patpro -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski pat...@patpro.net: That's what I do, but I think using a loop is ugly, and I would like to find a way to feed data continuously into sqlite. I can't see why you would want to do this more than once every minute - or do you? If not, == cron iostat myfile. Parse myfile - get data out. Insert into SQLite. Delete myfile. === Should take a max of 5 seconds = Then repeat cron for the next min, 2 mins 5/10/whatever... Not elegant I know, but it will do the job. Why, exactly, do you want to do this anyway? I'm interested because I've done something similar. Paul... patpro -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski pat...@patpro.net: Take a look at a utility called dstat. no, it's linux only. But it is written in Python - so it should be relatively transportable. I've even managed to modify the code myself - and if I can do it, anybody can! 8-) Paul... patpro -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature about Foreign Keys
I would ask for a feature: to have a meta table where the foreign keys are listed. This feature it would be nice for large schemes. There are lots of nice-to-have features that one could add to SQLite. If you want to keep track of your foreign keys, download Datamodeler from Oracle - it's free to use and is *_very_* nice for doing just what you want, albeit manually. Paul... -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] update required
2011/8/21 Richard Hipp d...@sqlite.org: But we've also gotten messages (including some irate late-night phone calls to my personal telephone) complaining of problems with English-language versions as well. That is a disgrace, and I hope that I speak for everyone on this list in condemning this sort of completely unacceptable behaviour. I have known projects in the past which have floundered because the lead programmer just said that they had had enough of rude/obscene/offensive emails/communications and that they were oprhaning the project. Just let me say that I hope you regard people who engage in this sort of idiocy as being unworthy of even your disdain and that you don't stop your marvellous contributions to the software world. I have Bugzilla working with SQLite - brillo! In the coming months I hope to evaluate Fossil. As the French would say (or rather write) Sincères salutations. Paul... D. Richard Hipp -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggest some Tools for Sqlite
2011/8/18 Madhankumar Rajaram maraja...@technip.com: I hope there should be some good Free or Licence Tools for using Sqlite. For ex : Toad for Oracle. Hmmm Toad for SQLite - there's a thought. ;) so, Kindly suggest the best Free / Licence Tool for using Sqlite Thanks Try the Firefox extension - I think it's great! Paul... Madhan Kumar R -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance Improvement
I can at least store all the data, for the cases I have tested till now (600 billion entries for now), but it is awfully slow. I'm not surprised. Maybe you should consider some sort of partitioning scheme? Take a look at VoltDB.com - it might be an approach? Paul... -- Hmmm a life: wonder where I can download one of those? lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] current version support wal mode?
2011/7/2 Kees Nuyt k.n...@zonnet.nl: Just feed it the SQL statement: PRAGMA jounal_mode=WAL; I'm not being nasty here, but that is *_not_* an SQL statement. It is a C directive (or, at least, that's what I think it is!). Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] current version support wal mode?
2011/7/3 Danny dragonslaye...@yahoo.com: Like the old saying goes ... When all else fails, read the manual. http://www.sqlite.org/pragma.html Oooops However, I would argue that this The PRAGMA statement is an SQL extension specific to SQLite is the equivalent of saying that T-SQL or PL/SQL is SQL, it is an extension, albeit a non-proprietary one. Having said that, I will take my punishment like a man and admit to being wrong in this context - apologies for any offence/confustion caused! Have a nice day. Agus tusa! Paul... Danny -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlitebrowser - anyone compiled a recent one?
2011/6/9 Christoph P.U. Kukulies k...@kukulies.org: If you want something that works on Linux as well as Windows, try the Firefox SQLite extension - it's the dog's! I've heard big caveats about that one - to avoid like the .., no, I don't want to open another can of worms :) I don't wish to cause controversy here, but could you explain exactly what you mean by this? What is there about this tool that is problematic? TIA and rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlitebrowser - anyone compiled a recent one?
Thanks. I was prepared to receive alternate suggestions :) I wanted to have hands on and control over a browser with which can follow sqlite versions quicker. If you want something that works on Linux as well as Windows, try the Firefox SQLite extension - it's the dog's! Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper way to escape table name and column name
2011/6/6 Jean-Christophe Deschamps j...@antichoc.net: What is the official way to escape table name that contains a space and column name that contain a spaces? You can use square brakets or double-quotes: [This is a long name for a small table] This is a long name for a small table as well Or, what you can also do is use table names such as no_special_chars and/or do_not_distinguish_between_caps_and_lower_case_in_designing_system ? Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best JDBC driver for SQLite?
Hi, Thanks for your input. Yes and yes. http://www.catb.org/~esr/faqs/smart-questions.html I followed the links on this page - http://sqlite.org/support.html and found this http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/ It did not have a search function - I assumed that there wasn't one. There is no best unless you supply some constraints - ie what is important to you. Are you working with terabytes of data or just a few rows? Gigs - what I'm interested in is sharding and using SQLite instances as shards in a larger ensemble. What platforms? Linux, FreeBSD, Solaris and Windows. Do you want it to use JNI to an existing SQLite library or be bundled with one? I don't know - what should I be doing? Does documentation matter? Good to have - but not critical. Rgs, Paul... Roger -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best JDBC driver for SQLite?
Hi, Thanks for your reply. Gigs - what I'm interested in is sharding and using SQLite instances as shards in a larger ensemble. Well now you've told us what you want out of it, we can comment usefully. Oh, goody! You can handle your shards yourself, and you can implement sharding by using VIEWs and TRIGGERs, but SQLite does not have sharding implemented /per se/. I know this - what I want to do is implement a sharding scheme myself with a queueing system and run it as a service on the different machines. These services will then communicate the result of any query back to the master server - web server - client. SQLite will be data storage only. I'll have to deal with the fact that writes in SQLite are blocking. The system I'm prototyping on is Firebird embedded, which is not client server either. Ideally, I want to use a serverless database system, but I will also be looking at Oracle XE and PosgreSQL. If your design, from the ground up, is based around multi-hosted data and/or client-server architecture, then SQLite is not the system for you. I kind of want to do this for myself - check out my email address - it's a uni and this is a learning project as much as anything else. You will be happier with MySQL or one of the other client/server systems. I know I can do it this way - I could have a scheme where a truckload of machines run Firebird server (or MS SQL Server or Oracle or yadda yadda, but I'm trying to do it myself and take a minimalist approach. Perhaps the shards could be minimalist virtual machines running on the same box - if one tried to run 10 db servers at once on the same machine, the load would mount. What I have in mind is loosely based on Michael Stonebraker's VoltDB, except with a commodity off-the-shelf datastore. However, there's no problem with the amount of data you specified, and SQLite works on all your platforms (more than any other SQL DBMS I'm aware of) without problems, since all the SQLite team supplies is the source code for the function library. I'm doing this for my Java as much as anything else, so the platform must support that too, which is fine for Linux, *BSD and Windows. If you need more details, ask more specific questions. Thanks again for your input. Rgs, Paul... Simon. -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best JDBC driver for SQLite?
Hi, I sent this message to the list As I'm going to be using Java, I would like to know what is the best JDBC driver for SQLite - or if there isn't a best one, what are the pros and cons of the different ones. two days ago - and haven't received a single reply. Is this not the correct forum for such a question? Can anybody even see this post? TIA and rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best JDBC driver for SQLite?
Hi all, I am currently working on a project which will use several different databases as a back end. I'm going to try and take advantage of the various features of the different databases as I go along, but obviously I will need to code some features by hand. As I'm going to be using Java, I would like to know what is the best JDBC driver for SQLite - or if there isn't a best one, what are the pros and cons of the different ones. I have looked here http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers but this is merely a list and not an assessment of the advantages and disadvantages of the various offerings. Any advice, opinions, references, URL's, ideas and discussion much appreciated. Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
