Re: [sqlite] forcing X'' literals in sqlite3's .dump?
Stephan Beal wrote: > Just to be pedantic for a moment: the shell is GPL if you #define > USE_READLINE to a true value. Such is the reality of viral licenses. From > shell.c: The shell in its lonesome is never GPL, only the combination with readline is. If you distributed a combination of the SQLite shell with readline, the combination would have to be GPL. But if someone took your combination and extracted out the SQLite shell parts, separating them from the readline parts, then those extracted parts are still also usable under the original SQLite license. The GPL never prevents anyone from using the SQLite source by itself under the public domain, no matter how anyone gets their copy of SQLite, whether linked with readline or not. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve the Specil characters from the Sql lite
On 17 Aug 2011, at 11:39pm, Kees Nuyt wrote: > On Wed, 17 Aug 2011 14:45:22 +0100, Simon Slavin > wrote: > >> Parsing a long string containing many SQL commands to >> find out where the commands are separated is non-trivial. [snip] > > It is built in. The first sqlite3_prepare*() on a multiple > statement string parses until the first statement delimiter, ';', > and returns a pointer to the rest of the string (pzTail), so if > properly used, the next sqlite3_prepare*() parses the next > statement in the same multiple statement string. Oh that's neat. Thanks, Nees. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve the Specil characters from the Sql lite
On Wed, 17 Aug 2011 14:45:22 +0100, Simon Slavin wrote: > >On 17 Aug 2011, at 2:26pm, Igor Tandetnik wrote: > >> Simon Slavin wrote: >>> Question for experts: Hmm. There's no sqlite3_exec16() ? >>> Do I misunderstand how it would be useful ? >> >> sqlite3_exec is a thin wrapper around sqlite3_prepare, >> step and finalize, maintained for backward compatibility. >> Just use those, or their _16 counterparts, directly. > > Parsing a long string containing many SQL commands to > find out where the commands are separated is non-trivial. > I figured that the necessary logic must already be built > into SQLite somewhere. > But hey, I don't actually have a use for this, > it's just that the omission looks a little strange. It is built in. The first sqlite3_prepare*() on a multiple statement string parses until the first statement delimiter, ';', and returns a pointer to the rest of the string (pzTail), so if properly used, the next sqlite3_prepare*() parses the next statement in the same multiple statement string. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forcing X'' literals in sqlite3's .dump?
On Wed, Aug 17, 2011 at 11:52 PM, Richard Hipp wrote: > On Wed, Aug 17, 2011 at 12:28 PM, Stephan Beal >wrote: > > > But the reality is still: if the sqlite3 shell links with libreadline, it > > is GPL. > > > > > Not. > Sorry, i wasn't clear: the resulting binary, not the shell sources, is GPL. And thus... > then *you* must also be willing to distribute sources for that program > under > the GPL terms. but the sources aren't affected, at least as long as the readline bits are only conditionally compiled. If i'm not sorely mistaken, UNconditionally #including GPLd code makes the sources including them (and those including _them_) GPL by extension. (But it's not my intention to start a religious discussion. ;) -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forcing X'' literals in sqlite3's .dump?
On Wed, Aug 17, 2011 at 12:28 PM, Stephan Beal wrote: > But the reality is still: if the sqlite3 shell links with libreadline, it > is GPL. > > Not. If *you* distribution binaries of a program that links against readline, then *you* must also be willing to distribute sources for that program under the GPL terms. But what *you* do does not place GPL requirements on anybody else. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] null handling import
Stephan, Yeah, there's a lot more to SQL than I thought. Seems to be many different ways to skin the same cat. The reason it took me so long to respond to Tom was that I trying to use views to import several files with a different number and ordering of columns into a single generic table. Matt --- On Wed, 17/8/11, Stephan Beal wrote: From: Stephan Beal Subject: Re: [sqlite] null handling import To: "General Discussion of SQLite Database" Date: Wednesday, 17 August, 2011, 18:13 On Wed, Aug 17, 2011 at 7:12 PM, matthew (matthew.jsoft) white < matthew.js...@btinternet.com> wrote: > I didn't realise that views could be written to. > i didn't, either. Thank you very much for posting a follow-up with your results. i now have some experimentation to do... Happy Hacking! -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ 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] null handling import
On Wed, Aug 17, 2011 at 7:12 PM, matthew (matthew.jsoft) white < matthew.js...@btinternet.com> wrote: > I didn't realise that views could be written to. > i didn't, either. Thank you very much for posting a follow-up with your results. i now have some experimentation to do... Happy Hacking! -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] null handling import
Hi Tom, I've been testing your suggestion over the past few days and it works fine. The great advantage of using triggers is being able to perform arbitrary checks and manipulation of the raw data. At the expense of more elaborate SQL srcipts, the CSV files can be left in a more readable form which allows them to be easy visually scanned and DIFF'ed when testing the AWK conversion programs. I didn't realise that views could be written to. Cheers, Matt --- On Sun, 14/8/11, BareFeetWare wrote: From: BareFeetWare Subject: Re: [sqlite] null handling import To: "General Discussion of SQLite Database" Date: Sunday, 14 August, 2011, 2:04 > On 13 Aug 2011, at 7:58am, matthew (matthew.jsoft) white wrote: > >> I was just wondering if sq3 has some kind of mechanism to parse blank >> records as null instead of empty strings. One way to do this is to import into a view and use a trigger to convert the rows how you like. For example: create table Person ( ID integer primary key not null , Name text collate nocase , Email text collate nocase ) ; create view Import as select Name, Email from Person ; create trigger "Import insert" instead of insert on Import begin insert into Person (Name, Email) select case when Name = '' then null else Name end , case when Email = '' then null else Email end ; end ; Then just import into the "Import" view instead of the table. Tom Tom Brodhurst-Hill BareFeetWare Sent from my iPhone ___ 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] forcing X'' literals in sqlite3's .dump?
On Wed, Aug 17, 2011 at 6:25 PM, Stephan Beal wrote: > Just to be pedantic for a moment: the shell is GPL if you #define > USE_READLINE to a true value. Such is the reality of viral licenses. From > shell.c: > And to be even more pedantic: that code came from the WRONG shell.c (not the copy which comes with sqlite3, but a local copy i created 3 years ago and haven't touched since then). My apologies for any confusion. But the reality is still: if the sqlite3 shell links with libreadline, it is GPL. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forcing X'' literals in sqlite3's .dump?
On Wed, Aug 17, 2011 at 5:09 PM, Roger Binns wrote: > They are part of the standalone shell (ie not the library). The shell > source code is under the same license as the rest of SQLite (ie as > public domain as possible) so you are free to make a copy and do > whatever you want with it. > > Just to be pedantic for a moment: the shell is GPL if you #define USE_READLINE to a true value. Such is the reality of viral licenses. From shell.c: #if USE_READLINE # include # include #else # define readline(p) local_getline(p,stdin) # define add_history(X) # define read_history(X) # define write_history(X) # define stifle_history(X) #endif -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SqLite - Help
> Is it possible to create Storedprocedure or Functions in Sqlite. ? No. > Since i am new to Sqlite. Can u suggest some best sites or links to learn > Sqlite (ofcourse i saw Sqlite.org site) Other than sqlite.org you can look at google.com. It is usually very good at finding answers on SQLite-related questions. ;-) More seriously you can buy this book http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189 or ask questions on this mailing list (btw, author of that book is actively answering questions on this list too). Pavel On Wed, Aug 17, 2011 at 4:59 AM, Madhankumar Rajaram wrote: > > > > Hi, > I am new to Sqlite. > I need to develop a Desktop windows application C# with Sqlite. > > Is it possible to create Storedprocedure or Functions in Sqlite. ? > If so, can you send me an example for both. > > Since i am new to Sqlite. Can u suggest some best sites or links to learn > Sqlite (ofcourse i saw Sqlite.org site) > > Thanks a lot in advance. > > > > Madhan Kumar R > > > Corporate Development Center (CDC) > > > > > > Technip France – Abu Dhabi - Region Middle East - Hamdan Street (5th > Street) – P.O.Box 7657- Abu Dhabi - UAE - www.technip.com > Tel (Direct) +971 2 6116485 - Switchboard +971 2 6116000 - Fax +971 2 > 6266580 – EMail maraja...@technip.com - Mobile +971 55 9198065 Visit the > CDC page on TPnet > > > > > > This email and any attached files ("Message") may contain confidential and/or > privileged information. It is intended solely for the addressee(s). If you > receive this Message in error, inform the sender by reply email, delete the > Message and destroy any printed copy. > Any unauthorized use, distribution, or copying of this Message or any part > thereof is prohibited. Emails are susceptible to alteration. Neither Technip > nor any of its affiliates shall be liable for the Message if altered or > falsified nor shall they be liable for any damage caused by any virus that > might be transmitted with this Message. > > ___ > 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] type of a value bound by sqlite3_bind_blob ()?
> Roger Binns writes: > On 08/16/2011 04:59 PM, Ivan Shmakov wrote: >> In the sqlite3's .dump command's output, the binary blobs may either >> be represented as hexadecimal X''-literals, or as text strings. […] > I suggest using typeof on the data you think is blobs to verify what > type they really are. The values in question are indeed of mixed type. Thanks! Yet, it makes me wonder, how the following bit of code could ever have resulted in a value of the TEXT type being bound? { int r = sqlite3_bind_blob (st, ix_sha1, sha1, CHUNK_DB_SHA1_LEN, SQLITE_TRANSIENT); assert (r == SQLITE_OK); } Somehow, I've assumed that sqlite3_bind_blob () will bind a parameter to a blob. (It's SQLite 3.7.3 debian 1.) >> Also, are the .dump and .read commands implemented as part of >> the sqlite3 binary, or are they part of the library? > They are part of the standalone shell (ie not the library). The > shell source code is under the same license as the rest of SQLite (ie > as public domain as possible) so you are free to make a copy and do > whatever you want with it. ACK. Thanks. -- FSF associate member #7257 Coming soon: Software Freedom Day http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forcing X'' literals in sqlite3's .dump?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/16/2011 04:59 PM, Ivan Shmakov wrote: > In the sqlite3's .dump command's output, the binary blobs may > either be represented as hexadecimal X''-literals, or as text > strings. What evidence do you have for that claim? > I wonder, how do I force sqlite3(1) to exclusively use the X'' > representation? Binary blobs are always output using the X representation. Evidence below using ASCII characters 'abc': sqlite> create table foo(x); sqlite> insert into foo values(X'616263'); sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE foo(x); INSERT INTO "foo" VALUES(X'616263'); COMMIT; sqlite> select * from foo; abc sqlite> select typeof(x) from foo; blob I suggest using typeof on the data you think is blobs to verify what type they really are. > Also, are the .dump and .read commands implemented as part of > the sqlite3 binary, or are they part of the library? They are part of the standalone shell (ie not the library). The shell source code is under the same license as the rest of SQLite (ie as public domain as possible) so you are free to make a copy and do whatever you want with it. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5L2akACgkQmOOfHg372QS3rQCfUjaaTshX9RJe8V68XwygC6nh mwMAnjgMe8nkbHISzKjFA/8Bx74Jejig =HkEd -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Official replacement for sqlite3_expire
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/17/2011 02:47 PM, Richard Hipp wrote: > So you, the programmer, don't need to mess > with sqlite3_expired() any more. However you do need to be aware that error codes are different between an identical statement that you called prepare on versus one that has been automatically reprepared. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5L1oUACgkQmOOfHg372QQTXACgjj+OK+xosx0Zi0k/jLppoAYN KBkAoIFtppknJbTLHUkIn9yyFTAHteV0 =46dZ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] aggregate by break in sequence
Well, not easy in a programming language - however, certainly an option. I was hoping that there would be some kind of break in sequence aggregation available (or a trick) in Sqlite. My problem I suspect is not so uncommon. I know that I can do this in INFO (an outdated database of ArcInfo) and I believe in SqlPlus (oracle?). Thanks again for the pointers. Prasad On Tue, Aug 16, 2011 at 5:41 PM, Simon Slavin wrote: > > On 16 Aug 2011, at 9:58pm, Anantha Prasad wrote: > > > The data is sorted by Longitude and then by Distance. Whenever there are > > repeated values of Distance within a Longitude, I want the Sum and Count > to > > be added and divided - for example, > > Here is the table: > > Longitude Distance AvgColz SumColz CountColz > > 71.0 1050. 1002 > > 71.0 1092.8571 2600 28 > > 71.0 2083.8710 5200 62 > > 71.0 2082.6389 11900 144 > > 71.0 3062.0425 2916 47 > > 71.0 3081.7119 9642 118 > > 71.0 4017.8723 840 47 > > 71.0 4049.8868 2644 53 > > 71.0 508.581436943 > > 71.0 605.0238 211 42 > > This is manipulation of data, and the way you want it done is not going to > be the way the next programmer is going to want it done. Whatever kind of > averaging or interpolation you want done, do it in your programming > language, either by pre-scanning your database and replacing what you find, > or by writing your own sum and count routines which understand what you want > done. > > 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] How to retrieve the Specil characters from the Sql lite
On 17 Aug 2011, at 2:26pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> Question for experts: Hmm. There's no sqlite3_exec16() ? Do I >> misunderstand how it would be useful ? > > sqlite3_exec is a thin wrapper around sqlite3_prepare, step and finalize, > maintained for backward compatibility. Just use those, or their _16 > counterparts, directly. Parsing a long string containing many SQL commands to find out where the commands are separated is non-trivial. I figured that the necessary logic must already be built into SQLite somewhere. But hey, I don't actually have a use for this, it's just that the omission looks a little strange. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve the Specil characters from the Sql lite
Simon Slavin wrote: > Question for experts: Hmm. There's no sqlite3_exec16() ? Do I misunderstand > how it would be useful ? sqlite3_exec is a thin wrapper around sqlite3_prepare, step and finalize, maintained for backward compatibility. Just use those, or their _16 counterparts, directly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve the Specil characters from the Sql lite
On 17 Aug 2011, at 6:44am, swathi wrote: >I want to retrieve some german characters form the Sqlite . How > can i use it . Please Help me Use the 16-bit routines from SQLite to store and retrieve characters. In other words, if you're binding text, see this page http://www.sqlite.org/c3ref/bind_blob.html and use int sqlite3_bind_text16 instead of sqlite3_bind_text . If you're using prepare/step/finalize then see this page http://www.sqlite.org/c3ref/prepare.html and use sqlite3_prepare16_v2 instead of sqlite3_prepare_v2 . The 16-bit routines expect to receive strings coded in Unicode, and return strings coded in Unicode. Make sure your program converts your strings to Unicode before storing them in the database, and understands that strings it retrieves from the database will be encoded as Unicode. Question for experts: Hmm. There's no sqlite3_exec16() ? Do I misunderstand how it would be useful ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Primary Key size
On Wed, 17 Aug 2011 01:39:57 -0700 (PDT), LiranR wrote: > >Hi all ! >A quick question: >Do you know if there is a different in performance when i use 4 bytes >Integer Primary Key or 8 bytes Integer Primary Key? The INTEGER type in SQLite is a 64bit signed integer. It is stored in a compressed form, such that the left most zero's of the binary representation are dropped. Stprage size and speed is best when using smaller, positive values, but don't overestimate this effect. Remember to use the exact wording: INTEGER PRIMARY KEY, as it will make the primary key an alias for the always present internal primary key, AKA rowid. This has several advantages (like portability, speed, and the value of rowid will not change using VACUUM). So, avoid types like INT(4) or INT(8), especially for primary keys. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Official replacement for sqlite3_expire
On Wed, Aug 17, 2011 at 4:22 AM, Scholz Maik (CM-AI/PJ-CF42) < maik.sch...@de.bosch.com> wrote: > Hi, > I like to use and reuse SQLite statements. > I need a way to detect, when a statement is obsolete because e.g. > the schema was changed. > > So sqlite3_expire is marked deprecated, what shall I use instead? > Prepare your statements using sqlite3_prepare_v2() instead of sqlite3_prepare(). Then if the schema changes and the statements need to be reprepared, the reprepare will happen automatically the next time the statement is used. SQLite knows that the statement has expired and deals with it for you automatically. So you, the programmer, don't need to mess with sqlite3_expired() any more. > > Maik > > ___ > 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] Official replacement for sqlite3_expire
Hi, I like to use and reuse SQLite statements. I need a way to detect, when a statement is obsolete because e.g. the schema was changed. So sqlite3_expire is marked deprecated, what shall I use instead? Maik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to retrieve the Specil characters from the Sql lite
Hello , I want to retrieve some german characters form the Sqlite . How can i use it . Please Help me -- *Thanks&Regards* *swathi.Empress* *4 the people* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read only scaling optimization
Pavel, Thank you for this information. It is exactly what I was looking for. That said if you are absolutely sure that no other process will write to the database while you are reading you can create your own VFS and make xAccess and xCheckReservedLock methods a no-op. You can read more about VFS starting from here http://www.sqlite.org/c3ref/vfs_find.html (follow the links to read all related info). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SqLite - Help
Hi, I am new to Sqlite. I need to develop a Desktop windows application C# with Sqlite. Is it possible to create Storedprocedure or Functions in Sqlite. ? If so, can you send me an example for both. Since i am new to Sqlite. Can u suggest some best sites or links to learn Sqlite (ofcourse i saw Sqlite.org site) Thanks a lot in advance. Madhan Kumar R Corporate Development Center (CDC) Technip France – Abu Dhabi - Region Middle East - Hamdan Street (5th Street) – P.O.Box 7657- Abu Dhabi - UAE - www.technip.com Tel (Direct) +971 2 6116485 - Switchboard +971 2 6116000 - Fax +971 2 6266580 – EMail maraja...@technip.com - Mobile +971 55 9198065 Visit the CDC page on TPnet This email and any attached files ("Message") may contain confidential and/or privileged information. It is intended solely for the addressee(s). If you receive this Message in error, inform the sender by reply email, delete the Message and destroy any printed copy. Any unauthorized use, distribution, or copying of this Message or any part thereof is prohibited. Emails are susceptible to alteration. Neither Technip nor any of its affiliates shall be liable for the Message if altered or falsified nor shall they be liable for any damage caused by any virus that might be transmitted with this Message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Primary Key size
Hi all ! A quick question: Do you know if there is a different in performance when i use 4 bytes Integer Primary Key or 8 bytes Integer Primary Key? -- View this message in context: http://old.nabble.com/Primary-Key-size-tp32277931p32277931.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