[sqlite] count up how much an free space on DB
Hello. There is a DB. auto_vacuum is not used. One of tables of a database contains 500 records. We for example delete from it 300 records. The place in a database was released, but the size of a file has not decreased how is not set auto_vacuum. Question, whether probably to count up how much an free space there is in this a DB? Вы уже с Yahoo!? Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
Hi RBS! If I understood you correctly you need a tool to transform these data just once? So there is a pseudocode describing one of possible approaches. To convenient transformation SQLite is not enough for me, I suggest to use any script language like Lua, Ptython, etc. 1) With a statement SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC LIMIT 1 Determine max number of a values 2) construct create table statement CREATE TABLE new( ID INTEGER NOT NULL UNIQUE for n=1, maxVal ", value TEXT" end ); and execute it 3) then navigate through 'old' table, create statements for insert data to 'new' But please be sure that you need exactly such transformation. It is a _denormalization_, almost anytime people try to perform conversion exactly as you describe but in reverse direction :) With a 'new' table many operation, such as adding another one value for ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away from SQL logic. Regards, Denis -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Thursday, December 14, 2006 10:39 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Transpose table The example I gave shows exactly what I need to do. I have a column of ID numbers with duplicates. I have to make this column hold only unique ID numbers by moving the values to the first row where that ID number appears, with that increasing the number of columns. Hope this makes it a clearer. RBS -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: 14 December 2006 06:59 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Transpose table Can you please provide a use case for your example, so we know what you're trying to accomplish? That should help us to help you better. -- Darren Duncan At 12:08 AM + 12/14/06, RB Smissaert wrote: >I am moving my code away from VBA and transferring it to SQL. There is >one particular routine where I haven't found a good replacement for >and that is to transpose a table from a vertical layout to a horizontal one, - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
Hi Denis, Yes, it is a one-off action and the only purpose is to present the data into and Excel sheet in a more readable way. I had done your suggestion in VBA, but I thought it was a bit slow and wondered if there was a better way. I have just found a possible way to do this and maybe it is faster. Say I have a table with an ID column and 3 other columns. The data in these other columns need to be grouped by ID number, so ID col1 col2 col3 would become: ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 etc. where the maximum number of fields will be determined by the maximum number of records for one ID number Now I found that if I do: select ID, col1, col2, col3 from table group by ID Then it will always pick up the row that comes last in the group of ID numbers. This might actually be faster than doing a subquery with MAX. Now if I run the above and move the data to a new table, say table2 and then run a query like this: select t1.ID, t1.col1, t1.col2, t1.col3 from table1 t1 inner join table2 t2 on (t1.ID = t2.ID) where t1.col1 < t2.col1 group by t1.ID Then I will get the rows (if there was a row left)in the ID group that comes second from last, so ID 1 1 1 1 < will get this one 1 If I keep repeating this in a VBA loop and then join the tables I would get my output. Not sure it is faster, but I think it might. Will see. RBS > Hi RBS! > > If I understood you correctly you need a tool to transform these data > just once? > So there is a pseudocode describing one of possible approaches. To > convenient transformation SQLite is not enough for me, I suggest to use > any script language like Lua, Ptython, etc. > > 1) With a statement > SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC > LIMIT 1 > Determine max number of a values > > 2) construct create table statement > CREATE TABLE new( > ID INTEGER NOT NULL UNIQUE > for n=1, maxVal > ", value TEXT" > end > ); > and execute it > > 3) then navigate through 'old' table, create statements for insert data > to 'new' > > > > But please be sure that you need exactly such transformation. It is a > _denormalization_, almost anytime people try to perform conversion > exactly as you describe but in reverse direction :) > > With a 'new' table many operation, such as adding another one value for > ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away > from SQL logic. > > Regards, Denis > > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 14, 2006 10:39 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Transpose table > > > The example I gave shows exactly what I need to do. > I have a column of ID numbers with duplicates. I have to make this > column hold only unique ID numbers by moving the values to the first row > where that ID number appears, with that increasing the number of > columns. Hope this makes it a clearer. > > RBS > > -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: 14 December 2006 06:59 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Transpose table > > Can you please provide a use case for your example, so we know what > you're trying to accomplish? That should help us to help you better. > -- Darren Duncan > > At 12:08 AM + 12/14/06, RB Smissaert wrote: >>I am moving my code away from VBA and transferring it to SQL. There is >>one particular routine where I haven't found a good replacement > for >>and that is to transpose a table from a vertical layout to a horizontal > one, > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > > - > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] building sqlite on windows in Unicode
Check the requirements in: http://www.sqlite.org/copyright.html for patches and other submissions to SQLite. This could be what is holding up inclusion of the patch. Christian Brodie Thiesfield uttered: Hi, Building sqlite on windows in Unicode mode broke with the addition of the loadable extensions. I found a bug matching this problem and attached a patch to it to fix it a while ago, however I haven't seen any other comments or movement in the bug. I'm not sure what else needs to be done to have a patch accepted, so I'm posting here in the hope to prod it along for review or acceptance. The problem is that the dlopen/LoadLibrary code looks like it was hacked in instead of being added to the platform abstraction API and it doesn't support windows unicode builds out of the box. The patch fixes that problem and silences a warning generated by the new index format. bug... http://www.sqlite.org/cvstrac/tktview?tn=2023 patch... http://www.sqlite.org/cvstrac/attach_get/309/sqlite3.patch Regards, Brodie - To unsubscribe, send email to [EMAIL PROTECTED] - -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
Have tested this now and it seems to work fine. Faster as well than my old method. RBS > Hi Denis, > > Yes, it is a one-off action and the only purpose is to present the data > into and Excel sheet in a more readable way. > I had done your suggestion in VBA, but I thought it was a bit slow > and wondered if there was a better way. > > I have just found a possible way to do this and maybe it is faster. > Say I have a table with an ID column and 3 other columns. > The data in these other columns need to be grouped by ID number, so > > ID col1 col2 col3 > > would become: > > ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > > etc. where the maximum number of fields will be determined by the > maximum number of records for one ID number > > Now I found that if I do: > select > ID, > col1, > col2, > col3 > from > table > group by > ID > > Then it will always pick up the row that comes last in the group of > ID numbers. This might actually be faster than doing a subquery with MAX. > > Now if I run the above and move the data to a new table, say table2 and > then run a query like this: > > select > t1.ID, > t1.col1, > t1.col2, > t1.col3 > from > table1 t1 inner join table2 t2 on > (t1.ID = t2.ID) > where > t1.col1 < t2.col1 > group by > t1.ID > > Then I will get the rows (if there was a row left)in the ID group > that comes second from last, so > > ID > 1 > 1 > 1 > 1 < will get this one > 1 > > If I keep repeating this in a VBA loop and then join the tables I would > get my output. Not sure it is faster, but I think it might. > Will see. > > > RBS > > > > > > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? >> So there is a pseudocode describing one of possible approaches. To >> convenient transformation SQLite is not enough for me, I suggest to use >> any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC >> LIMIT 1 >> Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert data >> to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value for >> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away >> from SQL logic. >> >> Regards, Denis >> >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first row >> where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -Original Message- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM + 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There is >>>one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a horizontal >> one, >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> >> - >> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: File Syste
At 22:03 13/12/2006, you wrote: I am developing a File System, and I'd like to use B+ Tree and not lost time and CPU understanding SQL... Check HFS(16/32 bits) and HFS+(64 bits) filesystems from Apple, they use B+ trees. The code is open source and you can find it on Darwin repository. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] disk I/O error writing files mounted via samba
I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to read/write a database file in a directory which is actually on a windows share mounted via samba/cifs. I can open the file, and read from it, but writing produces "disk I/O error" messages (SQLITE_IOERR error code). I can write ordinary files on the share (echo "foo" > [share]/file.txt) so it doesn't look like a permissions issue. Only one process is ever going to access the file so I wouldn't expect locking issues. If I try turning things around so that I build/run my sqlite program on windows and access a file on a samba share exported from my linux box I can read/write without any errors. Anyone tried this? I'd love to solve this problem as it will be the final nail in the coffin for MSAccess in my app ;-) cheers guy - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] GROUP BY in SQLite
Just wondering about the implementation of GROUP BY in SQLite. It looks I can do: select field1, field2, field3 from table group by field1 and I will get the last row in the field1 group. This is fine and I can use that, but I thought that the SQL standard was that all non-aggregate fields should be in the GROUP BY clause. I just tried it in Interbase and the above construction indeed doesn't work with the error: SQL error code = -104, invalid column reference Runs fine though in SQLite. Is this a known feature? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] count up how much an free space on DB
Artem Yankovskiy <[EMAIL PROTECTED]> wrote: > Question, whether probably to count up how much an > free space there is in this a DB? > If the first byte of the database file is called byte 0, then bytes 16 and 17 form a 16-bit big-endian integer which is the number of bytes per page. Bytes 36-39 form a 32-bit big-endian integer which is the number of unused pages in the database. If you multiple those two numbers together, you will get the amount of reusable free space in the database file. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] disk I/O error writing files mounted via samba
Guy Hindell <[EMAIL PROTECTED]> wrote: > I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to > read/write a database file in a directory which is actually on a windows > share mounted via samba/cifs. I can open the file, and read from it, but > writing produces "disk I/O error" messages (SQLITE_IOERR error code). I > can write ordinary files on the share (echo "foo" > [share]/file.txt) so > it doesn't look like a permissions issue. Only one process is ever going > to access the file so I wouldn't expect locking issues. If I try turning > things around so that I build/run my sqlite program on windows and > access a file on a samba share exported from my linux box I can > read/write without any errors. > Please turn on extended result codes using sqlite3_extended_result_codes(db, 1) Then tell me the detailed error code that results from this error. That will help to isolate the problem. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
It's nice that problem was solved. JFYI. In common, task for creating sparse matrix from plain sql normalized table is very common for OLAP. Maybe you shall read something about it if these task arised from time to time. Best regards, Denis -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 14, 2006 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Transpose table Have tested this now and it seems to work fine. Faster as well than my old method. RBS > Hi Denis, > > Yes, it is a one-off action and the only purpose is to present the > data into and Excel sheet in a more readable way. I had done your > suggestion in VBA, but I thought it was a bit slow and wondered if > there was a better way. > > I have just found a possible way to do this and maybe it is faster. > Say I have a table with an ID column and 3 other columns. The data in > these other columns need to be grouped by ID number, so > > ID col1 col2 col3 > > would become: > > ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > > etc. where the maximum number of fields will be determined by the > maximum number of records for one ID number > > Now I found that if I do: > select > ID, > col1, > col2, > col3 > from > table > group by > ID > > Then it will always pick up the row that comes last in the group of ID > numbers. This might actually be faster than doing a subquery with MAX. > > Now if I run the above and move the data to a new table, say table2 > and then run a query like this: > > select > t1.ID, > t1.col1, > t1.col2, > t1.col3 > from > table1 t1 inner join table2 t2 on > (t1.ID = t2.ID) > where > t1.col1 < t2.col1 > group by > t1.ID > > Then I will get the rows (if there was a row left)in the ID group that > comes second from last, so > > ID > 1 > 1 > 1 > 1 < will get this one > 1 > > If I keep repeating this in a VBA loop and then join the tables I > would get my output. Not sure it is faster, but I think it might. Will > see. > > > RBS > > > > > > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? So there is a pseudocode describing one of possible >> approaches. To convenient transformation SQLite is not enough for me, >> I suggest to use any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter >> DESC LIMIT 1 Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert >> data to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value >> for ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come >> away from SQL logic. >> >> Regards, Denis >> >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first >> row where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -Original Message- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM + 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There >>>is one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a >>>horizontal >> one, >> >> >> - >> --- >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> >> - >> >> >> >> >> - >> --- >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >>
Re: [sqlite] GROUP BY in SQLite
Hi Bart, First of all, you clearly try to start a new subject here, so please don't reply to something completely different, but start a new thread. TIA. On Thu, 14 Dec 2006 12:15:57 - (GMT), you wrote: >Just wondering about the implementation of GROUP BY in SQLite. >It looks I can do: > >select >field1, >field2, >field3 >from >table >group by >field1 > >and I will get the last row in the field1 group. In SQL there isn't something like a "last row", unless you specify ORDER BY. You get "some row", next time you run the query you might get another one. >This is fine and I can use that, but I thought that the SQL standard >was that all non-aggregate fields should be in the GROUP BY clause. That's right. So if you value your application you can't use it, even though sqlite doesn't throw an error at the moment. >I just tried it in Interbase and the above construction >indeed doesn't work with the error: > >SQL error code = -104, invalid column reference > >Runs fine though in SQLite. > >Is this a known feature? I wouldn't call it a feature, and you'd better not rely on this behaviour. >RBS HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transpose table
On Thu, 14 Dec 2006 09:53:22 - (GMT), you wrote: >Hi Denis, > >Yes, it is a one-off action and the only purpose is to present the data >into and Excel sheet in a more readable way. >I had done your suggestion in VBA, but I thought it was a bit slow >and wondered if there was a better way. If Excel is the target anyway I guess the fasted way to do this is to use the transpose option of the paste-special function in Excel itself. >I have just found a possible way to do this and maybe it is faster. >Say I have a table with an ID column and 3 other columns. >The data in these other columns need to be grouped by ID number, so > >ID col1 col2 col3 > >would become: > >ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > >etc. where the maximum number of fields will be determined by the >maximum number of records for one ID number > >Now I found that if I do: >select >ID, >col1, >col2, >col3 >from >table >group by >ID > >Then it will always pick up the row that comes last in the group of >ID numbers. This might actually be faster than doing a subquery with MAX. > >Now if I run the above and move the data to a new table, say table2 and >then run a query like this: > >select >t1.ID, >t1.col1, >t1.col2, >t1.col3 >from >table1 t1 inner join table2 t2 on >(t1.ID = t2.ID) >where >t1.col1 < t2.col1 >group by >t1.ID > >Then I will get the rows (if there was a row left)in the ID group >that comes second from last, so > >ID >1 >1 >1 >1 < will get this one >1 > >If I keep repeating this in a VBA loop and then join the tables I would >get my output. Not sure it is faster, but I think it might. >Will see. > > >RBS > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? >> So there is a pseudocode describing one of possible approaches. To >> convenient transformation SQLite is not enough for me, I suggest to use >> any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC >> LIMIT 1 >> Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert data >> to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value for >> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away >> from SQL logic. >> >> Regards, Denis >> >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first row >> where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -Original Message- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM + 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There is >>>one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a horizontal >> one, >> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ANN: SQLite Maestro 6.12 released
Hi All, SQL Maestro Group is happy to announce the release of SQLite Maestro 6.12, a powerful Windows GUI tool for SQLite databases administration and development. The new version is immediately available at http://www.sqlmaestro.com/products/sqlite/maestro/ Also we're happy to grant you a time-limited 25% discount on all our products and bundles. Don't miss this unique chance to get our software at a special Christmas price! http://www.sqlmaestro.com/purchase/ What's new/changed in version 6.12? 1. The main feature of the new version is the tabbed MDI interface implementation (like Mozilla Firefox). Here are some its advantages: - you can drag-n-drop tabs to change their order; - you can close the current tab pressing the mouse wheel (or the middle mouse button); - you can drag-n-drop database objects or selected text between tabs; - each tab has a popup menu, which allows you to close current tab, close all tabs or close all the tab except selected one (close other tabs). 2. SQL Script Editor: if a user opens a file larger than 100K, SQLite Maestro will suggest him to execute the script file without opening it in the editor. Of course it is also possible to execute any script file using this way: just press the "Execute script from file" link on the Script Editor navigation bar and specify the filename. 3. We've added an option for converting new object names to lower/upper case. 4. Data grid: the speed of data loading was significantly increased. 5. Some other improvements and small bugfixes. Sincerely yours, SQL Maestro Group http://www.sqlmaestro.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] GROUP BY in SQLite
Give each line a few extra spaces. I don't know why, but I had the same error. I gave a few extra spaces, specially at the end, and it worked just find... - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Thursday, December 14, 2006 7:15 AM Subject: [sqlite] GROUP BY in SQLite Just wondering about the implementation of GROUP BY in SQLite. It looks I can do: select field1, field2, field3 from table group by field1 and I will get the last row in the field1 group. This is fine and I can use that, but I thought that the SQL standard was that all non-aggregate fields should be in the GROUP BY clause. I just tried it in Interbase and the above construction indeed doesn't work with the error: SQL error code = -104, invalid column reference Runs fine though in SQLite. Is this a known feature? RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition
Alexandre Guion <[EMAIL PROTECTED]> wrote: I tried this one of course, and it doesn't work, it could be a bug. It returns every property for every medium (not just 'myprops') This one is tested and appears to work: SELECT m.MediumID AS id, IFNULL(MAX(pn.Text), '') AS name, IFNULL(MAX(CASE WHEN pn.Text IS NULL THEN NULL ELSE pv.Text END), '') AS value FROM Media AS m LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1) LEFT JOIN PropName AS pn ON (p.PropNameID=pn.PropNameID AND pn.Text='myprop') LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID GROUP BY m.MediumID ORDER BY value; Each group produced by GROUP BY can have only one non-NULL value in pn.Text (assuming each medium may have no more than one of each property). MAX aggregate is simply used to pick up this single non-NULL value, and the corresponding pv.Text Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL extension
What are our options to extend or change SQL syntax if needed? Dusan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLiteJDBC is now 100% Pure Java
David Crawshaw uttered: For several months now I have been providing a Java JDBC driver for SQLite 3. Now I am happy to announce with v030, the completion of a 100% Pure Java version. http://www.zentus.com/sqlitejdbc This is accomplished with the support of the great NestedVM project. The SQLite C source is compiled by gcc to a MIPS binary, that is then dynamically interpreted on top of the Java runtime. This involves a performance hit but greatly simplifies inclusion in current projects and delivery cross-platform. Is locking now handled by NestedVM so that the driver is useable with native SQLite processes? The native JNI version has not been abandoned, and binaries are still provided for Windows and Mac OS X. Linux can be directly compiled. The new native version has been extensively tested, with the only major caveat at the moment being the size of the binary. I believe it can be greatly reduced from the current ~ 800kb, I just do not have the time. Still, it's smaller than Derby. A head to head might be nice. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLiteJDBC is now 100% Pure Java
On 14/12/06, Christian Smith <[EMAIL PROTECTED]> wrote: > http://www.zentus.com/sqlitejdbc Is locking now handled by NestedVM so that the driver is useable with native SQLite processes? Yes, that's why it took me so long. :) I have implemented fcntl() locking using java.nio.*, which means it requires Java 1.4 for file locking. Amazingly Java did not support file locking before 1.4 and when they did implement it they made a terrible mistake, reminiscent of closing an FD on fcntl() locks. > I believe it can be greatly reduced from the current ~ 800kb, Still, it's smaller than Derby. A head to head might be nice. It can be reduced to ~ 350kb with dynamic interpreter as Joe Wilson suggested, but this will slow it down further. I will do a speed test with derby when I am done with some other nagging bugs. d - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] disk I/O error writing files mounted via samba
Guy Hindell wrote: > I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to > read/write a database file in a directory which is actually on a windows > share mounted via samba/cifs. I can open the file, and read from it, but > writing produces "disk I/O error" messages (SQLITE_IOERR error code). I > can write ordinary files on the share (echo "foo" > [share]/file.txt) so > it doesn't look like a permissions issue. Only one process is ever going > to access the file so I wouldn't expect locking issues. If I try turning > things around so that I build/run my sqlite program on windows and > access a file on a samba share exported from my linux box I can > read/write without any errors. > > Anyone tried this? I'd love to solve this problem as it will be the > final nail in the coffin for MSAccess in my app ;-) Is the database bigger than 2Gb? If so, have you got large file support in samba (lfs in the mount options)? -- Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming http://www.trumphurst.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Ответ: Re: [sqlite] count up how much an free space on DB
Thanks Richard. I will try. --- [EMAIL PROTECTED] пишет: > Artem Yankovskiy <[EMAIL PROTECTED]> wrote: > > Question, whether probably to count up how much an > > free space there is in this a DB? > > > > If the first byte of the database file is called > byte 0, > then bytes 16 and 17 form a 16-bit big-endian > integer > which is the number of bytes per page. Bytes 36-39 > form > a 32-bit big-endian integer which is the number of > unused > pages in the database. If you multiple those two > numbers > together, you will get the amount of reusable free > space > in the database file. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > Вы уже с Yahoo!? Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] combining multiple queries
heya folks, I'm trying to figure out the proper syntax for doing a query of something like this: I have two different types of things that people will be querying for, and I want to do an OR on each, but and AND between them.. So let's say i've got a table that looks like this: Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR): Genre can be of whatever genre the user wants.. comedy, drama, etc. tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;" etc If the user doesn't specify either genre or a tag when they're querying, I want to return the whole list of films (that's an easy one). If the user specifies one or more tags, I want to be able to return all moves that match EITHER tag.. for example, if htey specify "carrey" and "snow", it should return all movies that have either carrey or snow in the list. If they then specify a genre, I want it to return ONLY the movies in the specified genre that match carrey or snow. Then (just to make it more complicated), if they specify two genres, it should return all movies in either of those genres that match either carrey or snow. Does this make sense? I know how to string a whole bunch of LIKEs together with an OR to get it to work so you could get it to match a genre or a tag.. but how do I do something like: SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag = "bill murry" OR tag = "scarlett o'hara" OR tag = "silly"); any help is greatly appreciated.. cheers! -jason -- jason schleifer ah-ni-may-tor | weirdo http://jonhandhisdog.com/
Re: [sqlite] combining multiple queries
two tings I would do. 1. further normalize your tag field make it a table keyed by id and tag join to this in your query 2. for genre = "comedy" OR genre = "drama" I would genre IN ("comedy", "drama") you might consider further normalizing genre, to reduce duplication and also allow multiple genre for a film. jason schleifer wrote: heya folks, I'm trying to figure out the proper syntax for doing a query of something like this: I have two different types of things that people will be querying for, and I want to do an OR on each, but and AND between them.. So let's say i've got a table that looks like this: Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR): Genre can be of whatever genre the user wants.. comedy, drama, etc. tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;" etc If the user doesn't specify either genre or a tag when they're querying, I want to return the whole list of films (that's an easy one). If the user specifies one or more tags, I want to be able to return all moves that match EITHER tag.. for example, if htey specify "carrey" and "snow", it should return all movies that have either carrey or snow in the list. If they then specify a genre, I want it to return ONLY the movies in the specified genre that match carrey or snow. Then (just to make it more complicated), if they specify two genres, it should return all movies in either of those genres that match either carrey or snow. Does this make sense? I know how to string a whole bunch of LIKEs together with an OR to get it to work so you could get it to match a genre or a tag.. but how do I do something like: SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag = "bill murry" OR tag = "scarlett o'hara" OR tag = "silly"); any help is greatly appreciated.. cheers! -jason - To unsubscribe, send email to [EMAIL PROTECTED] -