[sqlite] What is the definition/use/point with the xShm* procedures in sqlite3_io_methods, and what's the benefit of using WAL with those rather than the global file locking option?
Dear list, I understand WAL recently got the option of only relying on a db-file-global lock in order to behave correctly across sqlite instances accessing the same file. At the same time, I presume the other access option, which is based on the presence and functioning of the shared memory features in the underlying VFS, has benefits. While I'm aware shared memory functionality is widespread in all kinds of OS:es, I have a hard time getting a clue of how exactly sqlite uses them, even after reading the sourcecode of their implementations in sqlite's code. Can you please detail how those VFS procedures and their arguments are defined and how a VFS implementation that implements them is required to function to deliver for all of Sqlite's current requirements: xShmMap (Is the point that to ensure that a particular byte range in the wal or db file is kept in memory and thus that ordinary xRead/xWrite are supposedly higher speed, or does it allocate a block of RAM that all processes doing the same mapping command get r/w access to, or what?) xShmLock and SQLITE_SHM_NLOCK xShmBarrier xShmUnmap (Drops all locks and mappings for the file, or what?) Ultimately, is the point with WAL using shm to get a more fine-grained access to files, or a kind of IPC message passing functionality across sqlite instances, or both? If you could give a rough example of how a session of opening/accessing/closing a sqlite WAL DB looks in terms of sqlite3_io_methods calls (the parts that relate to shm in particular) and briefly the point with the respective calls, would be much appreciated. As of today, are the VFS shm functionality (xShm* procedures) only used when in WAL mode and for the WAL files only? Also, what the benefits you see are with going with shm instead of the file-global locking mode for a WAL DB? Is the main benefit more fine-grained access to the wal/db files' contents and thus higher parallell processing across sqlite instances? Thanks and kind regards, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000
I have a table in my sqlite3 database that stores 1001 float numbers in 1001 columns. I wish to dump the database to ascii format for backup purposes, and I expect the .dump function will create a snapshot of the database. However, I didn't find any rows of data in the table after restoration. When I inspect the dump file, I didn't find any INSERT commands. Did I miss any arguments in the .dump command to dump the database successfully? Here's the minimal sql command to construct the database (command.sql): |CREATE TABLE trace1001_constructs ( id INTEGER NOT NULL, tr FLOAT, tr0001 FLOAT, tr0002 FLOAT, ..(and so on). tr0999 FLOAT, tr1000 FLOAT, PRIMARY KEY (id)); INSERT INTO trace1001_constructs(id) VALUES (1); | This is the command I intend to generate the file dump (dump.sql) from linux shell: $ rm -f database.sqlite $ sqlite3 database.sqlite command.sql $ sqlite3 database.sqlite .dump dump.sql I don't think SQLITE_MAX_COLUMNS is the limiting factor, as I only have 1002 columns in the above table. I don't think SQLITE_MAX_SQL_LENGTH is the limiting factor either, as the CREATE TABLE command is present in the file dump.sql, which should be much longer than the INSERT command. || ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite mobile size capacity
what is the maximum size of sqlite on the mobiles phone (android, iPhone, blackberry, windows mobile) its the same on all of them ? and in wish of them sqlite more compatibility and the performance of it is high thx for answer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000
On 28 Jan 2012, at 4:45am, Antony wrote: |CREATE TABLE trace1001_constructs ( id INTEGER NOT NULL, tr FLOAT, tr0001 FLOAT, tr0002 FLOAT, If this database is for longterm use, you're doing the wrong thing. Those should be different rows, not all one huge long row. If it's a one-off hack, then do it and get rid of it before anyone else sees it and shames you. This is the command I intend to generate the file dump (dump.sql) from linux shell: $ rm -f database.sqlite $ sqlite3 database.sqlite command.sql $ sqlite3 database.sqlite .dump dump.sql Do this interactively rather than as a shell script. rm database.sqlite sqlite3 database.sqlite .read command.sql Any error messages so far ? if not .dump Did that produce the right result ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite mobile size capacity
On 28 Jan 2012, at 11:15am, Live Happy wrote: what is the maximum size of sqlite on the mobiles phone (android, iPhone, blackberry, windows mobile) its the same on all of them ? SQLite can handle far bigger databases than any of those platforms can. So the maximum size of a database depends on the capacity of each specific phone. And all those platforms include phones of different capacities. and in wish of them sqlite more compatibility SQLite is perfectly compatible with itself: it uses the same database file format, no matter what hardware it is running on. However, I don't know what's involved in programming blackberry phones. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite version 1.0.79.0 released
System.Data.SQLite version 1.0.79.0 (with SQLite 3.7.10) is now available on the System.Data.SQLite website: http://system.data.sqlite.org/ Further information about this release can be seen at http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you encounter any problems with this release. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000
2012/1/28 Simon Slavin slav...@bigfraud.org: If this database is for longterm use, you're doing the wrong thing. Those should be different rows, not all one huge long row. If it's a one-off hack, then do it and get rid of it before anyone else sees it and shames you. Why not? Please send link to SQLite documentation instead of any astral reasons. -- 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
Re: [sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000
Without knowing what you're doing with the data or what your asbtract structure is many will asssume that too many columns means not enough normalization of your database. It's not SQLite -- it's standard database design. http://en.wikipedia.org/wiki/Database_normalization You may be perfectly justified in what you're doing but without explaining what you're doing it raises suspiscions when lots of columns are used. It's usually a bad idea for long-term design and maintenance purposes. 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 Alexey Pechnikov [pechni...@mobigroup.ru] Sent: Saturday, January 28, 2012 9:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000 2012/1/28 Simon Slavin slav...@bigfraud.org: If this database is for longterm use, you're doing the wrong thing. Those should be different rows, not all one huge long row. If it's a one-off hack, then do it and get rid of it before anyone else sees it and shames you. Why not? Please send link to SQLite documentation instead of any astral reasons. -- 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with sqlite and codeblocks (mingw)
Hi, I have a problem with compilation sqlite for mingw and code:blocks, everything is ok and compilation don't show any abnormal information, but when I try only add library of sqlite to the linker of sample wxwidgets program it doesn't work. Everything is ok when I use normal cosole program for C++ but when I use wxwidgets everything is wrong, program compile without any errors and warning but don't show anything on the screen, when I try debugging. It is my build log of sqlite library (everything build as a static library): [ 33,3%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\shell.c -o obj\Debug\shell.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [ 66,7%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\sqlite3.c -o obj\Debug\sqlite3.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [100,0%] ar.exe -r -s libsqlite.a obj\Debug\shell.o obj\Debug\sqlite3.o ar.exe: creating libsqlite.a Output size is 1,28 MB The sam build log for my sample wxwidgets_app: -- Build: Debug in Wx_test_sql --- [ 20,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\wx_pch.h -o wx_pch.h.gch\Debug_wx_pch_h_gch [ 40,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlApp.cpp -o obj\Debug\Wx_test_sqlApp.o [ 60,0%] windres.exe -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -J rc -O coff -i C:\MY_PRO~1\WX_TES~3\resource.rc -o obj\Debug\resource.res [ 80,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlMain.cpp -o obj\Debug\Wx_test_sqlMain.o [100,0%] mingw32-g++.exe -LC:\wxWidgets-2.8.12\lib\gcc_lib -o bin\Debug\Wx_test_sql.exe obj\Debug\Wx_test_sqlApp.o obj\Debug\Wx_test_sqlMain.o obj\Debug\resource.res -mthreads -lwxmsw28ud_richtext -lwxmsw28ud_xrc -lwxmsw28ud_aui -lwxmsw28ud_media -lwxbase28ud_net -lwxbase28ud_xml -lwxmsw28ud_adv -lwxmsw28ud_html -lwxmsw28ud_core -lwxbase28ud -lwxpngd -lwxjpegd -lwxtiffd -lwxzlibd -lwxregexud -lwxexpatd ..\sqlite\libsqlite.a -lkernel32 -luser32 -lgdi32 -lwinspool -lcomdlg32 -ladvapi32 -lshell32 -lole32 -loleaut32 -luuid -lcomctl32 -lwsock32 -lodbc32 -mwindows Output size is 31,03 MB Process terminated with status 0 (0 minutes, 14 seconds) 0 errors, 0 warnings (0 minutes, 14 seconds) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite and codeblocks (mingw)
Robert, Have you tried wxSQLite3? It works like a charm for many wxWidgets' projects. Here's the link http://wxcode.sourceforge.net/components/wxsqlite3/ Regards, Stefanos From: Robert Gdula robert.gd...@gmail.com To: sqlite-users@sqlite.org Sent: Saturday, January 28, 2012 5:46 PM Subject: [sqlite] Problem with sqlite and codeblocks (mingw) Hi, I have a problem with compilation sqlite for mingw and code:blocks, everything is ok and compilation don't show any abnormal information, but when I try only add library of sqlite to the linker of sample wxwidgets program it doesn't work. Everything is ok when I use normal cosole program for C++ but when I use wxwidgets everything is wrong, program compile without any errors and warning but don't show anything on the screen, when I try debugging. It is my build log of sqlite library (everything build as a static library): [ 33,3%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\shell.c -o obj\Debug\shell.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [ 66,7%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\sqlite3.c -o obj\Debug\sqlite3.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [100,0%] ar.exe -r -s libsqlite.a obj\Debug\shell.o obj\Debug\sqlite3.o ar.exe: creating libsqlite.a Output size is 1,28 MB The sam build log for my sample wxwidgets_app: -- Build: Debug in Wx_test_sql --- [ 20,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\wx_pch.h -o wx_pch.h.gch\Debug_wx_pch_h_gch [ 40,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlApp.cpp -o obj\Debug\Wx_test_sqlApp.o [ 60,0%] windres.exe -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -J rc -O coff -i C:\MY_PRO~1\WX_TES~3\resource.rc -o obj\Debug\resource.res [ 80,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlMain.cpp -o obj\Debug\Wx_test_sqlMain.o [100,0%] mingw32-g++.exe -LC:\wxWidgets-2.8.12\lib\gcc_lib -o bin\Debug\Wx_test_sql.exe obj\Debug\Wx_test_sqlApp.o obj\Debug\Wx_test_sqlMain.o obj\Debug\resource.res -mthreads -lwxmsw28ud_richtext -lwxmsw28ud_xrc -lwxmsw28ud_aui -lwxmsw28ud_media -lwxbase28ud_net -lwxbase28ud_xml -lwxmsw28ud_adv -lwxmsw28ud_html -lwxmsw28ud_core -lwxbase28ud -lwxpngd -lwxjpegd -lwxtiffd -lwxzlibd -lwxregexud -lwxexpatd ..\sqlite\libsqlite.a -lkernel32 -luser32 -lgdi32 -lwinspool -lcomdlg32 -ladvapi32 -lshell32 -lole32 -loleaut32 -luuid -lcomctl32 -lwsock32 -lodbc32 -mwindows Output size is 31,03 MB Process terminated with status 0 (0 minutes, 14 seconds) 0 errors, 0 warnings (0 minutes, 14 seconds) ___ 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] Problem with sqlite and codeblocks (mingw)
No becuase, I need encryption and it is not supported bu wxslite3, I try Sqlcipher and I have exactly the same problem... http://forums.codeblocks.org/index.php/topic,15849.0.html I described it here but nobody can help me ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite and codeblocks (mingw)
I don't think you want shell.c in your library. That has a main which will conflict. You don't need it anways. 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 Robert Gdula [robert.gd...@gmail.com] Sent: Saturday, January 28, 2012 9:46 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Problem with sqlite and codeblocks (mingw) Hi, I have a problem with compilation sqlite for mingw and code:blocks, everything is ok and compilation don't show any abnormal information, but when I try only add library of sqlite to the linker of sample wxwidgets program it doesn't work. Everything is ok when I use normal cosole program for C++ but when I use wxwidgets everything is wrong, program compile without any errors and warning but don't show anything on the screen, when I try debugging. It is my build log of sqlite library (everything build as a static library): [ 33,3%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\shell.c -o obj\Debug\shell.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [ 66,7%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\sqlite3.c -o obj\Debug\sqlite3.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [100,0%] ar.exe -r -s libsqlite.a obj\Debug\shell.o obj\Debug\sqlite3.o ar.exe: creating libsqlite.a Output size is 1,28 MB The sam build log for my sample wxwidgets_app: -- Build: Debug in Wx_test_sql --- [ 20,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\wx_pch.h -o wx_pch.h.gch\Debug_wx_pch_h_gch [ 40,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlApp.cpp -o obj\Debug\Wx_test_sqlApp.o [ 60,0%] windres.exe -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -J rc -O coff -i C:\MY_PRO~1\WX_TES~3\resource.rc -o obj\Debug\resource.res [ 80,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlMain.cpp -o obj\Debug\Wx_test_sqlMain.o [100,0%] mingw32-g++.exe -LC:\wxWidgets-2.8.12\lib\gcc_lib -o bin\Debug\Wx_test_sql.exe obj\Debug\Wx_test_sqlApp.o obj\Debug\Wx_test_sqlMain.o obj\Debug\resource.res -mthreads -lwxmsw28ud_richtext -lwxmsw28ud_xrc -lwxmsw28ud_aui -lwxmsw28ud_media -lwxbase28ud_net -lwxbase28ud_xml -lwxmsw28ud_adv -lwxmsw28ud_html -lwxmsw28ud_core -lwxbase28ud -lwxpngd -lwxjpegd -lwxtiffd -lwxzlibd -lwxregexud -lwxexpatd ..\sqlite\libsqlite.a -lkernel32 -luser32 -lgdi32 -lwinspool -lcomdlg32 -ladvapi32 -lshell32 -lole32 -loleaut32 -luuid -lcomctl32 -lwsock32 -lodbc32 -mwindows Output size is 31,03 MB Process terminated with status 0 (0 minutes, 14 seconds) 0 errors, 0 warnings (0 minutes, 14 seconds) ___ 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 3.6.22] database .dump function cannot export table of large number of columns 2000
On 28 Jan 2012, at 3:31pm, Alexey Pechnikov wrote: 2012/1/28 Simon Slavin slav...@bigfraud.org: If this database is for longterm use, you're doing the wrong thing. Those should be different rows, not all one huge long row. If it's a one-off hack, then do it and get rid of it before anyone else sees it and shames you. Why not? Please send link to SQLite documentation instead of any astral reasons. It makes the database schema huge, taking up a lot of memory and a long time to search. So when you refer to a column called 'tr0997' it has to search a list of 1000 items in memory before it can work out which column number corresponds to the column called 'tr0997'. Also, it's very difficult to search and process. Suppose I want to find 'tr' values are equal to 123. How do I do it ? If all you need is serial storage just store the values in a long comma-separated value file, one line per record, with values separated by commas. This produces a far smaller file which is far faster to write, read and process. I'm not sure what your numeric indices are for but a more normal way to store that would be something like CREATE TABLE traces ( traceNumber INTEGER NOT NULL, itemNumber INTEGER, value FLOAT, PRIMARY KEY (traceNumber,itemNumber)); INSERT INTO traces (id,itemNumber,value) VALUES (1,0,1) That means you can store any number of items for each id (not limited to 1000) and the database engine can find each one directly without having to walk a long list. However, as I wrote above, if you're doing what you're doing for a quick hack, it doesn't matter. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite 3.6.22] database .dump function cannot export table of large number of columns 2000
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 27/01/12 20:45, Antony wrote: Did I miss any arguments in the .dump command to dump the database successfully? Putting aside the wisdom of structuring a table this way, you did indeed hit a problem in the SQLite code to dump a table. The .dump command emits the SQL for the table definition and then has this error: / ERROR: (1) Expression tree is too large (maximum depth 1000) */ Then it emits a ROLLBACK. This is because of the code used to do a dump. The SQLite shell concocts a select statement behind the scenes whose results are the needed insert statement (function dump_callback in the source) and it is the parsing of that that causes the error. If the dump code just iterated over the table directly outputting each value itself then things would work just fine. Added a ticket: http://www.sqlite.org/src/info/232637c465 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk8kSdcACgkQmOOfHg372QTH3QCfQUV5Ilz4YNOivf3gLky2FBcs QlwAoJB/roeWs1qHQczxOUt7WKcXSIfd =cQyn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite and codeblocks (mingw)
Thank you, You have right :) Thank's a lot :) 2012/1/28 Black, Michael (IS) michael.bla...@ngc.com I don't think you want shell.c in your library. That has a main which will conflict. You don't need it anways. 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 Robert Gdula [robert.gd...@gmail.com] Sent: Saturday, January 28, 2012 9:46 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Problem with sqlite and codeblocks (mingw) Hi, I have a problem with compilation sqlite for mingw and code:blocks, everything is ok and compilation don't show any abnormal information, but when I try only add library of sqlite to the linker of sample wxwidgets program it doesn't work. Everything is ok when I use normal cosole program for C++ but when I use wxwidgets everything is wrong, program compile without any errors and warning but don't show anything on the screen, when I try debugging. It is my build log of sqlite library (everything build as a static library): [ 33,3%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\shell.c -o obj\Debug\shell.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [ 66,7%] mingw32-gcc.exe -Wall -g -Weffc++ -w -Wextra -g -IC:\MY_PROJECT\SQLCIPHER\lib -c C:\MY_PROJECT\sqlite\sqlite3.c -o obj\Debug\sqlite3.o cc1.exe: warning: command line option '-Weffc++' is valid for C++/ObjC++ but not for C [enabled by default] [100,0%] ar.exe -r -s libsqlite.a obj\Debug\shell.o obj\Debug\sqlite3.o ar.exe: creating libsqlite.a Output size is 1,28 MB The sam build log for my sample wxwidgets_app: -- Build: Debug in Wx_test_sql --- [ 20,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\wx_pch.h -o wx_pch.h.gch\Debug_wx_pch_h_gch [ 40,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlApp.cpp -o obj\Debug\Wx_test_sqlApp.o [ 60,0%] windres.exe -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -J rc -O coff -i C:\MY_PRO~1\WX_TES~3\resource.rc -o obj\Debug\resource.res [ 80,0%] mingw32-g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DwxUSE_UNICODE -Wno-attributes -Winvalid-pch -include wx_pch.h -DWX_PRECOMP -Wall -g -D__WXDEBUG__ -Weffc++ -w -Wextra -g -IC:\wxWidgets-2.8.12\include -IC:\wxWidgets-2.8.12\contrib\include -IC:\wxWidgets-2.8.12\lib\gcc_lib\mswud -c C:\MY_PROJECT\Wx_test_sql\Wx_test_sqlMain.cpp -o obj\Debug\Wx_test_sqlMain.o [100,0%] mingw32-g++.exe -LC:\wxWidgets-2.8.12\lib\gcc_lib -o bin\Debug\Wx_test_sql.exe obj\Debug\Wx_test_sqlApp.o obj\Debug\Wx_test_sqlMain.o obj\Debug\resource.res -mthreads -lwxmsw28ud_richtext -lwxmsw28ud_xrc -lwxmsw28ud_aui -lwxmsw28ud_media -lwxbase28ud_net -lwxbase28ud_xml -lwxmsw28ud_adv -lwxmsw28ud_html -lwxmsw28ud_core -lwxbase28ud -lwxpngd -lwxjpegd -lwxtiffd -lwxzlibd -lwxregexud -lwxexpatd ..\sqlite\libsqlite.a -lkernel32 -luser32 -lgdi32 -lwinspool -lcomdlg32 -ladvapi32 -lshell32 -lole32 -loleaut32 -luuid -lcomctl32 -lwsock32 -lodbc32 -mwindows Output size is 31,03 MB Process terminated with status 0 (0 minutes, 14 seconds) 0 errors, 0 warnings (0 minutes, 14 seconds) ___ 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] Database is Locked
Hi, I'm using System.Data.Sqlite the latest version that supports .NET 4 in an ordinary .NET website. when trying to issue a single update to the database I'm receiving the error message saying that the database file is locked. there is only one connection open. In the connection string connection pooling has been set to off the website is being hosted on a shared hosting server. usualy there is no problem and , in the development enviroment I couldn't recreate the problem please help, if I won't manage to find a solution I would have to switch to MsAccess database in thie project, and maybe in future projects too, If I won't find the solution for this issue. thanks in advanced Shahar Weinstein. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
On Sat, Jan 28, 2012 at 23:36:36 +0200, Shahar Weinstein wrote: I'm using System.Data.Sqlite the latest version that supports .NET 4 in an ordinary .NET website. when trying to issue a single update to the database I'm receiving the error message saying that the database file is locked. there is only one connection open. In the connection string connection pooling has been set to off the website is being hosted on a shared hosting server. Sqlite needs wrtie access to the database file itself, ability to create and write to a file in the same directory as the database file and ability to create and write to a file in temporary directory. So first check permissions whether it can do all those operations. To select temporary directory, set TMP variable in environment before opening the database. And make sure you set the same temporary directory in all processes that will access the same database. -- Jan 'Bulb' Hudec b...@ucw.cz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
hi, thanks for your reply. I know there is no permissions problems since after some time when I try the same action, it succeeds. in the website, I manage to read/write/delete in different locations of the code. and since the website is on a shared hosting I cannot modify the TMP definitions, which I assume they exist. the fact that it does work for time to time , makes it difficult to debug. Shahar. 2012/1/29 Jan Hudec b...@ucw.cz On Sat, Jan 28, 2012 at 23:36:36 +0200, Shahar Weinstein wrote: I'm using System.Data.Sqlite the latest version that supports .NET 4 in an ordinary .NET website. when trying to issue a single update to the database I'm receiving the error message saying that the database file is locked. there is only one connection open. In the connection string connection pooling has been set to off the website is being hosted on a shared hosting server. Sqlite needs wrtie access to the database file itself, ability to create and write to a file in the same directory as the database file and ability to create and write to a file in temporary directory. So first check permissions whether it can do all those operations. To select temporary directory, set TMP variable in environment before opening the database. And make sure you set the same temporary directory in all processes that will access the same database. -- Jan 'Bulb' Hudec b...@ucw.cz ___ 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] Database is Locked
On Sun, Jan 29, 2012 at 00:43:07 +0200, Shahar Weinstein wrote: I know there is no permissions problems since after some time when I try the same action, it succeeds. in the website, I manage to read/write/delete in different locations of the code. Can it be that the database is really locked by another request running in parallel? Sqlite only allows one writer to the whole file and unless you set WAL mode, no readers while writing. and since the website is on a shared hosting I cannot modify the TMP Yes, you can. You should be setting them *inside* the application. At C level it's setenv() call, but I don't remember the exact .NET equivalent. definitions, which I assume they exist. They are almost certainly wrong on Windows, which it probably is. -- Jan 'Bulb' Hudec b...@ucw.cz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the definition/use/point with the xShm* procedures in sqlite3_io_methods, and what's the benefit of using WAL with those rather than the global file locking option?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/01/12 02:17, Mikael wrote: I have a hard time getting a clue of how exactly sqlite uses them, WAL is well documented at: http://www.sqlite.org/wal.html The latest major section talks about shared memory and the wal-index. It also includes a link to more details about the wal-index which points here: http://www.sqlite.org/fileformat2.html#walindexformat Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk8km4EACgkQmOOfHg372QSKVQCgn3p/I1acLmBCScAItsUnSaEB V/4AoMsDQfEgtJKP4eHYVtXzgO+bezj5 =EZ52 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
On 28 Jan 2012, at 11:47pm, Jan Hudec wrote: On Sun, Jan 29, 2012 at 00:43:07 +0200, Shahar Weinstein wrote: I know there is no permissions problems since after some time when I try the same action, it succeeds. in the website, I manage to read/write/delete in different locations of the code. Can it be that the database is really locked by another request running in parallel? Sqlite only allows one writer to the whole file and unless you set WAL mode, no readers while writing. If I get the setup correctly, this is a web-facing app. Some web servers start a process for each HTTP session, or for each item they serve. That means that even though he has only one application that accesses the database (an active web page) he might have two concurrent processes using the SQLite API to access the database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
