Re: [sqlite] Simple SQLite-based spreadsheet?
Maybe you can achieve this if you use ODBC connectors and Excel Tables. *Jonas Malaco Filho* 2012/12/7 Gilles Ganault > Hello > > I need to enter a bunch of items into a table that I can later read > from a web app. > > I was wondering if someone had come up with a Windows application that > would... > - use Sqliteto host the data > - make it very easy to add a table anc columns (possibly several > related tables) > - display a spreadsheet to make it easy to add data > ? > > Namely, a basic, no brainer MS Access relying on SQLite. Considering > so many people use Excel as poor man's database because anyone can use > it while Access takes some learning... I think there's an opportunity > here. > > Thank you. > > ___ > 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] Compiling SQLite3 with MSVC 2010
Actually, on MSVC 2010 I just got the following errors with /W3: -- Rebuild All started: Project: Shell, Configuration: Release x64 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj -> X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86-64\sqlite3.exe -- Rebuild All started: Project: Shell, Configuration: Release Win32 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj -> X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86\sqlite3.exe == Rebuild All: 2 succeeded, 0 failed, 0 skipped == *Jonas Malaco Filho* 2012/11/3 Black, Michael (IS) > You probably have the warning level turned up high. > > Up to level 3 it compiles without warnings. Level 4 starts complaining > loudly. > > > 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 Igor Korot [ikoro...@gmail.com] > Sent: Friday, November 02, 2012 6:25 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 > > Richard, > > On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp wrote: > > On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot wrote: > > > >> Hi, ALL, > >> Is anybody trying to compile SQLite with MSVC 2010? > >> > > > > Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were > > performed using MSVC 2010. > > > > > >> > >> I am getting a lot of warnings. > >> Is there any interest in fixing those? > >> > > > > No. See http://www.sqlite.org/testing.html#staticanalysis for an > > explanation. > > I just read this link. Interesting information. > IIUC, all those warnings are harmless and they do not appear on other > platforms. > Which means that either gcc is more forgiving or that I am trying to > compile my > application with some very strange configuration. > Or maybe it's C++11 that throws the compilation off of track? > > I'm just trying to understand why those warnings appear and why nobody else > see them on other platforms. > > Thank you. > > > > > > >> > >> If not what is the policy of using SQLite3 code? I'm using 3.7.14 > release. > >> > >> Thank you. > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dump a memory DB
You can use the .backup command: http://www.sqlite.org/sqlite.html *Jonas Malaco Filho* 2012/10/31 Andrea Peri > Hi, > > From the sqlite3 console, is possible to dump on disk a memory DB ? > > Thx, > > -- > - > Andrea Peri > . . . . . . . . . > qwerty àèìòù > - > ___ > 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] "natrual" join (not "natural")
Maybe there could be a strict switch. It could also be useful for developing/testing stuff on SQLite that would later be sent to different a RDBMS or a custom SQL processor. *Jonas Malaco Filho* 2012/10/31 Richard Hipp > On Wed, Oct 31, 2012 at 10:13 AM, Ryan Johnson > wrote: > > > On 31/10/2012 9:54 AM, Samuel Neff wrote: > > > >> We wrote a query and intended to use a "natural" join but had a typo and > >> wrote "natrual" join instead. We were surprised this query was > processed > >> without error and performed a cross join. > >> > > That must have been a delightful one to track down... > > > > It's due to the (non-standard?) support for specifing table aliases > > without the keyword AS. In other words, sqlite3 sees the following as > > identical: > > > > sqlite> select natral.* from a natral join b; > > sqlite> select natral.* from a as natral join b; > > > > ... and will reject this: > > sqlite> select a.* from a natral join b; > > > > Whether this is a bug or a feature, I'm not sure... but it clearly had an > > unintended consequence in your case. > > > > I copied this syntax from PostgreSQL > > > > > > Ryan > > > > > > __**_ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users< > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Updating on 32bit os slower than 64bit?
Could be different compilation settings for sqlite (or libs).
*Jonas Malaco Filho*
2012/7/15 xp
> Hi,
>
> I am a sqlite newbie. I would be very grateful if anyone can answer my
> question or point me to the right direction.
>
> I have a very simple database:
>
> create table epics_channel(
> id integer primary key,
> epics_pv text unique,
> value_type text, -- current or voltage
> value real default 0.0
> );
> - Populate EPICS channel table --
> insert into epics_channel(epics_pv, value_type) values ('01QM000I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('01MP001I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('01MP002I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('01MP003I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP001I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP002I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP003I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP004I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP005I01',
> 'current');
> insert into epics_channel(epics_pv, value_type) values ('02MP006I01',
> 'current');
>
> And a single c++ code to update the database:
>
> sqlite3* db;
> char* errmsg;
> sqlite3_open("example.db", &db);
> sqlite3_enable_load_extension(db, 1);
> const char* lib = "./libsqliteext.so";
> sqlite3_load_extension(db, lib, 0, &errmsg);
>
> std::string sql;
> sqlite3_stmt* stmt;
>
> for(int j = 1; j <=10; ++j )
> {
> std::stringstream val;
> std::stringstream id;
> val << a_number; id << j;
> sql = "update epics_channel set value = " + val.str() + " where id = "
> +
> id.str();
> sqlite3_prepare(db, sql.c_str(), -1, &stmt, NULL);
> sqlite3_step(stmt);
> sqlite3_finalize(stmt);
> }
>
> I ran the same code on a 64bit(scientific linux 6.1) and a 32bit(fedora 17)
> linux box. it took 0.113859 sec for the 64bit system to finish, but 14.4922
> sec for the 32bit fedora 17. Can anyone tell me why it was so slow on the
> 32bit system?
> And I noticed this only happened for the "update". If I use "select", it
> took both systems about the same time to finish.
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Updating-on-32bit-os-slower-than-64bit-tp63292.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to?
I think that session info is stored in sessionrestore.js, in your Firefox profile data folder. There is also a sessionrestore.bak, that may help you. You can check if you have other (older) versions of these files thanks to shadowing (or any other recovery methods). *Jonas Malaco Filho* 2012/6/5 Simon Slavin > > On 5 Jun 2012, at 12:26pm, baboushka jane wrote: > > > i hope i have found the right place. i would like some help with my > sqlite files. > > a week or so back i was clearing my recent Firefox history when my > browser froze and so i used Activity Monitor to force close Firefox. > meanwhile, i was reading an e-mail and clicked on a link which opened up a > new session.. meaning, i could not retrieve my last session - i.e. the > countless tabs that i had open when i force closed.. > > after a little googling, i think i gather that this last session is > somewhere in a sqlite file on my HD. > > i had some really valuable reading lined up that i unfortunately didn't > bookmark in time.. thank you so much in advance! > > Sorry, but you need an expert in FireFox, not one in SQLite. Try asking > on one of the FireFox support lists. > > 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] What does "The use of the amalgamation is recommended for all applications." mean, precisely?
Just curious, what dev language are you using? *Jonas Malaco Filho* On 25/05/2012, at 17:57, Peter Haworth wrote: Just picking a random post to make my reply to. I truly wish I could get access to an external sqlite library to load at runtime. I use e devlopement language that has the sqlite library built into it so I am at the mercy of the language provider as to what is provided. Right now, they are using sqlite 3.7.4 which looks to be at least 7 releases out of date and lacks several features including fts4aux and rtree virtual tables. TO make matters worse, they don;t expose whatever function it is in sqlite that would allow me to load extensions to sqlite from external libraries. So while I think that in most circumstances it makes sense to compile the sqlite library into your application for all the reasons mentioned, I wish that was not the case in my circumstances. Pete lcSQL Software <http://www.lcsql.com> On Fri, May 25, 2012 at 9:00 AM, wrote: Message: 60 Date: Fri, 25 May 2012 12:45:07 + From: "Black, Michael (IS)" To: General Discussion of SQLite Database Subject: Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely? Message-ID: Content-Type: text/plain; charset="iso-8859-1" On the 3rd hand (Medusa here) those of us involved in mission critical apps refuse to let people arbitrarily update packages that are part of the "system". Things like the bug introduced in 3.7.12 being a prime example. It's called "Configuration Control". Some apps may depend on updated system libraries...just keep them out of my sandbox. If our app needs a fix WE put it in under a controlled manner with regression testing, test system, and final deployment. And we may include a newer version than the system is allowed to have. Call me (and my configuration control panel) control freaks. We used to have tons of problems on Solaris due to shared libraries. Numerous incompatibilities across multiple versions of Solaris. Static link always worked. 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite SELECT performance problem
> > And don't forget /D_CRT_DISABLE_PERFCRIT_LOCKS > Did know about this... Don't you just love obscure settings and behavior like this? > Generated code is still of "Microsoft Quality" though (if you want proper > instruction scheduling and optimization that actual optimizes anything, > then use a real compiler, such as gcc or watcom). I only use MSVC in order > to integrate the runtimes with other things which use the MS compilers, not > because they are of any worth. Yes. But it also depends on the port. Which one do you use most? I like MinGW, for its simplicity, but it was a pain to set it up for x86-64. In my still very limited experience, the generated code from MinGW32 was not much (if at all) faster than MSVC, and compile times were higher. For me, that has always a bit disapointing (I guess I expected a gcc like performance). Anyway, I also ran the query on executables compiled with MinGW, although I did not really go into optimization settings for it. I tried -Ofast but since I would have to disable -ffast_math (and was not sure if the rest would work well) I just used -O3 instead. Original x86: 00:00:04.016 MSVC x86 (as before): 00:00:01.248 MSVC x86-64 (as before): 00:00:01.048 MinGW x86: 00:00:01.429 MinGW x86 -march=corei7-avx: 00:00:01.307 MinGW x86_64: 00:00:01.195 MinGW x86_64 -march=corei7-avx: 00:00:01.171 *Jonas Malaco Filho* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite SELECT performance problem
I've tested your database and query, using the official sqlite3.exe and the versions I compile myself (Microsoft Visual Studio 2010, x86 and x64): - Official (x86): ~5s (CPU Time: user 1.965613 sys 2.527216) - Compiled by me (x86): ~1s (CPU Time: user 1.154407 sys 0.187201) - Compiled by me (x64): ~1s (CPU Time: user 0.904806 sys 0.218401) My SQLite compilation options differ from the default values (so this does not address the weird behavior you described), but maybe it can still be useful for you: - x86: /DSQLITE_DEFAULT_CACHE_SIZE=2000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1 /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=4096 /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=2000 /DSQLITE_THREADSAFE=0 /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3 - x64: /DSQLITE_DEFAULT_CACHE_SIZE=4000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1 /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=8192 /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=4000 /DSQLITE_THREADSAFE=0 /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3 Both use: - Full Optimization (/Ox) - (Inlining) Any Suitable (/Ob2) - (Enable Intrinsic Functions) Yes (/Oi) - Favor fast code (/Ot) - Use MFC in a Static Library Everything is always in my Dropbox: https://www.dropbox.com/sh/ecyzbn5kz4nf7ft/JHGD62iezx *Jonas Malaco Filho* 2012/5/24 Simon Slavin > > On 24 May 2012, at 11:49pm, Nick wrote: > > > The sizes of the executable files are almost identical - there's a few > > kilobytes difference. > > I have attached the original (downloaded from sqlite.org) sqlite3.exe, > > a compiled-by-myself sqlite3console.exe. And the source code. > > Attachments don't work on this list (we don't want everyone sending us > their homework) but I hope the information you've provided gives someone a > hint about what's going on. > > 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] SQLite SELECT performance problem
Why TEMP_STORE=1 (file by default) and not TEMP_STORE=2 (memory by default)? *Jonas Malaco Filho* 2012/5/24 Simon Slavin > > On 24 May 2012, at 8:59pm, Nick wrote: > > > So why does a prebuilt, downloaded from the sqlite website, command > > line tool takes only 4 seconds, while the same tool, built by me, > > takes 4 times longer time to execute? > > I'm wondering whether the speed increase is related to figuring out the > parameters in the command. Purely out of interest, and not because I know > it's faster, have you tried executing the query using sqlite3_exec() rather > than _prepare(), _step(), _finalize() ? Don't do a lot of reworking of > your code unless it turns out to be faster. > > 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] is SQLite the right tool to analyze a 44GB file
Have you ran *.mode csv*? Jonas Malaco Filho 2012/5/7 peter korinis > Regarding SQLITE3.exe statement ".import FILE TABLE" > I created a table. > My input file is a comma-delimited text file > When I run .import I get the following "Error: FILE line 1: expected 46 > columns of data but found 1" > It seems .import is not recognizing comma delimiter. > I suspect this is a simple syntax error, but I don't find any > document/explanation. > > Thanks, > peter > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Friday, May 04, 2012 11:16 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file > > > On 4 May 2012, at 4:02pm, peter korinis wrote: > > > Sqlitespy looks good ... I will try it. > > website says download contains sqlite itself, which I already have - > > will there be a problem using ...spy with existing sqlite? > > SQLite is not a single library which has to live somewhere on your > computer. > It is C code which each programmer includes in their program. You can have > twenty programs on your disk, each using a different version of SQLite, > without problems. > > > I was trying to use sqlite3 CLI cmd ".import testfile.txt test_tbl;" > > to load a 999x46 comma-delimited file into a previously created empty > > table with 46 col. (if this works I will load two 22M row x 46 col csv > > files into that > > table.) does this cmd work this way or must I create INSERT statements > > to do > > 999 inserts (later 44M inserts)? > > Semicolons are needed at the end of SQL commands. You don't want them at > the end of commands which start with a dot. Apart from that you have > something worth trying. Why not make a tiny test case with three lines of > two columns before you get started with the 46-column monster ? > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
