[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
On 17.09.2015 20:14, Scott Hess wrote: > The problem is that there are LOCALE settings where tolower() does things C > programmers don't expect. I think tr_TR was one case, the handling of 'I' > (Google "tr_tr locale bug" and you'll see lots of people hitting the same > general problem). It isn't a problem of type safety, it's a problem that > the same inputs might have different outputs for certain library functions > when you change environment variables. I don't remember whether there were > specific problems with other ctype functions, or if I just thought it was a > good idea to be careful, once I realized the class of problem. And this check-in therefore misses the point as it does not address this LOCALE problem IMHO: http://www.sqlite.org/src/info/6713e35b8a8c997a Ralf
[sqlite] Nuget Sqlite Packages
On 17 Sep 2015, at 8:27pm, R.Smith wrote: > On 2015-09-17 07:19 PM, Jim Boyce wrote: >> DISCLAIMER "This email and its attachments may contain privileged and/or >> confidential information//... > > It might also not contain anything at all. Actually, under the laws of both the European Union and the United States of America (no idea about South Africa) use of boilerplate text for messages to people you don't have a contract with invalidates it. So use for messages which will definitely be posted to the web (as all messages to this mailing list are) automatically invalidate any force those words might have. European Union law: Reciprocity of contracts requires a party to agree to a contract to be bound by it. You can't send a message to someone which magically enters them into an agreement. US law: Model Rule 1.06(c): Confidentiality of Information "A lawyer shall make reasonable efforts to prevent the inadvertent or unauthorized disclosure of, or unauthorized access to, information relating to the representation of a client." -- no "reasonable effort" if you intentionally send the message to a public mailing list. Obligatory Disclaimer: I am not a lawyer in either jurisdiction. Simon.
[sqlite] Nuget Sqlite Packages
On 2015-09-17 07:19 PM, Jim Boyce wrote: > DISCLAIMER "This email and its attachments may contain privileged and/or > confidential information//... It might also not contain anything at all. :)
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-17 05:13 AM, Nicolas J?ger wrote: > @Ryan, >> To get you started on this road, may I ask you take a leap of faith >> and explain to us from A to Z what you would like to achieve with the >> tables and data you are asking about above - i.e don't show us the >> programming you think will solve the problem and ask us if that is >> good SQL, rather explain us the problem to be solved and allow us to >> show you the good SQL that will do it. >> >> Every next task should be clearer and easier. > Actualy, it's a personal open source project. So the code could be see > by anyone. The only thing is, the most recent part isn't yet on the > repo (last push was before the summer). But, if for some reason you > want to see the code as it is today on my computer (almost all files > were heavily modified during the summer), I can push it. > > this project is about helping me to manage all files I have on my > computer. I'm a metalhead and science nerd at the same time. > So, I have tons of music and tons of ebooks and publications... So my > project as currently two goals: > >1) store the data on disk, I should not access the files directly. > >2) using a DB to retrieve any document trough a search engine. > > (actually I want to add a third goal: I want to create something I > don't know how I will call it, but it's something like a binder. For > example if you open a binder about 'modelling demons', you can link data > of differents files even if they don't share tags or whatever (can be > in that example some texture, some .blend or pdf about modelling > humanoid...).) > > So, I'm writing some custom GTKmm widgets wich interact with the DB > (but they don't directly access any sqlite function, I'm working with > engines communication (gtkmm_engine, sqlite_engine, etc.)). > > I'm currently thinking to make that program modular. For example, the > user (usualy me), set what kind of data (trough some config file (in > my head I call these files 'collection')) should be store in the > DB. For each data will correspond a widget. > > Let's took an example, > > for ebooks: > > I want to save the filename (filename is ruled by the program, the > user has no direct way to modify it), title, some tags and a description > for each ebook (for the last three the program shows some widgets, > wich allow the user to modify the values). > > At this point, when the program read the ebook.collection, it will > create a first table EBOOK with four columns > (ID,FILENAME,TITLE,DESCRIPTION) > > for the tags, things are more complicated. I need two tables: > - TAGS with three columns (ID,NAME,COUNT) > - TAGSMAP with two columns (ID_EBOOK,ID_TAGS) (I explain the use below) > > so if I take my old example : > > (id|NAME|COUNT) > 53|black metal|3 > 55|occult rock|3 > 61|satanic smurfs|1 > 62|beer|0 > 63|pizza|0 > > if I'm seeking to get all files with the tags 'black metal' the program > check all entries in TAGSMAP with ID_TAGS = 53, > > SELECT * FROM TAGSMAP WHERE ID_TAGS = 53 would returns something like, > > (ID_EBOOK|ID_TAGS) > 3|53 > 5|53 > 9|53 > > then the ID_EBOOKs refer to the IDs of the data (title and description > in this case) for some files in EBOOK. > > now my problems come when you want to add/delete a tags. If for a file > you want (eg) to delete the tag 'black metal' you click on the button I > setup to delete that tag, the program at this time has two > informations: > > the current EBOOK.FILENAME and the TAGS.NAME of the tag. > > So I have to query the DB to get their respective IDs (ID of that > FILENAME in EBOOK and ID of that NAME in TAGS). > > Then, the program has to delete the entry in the TAGSMAP where ID_EBOOK > is the same than ID for the current file in EBOOK and where ID_TAGS > equal the ID in TAGS. > > For TAGS, the program has first to check COUNT. Before the check I have > to get the value from the DB. > >If COUNT = 1, the program has to delete the tag with NAME 'black >metal'. > >If COUNT > 1, the program has to decrement count. > > My first manner to do that, was to just store/read the data to/from the > DB. All conditions (like what I try to do for some days now...) were > executed by the program, not by queries. But the problem (wich is > not really a big issue), I do a lot of queries because of the IFs in my > program then I get one/two seconds of lags (wich may also be here > because I wrote all operation I do in the console for debug > purpose...)... > > So, as I wrote in an earlier email, since I know I can use > transaction, I try to replace all queries in one giant query send to > the DB when I want to update the data for a file (title, tags and > description). > > Maybe, I should ask if what I want to do is really possible by > SQL or if I should better let the IFs in the program better than > struggling like I did in my last emails... > > > I really hope I was clear. If you need further explanation about > something just tell it. Anyway thank you for the time you
[sqlite] I don't understand how to use NOT EXISTS
> Some initial things. > Data needs to be kept in 1NF (or 1st-Normal-Form) which is relational > theory speak for "Everything without a 1-to-1 relationship with the key > field in a table, should be in another table". Many reasons for this > (if you care to read up on some RT) but the most simple reason is: We > can make complex extractions from 1NF data tables via JOINs, sub-queries > and the like, but we cannot extract simple information (or de-complicate > it) from convoluted tables. Except for very simple cases, data in first normal form that is not also in third or boyce-codd normal form usually has anomalous update/delete behaviour. Although it may be complicated, one usually wants to normalize to at least third normal. Then you can denormalize (usually for performance reasons) and deal with the anomalies that you (re)introduce through each denormalization step manually.
[sqlite] Using the json1 extension with Python
I spent some time this week figuring out how to compile ghaering's `pysqlite` library with support for the new SQLite json extension. Finally got it working and I must say it's pretty damn exciting! Here's a blog post I wrote that covers everything from compiling sqlite with JSON to running queries from Python: http://charlesleifer.com/blog/using-the-sqlite-json-extension-with-python/ Hope you all find it helpful. Charlie
[sqlite] Handling the whole select query from an index
Nicolas, Speed of development, ease of development, maintenance, available frameworks, available Perl modules. I actually know C the best of any language and will very happily code in C, but for some things its quicker to develop it in out in Perl. Certainly for anything involving ?interesting? text manipulations Perl is far, far quicker. We use the DBI module for database integration which we use with SQLite BUT we could change that for PostgreSQL or mysql in a few hours. As an example I built a very simple restful server in Perl using Mojoliscous in around two hours this afternoon. The other two hours was coming to the conclusion that SQLite was not the bottleneck anyway in the webservice :) Now if I had to build that in C I?d still be reading the manual on sockets and programming them and trying to remember various flags. I have no doubt I could write something that worked but it would take me an awful lot longer. It would also be an awful lot bigger. A lot of Perl is very, very, very fast. People write modules in C that integrate into Perl and simple slot in. You get the coding speed of C with the ease of maintenance and development of Perl . Code speed close to C is not uncommon. Its also very mature as a language, though I dip in and out of object orientated Perl and back to traditional Perl as I see fit. There are disadvantages to Perl: 1. its single threaded so high clock speed is good or you need to break the problem down into different processes. 2. People (myself included) can write the most awful code imaginable. 3. The CPAN modules can be waaay out of date and unmaintained. 4. Memory management consists of stopping the program to free the memory. Mmmm?. 5. The syntax can be obtuse. I was a beta tester for Perl about a zillion years ago and it was a little archaic then. 6. Writing regular expressions is great but it can lead to code that looks like noise on a modem line. 7. You can end up with the same dependency hell as any other language. 8. Any other Perl coder is as old as me. However as an example of what you can get, we do a lot of natural language programming and Bayesian maths in our system. We needed a language stemmer. Writing this would have taken us weeks , if not months, all of these modules were available on CPAN. We installed them and got up and running in hours (well a few days). Try that in C or JavaScript or PHP or C++ or Java. I suspect Lisp would work though. Hope that helps, Rob > On 17 Sep 2015, at 17:30, Nicolas J?ger wrote: > > hi Rob (Willet), > > May I ask why are you using Perl instead of C/C++ ? I'm not going to start a > debate, I'm just > curious. > > > regards, > Nicolas > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling the whole select query from an index
On 2015-09-17 04:50 PM, John McKown wrote: > On Thu, Sep 17, 2015 at 9:41 AM, R.Smith wrote: > >> (11) This forum. Need help or suggestions with how to optimize your >> internal value-store? Not so easy. Need help optimizing an SQLite DB or >> query? We've all seen the very clever people here always eager to assist. >> It's an amazing and very valuable resource. (This one is also true for >> other community-supported DB systems in general) >> >> >> Cheers, >> Ryan >> >> > ?OK, I did really put my foot in it. [grin/]. I do that often enough to not > be upset by it. But it did result in the OP doing a test and realizing that > the problem was _not_ data access, but apparently elsewhere in the code. > I'm a bit of an pro-SQL person myself. Especially when doing things which > require joins & CTEs! I trust the SQL people's code a lot more than I trust > my own (I'm a sysadmin who can program, but not a professional programmer). > But it is not _always_ the "one true way". This would be more for > "unstructured" data. But, then, I need to be reading up on FTS so I could > well be wrong on this point too. Heh, to be clear, I do not think you've put a foot in it, your advice was basically "look, you don't need a DB for that" and it is true and good advice for the specific case. Apart from the fact that SQLite ended up able to achieve the same speed as another option - many of Richard's and my subsequent points were based on the fact that you "might" need something more/something else in future, for which SQL will always win. This may well not be the case for the OP, but experience as a programmer (I'm more a programmer doing occasional reluctant DB admin) has taught that always always always, tomorrow will reveal a need that nobody saw today. When this happens, every time I had based system data on SQL, I smiled, and every time I did not, I cried. Hence the clear bias :)
[sqlite] Handling the whole select query from an index
On 2015-09-17 02:22 PM, Richard Hipp wrote: > On 9/17/15, John McKown wrote: >> Well, this may be a heretical answer. > Yes, it is. > > There are a number of reasons to still use SQLite even if you are only > doing key/value lookups: > > (1) SQLite is the most widely deployed database engine in the world, > so it is more likely to already be on the system you are using than > any other database. > > (2) Those PRIMARY KEY lookups that the OP is doing are surprisingly > competitive in performance. They may even be faster than other > hash-based systems. Have you measured? Do you *know* that a hashing > system is faster, or are you just assuming? > > (3) Does that alternative key/value database engine offer > transactions? What happens if you lose power in the middle of a > write? > > (4) The next poor soul who has to maintain the program will be better > able to understand the the code if she can see the CREATE TABLE > schema, containing the obvious hints to the meanings of the various > columns inherent in their names. Data lives longer than code. It is > good to keep it in an easy-to-reuse and portable format. > > (5) If you already have a relational database at hand, the application > will be much easier to enhance when you move to version 2. > > (6) Is that hash-based key/value database you want to use > cross-platform? Can you move the database files to a big-endian > system and have it still work? You could if it were SQLite. > > I now open the floor for additional suggestions from readers for why > it is still better to use SQLite even if you currently only think you > need a key/value store (7) Let's not forget the most important bit: SQL, so today you only need a key-lookup.. but tomorrow your system gets adopted everywhere, and users start asking for more functions... maybe you need to find the 10 closest points? or you want to add a counter for how many times a point is looked up to provide stats or plots SQL will just do that with simple queries or schema additions at lightning speed. Or perhaps you just want to check how many / if any duplicate points are in the system. 1 small SQL statement for man, one giant leap for don't-have-to-program-that-stuff-ever-again. (8) 3rd Party DB admin tools - need to make changes to your DB only once? No need to add programmy bits to do it, just use a DB tool, of which there are a myriad out there, especially in SQLite's case. Perhaps grow and go really big later and upgrade to full client-server system? The same 3rd party tools can make transfer very simple from an existing DB. (9) Get the automatic advantage of future improvements in efficiency and upgrades without any need to redo any code. (10) Later perhaps port your system code to another PHP, Java, whatever... much easier to port 1 SQL instruction to the new system's DB interface than to rewrite all the lines of code that maintains your proprietary hash-value-store. (11) This forum. Need help or suggestions with how to optimize your internal value-store? Not so easy. Need help optimizing an SQLite DB or query? We've all seen the very clever people here always eager to assist. It's an amazing and very valuable resource. (This one is also true for other community-supported DB systems in general) Cheers, Ryan
[sqlite] Handling the whole select query from an index
John,
The problem was the version of SQLite included in DBD. Its way old?.
I am a sysadmin as well as developer as well as tester as well as CFO. I wear
many hats in this startup :)
Our conclusion is that we need to look at the architecture again to see if the
web services can be speeded up. There?s no point in us shaving off 0.01 ms off
SQlite if the response takes 0.1ms in the web service. 100 calls per second
isn?t good enough. We?ll look at mojolicious and see how many processes we can
pre-fork.
Rob
> On 17 Sep 2015, at 15:41, John McKown wrote:
>
> The latest PERL DBI for SQLite that I could see is at
> http://www.cpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.48.tar.gz . I
> took a look at it and it has sqlite3.c (and others) from the 3.8.10
> amalgamation in it.
>
> Just "for fun", I copied 3 files (sqlite3.c, sqlite3.h, and sqlite3ext.h)
> from my SQLite (64abb65d4df11e5b3bcc4afc8e7c18e907c6080a 2015-08-28
> 03:48:04 UTC) source. In the DBD-SQLite-1.48 source directory, I then did:
> perl Makefile.PL #create the Makefile
> make #create the SQLite.so shared library
> sudo make install # Install the new DBD for SQLite
>
> The above was on RedHat Fedora 22 x86_64. All the test ran successfully.
>
> If it were me, I'd download the latest SQLite almagamation & the above
> mentioned DBD-SQLite. Copy the 3 files I mentioned from the amalgamation
> source to the DBD-SQLite-1.48 directory, then re-install DBD-SQLite as I
> did. But, of course, this will likely need to go though whatever change
> control procedures that Rob's installation has. Hum, I guess that I assumed
> that Rob is a sysadmin on this system. So maybe he will really to push an
> update request through channels to get DBD-SQLite updated.
>
>
> On Thu, Sep 17, 2015 at 9:00 AM, Simon Slavin wrote:
>
>>
>>> On 17 Sep 2015, at 2:47pm, Rob Willett
>> wrote:
>>>
>>> 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the
>> following error
>>>
>>> DBD::SQLite::db prepare failed: malformed database schema (postcode) -
>> near ?WITHOUT"
>>>
>>> This appears to be due to mismatched SQLite version but we are running
>>>
>>> macpro:postcode rwillett$ sqlite3 --version
>>> 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
>>>
>>> The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher
>> so we?re confused as to what the problem is.
>>
>> SQLite is not 'installed in your system'. It is compiled separately into
>> each program which uses it. The version number returned by the
>> command-line tool (sqlite3) is the version of SQLite that is compiled into
>> the command-line tool.
>>
>> Your version of Perl will have a different version of SQLite compiled into
>> it. To find out which version that is, I think you can do this:
>>
>> $dbh->{sqlite_version};
>>
>> assuming $dbh is your handle to a SQLite connection.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling the whole select query from an index
John,
The problem was the version of SQLite included in DBD. Its way old?.
I am a sysadmin as well as developer as well as tester as well as CFO. I wear
many hats in this startup :)
Our conclusion is that we need to look at the architecture again to see if the
web services can be speeded up. There?s no point in us shaving off 0.01 ms off
SQlite if the response takes 0.1ms in the web service. 100 calls per second
isn?t good enough. We?ll look at mojolicious and see how many processes we can
pre-fork.
Rob
> On 17 Sep 2015, at 15:41, John McKown wrote:
>
> The latest PERL DBI for SQLite that I could see is at
> http://www.cpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.48.tar.gz . I
> took a look at it and it has sqlite3.c (and others) from the 3.8.10
> amalgamation in it.
>
> Just "for fun", I copied 3 files (sqlite3.c, sqlite3.h, and sqlite3ext.h)
> from my SQLite (64abb65d4df11e5b3bcc4afc8e7c18e907c6080a 2015-08-28
> 03:48:04 UTC) source. In the DBD-SQLite-1.48 source directory, I then did:
> perl Makefile.PL #create the Makefile
> make #create the SQLite.so shared library
> sudo make install # Install the new DBD for SQLite
>
> The above was on RedHat Fedora 22 x86_64. All the test ran successfully.
>
> If it were me, I'd download the latest SQLite almagamation & the above
> mentioned DBD-SQLite. Copy the 3 files I mentioned from the amalgamation
> source to the DBD-SQLite-1.48 directory, then re-install DBD-SQLite as I
> did. But, of course, this will likely need to go though whatever change
> control procedures that Rob's installation has. Hum, I guess that I assumed
> that Rob is a sysadmin on this system. So maybe he will really to push an
> update request through channels to get DBD-SQLite updated.
>
>
> On Thu, Sep 17, 2015 at 9:00 AM, Simon Slavin wrote:
>
>>
>>> On 17 Sep 2015, at 2:47pm, Rob Willett
>> wrote:
>>>
>>> 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the
>> following error
>>>
>>> DBD::SQLite::db prepare failed: malformed database schema (postcode) -
>> near ?WITHOUT"
>>>
>>> This appears to be due to mismatched SQLite version but we are running
>>>
>>> macpro:postcode rwillett$ sqlite3 --version
>>> 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
>>>
>>> The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher
>> so we?re confused as to what the problem is.
>>
>> SQLite is not 'installed in your system'. It is compiled separately into
>> each program which uses it. The version number returned by the
>> command-line tool (sqlite3) is the version of SQLite that is compiled into
>> the command-line tool.
>>
>> Your version of Perl will have a different version of SQLite compiled into
>> it. To find out which version that is, I think you can do this:
>>
>> $dbh->{sqlite_version};
>>
>> assuming $dbh is your handle to a SQLite connection.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling the whole select query from an index
Simon,
We understand how SQlite is compiled into programs. We weren?t sure if the
issues were Navicat which we sometimes use for a front end or the Perl DB
stuff. My money was on Navicat being the problem. I was wrong, it was the DB
version.
The sqlite version is 3.7.17 on Ubuntu 14.04 server.
Rob
> On 17 Sep 2015, at 15:00, Simon Slavin wrote:
>
>
>> On 17 Sep 2015, at 2:47pm, Rob Willett
>> wrote:
>>
>> 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the following
>> error
>>
>> DBD::SQLite::db prepare failed: malformed database schema (postcode) - near
>> ?WITHOUT"
>>
>> This appears to be due to mismatched SQLite version but we are running
>>
>> macpro:postcode rwillett$ sqlite3 --version
>> 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
>>
>> The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher so
>> we?re confused as to what the problem is.
>
> SQLite is not 'installed in your system'. It is compiled separately into
> each program which uses it. The version number returned by the command-line
> tool (sqlite3) is the version of SQLite that is compiled into the
> command-line tool.
>
> Your version of Perl will have a different version of SQLite compiled into
> it. To find out which version that is, I think you can do this:
>
> $dbh->{sqlite_version};
>
> assuming $dbh is your handle to a SQLite connection.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nuget Sqlite Packages
On Thu, Sep 17, 2015 at 3:18 PM, Simon Slavin wrote: > > On 17 Sep 2015, at 8:27pm, R.Smith wrote: > > > On 2015-09-17 07:19 PM, Jim Boyce wrote: > >> DISCLAIMER "This email and its attachments may contain privileged > and/or confidential information//... > > > > It might also not contain anything at all. > > Actually, under the laws of both the European Union and the United States > of America (no idea about South Africa) use of boilerplate text for > messages to people you don't have a contract with invalidates it. So use > for messages which will definitely be posted to the web (as all messages to > this mailing list are) automatically invalidate any force those words might > have. > > European Union law: Reciprocity of contracts requires a party to agree to > a contract to be bound by it. You can't send a message to someone which > magically enters them into an agreement. > > US law: Model Rule 1.06(c): Confidentiality of Information > "A lawyer shall make reasonable efforts to prevent the inadvertent or > unauthorized disclosure of, or unauthorized access to, information relating > to the representation of a client." -- no "reasonable effort" if you > intentionally send the message to a public mailing list. > > Obligatory Disclaimer: I am not a lawyer in either jurisdiction. > I am not either, but it is so amazingly obvious that you are not bound by a contract that you are not a party to I can't imagine why they keep including it. I mean, I understand why they do it. String legal words together and attempt to intimidate people into bowing to your will. In reality they'd have a hard time enforcing it. If I ever received such a thing with any value attached, I might be tempted into contacting them and offering to be a party to their contract in exchange for appropriate compensation. :) It does not help that this boilerplate code is automatically added to every message through their corporation's mail servers, so it shows up whether or not there is sensitive information to be protected. That being said, RSmith's message was amusing. :) -- Scott Robison
[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
2015-08-26 19:03 GMT+02:00 Ralf Junker :
> ext/misc/json1.c uses the following functions from the C library:
>
> isalnum(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=564
> isspace(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=635
> isdigit(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=829
> Shouldn't json1.c avoid them for the same reasons?
Simpler is: cast the argument to (unsigned char), that
has the same effect (but is more efficient).
Proposed patch below.
Thanks!
Regards,
Jan Nijtmans
==
--- ext/misc/json1.c
+++ ext/misc/json1.c
@@ -583,18 +583,18 @@
char c;
u32 j;
int iThis;
int x;
JsonNode *pNode;
- while( isspace(pParse->zJson[i]) ){ i++; }
+ while( isspace((unsigned char)pParse->zJson[i]) ){ i++; }
if( (c = pParse->zJson[i])==0 ) return 0;
if( c=='{' ){
/* Parse object */
iThis = jsonParseAddNode(pParse, JSON_OBJECT, 0, 0);
if( iThis<0 ) return -1;
for(j=i+1;;j++){
- while( isspace(pParse->zJson[j]) ){ j++; }
+ while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
x = jsonParseValue(pParse, j);
if( x<0 ){
if( x==(-2) && pParse->nNode==(u32)iThis+1 ) return j+1;
return -1;
}
@@ -601,17 +601,17 @@
if( pParse->oom ) return -1;
pNode = >aNode[pParse->nNode-1];
if( pNode->eType!=JSON_STRING ) return -1;
pNode->jnFlags |= JNODE_LABEL;
j = x;
- while( isspace(pParse->zJson[j]) ){ j++; }
+ while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
if( pParse->zJson[j]!=':' ) return -1;
j++;
x = jsonParseValue(pParse, j);
if( x<0 ) return -1;
j = x;
- while( isspace(pParse->zJson[j]) ){ j++; }
+ while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
c = pParse->zJson[j];
if( c==',' ) continue;
if( c!='}' ) return -1;
break;
}
@@ -620,18 +620,18 @@
}else if( c=='[' ){
/* Parse array */
iThis = jsonParseAddNode(pParse, JSON_ARRAY, 0, 0);
if( iThis<0 ) return -1;
for(j=i+1;;j++){
- while( isspace(pParse->zJson[j]) ){ j++; }
+ while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
x = jsonParseValue(pParse, j);
if( x<0 ){
if( x==(-3) && pParse->nNode==(u32)iThis+1 ) return j+1;
return -1;
}
j = x;
- while( isspace(pParse->zJson[j]) ){ j++; }
+ while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
c = pParse->zJson[j];
if( c==',' ) continue;
if( c!=']' ) return -1;
break;
}
@@ -656,21 +656,21 @@
jsonParseAddNode(pParse, JSON_STRING, j+1-i, >zJson[i]);
if( !pParse->oom ) pParse->aNode[pParse->nNode-1].jnFlags = jnFlags;
return j+1;
}else if( c=='n'
&& strncmp(pParse->zJson+i,"null",4)==0
- && !isalnum(pParse->zJson[i+4]) ){
+ && !isalnum((unsigned char)pParse->zJson[i+4]) ){
jsonParseAddNode(pParse, JSON_NULL, 0, 0);
return i+4;
}else if( c=='t'
&& strncmp(pParse->zJson+i,"true",4)==0
- && !isalnum(pParse->zJson[i+4]) ){
+ && !isalnum((unsigned char)pParse->zJson[i+4]) ){
jsonParseAddNode(pParse, JSON_TRUE, 0, 0);
return i+4;
}else if( c=='f'
&& strncmp(pParse->zJson+i,"false",5)==0
- && !isalnum(pParse->zJson[i+5]) ){
+ && !isalnum((unsigned char)pParse->zJson[i+5]) ){
jsonParseAddNode(pParse, JSON_FALSE, 0, 0);
return i+5;
}else if( c=='-' || (c>='0' && c<='9') ){
/* Parse number */
u8 seenDP = 0;
@@ -729,11 +729,11 @@
if( zJson==0 ) return 1;
pParse->zJson = zJson;
i = jsonParseValue(pParse, 0);
if( pParse->oom ) i = -1;
if( i>0 ){
-while( isspace(zJson[i]) ) i++;
+while( isspace((unsigned char)zJson[i]) ) i++;
if( zJson[i] ) i = -1;
}
if( i<=0 ){
if( pCtx!=0 ){
if( pParse->oom ){
@@ -860,15 +860,15 @@
pRoot->jnFlags |= JNODE_APPEND;
pParse->aNode[iLabel].jnFlags |= JNODE_RAW;
}
return pNode;
}
- }else if( zPath[0]=='[' && isdigit(zPath[1]) ){
+ }else if( zPath[0]=='[' && isdigit((unsigned char)zPath[1]) ){
if( pRoot->eType!=JSON_ARRAY ) return 0;
i = 0;
zPath++;
-while( isdigit(zPath[0]) ){
+while( isdigit((unsigned char)zPath[0]) ){
i = i*10 + zPath[0] - '0';
zPath++;
}
if( zPath[0]!=']' ){
*pzErr = zPath;
[sqlite] Handling the whole select query from an index
> On 17 Sep 2015, at 2:47pm, Rob Willett
> wrote:
>
> 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the following
> error
>
> DBD::SQLite::db prepare failed: malformed database schema (postcode) - near
> ?WITHOUT"
>
> This appears to be due to mismatched SQLite version but we are running
>
> macpro:postcode rwillett$ sqlite3 --version
> 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
>
> The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher so
> we?re confused as to what the problem is.
SQLite is not 'installed in your system'. It is compiled separately into each
program which uses it. The version number returned by the command-line tool
(sqlite3) is the version of SQLite that is compiled into the command-line tool.
Your version of Perl will have a different version of SQLite compiled into it.
To find out which version that is, I think you can do this:
$dbh->{sqlite_version};
assuming $dbh is your handle to a SQLite connection.
Simon.
[sqlite] Handling the whole select query from an index
John, Richard, A quick test shows the following: 1. Creating the database ?normally? without using WITHOUT ROWID. This has 1703538 rows. Doing 1,000 randomish accesses via the web service takes 10 secs. 2. Using a perl assoc array and doing the same access via a web service takes 10 secs. This also has 1703538 rows. 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the following error DBD::SQLite::db prepare failed: malformed database schema (postcode) - near ?WITHOUT" This appears to be due to mismatched SQLite version but we are running macpro:postcode rwillett$ sqlite3 --version 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher so we?re confused as to what the problem is. We?ve never actually had problem with SQLite and Perl DBI so we need to look into it a little more. 4. We then ran the same test using nothing more than select statements directly into sqlite3 e.g. select * from postcode where id = 'BT635SW'; select * from postcode where id = 'BT350PD'; select * from postcode where id = 'BT667TG'; select * from postcode where id = 'BT750PE'; select * from postcode where id = 'BT621AE'; select * from postcode where id = 'BT623PJ'; select * from postcode where id = 'BT670BX'; select * from postcode where id = 'BT623EG'; select * from postcode where id = 'BT670DS'; select * from postcode where id = 'BT655BU?; ?? We ran 1,000 tests in real0m0.025s user0m0.013s sys 0m0.013s So the ?direct? connection runs approx 400x time quicker. Now this direct connection doesn?t have to open and close the database each time as ours currently does and doesn?t have the overhead of the web service around it. So its not a realistic comparison, but it does show how quick sqlite can be :) In conclusion 1. I can optimise the actual SQL as much as Iike, the bottleneck isn?t SQLite at the moment. 2. I need to work out what is happening using WITHOUT ROWID. We?ll dig further but suspect that we are now optimising the wrong thing in our system :) Rob If we ignore the WITHOUT ROWID issue, then > On 17 Sep 2015, at 14:21, John McKown wrote: > > On Thu, Sep 17, 2015 at 7:21 AM, Rob Willett > wrote: > >> John, >> >> Thanks for this. >> >> The base OS is Linux on a a 64bit server, the wrapper will be Perl running >> as a web service under Mojolicious. It will eventually be presented as a >> web service. >> >> We could simply load up the entire database into Perl into an Assoc array >> and search on that. >> > > ?This is probably would I would do, in this particular case.? > > > >> >> Richard, >> >> WITHOUT ROWID was what I was looking for. Thanks > > > ?I'm going to have to review this option as well. I'm not familiar with > what it accomplishes, performance wise.? > > > >> . >> >> All, >> >> What we?ll do is write a quick Perl script to check the speed of an assoc >> array in Perl vs SQLite. It might be instructive to see the difference. >> > > ?Yes, it would. Please post your results. I'm really curious about it.? > > > >> >> Rob. >> >> > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] user defined function returning a result set
>
> To do this, you'd have to create a virtual table that actually gets
> instantiated per CSV file:
>
> CREATE VIRTUAL TABLE temp.file1 USING
> csvFileReader('/path/to/my/file.csv');
> SELECT * FROM file1;
> DROP TABLE file1;
>
> In the above, the xCreate method of the virtual table implementation
> could peek at the CSV file to see how many columns it has before
> declaring how many rows it intends to return. Which is what you need.
> --
> D. Richard Hipp
> drh at sqlite.org
>
>
Excellent thank you very much, I could find a example and I will take it
from there.
Thanks again :-)
[sqlite] Handling the whole select query from an index
Simon, Fair point and agreed. We?ll update it. Rob > On 17 Sep 2015, at 13:56, Simon Slavin wrote: > > > On 17 Sep 2015, at 12:58pm, Rob Willett > wrote: > >> CREATE TABLE "postcode" ( >> "postcode" text NOT NULL, > > As an incidental tip not related to your question, I would recommend that you > don't have a column name which is the same as a table name. Call the table > 'locations' instead or something. > > It's not illegal in SQL, but it can be confusing if you come back to it in a > few years. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling the whole select query from an index
On 17 Sep 2015, at 12:58pm, Rob Willett wrote: > CREATE TABLE "postcode" ( >"postcode" text NOT NULL, As an incidental tip not related to your question, I would recommend that you don't have a column name which is the same as a table name. Call the table 'locations' instead or something. It's not illegal in SQL, but it can be confusing if you come back to it in a few years. Simon.
[sqlite] Handling the whole select query from an index
On 17 Sep 2015, at 1:13pm, Richard Hipp wrote: > At the end of the CREATE TABLE statement add keywords: "WITHOUT > ROWID". Like this: > > CREATE TABLE postcode( > postcode TEXT, > long TEXT, > lat TEXT > ) WITHOUT ROWID; The documentation suggests that you need to explicitly declare a PRIMARY KEY, so you would need something more like CREATE TABLE postcode( postcode TEXT PRIMARY KEY, long TEXT, lat TEXT ) WITHOUT ROWID; but perhaps I misunderstood. Simon.
[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
On Thu, Sep 17, 2015 at 1:24 PM, Ralf Junker wrote: > On 17.09.2015 20:14, Scott Hess wrote: > >> The problem is that there are LOCALE settings where tolower() does things >> C >> programmers don't expect. I think tr_TR was one case, the handling of 'I' >> (Google "tr_tr locale bug" and you'll see lots of people hitting the same >> general problem). It isn't a problem of type safety, it's a problem that >> the same inputs might have different outputs for certain library functions >> when you change environment variables. I don't remember whether there >> were >> specific problems with other ctype functions, or if I just thought it was >> a >> good idea to be careful, once I realized the class of problem. >> > > And this check-in therefore misses the point as it does not address this > LOCALE problem IMHO: > > http://www.sqlite.org/src/info/6713e35b8a8c997a Hmm. Well, it might miss _a_ point, while solidly landing some other point :-). Current fts3 seems to spread this code differently, under fts2.c it was like: https://code.google.com/p/chromium/codesearch#chromium/src/third_party/sqlite/src/ext/fts2/fts2.c=336 where it's not a wrapper around the library, instead it was a direct implementation of the functions which only acted on 7-bit values in the ASCII set. I think these should really be in terms of sqlite3UpperToLower and sqlite3CtypeMap. That might be an issue to expose to an extension sensibly. -scott
[sqlite] Handling the whole select query from an index
John,
Thanks for this.
The base OS is Linux on a a 64bit server, the wrapper will be Perl running as a
web service under Mojolicious. It will eventually be presented as a web service.
We could simply load up the entire database into Perl into an Assoc array and
search on that.
Richard,
WITHOUT ROWID was what I was looking for. Thanks.
All,
What we?ll do is write a quick Perl script to check the speed of an assoc array
in Perl vs SQLite. It might be instructive to see the difference.
Rob.
> On 17 Sep 2015, at 13:12, John McKown wrote:
>
> Well, this may be a heretical answer. Given what you have said, I wouldn't
> even try to use SQLite. Well, at least not directly. I would use a "hash
> table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
> is the _only_ option. What would likely work better is a "hash index". You
> might get some more concrete answers if you were to post the OS and
> implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
> using C++. Also, do you an SQL data base? Perhaps something else would be
> better, if you're not really doing relational queries. But I don't know
> what, given that I don't know your system environment.
>
> On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett
> wrote:
>
>> Hi,
>>
>> There was a topic on here a few weeks ago which I cannot remember and
>> cannot find in the mess that is my e-mail system and after spending the
>> last hours search the SQLite archives I still can?t find it so will ask
>> here if anybody can remember or help. I can?t even format the question for
>> Google to search on :(
>>
>> The specific question I have is about trying to provide the fastest
>> response possible to a select query. I recall that the e-mail talked about
>> using an index to satisfy the query and therefore never having to go out to
>> get the rest of the data from the table, so it was a lot quicker. Is there
>> anything that I need to do specially to make this happen. e.g. if I put all
>> the fields of the table in the index BUT I really only search on the
>> primary key
>>
>> The reason for this I want to look up UK postcodes (Zip codes to our
>> American brethren) and get their longitude and latitude. A UK postcode
>> identifies a number of houses or commercial buildings. Depending on the
>> area it can be just one building (a big one) or if you are in the country
>> it can be quite a big area. If you sent a letter just to a postcode with no
>> other identifier it probably wouldn?t get delivered, but putting a name on
>> it or a building number, there?s a very good chance the post(wo)?man will
>> deliver it.
>>
>> The CSV file looks like this
>>
>> id,postcode,latitude,longitude
>> 1,AB101XG,57.14416516000,-2.11484776800
>> 2,AB106RN,57.13787976000,-2.12148668800
>> 3,AB107JB,57.12427377000,-2.12718964400
>> 4,AB115QN,57.14270109000,-2.09301461900
>> 5,AB116UL,57.13754663000,-2.11269588600
>> ?.
>> Couple of million more lines
>>
>> The entire database schema looks like this. I know its complicated but
>> bear with me :)
>>
>> CREATE TABLE "postcode" (
>> "postcode" text NOT NULL,
>> "long" TEXT NOT NULL,
>> "lat" TEXT NOT NULL,
>>PRIMARY KEY("postcode")
>> );
>>
>> The only query that will ever run will be
>>
>> select long,lat from postcode where postcode = ??
>>
>> Note I drop off the id field (column 0 in the CSV file) as its of no
>> interest to me. I also store the long and lat as strings as I don?t want
>> any number formatting changes at all. Rounding on a GPS number could cause
>> the wrong location to be used.
>>
>> The database will do nothing but return long and lat based on doing a
>> postcode lookup. There will never be any updates or changes. If there are,
>> the whole database will be regenerated.
>>
>> I need this to be as fast as possible and if necessary I?ll put it all in
>> RAM. The database is currently 120MB so it would easily fit in RAM. As it
>> never changes (perhaps 4 times per year), it could stay there.
>>
>> Is there anything else from the database schema side that would make
>> things quicker? e.g. If I created an index with postcode, long, lat in,
>> would that be quicker? or if i changed the long, lat to real (though I?m
>> reluctant to do so), would that make a lot of difference?
>>
>> Any suggestions gratefully received and apologies for not being able to
>> find it in the archives.
>>
>> Thanks,
>>
>> Rob
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
>
[sqlite] Nuget Sqlite Packages
AVISO LEGAL "As informa??es existentes nesta mensagem e nos arquivos anexados s?o para uso restrito. A utiliza??o, divulga??o, c?pia ou distribui??o dessa mensagem por qualquer pessoa diferente do destinat?rio ? proibida. Se essa mensagem foi recebida por engano, favor exclu?-la e informar ao remetente pelo endere?o eletr?nico acima." ***DISCLAIMER "This email and its attachments may contain privileged and/or confidential information. Use, disclosure, copying or distribution of this message by anyone other than the intended recipient is strictly prohibited. If you have received this email in error, please notify the sender by reply email and destroy all copies of this message."
[sqlite] Suggestion: Regularize output of setting pragmas.
Often, PRAGMA are documented like mmap_size, like: > Query or change the maximum number of bytes that are set aside > for memory-mapped I/O on a single database. The first > form (without an argument) queries the current limit. The > second form (with a numeric argument) sets the limit for the > specified database, or for all databases if the optional > database name is omitted. I notice that mmap_size _also_ returns the set value, which is useful to query since it could be constrained by compile-time options or sqlite3_config settings. Like: sqlite> pragma mmap_size = 3221225472; -- 3GB 2147418112 -- 2GB - 64KB Sometimes pragma don't do this: sqlite> pragma auto_vacuum = 1; sqlite> pragma auto_vacuum; 1 Might it be useful to have a general policy that all PRAGMA calls return a result consistent with the first form, even if an argument is passed? As things currently stand, you really shouldn't depend on behavior like mmap_size's current implementation to do set-and-query, but you also can't write code as if setting pragma do not return results. -scott
[sqlite] Handling the whole select query from an index
Hi,
There was a topic on here a few weeks ago which I cannot remember and cannot
find in the mess that is my e-mail system and after spending the last hours
search the SQLite archives I still can?t find it so will ask here if anybody
can remember or help. I can?t even format the question for Google to search on
:(
The specific question I have is about trying to provide the fastest response
possible to a select query. I recall that the e-mail talked about using an
index to satisfy the query and therefore never having to go out to get the rest
of the data from the table, so it was a lot quicker. Is there anything that I
need to do specially to make this happen. e.g. if I put all the fields of the
table in the index BUT I really only search on the primary key
The reason for this I want to look up UK postcodes (Zip codes to our American
brethren) and get their longitude and latitude. A UK postcode identifies a
number of houses or commercial buildings. Depending on the area it can be just
one building (a big one) or if you are in the country it can be quite a big
area. If you sent a letter just to a postcode with no other identifier it
probably wouldn?t get delivered, but putting a name on it or a building number,
there?s a very good chance the post(wo)?man will deliver it.
The CSV file looks like this
id,postcode,latitude,longitude
1,AB101XG,57.14416516000,-2.11484776800
2,AB106RN,57.13787976000,-2.12148668800
3,AB107JB,57.12427377000,-2.12718964400
4,AB115QN,57.14270109000,-2.09301461900
5,AB116UL,57.13754663000,-2.11269588600
?.
Couple of million more lines
The entire database schema looks like this. I know its complicated but bear
with me :)
CREATE TABLE "postcode" (
"postcode" text NOT NULL,
"long" TEXT NOT NULL,
"lat" TEXT NOT NULL,
PRIMARY KEY("postcode")
);
The only query that will ever run will be
select long,lat from postcode where postcode = ??
Note I drop off the id field (column 0 in the CSV file) as its of no interest
to me. I also store the long and lat as strings as I don?t want any number
formatting changes at all. Rounding on a GPS number could cause the wrong
location to be used.
The database will do nothing but return long and lat based on doing a postcode
lookup. There will never be any updates or changes. If there are, the whole
database will be regenerated.
I need this to be as fast as possible and if necessary I?ll put it all in RAM.
The database is currently 120MB so it would easily fit in RAM. As it never
changes (perhaps 4 times per year), it could stay there.
Is there anything else from the database schema side that would make things
quicker? e.g. If I created an index with postcode, long, lat in, would that be
quicker? or if i changed the long, lat to real (though I?m reluctant to do so),
would that make a lot of difference?
Any suggestions gratefully received and apologies for not being able to find it
in the archives.
Thanks,
Rob
[sqlite] Handling the whole select query from an index
hi Rob (Willet), May I ask why are you using Perl instead of C/C++ ? I'm not going to start a debate, I'm just curious. regards, Nicolas
[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
The problem is that there are LOCALE settings where tolower() does things C
programmers don't expect. I think tr_TR was one case, the handling of 'I'
(Google "tr_tr locale bug" and you'll see lots of people hitting the same
general problem). It isn't a problem of type safety, it's a problem that
the same inputs might have different outputs for certain library functions
when you change environment variables. I don't remember whether there were
specific problems with other ctype functions, or if I just thought it was a
good idea to be careful, once I realized the class of problem.
[My run-in with this issue was in development of fts2.c/fts3.c. I'm sure
the same general series of events leads to similar local implementations in
other projects :-).]
-scott
On Thu, Sep 17, 2015 at 7:03 AM, Jan Nijtmans
wrote:
> 2015-08-26 19:03 GMT+02:00 Ralf Junker :
> > ext/misc/json1.c uses the following functions from the C library:
> >
> > isalnum(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=564
> > isspace(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=635
> > isdigit(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=829
>
> > Shouldn't json1.c avoid them for the same reasons?
>
> Simpler is: cast the argument to (unsigned char), that
> has the same effect (but is more efficient).
>
> Proposed patch below.
>
> Thanks!
>
> Regards,
> Jan Nijtmans
> ==
> --- ext/misc/json1.c
> +++ ext/misc/json1.c
> @@ -583,18 +583,18 @@
>char c;
>u32 j;
>int iThis;
>int x;
>JsonNode *pNode;
> - while( isspace(pParse->zJson[i]) ){ i++; }
> + while( isspace((unsigned char)pParse->zJson[i]) ){ i++; }
>if( (c = pParse->zJson[i])==0 ) return 0;
>if( c=='{' ){
> /* Parse object */
> iThis = jsonParseAddNode(pParse, JSON_OBJECT, 0, 0);
> if( iThis<0 ) return -1;
> for(j=i+1;;j++){
> - while( isspace(pParse->zJson[j]) ){ j++; }
> + while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
>x = jsonParseValue(pParse, j);
>if( x<0 ){
> if( x==(-2) && pParse->nNode==(u32)iThis+1 ) return j+1;
> return -1;
>}
> @@ -601,17 +601,17 @@
>if( pParse->oom ) return -1;
>pNode = >aNode[pParse->nNode-1];
>if( pNode->eType!=JSON_STRING ) return -1;
>pNode->jnFlags |= JNODE_LABEL;
>j = x;
> - while( isspace(pParse->zJson[j]) ){ j++; }
> + while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
>if( pParse->zJson[j]!=':' ) return -1;
>j++;
>x = jsonParseValue(pParse, j);
>if( x<0 ) return -1;
>j = x;
> - while( isspace(pParse->zJson[j]) ){ j++; }
> + while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
>c = pParse->zJson[j];
>if( c==',' ) continue;
>if( c!='}' ) return -1;
>break;
> }
> @@ -620,18 +620,18 @@
>}else if( c=='[' ){
> /* Parse array */
> iThis = jsonParseAddNode(pParse, JSON_ARRAY, 0, 0);
> if( iThis<0 ) return -1;
> for(j=i+1;;j++){
> - while( isspace(pParse->zJson[j]) ){ j++; }
> + while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
>x = jsonParseValue(pParse, j);
>if( x<0 ){
> if( x==(-3) && pParse->nNode==(u32)iThis+1 ) return j+1;
> return -1;
>}
>j = x;
> - while( isspace(pParse->zJson[j]) ){ j++; }
> + while( isspace((unsigned char)pParse->zJson[j]) ){ j++; }
>c = pParse->zJson[j];
>if( c==',' ) continue;
>if( c!=']' ) return -1;
>break;
> }
> @@ -656,21 +656,21 @@
> jsonParseAddNode(pParse, JSON_STRING, j+1-i, >zJson[i]);
> if( !pParse->oom ) pParse->aNode[pParse->nNode-1].jnFlags = jnFlags;
> return j+1;
>}else if( c=='n'
> && strncmp(pParse->zJson+i,"null",4)==0
> - && !isalnum(pParse->zJson[i+4]) ){
> + && !isalnum((unsigned char)pParse->zJson[i+4]) ){
> jsonParseAddNode(pParse, JSON_NULL, 0, 0);
> return i+4;
>}else if( c=='t'
> && strncmp(pParse->zJson+i,"true",4)==0
> - && !isalnum(pParse->zJson[i+4]) ){
> + && !isalnum((unsigned char)pParse->zJson[i+4]) ){
> jsonParseAddNode(pParse, JSON_TRUE, 0, 0);
> return i+4;
>}else if( c=='f'
> && strncmp(pParse->zJson+i,"false",5)==0
> - && !isalnum(pParse->zJson[i+5]) ){
> + && !isalnum((unsigned char)pParse->zJson[i+5]) ){
> jsonParseAddNode(pParse, JSON_FALSE, 0, 0);
> return i+5;
>}else if( c=='-' || (c>='0' && c<='9') ){
> /* Parse number */
> u8 seenDP = 0;
> @@ -729,11 +729,11 @@
>if( zJson==0 ) return 1;
>pParse->zJson = zJson;
>i = jsonParseValue(pParse, 0);
>if( pParse->oom ) i = -1;
>if( i>0 ){
> -while( isspace(zJson[i]) ) i++;
> +while( isspace((unsigned char)zJson[i]) ) i++;
> if( zJson[i] ) i = -1;
>}
>if( i<=0 ){
>
[sqlite] Handling the whole select query from an index
On Thu, Sep 17, 2015 at 9:41 AM, R.Smith wrote: > > > On 2015-09-17 02:22 PM, Richard Hipp wrote: > >> On 9/17/15, John McKown wrote: >> >>> Well, this may be a heretical answer. >>> >> Yes, it is. >> >> ?? ?? > (11) This forum. Need help or suggestions with how to optimize your > internal value-store? Not so easy. Need help optimizing an SQLite DB or > query? We've all seen the very clever people here always eager to assist. > It's an amazing and very valuable resource. (This one is also true for > other community-supported DB systems in general) > > > Cheers, > Ryan > > ?OK, I did really put my foot in it. [grin/]. I do that often enough to not be upset by it. But it did result in the OP doing a test and realizing that the problem was _not_ data access, but apparently elsewhere in the code. I'm a bit of an pro-SQL person myself. Especially when doing things which require joins & CTEs! I trust the SQL people's code a lot more than I trust my own (I'm a sysadmin who can program, but not a professional programmer). But it is not _always_ the "one true way". This would be more for "unstructured" data. But, then, I need to be reading up on FTS so I could well be wrong on this point too. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Handling the whole select query from an index
The latest PERL DBI for SQLite that I could see is at
http://www.cpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.48.tar.gz . I
took a look at it and it has sqlite3.c (and others) from the 3.8.10
amalgamation in it.
Just "for fun", I copied 3 files (sqlite3.c, sqlite3.h, and sqlite3ext.h)
from my SQLite (64abb65d4df11e5b3bcc4afc8e7c18e907c6080a 2015-08-28
03:48:04 UTC) source. In the DBD-SQLite-1.48 source directory, I then did:
perl Makefile.PL #create the Makefile
make #create the SQLite.so shared library
sudo make install # Install the new DBD for SQLite
The above was on RedHat Fedora 22 x86_64. All the test ran successfully.
If it were me, I'd download the latest SQLite almagamation & the above
mentioned DBD-SQLite. Copy the 3 files I mentioned from the amalgamation
source to the DBD-SQLite-1.48 directory, then re-install DBD-SQLite as I
did. But, of course, this will likely need to go though whatever change
control procedures that Rob's installation has. Hum, I guess that I assumed
that Rob is a sysadmin on this system. So maybe he will really to push an
update request through channels to get DBD-SQLite updated.
On Thu, Sep 17, 2015 at 9:00 AM, Simon Slavin wrote:
>
> > On 17 Sep 2015, at 2:47pm, Rob Willett
> wrote:
> >
> > 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the
> following error
> >
> > DBD::SQLite::db prepare failed: malformed database schema (postcode) -
> near ?WITHOUT"
> >
> > This appears to be due to mismatched SQLite version but we are running
> >
> > macpro:postcode rwillett$ sqlite3 --version
> > 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace
> >
> > The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher
> so we?re confused as to what the problem is.
>
> SQLite is not 'installed in your system'. It is compiled separately into
> each program which uses it. The version number returned by the
> command-line tool (sqlite3) is the version of SQLite that is compiled into
> the command-line tool.
>
> Your version of Perl will have a different version of SQLite compiled into
> it. To find out which version that is, I think you can do this:
>
> $dbh->{sqlite_version};
>
> assuming $dbh is your handle to a SQLite connection.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] Handling the whole select query from an index
On 9/17/15, John McKown wrote:
> Well, this may be a heretical answer.
Yes, it is.
There are a number of reasons to still use SQLite even if you are only
doing key/value lookups:
(1) SQLite is the most widely deployed database engine in the world,
so it is more likely to already be on the system you are using than
any other database.
(2) Those PRIMARY KEY lookups that the OP is doing are surprisingly
competitive in performance. They may even be faster than other
hash-based systems. Have you measured? Do you *know* that a hashing
system is faster, or are you just assuming?
(3) Does that alternative key/value database engine offer
transactions? What happens if you lose power in the middle of a
write?
(4) The next poor soul who has to maintain the program will be better
able to understand the the code if she can see the CREATE TABLE
schema, containing the obvious hints to the meanings of the various
columns inherent in their names. Data lives longer than code. It is
good to keep it in an easy-to-reuse and portable format.
(5) If you already have a relational database at hand, the application
will be much easier to enhance when you move to version 2.
(6) Is that hash-based key/value database you want to use
cross-platform? Can you move the database files to a big-endian
system and have it still work? You could if it were SQLite.
I now open the floor for additional suggestions from readers for why
it is still better to use SQLite even if you currently only think you
need a key/value store
> Given what you have said, I wouldn't
> even try to use SQLite. Well, at least not directly. I would use a "hash
> table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
> is the _only_ option. What would likely work better is a "hash index". You
> might get some more concrete answers if you were to post the OS and
> implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
> using C++. Also, do you an SQL data base? Perhaps something else would be
> better, if you're not really doing relational queries. But I don't know
> what, given that I don't know your system environment.
>
> On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett
> wrote:
>
>> Hi,
>>
>> There was a topic on here a few weeks ago which I cannot remember and
>> cannot find in the mess that is my e-mail system and after spending the
>> last hours search the SQLite archives I still can?t find it so will ask
>> here if anybody can remember or help. I can?t even format the question for
>> Google to search on :(
>>
>> The specific question I have is about trying to provide the fastest
>> response possible to a select query. I recall that the e-mail talked
>> about
>> using an index to satisfy the query and therefore never having to go out
>> to
>> get the rest of the data from the table, so it was a lot quicker. Is there
>> anything that I need to do specially to make this happen. e.g. if I put
>> all
>> the fields of the table in the index BUT I really only search on the
>> primary key
>>
>> The reason for this I want to look up UK postcodes (Zip codes to our
>> American brethren) and get their longitude and latitude. A UK postcode
>> identifies a number of houses or commercial buildings. Depending on the
>> area it can be just one building (a big one) or if you are in the country
>> it can be quite a big area. If you sent a letter just to a postcode with
>> no
>> other identifier it probably wouldn?t get delivered, but putting a name on
>> it or a building number, there?s a very good chance the post(wo)?man will
>> deliver it.
>>
>> The CSV file looks like this
>>
>> id,postcode,latitude,longitude
>> 1,AB101XG,57.14416516000,-2.11484776800
>> 2,AB106RN,57.13787976000,-2.12148668800
>> 3,AB107JB,57.12427377000,-2.12718964400
>> 4,AB115QN,57.14270109000,-2.09301461900
>> 5,AB116UL,57.13754663000,-2.11269588600
>> ?.
>> Couple of million more lines
>>
>> The entire database schema looks like this. I know its complicated but
>> bear with me :)
>>
>> CREATE TABLE "postcode" (
>> "postcode" text NOT NULL,
>> "long" TEXT NOT NULL,
>> "lat" TEXT NOT NULL,
>> PRIMARY KEY("postcode")
>> );
>>
>> The only query that will ever run will be
>>
>> select long,lat from postcode where postcode = ??
>>
>> Note I drop off the id field (column 0 in the CSV file) as its of no
>> interest to me. I also store the long and lat as strings as I don?t want
>> any number formatting changes at all. Rounding on a GPS number could cause
>> the wrong location to be used.
>>
>> The database will do nothing but return long and lat based on doing a
>> postcode lookup. There will never be any updates or changes. If there are,
>> the whole database will be regenerated.
>>
>> I need this to be as fast as possible and if necessary I?ll put it all in
>> RAM. The database is currently 120MB so it would easily fit in RAM. As it
>> never changes (perhaps 4 times per year), it
[sqlite] Handling the whole select query from an index
On Thu, Sep 17, 2015 at 7:21 AM, Rob Willett wrote: > John, > > Thanks for this. > > The base OS is Linux on a a 64bit server, the wrapper will be Perl running > as a web service under Mojolicious. It will eventually be presented as a > web service. > > We could simply load up the entire database into Perl into an Assoc array > and search on that. > ?This is probably would I would do, in this particular case.? > > Richard, > > WITHOUT ROWID was what I was looking for. Thanks ?I'm going to have to review this option as well. I'm not familiar with what it accomplishes, performance wise.? > . > > All, > > What we?ll do is write a quick Perl script to check the speed of an assoc > array in Perl vs SQLite. It might be instructive to see the difference. > ?Yes, it would. Please post your results. I'm really curious about it.? > > Rob. > > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Handling the whole select query from an index
On 9/17/15, Rob Willett wrote:
>
> The specific question I have is about trying to provide the fastest response
> possible to a select query. I recall that the e-mail talked about using an
> index to satisfy the query and therefore never having to go out to get the
> rest of the data from the table, so it was a lot quicker. Is there anything
> that I need to do specially to make this happen. e.g. if I put all the
> fields of the table in the index BUT I really only search on the primary key
>
>
> The entire database schema looks like this. I know its complicated but bear
> with me :)
>
> CREATE TABLE "postcode" (
>"postcode" text NOT NULL,
>"long" TEXT NOT NULL,
>"lat" TEXT NOT NULL,
> PRIMARY KEY("postcode")
> );
>
> The only query that will ever run will be
>
> select long,lat from postcode where postcode = ??
>
At the end of the CREATE TABLE statement add keywords: "WITHOUT
ROWID". Like this:
CREATE TABLE postcode(
postcode TEXT,
long TEXT,
lat TEXT
) WITHOUT ROWID;
That should do the trick for you. You can search for WITHOUT ROWID in
the search bar on the SQLite homepage for additional information.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Handling the whole select query from an index
Well, this may be a heretical answer. Given what you have said, I wouldn't
even try to use SQLite. Well, at least not directly. I would use a "hash
table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
is the _only_ option. What would likely work better is a "hash index". You
might get some more concrete answers if you were to post the OS and
implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
using C++. Also, do you an SQL data base? Perhaps something else would be
better, if you're not really doing relational queries. But I don't know
what, given that I don't know your system environment.
On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett
wrote:
> Hi,
>
> There was a topic on here a few weeks ago which I cannot remember and
> cannot find in the mess that is my e-mail system and after spending the
> last hours search the SQLite archives I still can?t find it so will ask
> here if anybody can remember or help. I can?t even format the question for
> Google to search on :(
>
> The specific question I have is about trying to provide the fastest
> response possible to a select query. I recall that the e-mail talked about
> using an index to satisfy the query and therefore never having to go out to
> get the rest of the data from the table, so it was a lot quicker. Is there
> anything that I need to do specially to make this happen. e.g. if I put all
> the fields of the table in the index BUT I really only search on the
> primary key
>
> The reason for this I want to look up UK postcodes (Zip codes to our
> American brethren) and get their longitude and latitude. A UK postcode
> identifies a number of houses or commercial buildings. Depending on the
> area it can be just one building (a big one) or if you are in the country
> it can be quite a big area. If you sent a letter just to a postcode with no
> other identifier it probably wouldn?t get delivered, but putting a name on
> it or a building number, there?s a very good chance the post(wo)?man will
> deliver it.
>
> The CSV file looks like this
>
> id,postcode,latitude,longitude
> 1,AB101XG,57.14416516000,-2.11484776800
> 2,AB106RN,57.13787976000,-2.12148668800
> 3,AB107JB,57.12427377000,-2.12718964400
> 4,AB115QN,57.14270109000,-2.09301461900
> 5,AB116UL,57.13754663000,-2.11269588600
> ?.
> Couple of million more lines
>
> The entire database schema looks like this. I know its complicated but
> bear with me :)
>
> CREATE TABLE "postcode" (
> "postcode" text NOT NULL,
> "long" TEXT NOT NULL,
> "lat" TEXT NOT NULL,
> PRIMARY KEY("postcode")
> );
>
> The only query that will ever run will be
>
> select long,lat from postcode where postcode = ??
>
> Note I drop off the id field (column 0 in the CSV file) as its of no
> interest to me. I also store the long and lat as strings as I don?t want
> any number formatting changes at all. Rounding on a GPS number could cause
> the wrong location to be used.
>
> The database will do nothing but return long and lat based on doing a
> postcode lookup. There will never be any updates or changes. If there are,
> the whole database will be regenerated.
>
> I need this to be as fast as possible and if necessary I?ll put it all in
> RAM. The database is currently 120MB so it would easily fit in RAM. As it
> never changes (perhaps 4 times per year), it could stay there.
>
> Is there anything else from the database schema side that would make
> things quicker? e.g. If I created an index with postcode, long, lat in,
> would that be quicker? or if i changed the long, lat to real (though I?m
> reluctant to do so), would that make a lot of difference?
>
> Any suggestions gratefully received and apologies for not being able to
> find it in the archives.
>
> Thanks,
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] I don't understand how to use NOT EXISTS
On 2015-09-16 08:27 PM, Nicolas J?ger wrote: > Hi guys, > > so there is the two ways I made: > > FIRST ONE: > == > > ** Create a table wich will store the result of EXISTS, > > CREATE TABLE _TAG_EXISTS_RESULT_( NAME, BOOL ); > > > ** The next query has to INSERT 'evil little sister' with BOOL to 1 in > _TAG_EXISTS_RESULT_ if 'evil little sister' is already present in TAGS, > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > SELECT 'evil little sister' > , EXISTS (SELECT 1 FROM TAGS WHERE NAME='evil little sister'); > > > ** Then I add 'evil little sister' if _TAG_EXISTS_RESULT_.BOOL = 0.. > > INSERT INTO TAGS (NAME, COUNT) >VALUES( > CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 >THEN ( SElECT NAME FROM _TAG_EXISTS_RESULT_ ) >ELSE '$NOT_USED$' > END >, 0 ); > > ** delete $NOT_USED$ if so, > > DELETE FROM TAGS WHERE NAME='$NOT_USED$'; > > > ** Then, clear _TAG_EXISTS_RESULT_, > > DELETE FROM _TAG_EXISTS_RESULT_; > > > SECOND ONE: > == > > ** I made a compact version, > > INSERT INTO TAGS (NAME, COUNT) >VALUES >( > CASE( SELECT EXISTS( SELECT 1 FROM TAGS WHERE NAME='evil little > sister' ) ) WHEN 0 > THEN 'evil little sister' > ELSE '$NOT_USED$' > END >, 1 >); > > > ** delete $NOT_USED$ if so, > > DELETE FROM TAGS WHERE NAME='$NOT_USED$'; > > > comments are welcome. It's hard to know where to begin. This looks much more like programming code than SQL - and while there are similarities, the usage is very different. Let me start with explaining the first and most important difference: Most programming languages are imperative (methodical or executive) descriptors - i.e you tell the process/engine/compiler HOW to achieve a task (which seems a bit like what you are trying to do up above). SQL is really a declarative language, i.e. you tell the process/engine WHAT you need, and it will figure out HOW to achieve that in the best and most efficient way. Of course, that is easier said than done, but we've all started with little things and built on from there. Very soon you will see how you can create a schema and some queries to achieve data-handling tasks that used to take you ages to program (at least, that's how it happened for me). To get you started on this road, may I ask you take a leap of faith and explain to us from A to Z what you would like to achieve with the tables and data you are asking about above - i.e don't show us the programming you think will solve the problem and ask us if that is good SQL, rather explain us the problem to be solved and allow us to show you the good SQL that will do it. Every next task should be clearer and easier. Cheers, Ryan
[sqlite] I don't understand how to use NOT EXISTS
Hi, @Simon, > >CASE ( SElECT BOOL FROM _TAG_EXISTS_RESULT_ ) WHEN 0 > > CASE takes a value. You can't put a SQLite command in there. > actually, it's working if you put the command between (). I guess it's like the `` on linux (eg), mplayer `ls *.mp3` but maybe,even if it's working, you want to point that's not correct to use it. > More generally, you come from a programming background and are > expecting SQL to work like a programming language. It doesn't. that's clearly a difficulty for me... > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. > > This is why we quoted the form > > INSERT OR IGNORE ...; > UPDATE ...; > > earlier. Two separate commands, not one command trying to run > another. I have to think about that... - @Ryan, > To get you started on this road, may I ask you take a leap of faith > and explain to us from A to Z what you would like to achieve with the > tables and data you are asking about above - i.e don't show us the > programming you think will solve the problem and ask us if that is > good SQL, rather explain us the problem to be solved and allow us to > show you the good SQL that will do it. > > Every next task should be clearer and easier. Actualy, it's a personal open source project. So the code could be see by anyone. The only thing is, the most recent part isn't yet on the repo (last push was before the summer). But, if for some reason you want to see the code as it is today on my computer (almost all files were heavily modified during the summer), I can push it. this project is about helping me to manage all files I have on my computer. I'm a metalhead and science nerd at the same time. So, I have tons of music and tons of ebooks and publications... So my project as currently two goals: 1) store the data on disk, I should not access the files directly. 2) using a DB to retrieve any document trough a search engine. (actually I want to add a third goal: I want to create something I don't know how I will call it, but it's something like a binder. For example if you open a binder about 'modelling demons', you can link data of differents files even if they don't share tags or whatever (can be in that example some texture, some .blend or pdf about modelling humanoid...).) So, I'm writing some custom GTKmm widgets wich interact with the DB (but they don't directly access any sqlite function, I'm working with engines communication (gtkmm_engine, sqlite_engine, etc.)). I'm currently thinking to make that program modular. For example, the user (usualy me), set what kind of data (trough some config file (in my head I call these files 'collection')) should be store in the DB. For each data will correspond a widget. Let's took an example, for ebooks: I want to save the filename (filename is ruled by the program, the user has no direct way to modify it), title, some tags and a description for each ebook (for the last three the program shows some widgets, wich allow the user to modify the values). At this point, when the program read the ebook.collection, it will create a first table EBOOK with four columns (ID,FILENAME,TITLE,DESCRIPTION) for the tags, things are more complicated. I need two tables: - TAGS with three columns (ID,NAME,COUNT) - TAGSMAP with two columns (ID_EBOOK,ID_TAGS) (I explain the use below) so if I take my old example : (id|NAME|COUNT) 53|black metal|3 55|occult rock|3 61|satanic smurfs|1 62|beer|0 63|pizza|0 if I'm seeking to get all files with the tags 'black metal' the program check all entries in TAGSMAP with ID_TAGS = 53, SELECT * FROM TAGSMAP WHERE ID_TAGS = 53 would returns something like, (ID_EBOOK|ID_TAGS) 3|53 5|53 9|53 then the ID_EBOOKs refer to the IDs of the data (title and description in this case) for some files in EBOOK. now my problems come when you want to add/delete a tags. If for a file you want (eg) to delete the tag 'black metal' you click on the button I setup to delete that tag, the program at this time has two informations: the current EBOOK.FILENAME and the TAGS.NAME of the tag. So I have to query the DB to get their respective IDs (ID of that FILENAME in EBOOK and ID of that NAME in TAGS). Then, the program has to delete the entry in the TAGSMAP where ID_EBOOK is the same than ID for the current file in EBOOK and where ID_TAGS equal the ID in TAGS. For TAGS, the program has first to check COUNT. Before the check I have to get the value from the DB. If COUNT = 1, the program has to delete the tag with NAME 'black metal'. If COUNT > 1, the program has to decrement count. My first manner to do that, was to just store/read the data to/from the DB. All conditions (like what I try to do for some days now...) were executed by the program, not by queries. But the problem
