[sqlite] SQLite3 v3.10.0 compilation error
Got the latest [3392f8fa] fix from the trunk but now get these
errors/warnings:
./sqlite3.c:27509:42: error: expected expression before ?,? token
{ "munmap", (sqlite3_syscall_ptr),0 },
^
./sqlite3.c:27387:12: warning: ?unixGetpagesize? used but never defined
[enabled by default]
static int unixGetpagesize(void);
^
-Original Message-
From: to...@acm.org
Sent: Friday, January 08, 2016 8:52 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite3 v3.10.0 compilation error
(Under Linux)
I?m using the SQLITE_OMIT_WAL option and because of this the #define
osReadlink is not defined (amalgamation line # 27508) which is later
required by unixFullPathname function, and compilation fails.
Thanks.
*** SORRY IF YOU HAVE RECEIVED THIS TWICE ? NOT SURE IT MADE IT THE FIRST
TIME ***
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to make sqlite3_release_memory produce a result?
Just to confirm that if sqlite3.dll is compiled with DSQLITE_DEFAULT_PCACHE_INITSZ=0 I can also see memory being released here. So, that was indeed it. RBS On Thu, Jan 7, 2016 at 11:04 PM, Scott Hess wrote: > Hmm. I see that sqlite3PcacheReleaseMemory() is a no-op > if sqlite3GlobalConfig.nPage is non-zero, and that happens > when SQLITE_DEFAULT_PCACHE_INITSZ is not zero. If I compile with > -DSQLITE_DEFAULT_PCACHE_INITSZ=0 , then I see the expected pages freed. > AFAICT, it doesn't matter if you make use of more than > SQLITE_DEFAULT_PCACHE_INITSZ pages, it still doesn't free the excess. > > Looks like that was added in 3.8.11 back in July. > > -scott > > > On Thu, Jan 7, 2016 at 5:44 AM, Bart Smissaert > wrote: > > > These are the compile flags of my sqlite3.dll: > > > > compile_option > > > > DEBUG > > ENABLE_COLUMN_METADATA > > ENABLE_MEMORY_MANAGEMENT > > MEMDEBUG > > OMIT_LOOKASIDE > > THREADSAFE=0 > > > > And these are the set pragma's: > > > > PRAGMA cache_size 32768 > > PRAGMA default_cache_size 32768 > > PRAGMA page_count 712711 > > PRAGMA max_page_count 1073741823 > > PRAGMA page_size 1024 > > PRAGMA journal_size_limit -1 > > PRAGMA locking_mode normal > > PRAGMA automatic_index 1 > > PRAGMA encoding UTF-8 > > PRAGMA ignore_check_constraints 0 > > PRAGMA read_uncommitted 0 > > PRAGMA recursive_triggers 0 > > PRAGMA reverse_unordered_selects 0 > > PRAGMA secure_delete 0 > > PRAGMA wal_autocheckpoint 1000 > > PRAGMA writable_schema 0 > > PRAGMA journal_mode off > > PRAGMA auto_vacuum NONE > > PRAGMA synchronous OFF > > PRAGMA temp_store DEFAULT > > > > > > RBS > > > > On Thu, Jan 7, 2016 at 1:39 PM, Bart Smissaert > > > wrote: > > > > > > So you should > > > see results if you start a transaction, do a few update statements, > > commit > > > the transaction, the call sqlite3_release_memory(db). > > > > > > I tried this with a large table, first with no transaction then with a > > > transaction > > > and tried the sqlite3_release_memory directly after the > sqlite3_finalize, > > > but in both cases result still zero: > > > > > > sqlite3_memory_used: 37190712 > > > sqlite3_release_memory:0 > > > sqlite3_memory_used: 37190712 > > > > > > This was a single update, where I don't think a transaction is helpful. > > > Still no idea how I can make sqlite3_release_memory produce non-zero. > > > > > > RBS > > > > > > > > > > > > On Thu, Jan 7, 2016 at 6:05 AM, Scott Hess wrote: > > > > > >> On Wed, Jan 6, 2016 at 3:03 PM, Bart Smissaert < > > bart.smissaert at gmail.com> > > >> wrote: > > >> > > >> > Have compiled sqlite3.dll (latest) compiled with > > >> ENABLE_MEMORY_MANAGEMENT, > > >> > but sofar > > >> > not been able yet to make sqlite3_release_memory produce anything > else > > >> than > > >> > 0. > > >> > What would be the simplest way to make this happen? > > >> > I don't want to do this with C coding, so it should be some SQL > > >> scenario or > > >> > to do with simple > > >> > SQLite functions such as sqlite3_step, _prepare, -finalize etc. > > >> > > >> > > >> Last time I was paying attention to this, I believe that I found that > > the > > >> biggest effect was to free unpinned pages from the page cache. So it > > >> might > > >> not free pages if you're in a transaction, for instance. I would > guess > > >> that if you had memory-mapped mode on and are doing only reads, there > > >> would > > >> be no pages to free (mmap pages aren't in the page cache). So you > > should > > >> see results if you start a transaction, do a few update statements, > > commit > > >> the transaction, the call sqlite3_release_memory(db). > > >> > > >> -scott > > >> ___ > > >> sqlite-users mailing list > > >> sqlite-users at mailinglists.sqlite.org > > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >> > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] SQLite3 v3.10.0 compilation error
(Under Linux) I?m using the SQLITE_OMIT_WAL option and because of this the #define osReadlink is not defined (amalgamation line # 27508) which is later required by unixFullPathname function, and compilation fails. Thanks. *** SORRY IF YOU HAVE RECEIVED THIS TWICE ? NOT SURE IT MADE IT THE FIRST TIME ***
[sqlite] Can SQLite know from the statement string if it is row producing or not?
I am interested to know from the statement string if the statement is invalid, row producing (could produce rows) or non row producing. I know sqlite3_prepare16_v2 can see if the statement is valid or not but how about the other 2? I can do this in code no problem, but it may not always be 100% reliable for example there could be a new pragma or maybe some new SQL keyword. RBS
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
On 2016/01/08 9:51 AM, Darren Duncan wrote: > Stephen, > > What you are arguing for (no shared libraries) is bad old days where > one had to recompile their programming language to add support for a > DBMS, rather than the DBMS support being a separately installable > library that one could choose to install or not or upgrade > semi-independently or not, or choose to use an alternative or not. I can't agree more - and to add, while I can sympathize with the point, I absolutely love SQLite, but the amount of projects I have made without SQLite far outweighs those containing it (on all platforms). I would like it to remain optional everywhere. Speaking of Delphi specifically (as the OP mentions, and which I do use), I have simply a unit that links the DLL, and another that maintains an object that does all data handling. It's the simplest solution - Sure I need to add the "sqlite3.dll" file to my installers and updaters, but I get a free upgrade by just dropping in the new DLL when it arrives - no need to recompile or re-setup or anything. (Likewise for iOS / Mac OSX, but Linux projects [freepascal / Lazarus] are more tricky in this regard - probably only due to my limited knowledge, I'm sure someone somewhere made stuff for it). I'm quite willing to share any of the delphi libraries and objects if anyone is interested (best mail me off-list) - they can be seen in action if need be by simply peeking at sqlitespeed from http://www.sqlc.rifin.co.za Cheers, Ryan
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
Okay, I think this clears some things up. On 2016-01-08 11:36 AM, Warren Young wrote: > On Jan 8, 2016, at 12:39 AM, Darren Duncan wrote: >> >> I interpreted your request as if current systems' error outputs at execute >> time were printing out the problematic SQL statement with placeholder names >> as originally prepared, and you wanted the error outputs to have the >> placeholders substituted with literals for the values passed to them at >> execute time interpolated into them. > > Yes. > >> one can just list the bound values separately / afterwards rather than >> having to rewrite the SQL to interpolate those values. > > Of course, but the question is not whether a caller *can* do this, it?s > whether the caller *should have to* do this. > > From the caller?s perspective, it has already passed ownership of the values > off to SQLite via sqlite3_bind_*(). If an error occurs, the bind calls may > be a few levels separate from the sqlite3_step() call that actually causes > the error. SQLite still owns the values, though, and could provide them in a > hypothetical sqlite3_preview() call, which assembles the effective SQL it > tried to execute and failed. > > You?re asking the caller to maintain separate ownership of data that SQLite > needs just for the error case. SQLite has a much better reason to have a > copy of that data, so it should be providing the values to the error handler, > not the caller. Actually, I agree with you that SQLite should be providing everything. What I disagree with is making the interpolation necessary. The hypothetical sqlite3_preview() should output 2 things, the SQL as originally passed to prepare with placeholders intact, plus a list of placeholder names and their bound values that failed. >> If your problem is that the output simply says an error occurred and doesn't >> print out the SQL, then fair enough, I misunderstood you. > > It?s a bit more than that. The problem is that a given prepared statement is > necessarily generic. Just from looking at the statement in a log file, you > can?t tell what values were used with it, which would help you understand the > context in which it was used. To further explain, I see SQL as a programming language same as C or Perl or whatever. Calling prepare is effectively invoking a compiler on SQL source code where that SQL source defines a routine that may have parameters. Calling execute is then asking to execute that compiled routine where the bind parameters are the runtime-provided arguments to the routine. Do you think it makes sense in any other common programming language that, if a routine fails with a particular set of arguments, that the debugging message includes say C source code rewritten to substitute literals where references to its parameters were? Or does it make more sense for the debugging message to print the actual routine source plus a list of the passed argument values? I am arguing for the latter, all done by SQLite. I consider what is reasonable for SQL to be the same as for other languages. -- Darren Duncan
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
On 2016-01-08 8:08 AM, Stephen Chrzanowski wrote: > For the record, *I* personally prefer trying to get all essential resources > built directly into my final output (For SQLite, default database > structures, SQLite strings, and maybe that one day, SQLite itself), that > way I'm in control of what the application does, and have no reliance on a > 3rd party update to a resource file that breaks my code. That is just my > preference, and old school or not, I prefer working software, not software > that might work after MySQL updates and breaks a resource I require when my > application doesn't touch MySQL, or when a user deletes a critical file my > application requires and claims they didn't do anything I've > never had 100% success on a fully independent database driven application > (SQLite or not), and that is perfectly OK. That doesn't mean I'd like to > strive for that one day. You are or seem to be talking about 2 different things in this thread. I very much agree with you that it is reasonable for an APPLICATION to bundle its key dependent libraries in ITS executable so the proper functioning of the application is insulated against many changes to system-provided or separately installed libraries. Especially today with abundant disk space. But what you seemed to be arguing for before was that a programmer tool for making applications, that is Perl itself or R itself or what have you should be bundling SQLite with it, and this I disagree with. The user base of programming language environments is programmers who are making applications, and it should be those users' decision to bundle SQLite with their application, and not having it forced on them by the creator of the programming language to include SQLite with all applications regardless of whether it is used or not. Apples and oranges. -- Darren Duncan
[sqlite] Some FTS5 guidance
You can create a custom tokenizer as well then use the standard search APIs. I imagine that functionality would work well in this case: https://sqlite.org/fts5.html#section_7 On Thu, Jan 7, 2016 at 3:59 PM, Stadin, Benjamin < Benjamin.Stadin at heidelberg-mobil.com> wrote: > One such algorithm would be a (generalized) Ukkonnen suffix tree ( > https://en.m.wikipedia.org/wiki/Ukkonen%27s_algorithm). > It allows you to search efficiently for substrings. > It would be possible to do some match weigthing based on match distance > within words. But a general solution for a database is probably not trivial > to implement. > > Ben > > Von meinem iPad gesendet > > > Am 07.01.2016 um 21:46 schrieb Matthias-Christian Ott : > > > >> On 2016-01-07 19:31, Mario M. Westphal wrote: > >> I hence wonder if this problem has been tackled already and if there is > a > >> "standard" solution. > > > > If I understand you correctly, it seems that you are looking for a > > compound splitting or decompounding algorithm. Unfortunately there is > > not a "standard solution" for this. There are many languages in the > > world and for some usable compound splitting algorithms exist. There are > > also attempts to create statistical universal algorithms. > > > > As you said, for English a simple sub-string search might suffice but > > for other languages it more complex. I assume that you speak German. If > > you have a document that contains the term "Verkehrsleitsystem" and your > > search query is "Verkehr leiten", it's reasonable to assume that the > > document is relevant to the search query. Unfortunately a sub-string > > search could not find the document. Other languages are even more > > difficult (a textbook on linguistics will explain this better than I > can). > > > > Even if you have such algorithm, it's not trivial to score the results > > and there are more aspects to consider to create a simple search > > algorithm. For example, in English you will also have to do some > > analysis of the phrase structure to identify open compounds. > > > > Perhaps it helps to mention the languages you are interested in and the > > application you have in mind to evaluate whether the SQLite FTS5 could > > meet your requirements. > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Some FTS5 guidance
With fts4 you could search for matching terms in an fts4aux table, then use those to construct a query against the original table. You'd have a full scan of the fts index, but you'd not have to do a full table scan of the primary data. Unfortunately if there were a large number of hits in the index scan, then it would be cheaper to just do the full table scan and skip the index scan. I don't know if there's a similar thing for fts5 at this time. This wouldn't be as efficient as something more suited to substring matches (an N-gram index, maybe?), but I haven't heard anyone talking about writing a virtual table to do that. -scott On Fri, Jan 8, 2016 at 11:54 AM, Charles Leifer wrote: > You can create a custom tokenizer as well then use the standard search > APIs. I imagine that functionality would work well in this case: > https://sqlite.org/fts5.html#section_7 > > On Thu, Jan 7, 2016 at 3:59 PM, Stadin, Benjamin < > Benjamin.Stadin at heidelberg-mobil.com> wrote: > > > One such algorithm would be a (generalized) Ukkonnen suffix tree ( > > https://en.m.wikipedia.org/wiki/Ukkonen%27s_algorithm). > > It allows you to search efficiently for substrings. > > It would be possible to do some match weigthing based on match distance > > within words. But a general solution for a database is probably not > trivial > > to implement. > > > > Ben > > > > Von meinem iPad gesendet > > > > > Am 07.01.2016 um 21:46 schrieb Matthias-Christian Ott : > > > > > >> On 2016-01-07 19:31, Mario M. Westphal wrote: > > >> I hence wonder if this problem has been tackled already and if there > is > > a > > >> "standard" solution. > > > > > > If I understand you correctly, it seems that you are looking for a > > > compound splitting or decompounding algorithm. Unfortunately there is > > > not a "standard solution" for this. There are many languages in the > > > world and for some usable compound splitting algorithms exist. There > are > > > also attempts to create statistical universal algorithms. > > > > > > As you said, for English a simple sub-string search might suffice but > > > for other languages it more complex. I assume that you speak German. If > > > you have a document that contains the term "Verkehrsleitsystem" and > your > > > search query is "Verkehr leiten", it's reasonable to assume that the > > > document is relevant to the search query. Unfortunately a sub-string > > > search could not find the document. Other languages are even more > > > difficult (a textbook on linguistics will explain this better than I > > can). > > > > > > Even if you have such algorithm, it's not trivial to score the results > > > and there are more aspects to consider to create a simple search > > > algorithm. For example, in English you will also have to do some > > > analysis of the phrase structure to identify open compounds. > > > > > > Perhaps it helps to mention the languages you are interested in and the > > > application you have in mind to evaluate whether the SQLite FTS5 could > > > meet your requirements. > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
On Jan 8, 2016, at 12:39 AM, Darren Duncan wrote: > > I interpreted your request as if current systems' error outputs at execute > time were printing out the problematic SQL statement with placeholder names > as originally prepared, and you wanted the error outputs to have the > placeholders substituted with literals for the values passed to them at > execute time interpolated into them. Yes. > one can just list the bound values separately / afterwards rather than having > to rewrite the SQL to interpolate those values. Of course, but the question is not whether a caller *can* do this, it?s whether the caller *should have to* do this.
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
On Fri, Jan 8, 2016 at 10:54 AM, R Smith wrote:
>
> I can't agree more - and to add, while I can sympathize with the point, I
> absolutely love SQLite, but the amount of projects I have made without
> SQLite far outweighs those containing it (on all platforms). I would like
> it to remain optional everywhere.
>
Acceptable. I'm not saying it should be a requirement to include it in the
language core. A simple INCLUDE or whatever relevant should be available
by the language developers that allows for a basic interface to the SQLite
library, be it relying on the external DLL/SO or get it embedded. But
having that INCLUDE or USES should be at the language side of things, not
SQLite. SQLite should not cater to every language out there, regardless of
size and use. It should be the languages that obey the laws and rules and
integration methodologies provided by SQLite. *OF COURSE* Dr Hipp and
others should listen to what the community has to say about the interfaces,
and they should also pay attention to what kinds of integrations and
methodologies of different techs are out there, and consider if it is
viable and worth while to get implemented into the core of SQLite. But
because "R" has an oddball connection methodology (Or whatever the wishlist
had), it shouldn't mean that other languages should now start working the
way "R" wants it to be done.
>
> Speaking of Delphi specifically (as the OP mentions, and which I do use),
> I have simply a unit that links the DLL, and another that maintains an
> object that does all data handling. It's the simplest solution - Sure I
> need to add the "sqlite3.dll" file to my installers and updaters, but I get
> a free upgrade by just dropping in the new DLL when it arrives - no need to
> recompile or re-setup or anything. (Likewise for iOS / Mac OSX, but Linux
> projects [freepascal / Lazarus] are more tricky in this regard - probably
> only due to my limited knowledge, I'm sure someone somewhere made stuff for
> it).
>
>
Half a mind to devil-advocate this, but, I just wanna go play games!
{chuckle}
> I'm quite willing to share any of the delphi libraries and objects if
> anyone is interested (best mail me off-list) - they can be seen in action
> if need be by simply peeking at sqlitespeed from
> http://www.sqlc.rifin.co.za
>
>
BM'd for later viewing. Got Creepers and Skeletons to kill.
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
Because this list supports many different things, not just SQLite downloaded from sqlite.org, maybe I'm off target with my interpretation of these wishlists. I'm not arguing about pros and cons of shared libraries directly. My comments were made from a tired guy who started the day early, was busy being active with family, and then finished late, so yeah, it was a bit confusing. For the record, *I* personally prefer trying to get all essential resources built directly into my final output (For SQLite, default database structures, SQLite strings, and maybe that one day, SQLite itself), that way I'm in control of what the application does, and have no reliance on a 3rd party update to a resource file that breaks my code. That is just my preference, and old school or not, I prefer working software, not software that might work after MySQL updates and breaks a resource I require when my application doesn't touch MySQL, or when a user deletes a critical file my application requires and claims they didn't do anything (Other than sort all .EXE into c:\EXE and trashed as many DLL files since they don't RUN properly -- Thank you BOFH for bringing that situation to mind). I've never had 100% success on a fully independent database driven application (SQLite or not), and that is perfectly OK. That doesn't mean I'd like to strive for that one day. The thought of my recoding SQLite C to Pascal is only a pipe dream, and only means that I can remove another external dependency not controlled by me, and it'd be kind of fun, AND I might finally understand the C code well enough to contribute, *AND* it'd be my first application that'd let me read and write to a foreign binary data structure successfully. If I'm bored, I might do it. More likely to shoot my foot off, or some jazz like that though. What I'm arguing for is that SQLite stays as is (Allowing for enhancements, of course), and any language out in the field that would LIKE to include SQLite should conform to SQLites calling conventions, at least to the basics of calling conventions for prepare, or connect, or what have you. Obviously, making BASH scripts directly support SQLite is kind of an oddball request, but if it were ever to be, it should be on the BASH language developers to conform to what SQLite provides, not ask that SQLite abides by BASHs rules and regulations. The reason for this is that SQLite should NEVER conform to everyone elses standards because everyone believes their standards are first and foremost and to hell with everyone else. There is no global standard for all language calling conventions, and some of the wishlists I've seen are asking that SQLite bend to their views, which is exactly what has me on edge. A wrapper should be used for convenience or internal standardization, not the pendulum in a lever (Otherwise you just have a stick, or a bunch of unhappy kids who can't play on the teeter-totter). A wrapper should take whatever the language provides and either enhance it, or, rename functions that makes more sense to the developers. I'm in no way arguing that the sqlite3.dll (and equivalent) should be embedded into every language compiler so it is transparent and shipped with the executable with no reliance on a shared resource. That purely is my deal, my internal wish list, and NOT something I'd ever DREAM of asking the SQLite devs to conform to. I'm arguing that any language that wants to provide access to SQLite should have some rudimentary built in commands that permit access to the shared resources, and then the Perl, "R", Pascal, Delphi, PHP, and DOT-NET developers just need to write their wrappers against what the language developers provide. Again, I'm not arguing that the DLL/SO/whatever ends up in the final build of the executable, but just that the language compiler or interpreter has the fundamental functionality to allow for simple wrappers to exist without having to go to strangers. To be honest, I've never heard of the language "R", so I don't know what it is, how "high" that high-level is, or even what its syntax is like, whether it is a compiled language or if it is JIT. So the comments of (And I'm SUPER exaggerating with this) *"I use this language called 'Ego' which is far superior than any other language, and the way SQLite interacts with my language sucks. I'd like to see SQLite allow me to use SEL instead of SELECT because I use a lot of selects in negative-endian, reverse notation, double-width-unicode strings only my language supports, so I'd be saving LOTS of space!"* is what is kind of grinding my wooden gears. On Fri, Jan 8, 2016 at 2:51 AM, Darren Duncan wrote: > Stephen, > > What you are arguing for (no shared libraries) is bad old days where one > had to recompile their programming language to add support for a DBMS, > rather than the DBMS support being a separately installable library that > one could choose to install or not or upgrade semi-independently or not, or > choose to use an
[sqlite] sqlite bug report
Bug report I downloaded sqlite 3.10.0 released in 2016-1-6, earlier I downloaded sqlite 3.9.2 and earlier version. I found below 2 bugs: bug1: Database directory which contains Simplified Chinese Character not support. bug2: Simplified Chinese Character in database table display error. bug1: Database directory which contains Simplified Chinese Character not support OS: Simplified Chinese Windows XP SP3 + 195 hotfixes sqlite version: 3.10.0 and earlier version bug description: in sqlite3.exe command line, if database directory contains Simplified Chinese Character, Simplified Chinese Character will not function. like this: suppose directory c:\test\?? exists, employee.db does not exist. // "??" is Simplified Chinese Charater. in sqlite 3.9.2 and earlier version, run below command: C:\>sqlite3.exe c:\test\??\employee.db "create table employee(name, age);" Error: unable to open database "c:\test\??\employee.db": unable to open database file in sqlite 3.10.0, run below same command: C:\>sqlite3.exe c:\test\??\employee.db "create table employee(name, age);" Error: unable to open database "c:\test\?\employee.db": unable to open database file // extra bug: "??" displayed as "?" in sqlite 3.10.0 if delete "??" in directory c:\test\??, run below command success in sqlite 3.10.0, 3.9.2 and earlier version: C:\>sqlite3.exe c:\test\employee.db "create table employee(name, age);" C:\>dir c:\test 2016-01-08 09:54 2,048 employee.db bug2: Simplified Chinese Character in database table display error. OS: Simplified Chinese Windows XP SP3 + 195 hotfixes sqlite version: 3.10.0 bug description: in sqlite 3.9.2, run below command C:\>sqlite3.exe test.db -cmd ".header on" "select * from employee" name|age ??|37 // Simplified Chinese Character displayed normal. ??|29 // Simplified Chinese Character displayed normal. Tom|8 Jerry|7 but for same database test.db, in sqlite 3.10.0, run below command C:\>sqlite3.exe test.db -cmd ".header on" "select * from employee" name|age |37 // bug: Simplified Chinese Character disappear. |29 // bug: Simplified Chinese Character disappear. Tom|8 Jerry|7 bug2 caused by sqlite 3.10.0 new enhancements to the command-line shell: Translate between MBCS and UTF8 when running in cmd.exe on Windows. In Simplified Chinese Windows XP, all Chinese Characters input in cmd.exe must be MBCS, not one of UTF-8, UTF16LE, UTF16BE. So I hope to delete codes that converts MBCS to UTF-8 and vice versa in shell.c when display field value. Maybe another better solution is to add new command line switch(ex: /mbcs) to next version of sqlite3.exe 3.10.0.
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
This is how R works too. That is the RSQLite package that gives
access to SQLite includes SQLite itself right in the package itself so
one need not separately install SQLite.
Also RSQlite uses the R DBI package which defines connections as
classes which are subclassed by the various database packages
(RSQLite, RMySQL, RPostgreSQL, etc.). Thus, in the case of RSQLite it
defines a connection subclass whose methods allow one to access
SQLite.
R also has the higher level sqldf package that lets one access R
data.frames (R data frames are like memory resident database tables)
as if they were tables in a relational database. When sqldf is run it
creates an SQLite database (or other backend that might be used but
the default is SQLite), uploads any data frames referenced in the SQL
statement, performs the SQL statements and downloads the result
destroying the database. SQLite is sufficiently fast that this is
often faster than performing the same operation in native R despite
having to upload the inputs and download the output.
For example, the following installs sqldf and its dependencies
(RSQLite, DBI) on the first line, loads them all into the current
session's workspace in the second line and then lists the first 4 rows
of the iris data frame (iris comes with R) using SQLite as the backend
and then defines a data.frame DF and performs another SQL statement:
install.packages("sqldf")
library(sqldf)
sqldf("select * from iris limit 4")
DF <- data.frame(a = 1:26, b = LETTERS)
sqldf("select * from DF where a > 10 limit 3")
On Fri, Jan 8, 2016 at 2:51 AM, Darren Duncan
wrote:
> Stephen,
>
> What you are arguing for (no shared libraries) is bad old days where one had
> to recompile their programming language to add support for a DBMS, rather
> than the DBMS support being a separately installable library that one could
> choose to install or not or upgrade semi-independently or not, or choose to
> use an alternative or not.
>
> Sure, SQLite is public domain, but why should every language bundle it into
> their core just because? There are lots of other useful libraries one could
> make the same argument for. Bundling it can make sense if the language core
> itself depends on SQLite or practically all of its users would use it, but
> that's not usually the case.
>
> I should also point out that the standard Perl interface for SQLite, the
> DBD::SQLite module, bundles the SQLite source with it, so installing that
> Perl library gives you SQLite itself, there are no DLLs or dependence on
> some system SQLite library, but Perl itself doesn't have this built-in nor
> should it.
>
> In the Perl 4 days you had to recompile Perl to make a version that can talk
> to a DBMS, eg "Oraperl", but thankfully with Perl 5 (1994 or so) we did away
> with that.
>
> -- Darren Duncan
>
>
> On 2016-01-07 5:47 PM, Stephen Chrzanowski wrote:
>>
>> I personally wish the reverse. I wish that these interpreted language
>> engines would incorporate the SQLite code directly into their own
>> existence
>> to avoid having to write wrappers to begin with, except for those wrappers
>> where their method name is "DatabaseOpen" and I prefer "OpenDatabase".
>>
>> SQLite has been around for years, and "R", PHP, Java, Perl, and all these
>> other interpreted new and old style languages have never bothered to
>> incorporate this public domain database engine within itself. It isn't
>> like the maintainers of these languages don't know it doesn't exist, and
>> if
>> they didn't, then my god they gotta get out from under that rock. Most
>> web
>> browsers use SQLite for crying out loud.
>>
>> For a few years, I've considered taking the entire amalgamation and
>> porting
>> it to Pascal (Delphi/FPC) so I have exactly zero reliance on DLLs. No
>> worries about OBJ files, no worries about dependencies, I just include a
>> unit and my app is now database aware. I know 386 assembly, and I can
>> always read up on other specifications if I needed to. My problem is that
>> gaming gets in the way.
>>
>> My 2016 wish list for SQLite is that all developers who write for, or use
>> directly or indirectly, any database engine out on the market has a safe
>> and happy 2016 and beyond.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/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] whish list for 2016
On Fri, Jan 8, 2016 at 12:14 AM, James K. Lowden wrote: > I would like to see a strict mode, too. +1 > I would also like to be able to make "strictness" a property of the > database, not the connection. > +1 similarly I'd like enforcing FKs to be per database, not per connection. > One way to do that would be to honor a special user-created table, say > "PRAGMAS", with name-value pairs that are automatically applied when > the database is opened. > Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar to sqlite_master. --DD
[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages
On 8 Jan 2016, at 12:22am, Jim Callahan wrote: > The existing SQLite APIs are correct, but hard to use in the > sense that creating an interface from an OOIL language is more involved > than just "wrapping" one by one a set of functions. What I am proposing is > a second set of APIs that when trivially wrapped for use in an OOIL > language would result in a function that makes sense to an OOIL programmer You may be interested to know that the SQLite3 interface to PHP is object oriented. Connections and prepared statements are objects. Most things you can do with them are methods of those objects. Because every language implements objects its own way, having a low-level object-oriented API for SQLite wouldn't help anyone trying to write an OO interface for their language. It would actually be harder for them to handle both an SQLite3 object and their own kind of object and 'translate' between their needs. Error-handling, for instance, would be a nightmare since you'd have to keep an errored-out SQLite object around and never quite know if you could dispose of it yet. I think things work fine as they are. Simon.
[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac
06-01-2016, Scott Perry: The SQLite built into OS X does not support cache sharing for performance reasons?, which is probably why your results are statistically identical and the OP's results are wildly different. You can verify this by checking the return value of sqlite3_enable_shared_cache; on OS X it returns SQLITE_MISUSE. - On multicore systems, memory barriers are extremely expensive; not sharing caches allows them to run lock-free Hello, to recall the timings once again: sanhua.zh's test (objective C) without shared cache mode 2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914 2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914 2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964 2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958 shared cache mode 2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480 2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449 2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768 2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169 My Python test: Timings for default mode: steps 10 connect+fetch 4.39 connect 0.0 steps 10 connect+fetch 4.52 connect 0.0 steps 10 connect+fetch 4.62 connect 0.0 steps 10 connect+fetch 4.51 connect 0.0 elapsed 5.21 Timings in shared cache mode: steps 10 connect+fetch 6.4 connect 0.0 steps 10 connect+fetch 6.17 connect 0.0 steps 10 connect+fetch 6.56 connect 0.0 steps 10 connect+fetch 6.46 connect 0.0 elapsed 6.85 At least the tests in non-shared cache mode should be comparable. And I try to explain why on my poor Mac the test is 100-200 times slower there (4.5 / 0.03 seconds). This must be the overhead of the Python wrapper. After sqlite_step the row is converted for Python. This overhead is practically neglectable but apparently not in an exagerated test (fetching rows without any further action). Further it would be interesting to know if Sanhua tested on a multicore system. Then cache sharing is indeed very expensive there (if I summarize this correctly) Thanks, E. Pasma
