Re: [sqlite] Best Practice: Storing Dates
For the data collection systems we use we store only UTC in the database. The application can translate times to the appropriate time zone and format for the user as required. This variable complexity needs to be controlled into one layer of your program. Since governments, even some city ones, have the authority to change the time zone or implementation date for their population, there is a high potential for change. 3rd party time zone rule libraries can externalize most of the maintenance work without affecting the core app or the database. regards, Adam DeVita On Wed, Jan 14, 2015 at 12:57 PM, Simon Slavin slav...@bigfraud.org wrote: On 14 Jan 2015, at 5:53pm, Nigel Verity nigelver...@hotmail.com wrote: I generally just use a fixed-length 14-character string to store the date and time in MMDDHHMMSS format. It accommodates any time stamp across a 10,000 year timespan and also supports simple date/time comparisons and sorting. There is no problem with using that format. However I would advise you to make a note in your documentation, and/or to add comments to your code, saying what TimeZone these stamps are in. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Counting rows
From previous reading (years ago on this list) I normally do select count(1) from tableName ; to count the rows in a table. as an alternate, select count(primary_key_or_SomeIndexName) from tableName when trying to get an actual count. beware: select count(someField) from table; will not count rows where someField is null select count(1) from table; will. Adam On Thu, Dec 11, 2014 at 11:39 AM, Dominique Devienne ddevie...@gmail.com wrote: On Thu, Dec 11, 2014 at 4:19 PM, Simon Slavin slav...@bigfraud.org wrote: In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I have a little utility that connects to Oracle, and does a big UNION ALL query to get the counts of all my tables (82 currently): TOTAL: 1,900,343 rows in 20 tables (out of 82) 0.129u 0.051s 0:00.66 25.7% 0+0k 32+32io 0pf+0w (COLD) 0.128u 0.045s 0:00.34 47.0% 0+0k 0+32io 0pf+0w (HOT) Granted, it's not 300M rows, just 1.9M, but that's 660ms (340ms when in cache), and that's counting the startup and connect time (~ 170ms). The plan is INDEX (FAST FULL SCAN) of the PK (a raw(16) for a GUID). FWIW, for context/comparison. --DD PS: I was actually surprised it was that cheap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?
Your table definition seems to have a contradiction.
The expression INTEGER PRIMARY KEY is a special keyword that means
'auto-increment', which would be a default value. DEFAULT (random() )
would contradict the auto-increment instruction. The row id was being used
to generate the key.
On Thu, Sep 25, 2014 at 3:10 PM, Mark Lawrence no...@null.net wrote:
Plan:
CREATE TABLE x(
id INTEGER PRIMARY KEY DEFAULT (random()),
val VARCHAR
);
INSERT INTO x(val) VALUES ('a');
SELECT * FROM x;
Result:
id val
-- --
1 a
Expected result:
id val
--- --
4841191733402647298 a
I get the expected result if I create the table WITHOUT ROWID.
--
Mark Lawrence
___
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] Window functions?
dbase3 would give an error if you did not include all the non-aggregate fields in the Group By. (One could also step forward/backward in a row-set, so some crude windowing was available if one coded to do that.) on this: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. One usually codes to documented behavior because it it is less likely to change without notice. Thanks for the references about windowing functions. Very interesting. The point of what is heavy now vs in 2020 is well made. Is Windowing a Major endeavor, better for sqlite 4? Adam DeVita On Wed, Aug 27, 2014 at 8:25 PM, Keith Medcalf kmedc...@dessus.com wrote: On Wednesday, 27 August, 2014 13:17, Petite Abeille said: On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; Done. And no need for any windowing functions ... This peculiar behavior is very unique to SQLite. Not really. Sybase, SQL Server and DB2 do (or did do) the same thing. Most reasonable SQL engines will throw an exception when confronted with the above. SQLite calls it a feature. I personally see it as a misfeature. ( Ditto with tagging an implicit limit 1 to scalar queries. Anyway. ) Well, I kind of like the former (group by) behaviour. Tacking of an automatic limit 1 on a scalar subquery may lead one to make bad assumptions about the shape of one's data, however, if one actually knows what one is doing, I don't think this is a problem either. On the other hand, one could look at the current 'group by' behavior as exhibited by SQLite as a precursor to a proper, more formalize, handling of analytic functions :) Perhaps. On the other hand, I really do not understand why people want analytic functions -- we did perfectly well analyzing data long before they were invented. But then again I cannot understand why people think that Relational Databases using SQL are better for everything than good old-fashioned Network-Extended Navigational Databases. But then again, maybe I'm just an old fart ... ___ 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] Crash in sqlite3_mutex_try [Was: SQLite 3.8.6 beta]
1a) Somebody is paying you to do it. 1b) Incremental cost (or risk) of supporting it is small compared to the cost (or risk) of porting /upgrading Adam On Tue, Aug 12, 2014 at 2:46 AM, Klaas V klaasva...@yahoo.com wrote: Jan wrote: ** can manually set this value to 1 to emulate Win98 behavior. */ Can anyone give me one good reason apart from nostalgia to support a MS system not supported by MS? Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse, Klaas `Z4us` V - OrcID -0001-7190-2544 ___ 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 Support for CE x32 FW 3.5
Did you already read https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlite-netFx35-binary-PocketPC-ARM-2008 ? On Tue, Aug 5, 2014 at 10:32 AM, Tobias Stüker t.stue...@beckhoff.com wrote: Hello, I am developing an .NET Framework 3.5 application for a Windows CE x32 PC. Can I get a Library of SQLite for that System? Best regards, Tobias Stüker Beckhoff Automation GmbH | Managing Director: Dipl. Phys. Hans Beckhoff, Arnold Beckhoff Registered office: Verl, Germany | Register court: Gütersloh HRB 1803 ___ 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 synchronize two databases
Are the databases designed to allow you to perform such an operation easily? On the databases I do it with, I have designed in enough extra data in the db and logic in my code to handle sorting out what to do (which record to use) if both databases have different data with the same primary key. regards, Adam DeVita On Mon, Mar 10, 2014 at 8:15 AM, Simon Slavin slav...@bigfraud.org wrote: On 10 Mar 2014, at 12:58pm, Muhammad Bashir Al-Noimi mbno...@gmail.com wrote: May I get some help from you guys? If you're asking how to synchronise two SQL databases which may have had different commands executed on them, then you should know that this is an unsolved problem which involves many difficult questions. The reason nobody is giving you an answer is that nobody in the whole world has a good solution which works in all cases. One way involves logging all the commands executed on the databases, and playing back these commands on an unaltered copy of the database. And even this can lead to undesired results. 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 destroys civilization.
LOL! Hopefully they wrote credit at the top of the source file.
I saw season 1 of the show. Aaron is a good guy.
http://en.wikipedia.org/wiki/Revolution_%28TV_series%29
A
On Sun, Mar 2, 2014 at 9:40 PM, mm.w 0xcafef...@gmail.com wrote:
LOL
don't know if it will go thru see png
layer or neuron out of bounds !
Best Regards.
On Sun, Mar 2, 2014 at 2:04 PM, Richard Hipp d...@sqlite.org wrote:
On Sun, Mar 2, 2014 at 12:34 PM, Richard Hipp d...@sqlite.org wrote:
Reports on twitter say that the nanobots in the TV drama Revolution
have source code in the season two finale that looks like this:
https://pbs.twimg.com/media/BhvIsgBCYAAQdvP.png:large
Compare to the SQLite source code here:
http://www.sqlite.org/src/artifact/69761e167?ln=1264-1281
A video clip from the episode can be seen here:
http://www.nbc.com/revolution/video/repairing-the-code/2748856#i145567,p1
You can clearly see the SQLite code on the monitor. The dialog goes
something like this:
Aaron: Wait. Hold on. There.
Male actor 1: What?
Aaron: There's a memory leak here. This chunk of code. (Points to the
SQLite analyzeTable() routine). That's the problem. It's eating up all
available resources. It will force a segmentation fault. The whole
system
will crash!
At that point, I said Not in my code!
But upon closer inspection, Aaron is correct. The code has been altered
slightly. This is what Aaron is looking at (line numbers added):
01 static void analyzeTable(Parse *pParse, Table *pTab, Index *pOnlyIdx){
02 int iDb;
03 int iStatCur;
04 int *key = (char*)malloc(8*sizeOf(char))
05 assert( pTab!=0 );
06 assert( ecrypBtreeHoldsAllMutexes(pParse-db) );
07 iDb = ecrypSchemaToIndex(pParse-db, pTab-pSchema);
08 ecrypBeginWriteOperation(pParse, 0, iDb);
09 iStatCur = pParse-nTab;
10 pParse-nTab += 3;
11 if( pOnlyIdx ){
12 openStatTable(pParse, iDb, iStatCur, pOnlyIdx-zName, idx);
13 }else{
14 openStatTable(pParse, iDb, iStatCur, pTab-zName, tbl);
15 }
16 }
The changes from SQLite are (1) all sqlite3 name prefixes are changes
to
ecryp and (2) line 04 has been added. Line 04 is the memory leak.
It
also contains at least four other errors: (A) there is no semicolon at
the
end. (B) sizeof has a capital O. (C) It assigns a char* pointer to
an
int* variable. (D) It calls malloc() directly, which is forbidden inside
of SQLite since the application might assign a different set of memory
allocation functions. The first two errors are fatal - this function
won't
even compile. But, heh, it's a TV show
So there you go. SQLite used in evil nanobots that destroy civilization.
I've never actually seen Revolution (I don't own a TV set). So I don't
really understand the plot. Can somebody who has watched this drama
please
brief me? In particular, I'm curious to know if Aaron a good guy or a
bad
guy?
--
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
Re: [sqlite] Recommended way to delete rows
Yes. On Fri, Feb 28, 2014 at 12:18 PM, L. Wood lwoo...@live.com wrote: I expect #2 to work best. Make sure to enclose the whole thing in an explicit transaction (or at least, run large batches within explicit transactions; one implicit transaction per deleted row will be slow as molasses). If I do this, would you expect _step() for the BEGIN TRANSACTION query and _step() for each DELETE query to be very fast, but the _step() for the END TRANSACTION query to take most (99%) of the time? Would you expect a similar speed boost for INSERT? Is one by one INSERT in a similar way slow as molasses, and wrapping many inserts in a transaction recommended? ___ 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 use SQLite in C#
Good day, There are 2 different ideas to look into: 1) Using the API 2) Integrating tools into your IDE From the prospective of my 32-bit Windows 7 machine. Under 1) Using the API If you are just using the API, then you don't need very many of those files. using System.Data.SQLite; Be sure to reference System.Data.SQLite.dll. I've also got SQLite.Interop.dll in the path. Looking at the Windows Forms project I use and reference LINQ items, although they are not sqlite ones. 2) IDE Integration, meaning some graphics tools in Visual Studio etc. I think posting your version would help others help you. Between VS2008 and VS2010 some config parameters and directories got moved around. Integrating new tools with an installer designed for something older can be an arduous path to enlightenment. I haven't had the pleasure with other IDEs yet, and haven't had the need for the tools. regards, Adam DeVita On Wed, Feb 12, 2014 at 9:23 AM, Jamiil jam...@live.ca wrote: After downloading sqlite-netFx451-static-binary-x64-2013-1.0.90.0 and uncompressing it, I got this list of file: Installer.exe Installer.pdb northwindEF.db SQLite.Designer.dll SQLite.Designer.pdb SQLite.Designer.xml SQLite.Interop.dll SQLite.Interop.pdb sqlite_file_list.txt System.Data.SQLite.dll System.Data.SQLite.Linq.dll System.Data.SQLite.Linq.pdb System.Data.SQLite.Linq.xml System.Data.SQLite.pdb System.Data.SQLite.xml test.db test.exe test.exe.config test.pdb testlinq.exe testlinq.exe.config testlinq.pdb [ I tried the intaller.exe, but I get a msg saying: Cannot continue, the confirm option is not enabled. ] I have a project that looks like this: Project: | pro.exe |--- image |--- gui |--- mylib |--- sound |--- sqlite mysqlite.cs The files contained in the sqlite-netFx451-static-binary-x64-2013-1.0.90.0 are located in a folder that exists in the %path%, but I cannot stop to wonder if all the files are necessary or if all I need is the DLLs in order to reference the methods in the database, and if I only need certain files, which ones are those? My second question is, how can I add the DLLs from sqlite-netFx451-static-binary-x64-2013-1.0.90.0 to the 'mysqlite.cs' file in order to reference the its methods? Any help would be much appreciated. Thanks in advance. ___ 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] Proposed enhancement to the sqlite3.exe command-line shell
Good day, I'd rather the warning be in the text when you open the sqlite tool with an implied in memory database. Put an extra \n if you want the warning to stand out. Adam On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille petite.abei...@gmail.comwrote: On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck ggrothendi...@gmail.com wrote: The other features that would make teaching a bit easier would be to support left join explicitly and support the rfc4180 standard for csv files. Hmmm? Left join: http://www.sqlite.org/syntaxdiagrams.html#join-operator RFC-4180 compliant .import: http://sqlite.org/releaselog/3_8_0.html ___ 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] possible bug 3.8.1 /3.8.3
Good day, I'm debugging some code that uses 3.8.1, and I've tried just upgrading to 3.8.3, which didn't work. The observation is that This query: SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = ?) LIMIT 1 seems to occasionally produce a wrong result (the content of data_blob is incorrect given the values of stream_num) yet this query SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc LIMIT 1 seems just fine, insofar as the same tests on the same data have not hit any of the error condition / contradiction. in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for parameter ? We are using an in memory database as a smarter queue where timestamp data gets inserted, and if the db size is sufficient (30 to 40 records) the above query lets us pop the earliest timestamp (which is stored as int64 via sqlite3_bind_int64). Is this a possible bug or am I missing something? Using the backup api to look at it from a file sqlite.schema CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL, stream_num TINYINT, source_seq_num TINYINT, event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL); sqlite SELECT id, data_blob FROM data WHERE stream_num = 2 order by timestamp asc LIMIT 1; 4|☺ sqlite SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1; 3|☻ sqlite regards, Adam DeVita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible bug 3.8.1 /3.8.3
Good day all, Thank you for your replies. Yes, I can provide the data if required, although I don't think it is needed, as the bug is in the user's code. The point about what happens if several timestamps have the same value is valid, and in this case, I think is the explanation. sqlite SELECT id, timestamp, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = 2) ; 3|12946000654830|☻ 4|12946000654830|☺ 5|12946000654830|☺ sqlite SELECT id, timestamp, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = 2) and stream_num = 2; 4|12946000654830|☺ sqlite SELECT id, timestamp, data_blob FROM data WHERE stream_num = 2 order by timestamp asc; 4|12946000654830|☺ Obviously, there are several records with the same timetamp, and putting the restriction on the stream num ensures that the right one is picked. regards, Adam On Mon, Feb 3, 2014 at 12:47 PM, Richard Hipp d...@sqlite.org wrote: Can you provide data? Without some sample data, we cannot tell if the answer SQLite is providing is right or wrong. On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita adev...@verifeye.com wrote: Good day, I'm debugging some code that uses 3.8.1, and I've tried just upgrading to 3.8.3, which didn't work. The observation is that This query: SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = ?) LIMIT 1 seems to occasionally produce a wrong result (the content of data_blob is incorrect given the values of stream_num) yet this query SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc LIMIT 1 seems just fine, insofar as the same tests on the same data have not hit any of the error condition / contradiction. in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for parameter ? We are using an in memory database as a smarter queue where timestamp data gets inserted, and if the db size is sufficient (30 to 40 records) the above query lets us pop the earliest timestamp (which is stored as int64 via sqlite3_bind_int64). Is this a possible bug or am I missing something? Using the backup api to look at it from a file sqlite.schema CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL, stream_num TINYINT, source_seq_num TINYINT, event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL); sqlite SELECT id, data_blob FROM data WHERE stream_num = 2 order by timestamp asc LIMIT 1; 4|☺ sqlite SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1; 3|☻ sqlite regards, Adam DeVita ___ 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
Re: [sqlite] SQLite patch contribution
Is it possible for one in a nation that doesn't permit dedication to public domain to simply gift the work (and intellectual rights) to someone personally, who can and will the reassign it to the public domain (such as a member of the sqlite dev team)? On Mon, Jul 22, 2013 at 9:30 AM, Dušan Paulovič paulo...@gisoft.cz wrote: 1. I am not citizen of Czech Republic (but citizen of Slovak Republic with similar law) 2. If I do not want apply my copyrights, I can public my code without it. 2013/7/22 Richard Hipp d...@sqlite.org On Mon, Jul 22, 2013 at 8:10 AM, Dušan Paulovič paulo...@gisoft.cz wrote: Hello, I like to make a patch for SQLite so that function xBestIndex gets a collation sequences as a part of sqlite3_index_info structure. Patch will be binary compatible with previous versions, so all existing virtual table implementations will work with new version. I understand, that I must attach also public domain copyright statment to be SQLite team able to merge it. According to what I read in Wikipedia, citizens of the Czech Republic are not allowed to dedicate their work to the public domain. :-( What I do not know is: Can I somehow contact anybody about internal rules? Probably the sqlite-...@sqlite.org mailing list. Can I create new empty typedef for CollSeq struct? (name: sqlite3_coll_seq) Can I create new API functions? (sqlite3_coll_seq_strcmp, sqlite3_coll_seq_name, sqlite3_coll_seq_enc) What all documentation should I provide? (I am not english native speaker) Should be such code in #ifndef SQLITE_OMIT_VIRTUALTABLE blocks? Is there any chance that such patch will be merged to SQLite? It is an up-hill battle. New interfaces in SQLite must be supported forever, which is a lot of work for the core team. So in order to accept a new interface, we need to be convinced that there is lasting value for a large community of users and that this value is sufficient to justify the long-term support costs. Other obstacles include the copyright issue cited above, and the necessity of having 100% branch test coverage and complete documentation of the new features. -- 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 -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Command-line utility
http://www.sqlite.org/download.html scroll to Precompiled Binaries for Windows It runs just fine on 32 bit windows. Adam On Thu, Jul 11, 2013 at 12:20 PM, RSmith rsm...@rsweb.co.za wrote: Could someone send me a build with the current trunk of the command-line utility for Windows 32Bit with the standard option set for testing purposes please, or point me to where I can download it if a standard build already exists. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting Started with Sqlite
When you open the command prompt you will see something like this: c:\PINTSsqlite3.exe SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite The above clearly indicates version 3.6.10. Others on the list will correct me if I am wrong, but the command line utility does not use the DLL. If the path to the exe file is in your windows PATH environment variable folder you will be able to execute it. I normally start it from a command prompt (Start-run cmd) regards, Adam On Wed, May 22, 2013 at 9:51 AM, Sean Dzafovic sdzafo...@gmail.com wrote: On Wed, May 22, 2013 at 10:30 AM, Igor Tandetnik i...@tandetnik.org wrote: On 5/22/2013 8:58 AM, Sean Dzafovic wrote: I downloaded the shell and the dll from the sqlite.org site. I put the .dll in the windows/system32 folder. However, when I try to create a test db using the command sqlite3 test.db as per the example, I get Error: near sqlite3 :syntax error. What am I doing wrong? You are running this command on sqlite3 command line (do you see sqlite3 prompt?) Instead, you should run this command on Windows command line (Start Run cmd), in order to start sqlite3 shell with a given DB file as a parameter. Tried it with the shell on my desktop and got the error. Moved it to the system32 folder along with the .dll and got the same error. (Both of these by clicking on the icon). Same when I tried using the Windows command line. Either way when I open the shell it gives me a sqlite prompt, not sqlite3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database recovery
Where there any errors on .read dump_all.sql ? was PRAGMA integrity_check; on the new file or the old one? On Tue, May 14, 2013 at 10:29 AM, Marcin ign...@op.pl wrote: Hi everybody. Somehow my database got malformed, and journal file is unfortunately nowhere to found. Suprisingly, I'm able to open it in Sqlite Database Browser, and read stored values. There is only minor data loss, as I can't really see what got missing. I've also managed to figure which table is damaged. I've tried doing this: sqlite3.exe tshock.sqlite sqlite .mode insert sqlite .output dump_all.sql sqlite .dump sqlite .exit sqlite3.exe tshockfixed.sqlite sqlite .read dump_all.sql sqlite .exit But it still returns malformed one. PRAGMA integrity_check; returns this: *** in database main *** On tree page 3 cell 17: invalid page number 9049 On tree page 3 cell 17: Child page depth differs On tree page 3 cell 18: Child page depth differs On tree page 8948 cell 86: invalid page number 9047 On tree page 8948 cell 86: Child page depth differs On tree page 8948 cell 87: invalid page number 9051 On tree page 8948 cell 88: invalid page number 9055 On tree page 8948 cell 89: Child page depth differs On tree page 8948 cell 90: invalid page number 9061 On tree page 8948 cell 90: Child page depth differs On tree page 8948 cell 91: invalid page number 9066 On tree page 8948 cell 92: invalid page number 9069 On page 8948 at right child: invalid page number 9074 On tree page 9039 cell 69: invalid page number 9046 On tree page 9039 cell 69: Child page depth differs On tree page 9039 cell 70: invalid page number 9048 On tree page 9039 cell 71: invalid page number 9050 On tree page 9039 cell 72: invalid page number 9052 On tree page 9039 cell 73: invalid page number 9053 On tree page 9039 cell 74: invalid page number 9054 On tree page 9039 cell 75: invalid page number 9056 On tree page 9039 cell 76: invalid page number 9057 On tree page 9039 cell 77: invalid page number 9058 On tree page 9039 cell 78: invalid page number 9059 On tree page 9039 cell 79: invalid page number 9060 On tree page 9039 cell 80: invalid page number 9062 On tree page 9039 cell 81: invalid page number 9063 On tree page 9039 cell 82: invalid page number 9064 On tree page 9039 cell 83: invalid page number 9065 On tree page 9039 cell 84: invalid page number 9067 On tree page 9039 cell 85: invalid page number 9068 On tree page 9039 cell 86: invalid page number 9070 On tree page 9039 cell 87: invalid page number 9071 On tree page 9039 cell 88: invalid page number 9072 On tree page 9039 cell 89: invalid page number 9073 On tree page 9039 cell 90: invalid page number 9075 On tree page 9039 cell 91: invalid page number 9077 On page 9039 at right child: invalid page number 9076 Error: database disk image is malformed Does anybody got an idea, and can help me? ignac8 __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database recovery
If you can get the errors, by say redirecting the output from the .read to a file, the error message will likely be enlightening. When I recover corrupt db files, there are often some records with a conflicting primary key. In those cases I have to edit the input to fix the old one. Naturally, you desire a commit rather than a rollback when your .read can execute with no errors. Others on the list are far better at explaining the output of the integrity check than I am. Adam On Tue, May 14, 2013 at 11:40 AM, Marcin ign...@op.pl wrote: No errors, but tshockfixed.sqlite is empty, and in the end of file dump_all.sql there is ROLLBACK; -- due to errors. It's was check of tshock.sqlite, old one. ignac8 Sent: Tuesday, May 14, 2013 4:43 PM Subject: Re: [sqlite] Malformed database recovery Where there any errors on .read dump_all.sql ? was PRAGMA integrity_check; on the new file or the old one? On Tue, May 14, 2013 at 10:29 AM, Marcin ign...@op.pl wrote: Hi everybody. Somehow my database got malformed, and journal file is unfortunately nowhere to found. Suprisingly, I'm able to open it in Sqlite Database Browser, and read stored values. There is only minor data loss, as I can't really see what got missing. I've also managed to figure which table is damaged. I've tried doing this: sqlite3.exe tshock.sqlite sqlite .mode insert sqlite .output dump_all.sql sqlite .dump sqlite .exit sqlite3.exe tshockfixed.sqlite sqlite .read dump_all.sql sqlite .exit But it still returns malformed one. PRAGMA integrity_check; returns this: *** in database main *** On tree page 3 cell 17: invalid page number 9049 On tree page 3 cell 17: Child page depth differs On tree page 3 cell 18: Child page depth differs On tree page 8948 cell 86: invalid page number 9047 On tree page 8948 cell 86: Child page depth differs On tree page 8948 cell 87: invalid page number 9051 On tree page 8948 cell 88: invalid page number 9055 On tree page 8948 cell 89: Child page depth differs On tree page 8948 cell 90: invalid page number 9061 On tree page 8948 cell 90: Child page depth differs On tree page 8948 cell 91: invalid page number 9066 On tree page 8948 cell 92: invalid page number 9069 On page 8948 at right child: invalid page number 9074 On tree page 9039 cell 69: invalid page number 9046 On tree page 9039 cell 69: Child page depth differs On tree page 9039 cell 70: invalid page number 9048 On tree page 9039 cell 71: invalid page number 9050 On tree page 9039 cell 72: invalid page number 9052 On tree page 9039 cell 73: invalid page number 9053 On tree page 9039 cell 74: invalid page number 9054 On tree page 9039 cell 75: invalid page number 9056 On tree page 9039 cell 76: invalid page number 9057 On tree page 9039 cell 77: invalid page number 9058 On tree page 9039 cell 78: invalid page number 9059 On tree page 9039 cell 79: invalid page number 9060 On tree page 9039 cell 80: invalid page number 9062 On tree page 9039 cell 81: invalid page number 9063 On tree page 9039 cell 82: invalid page number 9064 On tree page 9039 cell 83: invalid page number 9065 On tree page 9039 cell 84: invalid page number 9067 On tree page 9039 cell 85: invalid page number 9068 On tree page 9039 cell 86: invalid page number 9070 On tree page 9039 cell 87: invalid page number 9071 On tree page 9039 cell 88: invalid page number 9072 On tree page 9039 cell 89: invalid page number 9073 On tree page 9039 cell 90: invalid page number 9075 On tree page 9039 cell 91: invalid page number 9077 On page 9039 at right child: invalid page number 9076 Error: database disk image is malformed Does anybody got an idea, and can help me? ignac8 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-usershttp://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] system.data.sqlite example code?
From this URL http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/ The basics of how to set your project and include/reference the Dlls are there. You should be able to adapt to your IDE. (I currently use VS2010 for C# sqlite projects.) and this one http://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteConnection.html The examples are shorter and more to the point. Good is subjective. For people with experience in sqlite or databases but are new to the C# api, I felt this was helpful to get me started. There are many ways to import spread sheets in various formats. If you have your IDE configured one can do inserts from within Visual Studio. You could also do it using the standard command line tool. You could write a small program to do it. This list has some lengthy discussions of the hazards and trials of CSV formal. (I personally favour TAB delimited text files for plane text over CSV because my users would never use a horizontal tab, but will put quotes and commas in the names of things.) I think that the discussion of importing data is a separate discussion from 'give some references to good examples of using the API'. Sometimes I just use the spread sheet to write the insert queries, if it is reasonable to do so. (Such as I can reasonably review it all to know the data is clean and safe.) WPF trees, grids etc. you should determine if the thing needs to be static or dynamic. Having done it, that type of code has several examples online and is more of a Windows Forms of WPF code discussion compared to sqlite. If you have a predefined db where the columns of your grid are not dynamic then there are lots of examples online. regards, Adam On Sun, Jan 27, 2013 at 9:50 AM, Don Goyette d...@donandcarla.com wrote: Hi All, I was wondering if anyone knows of a good websites or forums that have good articles and/or tutorials (with example code) on how to use system.data.sqlite with C#, .NET framework, running in Visual Studio 2012 Pro? And with a variety of data sources to get the initial data for populating the database tables (ie. CSV, Excel, existing related SQL/SQLite tables, XML, etc. I'm open as to data display choices (Windows Forms or WPF) and controls (List, Tree, Grid). I learn best by seeing the code, versus reading long papers or books. Thanks in advance! -Don ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] complex update
Thank you for the principal. I had to rewrite a little since I only wanted to affect the rows that were using the max entry. Adam On Tue, Dec 18, 2012 at 12:58 PM, Igor Tandetnik i...@tandetnik.org wrote: On 12/18/2012 12:27 PM, Adam DeVita wrote: There is a table products where has a location id. Unfortunately duplicate dictionary names got added to list of locations products haslocationid and a bunch of other stuff I can easily get the max (bad) and min (good) location ids associated with each name (I know I should have made the name field UNIQUE... mistakes were made years ago) how do I write an update that essentially says update products set locationid = good where locationid = bad , but do it for each good bad pair ? update Products set locationid = ( select min(locationid) from Locations where name = (select name from Locations L where L.locationid = Products.locationId) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] complex update
Good day, I'm attempting to fix some bad data: There is a table products where has a location id. Unfortunately duplicate dictionary names got added to list of locations products haslocationid and a bunch of other stuff I can easily get the max (bad) and min (good) location ids associated with each name (I know I should have made the name field UNIQUE... mistakes were made years ago) how do I write an update that essentially says update products set locationid = good where locationid = bad , but do it for each good bad pair ? I can see how to do it with insert or replace, but is there a way to do it with UPDATE? regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] just a test
Gmail users: You can set a Filter to ensure the Igor's messages are delivered to your in-box. I did this last week and since then his messages do not end up in my SPAM folder. If you search This message may not have been sent by... warning in Gmail help, there are instructions. Adam On Mon, Dec 10, 2012 at 8:09 AM, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: Clearly, Igor is too helpful and responds to too many messages... *rolls eyes* I'm not on gmail, so I didn't know this was even a problem, but hopefully it gets sorted out soon. Ryan On 09/12/2012 2:01 AM, dd wrote: Yes. Igor Tandetnik mails marked as a spam nowadays. I marked it as a NOT SPAM. On Sun, Dec 9, 2012 at 9:33 AM, Gabor Grothendieck ggrothendi...@gmail.comwrote: I am still having problems with Igor's gmail messages being marked as spam in gmail but after the upteenth time declaring them not to be spam google finally asked me if I wanted to report it to their gmail team so hopefully they will fix it soon. On Mon, Dec 3, 2012 at 11:59 PM, Clive Hayward haywa...@chayward.com wrote: Igor's messages sometimes get marked as spam by gmail. -- Clive Hayward On 2012-12-03, at 7:57 AM, e-mail mgbg25171 mgbg25...@blueyonder.co.uk wrote: I've posted a couple of mails lately...I'm not getting them via the list or any responses. Admin says Igor responded to one of them...Thanks Igor! This is just a test to see if the mail is coming to me (as a member of the list). Therefore please just ignore this. ___ 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 -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VC++ and SQLite
It isn't VS2010 specific. Even going back to VS6 writing your own C++ wrapper and including the.c file you had to tell it to not use precompiled headers for that file. (Both Debug and Release builds) You should tell VS that this file will not ever be using precompiled headers. On VS2012 Professional Edition one can: Right click on the file within VS10, select Properties. Open the C/C++ tree. Select Precompiled Headers. Set Precompiled Header to Not Using Precompiled Headers. Adam On Mon, Nov 12, 2012 at 10:17 AM, John Drescher dresche...@gmail.com wrote: I know this question is not a SQLite question, but I am hoping that someone here has had a similar experience and/or can point me to the right place to ask this question. After years or using Code::Blocks and Dev-Cpp, I have recently installed Visual Studio 10 Express; it is the first time I am using it, in my Windows 7 machine. I have written, with the help of this mailing list a wrapper class for the latest SQLite3 library using C::B as my development platform, now that I want to switch to VS10, there were a lot of gcc specific code that I had to repair and after clearing all the C++ discrepancies between MinGW's g++ and MS's VC++ I have been left with this error message: fatal error C1853: 'Debug\sql.pch' precompiled header file is from a previous version of the compiler, or the precompiled header is C++ and you are using it from C (or vice versa Does anyone know how to resolve this issue or perhaps a VS10 specific You can like the other poster said disable PCH in visual studio or just delete all the PCH files and have VS rebuild them. The second is what I do in Visual Studio retail versions when I get this error. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB
Thanks for the clarification. Adam On Sat, Oct 27, 2012 at 12:13 PM, Simon Slavin slav...@bigfraud.org wrote: On 27 Oct 2012, at 6:36am, Dan Kennedy danielk1...@gmail.com wrote: On 10/27/2012 07:06 AM, Simon Slavin wrote: On 26 Oct 2012, at 11:05pm, Clemens Ladischclem...@ladisch.de wrote: Yes; sqlite3_finalize _always_ frees the statement. And if the statement is already finalized (due to an earlier error, perhaps) then it is a harmless noop. So you can do it near the end of your routine harmlessly. That's a bit deceptive. Passing the same pointer to sqlite3_finalize() twice is undefined behavior. You might get an SQLITE_MISUSE error, but you also might get a segfault. Oh, right. It releases the memory the statement was using. Sorry. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] statement prepares OK but step returns SQLITE_NOTADB
Good day, As an error check, I've got a program opening an encrypted file. sqlite3_prepare_v2 returns SQLITE_OK and ppStmt is not null. When I run sqlite3_step(ppStmt) it returns SQLITE_NOTADB. Recognizing an error at this point I'd like to clean up properly. sqlite3_finalize(ppStmt) returns SQLITE_NOTADB. At this point, has it actually cleared the prepared statement, so I can set ppStmt = NULL and carry on to close the db and tell the user they shouldn't have opened that file? http://www.sqlite.org/capi3ref.html#sqlite3_finalize is a bit brief on the success of finalizing the statement given that it returned an error. I'm using the basic amalgamation c file SQLite version 3.7.4. regards, Adam DeVita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't SQLite drop columns?
There are some applications where using a 3rd party utility is inherently awkward and time consuming, but using your application that uses the sqlite api is not. (For example, you can change your code via an automatic update mechanism, but perhaps can't execute a 3rd party tool that likely isn't on the user's system anyway.) Use some 3rd party utility isn't really an answer to Why can't I use this command?. The OP didn't ask how can they alter the table, they know how. The lack of ALTER TABLE means that your application would have to do it This is simple enough to do, but means you have to write your own implementation of alter table, to some degree of complication or duplication, and of course with more lines of code comes an increased risk of writing a new bug. The why seems to be something about the necessity of rewriting the table, which is 'slow'. I expect that users accept that Alter table can be expensive.Is there more to it? Some sort of ACID breakdown? regards, Adam DeVita On Tue, Oct 9, 2012 at 12:45 PM, Peter Haworth p...@lcsql.com wrote: Pete lcSQL Software http://www.lcsql.com You're probably already aware of this but there are third party tools available that will do this for you, plus many other schema maintenance functions that aren't provided in sqlite. One such is my SQLiteAdmin program, available at www.lcsql.com. On Tue, Oct 9, 2012 at 9:00 AM, sqlite-users-requ...@sqlite.org wrote: Message: 11 Date: Mon, 08 Oct 2012 21:57:21 +0200 From: Yves Goergen nospam.l...@unclassified.de To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: [sqlite] Why can't SQLite drop columns? Message-ID: 50733021.8020...@unclassified.de Content-Type: text/plain; charset=UTF-8 Hello, I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike probably every other SQL database system. But every time I come across this, I feel the pain of having to write huge amounts of code to automatically remove single columns in a table. When doing that in code, it's usually working non-interactively on some generic table schema and cannot use hard-coded column names. So I really have to collect all relevant data including foreign keys and all column attributes and then generate the right SQL code to copy everything right except the dropped column. I very much believe that it would save a lot of developers' resources if SQLite supported that directly. After all, the DBMS has all the data it needs in its readily readable data structures. It would possibly be less work for SQLite than for anybody using it. So I am asking: Why does SQLite still not support dropping columns through SQL after all these years? Do the SQLite developers have strong arguments against it, and which? Are there technical limitations (I can't believe that)? Is there some kind of religion behind it? -- Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLLite Question regarding instances and files
Good day, You are treating the database files as test log files, correct? If you are wanting a PC to execute a program that accesses an sqlite database file on a network, it is possible. If you read through previous discussions in this mail list archive you will find numerous warnings and challenges people have while attempting to share a db on a network directory. a) Performance b) Issues handling unexpected loss of connection or media c) Issues handling multiple clients attempting to access the same database file. Have you considered creating the little local databases and then moving them to a network share when the test is done? regards, Adam On Mon, Sep 17, 2012 at 1:08 PM, Wilk, John (J.R.) jwil...@ford.com wrote: My question is that I have a client who would like to be able to have a different database file for each group of data they are collecting (a test involving data acquisition). The database file would be saved to a network share that would occasionally get backed up by the network admin. The database itself would be run locally. Every time a new test was run a new database file would be generated and every time a test needed to be analyzed a different database file would have to be opened. I know this is a little unorthodox and without getting into why the client wishes to do this I was wondering if it's even possible much less a good idea? Thanks John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C# Dynamic data type
Interesting idea. Thanks.
Adam
On Tue, Aug 7, 2012 at 12:07 PM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
You can use sscanf to determine data type...I've done it before using a
method that's not obvious...
You parse from most restrictive to least restrictive format like this...this
will accept any valid float format including scientific notation.
#include stdio.h
enum {UNKNOWN, FLOAT, INT, STRING};
int datatype(char *s)
{
long i;
double f;
char buf[4096];
int n;
n = sscanf(s,%d%s,i,buf);
if (n == 1) {
printf(INT\n);
return INT;
}
n = sscanf(s,%lg%s,f,buf);
if (n == 1) {
printf(FLOAT\n);
return FLOAT;
}
n = sscanf(s,%s,buf);
if (n == 1) {
printf(STRING\n);
return STRING;
}
else {
printf(UNKNOWN\n);
return UNKNOWN; // should never get here
}
}
main()
{
char *line1=1234;
char *line2=1234.5;
char *line3=x1234.5;
datatype(line1);
datatype(line2);
datatype(line3);
}
~
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 Adam DeVita [adev...@verifeye.com]
Sent: Tuesday, August 07, 2012 10:26 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] C# Dynamic data type
Good day,
I've been reading a bit of conflicted stuff online in terms of data type.
The most basic question, in C#, is can you easily determine the data
type of the Nth entry in a column.
{Ex: Create table A( x TEXT, y )
... a few inserts, binding a float, then a string, then an int into y..
select x,y from A
check the type of y before retrieving a value from it.
}
The docs for SQLiteDataReader.GetFieldType() seems to read as if it
will return the column affinity.
regards,
Adam
___
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
--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C# Dynamic data type
Good day,
I've been reading a bit of conflicted stuff online in terms of data type.
The most basic question, in C#, is can you easily determine the data
type of the Nth entry in a column.
{Ex: Create table A( x TEXT, y )
... a few inserts, binding a float, then a string, then an int into y..
select x,y from A
check the type of y before retrieving a value from it.
}
The docs for SQLiteDataReader.GetFieldType() seems to read as if it
will return the column affinity.
regards,
Adam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 database unreadable on Mountain Lion
Good day, I had a similar sounding issue on 2 different flavours of Windows. The problem was an over active anti-virus program. Adam On Fri, Aug 3, 2012 at 11:45 AM, Simon Slavin slav...@bigfraud.org wrote: On 3 Aug 2012, at 3:33pm, Tobias Giesen tobiasgie...@gmail.com wrote: I have one particular type of database that has become unreadable on the new Mac OS 10.8. It must be related to the SQL structure. The error I get is database disk image is malformed. But the same file, on Snow Leopard, works fine. The SQLite version on Snow Leopard is 3.6.12, and on Mountain Lion it is 3.7.12. How are you accessing this file ? Are you using your own application or are you using the shell tool included with Mac OS X in /usr/bin/sqlite3 ? In the folder where you find the database file on your 10.7 computer, are there any other files with similar names ? They may be journal files for when the database was not closed properly. The strange thing is, when I attempt to load the sqlite3.dylib from Snow Leopard under Mountain Lion, it also does not work. But I'm not totally sure if loading the older sqlite3 library actually worked. That may be totally unrelated to the file format. You may be trying to open the wrong dynamic library, or one compiled for a different OS, or something. Check out the database itself using the shell tool, then involve a dynamic library only once you're sure the database file is okay. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not sure how to interrupt this
SQLITE DONE is what you get when you successfully run an insert. What is the problem? Adam On Wed, Jun 27, 2012 at 7:02 PM, Jeff Archer jarch...@yahoo.com wrote: I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement is clearly being executed. SQL: INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result); The table has been created as: CREATE TABLE [Scans] (ScanID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,Result VARCHAR ); CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime, Result); CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp); I have SQLITE_CONFIG_LOG callback installed: sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL); I get the following message through the SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database schema has changed SQLITE_VERSION3.7.13 - amalgamation Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with including sqlite3.c into c++ project
Do not use precompiled headers on sqlite3.c On Mon, Jun 25, 2012 at 4:22 PM, Pavel Ivanov paiva...@gmail.com wrote: On Mon, Jun 25, 2012 at 4:15 PM, deltagam...@gmx.net deltagam...@gmx.net wrote: Hello, Im using MSVS 2010 for an c++ GUI project. After including sqlite3.h and sqlite3.c from the amalgamation-3071200 and with the Project Properties-- C/C++ -- Precompiled Headers -- Precompiled Header -- Use (/Yu) I get the error sqlite3.c : fatal error C1853: 'Debug\Contegos_UI.pch' precompiled header file is from a previous version of the compiler, or the precompiled header is C++ and you are using it from C (or vice versa) If I change to Precompiled Header -- Create (/Yc) I get the error sqlite3.c(136660): error C2857: '#include' statement specified with the /YcStdAfx.h command-line option was not found in the source file How can I solve this problem ? Change it to Precompiled Header -- Not Using Precompiled Headers. Because you won't use the same headers to compile your application and sqlite3.c. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Problem with SQLite when deployed.
Are you saying that you can manually place the dlls and exes on different computers and have it work? regards, Adam On Mon, May 28, 2012 at 7:36 AM, Peter Walburn peter.walb...@omega-data.com wrote: Okay, I've tried everything I can think of to get this to work. I've been at it for 4 days now!! I know that it is possible, because it did run on the 32-bit XP system that I was running my installation on - that's when I copied the 32-bit SQLite.Interop.dll from the 64-bit OS to the 32-bit OS. Then I rebuilt the install routine to use this file, but it has not worked since. I have absolutely no idea what to try next. Is there a similar program to SQLite that I might be able to use in C#? Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Peter Walburn Sent: 28 May 2012 10:13 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. I thought that things were going to work ok on Friday, but now that my Install routine copies the 32-bit DLL files onto the 32-bit operating system, I am receiving the same errors. Installing the application (using the same install routine) on a 64-bit operating system does work fine. This makes me think that there is something I am doing wrong and maybe the 32 and 64-bit DLLs are getting mixed up. Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Peter Walburn Sent: 25 May 2012 16:48 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. I've just been trying some other things and it seems that the SQLite.Interop.dll file that I have copied to the 32-bit operating system is the 64-bit version. I think it will work if I make sure that the 32-bit versions of the DLLs are included in my install program. Will the 32-bit versions work on 64-bit operating systems too? Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: 25 May 2012 16:37 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 10:57 AM, Peter Walburn peter.walb...@omega-data.com wrote: Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: The message's content type was not explicitly allowed. Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com This is an email from Omega Data Services Ltd, a company registered in Edinburgh, Scotland, with company number SC192323. Registered Office: Maclay Murray Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel: 01224-356130. Website: www.omega-data.com http://www.omega-data.com/. This email and any files transmitted are confidential and intended solely for the individual or entity to whom they are addressed. Any views or opinions expressed or presented are those
Re: [sqlite] FW: Problem with SQLite when deployed.
Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 10:57 AM, Peter Walburn peter.walb...@omega-data.com wrote: Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: The message's content type was not explicitly allowed. Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com This is an email from Omega Data Services Ltd, a company registered in Edinburgh, Scotland, with company number SC192323. Registered Office: Maclay Murray Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel: 01224-356130. Website: www.omega-data.com http://www.omega-data.com/. This email and any files transmitted are confidential and intended solely for the individual or entity to whom they are addressed. Any views or opinions expressed or presented are those of the author(s) and may not necessarily represent those of the company and no representation is given nor liability accepted for the accuracy or completeness of any information contained in this email unless expressly stated to the contrary. If you are not the intended recipient or have received this email in error, you may not use, disseminate, forward, print or copy it, but please notify the sender that you have received it in error and remove the message from your system immediately. Whilst we have taken reasonable precautions to ensure that this email and any attachments have been checked for viruses, we cannot guarantee that they are virus free, and we cannot accept liability for any damage sustained as a result of software viruses. We would advise that you carry out your own virus checks, especially before opening an attachment. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Problem with SQLite when deployed.
Under normal circumstances, same directory as the exe is part of a default path of places Windows looks. I just ran my 32 bit code on a windows 7 64 bit machine. It worked ok ( It is just a count records in this table and display it on a test label. ). For device drivers, the 32 64 bit difference can be incompatible. For other libraries, DLL functions are just like exe ones. The 64 bit machine should execute the 32 bit code. I'm testing with the .NET 3.5 SP1 32 bit code. Adam On Fri, May 25, 2012 at 11:47 AM, Peter Walburn peter.walb...@omega-data.com wrote: I've just been trying some other things and it seems that the SQLite.Interop.dll file that I have copied to the 32-bit operating system is the 64-bit version. I think it will work if I make sure that the 32-bit versions of the DLLs are included in my install program. Will the 32-bit versions work on 64-bit operating systems too? Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: 25 May 2012 16:37 To: General Discussion of SQLite Database Subject: Re: [sqlite] FW: Problem with SQLite when deployed. Simple thing first: Did you ensure that the SQLite.Interop.dll is in the path that the PC is searching? regards, Adam On Fri, May 25, 2012 at 10:57 AM, Peter Walburn peter.walb...@omega-data.com wrote: Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: The message's content type was not explicitly allowed. Peter Walburn Software Engineer E-mail: peter.walb...@omega-data.com Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen AB21 0GP Tel: +44 (0)1224 772763 Fax: +44 (0)1224 772783 www.omega-data.com This is an email from Omega Data Services Ltd, a company registered in Edinburgh, Scotland, with company number SC192323. Registered Office: Maclay Murray Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel: 01224-356130. Website: www.omega-data.com http://www.omega-data.com/. This email and any files transmitted are confidential and intended solely for the individual or entity to whom they are addressed. Any views or opinions expressed or presented are those of the author(s) and may not necessarily represent those of the company and no representation is given nor liability accepted for the accuracy or completeness of any information contained in this email unless expressly stated to the contrary. If you are not the intended recipient or have received this email in error, you may not use, disseminate, forward, print or copy it, but please notify the sender that you have received it in error and remove the message from your system immediately. Whilst we have taken reasonable precautions to ensure that this email and any attachments have been checked for viruses, we cannot guarantee that they are virus free, and we cannot accept liability for any damage sustained as a result of software viruses. We would advise that you carry out your own virus checks, especially before opening an attachment. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- This message has been scanned for viruses and dangerous content by Converged, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org
Re: [sqlite] Please review this email to sqlite's mailing list
Did you check out http://www.sqlite.org/inmemorydb.html Could you use an in-memory db to act as a db for a save point? When you are ready to commit, do so from 1 in memory db, while accumulating into another in preparation for that save point. Adam On Tue, May 15, 2012 at 1:55 PM, Charles Samuels char...@cariden.comwrote: I'm using sqlite in addition to another database (otherdb) storing data in a specific manner. I'm trying to keep atomicity of my disk commits. It can take several minutes for otherdb to commit, and while it commits it can already start accumulating data for a future transaction. Some of the data coming into this application also goes into the sqlite database. But I'd like to keep what's on the oxide between sqlite and otherdb consistent with eachother. Let's accept that otherdb At some point, we get a checkpoint; at this instant, what is in otherdb and what is in sqlite is what we want committed to sqlite, if either of them fails, we can rollback both of them and both databases return to a consistent state of a previous checkpoint. The problem is that in the time between checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving. The question here is: where can I put that more data so that it won't be part of checkpoint 1, but is still accessable by sqlite select statements? (Accept that otherdb allows asychronous commits such that I can add more data to it that doesn't wind up on disk). There's a few possibilities with some serious disadvantages: * When otherdb completes its checkpoint, I commit sqlite; until otherdb and sqlite finish their commits, any data going into sqlite instead goes into a mirror sqlite that I can do queries against meanwhile (but then I have to replay *all* of those modifications against the primary sqlite). This can cost huge amounts of memory because the sqlite database can get big: 3GiB or more. It's also slow because all of a sudden I have to do a whole bunch of sqlite statements. It's even slower because now any update I do *normally* has to be cloned. * I could write a virtual filesystem layer for sqlite that somehow accumulates changes that I can merge in with insert statements. So it's like the previous solution but I use some arm waving in combination with smoke and mirrors to at least not make me have two total copies of the database. The problem with this one is I don't know how to do it, and even if I did, I wouldn't know how reliable it was. * If sqlite had a commit transaction to savepoint X, then sqlite commits to the oxide everything up to a specific savepoint, keeping the savepoints after those committed still as active and uncommitted savepoints. The only disadvantage I can think of to this is that sqlite has no such feature. So how could I do this? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Explain what 'transaction' means in javascript for sqlite, please?
STW: http://stackoverflow.com/questions/740523/getting-a-webkit-executesql-transaction-to-return-a-value other references http://stackoverflow.com/questions/61972/javascript-sqlite On Wed, May 9, 2012 at 10:32 AM, Pavel Ivanov paiva...@gmail.com wrote: And again you wrote to the wrong mailing list. Use Reply button please. Pavel On Wed, May 9, 2012 at 10:27 AM, Andrew Lewis andy.lewi...@gmail.com wrote: Hi Pavel, I disagree I'm afraid. It is instrumental in getting SQLIte up and running in a javascript client-side environment, which SQLite lauds itself for. Whilst it is in a wrapper for SQLite to operate within the javascript code, it is integrated to such an extent that there is no reasonable separation. I am hopeful someone from the SQLite domain will be able to help. All the best, Andrew. On 9 May 2012 15:02, Andrew Lewis andy.lewi...@gmail.com wrote: Hi! Can someone help me over a hump here please? I am trying to fire up SQLite in javascript for mobiles and whilst there are plenty of sites which contain the functions for creating a footballer list on the screen, some of which seem to work, none of them explains the 'transaction' call in db.transaction(call1, call2, callerror) I know of the SQL 'TRANSACTION' , BEGIN etc, but not of any Javascript command called transaction. It seems to be building an anonymous function but I cannot work that out. Can someone please explain? -- Andrew J. Lewis MSc FLS MRI Simul Systems Ltd Chelmsford www.sysenvir.com 44-(0)7710 588318 -- Andrew J. Lewis MSc FLS MRI Simul Systems Ltd Chelmsford www.sysenvir.com 44-(0)7710 588318 ___ sqlite-dev mailing list sqlite-...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .NET and network server
Good day, Is anyone using .NET c# code as a client to connect to one of the free network server implementations? regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] error 404
Good day, This page http://sqlite.org/cvstrac/wiki?p=SqliteNetwork Is giving an error 404 for this link *SQL4Sockets* (http://www.oneledger.co.uk/sql4sockets.html) regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] type-o on http://www.sqlite.org/download.html
Good day, This sentence on http://www.sqlite.org/download.html Visit the System.Data.SQLite.org http://system.data.sqlite.org/ website and especially the download pagehttp://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wikifor source code an binaries of SQLite for .NET is missing a letter 'd'. The word an should be and. source code and binaries regards, Adam DeVita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] why one row's results isn't the same between program and command shell
Good day, I'm just exporting data to another db of known same structure, and setting a flag. It seems that one row shows an incorrect result, but only so in my c++ code. I extracted the queries being run using my debugger, and tried them in the command prompt. When I run the following batch of queries. Table is CREATE TABLE WorkStations (WSIDtxt text primary key, LocationID integer, record_updatetime text, write_out_ok int default 0); Command Prompt results: Tests prepared with: update workstations set write_out_ok=0; sqlite select * from workstations; WSIDtxt|LocationID|record_updatetime|write_out_ok DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0 PROGRAMMER-LAB00-1E-90-31-8D-19|3||0 PROGRAMMER-LAB00-03-0D-00-00-01|3||0 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 attach 'Z:\PINTS\modules\deltafiles\deltas\2012.03.21_ADAM-PC00-25-64-8C-5A-3B_r006.db' as 'foo' ; insert into foo.WorkStations select distinct * from main.WorkStations M where M.write_out_ok =0 and M.wsidtxt is not null; update WorkStations set write_out_ok =1 where wsidtxt in (select wsidtxt from foo.WorkStations ) ; sqlite select * from foo.workstations; ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0 PROGRAMMER-LAB00-03-0D-00-00-01|3||0 PROGRAMMER-LAB00-1E-90-31-8D-19|3||0 sqlite select * from workstations; DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1 PROGRAMMER-LAB00-1E-90-31-8D-19|3||1 PROGRAMMER-LAB00-03-0D-00-00-01|3||1 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|1 After executing a program that should have identical results, as identical queries are run: foo.workstations is identical. sqlite select * from workstations; WSIDtxt|LocationID|record_updatetime|write_out_ok DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1 PROGRAMMER-LAB00-1E-90-31-8D-19|3||1 PROGRAMMER-LAB00-03-0D-00-00-01|3||1 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 (Windows 7 32 bit edition PC. Tried with new copy of amalgamation 3.7.11 as well as 3.7.4) Why is the last entry wrong? Any suggestions as to how to review? regards, Adam DeVita VerifEye Technologies Inc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why one row's results isn't the same between program and command shell
Sorry for false alarm. Please disregard this thread: Solution: make sure you quit everything and isolate the code. There is a subsequent write to the new record that made it appear as a problem, when it wasn't. Adam On Wed, Mar 21, 2012 at 3:25 PM, Adam DeVita adev...@verifeye.com wrote: Good day, I'm just exporting data to another db of known same structure, and setting a flag. It seems that one row shows an incorrect result, but only so in my c++ code. I extracted the queries being run using my debugger, and tried them in the command prompt. When I run the following batch of queries. Table is CREATE TABLE WorkStations (WSIDtxt text primary key, LocationID integer, record_updatetime text, write_out_ok int default 0); Command Prompt results: Tests prepared with: update workstations set write_out_ok=0; sqlite select * from workstations; WSIDtxt|LocationID|record_updatetime|write_out_ok DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0 PROGRAMMER-LAB00-1E-90-31-8D-19|3||0 PROGRAMMER-LAB00-03-0D-00-00-01|3||0 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 attach 'Z:\PINTS\modules\deltafiles\deltas\2012.03.21_ADAM-PC00-25-64-8C-5A-3B_r006.db' as 'foo' ; insert into foo.WorkStations select distinct * from main.WorkStations M where M.write_out_ok =0 and M.wsidtxt is not null; update WorkStations set write_out_ok =1 where wsidtxt in (select wsidtxt from foo.WorkStations ) ; sqlite select * from foo.workstations; ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|0 PROGRAMMER-LAB00-03-0D-00-00-01|3||0 PROGRAMMER-LAB00-1E-90-31-8D-19|3||0 sqlite select * from workstations; DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1 PROGRAMMER-LAB00-1E-90-31-8D-19|3||1 PROGRAMMER-LAB00-03-0D-00-00-01|3||1 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|1 After executing a program that should have identical results, as identical queries are run: foo.workstations is identical. sqlite select * from workstations; WSIDtxt|LocationID|record_updatetime|write_out_ok DT00-13-D3-50-AF-F9|2|2009.02.03.13.41|1 PROGRAMMER-LAB00-1E-90-31-8D-19|3||1 PROGRAMMER-LAB00-03-0D-00-00-01|3||1 ADAM-PC00-25-64-8C-5A-3B|3|2012.03.21.18.57.19|0 (Windows 7 32 bit edition PC. Tried with new copy of amalgamation 3.7.11 as well as 3.7.4) Why is the last entry wrong? Any suggestions as to how to review? regards, Adam DeVita VerifEye Technologies Inc. -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert image into db - windows batch
You should be able to do it.
We put a shot set of hex data in using a script. You likely want to load
your file into a variable of hex (or binary) type, if you don't want to
write a script that writes a script.
Then
insert into your_table (f1, blob) values ( your_f1_value ,
X'reference_to_your_hex_data' )
the X' ' to denotes your hex value. This is listed in the documentation.
regards,
Adam DeVita
On Wed, Jan 18, 2012 at 10:02 AM, Petr Lázňovský la...@volny.cz wrote:
On 18 Jan 2012, at 12:30pm, Petr Lázňovský wrote:
have windows batch working with sqlite, may I insert image into
database and than read this images from?
Convert your image into a BLOB and store it as a BLOB. BLOBs are just
runs of bytes -- you can store anything you want as a BLOB.
What you mean by Convert image into a BLOB is there some kind of SW
to do this? Does SQLite offer some way to do this? Sorry for dumb question,
but I googling about this some time with no luck..
If you don't already know how to use your programming language to store
integers and strings in a SQLite database, then learn that first. Once you
have software which can do that, read on:
An image (assuming you mean a file like a .jpeg or .png file) is just a
long run of bytes. You can store a long run of bytes in a SQLite database
as data of type 'BLOB'. This isn't a string, or a number, or a date, it's
just a long run of bytes which is stored exactly as supplied with no
interpretation.
So in your software, open the image file and read the contents of the
file into memory. Then use the SQLite library routine to create a new row,
and bind that piece of memory to a BLOB. When you want to retrieve that
data, read the BLOB back out of the database. Then if you want to make an
image file of it you can do that. If you want to display the image on the
screen without making a file of it, you can do that instead if your
programming language gives you way to do it.
The exact routines to use depends on the language your software is
written in: C, Python, PHP, whatever. That's all down to your personal
programming choice. But all the commonly-used interfaces to SQLite have
the ability to handle BLOBs.
Simon,
did you read the subject of my mail? I am use sqlite from Win batch
(shell) scripting by commands like:
sqlite3.exe main.db Insert into Table1 values('','','');
or
sqlite3.exe main.db select * from Table1 where Column='';
I am currently not a programmer (means Do not know any REAL language, only
partialy Win shell) and this is my first deal with databases at all. So
please be patient with me ;-)
In Win shell AFAIK everything is a text, there are no data types. I spent
much time with google, but seems nobody uses this combination (Win shell +
sqlite) so there are very few informations on web :-/
L.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Auto index with wrong number of entries
Success! On Tue, Jun 28, 2011 at 11:34 AM, Simon Slavin slav...@bigfraud.org wrote: On 28 Jun 2011, at 4:22pm, Adam DeVita wrote: I can see the data that I want to export. How do I fix these indexes? Use the sqlite3 command-line shell to dump the database to SQL commands, then create a new database by reading it back in. While the data is in the SQL command file, you can take a look and make sure those records are present. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Auto index with wrong number of entries
Good idea. The result was : ok Of note, we found in our dump and import duplicate 4 entries that violated uniqueness of the primary key. (2 entries of 4 different primary keys, with only 1 other field having a different int.) We identified which one belongs and commented out the others. How did this happen for this workstation? I'm not sure if we will ever know, given that the offending records are create date 2 years ago, modified 1 year ago. Command prompt reports version 3.6.10 on start up. Adam On Wed, Jun 29, 2011 at 9:08 AM, Simon Slavin slav...@bigfraud.org wrote: On 29 Jun 2011, at 2:04pm, Adam DeVita wrote: On Tue, Jun 28, 2011 at 11:34 AM, Simon Slavin slav...@bigfraud.org wrote: Use the sqlite3 command-line shell to dump the database to SQL commands, then create a new database by reading it back in. While the data is in the SQL command file, you can take a look and make sure those records are present. Success! Great. You might want to run integrity_check on the result just for the very unlikely possibility that you have discovered a bug in SQLite and the resulting database has the same problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Auto index with wrong number of entries
Good day, Following a data collection reporting error from a workstation, I have found that pragma integrity_check reported that 2 of my tables have a few thousand entries missing in their auto indexes. wrong number of entries in index sqlite_auto_index_tablename_1 rowid 87973 missing from ... table above. I can see the data that I want to export. How do I fix these indexes? regards, Adam DeVita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a Logger: How to mimic rotation of logs?
Why not use INSERT OR REPLACE to your advantage?
If you set the maximum number of log entries you wanted to keep, then kept
track of your log insert statement, you could wrap by
int this_log_entry_id=1; //initialize.. actually could be initialized by
getting the log entry id of the min date in your log at the beginning of
your program.
if (this_log_entry_id max_log_entries){
this_log_entry_id =1;
}
else{
this_log_entry_id
}
call_insert_function (this_log_entry_id /*becomes the primary key that you
are inserting or replacing*/ , data_to_be_logged ,.
Adam
On Tue, May 10, 2011 at 9:08 AM, Simon Slavin slav...@bigfraud.org wrote:
On 10 May 2011, at 1:57pm, Lauri Nurmi wrote:
El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
On 10 May 2011, at 11:42am, Lynton Grice wrote:
BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
to say FIX the sqlite database size to say 5 MB?
There isn't one. SQLite would not know which records to delete.
If such a pragma existed, SQLite wouldn't need to delete anything
necessarily, it could behave like it behaves when trying to write to a
full disk.
Good idea. Or introduce a new result code for 'Database has reached
maximum allowed size'. Presumably it would be handled as fixing the number
of pages. Might be useful for small platforms like cellphones, where
running out of memory is a disaster.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?
Thanks for the responses. Our application is typically implemented on a standard laptop PC. It seems that the symptoms displayed are consistent with what this list describes would happen, so it looks like I can start thinking of how to write a defence. It does suddenly become very slow. I think the potential solutions we may implement are all in application code, so not really an SQLite problem. Thanks, Adam On Mon, Apr 18, 2011 at 10:07 AM, eLaReF ela...@btinternet.com wrote: Talking as a Windows user only rather than an SQL expert (I'm not even good enough to call myself a beginner!) Are we talking about a small netbook type with only say 8GB of memory and no hard drive. If a Windows m/c has a hard drive, surely virtual memory (drive-swapping) comes into play? It would, of course become v-e-r-y slow in comparison. eLaReF On 18/04/2011 14:46, Pavel Ivanov wrote: You won't be able to insert. The statement will fail. Pavel On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVitaadev...@verifeye.com wrote: Good day, What happens if you insert more than your RAM size into an in memory database? (I'm particularly interested in the Windows context). regards, Adam ___ 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 -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens if you insert more than your RAM size into an in memory database?
Good day, What happens if you insert more than your RAM size into an in memory database? (I'm particularly interested in the Windows context). regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] X most recent entries
select id from table order by id desc limit 5000 Adam On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham i...@omroth.com wrote: Hey guys. I have a table with an autoincrement primary ID, and as part of a select I would like to only take the 5000 largest/most recent ids. Is there a quick way of doing this without having to get the max first? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] X most recent entries
Are you wanting the last 5000 from player 1 and last 5000 from player 2? You can even limit and order the sub selects. Otherwise, I don't see the purpose of a union when OR would do. SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' order by rowid desc limit 5000 UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?' rowid desc limit 5000) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) I'm not sure of your context, but SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) order by rowid desc limit 5000 UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?' AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) rowid desc limit 5000) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) so you get the last 5000 qualifying records of each, rather than the latest 5000 of each and then filtering out the disqualifying ones On Mon, Mar 14, 2011 at 2:02 PM, Duquette, William H (318K) william.h.duque...@jpl.nasa.gov wrote: Assuming that higher rowids really are later rowids, wouldn't adding ORDER BY rowid DESC and LIMIT 5000 do the job? Will On 3/14/11 10:58 AM, Ian Hardingham i...@omroth.com wrote: Ah, sorry about this - my query is this one: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) And I only want to consider the last 5000 for any SELECTs from multiturnTable. Thanks, Ian On 14/03/2011 17:54, Adam DeVita wrote: select id from table order by id desc limit 5000 Adam On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham i...@omroth.com mailto:i...@omroth.com wrote: Hey guys. I have a table with an autoincrement primary ID, and as part of a select I would like to only take the 5000 largest/most recent ids. Is there a quick way of doing this without having to get the max first? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org mailto:sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory It's amazing what you can do with the right tools. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Propose minor incompatible API change
I think that a bunch of good points have been made, especially as to why you should hold your ground. (I don't have sympathy for poor code that doesn't follow the documentation, especially when there is a large, competent, and helpful community group that usually comes to people's aid in less than 1 day. Since when is my app's bug your core problem?) If the smartphone providers are willing to pay enough to fund this project to ensure it keeps going, then I guess the idea of He who pays the piper calls the tune. applies. Having lots of apps that work well are a way these companies compete with each other. I don't like the idea of a technical decision being determined in the spirit of a bribe, but this doesn't affect well written code. so I don't much care which side this falls on. The idea of a examples library for the docs is good, probably should have its own thread to discuss how to implement. best wishes, Adam On Tue, Jan 11, 2011 at 9:09 AM, Philip Graham Willoughby phil.willoug...@strawberrycat.com wrote: On 11 Jan 2011, at 13:36, Andy Gibbs wrote: On Tuesday, January 11, 2011 1:35 PM, Jean-Denis Muys wrote: Don't encumber SQLite with workarounds and special cases to cater to bugs in client software. Isn't an accurate synopsis of the problem this: that Sqlite has *already* implemented a workaround in 3.7.0, and that this workaround has actually caused a bigger problem, albeit only for incorrectly written code. It has (also) caused problems for code which was correct (if not pretty) given the API as documented in the last release before 3.6.23.1. Therefore, shouldn't this original workaround be fixed, in the way prescribed (since for all intents and purposes the new fix is better than the old fix)? Arguable - either 'fix' is undesirable if you have pre-3.6.23.1 code which is expecting to see SQLITE_MISUSE when it used to see it. It also makes a certain class of bugs more likely - if you get SQLITE_BUSY within an explicit transaction you should roll-back that transaction and begin it again; IMO you are more likely to notice and obey that requirement if you cannot just immediately call sqlite3_step again. That said, I like the current behaviour best of the three options, as it's less code to write in applications and it's consistent with itself. The 3.6.23.1 behaviour is also consistent, and there is a case for going back to that if the current behaviour is (with hindsight) a more-incompatible change than should have been introduced mid-release. I like the proposed new fix least, as it still requires sqlite3_reset on the normal path and creates an inconsistency between that and the abnormal path. The issue of whether or not sqlite should provide workarounds (in future) to cater for bugs in client software is another question, isn't it? Yes; I would expect future workaround-requesters to appeal to the precedent set this time. The precedent that SQLite can be improved at any time, and that's what happened in 3.6.23.1 so it won't be reverted is one option. This is probably what developers expect in the open-source world. The precedent that flow-affecting changes will not be put in mid-release and this 3.6.23.1 change was therefore an error that will be reverted is another. This is probably what developers expect in the commercial world. The third is the precedent that developers don't need to worry about reading the documentation and handling errors correctly as SQLite will usually be changed in a future release to make their code work. And if this change breaks someone else then SQLite will be changed again. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required
Just to add my $0.02 We use http://www.safenet-inc.com/ HASP HL Encryption. (We use HASP keys for end user products so it was 'free' to my internal product tracking system db.) This key allows the exe to get encrypted and optionally a data file as well. The encryption of the program provides us with some security against a password being saved within the exe in clear form. When it comes to data encryption though, the performance penalty we suffer is 2x to 4x. Also, HASP HL data encryption + Sqlite + Windows 7, 64 bit editions don't work reliably. The HASP envelope does prevent an executable from running with a debugger open. It may be that newer versions of compiler or key will work, but I can't say that they will (nor does safenet's technical support actually provide answers). Bitter experience so far says Don't use HASP for data encryption. Adam On Sun, Jan 9, 2011 at 5:40 PM, Simon Slavin slav...@bigfraud.org wrote: On 9 Jan 2011, at 5:29pm, Roger Binns wrote: I think you misunderstand how the SQLite encryption extension works. The on disk storage format for SQLite is a series of fixed sized pages. The extension transparently encrypts each page on writing to disk and decrypts on reading. To use it you open/attach a database and then provide the password either via a C API or a pragma. You just make regular SQLite API calls and everything just works. http://www.hwaci.com/sw/sqlite/see.html The various other ones pointed out do something similar but since you go via their API layers they intersperse code to do encryption. I found it very hard to work out what they did for encryption since things like the algorithm used, IV (the usual weakness for home grown implementations) etc do matter. They also make other choices: As far as I can work out, the two solutions he pointed to encrypt at the field level. So if you understand the file structure of an SQLite database you can, for example, work out which records have the same values in either within a table or as across tables. It also gives you a handy-dandy plain/crypt pair since you will know that certain fields definitely start with 'CREATE TABLE ' and such things. On the other hand, these solutions are cheaper than the hwaci one. As with most encryption it depends how much effort you think the enemy will devote to attacking your technique. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bit sqlite 3
At first I was like awe, I don't wanna do my homework. I'd have to recompile all my little utilities and distribute them rather than just distribute a new DLL and it would be nice to keep our local program maintainers from helping instead of keeping to the officially released code. Now, after showing that my 64 bit problems go away when I include the amalgamation source in the project, the whining just stops. thanks :) Adam On Fri, Dec 17, 2010 at 5:20 PM, Simon Slavin slav...@bigfraud.org wrote: On 17 Dec 2010, at 4:30pm, Adam DeVita wrote: Will a 64 bit Windows DLL eventually be posted for download? SQLite is distributed as source. Generally speaking you compile the amagamation form directly into your application rather than making a separate library of it. Whatever form you want it in, feel free to compile it yourself. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bit sqlite 3
Will a 64 bit Windows DLL eventually be posted for download? On Fri, Dec 17, 2010 at 9:45 AM, Eric Smith eas@gmail.com wrote: On Fri, Dec 17, 2010 at 4:36 AM, giuseppe500 giuseppe...@yahoo.it wrote: There is a version of SQLite 3 for 64-bit systems? or, you can simply compile the source of sqlite3 at 64-bit with c++ 2008? thanks. FWIW I compiled sqlite 3.6.23.1 along with its tcl hooks and have been happily using it in a (single-threaded, multi-process) 64-bit application in tcl and C on both FreeBSD6 and linux 2.6.18-164, RHEL5.4 for 6 or 8 months with no issues whatsoever. The application parses a superset of csv (with arbitrary optional field separators, arbitrary optional quotation characters, arbitrary optional padding characters, arbitrary record separators, field data type checking etc) and exposes relational queries on the data set. The app screams along at 16 records per second on the parse side, and I'm still pretty sure it's my parse code that's the bottleneck and not sqlite. Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Why would you want to do this in plane sql, as opposed to using the API to
go through the list and derive it?
On Fri, Oct 1, 2010 at 3:37 PM, Andy Chambers achambers.h...@gmail.comwrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
Cheers,
Andy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a table that show all the available function from sqlite3?
Are you looking for http://www.sqlite.org/c3ref/funclist.html ? On Fri, Aug 13, 2010 at 12:37 PM, Peng Yu pengyu...@gmail.com wrote: Hi, http://www.sqlite.org/docs.html I don't see a table that shows all the available functions in sqlite3. Would you please let me know if there is such a table? -- Regards, Peng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
A variant on Simon's plan. Are the 10,000 rows static, slowly changing, or frequently changing? Does it make sense to pre-calculate some counts at the time data is loaded? Is this memory constrained so much that you can't afford 1 or 2 MB to let you look up based on ints? (I'm assuming that one letter is all you are after, either 'starts with' or 'contains' and not in order combinations.) Adam On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin slav...@bigfraud.org wrote: On 5 Aug 2010, at 10:03pm, Sam Roberts wrote: But do you think the section would make the counting faster? I think I'd have to get the row counts like this, which would still do the slow full table scan: select section, count(*) from my_table where name like '%e%' group by section; But 'group by section' can profit from the index on the section column so it should be faster. As with all these things, the suggestion is to try it and see. You should try six or seven different solutions including shuffling columns and indexes before you settle on the one that will be in your final code. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
From the point of view of a C question, make your array of characters large enough to hold the characters you want (and terminating null) before copying them in. From the point of view of an SQL: if you want to change the comparison constant in a where clause, look up bind parameters. read through http://www.sqlite.org/c3ref/bind_blob.html There are lots of examples on the list of binding. regards, Adam On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts rollerueckwae...@gmx.netwrote: Hello, I try to get an sql query string from 2 const chars. const char *language; language = '6'; const char *sql2 = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = ; const char *sql = strcpy(sql2,language); // or const char *sql = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = + language; //or const char *sql = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = language; nothing works :) How can i do this ? Hoping for help :) tobi -- View this message in context: http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concat 2 const chars ?
and don't use strcpy here is why https://buildsecurityin.us-cert.gov/bsi-rules/home/g1/848-BSI.html On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts rollerueckwae...@gmx.netwrote: Hello, I try to get an sql query string from 2 const chars. const char *language; language = '6'; const char *sql2 = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = ; const char *sql = strcpy(sql2,language); // or const char *sql = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = + language; //or const char *sql = SELECT key,name,text FROM uebersetzungen WHERE sprach_id = language; nothing works :) How can i do this ? Hoping for help :) tobi -- View this message in context: http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mail loop?
Is it possible for the list admin to seed a message to the list that, if auto-replied, would automatically remove one from the list? This thread, although off the sqlite topic, is still interesting. regards, Adam On Mon, Jun 14, 2010 at 10:06 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: And if Peter isn't the problem you can do a binary search. With 2500 members just keep splitting the list in half and set a test email to each half. Then do it again for the half that shows the loop. 2500-1250-625-318-159-80-40-20-10-5-3-2-1 26 emails will ID the culprit (two at each level -- one should show the loop and one should not). Just put a different subject in each email so you know what you're looking at. Or...2500 emails to each person on the list..one of which should show up on this list. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp Sent: Mon 6/14/2010 8:30 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Mail loop? On Mon, Jun 14, 2010 at 9:09 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: The problem is that somebody has a .forward or such which loops back to the list. It's probably in the alias expansion of sqlite-users which expands to a listfor which a member then expands back to sqlite-users. The mail logs may show whether it happens locally or if its a remote user. If it's a remote user you should see a log entry complaining about too many loops and it may ID the user name involved. I'd check sqlite-users for another sqlite-users@ entry... There is no sqlite-users entry in the (2500+) membership list for sqlite-users. Nor am I able to find any clues in the header of the bounce reply. Another ideas? Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp Sent: Mon 6/14/2010 7:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Mail loop? It is not just you. But I have no idea what the problem is or how to fix it. On Mon, Jun 14, 2010 at 8:43 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: I've been seing this the last 2-3 weeks whenever I email the list...is it just me??? This is the mail system at host sqlite.org. I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below. For further assistance, please send mail to postmaster. If you do so, please include this problem report. You can delete your own text from the attached returned message. The mail system sqlite-users@sqlite.org: mail forwarding loop for sqlite-users@sqlite.org Michael D. Black Senior Scientist Northrop Grumman Mission Systems ___ 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 -- - 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database development - correct way?
I wouldn't advise using an SQL keyword as a table name: Order I presume that your order collection table example is shorter than the real one for the sake of the example? One often sees a date or time of some sort associated with an order so that one can create reports based on dates. (How many sales did we make this month?) regards, Adam On Wed, Jun 9, 2010 at 9:09 AM, Oliver Peters oliver@web.de wrote: Hello, despite it's just a question about construction I hope somebody is willing to push me into the right direction if necessary. my simplified case -- I've the 3 tables customer, article and order my thoughts about the table customer: the customernumber can be from 3 different sources with possible overlappings (i.e. I can get 3 from source A and 3 from source B) so I adopt the field customerorigin to make a difference For simplicity I created a field id that is taking the part of the Primary Key and just declared UNIQUE(customernumber,customerorigin) the SQL-Code CREATE TABLE customer( idINTEGER PRIMARY KEY AUTOINCREMENT, customernumberINTEGER, customeroriginINTEGER, name TEXT, UNIQUE(customernumber,customerorigin) ); CREATE TABLE article( idINTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE order( idINTEGER PRIMARY KEY AUTOINCREMENT, id_customer INTEGER, id_articleINTEGER, UNIQUE(id_customer,id_article), FOREIGN KEY(id_customer) REFERENCES customer(id), FOREIGN KEY(id_article) REFERENCES article(id) ); simple question --- Is this a correct way or do I make a mistake? greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy data from one db to another
start by doing an open db1 (as main) then attach path to db2 as 'db2' insert into main.table_one_name select * from db2.table_one_name ; This selects all records from db2 and puts them into db1 in one statement. Adam On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel fran...@circlesfx.com wrote: Hi all, What's the best way to copy data from one db to another? Given 2 databases with identical schemas, one full of data and the other empty, the brute force way would be to perform selects on the source db, then for each row, perform an insert into the destination db. Is there a more efficient way? Thanks in advance! Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy data from one db to another
The db that you open your initial connection to is called main by default. I haven't had the occasion to use a temp or memory db so I can't comment. The attach statement works as normal SQL. attach 'path to your db' as 'some_alias_name' like attach 'c:\temp dir\db2.db' as 'db2' Suppose both files have a table named 'some_table'. select * from db2.some_table ; /*refers to the attached db*/ select * from main.some_table ; /*refers to the db you first made a connection with.*/ I find the command line tool wonderful for testing out syntax. Happy Computing Adam On Tue, Jun 8, 2010 at 4:09 PM, Scott Frankel fran...@circlesfx.com wrote: On Jun 8, 2010, at 12:07 PM, Adam DeVita wrote: start by doing an open db1 (as main) then attach path to db2 as 'db2' insert into main.table_one_name select * from db2.table_one_name ; This selects all records from db2 and puts them into db1 in one statement. I've been reading about the ATTACH DATABASE cmd, but was confused by the documentation's warnings about main and temp dbs, namings, and transactions using :memory:. I'll take a closer look. Thanks! Scott Adam On Tue, Jun 8, 2010 at 3:02 PM, Scott Frankel fran...@circlesfx.com wrote: Hi all, What's the best way to copy data from one db to another? Given 2 databases with identical schemas, one full of data and the other empty, the brute force way would be to perform selects on the source db, then for each row, perform an insert into the destination db. Is there a more efficient way? Thanks in advance! Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update: set multiple values
but... ...but I LOVE my hammer! How dare every problem not be a nail? ;) Good point. Likely all the updates can fit nicely into a transaction. On Mon, May 10, 2010 at 5:11 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 May 2010, at 9:25pm, Adam DeVita wrote: Simon, can you expand your syntax, or are you just saying, get x,y,z store them in a set of variables, then run update with appropriate bindings? Just that. You have a programming language with variables, so use it. That's what your programming language is for. You might be able to get extremely clever and work out some contorted SQLite syntax which will do the whole thing in one SQL command, but why bother ? It'll be hell to work out what's wrong if you get an error message. And it'll be difficult to document because you have to explain your perverse syntax. Better to use two extremely simple SQL commands and say We get three values here ... then we use them in this UPDATE.. Faster and simpler to write, debug and document. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite connection?
Yes. One can also attach 'somedatabase path' as anothername ; and you can run a query accessing both at the same time. regards, Adam On Tue, May 11, 2010 at 1:45 PM, john cummings jndbusin...@gmail.comwrote: hi all, i'm new to this forum and sqlite. is it possible to have an executable (i.e. .exe) with connections to 2 sqlite databases? i've read doc and it doesn't speak to this one way or the other. thanks, john ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update: set multiple values
Simon, can you expand your syntax, or are you just saying, get x,y,z store them in a set of variables, then run update with appropriate bindings? Hopefully this related question isn't called hijacking a thread. I feel this belongs together under set multiple values using the update query. I'm toying with something similar, and don't want to get the run multiple updates so that the C code can stay simple. create table x (sn int primary key , comboid, property1 int , property2 int , property3 int ...) create table dictionary (comboid int primary key, property1 int , property2 int, property3 int (original insert into x was sn, comboid=-1 /*a flag to indicate this needs an update*/ with property 1, 2, and 3 being correct. The original insert was honking, very slow thing that I've given up hope of salvaging since the below beats it 10 to 100:1 in speed) want to update each sn with the comboid from the dictionary where property1, 2, and 3 match. currently I'm leaning on insert or replace into x (sn, comboid, property1, property2, property3) values select x.sn, d.comboid, x.property1, x.property2, x.property3 from x inner join dictionary d on x.property1 =d.property1 and x.property=d.property2 and x.property3 =d.roperty3 where x.comboid=-1; This somehow feels like cheating, though it seems to produce an acceptable result quickly enough (on my relatively small db) Adam On Sun, May 9, 2010 at 5:23 PM, Simon Slavin slav...@bigfraud.org wrote: On 9 May 2010, at 8:41pm, Simon Hax wrote: I think in sqlite the following is not possible: update T set (a,b,c) = ( select x,y,z from ...) Does anyone know how to do in an easy way ? Do your SELECT first, then set the multiple variables to the values retrieved from that: UPDATE T SET a=x,b=y,c=z WHERE ... Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Returning column to default
Is there a primary key on the table? Is it possible to use insert or replace instead of update, and then not reference the column you want to set as a default? On Thu, May 6, 2010 at 10:41 AM, Andy Gibbs andyg1...@hotmail.co.uk wrote: You could write a trigger that sets default value if NULL is inserted or set via UPDATE. That's a great idea - thanks! It won't work in all the places since in some places 'NULL' is a valid value, but I'm sure I can think of a work-around. Thank you!! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Is it possible there is a null, tab, newline or other invisible character?
Try
select timeStamp, '' || resourceType || 'xx' From MyTable where
resourceType like 'PSM' LIMIT 10;
On Fri, Apr 30, 2010 at 9:53 AM, ecforu ecforus...@gmail.com wrote:
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column output truncating
Could you include a bit more information about your post? (Version number, operating system etc.) I'm unsure if you have compiled something or are using the command line tool. There are lots of very knowledgeable and helpful people on the list. On Thu, Apr 22, 2010 at 6:31 PM, Derek Martin demar...@akamai.com wrote: Hi, I have a query that produces about 10 columns, some of which are very wide. When I run sqlite3 with -column -header, it truncates all fields to 10 characters. This makes the query absolutely useless. How can I turn off this truncation? I already tried explicitly setting the width to 0, that did not help. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Dimension
good ideas. The spread sheet trick hadn't occurred to me. I think I'll go that route since it keeps things user readable thank you for your thoughts, all. regards, Adam On Thu, Apr 22, 2010 at 2:51 AM, Oliver Peters oliver@web.de wrote: Adam DeVita adev...@... writes: [...] If I have to generate the date dimension on my own, I'm hoping to use something like create table date_dimension ( [Dateid] integer primary key, [Real_Year] int , [Month_name] text, [Day] int , [QuarterNumber] int, [DayofWeek_name] text, [dayofYear] int, [epoch_day] int, [julian_day] int ); [...] Why don't you simply use a spreadsheet program like OpenOfice Calc or Excel to prepare the table data for your fixed timespan (2010 - 2030) and import the whole thing? Would be a work of a few minutes ;-) greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date Dimension
Good day, Given the context I'm in, sqlite is going to be used for our data warehousing. (We generate about 2MB of raw data in a month, so we don't think we need a heavy DB engine.) Since most warehouses have one, which are very similar from application to application, I'm wondering if there is somewhere to download a pre-defined Date Dimension? I could write my own script, but re-invent and debug the wheel? regards, Adam -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Dimension
Yes. A Date dimension is a table that has all possible dates for your data, thus making reporting on properties of the date easy. Something like this CREATE TABLE Date_dimension ( DateID int NOT NULL , /*an int key to match up to date fields in fact storage tables*/ [Date] datetime NOT NULL, [Year] int NOT NULL, [Month] int NOT NULL, [Day] int NOT NULL, [QuarterNumber] int NOT NULL, [DayofWeek_name] text, [Month_name] text, ) On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov paiva...@gmail.com wrote: What is a Date Dimension? Probably OP meant this: http://en.wikipedia.org/wiki/Dimension_(data_warehouse)http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29 . But I don't have any answer to the question asked. Pavel On Wed, Apr 21, 2010 at 3:21 PM, P Kishor punk.k...@gmail.com wrote: On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita adev...@verifeye.com wrote: Good day, Given the context I'm in, sqlite is going to be used for our data warehousing. (We generate about 2MB of raw data in a month, so we don't think we need a heavy DB engine.) Since most warehouses have one, which are very similar from application to application, I'm wondering if there is somewhere to download a pre-defined Date Dimension? What is a Date Dimension? For SQLite's date time functions, see http://www.sqlite.org/lang_datefunc.html I could write my own script, but re-invent and debug the wheel? regards, Adam -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Dimension
Good day,
I've been looking at doing that, but am having problems converting
backwards.
The idea of a date dimension is to have one row for every possible date in
the time span of interest
For example, I'm tracking product histories, so I know that there will be no
activity before January 1, 1990 (a date well before manufacture of the first
product)
and I won't care about what happens well into the future retire (say around
year 2030 )
This yeilds (20+20)*365.25 =14,610 maximum potential rows. If all the
possibilities are pre-calculated, then if a user wants to express a roll up
of the facts by any grouping
select count(somthing_interesting) , dd.day_of_week
from fact_table ft
inner join date_dimension dd on ft.dateid = dd.dateid
group by dd,day_of_week
Then
a) the user doesn't have to worry about converting dates by some group
because it is all done for them.
b) we store the date of the event in our fact_table as an int
c) we don't have to run much of calculation of dates, just a join.
(I've been reading The Data Warehoust Toolkit, Second Edition by Kimball
and Ross)
If I have to generate the date dimension on my own, I'm hoping to use
something like
create table date_dimension (
[Dateid] integer primary key,
[Real_Year] int ,
[Month_name] text,
[Day] int ,
[QuarterNumber] int,
[DayofWeek_name] text,
[dayofYear] int,
[epoch_day] int,
[julian_day] int
);
/*populate some an auto increment so that all days are covered even if I
have to write a loop doing*/
insert into date_dimension( epoch_day) select count(epoch_day) from
date_dimension;
/*then */
update date_dimension set julian_day = julianday('now') -
julianday('1990-01-01') + epoch_day;
/* then uh some query that updates the table containing the julian
date of every day from Jan 1, 1990 through 2030, and fill in the month
name, year, quarter in nice user friendly strings.*/
Is this approach better than generating a list of date strings for all
possible dates, throwing away the Feb 29s from non leap years, and then
parsing the string to get ye year, month, day, day of year, age from epoch,
month name, etc?
regards,
Adam
On Wed, Apr 21, 2010 at 3:59 PM, P Kishor punk.k...@gmail.com wrote:
On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita adev...@verifeye.com wrote:
Yes.
A Date dimension is a table that has all possible dates for your
data, thus making reporting on properties of the date easy. Something
like this
CREATE TABLE Date_dimension (
DateID int NOT NULL , /*an int key to match up to date fields in fact
storage tables*/
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Month] int NOT NULL,
[Day] int NOT NULL,
[QuarterNumber] int NOT NULL,
[DayofWeek_name] text,
[Month_name] text,
)
methinks you can calculate all of the above storing your dates as
strings in a single column, and using the date time functions on that
column. Check out the functions in the link I sent you.
On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov paiva...@gmail.com
wrote:
What is a Date Dimension?
Probably OP meant this:
http://en.wikipedia.org/wiki/Dimension_(data_warehouse)http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29
http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29
.
But I don't have any answer to the question asked.
Pavel
On Wed, Apr 21, 2010 at 3:21 PM, P Kishor punk.k...@gmail.com wrote:
On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita adev...@verifeye.com
wrote:
Good day,
Given the context I'm in, sqlite is going to be used for our data
warehousing. (We generate about 2MB of raw data in a month, so we
don't
think we need a heavy DB engine.)
Since most warehouses have one, which are very similar from
application
to
application, I'm wondering if there is somewhere to download a
pre-defined
Date Dimension?
What is a Date Dimension? For SQLite's date time functions, see
http://www.sqlite.org/lang_datefunc.html
I could write my own script, but re-invent and debug the
wheel?
regards,
Adam
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation
http://www.osgeo.org
Science Commons Fellow,
http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is
science
===
___
sqlite-users
Re: [sqlite] SQLite parsing of a .sql file
Is this a 1 off import? If so, perhaps the command line tool can .read it. On Fri, Apr 9, 2010 at 2:13 PM, Shaun Seckman (Firaxis) shaun.seck...@firaxis.com wrote: Greetings, I've got a .SQL file that contains multiple SQL insert statements for various tables as well as comments embedded throughout. When I attempted to pass the file into sqlite3_exec, I found that only the first SQL command was getting executed. What is the best way to ensure that all commands are executed? Parsing the file line-by-line is inaccurate as multiple statements may be on the same line and looking for the next ';' character has parsing problems as well. The documents state that sqlite3_complete() only returns a 1 if the statement is complete. Were this method to return the index into the character array to denote WHERE the SQL statement is complete, I could use that to parse multiple statements. Does anyone have any suggestions? -Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance problem with count(*) calculation
How does $ time sqlite3 test32k.db select count(1) from role_exist perform? On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov pechni...@mobigroup.ruwrote: Hello! $ time sqlite3 test32k.db select count(*) from role_exist 1250 real0m58.908s user0m0.056s sys 0m0.864s $ sqlite3 test32k.db SQLite version 3.6.23 sqlite .schema role_exist CREATE TABLE role_exist ( id INTEGER PRIMARY KEY, uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE ); So 58s for count of all records! The count(*) for all records may use the counter from primary key b-tree, is't it? == HARDWARE: $ grep CPU /proc/cpuinfo model name : Intel(R) Core(TM)2 Quad CPUQ6700 @ 2.66GHz model name : Intel(R) Core(TM)2 Quad CPUQ6700 @ 2.66GHz model name : Intel(R) Core(TM)2 Quad CPUQ6700 @ 2.66GHz model name : Intel(R) Core(TM)2 Quad CPUQ6700 @ 2.66GHz $ free total used free sharedbuffers cached Mem: 83108927552880 758012 0 294966667708 -/+ buffers/cache: 8556767455216 Swap: 3903784 3012403602544 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about binding
Good day,
For the sake of fun, I have to share this - especially with tall this talk
of binding all the parameters.
void poem(CString pth)
{
sqlite3_stmt *ppStmt; //statement pointer
sqlite3 *db; //database
const char *pzTail;
char *pzerr;
if( sqlite3_open(pth, db) ){
printf(Can't open database! );
sqlite3_close(db);
return ;
}
CString csql;
csql.Format(Create table if not exists poem (verseno integer primary
key, rings int, location text)); //sets the string.
int status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16
encoded */ csql.GetLength(), /* Maximum length of zSql in bytes. */
ppStmt, /* OUT: Statement handle */
pzTail /* OUT: Pointer to unused portion of zSql which
I'm not going to use because I want to bind things different ways.*/ );
if (status != SQLITE_OK){
printf(something is wrong, shame, shame, shame. );
sqlite3_close(db);
return ;
}
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);
int rings[5] ={3 , 7 ,9,1 ,1};
CString verse1 =_T( for elvin kings, under the sky);
CString verse2 =_T( for dwarf lords, in their halls of stone);
CString verse3 =_T( for mortal men, doomed to die);
CString verse4 =_T( for the dark lord, on his dark throne );
csql.Format( insert into poem (rings, location) values (?, ?) );
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/ csql.GetLength(), /* Maximum length of zSql in bytes.
*/ppStmt, /* OUT: Statement handle */
pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf(something is wrong, like %d,status);
sqlite3_close(db);
return ;
}
int ring_verse =0;
sqlite3_bind_int (ppStmt, 1, rings[ring_verse]);
sqlite3_bind_text(ppStmt, 2, verse1, verse1.GetLength(), SQLITE_STATIC);
sqlite3_step(ppStmt);
ring_verse++;
csql.Format( insert into poem (rings, location) values (?002, ?001)
);
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/csql.GetLength(), /* Maximum length of zSql in bytes. */
ppStmt, /* OUT: Statement handle */
pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf(something is wrong %d,status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, 1, verse2, verse2.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, 2, rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;
csql.Format( insert into poem (rings, location) values ($ringy, :versy)
);
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(), /* Maximum length of zSql in bytes. */
ppStmt, /* OUT: Statement handle */
pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf(something is wrong %d,status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
:versy), verse3, verse3.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
$ringy), rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;
CString csrepeated =_T(\nin the land of Mordor, where the shadows
lie.);
csql.Format( insert into poem (rings, location) values (@ringy, :versy
|| :repeats) );
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(), /* Maximum length of zSql in bytes. */
ppStmt, /* OUT: Statement handle */
pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf(something is wrong %d,status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
:repeats), csrepeated, csrepeated.GetLength(), SQLITE_STATIC);
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
:versy), verse4, verse4.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
@ringy), rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;
//and finally
verse3.Format( to bring them all and in the darkness BIND them );
csql.Format( insert into poem (rings, location) values (@ringy, ' ring
to rule them all '|| @ringy ||' ring to find them, '|| @ringy ||:versy ||
:repeats) );
status = sqlite3_prepare_v2(db,csql,csql.GetLength(), ppStmt,
pzTail );
if (status != SQLITE_OK){
printf(something is wrong %d,status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
:repeats), csrepeated, csrepeated.GetLength(), SQLITE_STATIC);
sqlite3_bind_text(ppStmt,
Re: [sqlite] The character ' not liked by sqlite?
Good day, If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob for the function int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); This will allow you to bind any character into an SQL statement. There are other benefits to using this technique. regards, Adam On Tue, Mar 2, 2010 at 2:37 PM, Kavita Raghunathan kavita.raghunat...@skyfiber.com wrote: Simon and Gabriel, I'm using the C API, I'm inserting strings. One of the strings happens to have an ' in it. I have to write extra code to parse the character and escape it, I'll do that if I have to. I have not tried the command line tool. I'll try it and get back to you. Kavita On 3/2/10 12:56 PM, Simon Slavin slav...@bigfraud.org wrote: On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: I notice that when I try to insert the character ³¹² as part of a string into the sqlite database, my updates don¹t work. Any ideas why? The same string without the ³¹² character works. I have not debugged to see where exactly in sqlite it fails. I¹m inserting a text like this: ³Rootuser¹s desktop² does not work. ³Rootuser desktop² works, the update to database suceeds and I¹m able to view it using select. What API or toolkit are you using ? Have you tried executing the same command with the command-line tool ? 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie problem using special column name
Create the table using single quotes around the strange name. sqlite create table x (boomer int, 'squid-nick' text); sqlite insert into x values (1,'asdlh'); sqlite select * from x; 1|asdlh sqlite select squid-nick from x; SQL error: no such column: nick sqlite select 'squid-nick' from x; squid-nick /*oops, I selected the string not a column name. */ sqlite select x.'squid-nick' from x; /*reference the table name before the column*/ asdlh As an aside: If you realize that the database name for a column doesn't have to be the same as the user friendly name in your user interface, you don't have to use keywords or special characters as column names. On Mon, Jan 25, 2010 at 9:18 AM, Patrick Ben Koetter p...@state-of-mind.dewrote: Can I add a column name containing a dash - and if yes, how would I do that? I am asking because I fail to add a column name that contains a dash - and I don't know if I cause the problem (easy solution) or if its something else causing this to fail. Here's what I try: sqlite create table test(column-1 varchar(255)); SQL error: near -: syntax error So far I have had a look at the SQLite documentation, but couldn't find anything that would tell me about 'reserved' characters or how I would escape a dash. Thanks, p...@rick -- state of mind Digitale Kommunikation http://www.state-of-mind.de Franziskanerstraße 15 Telefon +49 89 3090 4664 81669 München Telefax +49 89 3090 4666 Amtsgericht MünchenPartnerschaftsregister PR 563 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limitation on Column count
Good day, In general I try to work within the limits of any database engine that I am using. Often, the limits are there for good reasons (such as speed problems). I would suggest seeing if there is a way to normalize the big tables such that infrequently used columns are split into tables that aren't joined in often. (The principal I'm using is borrowed from hardware architecture Make the common case fast, and ensure the uncommon case is correct.) It may or may not be sensible given your data, but there may be an opportunity to reduce the number of columns by making an encoded column to aggregate, such as lots of mutually exclusive binary flag fields. regards, Adam On Mon, Jan 11, 2010 at 3:46 PM, Stefan_E se_m...@hotmail.com wrote: Hello all, I'm considering using SQLite for a new application. The schema will contain a bunch of small tables with few columns (~10) plus one large table with many columns and 1000...1 rows. 'Many columns' typically fits into the default 2000 column limit, but can exceed it at times (that is, on some of the foreseen databases). It will never exceed the theoretical / compile time selectable limit of 32k columns. Queries on this big table will be rather straight-forward: either on the table alone (SELECT * FROM table_large) or one join on one field to one of the smaller tables. The http://www.sqlite.org/limits.html Limits page warns: There are places in the SQLite code generator that use algorithms that are O(N²) where N is the number of columns. which is kind of discouraging to increase max. column count at compile time, but is not very specific about when this happens... I now have two design options: - increase max. column count at compile time (possibly setting SQLITE_LIMIT_COLUMN on databases where I don't need more than (say) 1000 columns) and accept the quoted performance degradation. - alternatively, in the client handle cases with more than 2000 columns, splitting the storage up into two (or more) tables Any advise, experience - or more specifics on the O(N²) remark are highly welcome! Thanks for your help - Stefan -- View this message in context: http://old.nabble.com/Limitation-on-Column-count-tp27117364p27117364.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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limitation on Column count
One may be able to make measurement type a column, thus eliminating the need for a column for each type. Some speed may be recoverable with indexing. regards, Adam On Tue, Jan 12, 2010 at 1:21 PM, Pavel Ivanov paiva...@gmail.com wrote: so normalization would lead to a doubling of the storage space (add a measurement_id to each measurement). My strong belief is that when you try this normalization you'll see that such doubling of storage is a good enough trade-off for the speed you'll achieve. I don't think that speed of queries on the table with 100+ columns would be any useful (of course unless you *always* select all columns and *never* try to select only a few ones). Second, the most common use case is to view the table in the currently foreseen format - so, I'd pay both in space and time... Most probably you view your table from your application which can denormalize the table very quickly. Even if you view your table from sqlite3 command line tool you still can write denormalizer even using bash scripts and I believe it will still work fast enough and it will be better than creating such huge table. Pavel On Tue, Jan 12, 2010 at 1:09 PM, Stefan_E se_m...@hotmail.com wrote: Hi Adam, thanks for your suggestion. Unfortunately, it doesn't help in my case. Essentially, we are talking about a time series (rows) of n different measurements (columns) - so normalization would lead to a doubling of the storage space (add a measurement_id to each measurement). Second, the most common use case is to view the table in the currently foreseen format - so, I'd pay both in space and time... Anyway, thanks for the suggestion! Regards, Stefan Adam DeVita wrote: Good day, In general I try to work within the limits of any database engine that I am using. Often, the limits are there for good reasons (such as speed problems). I would suggest seeing if there is a way to normalize the big tables such that infrequently used columns are split into tables that aren't joined in often. (The principal I'm using is borrowed from hardware architecture Make the common case fast, and ensure the uncommon case is correct.) It may or may not be sensible given your data, but there may be an opportunity to reduce the number of columns by making an encoded column to aggregate, such as lots of mutually exclusive binary flag fields. regards, Adam On Mon, Jan 11, 2010 at 3:46 PM, Stefan_E se_m...@hotmail.com wrote: Hello all, I'm considering using SQLite for a new application. The schema will contain a bunch of small tables with few columns (~10) plus one large table with many columns and 1000...1 rows. 'Many columns' typically fits into the default 2000 column limit, but can exceed it at times (that is, on some of the foreseen databases). It will never exceed the theoretical / compile time selectable limit of 32k columns. Queries on this big table will be rather straight-forward: either on the table alone (SELECT * FROM table_large) or one join on one field to one of the smaller tables. The http://www.sqlite.org/limits.html Limits page warns: There are places in the SQLite code generator that use algorithms that are O(N²) where N is the number of columns. which is kind of discouraging to increase max. column count at compile time, but is not very specific about when this happens... I now have two design options: - increase max. column count at compile time (possibly setting SQLITE_LIMIT_COLUMN on databases where I don't need more than (say) 1000 columns) and accept the quoted performance degradation. - alternatively, in the client handle cases with more than 2000 columns, splitting the storage up into two (or more) tables Any advise, experience - or more specifics on the O(N²) remark are highly welcome! Thanks for your help - Stefan -- View this message in context: http://old.nabble.com/Limitation-on-Column-count-tp27117364p27117364.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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/Limitation-on-Column-count-tp27117364p27131144.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
Re: [sqlite] Confusing FAQ(26) wording
http://www.sqlite.org/nulls.html seems to clarify things for me on this topic. Adam On Wed, Dec 30, 2009 at 9:13 AM, Tobias Hoffmann lsqlite-l...@thax.hardliners.org wrote: Hi, I had a hard time to understand the FAQ entry on UNIQUE constraint -- in the end I had to try out sqlite's behavior myself because the FAQ -- so maybe the wording can be improved and/or an example added. Here a some comments: *(26) The SQL standard requires that a UNIQUE constraint be enforced even of one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?* So this seems to imply that two NULL values will not violate the UNIQUEness of two rows in SQlite. [Btw. shouldn't it be ... enforced even IF one or ...?] Perhaps you are referring to the following statement from SQL92: A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. IMHO: as they did't just write .. have the same values in the unique columns, the database should only compare those columns that are non-null when enforcing uniqueness. (just as above -- and as SQlite does it). That statement is ambiguous, having at least two possible interpretations: Now the confusion begins. 1. A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns. Shall this mean something like (parenthesis to show parsing precendence) (no two rows in the table have the same values) and ([they] have non-null values) ...[after some time I realized: this does not make much sense. But how else was it meant?] or no two rows in a table have (the same values and have non-null values) in the unique columns.[maybe removing the second have would help] 2. A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are not null. So you compare only those columns that are not NULL, right? Where is the difference to (1)? [this made understanding (1) even more difficult to me]. And why does the following paragraph state that that SQLite does not follow this interpretation, although it seems that this is the unexpected behavior in the original question? SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does. After all I tried with SQLite and found out that you can have two rows with NULL in the same (unique-constraint) column. But I'm not sure if this is really the point of the question, as I still haven't understood (2) [and don't have MSSQL to test] - or whether its [wild guess:] about certain behavior with multi-column indices. If this is clear to everybody except me, I would appreciate a hint... otherwise please consider clarifying this FAQ. Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Archive Search Engine
But there is a search engine on: http://www.mail-archive.com/sqlite-users%40sqlite.org/info.html It is right at the top. Adam On Tue, Dec 29, 2009 at 12:02 PM, Bill Marvin bill_mar...@hotmail.comwrote: It would be very helpful if there was a search engine for the sqlite-user mailing list archive. My question might have already been answered, but currently using the archive I have to manually look through the threads month by month. It is like finding a needle in a haystack! Bill _ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Location of Sqlite Files
1) save the db wherever is appropriate for your application. (network drives are generally considered to be a bad idea) 2) An sqllite db is a file. There are lots of ways to delete files Adam On Fri, Nov 27, 2009 at 2:54 PM, mr_orange rlvl...@gmail.com wrote: Hey, I am kind of new to SQLite. I was wondering if anyone could help me with a couple things: 1) Where are the SQLite databases stored? I was told in the C:\WINDOWS\temp folder, but I can't seem to find any SQLite-related files there. 2) How do you delete a SQLite database? Any help is much appreciated, thank you. -- View this message in context: http://old.nabble.com/Location-of-Sqlite-Files-tp26545375p26545375.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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Windows 7?
Good day, Will a new DLL be required for Windows 7 ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideal way to check whether a table has a specific column
Another way SELECT name, sql FROM sqlite_master WHERE type='table' and name ='yourtable name' the field sql will give you the full table structure as a string. Parse for your desired table name. No statements fail. On Tue, Nov 3, 2009 at 4:37 PM, Igor Tandetnik itandet...@mvps.org wrote: Shaun Seckman (Firaxis) shaun.seck...@firaxis.com wrote: I just wanted to find out whether or not this is the most ideal method for determining if a column exists in a table. My current technique is to do execute pragma table_info(tableName) then step through the results and perform a string comparison against the name column until I hit a match or I've finished stepping through the record set. Is there a better way? I guess you could just prepare a statement select mycolumn from mytable; (you don't need to actually run it). If the column doesn't exist, prepare will fail. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import on a .csv file
Do you have any new lines, returns, or tabs in any of the real data? Can you prove it? Is this a 1 off thing or are you going to do this routinely? There has been a lot of discussion on this list about importing csv data and the hardships of writing a good csv importer. If this is a one off, some possible tricks: 1) If there are no newlines or tabs inside the data, perhaps you can run away from your embedded delimiter by changing the delimiter to a tab? 2) Have you considered using Access, XL, or open office to see if you can get a clean import into there? This may allow you to save into another format or... 2b) Use the spreadsheet to create the sql you want to import. The merits of various solutions can be found by searching the archive. Go to http://www.mail-archive.com/sqlite-users@sqlite.org/info.html and search on: Tedious CSV import question This was a good discussion. On Thu, Oct 22, 2009 at 12:16 PM, Scott Baker bak...@canbytel.com wrote: I'm trying to .import a CSV file and I can't quite figure out the syntax. I created a table, and then did: .separator , .import /tmp/foo.csv mytable This works sort of, unless my data has , in it. Something like last, first. Because it tries to split at that , and then the number of rows doesn't match my table. Then I tried setting the separator to .separator \,\ Which works correctly (i.e. it splits the data properly). However, now my first and last columns have on the beginning/end of them. Is there a way to import a well formed CSV. My CSV data looks like this: 38665,101977,Deadly Sparrows Inc.,1435 S. Doolis Ln,Donkville,OR,90210,Doolis, Jason,5032349422,Active Help! -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite encription
One can use a 3rd party tool such as a Alladdin HASP key. This encrypts the application, and optionally the database file too. The drivers for the program won't execute a program if it detects a debugger. This solution is of course limited to operating systems with the available drivers. Once nice thing about it is that you can encrypt a copy of the command line tool for yourself so that you can access the encrypted database. On Sun, Oct 18, 2009 at 11:25 PM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: It happened again. DRH explained to me last time I asked. If someone posts from an address that isn't on this list, there's a delay before the post shows up because it waits for moderator approval. In the meantime the original poster often posts the same question again. It didn't happen again - yes I checked before responding. The original post made it to the list several days ago. This post was a brand new one - look at the headers and you can see. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrb3BkACgkQmOOfHg372QTwZgCgoUnLkoaB0jEgCiGd0kAvi+pH oQIAoLc/iTrQ3oP6HIbMo/7frlOS5RTo =WQYU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Synchronising multiple copies of a database
regarding this The fault is that almost nobody does it right: they neglect to keep an 'unaltered central copy' and think they can cross-apply journals each time two databases talk to one-another. That does not work for various reasons. Would a central repository of journals that can be applied to local repositories be sufficient? I suppose I assume that running the same program on N workstations with the same set of journals should produce N identical results. On Wed, Oct 7, 2009 at 12:16 PM, Simon Slavin slav...@hearsay.demon.co.ukwrote: On 7 Oct 2009, at 1:47pm, Jean-Denis Muys wrote: On 10/7/09 11:50 , Simon Slavin slav...@hearsay.demon.co.uk wrote: Try really really hard just to have all sites access your MySQL database remotely. Unfortunately this approach is not possible in the short term. The client applications are legacy applications, porting them to that scheme is a major undertaking. [snip] I completely understand. The recommendation is valuable in the general case, but useless in yours. Still, that's why they pay you the big bucks: to write the complicated program. Keep a journal. Keep an unaltered central copy of the data. As each site contacts the central site, play that sites journal back against the unaltered central copy. The post-journal central copy of the database becomes the new copy for distribution. Interesting idea, that makes a lot of sense in the offline scenario. Standard solution to the synchronisation problem. The fault is that almost nobody does it right: they neglect to keep an 'unaltered central copy' and think they can cross-apply journals each time two databases talk to one-another. That does not work for various reasons. The synchronisation service built into Mac OS X (e.g. synchronising with online services or an iPhone/iPod) implements it in the correct manner. It takes extra data space and fussy programming but it does at least work right ! [snip] In any case, any book reference on this topic? Since I joined this list and noticed repeated questions on the subject I have been trying hard to find any book with anything significant to say on the issue. I failed: everything I found was lacking in some way. Some were flat-out wrong. I work at a university and I think I'm going to ask the Computing people to find me someone who knows this stuff. I'm just paid to do it in real life, not read or write books about it. If I find something good I'll read it and post here about it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert speed greatly decreasing over time
Would dropping and re-creating an index help? On Wed, Oct 7, 2009 at 2:19 PM, Alexey Pechnikov pechni...@mobigroup.ruwrote: Hello! Try this: pragma cache_size=20; 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Synchronising multiple copies of a database
You have to be really careful Absolutely. Even if you know the order of updates (which I do). If site A updates an off line record in a cached copy after site B deletes it other sites can receive the change records in order and have the record re-appear (via insert or replace). One can also get a mess if Mr. Red and Mr Black both get new customers, and enter them and they both get the same ID because the auto-generated int happens to be the same. Both copies get updated with the other guy's data, they then get annoyed and enter the stuff again and it happens over again, but now there are N entries of the other guy's customer in the database depending on how many times they do it. On Wed, Oct 7, 2009 at 3:18 PM, Simon Slavin slav...@hearsay.demon.co.ukwrote: On 7 Oct 2009, at 7:20pm, Adam DeVita wrote: regarding this The fault is that almost nobody does it right: they neglect to keep an 'unaltered central copy' and think they can cross-apply journals each time two databases talk to one-another. That does not work for various reasons. Would a central repository of journals that can be applied to local repositories be sufficient? I suppose I assume that running the same program on N workstations with the same set of journals should produce N identical results. You need a copy of the database which is not changed by any site. All the sites send in their journals. The journals are merged into a superjournal in time order. The superjournal is then applied to the central copy of the database. Then the updated database is sent back out to all sites. The problem comes when you apply multiple journals in a different order. Start with each site with identical copies of a TABLE with three clients: one managed by Mr. Green, one by Mr. Red, and one by Mr. Black. 'G R B'. Then, in this order ... Mr. Green goes on holiday ... Site A says that all Mr. Green's customers will be handled by Mr. Red. UPDATE clients SET contact = 'red' WHERE contact = 'green' Mr. Red goes on holiday ... Site B says that all Mr. Red's customers will be handled by Mr. Black. Then Mr. Green comes back from holiday, and Mr. Black goes on holiday so ... Site C says that all Mr. Black's customers will be handled by Mr. Green. Then they all synchronise databases. See if you can make them all end up with the same data if they synch against each-other rather than a central unaltered copy of the databases. Doesn't work: one site might have 'B B B', another 'R B R'. You can do it only by luck ... by happening to know in which order people went on holiday. However, if you always synch against a central unaltered copy of the database you can synch in any order. Once everyone has synchronised you distribute a copy of the central database to everyone and they all have identical data once more. That's the simplest setup. You can get more complicated by having each site remember which journals they've played back. The problem does not occur if any record can only ever be modified by one site. But if you have the normal 'anyone can do anything' setup, you have to be really really careful. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database Diff libs or applications
There has been a lot of discussion of this and several of us are doing it. Are you talking about A) DB1 which has modify data and DB2 which only receives modifications from DB1 only, or B) DB1 and DB2 both get updates independently and need to be synchronized? or C) something else Not (A or B) Your context implies what automated or custom solution you would use. On Thu, Oct 1, 2009 at 9:44 AM, Shaun Seckman (Firaxis) shaun.seck...@firaxis.com wrote: Hello, I'm looking to externally track the actions made to a database so that I can apply those same actions to another database (assuming the other database has a similar schema). I've searched the documentation and there doesn't seem to be an easy way to extract this data so my only option seems to be utilizing some sort of library or application to diff the two databases and generate a SQL script based on the changes. Has anyone done this before? Does such a library or application exist? I'm sure I could write my own if needed. -Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing two tables?
Good day, Are you looking to simply identify records that are different (not missing from the tables) or identify records with ANY field different and get the result? Is there a primary key? Posting the structure would be helpful. This should not be hard. C:\Documents and Settings\HP_Administratorsqlite3 adam.db SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .tables sqlite create table one (a int, b text); sqlite create table two (a int, b text); sqlite insert into one (a,b) values (1,'23); ... '); sqlite insert into one (a,b) values (2,'23'); sqlite insert into one (a,b) values (3,'423'); sqlite insert into one (a,b) values (5,'4423'); sqlite insert into one (a,b) values (6,'4423'); sqlite insert into two select * from one; sqlite insert into one (a,b) values (4,'3423'); sqlite insert into one (a,b) values (123,'3423'); sqlite insert into two (a,b) values (123,'3423'); sqlite insert into two (a,b) values (1233,'3423'); sqlite select a,b from one where a not in(select a from two) ... union all ... select a,b from two where a not in(select a from one) ; 4|3423 1233|3423 sqlite On Tue, Sep 29, 2009 at 12:38 PM, Petite Abeille petite.abei...@gmail.comwrote: On Sep 29, 2009, at 6:32 PM, Joe Bennett wrote: Have two tables structured exactly the same. Want to compare both of them and get the delta. Been Googling for about an hour now and I see tools that do this (maybe a freeware one I haven't found?) and was looking for a solution that more meets the budget I was given for this project, zero... Any words of wisdom from the group at large on where to find how to do what I'm looking for or any examples? Have you consider union/minus/intersect? Very handy. And free. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
This is why I generally advocate TAB delimited files over CSV Restaurant , Menu Item, Price Tom, Dick The MAN, and Harry's Bar Grill , Specials /new stuff! Mikey's Burger Delishiousness ' , $5 If you only have to upload your data once, you should be able to use a spreadsheet program to convert to TAB delimited rather than going through the work of writing your own parser. On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P ronald.wil...@tycoelectronics.com wrote: I'm trying to take a CSV file and create a sqlite3 database for the iPhone. The CSV file has 33K entries and is 2 MB. The problem I am having is that only about 1/10 of the database file gets written into the sqlite3 database. The .import csv method is imperfect; if you have quoted strings in your csv that have commas or newlines in them, the import will do surprising things. I had to write my own code to do imports with quoted strings. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
http://unixwiz.net/techtips/sql-injection.html is a nice introduction to
sql injection attacks. (Learning by example) It also explains why binding
is far superior to trying to invent a set of rules and cleaning the input.
.
On Thu, Jul 16, 2009 at 9:01 AM, Michael Schlenker m...@contact.de wrote:
Fredrik Karlsson schrieb:
On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenkerm...@contact.de
wrote:
Your working far too hard. The sqlite Tcl binding already does all thats
needed.
This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and
id
$compId}]
But you MUST use {} to quote your query and not , so sqlite gets to do
the
substitution (or better said convert things to prepared statements and
bind
values correctly) and not Tcl.
Michael
Hi Michael,
Ok, I can see how this would be the easiest solution, but what I am
doing is basically a query builder (maping of comands in a specialized
language to pattern subselects in SQL queries). Since the statements
can be nested in many different ways, I cannot expect to be able to
construct the query and keeping track of variable names to be used in
the final substitution, so that I can make use of the built in binding
feature of sqlite It is much to much hard work.
I don't think so.
Just use an array to store your values and prefix the names with the
identifier of your subpattern. Now when you emit your subpattern via
[format] or some other method just add the appropriate prefixed bind
variables. Should not be too hard.
Instead, I think I need to make each part of the query return a
complete (not to be evaluated further outside of sqlite) SQL query
subselect statement, which is why I think I need to make sure that the
values I insert is safe inside an SQL statement myself.
Or, do you know of a Tcl command to make strings SQL safe? (Sorry
for making this into a Tcl question now..)
Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
you know its wrong.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing insert or replace speed
Also, very good. No index on Delta File: 3 seconds. Index on SN: 4 seconds. Index on MasterList (write_out_ok, MFGID, TypeID, SN); 4 seconds. Time is to the nearest second in my test program, so I can't distinguish between the two. In summary: /*FAST */ insert or replace into main.masterlist select d.* from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn where d.write_out_ok=0 and d.record_updatetime = ifnull(M.record_updatetime, '') /* just as FAST*/ insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and not exists (select 1 from main.masterlist M where M.sn = d.sn and M.record_updatetime d.record_updatetime); /* very SLOW*/ insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and d.sn not in(select M.sn from main.masterlist M where M.record_updatetime d.record_updatetime) On Wed, Jul 15, 2009 at 7:33 AM, Igor Tandetnik itandet...@mvps.org wrote: Adam DeVita wrote: I've identified the following query as a bottle neck in a utility I've written. insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and d.sn not in(select M.sn from main.masterlist M where M.record_updatetime d.record_updatetime) Try this: insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and not exists (select 1 from main.masterlist M where M.sn = d.sn and M.record_updatetime d.record_updatetime); It appears that your query doesn't use an index on M(sn), while mine does. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimizing insert or replace speed
Good day, Could someone explain where I'm going wrong with this? I've identified the following query as a bottle neck in a utility I've written. insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and d.sn not in(select M.sn from main.masterlist M where M.record_updatetime d.record_updatetime) The purpose is to import a data from a remotely created change file, with only new/newer records. (Due to the fact that the subject of the data is shipping / receiving product serial numbers and that data moves faster than product there is no way independent nodes can create a change to a record at the same time. Also, deleting is not allowed.) The change file is attached as 'delta' The structure of masterlist in the main database is: sqlite .schema masterlist CREATE TABLE MasterList (SN int primary key not null, TypeID int default 0 references Product_type_dictionary(TypeID) , ConstructionDate text, MFGID int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date text, record_updatetime text default 2000.00.00.00, write_out_ok int default 0); CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); main.masterlist has 36,000 records deltas.masterlist has 9,000 records Notes about fields: write_out_ok is a flag indicating that the record has been imported. States are 1 or 0. MFGID is a manufacturer, about 4 different ints can be used. TypeID is a product Type, about 7 different types, The index is ordered by cardinality, and all int. record_updatetime is the modified date time GMT (UTC), .mm.dd.hh.MM.ss Experimenting with indexes on the delta file with No indexes: 7 min 22s CREATE INDEX IDX_MasterList on MasterList ( SN); 14min 52s CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); 20 min, 07s --- Dropped indexes on both main and delta. ~20 min. - Is the real problem a poor choice of index in main? regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing insert or replace speed
Awesome, brilliant, and decisive! New times: No index on Delta File: 3 seconds. Index on SN: 4 seconds. Index on MasterList (write_out_ok, MFGID, TypeID, SN); 4 seconds. The speedup of the one query is greater than this because the above time figures include 1) A query to see if there are any records in deltas with write_out_ok=0 (if so, don't execute other queries) 2) A query to update write_out_ok =1 in delta where the record in main exists and is newer. 3) A query to update write_out_ok =1 in main where the record came from delta; 1,2, 3 were negligible compared to the un-optimized insert or replace into TargetD select * from sourceD sa where sa.write_out_ok=0 and sa.sn not in (select ta.sn from TargetD ta where ta.record_updatetime sa.record_updatetime) ; Now, it appears that the time is comparable, so the actual time is in the order of 2 seconds faster than listed above. Dropping the sequence time from 7 min 22s down to 0 minutes 4 seconds is tremendous. thank you. Adam On Tue, Jul 14, 2009 at 2:04 PM, Pavel Ivanov paiva...@gmail.com wrote: I believe your choice of query is not good enough. Try this one: insert or replace into main.masterlist select d.* from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn where d.write_out_ok=0 and d.record_updatetime = ifnull(M.record_updatetime, '') Pavel On Tue, Jul 14, 2009 at 12:54 PM, Adam DeVitaadev...@verifeye.com wrote: Good day, Could someone explain where I'm going wrong with this? I've identified the following query as a bottle neck in a utility I've written. insert or replace into main.masterlist select * from delta.masterlist d where d.write_out_ok=0 and d.sn not in(select M.sn from main.masterlist M where M.record_updatetime d.record_updatetime) The purpose is to import a data from a remotely created change file, with only new/newer records. (Due to the fact that the subject of the data is shipping / receiving product serial numbers and that data moves faster than product there is no way independent nodes can create a change to a record at the same time. Also, deleting is not allowed.) The change file is attached as 'delta' The structure of masterlist in the main database is: sqlite .schema masterlist CREATE TABLE MasterList (SN int primary key not null, TypeID int default 0 references Product_type_dictionary(TypeID) , ConstructionDate text, MFGID int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date text, record_updatetime text default 2000.00.00.00, write_out_ok int default 0); CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); main.masterlist has 36,000 records deltas.masterlist has 9,000 records Notes about fields: write_out_ok is a flag indicating that the record has been imported. States are 1 or 0. MFGID is a manufacturer, about 4 different ints can be used. TypeID is a product Type, about 7 different types, The index is ordered by cardinality, and all int. record_updatetime is the modified date time GMT (UTC), .mm.dd.hh.MM.ss Experimenting with indexes on the delta file with No indexes: 7 min 22s CREATE INDEX IDX_MasterList on MasterList ( SN); 14min 52s CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); 20 min, 07s --- Dropped indexes on both main and delta. ~20 min. - Is the real problem a poor choice of index in main? regards, Adam ___ 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 -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
why not use: SELECT A.ID http://a.id/, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B ON A.Column3 = B.ID http://b.id/ INNER JOIN C ON B.Column2 = C.ID http://c.id/ ? On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke edward.ha...@hawkeyeinnovations.co.uk wrote: Hi all, I'm having problems getting nested inner joins to work with SQLite. As far as I can tell from various resources the correct way of joining multiple tables is this: SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID However depending upon where I put the parentheses I get various different errors from the viewer I use (SQLite Manager for Firefox). A normal Inner Join without the nesting works fine. Can anyone tell me what I'm doing wrong? Regards, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search in archive
There is a search of archives at http://www.mail-archive.com/sqlite-users%40sqlite.org/ On Fri, Jun 19, 2009 at 2:43 PM, Kees Nuyt k.n...@zonnet.nl wrote: On Fri, 19 Jun 2009 13:56:52 -0400, Rizzuto, Raymond raymond.rizz...@sig.com wrote: Is it possible to have a search feature for the archive? Which archive? I'll assume you have 18 different databases and you want to search them in parallel. I.e. rather than having to do a linear search through 18 archives for an answer to a question, have a google-like search across all of the archives? Yes, make your application multithreaded, one thread for the user interface and 18 for databases. Every dbthread would open a different database. It will only really help if your system has multiple processor cores, and if the databases are each on a different disk. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
